The great thing about this customization is that you can apply this technique for almost any form of formatting provided you can write the logic as a formula.
For this example, we want to have the bar with the largest value displayed with a different color. If there is a tie for the largest value, then all maximum valued bars need to have the different color.
This will be done to the right of the data in a second data preparation area. These results will display bars for the largest value in the first chart.
- Enter a title for the new data in cell F2 named “Max Condition”.
- Enter the following formula in cell F3 and repeat the formula down to cell F7.
=IF(B3=MAX($B$3:$B$7), B3, “”)
Next, we need to add the new calculations to the first chart.
- Select the first chart click Chart Design (tab) -> Data (group) -> Select Data.
- In the Select Data Source dialog box, click Add under Legend Entries and enter the below parameters in the Edit Series dialog box.
The newly added data series appears as follows:
- To get the new bar to appear in front of the companion bar for that same product, select Format Data Series -> Series Options -> Gap Width and set the value to 100%.
- Set the color of the newly added bar by selecting Format (tab) -> Shape Styles (group) -> Shape Fill -> Blue (or any color you prefer.)
Notice that in the event of a tie for the highest value, all maximum bars are colored.