Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel spreadsheet with several years of daily data in the following form:

Date       | Category1   | Category 2  | ...   | Category30
dd/mm/yyyy |  value      |  value      | ...   |  value 
...        |  ...        | ...         | ...   | ...

I also have a list in the following form, where each of the categories from the first table is assigned one of five Type descriptions:

| Type  | Category   |
| Type1 | Category1  |
| Type2 | Category2  | 
| Type2 | Category3  | 
| ...   | ...        | 
| Type5 | Category30 |

I want to be able to organize and summarize these data using a Pivot Table, and plot the time series using a Pivot Chart. However, because of the way the data are tabulated I cannot group them by category, type or sequential month (I am interested in charting the data by month/year instead of grouping them by only months across years).

If I ordered the data like in the following table, all my problems would be solved, but I would be duplicating several column's values as many times as there are categories (and I am very much opposed to duplicating data):

Date        | Year  | Month  | Day  | Type    | Category    | Values
date(i)     | yyyy  | mmm    | dd   | Type(j) | Category(k) | value(i,j,k)

My question is, how should I design my table so that I can quickly handle the data with pivot tables without having to duplicate all the other information?

share|improve this question
up vote 4 down vote accepted

For a PivotTable to work as expected, you must arrange your data like a database: a row is a record, and a column is a field providing information on that record.

So first off, having several columns containing multiple values for a specific date appears wrong by my understanding of the situation. That translates to having many records (values) crammed into one record. By definition, having several columns for different categories prevent you from using this data meaningfully in a PivotTable.

If you can modify your source data on a permanent basis, here's a suggested layout:

 | Date       | Category | Value
 | 01/01/2011 | 1        | x
 | 01/01/2011 | 2        | y
 | 01/01/2011 | 25       | z
 | 01/02/2011 | 1        | x

To which you can append the TYPE by using a VLOOKUP formula that fetches the proper type for your category to avoid duplication and automatically reflect any change in your TYPE table. (Note that this assumes inverted columns in your type table: CATEGORY | TYPE for VLOOKUP to work)

 | Date       | Category | Type                           | Value
 | 01/01/2011 | 1        | VLOOKUP(B2, 'TypeTable', 2, 0) | x
 | 01/01/2011 | 2        | VLOOKUP(C2, 'TypeTable', 2, 0) | y
 | 01/01/2011 | 25       | VLOOKUP(D2, 'TypeTable', 2, 0) | z
 | 01/02/2011 | 1        | VLOOKUP(E2, 'TypeTable', 2, 0) | x

Such a table would contain lots of rows but feeding this to a PivotTable will allow you to manipulate it in every way: by date, by category, by type.

In fact, your source data would be a good RESULT of a PivotTable summarizing values by date with a column label on category! And God knows it can be hard to take a PivotTable result and translate it back to data that can then be used to make another PivotTable. Solutions including manual labor, macros, or a bunch of intermediate formulas.

Hope that helps.

share|improve this answer
+1, great answer @mtone. Your last comment about the source data being a good pivot result is spot on. @Ricardo, you said you don't want to duplicate data but for pivot tables to work it is necessary. You get greater flexibility to manipulate your data at the expense of duplicating a lot of it. You can find instructions to unpivot your source data here – Mike Fitzpatrick Apr 27 '11 at 22:42
I agree that my source data would actually be a good result of the Pivot Table! That, along with my inability to come up with a simple way to group the data in a meaningful way made me consider the benefits of creating a proper database. I'm currently halfway through rearranging the data and so far (except for the unavoidably large number of rows) things are looking good. Thanks! – Ricardo Apr 28 '11 at 13:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .