Accounting
5 Excel Shortcuts Every Accountant Needs to Know
One of my earliest memories from my first job in public accounting was having to analyze data in an enormous, multi-tab Excel workbook before the higher-ups submitted it for partner review. I spent the entire day meticulously combing through each worksheet, making sure numbers tied out, and adding in checks wherever possible.
When I handed the file off to the senior associate, I watched as they flew through the formulas and tabs without lifting a finger off the keyboard like they were some kind of hacker. After signing off in minutes on my hours of work, I asked how they were able to work so quickly while also maintaining accuracy.
They told me, “Excel shortcuts, learn them.”
By spending just a few minutes familiarizing yourself with a few handy Excel shortcuts, you can save valuable time on your monthly close process or budget-to-actual variance analytics. Excel shortcuts will help keep your hands on the keyboard and away from the mouse, saving you from having to search through various menus and toolbars. After all, Excel was designed for efficiency — and while there are hundreds of formulas and shortcuts, there are a few really good ones every accountant needs to know. Here are a few Excel shortcuts I use in my day-to-day at Stack Overflow.
Switching Tabs (Ctrl + Page Up/Page Down)
Easily maneuvering between tabs in Excel is a must-have skill for accountants. This simple trick alone has saved me hours since my days in public accounting. Every good reconciliation will have a summary tab and multiple support tabs. Every financial package will have a tab for each financial statement, as well as multiple tabs for analytics.
Sure, navigating an Excel workbook with your mouse takes only a few seconds, but that extra time starts to add up when working through a large file. Instead, keep your hands on your keyboard and your brain focused by using this shortcut to quickly move between each worksheet. Before you know it, you’ll be flying through tabs in no time.
Create a Pivot Table (Alt + N, V)
When it comes to analyzing large data sets, pivot tables are your best friend. I can’t remember how many times I’ve used them to quickly sort, organize, and present useful dashboards. Unlike ordinary tables, pivot tables give you the flexibility to sort, filter, and refresh your data all in the same table.
Rather than scouring the menus to find the Insert Pivot Table function, highlight your data and use this shortcut to create a Pivot Table on the spot. Not only will you impress your coworkers with your ridiculous skills, but you’ll also be able to create tables on the go to optimize your time spent analyzing data.
Sum it up! (Alt + =)
The AutoSum function is one of the Excel shortcuts that should be in every accountant’s daily workflow. AutoSum is notated as Sigma (or Ʃ) and allows you to quickly add up a range of cells.
While the AutoSum button is easily found within the Formulas toolbar, nothing beats a good old keyboard shortcut. This one will automatically find the cells you are trying to sum and do the heavy lifting for you. You can even use it across horizontal rows by simply highlighting the cells first before applying the shortcut.
Summon Shortcut Menu (Alt + A)
Excel’s Data toolbar is arguably some of the most popular real estate on your worksheet. It’s home to some of my favorite functions, such as Sort, Filter, Remove Duplicates, Text to Columns, Group and Ungroup.
But did you know that you can summon all of these functions using a few simple Excel shortcuts? By simply pressing two keys (Alt + A) simultaneously — regardless of where you are in your worksheet, this shortcut will bring you to the Data toolbar and highlight all possible commands along with their corresponding letters for the Alt + A sequence. While trying to remember each sequence is a task fit only for a computer, here are some I use pretty often:
Alt + A + __ =
SS = Sort
T = Filter
C = Clear your filter
M = Remove Duplicates
G, U = Group, Ungroup
E = Text to columns
Committing a few of your favorite, frequently used Excel shortcuts to memory will save you a ton of time in the long run.
Moving on From VLOOKUP!
VLOOKUP is one of Excel’s most well-known formulas, but it turns out there may be a better option for grabbing data from a secondary source. As a recovering VLOOKUP fanatic, I had a hard time accepting that one of my favorite Excel shortcuts may not be as powerful as I had originally thought. What eventually helped me to see the light was my co-worker bugging me for months to upgrade to INDEX MATCH.
INDEX MATCH is actually two independent Excel functions. INDEX will return the value in a specified table based upon defined coordinates — similar to the game Battleship — and MATCH provides those coordinates by returning the relative location of a specific value.
Why is this important? It allows the user to search a table for data if the location is variable without updating the formula itself.
If that didn’t sell you on INDEX MATCH, here are a few other ways it’s far superior to VLOOKUP:
- Faster calculation times for large data sets
- No more need to count relative columns
- No more rearranging columns for the formula to work
The next time your Excel workbook freezes during a VLOOKUP, you’ll be wishing that you switched over to INDEX MATCH sooner.
Bonus Tip: Quick Access Toolbar
Excel’s Quick Access Toolbar (QAT) is painfully underutilized. With this feature, you can add any Excel command to the top-left of your workbook (to the right of the Save button). Then, all you need to do is hold Alt + the QAT button number to execute the command.
To add a command to the QAT, either right click the icon and select “Add to Quick Access Toolbar” or click the dropdown arrow on the top of your workbook and select the command from there.
My favorites include Save As, PivotTable, Sort, and Print Preview. Your favorites may differ, but whatever they may be, just plop them within the QAT to customize the tool to your specific needs.
In today’s fast-moving environment, being proficient in Excel is an absolute must. Remember, there are no shortcuts to success—but there are shortcuts to Excel!
