Practical application is the key to mastering basic Excel skills, and the following guide provides such hands-on application. The first step in developing basic Excel skills is to gain familiarity with its various features and functions and where to find them, the second step is finding the most efficient way to use them. Accordingly, try repeating certain commands introduced in each step multiple times to get them “under your belt.” Furthermore, in order to be able to use Excel in the most efficient way, try and resist the initial temptation to use a mouse
Basic Excel Shortcuts, Navigation, & Editing
Most keyboard shortcuts involve Alt or Ctrl keys, so it is very important to comprehend how to use each of them. When using keyboard shortcuts involving Alt, a user is able to press each key and let go. In other words, there is no need to hold down the Alt key while pressing additional commands on the keyboard. The following shortcut to “Save As” includes a total of only three distinct steps and acts as an example of the previously discussed points in order to develop basic Excel skills:
- Press Alt (and let go) – it activates the Main Tabs.
- Press f (and let go) – it opens the Office Button menu.
- Press a (and let go) – it brings up the ‘Save As’ screen.
- Most commands involving Alt are shortcuts to the commands and functions inside the eight default Main Tabs.
- Most commands involving Ctrl are shortcuts that have been automated by default to make Excel more efficient and user-friendly.
- In Ctrl commands, the Ctrl key must be held down
as the user presses the other key in the shortcut sequence.
- Notice how this is different from Alt commands which involve “distinct steps” (pressing one key after, not in conjunction, with another).
Explanation of Calculations and Formulas
Excel formulas start with the = sign. The = sign tells Excel that the information that will follow the = sign should be treated as a formula and not as plain text. Once the user types in the = sign, the arrow keys are used to navigate around the Excel workbook to find the cells that are needed for a particular formula. The signs for formulas are intuitive in Excel and are as follows:
- Addition and subtraction: + and –
- Multiplication: *
- Division: /
- Exponent ^
- Grouping operations ()
Formatting & Navigation
- “CTRL+SHIFT+!” Number format: 2 decimals, 000 separators. Example: 2847.34
- “CTRL+SHIFT+$: Currency format: 2 decimal places. Example: $2847.34
- “CTRL+SHIFT+%” Percentage format with no decimal places. Example: 284734%
- “CTRL+SH1FT+#” Date format with the day, month, and year. Example: 7-Jun-03
- “CTRL B” Boldface. Example: 2847.34
- “CTRL I” Italicize. Example: 2847.34
- “CTRL U” Underline. Example: 2847.34
- “CTRL+SHIFT+_” Remove all borders. Example: 2847.34
Common Excel Errors and Their Meaning
#DIV/0 Divided by zero
#VALUE! Incorrect arguments
#REF Referencing a previously deleted cell
#NUM! Number not valid
######## Column not wide enough
#NAME? Text not valid
There are two ways to audit formulas in Microsoft Excel. The first way to audit formulas is relatively simple:
- User goes to the desired cell and hits the F2 key. Excel highlights all cell components of an existing formula in different colors.
The second way of auditing cells is based on CTRL and is considered to be the superior method of basic Excel skills:
- User moves to the desired cell and hits CTRL which highlights the precedent cells. By keep hitting CTRL, Excel takes the user to the next precedent cells.
Paste Special is a very useful tool in Microsoft Excel which every user should have in their basic Excel skills set. In general, the combination CTRL v pastes all cell attributes. For instance, by copying and pasting a formula from cell A1 to B1, Excel not only copies the formula, but also the formatting. This means that by default, Microsoft Excel copies over all attributes of a cell.
There are many cases in which a user desires to only copy and paste certain attributes of one cell to the other. For instance, a user may want to copy over only comments, values, or formulas but nothing else.
Paste Special lets the user dictate the specific attributes of a cell or highlighted region that should be posted.
- The keyboard combination is CTRL c, followed by ALT e s.
In addition to pasting specific combinations of formulas, formats, and values, the paste special feature allows users to operate on multiple values in exactly the same way. Another useful paste special feature is the “transpose” feature. It allows users to convert a vertical lot of data into a horizontal list and vice versa.
Splitting and Freezing Panes
Excel worksheets often become too large to allow users to view all of their contents on one screen. Accordingly, ‘Freezing Panes’ and ‘Splitting Panes’ options provide users with the flexibility to select specific rows and columns that always remain visible when scrolling in the worksheet. Panes are defined as portions of the worksheet that are bounded and separated by vertical and/or horizontal bars.
While freezing panes allows users to select specific rows and columns that always remain visible when scrolling in the worksheet, splitting panes allows users to scroll in both areas of the worksheet, while rows and columns in the non-scrolled area remain visible.
How to split panes?
- Click the cell below the desired row and to the right of the desired column where you want to split panes and hit ALT w s.
- To un-split panes, click ALT w s again.
- Press F6 to move from pane to pane in a clockwise direction; press Shift F6 to move from pane to pane in a counter-clockwise direction.
How to freeze panes?
- Click the cell below the desired row and to the right of the desired column where you want to freeze panes and hit ALT w f f.
- To unfreeze panes, press ALT w f f again.
Date & Concatenation Functions
Introduction to Excel functions
Functions are pre-built combinations of operations in Excel that facilitate spreadsheet analysis. In Excel, there are over 300 functions, which are grouped into 11 categories by the various purposes they serve. Functions are useful on their own, but become even more powerful when they are combined together. In order to develop a basic Excel skills set, the basic structure of every function is as follows:
- Every function starts with an “=” sign, then the function name, and then the cell range (bound within parentheses).
- All functions follow the same syntax:=functionname(argumentl,argument2 ….. argumentx).
- Within the parentheses, functions can have 0, 1, or many arguments, separated by commas.
- In the SUM function =SUM(A1:A10), there is only one argument, and the argument is telling Excel the specific cell range that needs to be summed up; we will encounter functions where there are several arguments.
Arguments will become a little clearer when we actually deal with them, but for now just remember that arguments are things inside the parentheses of a function, separated by commas, that tell Excel certain things about the function.
- The IF function is one of the most useful functions in Excel.
- The IF function returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
- Use IF to conduct conditional tests on values and formulas.
- All IF statements follow the same structure
- x = Criteria
- y = output if criteria evaluates to true
- z = output if criteria evaluates to false
Cell contents as criteria in IF statements, ISNUMBER, ISTEXT
A surprisingly useful criteria used in IF statements, is a test of whether a cell has anything in it. For example, the IF statement =IF(C1,C2,C3) would see if there is anything in cell Cl, and if there was, it would output C2, otherwise C3. Two functions that do something similar are the ISNUMBER and ISTEXT function, both typically embedded within an IF statement, and test whether there is a number or text inside a cell, respectively. In the example below, we combine an IF statement with the ISTEXT function to create outputs that are different depending on whether there was text in the criteria cell.
HLOOKUP and VLOOKUP
Some of the most useful functions in Excel are functions that allow the user to locate data stored in large tables. HLOOKUP and VLOOKUP are the most popular functions for performing this type of data extraction and therefore a must in every basic Excel skills set.
- HLOOKUP(look up value, table range, row number) searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows.
- VLOOKUP(lookup value, table range, column number) searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table or array. Use VLOOKUP when your comparison values are located in a column to the left of a table of data, and you want to look across (and to the right) a specified number of columns.
Most Frequently Used Mathematical Functions
Some of the most frequently used and beneficial mathematical functions in Excel really become effective when combining with other Excel functionality
- =SUMPRODUCT(arrayl,array2,array3, …) multiplies corresponding components in two or more arrays, and returns the sum of those products.
MIN and MAX functions are used frequently in financial analysis and therefore crucial to every basic Excel skills set. A classic use in financial modeling is to use a max function to prevent a revolving credit line balance from dipping below 0 when there is a cash shortfall
- =MIN(numberl,number2,…) returns the smallest number in a specified set of values.
- =MAX(numberl,number2,…) returns the largest number in a specified set of values.
NPV vs. PV functions
- PV assumes constant payments, while NPV cash flows can vary from period to period.
- NPV does not require user to explicitly identify number of periods and simply assumes equal periods based on the number of values.
- NPV assumes payments occur at the end of the
period (but formula can be adjusted to simulate payments occurring at beginning
- =NPV(rate, value1, value2, …) returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values).
- Values are assumed to occur at the end of each time period and must be referenced in the order in which they occur.
- NPV performs equal discounting on each cash flow – this is not desirable when the timing of cash flows is uneven.
- XNPV Syntax =INPV(rate, values, dates)returns the net present value for a set of cash flows that do not necessarily occur at equal time intervals.
- Unlike NPV, the first cash flow is not discounted, while dates of subsequent cash flows can be included in the formula in any order.
- IRR Syntax: 4RR(values, guess) returns the IRR for a series of values.
- IRR is the rate corresponding to an NPV of 0.
- While values do not have to be identical each period, the periods are assumed to be equally far apart.
- ‘Guess’ is an optional argument. Excel needs a starting point to iterate to the right IRR. If you choose not to input a “guess”, Excel will automatically use 10% as the guess.
- XIRR =XIRR(values, dates, guess)returns the IRR for a series values which may not be periodic.
Macros & VBA
As users have developed the most basic Excel skills, they often find themselves repeating many tasks such as setting up print ranges in a particular way or formatting cells in a particular way. Instead of repeating the same steps all over again manually, Excel enables users to record particular instructions, assign a keyboard name, and invoke them as needed. These instructions are called macros. In the following video, the process of recording a simple macro is outlined.
Thanks for Visiting!
After going through this guide, you should have formed a decent understanding of the basic Excel skills. If you have any questions or comments, leave them below and make sure to subscribe the YouTube channel!