Calculation Issues

How to Create a Fuel and DEF Usage Tracker in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to build an Excel spreadsheet to log daily purchases of diesel fuel and diesel exhaust fluid (DEF), select the purchase type from a dropdown list, and automatically calculate monthly totals.

Product: Excel
Device & OS: not provided
Scenario: Setting up a daily expense log for vehicle or equipment fluids with automated monthly summaries.
Observed behavior: A structured tracking layout is required utilizing data validation for input consistency and summarization features to calculate ongoing purchase totals efficiently.

Before you start

Gather your recent fuel and DEF purchase receipts so you have sample data to test the dropdown lists and ensure your monthly calculations are working correctly.

Solution 1Recommended

Create a Consolidated Tracker Table with a PivotTable Summary

This method uses a single amount column with a dropdown list for the purchase type, making it highly scalable and ideal for long-term tracking using PivotTables.

Using a consolidated table ensures all your raw data stays in one place. By utilizing Excel's Data Validation and PivotTable features, you can automatically generate monthly, quarterly, and yearly summaries without writing complex formulas.

Set up the table headers

Open your spreadsheet and type 'Date' in cell A1, 'Type' in cell B1, and 'Amount' in cell C1.

Apply Data Validation for the dropdown list

Select the cells in the 'Type' column (e.g., B2:B100). Go to the Data tab on the top ribbon, click 'Data Validation', choose 'List' under the Allow dropdown, and type 'Diesel, DEF' in the Source box. Click OK.

Enter your purchase data

Log your daily transactions by typing the date, selecting either Diesel or DEF from your new dropdown menu, and entering the cost or volume in the Amount column.

Insert a PivotTable to summarize

Select your entire data range. Navigate to Insert > PivotTable and choose to place it on a New Worksheet. In the PivotTable Fields pane, drag 'Date' to the Rows area, 'Type' to the Columns area, and 'Amount' to the Values area.

Time-Saving Tip: When you add new purchases to your table, simply go to your PivotTable, right-click anywhere inside it, and select 'Refresh' to instantly update your monthly totals.

Solution 2

Use Separate Columns for Diesel and DEF with SUM Formulas

A simpler, formula-based approach for basic tracking where Diesel and DEF expenses are logged in separate columns and totaled directly at the bottom.

Free Spreadsheet Software

Build Your Fuel and DEF Tracker Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data validation, comprehensive formula support, and intuitive PivotTables to help you set up and manage your fuel expenses quickly.

1. Open WPS Spreadsheet: Launch WPS Office on your device and click on 'Spreadsheet' to create a new blank workbook.
2. Set Up Headers and Dropdowns: Type Date, Type, and Amount as your column headers. Select the Type column, click Data > Validation, and enter 'Diesel, DEF' to create your dropdown.
3. Insert a PivotTable: Highlight your table data, go to Insert > PivotTable, and drag the fields to organize your monthly totals efficiently.

Easily create dropdown lists with the intuitive Data Validation tool.Summarize monthly and yearly expenses instantly using robust PivotTables.100% compatible with Microsoft Excel (.xlsx) formulas and formatting.Free, lightweight, and features a familiar user interface for quick adoption.

microsoft office alternative - wps office

Frequently Asked Questions

How can I group my fuel purchases by month in a PivotTable?

Right-click any date value inside your PivotTable's Row Labels, select 'Group', and choose 'Months' and 'Years'. This will automatically summarize your individual daily entries into clean monthly totals.

Can I calculate the total amount for just DEF using a formula instead of a PivotTable?

Yes, you can use the SUMIF function. If your purchase types are in column B and amounts are in column C, click an empty cell and enter =SUMIF(B:B, "DEF", C:C) to get the total for DEF purchases.

Why is my data validation dropdown not showing up when I click the cell?

Ensure you selected 'List' under the Allow criteria in the Data Validation settings, and verify that the 'In-cell dropdown' checkbox is checked before clicking OK.

Can I add more fuel types to my tracker later?

Absolutely. Go back to Data > Validation for your 'Type' column. In the Source box, simply add your new fuel type separated by a comma (e.g., Diesel, DEF, Unleaded), or link the source to a separate range of cells containing your list.

Excel & Data Problems