Improve Dashboard UX in Excel! We’ll show you how to create contextual help using VBA! We’ll show you how to improve the user experience. Build elegant screen tips step-by-step.
First, we will explain how to add a useful contextual help to our spreadsheet. How to boost user experience using an interactive help system?
You will find the answers to this question after reading the following tutorial. You are looking for a solution for another business issue that can interest many Excel users or managers.
Table of Contents
Differences between UX and UI
UI is a series of screen mockups, charts, and visual elements. These controls enable a user to interact with the spreadsheet components. Design is an extremely important part of the UI. Typical user experience design elements are improving usability or enhancing customer satisfaction. End-user’s interaction with the spreadsheet or software is the key element of user experience.
Create Contextual Help for better Dashboard UX
Just a few words before we start. Let’s start with that unfortunate case when you work on a project for days, maybe weeks. You believe in rescuing the world, and in the end, there is something just not right.
It’s nice and good, you might calmly relax, but there is such a great volume of data on it that the outside user (who is your boss in most cases!) will not find his way on it. You can all agree that this is a very problematic situation.
Contextual help can dramatically improve the user experience of an interactive dashboard template. How can you help this? As a solution, you will use a little programming. Don’t get discouraged from this! You will need only minimal knowledge.
We are using Office365 Pro Plus to create contextual help. The double-click based writing into the shapes works fine.
Steps to build interactive screen tips in Excel
Step 1: On the ribbon, choose the Insert menu. Select the ‘Shapes’ option. From the figures of callouts, insert one that is appropriate for you.
Step 2: Change the default style! First, select the inserted shape. Navigate to Drawing Tools on the Format tab. Select and add your preferred color scheme to shape. If you want to apply a transparent background, don’t forget to remove the background color too.
Step 3: And what about the explanations in the bubbles? We must assign the explanations with the bubbles to enhance the user interface! It’s not necessary to insert an additional text box. Just double click on the bubble, and you can enter your description of contextual help.
Repeat this step four times. Now we have four shapes in which we will place the correct information and explanations.
Step 4: We need to merge the inserted shapes into a single object. Hold the Ctrl button and select the objects. Right-click and select Group.
Step 5: On more important things. You should add a name to the group. We do this by marking the group. Select the group then click on the Name box field. You can find the Name box on the top-left corner. Finally, we name the group, in this case, ‘HelpBox’.
Pro Tip for handling the explanation: In the earlier versions of Excel, you have to do the following. Select a chosen object. After pressing the ‘=’ key, select the cell containing the explanation that wanted to be displayed. After pressing Enter, the content of the selected cell will appear in the bubble. The assigning is done!
Step 6: Insert a help icon! Go to Insert tab (Illustrations Group). Choose your favorite icon. Click insert.
Dashboard UX and VBA macro – Start the engine!
Step 7: Write and use this simple macro below:
Sub HelpSwitcher() ' Macro to Show & Hide Dashboard Help ActiveSheet.Shapes.Range(Array("HelpBox")).Visible = Not ActiveSheet.Shapes.Range(Array("HelpBox")).Visible [valHelpStatus] = ActiveSheet.Shapes.Range(Array("HelpBox")).Visible End Sub
For this reason, we have made a very simple little macro. The name of the macro is ‘HelpSwither’. The small code created ‘HelpBox’ object in such a way. If you click on the question mark icon, calls show / hides the contextual help.
Click Alt+F11. On the VBA Editor window, create a new module and paste the code above.
Assign macro to the shape
Step 8: Finally, assign the macro to the inserted icon. The last step of the procedure is to assign the in / out macro switch to the question mark icon. The code is only a few lines, but it carries out its task perfectly.
Now right click on the icon! Select the ‘Assign Macro’ option. In the appearing window, chose the ‘HelpSwitcher’ macro and with a click, assign it to the icon.
Check the final result on the picture below:
Ways to improve Dashboard UX
Creating contextual help is not the only solution to improve user experience in Excel.
Here is our additional list if you are using large data tables.
- Freeze Top Row: Keep the first row of the table visible while you scroll down! Use this feature if you want to keep important information always on the top — for example, table headers with column names.
- Enable Horizontal Scroll: Use this function if you have large data sets, and you have important data in the first column. Go to the View Tab and choose the ‘Freeze first column’.
- Apply row styles: Frequently, we lose the focus when browsing large tables. Use alternate formatting (table styles) to keep our eyes on the main content.
- Add mouse-over tips: Use HYPERLINK function in Excel to create nice info-boxes for the details
- Use the GROUP and UNGROUP function to drill-down into details.
Contextual help in Excel is a simple but effective way to improve UX.