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 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:

multiple rows for headings 

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

wrap text to fit text into 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.

total patiend sample data

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. 


So, these are just some simple ways to make your data life easy or hard. It's up to you to implement them.

Isn't it Time to Upgrade Your Excel Charting Skills?

Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?

primitive charts
Primitive Charts
smart charts
Smart Charts

Stop Struggling with Excel Charts!
Start creating Smart Charts in just minutes.

Download a free 30-day trial. Get Smart Charts for Excel now!

Smart Charts Included in QI Macros for Excel