How to add a search bar and search functionality to a scrollable table on a Microsoft Excel Dashboard
More than 2 years back my friend and Microsoft Excel MVP Chandoo gave me the opportunity to publish a series of 6 articles on how to implement interactive Microsoft Excel dashboards on his excellent blog Chandoo.org: KPI dashboards with Microsoft Excel. To my surprise these articles still seem to be very popular over at Chandoo.org and Chandoo’s readers even keep on commenting these posts.
Yesterday Milind raised an interesting question: Is it possible to add a search bar to the dashboard to look up values in the source data and automatically scroll up or down to the according row of the search result?
Although Hui already explained how to do this in a comment, I decided to elaborate a little bit on the possible options in this additional article here on Clearly and Simply. Today’s post describes 2 different techniques on how to add a search functionality to a dashboard with a scrollable data table. As always, including the Microsoft Excel workbooks for free download.
The challenge
The original post KPI dashboards with Microsoft Excel described a very simply, yet effective technique to show an extract of a larger data table on an interactive dashboard using a form control scrollbar and OFFSET formulas. If it is a very large database, a search functionality can be a very useful additional interactive feature: it enables the user to immediately jump to a certain part in the data table without being forced to scroll up and down.
Option 1 – Use a cell to enter the search string
Here is the obvious idea: define a cell as the search string input field, use a MATCH formula to find the entered search string in the raw data and add some lines of VBA to overwrite the target cell of the scroll bar with the result of the MATCH formula. Here is the step-by-step description of what to change in the original workbook:
- Add a cell to your dashboard: the input field for the search string
- Assign a name to this cell (“mySearchString”)
- Use a new cell on the worksheet “Control” and type in the following formula:
=IF(ISNA(MATCH(mySearchString,Data!$C$5:$C$194,1)),1,
MATCH(mySearchString,Data!$C$5:$C$194,1)+
IF (ISNA(MATCH(mySearchString,Data!$C$5:$C$194,0)),1,0))
Here is this formula in pseudo code:If {neither the exact string nor a value less than this string can be found} Then
{1}
Else
{position of the largest value less or equal than the search string}
IF {the exact string can not be found} Then {add 1}
End IfThe formula returns 1 if nothing similar to the search string can be found, the position of the search string, if an exact match can be found and it adds 1 to the position of the closest match, if the exact string couldn’t be found.
- Define another new cell on the worksheet “Control”, assign a name (“myOverwritePosition”) and type in an MIN formula to prevent the overwrite position from becoming larger than the defined maximum start position.
- Add the following Sub to the code of your worksheet “Dashboard”:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("mySearchString").Address Then Call UpdateValues
End Sub - Finally insert a new VBA module and copy the following Sub to it:
Sub UpdateValues()
Range("myStartPosition").Value = Range("myOverwritePosition").Value
End Sub
That’s it. A couple of additional cells, 2 formulas and 2 small VBA Subs. Here is the workbook for free download:
Download Dashboard Table Scroll and Search simple v01 (Microsoft Excel 2003, 141.5K)
Option 2 – Use an ActiveX textbox to enter the search string
A slightly different approach, but only a few changes are needed:
- Insert an ActiveX text box on the dashboard
- Add another cell to the Control worksheet and assign a name to it (“mySearchString”)
- Replace the existing Sub UpdateValues by the following code:
Sub UpdateValues(SearchString As String)
Range("mySearchString").Value = SearchString
Range("myStartPosition").Value = Range("myOverwritePosition").Value
End Sub - Replace the Sub Worksheet_Change by a Sub called TextBox1_Change:
Private Sub TextBox1_Change()
Call UpdateValues(TextBox1.Value)
End Sub
That’s it. Same functionality like option 1, but one main advantage: this approach enables a “search as you type”: if you enter only one character, the scroll bar already jumps down to the first entry starting with this character. For instance, entering a “G” already takes you to Gabon, and after an “e” and an “r”, Germany is displayed in the top row of the dashboard data table on the fly: 
Here is the workbook for free download:
Download Dashboard Table Scroll and Search advanced v01 (Microsoft Excel 2003, 145K)
The Caveat and its Solution
There is one drawback coming with the solutions described above: they are only working properly if the categories (in our example the country names) are sorted alphabetically in the source data. If this is not the case, we have to change the search formula:
=IF(ISNA(MATCH(mySearchString&"*",Data!$C$5:$C$194,0)),1,
MATCH(mySearchString&"*",Data!$C$5:$C$194,0))
The trick is simple: we let the MATCH function look for an exact match by using a zero as the third parameter of the function. Furthermore we concatenate an asterisk to the search string. You probably know the functionality of using wildcards like “*” and “?” in your file searches with Windows Explorer. This functionality is available in Excel’s LOOKUP functions and in the MATCH function as well. If the user types in “Ger”, for instance, the MATCH function looks for “Ger*” and will find the first entry in the database starting with these 3 characters regardless the sort order of the raw data.
Here are the 2 workbooks using this alternative MATCH formula for free download:
Download Dashboard Table Scroll and Search simple v02 (Microsoft Excel 2003, 141.5K)
Download Dashboard Table Scroll and Search advanced v02 (Microsoft Excel 2003, 145K)
What’s next?
At the end of the recent article I announced a follow-up post to Export Microsoft Project Tasks to Outlook. I am still working on that and the article will definitely come during the next few weeks. In the meantime, however, I am planning to write an amendment to Bluffing Tableau Actions with Microsoft Excel, showing a way of how to add better chart tooltips to the World Map visualization.
Stay tuned.
Leave a Reply