Overview
Many of Tiller’s most popular dashboards include organize categories into a type-group-category hierarchy for user-friendly browsing. You might recognize this hierarchy from templates like the Monthly Budget, the Savings Budget, and the Yearly Budget.
If you have ever tried to build this type of hierarchy into your own templates, you know that the implementation is pretty fussy.
Inspired by some encouragement by @jpfieber, I refactored the formulas we use to implement this hierarchy for the soon-to-be-released, refreshed Spending Insights dashboard. With some recent formulas additions (e.g. LET, LAMBDA, MAP, etc), it is “easier than ever” to organize your visualizations in this way (though still not “easy”). All of the key formulas have been implemented as array formulas so a single formula can scale with your data rather than expanding repeating formulas down to the bottom of the sheet.
If you have any suggestions on how to make this better, I’d love to hear them in this thread! 
Project Organization
I built the formulas for modularity and reuse in a new demo sheet here.
What am I seeing here? There are a few sections to this example:
B:C- rendered categories for the dashboardF:H- column lookups and settingsJ:N- (raw) query dataP:R- type-group-category row indicesS:V- sorted and filtered data for dashboard
Let’s go into detail on these sections in the order they layer to build the dashboard.
Column Lookups and Settings F:H
Settings
The Settings section contains a single checkbox setting “More Space” that toggles the amount of spacing between the groups and types. This toggle is referenced in a few formulas throughout the sheet. Instead of referencing this settings cell, the setting can be hardcoded in the referencing formulas if you prefer.
Column Lookups
For this example, we are pulling data from the Transactions and Categories sheets. Depending on what you are trying to display (accounts, tags, transactions, etc), your column lookup needs may be different.
Data Range
For this example, we are pulling transaction totals for a data range driven by these cells. Again, depending on your needs, this may not be required.
Query Data J:N
The primary query is in M2. You can see that it sums transaction totals grouped by category within our date range in the Settings section. You can change this query to query whatever you want. ARRAYFORMULAs in cells J2:L2 lookup the hide-status, type, and group of the categories returned by the query.
When we pass these results into the next section, we will use columns J:L to filter (i.e. out hidden categories) and sort (i.e. by type and group) the data. Personally, I think this— aggregate all categories, lookup category metadata, apply filters— is a more performant way of taking these steps than trying to enrich the transaction query with lookups on every row (to run this operation in one fell swoop).
Sorted and Filtered Data for Dashboard S:V
In the next section, in S2 we filter and sort the data. We are just filtering out hidden categories but you could also filter on expenses-only or specific groups. Have fun with it!
Type-group-category Row Indices P:R
Two fussy arrayformulas in P3 and R3 scan the data in S:V and assign row indices for types, groups, and categories. You can see that row zero will have a type, row one will have a group, and row 2 will have a category, etc…
Both formulas define the variable MORE_SPACE and link that to the More Space setting to determine how much spacing to apply. Again, instead of linking to a toggle, you can hardcode this to suit your purposes.
Rendered Categories for the Dashboard B:C
Now that we know which rows all the information will be on, we can render the data.
The formula in B3 creates a SEQUENCE of indices that encompasses all type, group and category rows in the hierarchy by getting the MAX value in column S. Next, it uses the MAP function to map each row index into the name of a type, group or category. It does this through the use of nested IFERROR statements, essentially checking if each row index exists within first the category index column, then the group index column, then (finally) the type index column. If there is a match, an XLOOKUP function will fetch the text from the appropriate column S:U.
In a similar way, the formula in C3 creates a sequence of row indices and looks for matches in the index columns to determine what type of row each one is. In this example, if it is a category row, the category total is fetched with an XLOOKUP. If it is a group row, the formula dynamically creates a group total using SUMIFS.
Hopefully, you see the basic mechanics of building row indices in P:R and then rendering content based on row index matches. Of course, these formulas can be modified to include totals for category types, sparklines, etc…
Conditional Formatting
A final touch to this example is the conditional formatting that helps to identify the type of content in each row.
For types, we are using the formula:
=ISNUMBER(MATCH(ROW($B3:$B)-ROW($B$3),$P$3:$P,0))
For groups, we are using the formula:
=ISNUMBER(MATCH(ROW($B3:$B)-ROW($B$3),$Q$3:$Q,0))
In a similar way to the row-index lookups, these formulas check for a match between the row index, which is derived from the distance from the start row ROW($B3:$B)-ROW($B$3), and the values in the type index $P$3:$P and group index $Q$3:$Q columns.
