Skip main navigation

New offer! Get 30% off one whole year of Unlimited learning. Subscribe for just £249.99 £174.99. New subscribers only T&Cs apply

Find out more

Finishing tips 1

Hiding sheets and removing spreadsheet junk will improve the robustness of your dashboard. Watch Paula explain and demonstrate more.
Before we finish up on this course, I want to go through some finishing touches that you can add to your dashboards. If your dashboards are multiple pages, you can link all these pages together, and then, you can tidy up sheets by hiding things. So, let’s talk first about hiding all of your sheets. All of these worksheets that we have in here, except for the data, we can actually hide. We don’t need the users to be able to see them. So, we can go ahead and we can select all of our worksheets that aren’t needed and then we can select hide.
This has hided all of those tabs that that’s just talk about all of the tabs that we had in this course. A lot of this would be considered spreadsheet junk and I mentioned spreadsheet junk to you in an earlier video. We had a different worksheet setup for each chart on. That’s great for teaching with but it’s not always practical when you’re creating and designing a dashboard. The reason being, a lot of the figures that we used we used over and over again from the original data set. So, there’s no need to keep pulling these into different sheets all of the time.
If you have one calculation sheet and on one calculation sheet carry out all of your calculations and then you can put all the charts on that sheet, set up a copy of your wire frame on the same sheet and then just take a copy of that. So, you only have one sheet to hide. Now, sometimes that’s not always practical. It’s also a good idea when you are creating a dashboard for an organization, or as a project, for somebody else that your dashboard workbook includes some sort of introduction or control sheet or maybe an overview sheet that will explain to the user, On this, you will find such and such.
on this sheet, you will find such and such and then instructions on how to use. For example, please update the new data on the actual sheet on a monthly basis with the new figures. Please add in the data to division 1 and division 2 as the group will automatically update. Then, press refresh and your dashboard will totally update. So, you can have a sheet that explains to the user how to use things. So, that is one tip. Now, we also hid sheets there but it’s very easy for somebody to unhide sheets. All I have to do is select unhide and as soon as they select unhide, the sheet is unhidden. Now, what’s the problem with this?
Well, the problem with this, is that your work can then be easily deleted or lost or overwritten in error. So, you have another option for hiding sheets to make them less easy for users to find and mistakes to actually happen. To do this, you need to go to the properties tab. So, if you go to your developers and in your developer, on your sheet, select properties. In properties, down the bottom here, you have this visible. And we can see that it is currently visible. Now, you can set the sheet to Hidden or you can set the sheet to very hidden. When you set the sheet very hidden, nobody can find it by just clicking. So, let me show you.
If we click down here and we say on hide, the sheet that we have now put it in as very hidden is no longer available for a user to unhide. So, you can go ahead and make all of your sheets very hidden if need be. [END]

In the completed workbook you will find a copy of the Introduction sheet shown in this video. Hopefully from the article earlier, you are also familiar with the ability to hide sheets.

In the solutions workbook that you will be able to download at the end of this week, you will find an instructions sheet and the other tips implimented.

Comment below

What are your main takeaways from this video

This article is from the free online

Design and Create Custom Dashboards in Excel

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now