5 Tips to Conquer Your Taxes in Excel (Free Template Included)

Two words. Seven letters. Say them and most people start to worry. Tax Time.

As a CPA, the first and last thing on my mind is taxes. I spend a busy tax season helping my clients get control of their finances, managing the steady stream of bank statements, receipts, and mileage logs. At the end of the day I have a small amount of time to wrangle with my own taxes, and I’ve tackled this challenge in the most accountant way possible; with Excel.

I’m going to give you some tips and tricks on how to do taxes in Excel, including a free tax tracker template you can download and start using right away.

Take your budget spreadsheet to a whole new level

Budget spreadsheets are a great way to keep track of your finances. You can add up all of your income and expenses in one place and be well prepared to drop your final numbers into your tax prep software.

While a basic budget spreadsheet is great, it is a rather manual process to prepare. Either you are adding multiple numbers in one cell and triple checking you keyed them in correctly, or you are creating a detail spreadsheet from your PDF bank statement. Unfortunately, both of these are very labor intensive.

Another issue that plagues traditional budget spreadsheets is matching categories up with lines that are on your income tax return at the end of the year. Don’t get me wrong, detail is great in understanding where your money is going, but when it comes to actually doing your taxes it’s just an unnecessary added step. That’s why I advocate for a Tax Tracker. A Tax Tracker takes your raw transactions and generates a Profit and Loss statement (also referred to as an Income statement) to be updated regularly like a budget tracker. You can get the amazing tracking template I use for my business below.

To speed this process up a bit, you can start by downloading an Excel copy of your bank or credit card transactions. In this day and age, most business bank and credit card accounts provide an Excel version of your transactions online and not just the paper or PDF statement.

Armed with your transaction records and the tax tracker template provided, you can take the following steps to fill out your Income statement:

1. Copy and paste your Excel versions of bank or credit card statements, including headings, into the Inc Stmt Detail tab of the template.

2. Indicate which column represents the transaction amount using the drop down menu in row 9. Note: make sure only transaction amounts are in the Transaction Amount column or the calculation on the Income statement will be off.

3. Classify each line to an income/expense category using the drop down menu in Column A, for example “Gross Sales”, or “Other Business Income”.

4. The Income Statement tab utilizes a handy SUMIF formula for each line item on the Inc Stmt Detail tab. For example, for the Gross Sales calculation on the Income Statement tab, the SUMIF formula searches for “Gross Sales” on the Inc Stmt Detail tab, Column A, and sums up all transaction values that match.

The Tax Tracker template provides three major tools that alleviate the typical pain points with the 1040 Schedules A, C, & D. The tabs in the Tax Tracker can be filled out in any particular order and are great way to double check your return before filing.

• On the Sch, A tab, combine all your itemized deductions into the correct line items in column D. This will save you from paying an accountant to itemize your deductions for you.
• On the Sch. D Detail tab you’ll use columns A through G to calculate your gains and losses from the sale of investments during the year which is summarized on the Sch. D Summary tab.
• Use the Inc Stmt Detail and Income Statement tabs as shown above to turn bank and credit card statements into a working income statement that matches up with Schedule C on your tax return.

If you store your financial data in Excel, these next tips can help you make sure your spreadsheets are accurate and error free.

Numbers as they should be

Say your bank provides you with Excel transaction data that isn’t up to snuff to use these new tools. For example, what if all your transaction amounts are numbers stored as text and you need them to be the numbers they represent? Luckily Microsoft anticipated this situation as well and created another clutch formula.

In the event you have numbers stored as text you can use the VALUE formula to convert them to numbers. In an empty column, type the following formula:

=VALUE(text)

In this formula, text is the cell reference for the number to convert.The result of this formula is a mirror image of the original data with the exception that Excel recognizes it as numbers. Copy and paste values over the original data and now other mathematical formulas referencing those values will work properly.

Lists and data validation to streamline data entry

One of the best ways to streamline the categorization process is through predefined lists. To pull this off, data validation will reduce errors and will make data entry easier, particularly if you and your significant other are updating a shared spreadsheet. For example, if “office supplies” accidentally became “offiec supplies”, your formulas that rely on that label would not work. With data validation, you can add an error message that will pop up if you try to enter data that isn’t on the list. Follow these steps to create your own data validation list.

1. Create a list of data options in a single column or row.       

2. Select the cells where you want the drop down menu to be located then navigate to the Data Tab and click Data Validation.

3. Under the Settings tab, select List as the option under the "Allow" drop down list, and then select the range for your list under "Source".

4. Fill out the Error Alert tab to set your error message to stop you from entering values not included in the list.

Bonus Tip: to keep the list of values from being changed, lock the sheet with password protection so it can’t be edited. This can be accomplished under the Review ribbon.    

Track down formula errors with Trace Precedents

There are few things more frustrating than figuring out where the errors are in your formulas. You work hard inputting the data and linking it up only to find the numbers just don’t add up. Instead of clicking through your cells and rereading the code, Excel offers a great tool to quickly see how your data is flowing through your formulas.

1. Highlight the cell(s) containing the formulas you want traced.

2. Under the Formulas ribbon, click Trace Precedents

After you click, blue lines appear showing which cells are included in your formula. This formula is really helpful in showing which cells have data pulled from other sheets in the workbook.

Subtotals for simplicity

In the course of compiling expenses I like to see multiple levels of detail in one columnar format. It helps to visually digest and make sense of the data when there are subtotals along the way. However, the typical addition formulas Excel provides can cause frustration. Either the SUM formula double counts the intermediate sums or you risk missing a cell if added up one by one.

Microsoft realized the need for a summing formula that excluded the in between subtotals when it created SUBTOTAL. Using this formula, you can create sums of specific rows without cluttering the surrounding cells with calculations and your grand total will not double count. To utilize this formula use the following steps.

In the cell you want to create the subtotal, type the following formula:

=SUBTOTAL (function_num, ref1)

• ref1 is the data range that the function will be applied to.

• function_num is the list function to be applied to the selected cells (count, average, sum, etc.)

Below is a sample of the subtotal formula using the SUM function:

=SUBTOTAL(9, D34:D37)

Note the functions available for the SUBTOTAL formula are listed in numerical order while typing out the formula.

The subtotal formula also works great with grouping data. A lot of times you want to see the individual items that sum up to each line item in your Tax Tracker. Instead of creating a whole new sheet for this, you can sort your data by category and group with subtotals.

1. Highlight all the rows and columns of your transaction detail, including headers.

2. On the Home ribbon, click Sort & Filter > Custom Sort.

3. Under Column, select Category. Under Sort On, leave as Values. And for Order, choose either A to Z or Z to A. Also ensure the "My data has headers" check box is selected. Select OK.

4. With the same data highlighted, click on the Data ribbon and click Subtotal.

5. In the pop up window, select Category under "At each change in". Under "Use function" select Sum, and under "Add subtotal to", select Transaction amount. Also make sure the check box "Summary below data" is checked. Click OK.

The results of these steps add subtotal rows for each different category, and sum up on the transaction amounts, including a grand total at the bottom.

Time to tackle your taxes

I hope you’ll find these Excel tips and tricks helpful this tax season to manage all the data that goes into filling out your tax return. Ultimately the key to managing any data is consistency - whatever methods you choose to compile your tax data, it must be done on a regular basis.  Armed with the free Tax Tracker template and your Excel formula knowledge, preparing for tax season this year will be a breeze!