Abstract
Transcript
In this video, we'll look at how to highlight high and low values in an Excel chart using data labels.
This worksheet contains daily sales numbers for a small online store.
I'll plot the data in a basic column chart.
Let's say we want to highlight the highest and lowest values by showing these values directly over the bars.
This may seem tricky, but we can build a very straightforward solution using only data labels with a simple formula.
I'll work through the solution step-by step.
First, I'll enable data labels.
Now we can clearly see the sales values for each day.
However, we only want to show the highest and lowest values.
An easy way to handle this is to use the "value from cells" option for data labels.
You can find this setting under Label options in the format task pane.
To show you how this works, I'll first add a column next the data, and manually flag the minimum and maximum values.
Now, back in the label options area, I'll uncheck Value, and check "Value from cells". Then I need to select the new column.
When I click OK, the existing data labels are replaced by the labels I typed by hand.
So that's the concept. Now we need to make the solution dynamic, and pull in the actual values.
I'll start by adding the max value.
To make the formula easy to read and enter, I'll name the sales numbers "amounts".
The formula I need is:
=IF(C5=MAX(amounts), C5,"")
When I copy this formula down the column, only the maximum value is returned.
And back in the chart, we now have a data label that shows maximum value.
Now I need to extend the formula to handle the minimum value.
I could add another nested IF, but using the OR function is a bit more compact.
=IF(OR(C5=MAX(amounts),C5=MIN(amounts)), C5,"")
The formula reads if C5 is the max of amounts, or the min of amounts, return C5.
Otherwise, return an empty string.
Now the formula returns both min and max values, and the chart shows these values as data labels.
And id I replace the sales numbers with a formula to generate random values, the chart correctly highlights high and low values whenever data changes.
