Excel Conditional Formatting with Charts – Two Examples

You may already be familiar with the Conditional Formatting tool in Excel. The amazing tool that improves how we view and visualise our data.

Well unfortunately Excel does not yet have a Conditional Formatting feature that we can apply to charts. I have great news though. There is a way that we can do it, and it is not difficult.

This blog post will look at two examples of Conditional Formatting with charts so that you get a feel for how to do it. You can then apply the same technique to whatever example you need.

Highlight a Column Based on User Selection

In the first example shown in the image below. We want to be able to highlight the column in each chart that corresponds to the product type selected by the user in cell B3.

Excel charts with conditionally highlighted columns

Continue reading

Create a Rolling Chart for Last 6 Months

When creating reports in Excel, a common requirement is to report on a rolling basis. For example, this could mean the last 12 months, the last 6 weeks or the last 7 days.

Whatever the timeframe being reported, this can mean a lot of time editing chart sources and formulas to show the right data.

This blog post looks at creating a dynamic rolling chart to show the last 6 months of data, so when new data is added to the table, the chart automatically updates to report the last 6 rows (months).

Rolling chart showing last 6 months sales

Continue reading

Highlight Max and Min Values on a Column Chart

When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.

Highlighted max and min values

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart. Continue reading

Add Drop Lines to a Line Graph in Excel

Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.

Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.

Line graph without drop lines

Continue reading

Create a Battery Chart in Excel

Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.

In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.

There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.

The image below shows a battery chart being used to show the amount outstanding on a training budget.

Battery chart in Excel

Continue reading

Animate a Chart in Excel

Animate a chart in Excel to create a cool effect for your charts. You will see the chart build itself in front of your very eyes.

The chart can be created in the usual way and then VBA is used to create the animation effect.

In this example a combo box is used to provide a way for the user to select the chart they want to see. The chart then gradually appears one data point at a time.

Continue reading

Create a Scrollable Chart for your Excel Dashboards

Creating a scrollable chart is a great trick for enhancing the functionality of your Excel dashboards. You may have years of data to display in the chart, and yet space is limited on your spreadsheet.

By adding a scroll bar to the chart, users can interact with the chart and scroll to see the data they want displayed.

Continue reading

Create a Histogram in Excel

A Histogram is used in statistics to graphically represent the distribution of data. It looks like a column chart with each column representing an interval (bin), and the column height representing the frequency that it appears.

Essentially the graph groups numbers into intervals (bins) and displays how often they appear. The graph then beautifully illustrates how the sets of numbers are distributed.

Continue reading

Excel Waterfall Chart that Handles Negative Values

The main problem people come across when working with Waterfall charts in Excel is that they cannot handle negative values. When a negative value is entered it appears below zero destroying your clever Waterfall chart.

Now there are a few utilities on the market that can help you create a Waterfall chart in a snap. But you don’t need these if you know how to work with them.

This tutorial comes from a video I watched of Bill Jelen explaining how to create Waterfall charts. This video can be seen below.

This is the best Waterfall chart tutorial I have seen. I found it easy to follow, very useful, and therefore wanted to share it with you guys.

The image below show the layout of the Waterfall chart data with the formulas shown so that you can see how it all works.

Data for the Waterfall chart

Waterfall chart handling negative values