Overview
Tiller’s new Holdings sheet is a useful sheet containing raw data. But, it doesn’t make it easy to group, sort, and summarize your holdings. That’s why I built the Holdings Summary sheet.
The sheet is simple to use. After you install it, there are 3 dropdown selectors that give you 32 total different options for how to group the holdings, how to sort them and whether to display the Description or Symbol. The summary section now includes a sparkline bar for each row representing the % of Total.
The sheet, using just one formula, quickly generates a dynamic view based on those settings. You can easily change any of the settings to get a different view.
This sheet makes no recommendations and assumes nothing about your goals. It’s a more user-friendly view of your Holdings sheet to help you better understand what you own, with no additional user data entry.
A closeup of the summary section:
This sheet answers questions like:
- What are my largest Accounts or Holdings?
- Show me all the Holdings in each account by value?
- What is the value of each of my unique holdings?
- What are the holdings in each Account with the biggest gain and loss?
This sheet also restructures the entire report on the fly with dynamic groupings, which makes it much more readable than the raw data Holdings sheet.
I designed this sheet to give me a more meaningful view of my holdings. Hopefully, it will do the same for you.
Installation
How to Install the Sheet:
To install the sheet, you first make your own copy of the spreadsheet. Then, you copy the Holding Summary sheet into your spreadsheet where you already have a Holdings sheet.
-
Ensure you are logged into the Google account you use for Tiller (otherwise, the link below will just send you to a blank login page).
-
Click here to make a copy of the Holdings Summary template: https://docs.google.com/spreadsheets/d/1UjvazDqnBbh16TpURV-iM3WKo7wQ2D2ume_02ARJMs8/copy
-
Click the blue “Make a copy” button. This will open a standalone copy of the template in your Google Drive.
-
At the bottom of the screen, right-click the Holdings Summary tab.
-
Select Copy to > Existing spreadsheet.
-
A window will pop up. Search for and select your active Tiller spreadsheet.
-
Open your active Tiller spreadsheet. You will see a new tab at the bottom called “Copy of Holdings Summary”.
-
Rename that tab to Holdings Summary.
-
(Optional) You can now delete the standalone template from Step 2, as you no longer need it.
Don’t request Editor access to the sheet. Just make a copy of the template.
Setup
This sheet requires that you have the Tiller Holdings sheet, named “Holdings.”
It also requires you have the following standard columns in the Holdings sheet:
- Symbol
- Description
- Quantity
- Cost Basis
- Price
- Value
- Account
- Account Type
- Holding Type
Some earlier versions of the Holdings sheet used slightly different column names.
If this sheet isn’t working, check that you have the column names listed above or see the FAQ below.
Usage
The default settings for the sheet are:
- Group By: Account
- Sort Order: Value
- Show Description or Symbol: Description
Here are additional options:
- Group By: Account, Holding, Institution, Holding Type
- Sort Order: Value, Alphabetical, Cost Basis, Gain/Loss
- Show Description or Symbol: Description, Symbol
If “Holding” is selected as the Group By option, the sheet will dynamically group and display the holdings by either Description or Symbol, depending on your selection in cell B5.
Cells B3, B4 and B5 use Data validation rules to determine the options. They should survive when you copy the template, but if you run into any issues, you can re-load them.
Money Market Funds
This sheet contains a fix for Money Market Funds. When I built the sheet, if the Holding Type of the sheet is “moneyMarketFund”, the value listed on the Holdings sheet would show as $1.00.
Money Market Funds have a Price of $1.00, but the Value is based on the Quantity times the Price. Logic has been added to correctly calculate the Money Market Funds, and not use the incorrect $1.00 value. The Cost Basis of the Money Market Funds matches their Value.
If the Holdings sheet ever gets updated to fix this, this sheet should still work, but the extra logic won’t be needed.
Format Customization
In column J of the sheet, the row type is generated as text, such as “Summary”, “Group”, “Detail” and “Total.”
This allows custom formatting to be used on the rows starting in row 7. If you want to change the default blue and white color scheme for the header rows, you can change them.
To customize your own format settings, select the whole sheet, then Format, then Conditional format rules. You can adjust the format for row types: Type, Group, Total, Summary Total, and Blank.
The Gain/Loss cells use custom formatting to display gains in green and losses in red.
If you want, you can hide column J. But don’t delete it.
Permissions
Feel free to copy, use, and expand the sheet to your liking.
Notes
If you want to see how the sheet works, the only formula is in Cell A7.
The formula uses the LET function and other recent Google Sheets functions like MAP, HSTACK, and VSTACK. The formula is 140 lines long.
The sheet requires no helper or hidden columns. Because all the processing is done in memory in the single formula, it is lightning fast.
FAQ
Q: Why am I seeing a #REF! error in cell A7?
A: This usually happens for one of two reasons:
-
Your raw data sheet is not named exactly “Holdings” (check for extra spaces).
-
You accidentally typed data into one of the blank cells below A7. Because A7 generates the entire table, any text in its way will block the formula. Find the cell with the typed data, delete it, and the summary will reappear.
Q: Why is the formula returning an #N/A error?
A: The formula is likely having trouble finding one of the required columns. Double-check your “Holdings” sheet to ensure all the column headers match the required list exactly (e.g., “Cost Basis” instead of “Cost”, or “Holding Type” instead of “Type”).
Q: I added new columns to my main Tiller Holdings sheet. Will that break this summary?
A: No. As long as you don’t delete or rename the required columns, you can add as many new columns to your raw Holdings sheet as you like. The formula dynamically searches the headers to find what it needs.
Q: The dropdown selectors in B3, B4, or B5 disappeared. How do I get them back?
A: You can easily recreate them. Click the cell, go to Data > Data validation, click “Add rule,” set the criteria to “Dropdown,” and type in the options listed in the Usage section above.
Q: Can I add my own columns to the Holdings Summary sheet?
A: You can add columns to the right of the generated table (Column J and beyond), but you cannot insert columns into the middle of the generated table without modifying the formula in A7.
Updates
The sheet may be updated. If you want the latest version, check the version number in cell H1 and compare it to the latest Changelog entry below. If there is no version listed, it is an old version.
You can update the sheet by following the Installation section steps again. You will need to rename or delete your old Holdings Summary tab first. Any custom formatting or changes to the formula on the old sheet won’t carry over.
Changelog
Sept 30th 2026 - Version 2. Changed the range on raw_data in the formula so it will work with any size Holdings sheet. Added a sheet version number in H1.
Oct 1, 2026 - Version 3. Added Sparkline % of Total bars in the summary section.