Analyze in Excel, present in PowerPoint - this is the old rule of communication using office files. PowerPoint allows for a linear flow of information, which often makes the data easier to digest and understand, especially for people not working day to day with raw data (like sales people, executives, etc.). Let's take a look how to take an analytical Excel data file as source for perodic sales report created in PowerPoint.
To see, how this works, let's take a look at the following example.
Product sales report example - Excel data and PowerPoint template files
Excel data file
A quick preview of the file in an image format:
This is an example of an analytical Excel data file, where an analyst works with raw data, then transforms it by creating pivto tables, inserting various types of charts and aggregates data as they see fit for their purpose. What you will find in the file:
- raw sales data by product and month
- a pivot table which will be used as base data source for our charts
- tables of data which we would like to show along with the charts (each of the tables has a name / is formatted as an Excel table so we can reference them in our PowerPoint template, also column names can be later used in the template as well)
- chart titles (as named ranges so we can address them in our PowerPoint template)
TIP: To easilly access the "Name manager" in Excel to see the names of the named ranges and tables, press Ctrl-F3 to bring up the "Name manager" dialog, which will list all the named ranges and tables in the file.
You can download the file from here and examine its content.
PowerPoint template file
It can be easy to get lost in the analytical Excel file when you are not the author. That is why for the analyst to convey their work to others, they often create a PowerPoint template, which is used to create a report. To better visualize the content it's required to show key data tables and charts which are related to a single subject. PPTXMailMerge now supports inserting various types of charts and additionally allows you to take full control over how the table data will be displayed - you can wrtie your own headers, choose the order of columns and which columns will be displayed in the table.
Additionaly, by leveraging the the power of Excel addressing capabilities supported by PPTXMailMerge, you can address specific cell or named ranges, which allows to insert additional dat ainto chart titles, notes, or or other slide content (text boxes, images) so that your Excel file can be used as the complete source of the report. Thanks to all those features a once prepared PowerPoiint template and corresponding Excel data source can be used multiple times to create an instant report (e.g. weekly, monthly, quarterly, etc.)
Let's examine the contents of the PowerPoint template file.
In the slides you can find:
- a title with a placeholder
- in the first two slides you will also find that you can insert placeholders into the notes of the slide
- the tables and charts are just placeholder objects, which will be populated with data from the Excel file
- chart titles can also contain placeholders
- you can use different types of charts - bar, column, line, pie - with different versions - stacked, clustered, 100% stacked, 100% clustered, etc. (this is dependent on the placeholder chart type you insert into the template)
- to tell the engine, which data to use for a given table or chart placeholder, you can use the following syntax:
for tables:
{{type=table data=xlsx.Sheet1!Table1 columns="months,chips,chocolate"}}
TIP1: To easilly access the alt-text of the table press Alt-F10 to bring up the "Selection pane", then select the placeholder table object and press Shift-F10 to bring up the context menu, then select "View Alt Text" to edit the alt-text of the table.
TIP2: You can also filter the data displayed in the table by using the dataFilter attribute. This powerfull feature is explored in another article: Influencer reporting with Excel and PowerPoint.
for charts:
{{type=chart data=xlsx.Sheet1!Table1}}
The "Sheet1!Table1" is a table from the Excel file, which is named "Table1". In successive slides you can see how can you alter which columns and in which order they will be displayed in the PowerPoint table. You can give your custom headers (independently of the source data).
You can reference different tables and sheets - if you place some data you would like to use in a table or a chart in "Sheet2", just address it using an expression like this:
{{type=chart data=xlsx.Sheet2!Table45}}
If you give a different name to the sheet - just use it - assuming you chart data resides in a sheet called "ChartData", you can address it like this:
{{type=chart data=xlsx.ChartData!Table45}}
For charts you can also address specific cell or named ranges, which allows to insert additional dat ainto chart titles, notes, or or other slide content (text boxes, images) so that your Excel file can be used as the complete source of the report. Thanks to all those features a once prepared PowerPoiint template and corresponding Excel data source can be used multiple times to create an instant report (e.g. weekly, monthly, quarterly, etc.)
Generating the report with PPTXMailMerge using the above files as source
When you have the files setup as described above, you can generate the report by uploading the Excel data file and the PowerPoint template file to the pptxmailmerge.com - XLSX PowerPoint merge tool.
ATTENTION: One thing you need to remember is to choose the option: "Merge data with slides" in the "How to process the data" section - otherwise our rendering engine will try to repeat the template using the first table it finds (the raw data table).
After submitting the files you will get instantly a PowerPoint file with the report. You can download the file from the tool, preview it and edit it if needed.
In this way you can automate your reporting workflow to save yo from multiple hours of manual copy-pasting and setting up the slides manually. In the following reporting cycles you just update the data and the PowerPoint will be ready in just a few clicks.
You can download the PowerPoint template file from here and the Excel data file from here.