One of the best things about my job is that it’s filled with challenges to keep my mind sharp. No crossword puzzles required! This week’s challenge is to create some dashboard-type output from a bunch of Excel spreadsheets.
The tip I learned this week is a scrolling spreadsheet chart. This requires the following components:
- Your data (obviously). I like to keep the data in a separate sheet within a single Excel file.
- A sequence number, which will be used by the scroll bar and will help pull in the appropriate data
- A subset of your data that will be displayed as a chart
- A scroll bar
- The chart
Here is an example of the completed chart – note the scroll bar between the chart and the cell contents. It also has a dynamic chart title.
Here are a few things to notice:
- There is a sequence # above the data, which we can hide later.
- The display data is structured horizontally under the chart, with dates going across the columns.
- The actual raw data is on a separate worksheet.
So, we are starting with a spreadsheet of raw data that contains these 3 pieces of information:
- Weekly Date
The first thing we want to do is to create a new worksheet that will hold our completed chart.
On the new worksheet, in cells B28 B29 & B30, add the headings Week, Ordered, and Received (or whatever headings are appropriate for the values in your data).
Now we’ll create the scrollbar. First, note that row 27 will hold our sequence #, which is used for the scroll bar and for some of the HLOOKUP functions. Here are the steps for the scroll bar:
- Decide how many values you wish to display on your chart. In my example above, we are displaying 12 weeks.
- Create the sequence # by entering ‘1’ in cell C27 and enter ‘=C27+1’ into cell D27. Then copy D27 to E27 through N27 (for 12 weeks).
- To create the scrollbar, click on the developer tab, then click on the insert icon and look for the scrollbar under “form controls” – shown below.
Click & drag the cursor to create the scrollbar in the row directly above the sequence numbers.
Then right-click on the scroll bar and select ‘Format Control’. Set the scrollbar properties to match your needs. For the chart above, we set the control properties as follows:
- Minimum Value: 1
- Maximum Value: 100
- Incremental change: 12
- Page change: 12
- Cell link $C$27
At this point, if you click the scrollbar, the sequence numbers should change. Next, we’ll bring in the data. There are several ways to accomplish the next steps, depending on how your data is structured. The file in my example is structured horizontally, with each week added as a new column to the right, like below (note: this data is completely make-believe).
To do the lookup, just add a sequence # above the heading row, as shown above. Then:
- Pull in the proper WEEK date with an HLOOKUP on the sequence number above the scroll bar. The table for the HLOOKUP is the data shown above (it must also include the sequence #, since that’s what we’re doing the HLOOKUP on!).
- Here’s an example of the HLOOKUP function (right). Lookup value is the sequence#, table is the data, row_index_num is the 2nd value (down) in the data table.
- Copy this function to the remaining cells on the “Week” row.
NOTE: if your data is structured vertically, just use VLOOKUP instead of HLOOKUP.
If you did everything correctly, you should be able to use the scroll bar and see the dates change as your scroll through the data.
After you have this working, just repeat this process to pull in the other values, Ordered and Received (or whatever your values are).
Since everything is keyed off of the sequence# — including the scroll bar — all of the data changes when you scroll. It’s like magic!
Now, the easy part is creating the chart…
Simply select all of the HLOOKUP data, including the Week heading and click ‘insert’, ‘recommended charts’. Choose your chart from the list. The last steps are just the formatting – see Chart Formatting in Excel. You can also hide the sequence # row if you’d like (highlight the row, right-click, ‘hide’).