Lesson 4: How to Generate Multiple Excel Reports that Show Different Views from a Pivot Table
Problem:
You need to regularly “slice-and-dice” spreadsheet data, and publish multiple Excel spreadsheet reports, each of which is based on a different view of the data.
As we saw in earlier lessons, Excel pivot tables allow you to do the slice-and-dice analysis, so you can use that to produce all the views you need. Unfortunately, a pivot table shows only one view of the data at any time. How can you produce all the different reports you have to make, from just one pivot table?
You could work around this limitation by creating one pivot table for each view, and distributing all those pivot tables. But your audience doesn’t want to have to work with pivot tables. They want polished reports they only have to read.
That means you still have the problem of transferring the data from the pivot tables to the different reports.
Challenges:
As we saw in the previous lesson, it is very difficult to manually transfer data from those pivot tables to a regular Excel worksheet. You will have to re-format the report worksheet extensively, rework the charts, and review the formulas. You will have to do that for every single report.
Solution:
You can do this manually with plain Excel. However, each additional report would be just as hard to make as the first. The manual method for creating each report was described in the previous lesson. Doing all that manual work for multiple reports is seldom productive.
A more practical alternative is to use Excel VBA to write a program that automates the process for a specific set of reports. The kind of coding required is outside the scope of this lesson. All we can say is that it will require significant programming skills and time to implement such a program.
Alternatively, you can use Xcelential Pro together with Excel. Xcelential Pro automatically does the following:
- It creates an analysis spreadsheet by copying the template you selected.
- It opens all the source spreadsheet files you selected from a list it presents to you.
- It locates the data you want consolidated in each file.
- It copies the collected data to the file it created.
- It creates the pivot table and sets up the pivot fields, filters, and sorts based on the settings it "memorized".
- It uses an Excel worksheet you designate as a template for the desired output.
- It moves the data from the pivot table to the templated Excel worksheet, while preserving the integrity of the design elements, formulas, and charts of the target worksheet, even if the number of rows and columns has changed.
- It repeats steps 5 through 7 for each additional view of the data that it “memorized” pivot-table settings for.
- It generates all defined reports in each run.
Specifics:
The manual method to generate multiple Excel reports from a pivot table involves doing all the steps described in the previous lesson, so we won't repeat all that, here.
In contrast, doing the same thing with Xcelential Pro requires no additional steps at all, after a one-time set-up. Xcelential Pro completely automates the process of generating multiple Excel reports from pivot tables, as part of the process of consolidating data from multiple versions of a spreadsheet, and creating pivot table views of the data that we saw in the previous lesson.
Xcelential "memorizes" the various pivot-table settings, and "replays" them, then transfers the data to your custom-formatted report.
All you need to do is select the files whose consolidated data you want to analyze. Then, distribute all the report worksheet that Xcelential Pro will generate.
Click here for a complete step-by-step tutorial in pdf format.
If you have not yet downloaded the program, click here.
Coming Up:
We have seen how Xcelential Pro automates generating multiple custom-formatted reports from a pivot table in a single run. In the next lesson, we will see how you can use Xcelential to generate multiple charts within a single report.
And after that lesson, there will be a lesson on: how to generate a bunch of Excel reports for different people or departments at one time
Was the above helpful? Feel free to enter comments and suggestions in our forum. If you have a specific Excel task you want help with, ask us!
