Excel Tips for Tax Season

12/07/2017

By Val Steed

“When are CPAs going to give up their spreadsheets and use professionally designed software like Great Plains?” Doug Burgum, Governor of North Dakota and former President/CEO of Great Plains Software caught me off guard with this question over dinner a number of years ago.

“CPAs will give up spreadsheets when they die,” I responded. A statement that I believe is just as accurate today as it was back then.

Electronic spreadsheets are our answer to all the calculations that are not already in GL/AP/AR, etc. Spreadsheets facilitate data cleanup, organize and sort, as well as calculate and sum. Our needs for these functions have not changed and so our need for Excel’s time-saving and improved calculation capabilities remains.

In light of this, there are two features in Excel that can significantly help you this tax season. One old and one new, but both are very powerful and most accountants do not know about either.

Tip #1 – Flash Fill (Excel 2013, Excel 2016)

New in Excel 2013, Flash Fill is one of the greatest gifts accountants have ever received. Flash Fill is not an add-in. It‘s not something you need to turn on. Flash Fill is a feature that’s simply there, ready to help you clean up data and it in all versions of Excel 2013 and Excel 2016.

Let assume that you wanted a list with each element in a column—first name, last name, and a phone number someone can use to make calls. But instead, what you got is a last name and a first name jammed into the same column while only the numbers of a phone number appear down the next column. This can be very visually challenging to use for calls.

excel

Now, you will see that it populates the column with the exact information you want. While you can do some complex text functions to parse the information, you don’t need to do that anymore.

As you start to type the same solution in the second cell down the column, Excel will pop up an Auto Fill box showing the rest of the solutions for your data.

If the Auto Fill box does not appear type in the first solution, hit CTRL-E for Flash Fill or click to the Data Tab and Select Flash Fill. Remember, this only works on clean columns to the right of the data you are parsing. 

excel1

You can choose what you would like to have as a result. The results are NOT formulas but different arrangements of text. I’ve cut right to the desired list by typing first name, last name, and the phone number the way I want it to appear in D3 and then clicking Flash Fill again. Flash Fill will occasionally bring up the Auto-Complete box if you go down to the second cell in the range and start to typing, but I prefer using the command in the Ribbon or the hot-key CTRL-E.

excel2

excel3

With Flash Fill, you can parse, combine, or add text to your solution. Notice that I added the spaces, parenthesis, and dash.

To see this work at a secondary level, let’s look at a QuickBooks Trial Balance report sent to Excel. Note that the account number, sub account number, and description are all in the same column. 

excel4

You’ll notice that we’re headed for a problem. QuickBooks puts the account and sub-account descriptions and account numbers in column A. So, we need to do one more step compared to the first example above.

Flash Fill the account number like you did above and you should end up with the cursor stopping on the first cell with a sub-account. 

excel5

This is called an ambiguous cell. Excel does not have enough information to solve this secondary level of parse, yet.

Return to list