Spending more time than necessary in an Excel spreadsheet is the bane of an accountant’s existence. Often times when working on a spreadsheet we spend more time playing around with formatting and attempting to get the cells to do what we want than we actually spend manually inputting data. If you’re in the accounts receivable department, there are probably a thousand different tasks that would be more productive, such as making collection calls, than playing around in a spreadsheet. Below we have highlighted 5 useful Excel shortcuts for accountants to make formatting and navigation easier.
SELECT ENTIRE TABLE
If you want to select certain rows and columns including data, but not the surrounding empty cells, use this handy trick.
NAVIGATE THROUGH WORKSHEETS AND WORKBOOKS
There is nothing more frustrating than trying to move data from one worksheet or workbook to another and having to manually click back and forth. This wastes tons of extra time and, if you have multiple sheets or books open, runs the risk of inputting data in the wrong one. If you can easily toggle back and forth, you have an easier time editing.
Note: A workbook is the entire Excel spreadsheet and worksheets are the sub-pages that exist within them.
To move to the next worksheet:
To move back to a previous worksheet:
To toggle between workbooks:
COPY AND EDIT FORMULAS
Even trying to copy a formula from one cell and edit it in another can prove to be a difficult task in Excel. Sometimes editing a copied formula take multiple attempts and the cells can just be stubborn, then you are left wondering if the correct formula is even being used, compromising your data. In order to do this easily, use these keystrokes.
Pressing the control key and the apostrophe key will paste the formula in the above cell, below and immediately enter edit mode.
FORMATTING NUMBERS
Sometimes you want to enter a number into an excel worksheet as a specific format, such as with a certain number of decimal places, as a date or a time. Excel usually does not automatically recognize this formatting and you have to manually highlight the cells and change the formatting using tools. These shortcuts will save time from formatting numbers.
Formatting with two decimal places
Format as Time
Format as Date
Format as Currency Value
Format as Percentage
Format in Exponential Form
DETECT ALL CELLS LINKED TO A FORMULA
If someone sends you a report in the form of a spreadsheet, it’s important to make sure you know how the data was discovered and calculated. Especially if the formulas are complicated and rely on data from other cells, you need to quickly find out where the end results came from. The following shortcut will allow you to see all the cells that are linked to a formula and then you can drill down to see exactly what that formula is.
First, highlight all the cells that have a value and then use:
