fbpx

Utilities Expense Tracker Spreadsheet

Reduce utility bills with tips to save on electricity, gas, and water usage. Track consumption and costs over 5 years to compare and budget based on past usage.

Utilities such as electricity, natural gas, and water/sewer services can account for a significant portion of your household budget.

Understanding your utility costs and usage is important. The Utilities template assists with utility tracking and budgeting by:

  • Providing space to record consumption, usage amounts, and costs for each utility service over a period of up to 5 years
  • Graphing consumption and cost data over time for comparison
  • Calculating total payment amounts for each utility based on past transactions from up to 5 years
  • Graphing payment amounts over time for easy visualization of spending trends
  • Generating budget estimates based on either the previous year’s usage or full usage history that has been logged

Installation

This template can be installed into Google Sheets with the Tiller Community Solutions add-on for Google Sheets.

Notes

You don’t necessarily have to manually enter the consumption and invoice costs, you could just use this template for the ability to better budget your utilities based on past transactions.

Questions and Feedback

If you run into any problems or have ideas on how to improve this template, let me know! If there is interest, I’ll try to create an Excel version.

Share your questions and feedback here in the Tiller Community.

Setup

  1. Expand the Settings by clicking the + above column AB.
  2. Set each of the “Transactions Based On” to either “Category” or “Description”. This determines how the template will find your utility transactions on your Transactions sheet.
  3. In Transaction Categories, for each utility that you chose “Category” in step 2, choose which category from the drop down menus.
  4. In Transaction Descriptions, for each utility that you chose “Description” in step 2, enter the “Description” of the transaction (note that you’ll need to be sure all your transactions for this utility have the same description, a perfect job for AutoCAT!).
  5. In Budget Type, decide how you want to determine this years budget numbers:
    • Last Year: Each month of this year should be the same as each month of last year.
    • Last Year – AVG: Each month of this year should be the same as the average of all of last year.
    • Last Year – MAX: Each month of this year should be the same as the maximum amount from last year.
    • AVG Per Month: Each month of this year should be the same as the average of the same month over the past five years (or however many years you have data for).
    • MAX Per Month: Each month of this year should be the same as the maximum of the same month over the past five years (or however many years you have data for).
  6. In Budget Start, enter the first date of your budget period. For example, 1/1/2023.
4

Usage

Make changes only in the green cells, using past utility statements as a reference.

  • For each utility, enter the consumption for each month that you have data in the green cells in the leftmost section (Consumption). This can be useful so you can see how consumption varies over the year, as well as how it varies from year to year.
  • Enter the amount you paid in the green cells of the center section (Cost based on Invoices). This can be useful so you can see what each utility is costing you in the month where the usage occurred.
  • The right-most section (Cost based on Transactions) will be automatic, finding totals from your transactions based on the information you entered in the settings. This can be useful to see when the cost of your consumption will affect your budget.

Budgeting

Your budget information, based on the settings you entered, is displayed in the External References section (click the + above column AE to expand).

3 1

Budget Plan: To use your budget information in the Budget Plan 17 template, create a new Budget Item in the Budget plan sheet, choose the proper category for the utility you’re setting up, set the frequency to “ExternalSource”, and in the Notes column, enter:

  • For Electricity: Utilities!$AF$4:$AQ$5
  • For Gas: Utilities!$AF$8:$AQ$9
  • For Water/Sewer: Utilities!$AF$12:$AQ$13

Categories: To use your budget information in the Categories template (only use if you are not using the Budget Plan template), for each utility category, use the following formula in the first budget period month (eg. Jan 2023):

  • For Electricity: =Utilities!AF$5
  • For Gas: =Utilities!AF$9
  • For Water/Sewer: =Utilities!AF$13

Once those are entered, grab the ‘fill handle’ in the lower right corner of each of those cells and drag to the right to copy the formula across the remaining months of the year.

FAQ

This is a free template built by a member of the Tiller Community. While it’s free for anyone to use, it’s designed for spreadsheets automated by Tiller. Additionally, it’s recommended for use within the Foundation Template.

Tiller is the only automated personal finance service for Google Sheets and Microsoft Excel. Tiller automatically imports your daily spending, income, and balances in your spreadsheets, so you can see all your finances in one place and manage your money, your way. Learn more →

Unless otherwise noted above, this free template is installed with the Tiller Community Solutions add-on for Google Sheets. It's an easy way to browse and install dozens of free, prebuilt sheets for tracking budgets, debt payoff goals, net worth, savings, and more, all from the Google Sheets sidebar.

This template for Excel works best when installed in Tiller’s Foundation Template.

Visit the Tiller Community with any questions about this template.

Leave a Reply

Start Your Free Trial

"There isn’t another tool on the market that does what Tiller can do.”
Will Hinton, Google Review October 30, 2023