Docs: Monthly Analysis worksheet for Excel


Use the popular Monthly Analysis worksheet with your Tiller Foundation Template to drill down and visualize your budget, actuals, or remaining organized by type, group, and category and get a comparative analysis of how your budget and spending have changed over time. Note that this template is also available for Google Sheets .

How to Install the Monthly Analysis Worksheet

  1. Download the Monthly Analysis workbook v1.01 .
  2. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.


  • v1.01 on Oct 27, 2023: fixed Available calculation for income types in version

How to Use the Monthly Analysis Worksheet

Once you’ve categorized transactions you can immediately start gaining insights using this sheet. Use the controls at the top to customize your analysis

Date Range
Choose a specific year or the last 12 months. Years available in the selector are based on dates of transactions in your Transactions worksheet.

Cascading Selectors

The selectors below the date range cascade as you make your selection to help you get more granular.

Values to Show
Choose whether you want to see actuals, budget, or available values. Budget and available require that you have set budget values in the Categories worksheet.

This selector controls the comparison chart that starts on row 11 and the yearly chart that starts on row 26. E.g. if you choose actuals, your comparison will be of month over month actuals and the yearly chart will only show actuals. This selector does not control the Budget vs Actual chart in the upper right.

Choose Income or Expense. This selector controls the comparison chart that starts on row 11 and the yearly chart that starts on row 26. E.g. if you choose income you’ll only see income month over month comparison comparison. This selector also controls the chart in the upper right.

All or Filter By
Choose whether you want to see all data from all categories or choose Category or Group to display a fourth selector that allows you to choose a specific Category or Group.

This selector controls the comparison chart that starts on row 11 and the chart in the upper right.

When you choose to filter by a group or category this option becomes available and allows you to select a specific category or group. This selector controls the comparison chart that starts on row 11 and the chart in the upper right.

Video demo

@randy and @heather did a Community Webinar session on the Google Sheets version of this template, which you can review for reference but note that the Excel version is not included in this demo video.

Understanding the Monthly Analysis Dashboard


The selectors at the top of this dashboard allow you to control the way data is presented in other areas of the Dashboard.

Budget vs Actual Chart

Use the Budget vs Actual chart in the upper right to quickly visualize budget vs actuals across time for income or expense for all or specific categories or groups. This chart will always display budget vs actuals, but can be customized to review income or expense and further drill down by a specific category or group using the selectors.

For example, if you’d like to see your “Auto & Gas” category charted out over the last 12 months you would choose a selector configuration as depicted below.

Comparison Chart

The comparison chart that starts on row 11 can help you quickly understand how your budget has changed compared to the previous month. Use the Values to Show, Type, and Filter selectors to customize the data that’s presented.

You’ll see a % change from the previous month as well as the $ value difference.

Yearly Analysis

Use the Yearly Analysis table that the bottom to see the entire year’s actuals, budget, or remaining amounts for all types, groups, and categories.

Adjust the Values to Show selector to pick the data you want presented in this table. The Values to Show selector is the only selector used to change the data set for the Yearly Analysis.

Troubleshooting & FAQ

Can I use this with other templates?

This Monthly Analysis worksheet was specifically built for use with the Tiller Foundation Template so it cannot be used out of the box with other templates.

Why aren’t transfers included in the Type selector?

Generally transfers are ignored in most budgeting analysis tools because they typically net out to $0. If you feel having the transfer data available in this analysis sheet please add a feature request.

My data looks inaccurate, what should I do?

Reply to this topic and let us know and we’ll debug.

Ask a question or get help

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Get Help > Spreadsheet templates category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

:mega: Shoutout

Many thanks to @dannypark for his work porting the original Google Sheets version to Excel.

Nice Monthly Analysis! I encountered the budget error indicating “No Budget Found”.
=IF(ISNUMBER(XMATCH(B26,DATEVALUE(Categories[#Headers]))),“”,“No Budget Found”)
I’m not sure how to correct this error?

@david I think it means that you don’t have any budget values on your Categories sheet in the Month columns out right - can you double check that? if you don’t see the month columns to the right (starting in column E usually) in the Categories sheet they might not be expanded, look for the + at the top of the sheet just above the column letters to expand.

I see that I have January 2022 thru December 2022 in the Categories sheet. Looks like I need to update the sheet to manually change the year from 2022 to 2023 on each month column.

Is there an easy task to accomplish the change?

@david - you can just change the year from 2022 to 2023 by re-typing it in those cells

It worked to stop the error, but the budget red line is straight from 1/1/2023 through 12/1/2023 at $10,000 level.

Still investigating…

It doesn’t appear to work. The actual and budget are not updated. No way to customise the starting month and year like in the sheet. The Group and category are shown but no figures show up in Actual categories.

Did you go through the steps of breaking the links so they reference local worksheets, @ adekunledauda?

1 Like

Yes, I did. But found the problem…The calculation options are on manual hence the sheet not updating after linking to the local worksheet.

How do I fix the starting and ending dates say ( Jul 2023-June 2024) instead of Jan-Dec? In the Google Sheet version, today’s date can be changed to 6/30/2024 to recalculate Jul 23-June 24, but the Excel date range is based on the A12 formula. Thanks

1 Like

It’s not really setup for custom ranges, @adekunledauda, but you can make a simple change (overwriting the existing date functionality) to implement what you’re asking for.

I put a custom start date (e.g. 7/1/23) in cell D4, then overwrote the formula in A12 with this:

Just make sure to save the original formula from A12 somewhere. :wink:

Income section is negative. How can I fix that? Thanks!

The same thing happened in your template: Jan and May have negative income value, but Nov and Dec has positive. After importing this template - all my income show negative value and calculation afterwards are all messed up.

@randy can you double check this in the template? I notice that in the screenshots too. Is it that the income in your test sheet is categorized against negative transactions? I haven’t had a chance to dig into this.

It’s hard to tell what your settings are, @nwb, but, if you have the Values to Show set to “Available”, the template shows Income net of budget. What I’ve seen is that if you have more income in a month than the budget, the budget-actuals is a negative value. Is this maybe what you’re seeing?

It is set to available on my part. As Heather and I said, your template is not right - just look at the screenshot you provided -
Jan- paycheck:-600, expense: 918.04, cash flow:-1518.04

The correct version should be:
Paycheck:+600, expense:-918.04, cash flow: -318.04

After importing you template to my foundation sheet, anything that is an income shows a negative value on the monthly analysis sheet, and therefore, cashflow is wrong afterward.

Thanks for flagging this @nwb. I think I have a fix published. Can you download v1.01 at top and let me know if it resolves the issue?

The income is positive now. However, cashflow calculation is still incorrect after importing.

After importing on my sheet, cashflow = |income| + |expense|, it’s adding the positive value of both income and expense even tho expense is a negative value.

This also shows in the v1.01 template on the Nov and Dec column.

Thanks for fixing the income tho.

I figured it out. It should be a + between the xlookup

Good catch again, @nwb. Just updated the file (still version 1.01). Can you try again?


So I tried to add my 32nd category and the sheet showed “Spill”(propagation error). How can I fix that?