How to Calculate Gas Mileage in Excel

Though your car manufacturer probably lists how many miles per gallon (MPG) your car gets, the actual number may vary widely, depending on whether you drive mainly on highways or mainly on city streets, whether you have a lead foot or whether your tires are properly inflated. To calculate your actual gas mileage in Excel, follow the steps below.

...
Calculating miles traveled

Step

Fill your tank with gas. Make note of your mileage with the full tank. Also make note of how many gallons your tank holds (you can probably find this information in your vehicle's manual). Wait until your tank shows it's three-quarters full, half full or a quarter full. Make note of how many gallons were used and the new mileage.

Step

Open Microsoft Excel, and insert the column headings "Starting mileage" (in cell A1), "Ending mileage" (in cell B1), "Miles traveled" (in cell C1), "Est. gallons used" (in cell D1) and "Miles per gallon" (in cell E1).

Step

Insert your starting mileage in cell A2. Insert your ending mileage in cell B2.

...
Calculating miles traveled

Step

In cell C2, type the following: =B2-A2. Press "Enter." This will subtract starting mileage from ending mileage to give you total miles traveled.

Step

In cell D2, insert the number of gallons used. This will be an estimate. In the example given, it says 8 gallons, which assumes the tank holds 16 gallons and the driver has used half a tank.

...
Calculating miles per gallon

Step

In cell E2, type the following: =C2/D2. Press "Enter." This divides miles traveled by gallons used. The result is your gas mileage.

...
Adding data points

Step

Complete all the steps in the preceding section ("Calculating Gas Mileage Using Excel") before moving to this section. To find the average miles per gallon your car gets, record several more starting and ending mileage data points, and insert them into columns A and B. Also insert the corresponding gallons used in column D.

Step

Select cell C2, and press "CTRL+C" (or click on the Copy icon on the Home tab) to copy the formula in that cell.

...
Pasting formula from cell C2

Step

Then, select cells C3 through C10 by clicking "C3" and, while holding down the mouse button, moving the cursor down to cell "C10." Press "CTRL+V" (or click on the Paste icon on the Home tab) to paste the formula into those cells.

...
Pasting the formula from cell E2

Step

You'll want to repeat this copy-and-paste procedure for the formula in cell E2. Select "E2," and press "CTRL+C." Select cells "E3" through "E10," and press "CTRL+V."

...
Calculating the average

Step

In cell E11, type the following: =AVERAGE(E2:E10). Press "Enter." This gives you the average miles per gallon.