Home » QI Macros Tips » Organize Data in Excel

How to Organize Data in Excel

People sometimes make their data hard to chart or analyze by setting it up incorrectly. Ease and difficulty are determined by:

Horizontal versus Vertical layout
Headings
Formatting Mistakes
Summarizing

Horizontal or Vertical?

Since most calendars show time horizontally, not vertically, most people tend to show data organized by dates and times horizontally. QI Macros will draw the chart either way, but the best way is vertically. Why? Because there's a lot more rows than columns available. You are limited to 255 columns horizontally:

Organizing Excel Data Vertically vs Horizontally

Given a choice, start with vertical data.

Column and Row Headings

Having settled on vertical, it's time to think about headings. Every column and row should have a heading:

every column and row should have a heading

Column Heading: QI Macros will attempt to use whatever is in the column heading for chart tiles, so make the title descriptive.

Row Headings: If the data is organized by time, then the row headings should be:

Dates
Times
Batch or subgroup (as shown above)
(If this is a number like 1,2,3 consider using an apostrophe in front of the number to force it to be text. That way QI Macros cannot be confused whether it's data or a label.)

If the data is a category like type of defect, then use descriptive labels for each category instead of codes (e.g., FF for folded flaps or BF for Bent/Damaged Flaps):

Defect data in Excel

As shown in this example, use color for visual clarity.

If your data has a numerator and denominator, put them in adjacent columns with a leading heading (e.g., discrepancies/sample size):

discrepances and sample size in columns

Common Heading Mistakes
The most common and troubling mistakes are:

Using multiple rows for headings:

Instead, right click on the cell and use Format-Cells - "Wrap Text" to fit the text into the cell:

Formatting Mistakes

Don't merge cells like " Circuit Board Defects" below.
You can't tell if the title is in the first or second column.
Merged cells are particularly hard to use when copying, pasting or charting data.

merged cells can be difficult when copying, pasting, or charting data

Instead of "Merge Cells" use "Center Across Selection" to accomplish the same visual result without hog-tying copy/paste/chart capabilities.

use center across selection instead of merge cells

Don't put your time series data side by side, because you will have to stack it to draw a control chart.

It's okay, however, to show category data side by side, because it's a good way to make comparisons.

Defect data in Excel

Summarizing Data

First, put all of your data on one worksheet. We've seen annual data split into quarters and each quarter put on a separate worksheet. You can't use QI Macros if your data is split across worksheets.

Second, don't put quarterly totals and grand totals in line with your data. If you do. you'll have have to select around them to draw a chart. Put them beside your data:

falls per patient days sample data properly formatted :

I know that some manager will ask to see it in a different, more complex way, but keep your raw data clean and simple. Use QI Macros "Paste Link" or "Paste Link Transpose" functions to make a linked copy of the data that you can modify to meet any need, while keeping the raw data clean.

If, for example, we take the data above and use QI Macros "Paste Link Transpose" function, we will get the following data that has links back to the original data.:

paste link transpose sample data

You can insert quarterly averages for reporting purposes, but still have access to the original data for charting.