Being a successful accountant requires more than just being good with numbers. One of the most essential skills for any accountant is a mastery of Excel. It is a powerful tool that can save a lot of time and make them more productive, but it is also susceptible to cumulative errors if data is entered manually. Even a tiny mistake can have significant implications when dealing with large amounts of financial data.
However, it takes more than just knowing how to use the basic features of Excel to be genuinely successful. The best accountants master a few essential Excel productivity hacks that help them work faster and more efficiently.
Here are 11 Excel productivity hacks these professionals use and recommend for lazy but smart accountants:
Resize Columns Within Seconds
Can you relate to the frustration when you're putting the data in the column, and the text just breaks through the size of the column, and then you're going back and forth adjusting the size of the text or column? Imagine having hundreds of entries with different data – that can be painful. But what I'm about to tell you will save you a lot of time and frustration. Simply place your mouse cursor between the columns’ lines. You'll see your mouse will become a symbol with two-opposite arrows. As soon as it becomes, double click and the column's size will automatically adjust to the longest query or text. Just like that.
Gerrid Smith, Joy Organics
The Status Bar Function Display Makes Work Seamless
Part of what slows us down when using Excel is having to constantly check on ranges of data in which we have to look up metrics, but you can speed up the process by using the status bar function display hack. Looking up the average of rows and columns is a necessary part of using a spreadsheet, as it provides common data points, but it can also slow down your examination. By setting up the status bar functions display at the bottom of the page, you can look at all the key metrics, including sum, maximums, minimums and averages, to provide you the range quickly and without having to slow down to find this information individually or cell by cell. This simple productivity hack will provide the analysis tool to speed up the workload for any lazy but clever accountants and make your use of the Excel spreadsheet a seamless one.
Matt Miller, Embroker
Make Smart Use of the Flash Fill Feature
Excel is a great tool for smart accountants if they leverage its power. One of the cool features is ‘Flash Fill’. When you’re typing manually, this feature can do the job like magic within seconds. Let’s say you need to type 1000 serial numbers. All you need to do is to type the initials, say 0001 and 0002, in the first two cells, respectively. Then, select those first two boxes, and drag the cursor down — excel will do its magic by automatically writing the next serial all the way where you want to. This fantastic feature was introduced in 2013, and lazy but smart accountants are making great use of it since then.
Lachlan de Crespigny, Revelo
Add or Subtract Within Seconds
For lazy accountants, especially who are new to the Excel world, I’ve something really cool to tell. Since accountants are always dealing with money, adding or subtracting, putting the formula again and again can be really daunting. So, here’s the trick: simply highlight the last cell at the end of the column or row. Then press ALT and + sign simultaneously. Or, press ALT and - sign if you want to subtract. That will instantly add or abstract all the values of the cell or column – within seconds.
Daniel Close, We Buy Houses in Kentucky
Auto-Fill Calculations
Use the Sum function to auto-fill calculations. Whether it be consecutive dates or regular recurring payments, the auto-fill function in Excel can easily take care of the busy work of entering each field manually. Excel allows you to easily drag and copy fields down a line and will anticipate regular date changes in a column. Excel automation can save you a lot of legwork.
Stephen Skeel, 7 Wonders
Use Ctrl+Arrow Keys to Track Down Data
When you have huge data files with thousands of series, it can be so overwhelming to track. I'm going to give you a hack if you're too lazy to scroll up and down or left and right. Simply press Ctrl+Arrow Keys, and you'll instantly jump to the last cell of the column or row. But you should know that if there's any gap in between your column or row, this shortcut will jump you to the first gap in the sheet. Regardless of that, it can be so time-saving and efficient when you're dealing with piles of data that constantly need to be checked back and forth.
Joe Troyer, ReviewGrower
Apply the Concatenate Function to Merge Two or More Arrays
It comes as no surprise, but many great (and tenured) accountants fail miserably when it comes to excel efficiency. One simple and effective tip for accountants is to use the CONCATENATE function when merging two or more arrays. Example Cells: A1 = 123 A2 = Sunshine St. A3 = AZ A4 = 85054 A5 =CONCAT(A1," ",A2,", ",A3," ",A4) = 123 Sunshine St., AZ 85054 This is a simple yet great function to use when joining multiple series of data into a single cell. Make sure to insert quotations around segments of the data where you need a comma or space between your joined data.
Roman Villard, Full Send Finance
Easily Format Numbers With Shift + Ctrl + $
Let’s assume you have a column full of digits that all represent the same thing, such as an amount in dollars. If there’s no dollar sign in front of each number and you would like to add one you don’t have to edit each one separately. Simply select the column containing the digits you want to re-format and then use the keyboard shortcut above to format the entire column to dollars — your entire column will be displayed with the dollar sign, any necessary commas, and two points after the decimal point with that simple trick.
Guy Sharp, Andorra Guides
Scenarios Analysis
Excel also offers a tool that allows you to study hypothetical scenarios using three different options: goal Seek, data Tables, and Scenario Manager. The Scenario Manager is the most commonly used and updated of the three alternatives. It allows the user to enter variables with varied values depending on the situation. Users can assign numbers to situations and compare them to one another, as well as refer to the changing cells between systems. The Goal Seek option gives a circumstance until it fulfils the predefined condition. It is an excellent approach to shorten the time it takes to find a specific cell value in order to proceed with a fresh set of data. The Data Table enables the user to depict multiple scenarios at once. It aids in increasing productivity when a variety of conditions must be compared. Aside from that, Excel is a fantastic tool for financial analysis of business development, particularly for those who prefer autonomous contracts with clients.
Nely Mihaylova, UNAGI Scooters
Sort Data by Color
Data sorting is not restricted to sorting by cell values. Data can also be sorted according to cell and font color. Select the data to be sorted first. Select Sort & Filter from the Editing group on the Home ribbon bar, then Custom Sort. If headers were included in your option, make sure My data has headers checked. Select the column to sort from the Sort by drop-down list. Select Cell Color or Font Color from the Sort On drop-down menu. Select the color you wish to see first from the Order drop-down menu. Then, at the upper left of the Sort box, click on Add Level. Repeat the preceding procedures for the second color that should be displayed, and so on until you have informed Excel on the order in which to arrange all cell colors or font colors.
Tiffany Payne, iFlooded Restoration
Quickly Delete Empty Cells
For many reasons, certain default data will be blank. The quickest method is to filter out all blank cells and delete them with a single click if you need to remove them to ensure accuracy, particularly when computing the average value. Go to Data->Filter, choose the desired column, undo Select All when the downward button appears, and then select Blanks as the final option. The quick display of all empty cells. Return to the Home page and select Delete; they will all be deleted.
Hamza Usmani, BuyWeGovyOnline