One of the most challenging aspects of fleet management is tracking your fuel card data, but it is essential for your company to monitor changes in your monthly metrics.
Creating a Pivot Table in Excel is one of the easiest ways to monitor data like driver/vehicle breakdowns, how much you spend on each fuel product, and your billing by state, invoice, and date. To help your team organize the data from your invoice, this blog will walk you through the steps to creating the most popular pivot tables used by our clients.
Creating a Pivot Table
Open your latest invoice in Excel, then select the “Insert” Tab at the top of the page. The option to create your pivot table will be on the far left of your screen, circled below.
Click the “Pivot Table” button and a new window will pop up asking for specific instructions. For the first question, hit “Select Table or Range” and highlight your entire invoice.
For the second question, select “New Worksheet.”
When you hit “Ok” a new worksheet will open in your Excel File. The fields from your workbook will appear on the right hand side of your screen. You can drag these fields into the correct box depending on the goals of your report, and we have included some client favorites to get you started.
The fields you select will depend on the goals of your pivot table. We will walk you through a few of the most popular metrics that our customers use, but the possibilities are truly endless!
Tracking Your Total Spend Per Product
If your company is curious about your diesel versus gasoline spend, how much you pay in fuel taxes, or how much off-road/dyed diesel you’ve purchased, Excel can create a breakdown in a few seconds.
To create this pivot table, drag the “Product” field into the Rows box, then drag your “Amount” field into the Values box.
Tracking Your Total Spend Per Driver
As a fleet manager, it is essential to categorize your fuel card spend per driver and monitor for inconsistencies. Fortunately, Excel’s Pivot Tables can whip up a report for you in no time!
To create this pivot table from your invoice, drag the “Card_Desc” field into the Rows box, then drag the “Amount” field into the Values box. This will display a side-by-side comparison of each driver and their fuel spending for the month, allowing you to check for errors or overspending.
Track Your Total Spend by State
If you operate across the United States, organizing your fleet card invoice by State can help determine travel patterns and even your tax liability for each region.
To create a pivot table that sorts by state, drag the field “Region” into the Rows box and “Amount” into the values box.
Tracking Average Price Per Gallon
For some fleet managers, tracking the average price paid per gallon is an essential part of their monthly workload. To generate a quick and easy Price Per Gallon report, you can create a pivot table from your P-Fleet invoice.
To generate a PPG report for different products, drag the “Product” field into the rows box. Then drag the “Price” field into the Values box. The standard formula will default to the sum, instead of the average. To change this setting, click on the arrow to the left of the “Sum of Price” field. From there, a pop-up menu will allow you to select “Value Field Settings.” Click on the Average option and generate your new pivot table.
Pivot tables are a vital tool for any fleet manager, as they simplify your fuel card data with a few quick clicks. Though these are the most popular pivot tables that our clients use, you can adjust the formulas to track whatever metrics are a prime concern for your business.