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. 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:
- 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. The output worksheets may be located in separate Excel files.
- 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.
- If the template specifies multiple charts, Xcelential will generate as many charts as required.
- It repeats steps 5 through 8 for each additional view of the data that it “memorized” pivot-table settings for.
- It generates all defined reports in each run.
Learn how to mass produce Excel Charts with Xcelential Pro. Download a free trial version and a step-by-step tutorial.
June 24, 2009
Posted in: Charts, dashboards, excel charts 
No Comments
Dashboards and Reporting for Excel Pivot Tables
Dazzle associates with dashboards and insightful reports automatically generated by analyzing data spread over multiple spreadsheets (save time and effort, too).
Excel pivot tables and pivot charts are great tools for one-time analysis of data tables in a single spreadsheet. But, without automation, you can’t use them to share your findings with others, or to do repeated analysis as new data arrives.
“I used to spend hours preparing a monthly performance report for each project manager. I had to manually copy data from each page of a pivot table into a separate file for each PM. I had to adjust rows, columns and charts to match changes in the size of the data. With Xcelential, I do it all within a minute.” — Judy
Xcelential Pro is software that enhances Excel, making it possible to do any and all of these without macros or coding:
- Consolidate data from multiple, updated versions of a spreadsheet
- Feed the new consolidated data to pivot tables for analysis
- Place the results of those analyses into your own custom-formatted reports with any number of charts
- Update a dashboard from those reports
Xcelential Pro users are wowing their associates and managers by creating insightful Excel reports and dashboards that no one had the time, skill, and patience to do before. And they’re doing it effortlessly.
“I used pivot tables to analyze the performance of our Adwords campaigns for our weekly reviews with the Marketing Manager. But he was uncomfortable with pivot tables, and didn’t like the formatting. It took me over 30 minutes a week to update spreadsheet reports from the pivot tables. Xcelential automated the process. What a relief!” — Robert
Xcelential Pro users are wowing their associates and managers by creating insightful Excel reports and dashboards that no one had the time, skill, and patience to do before. And they’re doing it effortlessly.
Sign up for free lessons and free trial software so you can learn how to handle Excel challenges like these in minutes.
June 24, 2009
Tags: consolidate data, dashboard, Excel, pivot charts, reports Posted in: Charts, dashboards, excel pivot tables No Comments