Recently I’ve been sharing a technique in my client training that increases the payoff for making the effort to create a meaningful chart in Excel. Often we have results for different locations, regions, products, or scenarios to show. We don’t have to compare them to each other, but, for example, each region needs to report their actual revenue vs. budget and vs. last year.
It takes a lot of effort to create the same chart from scratch for each of the five regions you must report on. In the past I’ve suggested creating one worksheet with the chart for the first region. Copy the worksheet and change the data for each of the other regions. This works well but I want to offer another option that could be even more efficient.
This technique uses the INDIRECT function in Excel, a function most users are not familiar with. The INDIRECT function gets the value from the cell reference you provide. It seems like this is more work than just entering the cell reference itself. It is. So why would you ever use this function? Because it can take part of the cell reference from a value in another cell. This allows you to change one cell value and the entire chart changes which cells it uses for the data.
In the video below from a recent live session you see me demonstrate how changing the row number to use for the chart automatically updates the chart, the explanatory text and even the title. I explain how I use the INDIRECT function when creating a chart and how this method can allow you to quickly create a set of slides in PowerPoint with the resulting charts.
Consider using the INDIRECT function when building charts that will be re-used and updated. It can save a lot of time in the future.
Dave Paradi has over twenty-two years of experience delivering customized training workshops to help business professionals improve their presentations. He has written ten books and over 600 articles on the topic of effective presentations and his ideas have appeared in publications around the world. His focus is on helping corporate professionals visually communicate the messages in their data so they don’t overwhelm and confuse executives. Dave is one of fewer than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel, PowerPoint, and Teams communities. His articles and videos on virtual presenting have been viewed over 4.8 million times and liked over 17,000 times on YouTube.