Imagine you have a table of monthly results. Imagine that this table is really large and that it’s difficult to distinguish the results that you really want to see. Instead of having rows and rows of data that is difficult to look through, you can add conditional formatting, sort the data and add a scroll bar to allow you to see the results you really want to see.
Step 1: Make the developer tab visible
If it isn’t already visible, you will need to make the developer tab visible on the ribbon UI.
Step 2: Create the basic data tab
- Firstly import your data into an xlsm file into a tab called “data”. (See attached file: example data table with scroll bar)
- Then add four rows above the data.
- In cell A1 type “Slider Calculation” as a title
- In cell A2 type “Start Position”
- In cell A3 type “Maximum Position”
- In cell B2 type 1
- In cell B3 type =COUNTA(A6:A3000)-9 (You can of course adjust this if you have more than 3,000 rows of data)
These cells will be used to manage the slider bar once it’s added.
Step 3: Add the button & vba to sort the table automatically
- Click on Developer > Insert > Button
- Draw the button on the sheet inside the top few rows.
- Right click on the new button and choose “Edit Text”
- Type “Update table”
- Then click on Developer > Macros
- Type in the name “Macro1” and then click on the create button
- Once visual basic has opened, copy and paste the text below.
‘ Macro1 Macro
ActiveWorkbook.Worksheets(“Data”).Sort.SortFields.Add Key:=Range(“F6:F43”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
- Save the macro and exit visual basic. (Please note, I’ve written the macro so that it sorts the data by column F from minimum to maximum and that the table ends at row 43 and column H. You can of course alter this to meet your needs.)
- Right click again on the button and choose assign macro
- Ensure macro 1 is selected and press the OK button
- Test the button and ensure that it works.
Step 4: Create the Dashboard tab
- Then create a new tab called “Dashboard”.
- In cell A2 type =OFFSET(Data!D6,Data!$B$2,0,1,1)
- D6 is the first line of data of the first column of data you wish to display (I’ve chosen the person’s first name).
- In cell A3 type =OFFSET(Data!C6,Data!$B$2,0,1,1)
- C6 is the first line of data of the second column you wish to display (This time I’ve chosen the surname).
- Repeat this configuration until you can see the first line of data you wish to see in the order you wish to see it.
- Highlight the entire row and position the cursor over the bottom right corner. Click and drag the cursor until you can see 10 rows of data.
- Add your titles and units to the table as needed
- Insert a column for the scroll bar.
- Click on Developer > Insert > Scroll bar
- Draw the scroll bar in the empty column, then re-size the column to fit the slider width.
- In cell D1 type: =IF(Data!$B$2>1,”▲”,””)
- In cell D12 type: =IF(Data!$B$2<Data!$B$3,”▼”,””)
- (You may need to copy and paste the symbols from character map or similar)
- These two formulae will display and up and down arrow to let the user visually see if there is more data above or below the current lines showing.
- Right click on the slider bar and choose “Format control”
- Add the data as shown above:
- Current value: 1
- Minimum value: 1
- Maximum value: (value from Data!B3)
- Incremental change: 1
- Page change: 10
- Cell link: Data!$B$2
- Then press the OK button.
Step 5: Conditional formatting
- Add the lines and borders to the table as you wish
- Then highlight the table and click on Home > Conditional formatting > New rule
- Then select “Use a formula to determine which cells to format”
- In the rule description type: =$F2>=0.041666666667 (the $ sign tells the rule to always look at column F regardless of which column it is formatting. I’ve used this number as it is the value of 1 hour as a decimal value in Excel.)
- Then choose the format you wish to apply. I added green text and a light green fill
Your table should now be complete. Make sure you save it as an xlsm file to allow the macros to run. Don’t hesitate to download the file below or post a comment if you need further explanation.