If you’re an accountant, chances are you are in Microsoft Excel quite a bit. I’m a bit of an excel nerd myself (that’s probably a huge understatement). I find it to be extremely flexible and easy to work with, so I use excel in lots of different scenarios. Here are a few of the best excel tricks for accountants and bookkeepers that I find valuable in my every-day use of excel.
CTRL + Page Up/Down
This is a great navigation trick. If you are working in a file with multiple tabs and want to switch to the next one, don’t reach for your mouse – just hold down CTRL and hit Page Up to tab left or Page Down to tab right. You will automatically be transported to the next tab over, saving you some time.
CTRL + Up/Down/Left/Right
Another navigation trick that will help you move around the page quicker. If you want to get to the bottom of the column of data, but you are at the top, instead of holding down the down button or even hitting page down, you can hit CTRL + down arrow to move to the end of the data grid. You can also hit the arrows in any other direction. It basically takes you from where you are to either the end of the data (if there is data between the cell you are in and the next blank cell) or takes you to the next cell with data (if there is no data in the direction you’re going). Note: if there are breaks in the data (within a column or row), this trick will only take you to the break in data – it will stop at the last cell with data in that direction. A little hard to explain in text, so just fool around with it and you’ll get it.
Customize the Quick Access Toolbar (and bring it with you)
The green bar in the image above is called the Quick Access Toolbar (“QAT”). It is the top ribbon in your excel program. You can completely customize this! Try to put the things that you use regularly up here, so you don’t have to click into the various tabs on the ribbon menu to hunt for them. I use the items in my QAT regularly and at this point, I would be pretty lost without them in the QAT – I don’t even know where to find some of them. To customize this, right click in the bar and click “Customize Quick Access Toolbar” then add the commands that you want and reorder them to your liking.
Pro-Tip: Order some of the commands you use regularly on the far left. If you hit “Alt” while you are working in a workbook, you will see numbers/letters next to the commands in the QAT like this:
You can then hit the numbers for the action, so you don’t need to touch your mouse. So in my case, if I want to use the Sum function, I can hit “Alt” (then wait a half second while the numbers appear) then 9 and it saves me from reaching for my mouse. (Note: my Sum function is the 9th item in my QAT).
You can also customize the QAT in other MS products like Word, Outlook, PowerPoint, etc.
One more thing and then I promise I’ll stop talking about the QAT. You can bring it with you when you get a new computer. I loathed switching computers because I’d have to set up everything again, but then I found that you can download your excel settings and import them into your new machine. When you go to Customize the QAT, there is an Import / Export button at the bottom. Export the settings from an old computer and import them into your new computer.
I admit, this is more of a “long-term” strategy if you use the same files over and over again like I do (updating a file each month with new data for example). Setting up a structure to your files will make updating them more efficient and intuitive. I like to use font colors to identify what the cell is for. I do this consistently across all my excel files, so when I open a file that I haven’t used in a while, I immediately know (for the most part) what each cell is for. For example, I use blue font for input cells. Basically anything that I key in is usually in blue. It could be an assumption or possibly a date field that is used in my report somehow. I use black font for “standard” formulas where I know the formula is the same across a group of cells. For example, if I copied a formula down from the top of a column to the bottom without making any changes to the formula, then I would leave it in black font. If there is a change in the formula or if I consider something to be a “special” formula, then it gets red font. It tells me there is something to watch out for in that cell and I can’t just copy the formulas around it because this one is different. You can also use cell shading (“Fill color” in excel speak) to signify other things. For me, when things are open or not yet updated, I highlight them in yellow fill, and then remove the yellow once the item is updated (Yellow fill and blue font tells me there is an input that needs updating). Those are my standard colors, but your color palette can be anything you want.
Try these tricks on your next excel file – I hope you find them helpful. If you are looking for other ways to automate your processes and take your team to the next level, try AP genie. Our automated system will save your team time and money, while improving controls and efficiencies within your whole company. We have a 30-day no-risk free trial – sign up here.