fbpx Skip to content

6 Types of Advanced Excel Charts and How to Use Them

insightsoftware -
March 30, 2020

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

Advanced Excel Charts

When Excel was first released in 1985, it changed the course of business forever. Spreadsheets were nothing new at that point, even in electronic format, but Excel made them intuitive and accessible in a way that had never been possible before. Not surprisingly, the finance and accounting department was the first to realize the potential of a digital ledger, and now Excel exists less as a common business tool than a kind of modus operandi: an approach to all things.

What gets lost in this narrative, however, is that Excel hasn’t persisted for so long because it’s user-friendly. The enduring popularity has little to do with simplicity. Millions of businesses continue to use Excel on a daily basis because it is now and has always been a powerful tool for strategic finance. Users feel comfortable in the familiar Excel environment, but more importantly, that environment delivers actionable insights the accounting department can use.

Top 10 Best Practices for Financial Reporting in Excel

View Guide Now

Advanced Excel charts are a prime example. They give decision makers—from the CFO outward—a picture of financial performance that’s rich with depth and detail. These charts are full of insights that reveal the true financial strength of the company while exposing opportunities and roadblocks on the horizon. Like most aspects of Excel, the charts are designed in such a way to make encapsulating, explaining, and understanding complex financial matters relatively easy. To put it simply, advanced Excel charts showcase the most important information in a familiar, easily digestible format.

Examples of Excel Charts for Reporting

If you’re not taking advantage of this powerful visualization tool already, it’s time to start. Here are six examples of advanced Excel charts along with a quick explanation of how to use them.

1. Milestone Chart

Milestone charts mark important dates along a specified timeline. They’re useful for keeping projects in context, tracking where they’ve been, and where they’re headed. Because of that, milestone charts are particularly helpful when presenting data to an audience outside finance. If the company is trying to reach a goal by a specific date, for instance, milestone charts clearly illustrate the progress.

2. Actual vs. Target Chart

You could also call this chart “expectations vs. reality” because it highlights where the company wanted to be compared to what it actually achieved. There isn’t a default setting to create this kind of chart, but it’s simply a combination chart with adjustments to the type and formatting. Normally, comparing actuals to targets would take multiple steps and close consideration of the numbers. By putting it in chart form instead, the relationship between the numbers is instantly apparent.

3. Bullet Chart

Bullet charts are on this list because they pack a lot of insights into a very small package. As a result, these charts are popular in dashboards or reports where it’s important to communicate a lot without crowding a document full of facts and figures. A bullet chart is essentially an actual vs. target chart laid on top of a qualitative indicator (good, average, bad, etc). That way, users quickly see what the numbers are while also learning how to interpret them. In that way, bullet charts make it impossible to misunderstand what the numbers are saying while empowering the viewer to take immediate action.

4. Step Chart

Step charts show how performance rises and falls at set intervals over a specific period. They’re similar to a line chart, but instead of illustrating trends generally, they do a better job of pinpointing when dips and spikes occurred. As finance departments try to learn more from historical data to get better at predicting future events, step charts offer a valuable new perspective, uncovering insights that would have been overlooked before.

5. Tornado Charts

Tornado charts, like bullet charts, fit a lot of information into a small package. They use line charts to compare one metric measured from two sources (e.g. sales of product A at two different stores). Then they stack the charts on top of each other in descending order (sales for products B, C, and D, etc.) so that the finished chart looks like a tornado. By integrating two comparisons (between stores and between products) into one visualization, users digest a lot of information at once and see the numbers in a different light, one that wouldn’t exist using spreadsheets alone.

6. Speed-o-Meter Chart

Speed-o-meter charts look familiar to anyone with a car, and they’re helpful for the same reason: They clearly demonstrate the performance of one metric. Like bullet charts, they reveal whether a particular figure is good or bad so that users can get all the context they need in a single glance. For that reason, speed-o-meter charts are common in finance dashboards where certain KPIs need to be tracked constantly but without a lot of effort required.

6 Types Of Advanced Excel Charts And How To Use Them Small Image

Tips for Leveraging Advanced Excel Charts

Going beyond the basic Excel charts is an important first step, but filling a dashboard or report template with advanced charts doesn’t automatically elevate financial storytelling. Make sure your information is clear, comprehensive, and compelling by following these best practices:

  • Pick the Right Chart: Find the chart best suited to the data you want to use. Prioritize accessibility over visual flair.
  • Use Minimal Axes: Though it’s possible to include multiple axes in one chart, it often makes the finished product look crowded and confusing.
  • Space Bars Evenly: For the purposes of visual comprehension, make the amount of space between bars even (not greater) than the width of the bars themselves.
  • Erase Background Lines: Charts are a broad comparison tool, not a way to study minute differences. Eliminate grid/graph lines behind charts to cut down on visual clutter.
  • Minimize Styling Elements: Use a simple, consistent color scheme and keep unnecessary design flourishes to a minimum. Let the data speak louder than the chart.
  • Avoid 3D Effects: Charts that seem to jump off the screen are a perfect example of the kind of distracting design elements you want to avoid.
  • Practice Empathy: Imagine what the intended audience wants to see. Determine what information they need and how to present that information to best support their decision-making requirements.

The final tip on this list is also the most important: Use the best data available. That means incorporating the most complete, up-to-date, and error-free information possible. Just as importantly, it means accessing that data as easily as possible. If one element is out of sync—meaning it’s easy to access bad data or hard to access good data—the advanced Excel charts based on that data will be underwhelming at best and unreliable at worst.

insightsoftware understands Excel’s enduring appeal as well as the untapped potential of advanced charts and smart data visualizations. With our suite of Excel-compatible solutions, we make it effortless to collect, organize, and analyze data within an environment most professionals have worked in for their entire careers. Whether they choose to tell the financial story using charts, numbers, or both, our software transforms Excel into a powerful tool for leveraging data-driven insights. To get the most out of those insights, download our ebook: A Guide to Driving a High-Performance Organization today.