Hello Excel Funs!
In this Tutorial we are going to learn how to create a dynamic heat map using interactive controls like scroll bar and conditional formatting. So let’s dive in:
In this worksheet I have a table with earnings of a company by month, from January to December, and from year 2000 to year 2017. In a dashboard the area is limited , so I will create a heat map with 4 years or columns if you prefer and with the help of an interactive scroll bar I will scroll from year 2000 to 2017. In the 2nd worksheet I have only the months, from January from December.
The first step is to insert a scroll bar. Go to Developer tab, insert button and find the scroll bar form control. Click on it and draw a horizontal scroll bar somewhere there, from A14 to E14 cell. Right click on it and select format control properties. The current value would be 0 , the minimum value would be 0, the maximum value would be 14 because I want the 1st column to have as maximum value year 2014 , the incremental change would be 1 because I want a year after year increase. Leave the page change as it is.
Now I want to link a cell with the scroll bar, because I want the data to be updated every time I click on the scroll bar. So click here, go to data worksheet, and click T1 cell.
Try it by clicking the scroll bar and see the change of the value in T1 cell.
The next step is to use INDEX formula to create the updated values for my dynamic heat map. Click B1, equal , INDEX , the array will be the table from data sheet, from B1 to S13 , press F4 to insert absolute reference and lock the cells, now for row number I will use ROW function which use the current row number 1 , 2 3 etc. Now the column number will be T1 cell with absolute reference, plus the current column which is B1, so type columns function B1 to B1 , lock the first B1 cell because when I will copy it to the next column this will be B1 to B2 etc. Close the bracket and enter.
The result is 2000 which is the 1st year of our table. Every time I increase the value from scroll bar, the year is changing. The last value is 2014.
Copy the cell to the right and then all the way down. Great! Now I’m ready to create a Heat Map. Select all the data from B2 to E13, go to Home Tab, conditional formatting, and from color scales , select the 3rd one.
Our dynamic heat map is ready! This is an excellent dashboard component that you can use.
Hope you enjoyed the lesson. If you have any questions please post them in the Q & A discussion board. Thanks for watching ! Bye!
Download the Excel File (& Sample):