PnL Analyzer - Profit and Loss Analyzer Solution for Google Sheets


The PnL (Profit and Loss) Analyzer was developed primarily as a pivot-table–like view of aggregated monthly transactional data. It serves up data in such a way as to enable the user to:

  1. Analyze a year’s worth of monthly income and expense data, broken down by Group and Category, to identify missing data, inconsistencies, and outliers
  2. Compare the current year to the previous year to identify changing trends
  3. Visualize total monthly PnL both in tabular format and on a dynamic chart
  4. View Groups and Categories in a condensed pivot style fashion, and evaluate Unused Categories
  5. Dig deeper while remaining in-sheet by using a Transaction Selector to pull transaction details by Category

My Tiller transactional workflow is primarily carried out on a monthly basis. I wanted to reconcile transactions and look for any inconsistencies in known expenses and analyze any outliers, as well as to compare totals against the previous year to see how income and expenses were trending over time. I also wanted to optimize categories to make sure that I was applying them consistently and that they met my budgeting needs. To achieve these workflow objectives, I found myself creating pivot table reports that were fairly time consuming and tedious to maintain. So at last I developed the PnL Analyzer as a no-more-fuss solution. I can now spend more time on analysis and planning and less time on report preparation.

Some users who carry out a comprehensive and frequent budgeting workflow may not derive as much value from the solution, but may still find it useful for planning purposes. My budget is mainly revised on an annual basis and does not track every category item - some are tracked at a higher group level and some new category items may arise that have yet to be allocated in the budget. Therefore I use this sheet to inform future budget adjustments. It allows me to review all groups and categories regardless of their budgeting status. Most of all, it provides a high level financial health check as to how well income is covering expenses throughout the year.

The following Loom video walks through some highlights of the solution:

Loom Overview -


To install the sheet, simply copy it into your Tiller workbook.

A read-only version is available at the following link:

The file contains no underlying data and so the sheet will initially show “No Data - Check Inputs”. This is normal until installed. Once opened, right-click the tab “PnL Analyzer” and choose “Copy To”–>”Existing Spreadsheet” and choose your Tiller workbook. Once copied to your workbook, you may rename the tab and reposition it wherever you like. Once copied to your workbook, the sheet will auto-populate all of the necessary data for 12 months beginning with the selected Start Month and Start Year.

The solution only requires the “Transactions” and “Categories” sheets to work properly.

The solution will honor Categories marked as Hide.

My use case did not have a need for the Accounts sheet and therefore the Accounts sheet is not utilized by this solution. Transactions for an Account marked as Hide will not be filtered out of the dataset.

The solution contains no scripts.

Setup & Usage

All cells with possible selections are in typical “User Editable Green”.

The solution is pre-populated with a Start Year and Start Month. However you can select any Month you desire from the dropdown and enter any Start Year. As long as there is underlying data to support the selected timeframe, it will serve up the results for 12 subsequent months.

The “Show Unused Categories” toggles the display of any Categories that are not marked Hide that have no transactions assigned to them in the current period. At the far right, a column “Cnt” also appears to show the count of total transactions using the given category across all time frames in the Transactions data set. Categories that have a zero count are totally void of any assignment in the entire Transactions data set.

Two columns are provided with checkboxes in each row:

:arrow_backward:Highlight (0) - Toggling the checkbox highlights the selected row as a visual aid. Checkboxes can be toggled using the spacebar. Multiple rows can be selected and highlighted simultaneously. The column can be quickly cleared by clicking the column header to highlight the entire column and toggling all checkboxes off with the spacebar. The number in parentheses indicates how many rows are currently highlighted. Highlighted rows also sum together and display a total at the top of the sheet, indicated as “Highlighter Total P/L”.

:arrow_backward:Do Not Project (0) - Toggling this checkbox turns off the Projection logic used to calculate the Proj. column for an incomplete data set. The Proj. (Projection) column is needed in order to compare an incomplete current year data set with a complete previous year data set. It is calculated using the current year Monthly column values to fill in for any future/incomplete months in the current data set. It is desirable to turn this projection off for line items that are known to be complete already for the year. For example, auto insurance might be a one-time payment in January with no need to project additional expenses for the remaining 11 months. Same as above, these checkboxes can be toggled using the spacebar. Multiple rows can be selected simultaneously. The column can be quickly cleared by clicking the column header to highlight the entire column and toggling the checkboxes off with the spacebar. The number in parentheses indicates how many rows are currently selected.

These checkbox columns should be used with some caution - understand that with the addition or removal of categories to the dataset, the target category may be shifted into another row while the checkmark remains on the row where it was set. The unintended consequence is that a different category is then highlighted and/or marked as Do Not Project. Fixing this would likely require a more involved solution and they are meant merely as a temporary use convenience item.

Under the Transaction Selector area, a Category can be selected to display up to 250 Transactions in the current year. Leaving the selection empty will show any Uncategorized transactions for the current year. The row at the top of the sheet labeled “Missing Categorization” will also show the totals, by month, of missing transactions.

The Transaction Selector results can be sorted by one of Date, Amount, and Account columns, in Asc (Ascending) or Desc (Descending) order. The default sorting order is by Date, Descending. An arrow indicator shows what sorting is currently applied.

If you would like to be able to click the “Ref Link” hyperlink to be taken directly to a given transaction in the Transactions tab, you should enter the GID for the Transactions tab in the input labeled “Transactions Sheet GID”. If it is not entered, the hyperlink will simply be invalid. The GID can be found by navigating to the Transactions tab and copying it from the end of the URL.

If you select a Start Month and Start Year that results in an incomplete data set, meaning there are some months still to be completed into the future, the Monthly data column will be based only on fully completed months. Therefore the column is marked with an Asterisk * to call attention to this fact. The Monthly data column is calculated through the given month as indicated in the top left part of the sheet labeled “:arrow_backward:*Monthly and Proj. data thru”. Likewise, the Proj. (Projection) column for comparing a projected annual value against the previous year of data is also using this limited monthly value.

The Latest Available Transaction Date at the top of the sheet informs the user when only a partial month of data has been collected. For example, even if the sheet is showing several values for September and today is October 5th, if the Latest Available Transaction Date is September 5th, then the user should be aware that there could still be a lot of data missing in September. It’s possible that some data would need to be loaded and categorized before the picture will be complete for the full month of September.


Is it ok for others to copy, use, and modify your workflow?


This sheet employs Conditional formatting for a number of items in order to improve user readability. The amount of formatting should not be excessive to the point of slowing the performance of the sheet. However, if for any reason you would like to remove it, you can simply highlight the entire sheet contents (Ctrl-A or Cmd-A) and navigate to Format->Conditional Formatting and delete some or all of the conditional formatting. This should not affect the functionality of the sheet, but might have an effect on readability. For example, red text highlights in the “Compare w/ Prior Year” Delta Δ columns are intended to call attention to values that have trended in a negative direction (lower income, higher expense).


None at this time.

This is awesome! Thanks for sharing :slight_smile:


Wow! This is cool. :grin:

1 Like

Thank you so much @KyleT ! I just came back from vacation and this was a welcome surprise! I am headed out again in a few days, so won’t have time to work on this until next week. I will report back after installation. I really appreciate the time to build this out, and record the loom to explain it!


1 Like

My God, man; it’s beautiful.

Tiller, hire this guy.


I like it a lot.


Thanks @heather @morgan @ThinkEagle @chrisgp123 @MarcC Glad you all like it :+1:

1 Like

Kyle, this is fantastic. Very comprehensive and detailed way of managing the categories. Looking forward to playing with this. I know you have a ton of work into developing your idea. Great job.

1 Like

Hey @KyleT! Thanks so much for sharing this. It’s amazing.

I like this template for so many reasons.

  • It’s well built and well documented.
  • It is broadly useful and solves everyday personal-finance workflows.
  • Many features are not currently available through other templates.
  • And you anticipate and solve for some new and interesting problems— like highlighting+drilling into categories under review, projecting and comparing year end numbers, linking to transactions from the dashboard, and cleaning up categories based on usage.

Further, I love the accompanying video demonstration. What an efficient way to spin up on all the neat features you packed into this template!

Through our Builder Rewards Program, we offer cash awards for template submissions. Because this template provides new and compelling solutions to everyday personal-finance problems, Tiller is excited to award you $500.


P.S. Sorry for the late review. Have been looking forward to digging into this for a couple weeks now.

P.P.S. I’m intrigued by the way you dynamically reference columns in other sheets. It’s different from what I’ve seen previously. The most clever part about it is that the INDIRECT() references include strings for easier debugging.

1 Like

Hi @randy. Awesome :smiley:, glad you liked it and thanks so much! I really appreciate that positive feedback and the award. I enjoyed building it quite a lot and hope that other people will find it to be useful too. Thank you!

1 Like

Great stuff, @KyleT! The YOY is so concise, and it’s great to be able to sub-total an array of categories regardless of group affiliation. The Unused Categories tool is a whole new level of meta-analysis!

I’m getting some anomalies. My Transactions sheet data goes back to 1/1/2017. All 12-month periods starting with Jan 2017 - Jan 2021 display clearly in your sheet. However, any starting month in 2022 yields only blanks for all 12 months, including months that run into 2023. A 12-month period starting in Jan 2023 displays blanks in the Jan column, but January totals appear in the Feb column, etc. Start in Feb 2023, and Feb is blank, but February totals appear in the Mar column, etc. Start in Mar 2022, or any subsequent month up to now, and all available data is correctly represented.

I viewed the helper data referenced in cell B8 (AR7:BI300), and found that this helper data displays the same omissions as the analyzer table for the various periods. I did find that for all the 2022 starting months, cell AU7 displays a #REF! error (Array result was not expanded because it would overwrite data in BH7.) For the Jan-Feb 2023 starting months, cell AU7 displays “null”, rather than the YYYY-M-D string typically shown. I nudged some things around from there, but quickly got in over my head.

My Transaction sheet Is pure Foundation worksheet, and I’ve got any number of other solutions that are functioning as advertised. Downloaded your sheet three times just to make sure it wasn’t an ID ten T error! :slightly_smiling_face:

Apologies for all the verbiage, and I hope it is comprehensible. Much appreciation for the thought and effort you’ve put into this!

1 Like

Hi @GregC, thanks so much and glad you like it!

You’ve uncovered something really interesting. I spent some time studying this and although I couldn’t reproduce the issue initially with my data (running back to 2019), I could recreate what appears to be a similar behavior by introducing a mismatch between the Date and Month column entries in the Transactions sheet.

I’m not :100: that it’s the solution to the behavior you’re seeing, but please have a look into your Transactions sheet and see if perhaps the 2022 transactions (or any that appear to display incorrectly) have a date in the Month column with a mismatched month or year as compared to the actual Date of the transaction. I made a similar effect by altering my data and this indeed distorts the display kind of like you are describing. The reason it does this is because the QUERY function is pulling transactions by the Date column and then pivoting them by the corresponding Month column (under the assumption that the two are in sync).

The good news is that the sheet can now be made more robust by no longer relying on the Month column. Instead we can create the always correct months in the QUERY function directly.

However, I don’t know what other solutions use the Month column so it’s probably a good idea to clean the data just in case.

The change to the sheet is simple - if you could please try it out and let me know if this fixes the issue, it would be much appreciated.

In cells AU7 and BP7, replace the Col4 array element


with the following:


I’m including a before and after screenshot that explains it in detail. I will update the source sheet with this change as I think it makes the sheet more robust even if this turns out to not be the answer to your issue.

By the way if you do have several line items where the Month is in need of correction, you could add a temporary column to the far right end of the Transactions sheet and use a variant of the same formula to generate the Month data in one fell swoop and then use it to correct the mismatched data.

Something like…


…assuming your Date column is B.

You may also want to check the data in the Week column as well.

Thanks for bringing this to my attention! Please let me know if it helps.

1 Like

Hi again @GregC,

After my initial response, I also determined that my solution wasn’t handling historical months within the selected time range that have no transaction data at all. This could be a contributor to the behavior that you were seeing and needed to be corrected in any case for a more robust solution. These changes did not require much additional modification, but enough that rather than trying to walk through it here, I would recommend simply copying the latest sheet again. I have saved a Version 2 at the original link. I made a change log there where the affected cells are mentioned. In addition to the modification that I shared in the previous response, I updated the same QUERY functions in AU7 and BP7 to handle empty transaction months. Thanks again and let me know how it goes!

1 Like

Fantastic! Ship-shape and Bristol fashion! I found no mismatches in my Month/Week index columns, but your revision eliminated the problem.

Your illustration of the issue is equally impressive. Thanks for the fix!

1 Like

This is awesome! Well done!