Home » Categories » CHARTrunner

CR: Excel tips

At PQ, we often see spreadsheets our customers have created. This happens in the course of a support call while we are helping with an SPC question, a data analysis question, or when we are providing guidance on customizing an SPC chart from our CHARTrunner product.

Modern spreadsheets, like Microsoft Excel, provide incredible power and flexibility. Sometimes this can be a double edged sword. On one hand, power and flexibility allow the user to customize the spreadsheet to meet their specific needs. On the other hand, the power can lead to complex sheets which are difficult to use, difficult to fix when they contain errors, and are designed such that data analysis and charting can be a challenge.

After seeing hundreds of customer spreadsheets, I’ve come up with some tips and examples to help avoid these pitfalls. The tips are directed at spreadsheet users working in areas related to quality: statistical process control (SPC), quality improvement, six sigma projects, quality monitoring, etc. However, the tips apply to almost any spreadsheet application.

Here are the tips which are discussed below:

  • Decide the primary purpose of the spreadsheet
  • Make the primary purpose easy to accomplish
  • Use the simplest possible sheet that accomplishes the purpose
  • Don’t create future work for yourself
  • Keep the data “pure"
  • Be consistent among your sheets
  • Favor traditional arrangements over weird arrangements
  • Use a “notes” worksheet to document complex sheets

Decide the primary purpose of the spreadsheet

You are staring at a blank spreadsheet. You are thinking about how to arrange the rows, columns, and formulas. You are thinking about what information needs to be included. Stop. Before you enter that first column heading – write down the answer to this question:

What is the primary purpose of this sheet?

Keep your answer as short and concise as possible but write it down somewhere; in an email, in a document, on a scrap of paper. Forcing your brain through this groove will improve the sheet you create. Often, a sheet will be used for different purposes – however, when you know the primary purpose, it will be easier to design the sheet to support this.

Here is an example: the purpose of this sheet is to provide a place to enter and store information about how much product we ship each day. Notice that it says nothing about how this information will be summarized, who it will be presented to, or where it will be deployed. Keep it simple and focus on the primary purpose.

Make the primary purpose easy to accomplish

Keep the primary purpose at the forefront as you create the sheet. Ask yourself this question often: “will this support the primary purpose?” If the answer is “no” maybe you should leave it out. The subject of making a sheet easy to use could easily fill several pages – here are just a few starting tips:

  • Make the data entry progression natural: left to right, top to bottom.
  • Avoid forcing the user to jump around to different locations in the sheet or across different sheets during normal data entry.
  • Keep related information together in the same sections of the sheet.
  • Avoid making the user “go somewhere else” to look things up.
  • Don’t make the user type when the information might be filled in with a formula.
  • Consider what the user will be doing and or looking at while they use your sheet.
  • Consider that users will start, stop, and restart while using your sheet.
  • Most people know how to fill out forms and enter data in grids; don’t stray too far from this metaphor.
  • Give the user hints about where they should and should not enter data.

Use the simplest possible sheet that accomplishes the purpose

We often see calculated columns in a sheet that are never used. For example, if you have 4 columns of numbers the fifth column might contain a formula to calculate the total. This is fine if you need the total and you use the total for something. The question to ask is this: does the primary user of this sheet need the total? If the answer is no, leave it out. The more information in front of a user – the more decisions they have to make. Should I type there? What does that mean? Why is it changing? If some other user needs the total, consider putting it in a different sheet where it won’t clutter up the space for the primary user.

Don’t create future work for yourself

Excel provides a huge set of formatting and coloring options. These are nice when you need to present a professional, polished, sheet to a group of people. However, the polished look can take time to accomplish and require more time in the future. Consider this example:

Here, the spreadsheet designer has turned on grid lines around every cell. As new data is added, the grid lines will have to be added to keep the look consistent. This is extra work. A spreadsheet is a grid already – why add borders when the user can see the grid lines without them? Maintaining a sheet like this is more work than it should be. The same could be said of many color and background shading options. Use these only when they add noticeable value for most users of the sheet. If the primary purpose of this sheet is data entry – not presentation – consider leaving out grid lines and shading.

Keep the data “pure”

Typically, there will be a primary data entry user of a sheet. Other users may view the sheet to occasionally check on some summary data. Consider this example:

Here, the sheet designer has mixed raw data and summary data. Column A has “Month” as the header. To me this means that each row under column A will contain a month. But look at rows 5, 9, and 13. They do not contain a month – they contain something different. The same can be said for the Item count and error count columns.

If the primary purpose of this sheet is to “enter data for monthly item counts and error counts” then the sheet has been extended to do other things – namely to summarize the information on a quarterly basis. Summarizing the data quarterly is a legitimate need; however, I would recommend keeping that information separate from the raw data. An alternative:

In the alternative view – the data is entered in one place and the summary happens in another place. This makes analyzing and charting the raw data in different ways much easier as the raw data is “pure.” In other words – it contains only data and not superfluous information that might have to be filtered out for analysis.

Notice that the original sheet might be a good sheet if the primary purpose is presentation of the information to the management team each month. If this is the case, the presentation version of the sheet could easily be created on a separate worksheet with formulas linking to the raw data.

Be consistent among your sheets

As you create more spreadsheets you will develop standard ways of doing things. You will align cells a certain way, use particular fonts and colors, use conventions for how you name columns or format dates, and several other things. Be aware of these and use them consistently from sheet to sheet. Conventions save time. Once you spend 30 seconds deciding how you want dates to look – use that format everywhere. This way – you won’t have to make the decision over and over.

Favor traditional layouts over weird layouts

Many spreadsheets have been designed. Most data entry problems have already been setup in spreadsheet form. A set of conventions for spreadsheet layout has emerged. If you find yourself designing a sheet and thinking “this looks weird,” consider starting over. Design the sheet with some common conventions in mind:

  • Start in the upper left
  • Use the first row for column headings.
  • New data should be added as new rows down the sheet.
  • Keep the data in any one column all of the same type. For example don’t use ‘March 3’ in the first row and ‘03/03’ in the second row.
  • Keep summary data like totals or averages in separate worksheets or a separate section of the data entry sheet.
  • Avoid frivolous ‘over the top’ formatting, coloring, shading, and use of font type faces.
  • Avoid vertical text. Spreadsheets allow this but users find it hard to work with. If you see a user looking at their monitor with their head turned sideways – this is generally not good.
  • Break any of these rules you want – only if there is good reason to do so.

Use a “notes” worksheet to document complex sheets

Some data entry tasks do require more complicated worksheets. A complex sequence of values and formulas may be the only way to accomplish the primary task at hand. When this is the case, remember that 3 months from now or a year from now, you may be asked to enhance the worksheet or fix an error it contains. When this happens, it is helpful to leave some breadcrumbs behind so that you have some guidance in the future.

On a separate sheet within the workbook, create a notes section. As you design the sheet write notes describing how the sheet works, what the data entry sequence should be, and what the logic is for particularly complex formulas. The more your write, the better off you will be a year later when you have to figure out what you did. Be careful to keep these notes current as you design the sheet. If you write a note – then change the sheet without updating the note – it may actually misdirect future efforts to repair or improve the worksheet.

Summary

Many of us design and use spreadsheet every day. Spreadsheets have become an important part of many jobs. Basic use of spreadsheets is well understood; however, many sheets get designed and used which are overly complex and prone to data entry errors. By following some of the basic tips in this article you can avoid many of these issues.

 

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: Time Display
Viewed 2107 times since Fri, Nov 9, 2007
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2099 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2351 times since Fri, Nov 9, 2007
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 2056 times since Fri, Nov 9, 2007
CR: CHARTrunner and Open Office
Viewed 2432 times since Fri, Nov 9, 2007
CR: Formatting an Excel Date
Viewed 2497 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2582 times since Fri, Nov 9, 2007
CR: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2242 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2116 times since Fri, Nov 9, 2007
CR: Making a chart transportable
Viewed 2149 times since Fri, Nov 9, 2007