How to Calculate Income Tax Using Basic Excel Functions

Setting up your tax records in Excel is a good idea.
Setting up your tax records in Excel is a good idea. (Image: computer image by blaine stiger from

The hard part about preparing income tax returns is keeping records and knowing how much you gave to charity or how much you spent on that business trip. If you have good records, the tax forms themselves are fairly easy. The math is not too difficult. You need to know how to add, multiply and look things up in tables. There are a lot of software packages that you can purchase that are designed to do the math for you, but if you have Microsoft Excel it's not at all difficult to create your own spreadsheet. Even when you use purchased software, it doesn't hurt to itemize some of your calculations in Excel such as mileage or summing up your various charitable contributions.

Things You'll Need

  • Microsoft Excel
  • Applicable tax forms
  • Your tax records

Gather Your Paperwork

Collect all of your paperwork together in one place. This should actually start at the beginning of the year, with files for pay stubs, receipts, bills and donations. If you don't have time to sort and file everything throughout the year, at least keep a box or bin where you put all of your tax receipts during the year.

Downlod your tax forms from IRS and whatever state you pay taxes in.

Get your previous year tax forms. These will have basic information that will be easier to pull from there than to look up separately.

Create a spreadsheet. Label the tabs with the form numbers that you will be using. You can keep your state and federal taxes together in one workbook or in two separate workbooks.

Enter Data and Formulas into Excel

You can set up four columns when you prepare your taxes. The first column is the number of the line as it is numbered on the tax return. The second is a shortened version of the instructions from the tax form. You can put just enough in this column that you can remember what you are trying to do without going back to the tax instructions. The third column is the actual numbers or calculations. And the fourth column is for remarks or comments.

Enter data for every relevant blank on the tax form.

As you go down, color code cells to keep from accidentally overwriting formulas. For example, you can color each cell where you enter original data into the form a pale yellow. Cells with automatic formulas, where you know not to overwrite the formula with data, can be pale green. This includes values that come from another worksheet. Cells with values that come from a tax table can be color coded rose, tan or lavender so that you know to change that if anything changes that would affect that lookup value.

Enter formulas as described on the tax sheet. If it says to add cells 13 and 15a, then enter a formula that performs that basic function and color the cell green.

Use additional worksheets within the same Excel file for additional forms and schedules. You can get forms and instructions from Use a formula on the main sheet to copy the information needed from the additional worksheet.

Related Searches


Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

4 Credit Myths That Are Absolutely False

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!