The data labels in Excel charts are flexible and powerful. While you can easily use them to display values from source data, you can also display values from other cells. The video below shows how to leverage this feature to dynamically display the minimum and maximum value in the data, using formulas.
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.
In this video, we look at how to build a chart that automatically includes new data, and dynamically highlights the top and bottom values. This is an easy way to create an interactive chart for dashboards and other reports.
If you want to do something specific when a cell equals this or that (i.e. is equal to X or Y, etc.) you can use the IF function in combination with the OR function to run a test, then take one action if the r esult is TRUE, and (optionally) do...
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...