Dashboard 101 – MS Excel Tips

This section includes several tips and tricks that you can use while building your super awesome dashboard.

Crab Tip 1 - Fitting things – saving some dashboard real-estate

At times the dashboard becomes large and is not visible in one view of the Excel page. While this is no big deal, it definitely compromises the look of the dashboard and we would ideally like to design a dashboard that that fits in one view of the excel page. You can do a couple of things to save some real state on your dashboard:

  1. Reduce the sheet zoom to 85%. We would ideally make the dashboard at 100% zoom and reduce it by a bit, to see if its fits the page, and then do it again. We do not recommend reducing zoom below 85% as it may compromise visibility.
  2. Another way to save some real state is to go to View> show/hide and un-check the selections against ‘Formula bar’ and ‘Headings’. This will help you save some space from the top and left of the page
Removing formula bar and headings

 

 

 

 

 

Crab Tip 2 - Don’t let the users wander on your workbook

Your dashboard may not cover all the real-estate provided by MS Excel i.e. thousands of columns and rows. This gives the scope for the user to wander here and there on the dashboard.

There are certain ways in which you can stop it and one of them is to hide all the unnecessary rows and columns. How do you do this? Pretty simple, select all the not-required rows, right click, select hide. Perform similar operation on the columns.

 

Crab Tip 3 - Prevent sensitive data leakage – clear your Meta Data

Data security and privacy is a pressing concern in the Pharma sector. All MS Excel files store some meta data such as Author, title, company name, etc.

[To see how it looks on a Windows 7 PC go to any excel file > right click and select properties > select details.]

It’s a usual practice that we use old templates to make new dashboards, excel templates. While doing so, the files retain the Meta data from the previous file/template. Now you may want to send these dashboards/templates out of your company, unaware of the fact that they have hidden sensitive information. This is especially critical for the service industry where it is not unusual that an employee works for several clients.

So, it is advisable that you clear your Meta data before sending out any excel file. To do this go to the office button> select prepare > inspect document. This will inspect your document for all hidden and Meta Data. Remove all the information that you feel is sensitive in nature.

3 thoughts on “Dashboard 101 – MS Excel Tips

  1. Anonymous

    Excellent insight to the creation of Dashboards. I had heard about them and this tutorial has made them into something that I can practice and build on. Thanks for this and for it being free.

    Reply
  2. domnic

    Nice work Keshav.. love your blog. Hopefully, I can keep coming back to this and become one of the commentators/ followers of your blog. Hope to connect with you sometime in the future

    Reply

Have a question? just leave a comment and we will answer A.S.A.P