Excel is an extremely versatile piece of software and one that every accountant will have used at some point in their career. Excel can do as much or as little as you want it to, whether it’s automating processes using macros, creating projections, graphs, and of course those all too familiar perennial accounting tasks, from payroll to bank reconciliations to budgeting.
It can, however, prove to be intimidating to the uninitiated. So here are five tricks you need to have in your arsenal if you want to navigate the maze that is Excel:
1. VLOOKUP
VLOOKUP—shorthand for vertical lookup—is a function that allows you to establish the relationship between different columns. Depending on the specified value, VLOOKUP finds the value from one column, and then returns the corresponding value from another column.
This function is best used when trying to look for information in a spreadsheet that involves large data. You can also use this when searching for the same type of information within the spreadsheet.
Take note that VLOOKUP is for more intermediate users.
How To Use VLOOKUP
VLOOKUP follows this syntax:
=VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Let’s say you are looking for how much pay is owed to each team member, like so:
The task here is to find (lookup) each employee’s salary based on the available data that you have, which is their employee code.
Step 1: Click the cell where you want to view the value you are looking for. In this case it’s H3.
Step 2: Enter the VLOOKUP function on cell H3. Inside the parenthesis, you’ll include the set of arguments that would complete the function (to be discussed in steps 3 to 6). Arguments are pieces of data you’ll need to have for the VLOOKUP function to work.
Step 3: Enter the first argument. This refers to the data that you already know. For this example, the Employee Code is your lookup value.
Step 4: Enter the second argument, which is the block of values that the function needs to search. This is also known as “table array” or the lookup table.
Step 5: Enter the third argument. This is the column reference that tells the VLOOKUP function where it will find the data it is looking for.
Step 6: Enter the fourth (and final) argument. This is the range lookup, which indicates whether you want an exact match (FALSE) or an approximate match (TRUE).
Step 7: Press Enter. Notice that you’ll see an error message, which is to be expected.
To complete the steps, enter the Employee Code in cell H2. This will then give you the corresponding Employee Salary.
Last thing to take note of is that your columns need to have labels, as the VLOOKUP function will not work without them. You can also use Index Match as an alternative to VLOOKUP.
2. SUMIFS
The SUMIF function would allow you to add all the numbers in a range of cells based on one criteria (e.g. numbers less than 100). The SUMIFs function, on the other hand, functions the same, except you can impose multiple criteria.
This would be especially useful if you are working, say, with an inventory of various products on your spreadsheets.
How To Use SUMIF
Keep this syntax in mind:
=SUMIF(range, criteria, [sum_range])
- Range refers to the range of cells that the criteria will evaluate.
- Criteria is the condition that cells within the range argument should meet. If your range is a column with different numbers, the criteria can be to add all the numbers that are below 10 (e.g. “<10”). Criteria can be text, numerical, date, a logical expression, cell reference, or another function.
- Sum_range is an optional argument. This refers to the cells that you’ll add together. If this is not included, then the cells in the range argument will be summed instead.
Step 1: Type the SUMIF function on the cell of your choice.
Step 2: Click on the range of cells that you want to add (e.g. A1:A5).
Step 3: Enter the criteria that you want the numbers to meet, before the function adds the range of cells you typed in. Note that text criteria or criteria that includes math symbols should be in double quotations.
Step 4: Enter the range to sum if the corresponding cells in your range argument contains the value that you entered in criteria.
In the example below, the SUMIF function adds all the numbers on range B1 to B5 (the sum_range) that has a corresponding cell in A1 to A5 (the range) that contains the value 25 (the criteria). You’ll see the sum on cell B7.
3. Absolute Cell References
Absolute cell reference is used when you want to constantly refer to a particular cell in other areas of your spreadsheet.
This reference will stay the same regardless of where you copy the formula, and is often seen with the $ symbol.
How to Use Absolute Cell References
Step 1: Click on the cell where you want to have your formula.
Step 2: Begin typing the formula by starting with the = sign.
Step 3: Select the cell reference that you want to be the absolute reference, then hit the F4 key once. You’ll see the $ added to both the row and column elements of the cell reference. Hitting the F4 key again switches to either the column or the row element being absolute.
Step 4: Drag the same formula to other cells in the column. The reference will stay the same (unless you have just the row element set as absolute, in which case the column element of the cell reference will change as normal when copied down or up a column).
In the example below, you’ll see width and length (previously in cm on columns B and C) converted to inches (columns E and F), using the absolute reference on cell H3 (the conversion rate).
4. Filters
Filters or Excel Autofilters are an extremely simple way to sort through large data within a spreadsheet.
How to Use Filters
Step 1: Highlight all the cells in your spreadsheet’s header row.
Step 2: Go to the Data tab, then select ‘Filter’ from the Sort and Filter ribbon. You should see dropdown menus on each of your columns (indicated by upside-down triangles).
Step 3: Pick the Filter that you’d like to view any specific data.
Don’t forget to turn off any filter if you want to view your spreadsheet again in its entirety.
5. Conditional Formatting
Conditional formatting allows you to highlight specific data on your spreadsheets. While it may look like it’s only for the aesthetic purposes, it actually makes it easy for your to view various information, especially in the case of large data sets.
How to Use Conditional Formatting
Step 1: Select the cells you want to format, whether it’s a specific column, row, or the entire spreadsheet.
Step 2: Click on the Home tab and locate the Styles ribbon.
Step 3: Click on Conditional Formatting and choose which cells you’d like to highlight.
Step 4: You can also set your own rules for different formatting functions that are not provided for by Excel.
Take Control of Your Numbers
Excel may seem daunting for beginners, but it is an extremely versatile tool that you can use to make your day-to-day tasks easier. This is especially helpful if you’re at the early stages of your business, when you need to wear different hats including that of an accountant.
Start with simpler functions, then work your way up to intermediate and advanced levels. The more you practice, the more you’ll feel confident, and the more you’ll be able to maximise the functionality that this program provides.
About the Author: Nick Brown has been a chartered accountant since 1983 and a partner at Plummer Parsons since 1990, where he is also Head of Charity Audits and Payroll. You can connect with them Twitter, Facebook or LinkedIn.
|