Export an Excel Chart to PowerPoint

You can use VBA to streamline a lot of your day-to-day practices, eliminate boring tasks, and reduce tedium. A great example of a VBA project that would save a lot of time is report generation in Word or PowerPoint based on Excel content. Of course, such a project is more advanced than simply recording and replaying macros would be. I’ll use the small task of exporting an Excel chart to PowerPoint to illustrate such inter-application automation, and I’ll follow up soon with more intricate routines that accomplish more tasks.

There are a number of things you need to understand:

  • Excel’s Object Model
  • PowerPoint’s (Word’s, etc.) Object Model
  • Early and Late Binding, References, Libraries

We can learn all about Excel’s object model by recording macros and examining the code, as well as using tools like IntelliSense and the Object Browser in the VB Editor. And there’s always Google at our disposal. Search on “Excel VBA” and the particular keyword that has you stumped, or type the exact error message into the search box.

We learn about another application’s object model through VB Editor tools and of course Google. PowerPoint has Charts, Shapes, and Tables like Excel does; PowerPoint’s charts and shapes behave much like Excel’s, but its tables are much different. And PowerPoint has Presentations and Slides instead of Workbooks and Worksheets.

I’ll cover Early and Late Binding briefly, so that you can get up and running.

Early and Late Binding

When you write code, you are making use of code libraries, which contain the objects, methods, properties, and other keywords for the language you are using. For example, if you are writing Excel VBA code, you are using the Visual Basic for Applications library, the Microsoft Excel Object Library, the Microsoft Office Object Library, and others. In the VBA Editor, these libraries enable IntelliSense, which are the smart tooltips and dropdown boxes that help you get the code written quickly.

Early Binding

Early Binding is when you ensure that all libraries used in your project are explicitly identified when you are writing the code (at “design time”), by setting a reference to the library in your project (“binding” it to the project).

Early Binding enables design tools (IntelliSense, the Object Browser, etc.) in the VBA Editor to access an external library, so you can write your code more quickly.

IntelliSense ToolTip Showing Relevant SyntaxIntelliSense ToolTip Showing Relevant Syntax

IntelliSense DropDown Listing Appropriate OptionsIntelliSense Dropdown Listing Appropriate Options

Early Binding also saves time by telling the system what library to use, instead of making the system look up all the possible libraries and guess which one to use. In practice, I haven’t found this delay to be significant.

If I use Early Binding, and share my program with others, they must have all of the referenced libraries on their computer, or the language (i.e., VBA) has to be able to figure out which library to use instead. For example, if I program in Excel 2016, the project will automatically contain references to Excel 2016 and Office 2106. VBA is smart enough to use the relevant versions of the Excel and Office libraries if someone uses the project in an older or newer version of Excel. If I set a reference to PowerPoint 2016, however, VBA isn’t so smart. It will successfully use a newer version of the PowerPoint library, but if the user has an older version, he will get a compile error, and nothing will run.

Late Binding

Late Binding is when you don’t explicitly bind libraries to your project at design time, but instead let your program try to figure it out later (at “run time”).

Late Binding doesn’t help you find information via IntelliSense or the Object Browser, so development may be slower.

If a needed library is not present on the user’s computer, and you have used Late Binding, you still get an error if the system can’t find the library. However, it’s not a compile error; it’s a run-time error, and you can write code to handle it gracefully.

I believe there are some libraries which do not work unless they are specifically bound to the project that uses them, but everything I’ve needed for routine VBA automation has worked fine with Late Binding.

What Should I Use?

There are tradeoffs between Early and Late Binding. IntelliSense and other guidance that is made available by Early Binding is invaluable. On the other hand, the compile errors due to missing references are catastrophic. Many developers will use Early Binding while developing their code, setting the references they need; once the code is ready to ship, they switch to Late Binding, removing the references and making the other small changes to avoid compile errors in the code.

The Example Project

Here is a simple data set and chart in Excel.

Data and Chart in Excel

Here is a blank slide in PowerPoint.

Blank PowerPoint Slide

We’re going to export that chart from Excel onto that PowerPoint slide.

Early Binding – Setting References

To set a reference to an external, go to the VB Editor and make sure the project you’re working on is the active project. Go to the Tools menu > References, and the References dialog appears.

This dialog lists each open workbook and add-in, as well as all libraries installed on your system. This includes many that are not appropriate for VBA development, and using these may result in spectacular system failures. But any of them may one day come in handy. For example, long ago I used the WinAmp library (remember that? probably not) to build a UserForm-driven music utility, in which I could set up playlists and play music files in Excel.

References Dialog

Several references are added by default: Visual Basic for Applications, Microsoft Excel 2016 Object Library, OLE Automation, and Microsoft Office 2016 Object Library; if you add a UserForm to your project, Microsoft Forms 2.0 Object Library is also added automatically. In this view you can see that the Microsoft PowerPoint 16.0 Object Library has also been checked. If it’s not checked and you haven’t used it recently, you’ll have to scroll down until you find it, then check it.

These library references are only set for the active VB project. Other projects may have different libraries referenced. For example, I used two workbooks while working on this tutorial: one with the PowerPoint library checked for Early Binding, the other without for Late Binding.

Outline of Our Procedure

Here is the entire VBA procedure that will export the active Excel chart to PowerPoint. The code is organized like this:

  • We have declared several object variables at the top of the procedure.
    • In Early Binding, these objects are declared as PowerPoint objects.
    • In Late Binding, these objects are declared as generic objects.
  • We prevent an error if the user has not selected a chart.
  • We figure out where to paste the chart.
    • We look first for a running instance of PowerPoint.
      • If PowerPoint is not running, we start it up.
    • We look for the active presentation.
      • If there is no active presentation, we create a slide with the desired blank layout.
        • In Early Binding, we use the PowerPoint named constant ppLayoutBlank.
        • In Late Binding, we use its numerical equivalent (12).
    • We look for the active slide.
      • If we can’t find the active slide, we insert one.
      • (In older versions of PowerPoint, a new presentation started with no slides.)
  • We copy the active chart.
  • We paste the chart as a shape object in PowerPoint (it’s still a real chart).
  • We align the chart/shape on the slide.

Early Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToPowerPoint_EarlyBinding()
  Dim pptApp As PowerPoint.Application
  Dim pptPres As PowerPoint.Presentation
  Dim pptSlide As PowerPoint.Slide
  Dim pptShape As PowerPoint.Shape
  Dim pptShpRng As PowerPoint.ShapeRange
  Dim lActiveSlideNo As Long
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If
  
  ' figure out what slide to paste on
  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next
  
  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")
    Set pptPres = pptApp.Presentations.Add
    Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
  Else
    If pptApp.Presentations.Count > 0 Then
      Set pptPres = pptApp.ActivePresentation
      If pptPres.Slides.Count > 0 Then
        lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
        Set pptSlide = pptPres.Slides(lActiveSlideNo)
      Else
        Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
      End If
    Else
      Set pptPres = pptApp.Presentations.Add
      Set pptSlide = pptPres.Slides.Add(1, ppLayoutBlank)
    End If
  End If
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  With pptSlide
    .Shapes.Paste
    Set pptShape = .Shapes(.Shapes.Count)
    Set pptShpRng = .Shapes.Range(pptShape.Name)
  End With
  
  ' align shape on slide
  With pptShpRng
    .Align msoAlignCenters, True ' left-right
    .Align msoAlignMiddles, True ' top-bottom
  End With
End Sub

Late Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToPowerPoint_LateBinding()
  Dim pptApp As Object  ' PowerPoint.Application
  Dim pptPres As Object  ' PowerPoint.Presentation
  Dim pptSlide As Object  ' PowerPoint.Slide
  Dim pptShape As Object  ' PowerPoint.Shape
  Dim pptShpRng As Object  ' PowerPoint.ShapeRange
  Dim lActiveSlideNo As Long

  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If

  ' figure out what slide to paste on
  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next

  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")
    Set pptPres = pptApp.Presentations.Add
    Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
  Else
    If pptApp.Presentations.Count > 0 Then
      Set pptPres = pptApp.ActivePresentation
      If pptPres.Slides.Count > 0 Then
        lActiveSlideNo = pptApp.ActiveWindow.View.Slide.SlideIndex
        Set pptSlide = pptPres.Slides(lActiveSlideNo)
      Else
        Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
      End If
    Else
      Set pptPres = pptApp.Presentations.Add
      Set pptSlide = pptPres.Slides.Add(1, 12)  ' 12=ppLayoutBlank
    End If
  End If

  ' copy chart
  ActiveChart.ChartArea.Copy

  ' paste chart
  With pptSlide
    .Shapes.Paste
    Set pptShape = .Shapes(.Shapes.Count)
    Set pptShpRng = .Shapes.Range(pptShape.Name)
  End With

  ' align shape on slide
  With pptShpRng
    .Align msoAlignCenters, True  ' left-right
    .Align msoAlignMiddles, True  ' top-bottom
  End With
End Sub

Bits and Pieces

The Objects

We need to use the PowerPoint Application (we use the Excel one all the time in Excel VBA, but usually get away with ignoring it), plus a presentation, and a slide. The chart is pasted as a Shape, and we make it a member of a ShapeRange to align it within the slide. I’ve shown the Early Binding PowerPoint object types below; in Late Binding, these are all plain old Objects.

  Dim pptApp As PowerPoint.Application
  Dim pptPres As PowerPoint.Presentation
  Dim pptSlide As PowerPoint.Slide
  Dim pptShape As PowerPoint.Shape
  Dim pptShpRng As PowerPoint.ShapeRange

User-Proof the Code

Here we test that the user has selected the chart. If he has not, we pop up a message reminding him to select one next time.

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To PowerPoint"
    Exit Sub
  End If

Select a Chart, Dummy!

Get the PowerPoint Application

We use GetObject to find the running instance of PowerPoint. We wrap it in On Error statements so we don’t get an error if it’s not running. If it’s not running, pptApp is in fact Nothing, so we use CreateObject to start it up.

  On Error Resume Next
  Set pptApp = GetObject(, "PowerPoint.Application")
  On Error Resume Next
  
  If pptApp Is Nothing Then
    Set pptApp = CreateObject("PowerPoint.Application")

PowerPoint is a bit funny here, since it only supports one running instance at a time. We could be lazy and simply use CreateObject, and it will fetch the running instance or fire up a new one as needed. But what I have used is generally the proper way to do it, and it doesn’t hurt if we go through all the motions even if we don’t always need to.

At this point, it would be wise to test that we were able to start up PowerPoint. If we couldn’t start PowerPoint, for example, if PowerPoint were not installed on the computer, we would have to abandon the procedure without trying to use the various PowerPoint objects later in the code. We should also alert the user so he could rectify the situation.

Get the PowerPoint Presentation and Slide

ActivePresentation is probably obvious enough. If there is one, continue; otherwise create one first.

ActiveSlide is a tough one, because even though such an object would be useful, there is no ActiveSlide object in PowerPoint. The first thing you must check is whether the active presentation has any slides. Sounds dumb, but in prior versions of PowerPoint, when you created a new presentation, it started out with no slides, and you had to click in the window to insert the first slide. Presentations.Add also inserts a new presentation with no slides. So we need to add a slide to a new presentation and to an existing presentation that had no slides yet.

If the active presentation has at least one slide, we find the active slide with a convoluted line of code:

pptApp.ActiveWindow.View.Slide.SlideIndex

I think this line of code assumes certain views, like Normal view (with Slide pane, Slide Sorter pane, and Notes pane) and Slide view (Slide pane only). A more rigorous procedure would check for this to make sure to avoid the associated error.

Again, we should check that we did successfully end up with a presentation and a slide, to avoid errors later in the code.

Copy, Paste, and Align

Copying is straightforward: we copy the chart’s chart area. We could instead copy the chart’s parent ChartObject, which works exactly the same for an embedded chart, but would crash for a chart sheet.

We use pptSlide.Shapes.Paste to paste the chart as a shape (a chart’s ChartObject is the Excel Shape that contains the chart), as a member of the slides collection of Shapes.

To align the Shape, we first have to define the shape as the latest Shape added to the Slide, and then define a ShapeRange, which is a funny way to say a bunch of Shapes which is different than a collection of Shapes. Finally we align this shape range in the center middle of the slide.

    Set pptShape = pptSlide.Shapes(.Shapes.Count)
    Set pptShpRng = pptSlide.Shapes.Range(pptShape.Name)
    pptShpRng.Align msoAlignCenters, True  ' left-right
    pptShpRng.Align msoAlignMiddles, True  ' top-bottom

We could get into trouble here if the chart was not successfully copied or pasted.

The Result

Whether we use Early or Late Binding, the result is the same. Below is our formerly blank PowerPoint slide, containing our Excel chart.

PowerPoint Slide Containing Our Chart

Summary

This tutorial showed how to use VBA to export an Excel chart to PowerPoint. Both Early and Late Binding were demonstrated, and reasons were provided for preferring Late Binding for most cases.

The approaches for exporting an Excel chart to Word are similar.

Follow-up articles will cover exporting an Excel chart to Word, Exporting other content (worksheet ranges (“tables”) to PowerPoint or Word, and exporting multiple charts in one procedure, which will really reduce your daily tedium. These articles will also describe some of the code errors you may encounter and suggest workarounds, many gleaned through hours of mind-numbing trial-and-error.

 

Peltier Tech Chart Utility

Chart a Wide Range of Values

How do you chart a wide range of values? There are numerous solutions to this, each with pros and cons. I’m showing these in Excel charts, of course, but they apply no matter what charting package you may be using.

It’s easy enough to think of an example. The following two charts show monthly sales data. One month had very high sales, while the rest of the months had low sales. (I’ll be illustrating the concepts in this article using both line and column charts, because each has special considerations.

Chart A Wide Range Of Values

In both charts, the January sales value stands proud, while the other months are hiding in the weeds along the bottom of the chart.

Use a Logarithmic Scale

The first approach to chart a wide range of values was suggested in Logarithmic Scale In An Excel Chart, a tutorial on the MyExcelOnline Excel Blog. The My Excel Online web site is run by my colleague John Michaloudis, and it features lots of great tutorials, podcasts, free training, and paid courses. John’s point was well taken, but it was incomplete. It was a short and sweet review of the technique, but it left out the thought process that should accompany any charting effort.

It’s easy enough to apply a logarithmic scale to a numerical axis in an Excel chart. Select the axis, then press Ctrl+1 to open the Format Axis task pane (or dialog in Excel 2010 and earlier). In the same view that allows formatting of scale parameters like minimum and maximum, you should find a checkbox that says Logarithmic Scale. Check the box, and the scale is adjusted. You can also change the base of the log scale, but it’s usually best to keep it at 10.

Here are my two charts with log scales. The data I’m using here is very similar to the data in the MyExcelOnline post cited above.

Logarithmic Scales

Your first thought might be satisfaction that the data are now all shown with a similar magnitude. That’s wrong, of course, because they aren’t of similar magnitude. But that’s secondary, because we are really only comparing extreme values with very little in between.

The other point about the charts above is the scale limits. Excel likes to use zero as one endpoint of an axis, and 1 corresponds to zero on a log scale. However, this choice of scale minimum means that the bottom two-thirds of the chart is blank, all of the data occurs in the top.

We can reduce the white space by entering a better value for axis minimum.

Adjusted Logarithmic Scales

There is still a problem, especially with the bar chart. In a bar chart, our precognitive sense of the values is based on the lengths of the bars, and it’s hard to overcome that. When I look at the bar chart above, the short bars all look about half as tall as the long ones, so I get the mistaken impression that the smaller values are around half of the large value. (The first logarithmic column chart is even worse, since the shorter bars are 80% as tall as the long one.) The actual ratio of values is less than one-tenth.

The log scale also tends to wash out the variability in the shorter values. In the charts below, I show the previous log scale charts, and beside them a linear scale chart with the scale blown up so the mean of the linear scale and log scale are at about the same height. Of course the large value is way off scale, which I’ve tried to indicate by fading the top of the visible indication of the large value.

The variability in the small values is shown best using the linear scale.

Linear vs. Logarithmic Scales

I should mention a couple more points about logarithmic scales.

First, as should be clear above, there is no place for log scales in bar or column charts. Bar and column charts need to include zero in the axis scale, but you can’t include zero on a log scale, since log(0) is negative infinity.

Second, most audiences will not be able to properly appreciate a logarithmic scale. Sure, engineers, scientists, and quants may have a strong enough numerical sense to interpret them accurately. But general audiences, and even smart managers and executives, are likely to be misled by such numerical transformations. It’s best to stick to the linear scale that most people are comfortable with.

Show Part of the Data

The charts used above aren’t bad, showing that linear scale charts preserve variation in the data better than logarithmic charts. Maybe we don’t need to actually show the extreme values on the axis scale, if we just indicate that they’re way off scale.

I can adjust the scale a bit more, to center the smaller values in the chart, keeping the scale minimum at zero, and fading the top of the largest value. The large value is so much bigger anyway, maybe we don’t need to actually show it to indicate that it is much larger than the others. This fading is a bit tricky, but it’s important to indicate that the data point isn’t just at the top of the chart, but instead extends far beyond.

Linear Scales Better Than Logarithmic Scales

 

Break the Axis Scale

A common approach to chart a wide range of values is to break the axis, plotting small numbers below the break and large numbers above the break. An advantage here is that it generally uses a linear scale. A disadvantage is that it distorts data, and doesn’t really give a sense for the differences in value on either side of the break.

Here are the two original charts, with a break in the vertical axis scale.

Broken Axis Scales

Bravo, you got in all the data points. But despite the visual cues that the axis has broken, there is still a strong tendency to mentally interpolate the values: those short bars still look half as tall as the large one, and the faded center and gap in the axis tell my conscious mind but not my precognitive mind that my first impression is wrong.

Sure, it’s easy enough to read the value off the axis scale, corresponding to the data point. But if you have to do that much work, what’s the point of a chart? You might as well just read the data from the cells in the worksheet.

Another drawback to breaking an axis is that it’s hard. You need to hide the real axis, construct two parts of a fake axis with a combination chart and data labels, and change at least some of the data you’re plotting. Nobody understands how to make these charts, and nobody understands the output anyway. So why bother.

The charts in the Show Part of the Data section are more effective.

Use Multiple Charts, or Make a Panel Chart

A lot of people are obsessed with getting all of their data into exactly one chart. Sometimes this is fine, but as we’ve seen above, sometimes charts with all of the data are not very easy to interpret without distorting the relationships within the data.

What’s wrong if we use two charts? We can show the whole range of data while highlighting the larger values (see the first chart of this article), then add the chart from the Show Part of the Data section which highlights the smaller values.

Two Charts to Show The Data

There are some redundant chart elements, so let’s hide the category labels in the top charts and the titles in the bottom charts.

Two Charts Beat as One

From here it’s not a big stretch to combine both charts. Plot the original data twice, once each on the primary and secondary axes, do some heavy axis formatting, and voila. Here are panel charts, where one panel shows the full extent of the data, and the other zooms in on the smaller data.

Panel Charts

In my tutorial Broken Y Axis in an Excel Chart, I explain some of the shortcomings of a broken axis scale, and I give step-by-step instructions for creating exactly this kind of panel chart. Panel charts are a little more complicated to build and maintain, but using a single chart helps with alignment and other formatting.

Panel charts are among my favorite ways to show this kind of data. But we aren’t done yet with alternatives.

Pareto Charts

A Pareto chart is a combination chart that combines a column chart (sorted from largest value to smallest), like the one that led off this article, with a line chart showing the cumulative total.

Here are two versions of a Pareto chart for this data. The Pareto one on the left is sorted by value. The one on the right is sorted by month, so the cumulative line doubles as the cumulative YTD sales.

Pareto Charts

Below is a combination of Pareto chart and waterfall chart, which I call a “Floating Pareto” chart. The bars show the incremental values and the increasing additive value. The one on the left is sorted by value, while the one on the right is sorted by month, again showing YTD sales.

Floating Pareto Charts

First and only shameless plug of the entire article: the Advanced Edition of Peltier Tech Charts for Excel 3.0 includes Pareto and Floating Pareto charts. Check it out.

Think Further Out of the Box

You always have to keep in mind why you need to plot this data. If you want to say that our sales team kicked ass in January, then the sales director left for a competitor and took with him his account book and his entire staff, you can even get away with a pie chart.

A Pie Chart?

Sure, you’re thinking, “A PIE chart? Wut? Has he lost his mind? Pie charts suck.” They’re not good at showing numerical data, and blah blah, yada yada, etc etc etc.

Well, I’ve softened a bit in my stance on pie charts, in part because of some actual research into their effectiveness by Robert Kosara of EagarEyes and Tableau (go read An Illustrated Tour of the Pie Chart Study Results, and read the papers that post links to). Seriously, the chart above practically shouts, “What the hell changed between January 31 and February 1?” There really seems little point in examining the month to month variation from February on.

If you want something slightly more quantitative, you can make yourself a nice little stacked column chart. The chart below clearly says that January sales were enormous, more than the rest of the year combined.

A Pie Chart?

Why do you want to chart a wide range of values?

As with any charting exercise, you need to ask some questions. What am I trying to learn from this chart? Who am I making this chart for? What message am I trying to give them? What’s on Netflix tonight?

If your purpose is a quick overview, then something like the Pie Chart or Stacked Column Chart might be all you need. If you’re trying to show something in more detail, the Pareto Chart, the Panel Chart, or a chart from the Show Part of the Data section may be more appropriate. If it’s detailed engineering data or scientific model predictions and your audience is highly literate in mathematics, then consider Logarithmic Scale charts.

 

Peltier Tech Chart Utility

The New Waterfall Chart in Excel 2016

This article about Microsoft’s new Waterfall Chart in Excel 2016 was written by Kasper Langmann, co-founder with Mikkel Sciegienny of Spreadsheeto, a relative newcomer to the Excel blogosphere. Kasper and Mikkel are very enthusiastic about Excel, they have written a number of tutorials on the Spreadsheeto Blog, and they offer a comprehensive seven-part free training course on Excel. Mikkel approached me to write about the new charts that Microsoft introduced in Excel 2016, and he agreed that we should write some posts together.

In the first half of this post, Spreadsheeto will specifically talk about Microsoft’s new Waterfall Chart. I will follow up with a discussion of the extensive and flexible waterfall charts in Peltier Tech’s software.

The Waterfall Chart in Excel 2016

In 2015, Microsoft released six new charts in an update for Excel 2016. This release was a direct response to user feedback and one of those new charts was the waterfall chart. The waterfall chart is a bar chart in which the bars are placed along the vertical axis at different levels according to whether they are an increase or decrease.

Then totals are shown as bars of height from zero as they are affected by the increases and decreases.

This is often useful for visualizing things like financial data where revenue can be shown as bars that shift upward vertically and expenses can be shown as bars that shift downward by contrast.

As we dive into the details of how to create a waterfall chart, note that we will work with an example scenario in Excel 2016 for Windows.

Getting Started with a Waterfall Chart – Get the Data Right

The first and foremost objective when setting out to create a waterfall chart is to make sure our data is in the correct format. Let’s take a look at a simple example of income statement data.

Waterfall Chart Data

It is important that our data is in this form where increases (income) and decreases (expenses) will be shown in the waterfall chart from left to right according to the data points from top to bottom in our table. Also, any totals (Total Revenue, Operating Income, and Net Income) will be shown in the chart as they appear in sequence in our data table.

Note that Total Revenue, Operating Income, and Net Income are subtotals along the way that will be shown as cumulative totals in our waterfall chart. This is set up to be a very intuitive progression as the chart presents revenue and expenses in the same logical fashion as our data table.

This is an instrumental point for setting up our data. You get out of the waterfall chart what you put into it and this means knowing how to set up our data before creating the chart.

Inserting the Waterfall Chart

Once our data table fits this layout, simply highlight the entire table (or one cell within the table), click on the ‘Waterfall and Stock Charts’ dropdown button…

Waterfall Chart on the Excel Ribbon

Then select ‘Waterfall’ in the menu.

Waterfall Chart on the Excel Ribbon

Alternatively, click on the ‘See All Charts’ button at the bottom right of the Insert > Charts group…

Find All Charts on the Ribbon

Then find ‘Waterfall’ in the list of available charts and click OK.

Waterfall Chart Excel's All Charts Dialog

Identifying the Totals

Initially, our new waterfall chart will be a bit unorganized and will need some specific tweaks from before it will begin to take shape and be a better visual representation of our data. Note in the legend that there are three classifications of the data bars: ‘Increase’, ‘Decrease’, and ‘Total’.

Waterfall Chart, Not Ready Yet
Notice how ‘Increase’ bars rise in cascading fashion and ‘Decrease’ bars fall in the same way along the vertical axis according to the cumulative effect each has on the running total. Right now our Total Revenue, Operating Income, and Net Income totals are each shown as an ‘Increase’. We need to change them to each to a ‘Total’.

To do this, let’s first click on the Total Revenue bar and then right click.

Waterfall Chart, Setting Subtotals

Select ‘Set as Total’ and two things will happen. First, the color of the bar will change to match the legend as a ‘Total’ and second, the bottom of the bar will be anchored at zero. That way, the bar will be a visualization of the cumulative total income after increases due to Sales and Service income. Notice now how the top of the ‘Total Revenue’ bar is the exact same height as both the ‘Sales’ and ‘Service’ bars put together.

Waterfall Chart, Setting Subtotals

Now we just need to repeat this step for ‘Operating Income’ and ‘Net Income’. This results in the chart we ultimately want. Increases (income) are show as rising bars in blue while decreases (expenses) are shown as falling orange bars.  Note the cascading visual effect of the rising and falling bars that give the waterfall chart its name.

Waterfall Chart, Setting Subtotals

Now we can make any formatting changes we want just as we would with any other chart in Excel. Then it’s ready to disseminate across your organization, to your stakeholders, or for a presentation.

Summary

The waterfall chart is a really nice addition to the family of charts offered in Excel 2016. As we have seen here, it provides a cascading visualization of data that includes increases and decreases while allowing us to see the cumulative effects on the running total in a very intuitive fashion. For the right kind of data, the waterfall chart can really bring things to life by providing an at-a-glance representation that anyone can appreciate.

The new charts in Excel 2016 can be used in a variety of scenarios. To learn more about all of them check out our guide here.

Before you begin your charting adventure, you should definitely read this comprehensive piece on what to do and what not to do when creating charts.

This article is written by Kasper Langmann from Spreadsheeto. If you like it, you should check out his free Excel training.

Peltier Tech Charts for Excel Waterfall Charts

Many years ago, Peltier Tech introduced a tutorial that showed you how to make your own waterfall charts in Excel, the slow hard way. You can even read the latest edition of the Excel Waterfall Charts (Bridge Charts), which is only a few years old.

Peltier Tech came out with a demo Waterfall Chart utility a decade ago, because even though it’s possible to build your own waterfall chart by hand, it’s much easier to select your data and click a button than to follow a long protocol with many easy-to-omit steps.

Peltier Tech has had a commercial Waterfall Chart utility on the market since 2008. Over the years it has been upgraded to run in newer versions of Excel, and to run in Excel for Mac. It has been continually enhanced, several varieties of waterfall charts have been introduced beside it, and thousands of users now depend on it. The newest edition, released to coincide with Excel 2016 for Windows and Mac, is called Peltier Tech Charts for Excel 3.0.

When Microsoft finally came out with their own Waterfall Chart, Peltier Tech said, “Nice going, that’s a pretty good start.”

Regular Waterfall

The data for the Peltier Tech Waterfall Chart is like that for Microsoft’s version, except the totals are left blank. When the program encounters a blank in the data column, it knows to calculate a total for that category. (If the totals in your input data are wrong, the chart Microsoft creates is also wrong.)

Data for Peltier Tech Waterfall Chart

Select the data, then click the Waterfall dropdown button on the Peltier Tech tab > Custom Charts group of the ribbon, and click Waterfall.

Peltier Tech Waterfall Chart Dropdown

You get a dialog that lets you select from a number of options.

Peltier Tech Waterfall Chart Dialog

When you click OK, the program inserts a worksheet with links to the data as well as columns of formulas that make sure the plot comes out right, plus a chart. Alternatively, you could have the program construct these formulas in the original worksheet. There is a checkbox and list box next to the output range, so that you can modify some of the options selected in the dialog.

Peltier Tech Waterfall Chart Output

 

The waterfall chart looks like on of the charts below. The waterfall treats the first value in the input data as a subtotal, for example, as a starting value that you are tracing through a set of intermediate operations to get a final value.

Peltier Tech Waterfall Chart - First Category is Subtotal

The second waterfall chart treats the first value in the input data as a change in value, in this case, as the first increase in revenue, coming from sales.

Peltier Tech Waterfall Chart - First Category is Change in Value

These charts are regular Excel charts, so you can reformat them, resize them, copy and paste them wherever you want. You need to be careful, however, because if you format away some of the hidden magic that makes them work as waterfall charts, you may break them and have to rebuild them.

The Microsoft waterfall charts are still not 100% user editable, depending on which build of Excel 2016 you use. On my main laptop I have the non-Office-365 version of Excel 2016, so it isn’t updated monthly with new features. I can format the colors of the Microsoft waterfall chart, and I can change the maximum and minimum of the vertical axis, but I am unable to modify the major tick spacing of the vertical axis. I also cannot modify the orientation of the horizontal axis labels. I’m sure that before long Microsoft will enable formatting of all of the elements of its waterfall charts, but for now, as I said, it’s still just a “good start”.

Rotated Waterfall

So what are some of Peltier Tech’s other waterfall charts? One of them is a Rotated Waterfall Chart. It uses the same data as the “Regular” Waterfall Chart, but it uses horizontal bars instead of vertical ones. Most people prefer the regular orientation, but some customers asked for a rotated version, so here it is. Here is the “First Item is a Starting Total” version…

Peltier Tech Rotated Waterfall Chart - First Category is Subtotal

And here is the “First Item is a Change in Value” version.

Peltier Tech Rotated Waterfall Chart - First Category is Change in Value

Stacked Waterfall

There is also a Stacked Waterfall chart. which breaks down each bar into smaller components. For example, you may want to see how several divisions contribute to your company’s performance.

The data is the same as for the “Regular” Waterfall Chart, except that there are two or more columns of values, one for each division.

Data for Peltier Tech Stacked Waterfall Chart

Each division appears in the chart with its own color, so you can track it across the chart.

Peltier Tech Stacked Waterfall Chart

Note that it’s impossible to stack negative and positive values in a stacked bar chart. The Stacked Waterfall handles this situation by replacing a multicolored stacked bar with a single bar that merely shows the net for that category. There is a Split Bar Waterfall Chart in Peltier Tech Charts for Excel (not shown here) that takes this into account, splitting each bar vertically, to stack positive values in the left half of the bar and negative values in the right.

Dual Waterfall

Another type of waterfall is the Dual Waterfall Chart, which allows you to compare two sets of data. The data set below shows the same data for two years.

Data for Peltier Tech Dual Waterfall Chart

The Dual Waterfall shows one set of data as filled bars, the other as transparent bars with a thick outline, so you can compare performance item by item across two sets of data.

Peltier Tech Dual Waterfall Chart

Paired Waterfall Chart

I wrote about the Paired Waterfall Chart recently in the Peltier Tech Blog. This is yet another way to track the cumulative effects of intermediate factors on a value or set of values.

Paired Waterfall Chart

I’m sure there are numerous additional Waterfall Charts that people could show you, but this extensive set is all that I’ve got.

Peltier Tech Charts for Excel 3.0

All of these Waterfall Charts, and several other handy chart types, are included in the Advanced Edition of Peltier Tech Charts for Excel 3.0. The “Regular” and Stacked Waterfall Charts are included in the Standard Edition.

 

Peltier Tech Chart Utility

Axis Labels on Small Charts

My colleague Patrica McCarthy, the Excel Diva, wrote in 12 Months Data – 13 Months Data in a Chart last week about a problem with Excel charts. If you make a 12-month chart, as you shrink the chart, for example to fit it into a dashboard, Excel drops off the last month’s axis label. She mentions that plotting 13 months of data will avoid this problem.

That doesn’t make sense, I thought. Excel doesn’t drop off the last category axis label on a chart. But then I thought about it. When you shrink the chart, and the labels get close together, Excel does little things to prevent axis labels from overlapping.

I’ll illustrate by showing larger charts and a really simple data set.

The default chart in US versions of Excel is 5 inches wide and 3 inches tall. The figure below shows three charts, each 1.5 inches tall, at widths of 5, 4, and 3 inches. The month labels all appear in these charts. It helps that I’ve used the three-letter abbreviations for the month names, and I’ve shrunk the axis label font size from 9 to 8 points (and I’ve also shrunk the chart title from a ridiculous default of 14 points down to 9 points).

Larger Excel Charts with Normal Monthly Category Axes

When I shrink the chart a bit further to 2.5 inches in width, Excel realizes that the axis labels won’t all fit, and it uses one of its favorite tricks: Excel has rotated the axis labels 90° (see chart below). They fit fine, but they are harder to read than horizontal labels.

Smaller Excel Charts with Potentially Overlapping Vertical Labels

You can format the labels so they are horizontal, but Excel uses another of its favorite tricks: it only shows alternating labels, starting with the first (left chart below). This is what Patricia was talking about, but when she said Excel hides the last label, I didn’t realize she meant Excel was hiding alternating axis labels. You can format the axis so that it shows every label (in other words, it uses a label interval unit of 1), but now we see that the labels are beginning to overlap (right chart below).

Smaller Excel Charts with Potentially Overlapping Horizontal Labels

How about a 45° degree tilt to the labels? Starting with the first chart, if you change to 45° labels, Excel still decides to show only every second label (left chart below). If you show all of the axis labels, they really don’t overlap (right chart below). This isn’t terrible, but I find partially rotated text as distracting as fully rotated text, and hardly easier to read.

Smaller Excel Charts with Potentially Overlapping Inclined Labels

Patricia mentioned in her article that you can show 13 months of data to make the last month’s label visible. I’ve added the prior December’s data below, and now that we start the alternating axis labels with last December, this December also appears. We still miss every second label, but seeing the last one anchors the visual more effectively.

Plot 13 Months to Show Last Month's Label

Showing both December’s gives insight into a year-over-year comparison, at least for December.

In addition to using Patricia’s workaround above, and using smaller font sizes and shorter axis labels as I’ve done from the start, there are a few other tricks you can try.

Here I’ve used one-letter abbreviations for the month names. Sure, there are 3 J’s, 2 M’s and 2 A’s, but within the context of the entire year, there’s little confusion about this, and it is done frequently.

Shorten Labels to Make Them All Fit

Quick Number Format Tip #1: If you have a date in a cell, you can display just the three-letter abbreviation if you use a custom number format of MMM, and you can show the one-letter abbreviation if you use a custom number format of MMMMM. (A single M shows a one- or two-digit numerical month, 1 for January and 12 for December; a double MM shows a two-digit numerical month, 01 for January and 12 for December; and a quadruple MMMM spells out the entire name of the month.)

You can also show alternating month labels, ending with December, as follows. Make your chart as before with the full set of month abbreviations, set it to show every horizontal label, then delete every other month starting with January. Now at least the last month’s label is visible, even if you’re only showing half of the axis labels.

Hide Alternate Labels in Worksheet to Show the Labels You Want to Show

Finally, you can show all axis labels, but prevent overlapping them, if you stagger them, showing some higher and others lower. To do this, put the cursor at the beginning of some of the cells, and hold Alt while pressing the Enter key. This inserts a carriage return (or line feed, whichever) at that point in the cell’s text. I’ve set the cell’s Word Wrap to true, so you can see the taller two-line cells in every second row below, and these labels appear one line lower along the axis. This needs more space to display, so Excel shrinks the plot area slightly, but you get the benefit of seeing all of the axis labels.

Stagger Labels with Creative Use of Carriage Returns

Quick Number Format Tip #2: To get the carriage return in a cell containing a date, use a custom number format of MMM, but precede the MMM with Ctrl+J. This undocumented feature inserts a carriage return right in the number format. This undocumented feature also only works in the number format dialog for worksheet cells, not for chart elements, which is a total bummer, because they’re at least as useful in charts as in the worksheet.

Quick Formula Tip #1: If the labels are in a column of cells, say A2:A13, you can easily get a carriage return into alternate cells. Enter the formula =A2 in cell B2, enter =CHAR(10)&A3 in cell B3, then select B2:B3, and drag the square on the bottom right of the selection down to B13 to fill the range with these alternating formulas.

Quick Formula Tip #2: If you have dates in A2:A13 instead of labels, enter =TEXT(A2,"MMM") into B2, enter =CHAR(10)&TEXT(A3,"MMM") into B3, select B2:B3, and fill down as above to B13.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.