Share [Social9_Share]

How to Publish an Online Dashboard using Excel

publish-an-online-dashboard-using-excel-1

If you are reading this in early January of 2017, wish you a very happy new year! This being the first post in 2017, let’s try something new !

In this post I am going to show you how to publish your report or dashboard (that you have created in Excel) online.

By publishing it online I mean that once you have created the spreadsheet in your computer you can then create a shareable link and multiple users can access that report. And sure enough the report will be dynamic with all the slicers, buttons and fancy controls working in sync.

The only difference(s)

  • You work looks more smart and light (since you send a link and not a 50 MB attachment)
  • The users just get to see the smart dashboard and not the heavy duty workings that you have put in at the back-end
  • And possibly a brownie point for your promotion.. may be??

 

So let’s start with a Sample Dashboard..

I am going to pick up a recent Dashboard which I made for Chandoo’s Dashboard Contest. My Dashboard had 2 important screens and I only want the users to be able to access only these screens and not the workings of the Dashboard

  1. Compare Companies (Screen 1)
  2. Overall Market (Screen 2)

 

Name the range area to be displayed online

publish-an-online-dashboard-using-excel-2

publish-an-online-dashboard-using-excel-3

  • Note that I have named both the ranges (which contain the complete view of the dashboard) as Screen 1 and Screen 2.
  • This naming will help me publish only the part of this dashboard online and the rest of the workings will be hidden (yet working)

Read this –> If you need help with Cell and Range naming

 

Now save the Excel file to OneDrive

publish-an-online-dashboard-using-excel-4

  1. Make sure you have a OneDrive account. It is free!
  2. Go to File and Click on Save As (make sure one drive is selected)
  3. Then go to More Options
  4. And then in the popup window choose Browser View Options
  5. Under Show Tab – check Screen 1 and Screen 2 (the 2 named ranges)

 

Now Share the workbook

publish-an-online-dashboard-using-excel-5

  1. Go to the right hand top corner of the excel screen and click on Share. You can also find the sharing option in the File menu in the older versions of Excel (version 2010 – 2013)
  2. Then choose Get a Sharing Link
  3. Excel will offer you 2 types of links
    1. Links for editing
    2. Links for viewing only
  4. In my case I copied the view only link and I am ready to send this link to anyone!
  5. Done!

If you make any changes in the excel file (which will now also be saved on your local computer and on Onedrive) the users can instantly refresh the link and view the changes!

 

The pros & cons

One of the biggest pro is that it can massively reduce duplicate excel files floating around. Take a look!

publish-an-online-dashboard-using-excel-6

One of biggest con is security of data if anyone has the link to your online dashboard they get to see your numbers! So cautious about that

It still is largely helpful in presenting non confidential information to multiple users!

 

Comment Below!

Have you used this feature or a similar feature before? I would love to hear your views on how it went? What were some of the challenges or benefits that you faced

PS: You can do this PowerPoint as well [Broadcast your PowerPoint Presentation]

 

  • Asel Samaganova

    Thank you for sharing. I’m looking for a new job and I wanted to show some of my Dashboard to my potential employers. It can be a perfect solution!

  • Asel Samaganova

    Could you please help? I’m using Excel 2010 and I don’t see OneDrive as I go to “Save as…”

  • Asel Samaganova

    Just tried to upload one of my Dashboard. All the VBA based features don’t work. Any solutions? Same thing for some icons or pictures which aren’t visible…. :(((

  • Whoopsie Land

    Interesting. Could come in quite handy !

    Thanks Chandeep 🙂

© Goodly