Whether you work for a public accounting firm or are an accountant at a company, Microsoft Excel is likely an essential part of the work you perform. While I worked in public accounting, I used excel on a daily basis for numerous work assignments. Over that time, I picked up a few useful excel tricks and best practices that made my life a lot easier.
The following is a listing of tips and tricks that will help you as you work through your workpapers and spreadsheets.
- Absolute References
Absolute references prove very helpful for accountants who are copying formulas to many rows or columns. You may wish that your formula always references a specific cell or a specific row of data. This is where absolute references come in handy. Use the $ sign in your formulas to create an absolute reference.
Examples of absolute references:
- $H$1 = Locks in the column and row. The formula will only use the data found in this cell.
- An example of when you may want to use data found in only one cell could be when you are using an exact date or percentage in your formula.
- $H1 = Locks in the column. As you pull your formula down or across, the formula will always pull the data from column H for the corresponding row.
- H$1 = Locks in the row. As you pull your formula down or across, the formula will always pull the data from row 1 for the corresponding column.
Absolute references are a good idea to use even if you are not copying your formula down or across your spreadsheet. At times, you may need to copy your formula into another cell or you may add rows of columns to your spreadsheet. If you used absolute references in your formula, you do not have to worry about the solution to the formula changing.
- Copying Visible Cells Only
This is the most useful Excel trick I learned as an accountant and it saved me many headaches while working. I am also very surprised at how few people know about this shortcut.
So often during our work, we filter our spreadsheets to show only certain data we care about at the time. Often, I wanted to copy this data to another sheet. So I would highlight all the data I could see and hit copy. I would paste the item in the other sheet only to have Excel copy over the data that I thought I had filtered out in addition to the data I wanted. Ugh!
In order to fix this problem, complete the following steps when copying over filtered data.
- Highlight the data you want to copy.
- Hit F5.
- Click the “Special” button in the lower left hand corner.
- Click on the “Visible Cells Only” Circle box.
- Hit OK.
- Press Ctrl “C” or Edit Copy.
- Paste data into the appropriate sheet.
- Only the visible data should have been copied over.
- Pasting Formats – Paste Special Shortcut
If you have the opportunity to work as an auditor, you will create workpapers that are organized and formatted in a manner that is presentable to your superiors. To help with this, I formatted cells, rows and columns with specific font styles, colors, bordering, etc. Frequently, I wanted to apply the same formatting to numerous cells. This is where the Paste Special Shortcut comes in handy.
Instead of hitting the formatting buttons in the menu bar over and over again, follow the below.
- Copy the cell(s) that has the formatting you desire.
- Highlight the cell(s) you want formatted the same.
- Right Click on the cell(s) and click on “Paste Special”
- Click “Formats” under the Paste menu
- Hit OK
- The format will carry over.
This pasting function is especially useful when you want to copy over an entire column or row’s formatting to another column or row.
- Pivot Tables
Pivot tables are a tool that I did not learn how to use until after my second year in public accounting but one I wished I had known from the very beginning.
After learning how to use them, I was able to implement them into my workpapers to organize data for easier use and analysis.
Further, my superiors were impressed by my ability to use them, which is a great plus!
As such, I think it is quite useful for every accountant to take the time to learn pivot tables so that you are armed with one of the most useful tools Excel offers.
A good starting point for learning Pivot Tables is found in the following tutorial on Microsoft’s website.
Tutorials are great, but on-the-job learning is the best way to learn. Sit down with a colleague and ask them to show you the basics of Pivot Tables and then implement them on a data set you are currently working with.
Like it or not, most of your time as an accountant is spent in Excel. As such, it is best to learn as many excel tricks as you can in order to make your life easier. Hopefully the above list I have provided includes some new tricks you can implement in your work going forward. If you have some of your own favorite Excel tips, I invite you to leave a comment below for others to reference.