Best Excel Tips for Accountants

4 Excel Tricks That Will Save You From Hours of Headache
11 Comments

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:

  1. $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.
  2. $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.
  3. 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.

  1. Highlight the data you want to copy.
  2. Hit F5.
  3. Click the “Special” button in the lower left hand corner.
  4. Click on the “Visible Cells Only” Circle box.
  5. Hit OK.
  6. Press Ctrl “C” or Edit Copy.
  7. Paste data into the appropriate sheet.
  8. 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.

  1. Copy the cell(s) that has the formatting you desire.
  2. Highlight the cell(s) you want formatted the same.
  3. Right Click on the cell(s) and click on “Paste Special”
  4. Click “Formats” under the Paste menu
  5. Hit OK
  6. 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.

Russell

About Russell


Hi, I'm Russell, author of The Big 4 Playbook and a Certified Public Accountant (CPA) in Texas.

Before writing The Big 4 Playbook, I had the privilege of working as an auditor and tax consultant for several years for a Big 4 Accounting Firm. I am one of the few CPAs to serve as both an audit senior and a tax senior for a Big 4 Firm. During my time at the Big 4, the clients I served included Fortune 500 and Fortune 1000 companies, government organizations, hedge funds, and more.

I earned my Bachelor of Science in Accounting from Boston College and my Masters in Accounting from Vanderbilt University. I experienced the rigors of the Big 4 recruiting process at both levels of my college career, and I created The Big 4 Playbook to share the lessons I learned along the way with you.
Best Excel Tips for Accountants

4 Excel Tricks That Will Save You From Hours of Headache

11 Comments

  1. Pingback: 4 Excel Tricks and Tips for Accountants - CourseIndex

  2. Elena

    Thanks Russell.

    Reply

  3. Charles Li

    I think formula auditing is a good excel trick to learn.

    Reply

  4. Shalonda

    Thanks so much for sharing!

    Reply

  5. Jimmy

    Very helpful to new accountants! Thanks!

    Reply

  6. Wayne Galloway

    One of my favorites is the old “Insert-Picture-Scan” keboard shortcut that still works, at least in 2007 Excel & Word. Just hold down “ALT” and press IPS & the Insert Picture or Scanner dialogue box comes up – select Custom to pick paper size, text, color, etc. Easiest way to scan in a document! Remember Doug from King of Queens worked at “IPS”, the “ALTernate” of UPS! haha!

    Reply

  7. John Hardin

    Great tips. All very useful and practical. I would like to add my favorite – the “Fill Handle”. Plac ing your cursor the lower-right side of a cell will cause the cursor to change its appearance. If you then press and hold the left mouse button, you can copy that cell down or across any number of cells. Then release the left mouse button when you reach your stopping point. Depending upon the contents of the original cell, the succeeding cells will be incremented in some way. (ex- Jan, Feb, Mar… or Mon, Tue, Wed…, ect) If there is a formula in the cell, it will be copied referentially. Also, if you highlight two adjacent cells and then use the Fill Handle, Excel will try to make some decisions on how to increment the math in the succeeding cells. The Fill Handle also copies formatting from the original cell. I use the Fill Handle constantly, especially when setting up a spreadsheet.

    Reply

  8. Jane

    There’s a shortcut tool for copying visible tools only. It’s called Select Visible Cells and can be found under the Customize Quick Access Toolbar, More Commands, Commands Not in the Ribbon. Highlight the data you want to copy, click the tool, press Ctlr C or Edit Copy, and then paste into the target area.

    Reply

  9. Bruce Skinner

    Russell, one of the most useful Excel items I’ve used consistently is the VLOOKUP function. If you take the time (and it’s not much) to learn this database function, you’ll be amazed how easy it is to work with and extract data from databases.

    Reply

  10. Pingback: Here are 7 ways to appear more intelligent than other people - One Smart Place

  11. Mucahit

    As a new accountant at a firm, I found these tips useful.
    Thank you Russell.

    Reply

Leave a Reply