You may already be familiar with the Conditional Formatting tool in Excel. The amazing tool that automatically formats values tat meet specific criteria and improves how we visualise our data. Unfortunately, there is no Conditional Formatting with charts in Excel.
However, there is a way to create a Conditional Formatting with charts effect in Excel. And the great news. It is not that difficult. A simple IF function or other to perform a test and produce the required value for the chart is all we need.
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.
To achieve this we first need to add a column to the table which will show the value if the product type is selected by the user, but show the #N/A error if it is not.
The IF function is used to perform this logical test and required actions. The NA() function is used to return the #N/A error if it is not the product type selected. This is done because the chart will not plot error values, so it essentially hides those values on the chart.
The formula below is the one entered into cell C4. It compares the product name against the one selected by the user in B3 of the Sales By Product worksheet. It then displays the value in cell B4 if it is a match, or shows the error value if not.[Read more…] about Conditional Formatting with Charts in Excel – Two Examples