Automate PowerPoint from Excel VBA: Charts to Slides

You can build a full PowerPoint deck from Excel with a short chain of VBA macros. The foundation is a workbook where every chart reads from a dynamic named range built with the OFFSET function, so new data flows into every chart on its own. On top of that, one macro lists your charts in the order they should appear, a second creates the PowerPoint and pastes every chart onto titled slides, and a third merges in a template of title, summary and section slides. When new data arrives, the workbook recalculates and the same macros rebuild the entire deck.

The example here is a reporting workbook with 15 groups, or dimensions, such as a total dimension, a grade A dimension and a grade B dimension. Each dimension has 11 charts, so the workbook holds 165 charts. Copying and pasting that many charts by hand is slow and error-prone. The workflow below makes it completely hands free.

Make every chart dynamic with OFFSET and named ranges

This is the step that makes the whole workflow hands free. If each chart pointed at a fixed cell range, every new month of data would mean editing the ranges behind all 165 charts. Instead, every chart points at a named range that resizes itself as data is added.

OFFSET sizes the range

OFFSET takes five arguments: a reference, rows, columns, height and width. Lock the reference at cell A1, move down to the metric's row, move right to the first column of data, and use a height of 1. For example, to capture total units in row 3 with data starting in column D, you move down two rows and right three columns.

The width is what makes it dynamic. Instead of typing a fixed width, count the non-empty cells in the row with COUNTA and subtract the three label columns that are not data. When a new month is added to the right, the count goes up, the width grows, and the range extends to include it after the workbook calculates.

A macro creates a named range for every metric

The results page lists every metric in column A, about 4,500 rows in this workbook. A macro loops through those rows and creates a named range for each one, named after the label in column A and pointing at that row's OFFSET range. A companion macro deletes all existing named ranges, so you can clear and rebuild them cleanly at any time.

Two rules keep the macro running:

• Every cell in column A from the first row to the last must have an entry. The macro finds the last row by jumping up from the bottom of the sheet, and a blank cell in the list causes an error.
• Names should not start with C or R. In this workbook a dimension called credit card caused an object-defined error, so it was renamed with an X prefix.

Charts point at names, not cell ranges

Each chart series references a named range instead of a cell address: the metric's named range for the values and the period named range for the dates on the axis. Add a month of data, let the workbook calculate, and every chart extends on its own.

One set of charts becomes 165

The first set of 11 charts is built for the total dimension. Because the named ranges carry the dimension as a prefix, a macro can switch every series in a copied set from total to grade A, and a second macro updates the chart titles the same way. Repeating that for each dimension produces all 15 sets, and further macros apply your corporate colors to the bars and lines and add data labels to every chart.

Get the charts ready for slides

Check how your charts will look on a slide before you automate anything. In this workbook, two rows were deleted from each set of charts because the charts came out too tall to look good once pasted into PowerPoint. As you build your own presentations, look at the output and adjust until it looks right.

Two columns were also inserted at the front of the charts sheet. Column A is where the chart names will be listed in their presentation order.

Why chart order matters, and how to fix it

Say you want four charts on one slide. Manually, you copy one chart, paste it into PowerPoint, then copy the next, and so on. You have to think about the order: the first chart pasted might go in the upper left, the next in the upper right, and so on.

The complication is that chart names in Excel do not follow their position on the sheet. A block of charts that looks like it should run 1, 2, 3, 4 might actually be chart 1, chart 10, chart 14 and chart 15. Some sections of the sheet happen to be in order and others are not. So you cannot assume the charts will run from chart 1 through chart 165 in sequence.

The fix is a macro called list charts desired order. When you run it, it writes the chart names into column A in the proper order. Everything that follows depends on this list, so later macros paste charts in the order you actually want.

Tip: Excel lets you rename a VBA module, which helps keep a multi-step process organized. A letter prefix on each module name lines the macros up in the order you run them, and a prefix like Z pushes a module to the bottom of the list.

Add a sheet for slide titles

Next, add a sheet where column A holds the title for each slide. Type your slide titles there and the automation puts them on the matching slides.

The titles do not start at slide 1. In this build the first chart slide is slide 4, because the first three slides are reserved for a title page, an executive summary and the first section header. Those are added in a later step, so the slide numbers on the titles sheet are set up ahead of time.

Create the PowerPoint from Excel

The second macro, Create PowerPoint, does the heavy lifting. When you run it, PowerPoint opens and the charts are pasted one at a time into their positions, with titles on each slide.

For each dimension the pattern is the same:

• A slide with four charts
• A second slide with the next four charts
• Three slides with one chart each

The macro works through the total dimension, then grade A, then grade B, and keeps going until every chart on the charts sheet has been copied in. The result is around 92 slides, built with no manual copying or pasting.

When it finishes, save the file to your chosen location. In this example it is saved as course_ppt_step1.pptx, which the next macro refers to by name.

Build a sections template in PowerPoint

A deck that is all charts is hard to navigate, so the next step is a separate PowerPoint file called sections.pptx. It is saved in the same directory as the Excel files and works more or less as a template. It contains:

1. A title page, which becomes the first page of the presentation. You can change it to match your corporate color scheme.
2. An executive summary page, where you can add thoughts, commentary and the main takeaways from the charts.
3. Section splitters, one for each dimension. With 15 dimensions there are 15 sections, labeled section 1 through section 15.

These section headers go between the dimensions in the final deck, so each dimension opens with its own splitter. The VBA code combines this file with the chart deck to give a clean finished presentation.

Merge the section slides into the deck

The third macro, insertSections, opens both PowerPoint files: sections.pptx and course_ppt_step1.pptx. You need to put the full file address for the sections file in the code.

The macro reads the slides in the sections file and places each one in the right position in the chart deck. When it finishes:

• Page 1 is the title page
• Page 2 is the executive summary
• Page 3 is the first section header
• The chart slides for that section follow, then the next section header and its charts, and so on through section 15

This is why the first title on the slide titles sheet was set for slide 4. Once the three opening slides are inserted, the first chart slide lands exactly there.

At this point the deck is effectively done. What is left is interpreting the results and adding commentary, for example on the executive summary page.

Refresh the report with new data

The real payoff comes when new data arrives. Suppose it is early January and the year-end data is now available. Excel can be connected to SQL Server or other data servers, so the new data flows into the workbook and the formulas recalculate to cover the latest period. Because every chart reads from a dynamic named range, all 165 charts extend to the new month without a single range being edited.

From there, rebuilding the deck is the same three macros, run in order:

1. List the charts in the desired order.
2. Create the PowerPoint.
3. Insert the sections.

The result is a complete, refreshed deck with every chart, title and section slide in place. Two settings keep this fast and reliable. Keep the number of live formulas in the workbook small, because fewer live formulas means faster recalculation. And set calculation to automatic on the Formulas tab, so everything has recalculated before the macros run.

Control what the charts show

If you have, say, 24 months of data but showing all of them is too noisy, hide the months you do not want. You can also group those columns (the shortcut given is Alt G A G G) and collapse the group. The charts update to match.

You can also insert a summary column, such as a quarter one 2021 metric that summarizes those months, and hide the monthly columns. The charts then show the quarterly figure. Inserting a column triggers a lot of recalculation, so expect a short wait before the charts update.

Key takeaways

• Point every chart at a dynamic named range built with OFFSET and COUNTA, so new data extends all 165 charts with no range edits.
• Create the named ranges with a macro, and switch chart series between dimensions with another.
• Check how charts look pasted into PowerPoint and resize them before automating.
• Never assume chart names match their order on the sheet; use a macro to list them in presentation order.
• A slide titles sheet lets you control every slide title from Excel.
• One macro creates the PowerPoint and pastes all the charts, about 92 slides in this example, with no manual work.
• A separate sections template supplies the title page, executive summary and section splitters, and a second macro merges them into place.
• Plan slide numbers around the slides that will be inserted later, which is why the chart slides here start at slide 4.
• Keep few live formulas so refreshes stay fast, then rerun the three macros in order to rebuild the deck.