Creating a Reverse Pivot Table

A pivot table is a summary of data in a table. But what if you have a summary table, and you'd like to create a table from it? Figure 17-8 shows an example. Range B2:F14 contains a summary table — similar to a very simple pivot table. Columns I:K contain a 48-row table created from the summary table. In the table, each row contains one data point, and the first two columns describe that data point. In other words, the transformed data is normalized. (See the sidebar, “Data appropriate for a pivot table,” earlier in this chapter.)

Excel doesn't provide a way to transform a summary table into a normalized table, so it's a good job for a VBA macro. After I created this macro, I spent a bit more time and added a UserForm, shown in Figure 17-9. The UserForm gets the input and output ranges and also has an option to convert the output range to a table.