How to Mass-produce Excel Charts

Problem:

You need charts to help you and your audience see trends and relationships in an Excel spreadsheet’s data. However, there is too much data to show in a single chart. So, you need to create many separate charts for the report (as in the example below).

In addition, the size of the data varies with each report run, so some charts need to be added or deleted each time the data changes.

Creating a single chart is tough enough. Creating multiple charts can be a terrible chore, because it takes a lot of effort to create each chart, and to set their data ranges properly. We know staff members who would spend several hours each month preparing a report with a chart showing key indicators for each project manager.

Solution:

You can do this manually with plain Excel, by following a systematic approach. We discuss that in the this lesson's pdf file. However, it will require a lot of work. And, all this work to create additional charts is on top of all the work required to create the basic report, before creating any charts.

Alternatively, you can use Xcelential Pro together with Excel. Xcelential Pro makes creating multiple charts for a report "free" -- once the report is set up, you don't have to do anything more than you would for a report with no chart at all.

All you need to do is select the files whose consolidated data you want to analyze. Then, distribute the report worksheet that Xcelential Pro will generate.

Xcelential Pro does the following for you:

1. It creates an analysis spreadsheet by copying the template you selected.
2. It opens all the source spreadsheet files you selected from a list it presents to you.
3. It locates the data you want consolidated in each file.
4. It copies the collected data to the file it created.
5. It creates the pivot table and sets up the pivot fields, filters, and sorts based on the settings it "memorized".
6. It uses an Excel worksheet you designate as a template for the desired output. The output worksheets may be located in separate Excel files.
7. 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.
8. If the template specifies multiple charts, Xcelential will generate as many charts as required.
9. It repeats steps 5 through 8 for each additional view of the data that it “memorized” pivot-table settings for.
10. It generates all defined reports in each run.

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 and multiple charts from a pivot table in a single run. In the next lesson, we will see how you can use Xcelential to generate multiple reports in separate Excel workbooks in a single pass.

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!