As a tried and trusted resource for financial executives that can be seen on computer screens in virtually every corporate office, becoming an Excel super-user is a great career move for any accountant. The spreadsheet is always expanding and reams of text have been devoted to excelling with Excel. The number of shortcuts and ways to increase your savvy are expanding all the time and there is a big difference between intermediate and advanced knowledge of the program. Here are five more Excel tips for accountants.
SUBTOTAL over SUM
SUM should not be the go-to function for adding up values on report because it requires the user to pick and choose for each cell and is inefficient when dealing with recurring workbooks, leading to errors and wasted time. Rather use the SUBTOTAL function, which allows you to apply the mathematics of your choice to the entire range at one.
Table your concerns
Easy-to-format tables offer significant advantages over ordinary data ranges. They allow the user to maintain the header row for easy viewing and use filter controls to sort data. They also automatically expand when adding new data under the table. Formulas will automatically include any new transactions you paste-append to the table.
Formulate a data plan
Instead if tinkering around with manual steps in recurring workbooks, take a step back and work on a formula that allows you to easily and efficiently manipulate the data in the format in which it has been sent.
Add to your trove of shortcuts
You can never have enough shortcuts in Excel. You can add to your list by press the ALT key at any time to reveal Key Tips and access the keyboard shortcuts for the menu system. The program is designed to be used with shortcuts and there are key presses for virtually every action.
Think of others
Make your models friendly to other users by protecting or restricting the values that can be placed in cells and using drop-down boxes to get values from users. Hyperlinks can help guide them and using dates and filenames in default headers and footers will help anyone keep track of the source of the document.
