Skip to main content

Accounting

Avoiding the “Spilled Milk” Approach to Spreadsheet Design

Have you ever had one of those days, where everything seems to be harder to accomplish than it should be? Let's say that you're cleaning up a spill on your kitchen floor, one that seems to spread ever further, despite your efforts to contain it.

David Ringstrom

Mar. 13, 2018

Excel

Have you ever had one of those days, where everything seems to be harder to accomplish than it should be? Let’s say that you’re cleaning up a spill on your kitchen floor, one that seems to spread ever further, despite your efforts to contain it. Many users unwittingly adopt a similar approach to spreadsheet design by spreading data out horizontally instead of vertically. In this article I’ll explain several reasons why keeping your data as vertical as possible can have a dramatic improvement on your experience in Microsoft Excel.

A blank Excel spreadsheet is like a city with no natural barriers whose suburbs just keep expanding ever outward. When our cursor is in column A, we have 16,383 additional columns to the right, which in effect means no natural barriers for our data. And so away we go, adding ever more columns to the right as we accumulate more data. Like many things in life, this approach works until one day it doesn’t. Other instances of spreadsheets going horizontal include spinning off multiple versions of a workbook or adding ever more worksheets. The thinner that you spread data around Excel, the harder your data becomes to work with. Fortunately, you can start fighting back against the sprawl.

The first rule of thumb is to keep as much data in list form as possible. This means working down the columns, as opposed to across rows. If you’re entering stock prices for the last 30 days, this data should span rows 2 through 31, as opposed to columns A through AD. Row 1 should always be reserved for titles (often referred to as headers in Excel). Many of Excel’s features function best when the first row of a list is comprised of column titles. Going horizontal in Excel is like tying your shoelaces together with ever bigger and tighter knots.

You’ll still be able to move forward, but with ever more effort. Conversely, the discipline of going vertical unlocks the hidden capacity of Excel to function like a database. Indeed, many users take the “spread” part of spreadsheet a bit too literally. Here’s a sample of what you can do with data that is stored going down columns as opposed to across rows:

  • Easily extract or summarize data by way of look-up formulas such as VLOOKUP, INDEX/MATCH, SUMIF, SUMIFS, and more.
  • Use the Recommended Pivot Tables feature on the Insert menu in Excel 2013 and later to ask Excel to suggest ways to summarize the data.
  • Use Excel’s Table feature on the Insert menu of all versions of Excel to manage the list more effectively.
  • Use the Filter command on Excel’s Data menu to instantly collapse the list to only show rows that meet specific criteria. Or, in Excel 2013 and later use Slicers with tables to filter the data with a single mouse click.
  • Sort the data as needed by way of the Sort command on Excel’s Data menu. Granted, Excel does offer the ability to sort sideways, but this often falls into the category of “not that you would, but you could.”
  • Use look-up formulas that populate worksheets on demand, rather than brute force building out a worksheet for every scenario. For instance, many users create financial reporting spreadsheets that have one worksheet for each month of the year. This works until it doesn’t, in that now any changes to the structure of the worksheets must be carried out twelve times, as opposed to only once.
  • Utilizing the Data Model feature in Excel 2013 and later to relate multiple lists or tables together. This gives you leverage to use Excel like a database with pivot tables, pivot charts, or PowerView reports. All of these features turn the tide, so that Excel assembles your reports for you, rather than you having to cobble your data into desired formats.

You likely have many existing spreadsheets that are irredeemable. Going forward, try to enforce the discipline of organizing data into vertical lists. You’ll never use Excel the same away again once you experience the power of using Excel more like a database than a spreadsheet, with a special emphasis on “spread”. Further, spin off multiple worksheets or copies of files judiciously, so that your work doesn’t grow exponentially when it’s time to make changes across those worksheets or workbooks.