Low-Tech Excel Tips for Accounting
Admit it. Most of what you know about using Excel is either self-taught or learned by watching someone else do something really cool. It’s true for me, even though I’ve been using it forever.
I’m no Excel expert – and I’ll never be one! I don’t have the time to devote to it and, frankly, my talents are best suited elsewhere. I created a macro once or twice and I was done, so learning an esoteric function that I’ll only use once won’t be retained. Instead, I gravitate toward easy-to-learn-and-use-over-and-over tips and tricks.
Here are a few gems that fall into that category, and which are of particular use to accounting professionals. For reference, I’m using Excel 2010.
You know the challenges inherent in using the normal Windows Copy and Paste functions in Excel. First, you can retain only one thing, and the next time you copy, the first copied item is overwritten by the new one. Second, Excel has this annoying short-term memory problem: if you do something else after copying something, say, entering a number in another cell, Excel forgets what you copied.
The Office Clipboard comes to the rescue. Click on the diagonally-downward arrow next to the word Clipboard in the left section of the Home tab to reveal the Office Clipboard. Clicking on it again will hide the Clipboard.
Items can be copied normally or copied as pictures. In the screen shot to the left, you can see that the Office Clipboard has retained two items I’ve copied: the number 12345 and the number 987987. However, I’ve copied the number 987987 twice: once as a straight copy of the number and once as a picture of the number.
I can put my mouse cursor in any destination cell and then choose the drop-down menu next to the item I want to retrieve in the Office Clipboard and select Paste. I can also select Delete if I want it to disappear from the Office Clipboard.
The Office Clipboard is pretty powerful: I was copying the screen shots above using SnagIt, as well as certain text strings in editing this article, and the copies from SnagIt and Word appeared, along with a small icon indicating the source programs, in the Office Clipboard!
Any items I’ve copied in Excel using the regular Windows Copy function will appear in the Office Clipboard as well.
Since I’m focusing on Excel, you should know what the Office Clipboard can’t do for Excel users. Unlike the straight Copy function, which copies a formula (unless I choose Copy > Paste Special > Values), the Office Clipboard can retain only the result or value of the formula. So, if the result of a sum function is 18, pasting that copied item using the Office Clipboard will result in 18 being copied, not the sum function that would usually result in a different figure when pasted to another location.
In addition, the Office Clipboard does not have any of the Paste Special functions of the regular Windows Copy function. Nevertheless, the Office Clipboard is a great easy-to-use functional addition in your Excel arsenal and particularly useful when you have multiple spreadsheets and worksheets open.
If you’d like to know where the Office Clipboard is lurking in other Office programs for 2010 and other version years, click here.
I mentioned the Paste Special function above. I know a lot of you use it to paste the result of a formula, rather than the formula itself, by using Right Click > Copy (yes, the regular Copy function, not the Office Clipboard) and then choosing Right Click > Paste Special > Values. That same menu can take a matrix and paste it on its side, flipping the rows and columns around. After you’ve copied a grid using Right Click > Copy, click on your destination cell and use Right Click > Paste Special > Transpose and then click OK.
Here’s a bonus … the number 18 in the screen shot above was a formula; the sum of the cells containing the numbers 5, 6 and 7 in the cells are to the left of it. The transposed matrix contains the number 18, but it’s really a functioning formula with the sum of the cells containing the number 5, 6 and 7 in the cells above it. The special paste retained the formula.
If you like the Transpose feature, but want the transposed matrix to change if the contents of the original one does, this is doable as a function, but more advanced than I want to get into here. If you’re interested in the Live Transpose feature, click here for a good primer on how to do it.
Delete Blank Rows
This happens to me a lot. I’ve exported a report from QuickBooks® into Excel and the spreadsheet has a bunch of blank rows I can do without. I can manually delete each blank row or I can perform this neat trick:
Select a column from the area you want to clean up and select F5 > Special > Blanks and click OK. Then click the drop-down box next to Delete in the Home tab of Excel and choose Delete Sheet Rows.
The blank rows are now gone:
Type the cell address or cell name if you have named a cell, to which you want to jump in the Name Box – the field in the top left corner of Excel that identifies the cell address where you currently are – and the cursor will take you to that cell. Bam!
In the screen shot below, I highlighted cell F7500 and then typed the name EsthersTotal in the Name Box and then hit Enter. I have now assigned the name EsthersTotal to cell F7500.
Now, if I’m really far away from that F7500 (say at cell A1), then instead of scrolling manually to the F7500, I can type either F7500 or EsthersTotal into the Name Box and hit Enter. That will take me to F7500 instantly. My desired cell destination doesn’t have to have a name; I can enter the cell address in the Name Box if I remember what it is.
However, naming cells is a good idea; you’re more likely to remember what you named something rather than its cell address, especially if the address changed because you deleted or inserted cells around it.
If you’re having trouble with a particular formula, you can click on the cell where the formula resides and review it in the Formula Bar. However, that might be tedious if you have to review several formulas and you don’t want to migrate away from your current position.
Select CTRL+` (that’s the CTRL key in conjunction with the key to the left of the 1 at the top of your keyboard, just south of the Esc key) to toggle between displaying formulas and displaying their values in each cell.
The CTRL key is very powerful in Excel. It opens the door to a treasure trove of keyboard shortcuts. I encourage you to review these and other Excel shortcuts and use the ones that are screaming out to you to be adopted by clicking here.
You’ll use the shortcuts that are meant for you, and you’ll quickly forget the rest.
So we don’t have a black belt in spreadsheets, but we do know some cool Excel tips for the rest of us.