Excel is a powerful tool for collecting and manipulating data. In the accounting world, spreadsheets are an everyday tool used to analyze financial data, review exports of inventory, or search for trends of accounts payable invoices.
Whether you are a CPA, doing accounting for your business, managing your personal finances, or just keeping track of simple data, the more you know about using Excel, the easier your tasks will seem. Here are eight specific hacks users of Excel can master and use to their benefit.
1. Pull Data from Other Cells with Absolute and Relative References
One of the cornerstones of Excel is being able to reference and pull data from another cell. A simple reference is just pointing to the cell location. “=A3” is a cell reference: Whatever cell you put that in will pull the contents of cell A3.
For example, take a spreadsheet that multiplies the daily number of sales times a sales price in cell B1.
Each part of the formula (=B4*B1) is called a relative reference. That means each part moves as you copy the formula. If you copy this formula down to the other dates, you see an error message and some unexpected results.
This is because the formulas were relative reference. When you copy the formula down, it becomes =B5*B2 in the next cell. The first part does need to change to B5, since that is the current day sales, but you do not want the last part to change: It needs to refer to cell B1, the sales price.
This is when you use an absolute reference. To do that, put dollar signs in front of the “B” and the “1”. Now the original formula in cell C4 should be =B4*$B$1
(A quick way to do this is put your cursor between the “B” and the “1” and press F4.)
With the absolute reference in place, you can copy the formula down and get the result you need
2. Combine Cells with an Ampersand (&)
Sometimes you will get spreadsheets that have cells with pieces of information in them, but you need them to be combined into a larger cell. For example, maybe you have a list of names, with a column for first name, another column for middle initial, and a third column for last name.
But you may need them combined, so you can have the full name in one cell. This is easy with the “&” symbol. Make a formula showing each part you want in the new cell and put the “&” in between each. One thing to note is that when a spreadsheet combines the cells, if there are no spaces after each word, you need to add them to the formula. Use quotation marks with a space between them in your formula to add in those spaces: =A2&” “&B2&” “&C2
It takes the first name, which is cell A2, then puts a space, then the middle initial from B2, then another space, and finally C2, the last name. The “&” sign just combines everything.
3. Highlight Cells with Conditional formatting
Excel allows you to make cells a certain color based on the contents of the cell. This can be extremely helpful when looking at a large spreadsheet: It helps you see these numbers faster than searching for them.
Take the example of a personal budget spreadsheet, where you have several household expenses with projected and actual cost.
When looking at a budget, many times you want to see specific numbers, such as when your actual costs are too high or greater than projected. Conditional formatting lets you do that.
For example, if you would like to highlight all the items in the “Difference” column where actual costs exceed projected, highlight column E, and then go to: Home>Conditional Formatting>Highlight Cell Rules>Less Than.
In this menu you can choose what number to highlight: you can put “0” so it will highlight all negative numbers. The drop down on the right lets you choose the color you want use to highlight the cells.
Click “OK,” and you get a conditionally formatted budget.
Now you can quickly see where your expenses are more than expected.
4. Use VLOOKUP Function to Find Specific Data
One of the most powerful features in Excel is the VLOOKUP function. This function looks in a large table of data for a value you supply. When it finds it, Excel scrolls to the right the number of columns you tell it to and then brings back that value.
Revisiting the Budget example, there’s a row for Income. We want to enter each month’s income; however, income is in a separate table over to the right in the spreadsheet.
In the main table, in cell B4, you want to pull in June’s income. Instead of manually looking in the income table for June and copying it over, you can use the VLOOKUP function to do it.
Insert the VLOOKUP function into cell B4. Through the wizard you get these fields:
- Lookup value – What you are looking up. In this case it is B3, which is “June.”
- Table array – The table you are looking in (N:O). The leftmost column must contain the data you are looking up, which is “June.”
- Col Index num – The number of the column you want to pull back. 1 would return column N. 2 returns column O.
- Range Lookup – False means you want an exact match. True means it pulls the next closest if it doesn’t find a match
After all that, you get your result.
The VLOOKUP pulled 4000 for the income table, which was June’s income. To learn more about VLOOKUP and see a full tutorial, click here.
5. Use Copy – Paste Special to Paste Values, not Formulas
Performing a Copy-Paste is one of the most basic Excel functions any user will do—it’s just moving a cell to another spot. When you copy and paste a formula, however, you move that formula over, along with any cell references. Copy-Paste Special allows you to copy the actual results, not the formulas. This can be necessary when emailing out spreadsheets or when the cell references other files that aren’t always available.
In a budget spreadsheet, categorized by month, cell B4 refers to cell C5 in another worksheet called “Conditional Formatting.” This works fine until you need to send out this monthly sheet, as it would still have the reference to another worksheet. The other worksheet won’t be available to other viewers, so this formula wouldn’t be able to pull anything.
Get around this problem using Copy-Paste Special. Highlight all the cells that would be affected. In this example, it is all of column B. Right click, go to “Copy,” and then right click again to go to “Paste Special.”
In the Paste Special pop-up window, choose Values so Excel will replace the formula with the actual values. Now the cell has the final result instead of the formula.
6. Reduce Frustrating Errors with IFERROR Function
Error messages like #N/A, #REF, and #DIV/0! are all common and make spreadsheets ugly. They also keep you from doing future calculations on these cells. To get rid of errors, use IFERROR.
For example, a grocer has a spreadsheet with a formula to divide column B by column C.
Some entries in column C are “0,” which is causing some #DIV/0! errors. Get rid of this by inserting the IFERROR function in front of your formula. So instead of =B4/C4, you would have:
This tells Excel to perform the first part, which is B4/C4. Then, if there are errors, it will display the second part of this function, which is “0.” You can make that part whatever you want.
Now, after inserting the IFERROR function, you get a value instead of an error message.
7. Control Decimals with the FIXED Function
The FIXED function is used to limit the number of decimal places in a number. When you have a function that multiplies or divides two numbers, and you may get a result like 42.33333333. That doesn’t look good and may not work correctly in future calculations.
Use the FIXED function to limit this number to the exact number of decimals you need.
Consider this example of someone’s payroll. If you look in columns F and G, their Taxes and Net Wages have 3 and 4 decimal places. They need to have 2 decimals so they can reflect a real dollar amount.
You could format them, but the real number would still have 3 or 4 decimals. To make them 2 decimals, use the fixed function in front of the formula in the cell. In cell F4, you would make the formula =FIXED(E4*D4,2)
This limits the result from the formula E4*D4 to 2 decimal places. Now, after adding that FIXED function to all of columns F and G, the resulting value is a true dollar amount.
8. Count Values Using the COUNTIF Function
The COUNTIF Function will count how many items reach a certain criteria you set. This can be great when you need to keep track of certain results or scores. Take a spreadsheet counting bowling scores.
In this example there are 7 weeks of bowling scores, with 3 games each day. The average score is 180. What if you needed to find out how many games went over 200? Or how many games were under 150?
The COUNTIF Function will do that for you. Go to cell D13 and enter in this formula: =COUNTIF(B4:D10,”>200″)
What this formula does is looks in the range of B4 to D10, which are all the bowling scores, and then looks for the criteria of >200. It is going to count all the games that have a score greater than 200.
We can also do this for under 150 with =COUNTIF(B4:D10,”<150″)
The COUNTIF function, along with the preceding seven Excel tips and formulas, will take any novice user into more advanced usage of Excel. Using them will make your spreadsheets more efficient, easier to use, and more user friendly.
For the accountants out there, these tips will save you time everyday when you apply them to your daily Excel spreadsheets. No longer will your projects look like an amateur effort. You can apply these techniques to your financial trending spreadsheets, balance sheet analysis, or any other accounting project. These will make your time in Excel more productive!