Changing Visible Columns-Highlight Max and Min Values in an Interactive Chart in Microsoft Excel
This video shows how to apply conditional formatting to an interactive column chart to highlight dynamically the maximum and minimum values in Microsoft Excel. Highlighting in a chart can help to quickly focus the attention of your audience on key points, like best and worst performance. The table containing the charted values has a filter. As the filter selection is changed by the user, the highlighting has to adjust to the visible columns accordingly. This is done in two ways-either using formulas with function AGGREGATE and overlapping series (you can also use function SUBTOTAL instead) or writing a short macro using Microsoft Visual Basic for Applications. Enjoy and get inspired!
00:00 Dynamic conditional formatting in a chart
01:06 Solution using overlapping series
04:00 Adding a slicer to filter the table
04:19 Advantage of using AGGREGATE function in the formula
06:14 Solution using a macro
06:32 Setting up a dummy to trigger a calculate event
07:40 Declaring variables
08:35 Defining variables
10:19 Looping through the visible chart points
13:15 Testing the macro
14:21 Adapting the macro to your own file and the advantage of using it
For more contents like this, please subscribe to my channel
#MsExcel #ExcelTips #VBA
Screenshots used with permission from Microsoft.