Builder's Guide for Microsoft Excel

Microsoft Excel Show & Tell
1

As with our Google Sheets Builder’s Guide, this Builder’s Guide is a work in progress. We have published it as a wiki so that the community’s Superheroes :woman_superhero: can add, improve, and expand on it. (Superheroes, activate!)

Why Templates?

Worksheet templates are incredible tools that empower people with greater understanding, confidence, and control of their financial lives. Each of us has unique workflows for managing our finances and this community has become a place to share those ideas, workflows and best practices.

Overview

We hope this Builder’s Guide will be a seed that grows into a complete reference on Tiller’s workbook architecture and comprehensive set of best practices for aspiring and advanced builders. We will start with the basics, see what is missing, and enjoy the journey as this document evolves.

Best Practices

The best, most-useful templates are easy to try and benefit from. They are portable, intuitive, and adapt to our data organization. They are built in familiar ways and can be easily updated, forked, and maintained. (We designate such templates with the “Tiller Community” badge.)

The three :magic_wand: essential steps to meeting these objectives are:

  • Honor and leverage the Tiller architecture
  • Use structured references with Excel tables
  • Refer to the best practices in this document

Terminology

  • workbook - an integrated collection of one or more worksheets
  • worksheet - a single tab or page in a workbook
  • feed - a readily-updated connection between a user’s financial data and their workbook(s)
  • template - one or more worksheets that help solve a problem often linking to live data through formulas
  • workflow - a productivity-enhancing process or optimization that helps users efficiently solve common financial tasks
  • report - a static sheet of rendered data

Core Worksheets

Tiller workbooks are built atop Tiller’s “core worksheets," a best-practices data architecture that enables interoperability across the Tiller ecosystem.

Whether starting from the Foundation Template or not, Tiller workbooks leverage a predictable architecture based on four core worksheets with specific names, each organized as an Excel table.

  • Transactions
  • Balance History
  • Categories
  • Accounts

Other worksheets/templates in the workbook that do the work of displaying budgets, reporting on cashflow, or charting net worth depend on the data in these four worksheets. Without adherence to the core-worksheet standards, modular templates will not be shareable, fill via Tiller Feeds, or dependably work as designed.

Two of the core worksheets, Transactions and Balance History, are filled by the Tiller Feeds service. The Categories worksheet is not filled by Tiller Feeds but contains a standardized schema for category and budget data that is used for data validation in the Transactions worksheet and by Foundation Template worksheets like the Monthly Budget and Yearly Budget. Finally, the Accounts worksheet is a reference for linked accounts and user overrides.

These worksheets each contain reserved-word headers structured as Excel tables, allowing templates to locate and leverage data. Customers can choose and modify which columns they want to include in their Core Worksheets, but feeds, reports, tools, and templates will look for specific header names to find data within core worksheets.

Transactions

The Transactions worksheet is a running log of all transactions from accounts linked to the spreadsheet with each transaction receiving its own row. Column headers in the Excel table can be added, moved and deleted. Whenever a reserved-word column header is present, Tiller Feeds will fill the cell with available data for new transaction records.

  • Transactions records in the Transactions worksheet are written by Tiller Feeds only when they are first appended. Tiller Feeds never modifies data in already-added rows (though AutoCat may modify existing rows).
  • The Date column is required for sorting to work properly. If the Date column is missing, new rows will be appended at the bottom of the Transactions worksheet.
  • If an account is unlinked from the worksheet, any already-existing transactions will remain.

Balance History

The Balance History worksheet is a running log of balances for each linked account with each dated balance receiving its own row. This worksheet is typically hidden (as the data can be overwhelming when not rendered with charts and formulas). Column headers in the Excel table can be added, moved and deleted. Whenever a reserved-word column header is present, Tiller Feeds will fill the cell with available data from new balance records.

  • Balance records in the Balance History worksheet are written by Tiller Feeds only when they are first appended. Tiller Feeds never modifies data in already-added rows.
  • Balances for unlinked accounts can be added manually or via scripted workflows in the Tiller Money Feeds add-in.
  • Tiller uses the hidden Account ID column to uniquely identify accounts. This identifier is prefixed with manual: to identify manually-added accounts.

Categories

The Categories worksheet establishes conventions for category and budget data. It is not populated with data from Tiller Feeds.

  • If the Transactions worksheet contains a Category column, it links to the Categories worksheet for data validation.
  • The only required column for category data validation is the “Category” column.
  • Budget data is stored for each category in the Categories worksheet using monthly headers. Because of the way Excel Tables work, we were not able to use a formula in the header row in the Categories worksheet for the budget months so the months have to be manually typed and formatted appropriately. We added the New Year worksheet as a helper to quickly get the new months into your Categories worksheet. There is no limit to the number of budget columns.

Accounts

The Accounts worksheet aggregates account data from the balances in the Balance History worksheet. This worksheet serves two purposes:

  1. In the visible area, it allows optional overrides for accounts in the Balance History worksheet.
  2. In the hidden area, it renders the entire Balance History into a sorted unique list of accounts with latest balances. For performance reasons, templates requiring a list of accounts or latest balances should use this hidden data whenever possible. Note that neither the visible nor hidden sections of the Accounts worksheet are structured as Excel tables.

Notes about the Accounts worksheet:

  • The Accounts worksheet contains (optional) global account overrides such as Class, Group, and Hide. Group overrides (e.g. “Credit”, “Banking, Loan”) are used to group similar accounts types in reporting & dashboards like the Balances template.
  • The Hide setting hides accounts from dashboards that report at the account level. Templates that interact with accounts should honor this user preference.
  • Though it is required by some common templates like Balances, Accounts is an optional worksheet. Templates that reference it should use the data when the worksheet is present and also recover gracefully when the worksheet is not present.

Conventions

Data Conventions

  • Typical data sizes. Templates should be built to accommodate typical ranges of user data. Tiller’s templates are built for up to 200 categories and up to 100 accounts.
  • Honor overrides. Where transactions, accounts, and categories have documented overrides (e.g. through the Categories or Accounts core worksheets), these settings should be leveraged and respected in automated reporting. For example, the Categories core worksheet contains a “Hide From Reports” column. Templates and reports that present data organized by category should query the “Hide From Reports” setting and honor it in the report output.
  • Error messages. Avoid error messages such as #N/A, #VALUE, and #REF. Provide a contextual error message or blank cell when possible.

Style Conventions

  • Tiller’s product templates typically use the Calibri font at size 12 points for body text.
  • Try to show template users which cells are meant to be user-editable cells by coloring them green. We use #E2FFE2.

Recipes for Common Data Operations

Often the best how-to reference is picking apart a template that solves a similar problem. We have collected information on some of the most commonly-solved problems and best-practices below.

Dynamic Column References

If you want to share templates, this is THE MOST IMPORTANT thing.

Despite the Tiller core worksheet conventions, users will rightly take advantage of the inherent flexibility of their workbooks by adding, deleting, and moving columns. If your template direct-references the range Transactions!C2:C for the user’s transaction categories, more likely than not, it will point at the wrong data in the user’s worksheet (even though that reference worked in your development worksheet).

Fortunately, solving this riddle is not very hard, there is a very common solution, and it is implemented in most every template that is shared in this community. And, the structured references in Excel tables make implementing solutions in Excel even easier than Google sheets.

When table data is organized into structured references in Excel, ranges can be summoned using only their worksheet and column names:

  • Transactions[Category]
  • Categories[Hide From Reports]
  • BalanceHistory[Account]

Examples of this convention abound throughout Tiller’s Foundation Template.

For tables— like the Accounts worksheet— that are implemented without the superpowers of Excel tables, you will need to use dynamic column lookups and INDIRECT() references as we typically do in Google Sheets. For an example on how to do this, have a look at the new Spending Trends in the Foundation Template. You will find a small table that looks up column headers and transforms the result into INDIRECT column references.

image

You will then see these references used to calculate “Current Assets” with a formula that looks like:

=SUMIFS(INDIRECT($AA$21),INDIRECT($AA$19),"Asset",INDIRECT($AA$20),"<>Hide")

Due to the duplicative use of the Account, Group and Hide header names, column lookups into the Accounts worksheet are slightly different, starting in column G instead of A. For example, uses this formula to find headers (starting at column F) in the Account worksheet’s hidden area:

=SUBSTITUTE(ADDRESS(1,MATCH("Account Id",INDIRECT("'Accounts'!$F1:$Z1"),0)+5,4),1,"")

Or this formula (starting at column A) in other worksheets:

=SUBSTITUTE(ADDRESS(1,MATCH("Category",INDIRECT("'Transactions'!$A1:$Z1"),0),4),1,"")

The “Hidden Area”

Template developers often need a staging area for intermediate calculations. A “hidden area” can be used to feed charts with data, list data validation dropdown content, and or intermediate calculations. Many Tiller templates have a “hidden area” on the right of the main template body. Just select across the visible top-right column headers and click Unhide— honestly, the UX in Excel is a little fussy— to expose the hidden area.

XLOOKUP

The newish XLOOKUP is a powerful and indispensable tool for scanning tabular data with a key and extracting a related value. For example, if you have a category name but want to know its group, you could search the Categories worksheet for the category name, and, once found, extract the group from the same row.

Spill Ranges

Excel spill ranges are incredibly powerful tools for accessing contiguous data ranges. Spill ranges are accessed by modified data references that point to cell blocks. For example, if I render a FILTER() function on all of my account balances in to cell A1 on only accounts with Asset type, the result may be 1 or 100 balances. By running the function =SUM(A1#), I can use a spill range to sum all data from the FILTER() function, however many rows are returned. Spill ranges can 2D, encompassing multiple contiguous columns and rows.

Most Excel functions operate similarly to the ARRAYFORMULA() function in Google Sheets, when they are passed a spill range.

Aggregate Within a Period

There are many ways to aggregate totals within a period like SUMIF(), SUMIFS(), FILTER(), and SUMPRODUCT().

Have a peek at the the Spending Trends template in Tiller’s Foundation Template. In the hidden area, you will see transactions aggregated by category within a date range in the section labeled “Transactions Range”. To the right a tad, you’ll see those same categories aggregated on a daily basis.

Type→Group→Category Hierarchy

Some templates (like the Monthly Budget and Spending Trends) implement a cool dynamic category hierarchy.

image
image302×684 18.8 KB

If you want to reproduce this, the implementation is a little bit fussy but definitely possible. The best reference is the Spending Trends template in Tiller’s Foundation Template. In the hidden area, start by sorting your category data by type, then group, then category name. Copy the the formulas from Spending Trends in AC3:AE3 (under the Type/Group/Cat headers) to the left of your sorted data. these formulas will create row offsets to implement the hierarchy. (Note that a boolean more-space input is referenced to expand/densify the output.) Finally, in the visible area, formulas in B10:C10 display lookup data based on the row indices.

The formulas are somewhat different but you may find this writeup on the Google Sheets implementation helpful conceptually.

Honestly, this implementation is not for the faint-of-heart. The cell references will probably break as soon as you copy the formulas and pulling aggregated data (like totals by group or type using the formula in C10) is a challenge. Hopefully these pointers get you started… then feel free to flesh out this section to make it easier on the next bold soul.

Tiller Community Badge

The “Tiller Community” badge elevates shared templates that:

  • Enhances the value of a Tiller subscription
  • Builds upon the core-sheet datasets
  • Integrate seamlessly Tiller spreadsheet by honoring the conventions of Tiller spreadsheets
  • Includes documentation
  • Solution can be easily removed from a Tiller spreadsheet without significant rework
  • Does not store or transmit user data in any way
  • Are built in good faith