Spreadsheet Design:

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Good Design

A good design for your spreadsheet makes it easy for you:

  • to read the data
  • to understand the point of the sheet
  • to use the sheet's information
  • to update the sheet
  • to spot the important parts quickly

Good design does not have to be pretty or colorful, though that often helps. A well-designed sheet will be comfortable to work with and makes it easy to answer the questions you want to ask.

How can you tell if a worksheet has a good design? The questions below are a good guide.

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: Design To subtopics
    Analysis To subtopics
    FootprintPrint Comments
    What If...To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    ExercisesTo subtopics


Analyze a spreadsheet

To analyze the effectiveness of a spreadsheet's design use the following questions about the spreadsheet. With good design you can answer these easily. A poor or bad design makes it hard or impossible.

  1. Purpose:  What is it for? What questions does it answer?

  2. Data: What data is used and where did it come from?

  3. Calculations: How did they do that? (What parts are calculated and what formulas are used to do the calculations?)

  4. Changes: Does this sheet allow you to add or change data later? Is that important for this particular sheet? Will it be easy to do?

Bad Design: Example #1

What's wrong with the spreadsheet below? Try to answer the analysis questions: Purpose, Data, Calculations, Changes.

Bad Design #1

Bad Design #1

You can't tell anything about what is going on in this sheet! You cannot answer any of the analysis questions. The only good thing about the sheet is that it uses the default font, so you can read the values.

What's wrong?

  • No titles to tell you what this sheet is about.

  • No column labels to explain all the numbers.

  • Not clear whether any of the numbers are calculated.

  • The numbers in the last two columns are hard to read and compare to each other because they are not lined up on the decimal point.

Simple Solution: Adding some simple titles and labels makes the spreadsheet much more understandable, as the version below shows. Now can you answer any of the analysis questions?

Simple Solution for Bad Design #1

Adding titles and labels explains a lot!

It is still not really easy to read the number in F & G, and you can't tell if the Pay for Period entries are calculated or typed in.

Even Better Solution: Some formatting and expanded labels and titles will help even more, as the next illustration shows. Can you answer the analysis questions?

Better solution for Bad Design #1

Adding formatting and explanation of calculations

Now you can see that the Pay for Period is a calculated value using the Hours Worked and Pay Rate. Moving the Hours Worked over puts the values used in the formula next to each other, which makes it easier to see where the Pay for Period comes from -  and why some paychecks are bigger than others.

This spreadsheet is an example of one that you would not normally have to change later. It is a report of the values at a given date. However, a similar spreadsheet that logs the hours worked each day for each worker would have new data added every work day.

Bad Design: Example #2

Bad Design #2

Here is a design problem common when rows and columns are both being calculated. Can you answer the analysis questions above for this sheet?

What's wrong?

  • What formulas? Row 8 and Column F look like they could  be calculations.

  • Is cell F8 related to the row values, the column values, or something else?


  • Use formatting for F8 that matches the source data.

  • Add labels and other formatting to group related cells. In the revised sheet you can see that F8 is the SUM of Row 8.

Solution for Bad Design 2

The right color coding and labels make all clear

Design Principles

From the examples above and some common sense we can come up with some principals and guidelines that will help you design worksheets that work well.

  • Easy to read: Choose fonts and backgrounds and colors for good contrast and easy reading. Consider how the sheet will look in print as well as on  screen.
  • Logical positions: Position data is logically, both for reading and for entering data.
  • Describe: Create helpful labels and titles that make the purpose and function of the sheet clear.
  • Important parts: Position and format the key values, like totals, to make them stand out from the crowd of data.
  • Changes: Arrange the sheet so that adding new data will not break formulas. Surround data groups that may have additions later by blank cells and write formulas that include the blanks. Or use absolute references to cells that will not be moved if data is added.
  • Original data: Use copies or links to original data for actions that may be hard to undo, such as sorting and subtotals. This preserves the original data for other uses later.
  • Future: Think ahead to the future uses of your sheet. Anticipate the needs of other people who may use your sheet without knowing all that you know about it.