What are the things you need to consider when designing a spreadsheet and how do you apply them to spreadsheet design?

What makes a good spreadsheet? Two things: good design and good data.

This may sound obvious, but it’s all too easy to dive into creating a spreadsheet without putting much thought into its design. And a poorly designed spreadsheet is bound to make it harder to enter valid data. Without valid data, your spreadsheet will never yield useful results.

Good design doesn’t mean making your spreadsheet look good – although spreadsheet appearance can definitely aid in usability and readability. Good spreadsheet design consists of organising your data logically and ensuring that all your formulae are correct.

A simple spreadsheet design

Let’s take an ultra-simple example: a share listing which displays the stock names, stockholding, and current share price of the stocks you own. Our spreadsheet will consist of four columns labelled Company, Current Shareholding, Current Share Price and Current Value. We’ll enter the details for each company in a separate row under these columns.

Spreadsheet cells can contain raw data (numbers, dates, text labels, images, web addresses and so on) or formulae. In this spreadsheet, the value in column D (Current Value) is the result of a formula: multiplying Current Shareholding by Current Share Price. The selected cell (D5) thus contains the formula =(B5*C5) which you can see displayed in the Data Entry box; while the result of the calculation is displayed in the cell D5 itself.

Figure 1. A stock portfolio spreadsheet

Thus, if we’re fortunate enough to have shares in Microsoft, Facebook, Apple, Google and Amazon, our spreadsheet will have column labels in cells A1 to D1 (remember cells are addressed by their column and row coordinates), thus:

A1: Company

B1: Current Shareholding

C1: Current Share Price

D1: Current Value

The details for Microsoft will be entered in the second row, that is, cells A2 to D2; for Facebook in cells A3 to D3; for Apple in cells A4 to D4; and so on.

When you’re entering data, you simply click in a cell to position the cursor, type the value into the cell, and move to a new cell by using the arrow keys or (in most spreadsheet programs) by pressing the tab key.

Using formulae

Where do the values come from that appear in the Current Value column?

Well, you could manually multiply the Current Shareholding by the Current Share Price. But that’s what your spreadsheet is for: doing the manual labour of calculating. So, instead of working out the value and typing it in the appropriate row in column D, we insert a formula in column D instead of a value.

The formula for the Current Value of our Microsoft stocks, for example, is B2*C2 (that is, the value contained in cell B2 multiplied by the value in cell C2) and the result is placed in cell D2. The value of our Facebook stocks is derived by the formula B3*C3, and the formula is placed in cell D3.

Once you’ve placed a formula in a cell, you no longer have to bother about working out the calculation yourself. More importantly, any time you change the contents of a cell referenced by that formula, the contents of the cell containing the formula will be updated to show the new result.

For instance, if we have 1000 Microsoft shares which we bought yesterday at $31.00 per share, and the price rises today to $32.87, all we need to do is type in the new share price in cell C2 and the Current Value in cell D2 will be automatically updated to a healthy $32,870.00.

That’s the benefit of using formulae: you enter the formula once in the appropriate cell, and from then on the value displayed in that cell will reflect the latest data typed into your spreadsheet.

Eliminating repetitive work

This is all very well, but it still seems like a fair amount of work.

What if we had a portfolio of 60 different shareholdings, instead of our measly (but valuable) five? Would we have to type in 60 different formulae in column D to find out the current value of each of our shareholdings?

No. Spreadsheets are much smarter than that. Instead of creating a series of formulae such as B2*C2, B3*C3, B4*C4 and so on, you create the first formula and then copy it. The spreadsheet will not only copy the formula to the destination cells you select, but it will update the formula to reflect its new position.

For example, we place the formula B2*C2 in cell D2 to get the Current Value of our Microsoft shareholding. We select that formula and copy it (the copying method differs from one spreadsheet program to another), and then select cells D3 to D6 and paste the formula into them.

Of course, we don’t want the exact formula (B2*C2) copied, or the Current Value column will repeatedly show the value of our Microsoft shareholding. Fortunately, spreadsheets are smart enough to adapt the formula to its current position, filling in the appropriate B3*C3, B4*C4 and so on. If we have 60 different stocks, all we do is create the formula, copy it, select the next 59 cells in column D, and paste the formula. The spreadsheet will do all the hard work of pasting the appropriate formula in each cell.

In Microsoft Excel, there’s an even easier shorthand method: After having typed the formula =(B2*C2) into cell D2 to get the current value of our Microsoft shares (as in Figure 2), click in D2 and drag the small handle at the bottom right of the cell down across D3, D4, D5 and D6. Voila! The formula is copied to those cells and updated to match its context (as you can see in Figure 3). Use the same technique whether you’re copying a formula to five cells or 500.

Figure 2. Copying a formula

Figure 3. After copying the formula

Automatic numbering and labelling

Spreadsheets are wonderful at eliminating repetitive work. Not only can you automatically copy formulae in this fashion, but you’ll find most spreadsheets also have a feature called ‘auto-series’ which automatically inserts series of numbers, dates, days or months.

For instance, to place the months of the year across the top of a spreadsheet, type January in the first column, select the next 11 cells and click the auto-fill button. In Excel, it’s even easier:

Type January in the first column, then click-and-drag the ‘handle’ in the bottom right corner of the cell to the right for 11 cells to add a full year’s worth of months.

Figure 4. Using auto-fill in Excel

Apart from these auto-series and formula shortcuts, every spreadsheet includes an auto-sum shortcut. Probably the most common type of spreadsheet task is the quick totting up of a row or column of numbers. Spreadsheets turn this calculation into a one-click task: click beside or beneath the numbers you want summed, and click the auto-sum button on the toolbar. A formula summing the numbers is placed in the cell immediately to the right or beneath the appropriate numbers.

Apart from these auto-series and formula shortcuts, every spreadsheet includes an auto-sum shortcut. Probably the most common type of spreadsheet task is the quick totting up of a row or column of numbers. Spreadsheets turn this calculation into a one-click task: click the cell beside or beneath the cells you want summed and then click the auto-sum button on the toolbar (it looks like this      in Excel). A formula summing the numbers is placed in the cell immediately to the right or beneath the appropriate numbers.

Excel’s Flash Fill

Did you notice the little blue and white box with the plus sign at the bottom right of the selected cells in Figure 3 above? It’s a new feature in Microsoft Excel. If you click the arrow on the right of that box a small menu of options is displayed, letting you select exactly what gets copied—data and/or formatting—from one cell to another, as you can see in Figure 5. The last option on that menu, ‘Flash Fill’, is new to Excel 2013. Flash fill lets you perform quite complex manipulations on data in a range of cells simply by providing Excel with a couple of examples to follow.

Figure 5. Auto-fill context menu

For example, you can use flash fill to convert a series of names recorded separately as first name, middle initial and last name into a single ‘full name’ cell containing, say, the last name followed by a comma followed by the first name and the initial. As you can see in the image below, once you fill in the first example—in this case, Citizen, John Q.—and then start typing the next name, Excel displays a list of suggested values and, if you like the suggestions, simply pressing Enter will fill your entire list. Had you, instead, typed John Citizen and then started to type George Harrison, the resulting suggestions would have been Emily Post, Alice Toklas and Sandra O’Conner.

It’s nice on a list of five names; a miracle on a list of 500.

Figure 6. Using Flash Fill to transform names

Other spreadsheet shortcuts

Depending on the power of the spreadsheet program you’re using, you’ll find endless variations on such shortcuts. Most spreadsheets, for example, don’t force you to type in cryptic ranges such as D9:L27. Instead, you can click and drag to select the range of cells your formula will reference, and the spreadsheet inserts that range automatically in the formula.

Microsoft Excel lets you choose names for individual cells or ranges of cells, or refer to cells by their row and column labels.

For example, if we have the months listed across our columns and budget items such as Petrol, Rent, Phone and so on as our row labels, in Excel we can refer to a cell’s contents as January Rent or March Phone. Not only does this make formulae much easier to understand, it also eliminates many errors that result from trying to get our cell addresses correct.

Useable spreadsheets

This leads us back to our starting point: good spreadsheet design.

As you can see, it’s not at all difficult to get disoriented in the maze of cell references. You need to pay close attention to ensure that your spreadsheet formulae perform the right actions on the correct range of cells.

Before you start creating any spreadsheet, think ahead and decide how you want to label your columns and rows. Sometimes the number of rows or columns in a spreadsheet will be fixed, sometimes it will increase over time. In our share tracking spreadsheet, for example, the columns are fixed but we’ll add (or subtract) rows as we change the stocks in our share portfolio. You can always insert or delete rows and columns later on, and copy formulae to the newly created cells.

When you enter formulae, check and double-check the row and column coordinates. You’ll often find it easier to check your formulae if you enter some test data in your spreadsheet, check the results, alter the test data and re-check the results. If your software lets you, use descriptive labels wherever possible instead of column and row coordinates, so your formulae are easier to understand and check.

Checking your design

Make sure you not only check the cell’s contents but also use the data entry and address boxes at the top of the screen to double-check your position and the entire contents of a cell. Sometimes the contents of a cell are not totally visible, and the data entry box will provide a quick means to see the entire contents.

If you find a cell contains a series of hash marks (#), it means the value the cell contains is too large to be displayed. You can remedy this by resizing the column (usually a matter of positioning the cursor at the top of the worksheet on the line separating one column from another, and clicking and dragging the double-headed arrow until the column is the desired width).

Finally, you can use different typefaces and colour to make your spreadsheet easier to read. You can choose different background and font colours for different segments of your spreadsheet, enabling you to highlight important information and distinguish different areas of the spreadsheet. Make sure you use these formatting features judiciously: the aim is not to dazzle, but to make your spreadsheet easier to use and understand. Excel lets you automatically apply conditional formatting—different colours, icons and son on—depending on the values in a range of cells.

If you take care in your spreadsheet design, the task of data entry will be that much easier. It’s the combination of good design and correct data entry that will yield you useful results.

Advanced tools

This is just a taste of the design and data tools available to you. Programs like Microsoft Excel also provide data validation rules to help ensure the data you enter falls within permissible ranges; and a range of tools for checking that the formulae you type really do the job you want.