Tracking mileage in Excel

A friend of mine who is self-employed was preparing for his annual trip to the accountant and shared how he quickly sums up his miles using Excel 2007. It's a great example of how Excel can really simplify what appears to be a complex task.

First, he takes his mileage logs and quickly subtracts the start from the ending odometer records for each business trip. This proves faster than entering every piece of data into Excel. When he's done, he had a list of all business trip miles.

Here's how to quickly create a summary:

First, open a new workbook and enter every business trip mileage total into Column A. This can be done quickly by typing the number and then pressing the down arrow on the keyboard to advance to the next cell.

To generate the total, enter this formula into a cell near the top: =SUM(A:A)

The beauty of this is that it doesn't matter how long the list gets: Everything in Column A is added together. If you reuse this file, all you need to do is clear Column A and enter your new mileage totals.

You can go even further with this to calculate your total miles for the year and the percentage that is business related:

Create a row for each work vehicle and enter the Start and End odometer readings for the period you're tracking. Then generate the total by subtracting the start from the end ( =F2-E2 in the example above). Do this for each vehicle, select the totals and then use the Sum button to get the total for the year.

Finally, in a new cell, divide the total business miles by the total overall miles for the year ( =D9/G5 in our example) and then format the cell as a percentage by right-clicking, selecting Format Cells, and choosing Percentage for the Number Category.

The great thing is how reusable this is year to year, or - if your employer asks for regular mileage reports - however often you need to report them.

Suzanne