Learn Excel -Web App Dashboard –  Podcast 2012

Learn Excel -Web App Dashboard – Podcast 2012

Learn Excel from MrExcel podcast, episode
2012 – Web Dashboard! Alright, I’m podcasting this entire book,
go and subscribe to the playlist up there in the top-right hand corner! Welcome back to the MrExcel netcast, I’m Bill
Jelen. All week I’ve been building this dashboard,
now we’re going to publish this to the web, but before we publish it to the web, we’re
going to have it look less like Excel. So the way I do that, I choose all cells on
the dashboard, Home, just use a nice light color back there, that’s my first step to
make it look less like Excel. On the View tab, let’s turn off the formula
bar, turn off the headings, that’s the ABC across the top, turn off the grid lines, although
we already can’t see those because of the color. Out on the right hand side, I have the ^ indicator
out here, use that to hide the ribbon. The active cell, use the arrow keys to get
it behind a chart or behind a slicer, so no one sees the cell pointer out there. Alright, now I want to hide all sheets except
for the dashboard sheet, but before I do that, because it’s hard to unhide all sheets, so
I’m going to do View, Custom Views, add a new view called ShowAllWorksheets, click OK. And then I will hide, right-click, choose
Hide, I realize it’s out of view there, Ctrl-click these, right-click, choose Hide
again, I realize it’s out of view. Then we’ll go back into View, Custom Views,
and create a new view called OnlyDashboard, click OK. Now what does that mean? That means that when I go to View, Custom
Views, and do ShowAllWorksheets, it will unhide everything, or I can go into View, Custom
Views, and say OnlyDashboard, and I get only the dashboard. Cool, right? Now remember, outside of the dashboard, out
here in column P, there’s other stuff, stuff that I don’t want people to scroll out to. So I’m going to select just this range here,
and under Formulas, Name Manager, I’m going to create New Name, we’ll call it MyDashboard,
click OK, perfect. Alright, so this is the dashboard that we
want to put on the web, before we go there, I’m going to go to File, and down here use
Browser View Options. If you’re in earlier version of Excel, this
was under File, Export, Browser View Options. And I don’t want to do the whole workbook,
I want to do Items in the Workbook, and we’re going to look for our named range of MyDashboard,
So this is all that I want to share, click OK, alright. Now I got to save this, I’m going to Save
As, to save it out to the OneDrive. This has taken entirely too long, I’m still
waiting for it to upload to the OneDrive. Alright, there we are, it’s done! Now, I’m going to go to File, Share, and I
want to Share with People, Get a sharing link, and it’s actually a view-only link, alright,
and there is that great big link, will copy that, alright. So I pasted that insanely long link here into
a browser, and now check this out, there is my Pivot chart, my spark-lines, another Pivot
chart, my slicers, and amazingly, when I choose the slicer, you see that the data and the
charts update. How awesome is that, right? There you are, you’re not just an Excel person,
you are a Web App Developer. Now, this is not stored in your company, I’m
sure your company is going to flip out that this data is out on the web, provided someone
can figure out this crazy URL. Right, if they get that URL, yep, they’re
going to have your data, your call whether that’s a deal breaker or not. You want a way to impress the boss? Ah, this whole series, this is a great way
to impress the boss, it’s just a couple of pages in this book “MrExcel XL” click
that “i” on the top-right hand corner to buy the book! Alright, to recap: Before going to the web,
make your dashboard look less like Excel, alright? Select all cells, light fill color to get
rid of gridlines, uncheck the formula bar, headlines, headings, and gridlines. You can even, by the way, I didn’t show it
in the episode, but if you go into File, Options, Advanced, there’s things down here to turn
off the horizontal and vertical scrollbar, we hid all the sheets, but you can hide the
sheet tabs, and so on. Before you hide all the sheets, go into Custom
Views, create a new custom view for showing all worksheets, and then hide all the worksheets
except for the dashboard. Create a range name around your dashboard,
go into Browser View Options which used to be File, Export, but it’s now File, Info,
Browser View Options, and say that you only want to show that named range. Save the file to the OneDrive, and then get
a view-only sharing link, share that with your manager, and all of a sudden, you have
a beautiful, beautiful web app. Well hey, I want to thank you for stopping
by, we’ll see you next time for another netcast from MrExcel!

18 thoughts on “Learn Excel -Web App Dashboard – Podcast 2012

  1. this is simply awesome! but O have one question. lets say that o use Power query to get the data to my dashboard. when I get New data and update the dashboard I'll have to create a New link or there's a way to update the file and keep the link? thanks!

  2. Your dashboard would look even better if you would have used the alt-key while arranging (changing size, moving) the slicers, graphics, etc.
    This would force excel to fit these items right within the cell-frames… 🙂

  3. Very informative video, thanks! Any idea how you could save and host this on a local server or intranet instead of onedrive?

  4. Great video, Bill. Thanks. Question. I'm trying to link cells in one worksheet to another worksheet (within the same file) except that the file is already in the OneDrive and being accessed by collaborators (who are editing it in the Browser). How can I link to cells using the Web App?

  5. Great video! Can I embed this kind of calculators on my website? And is it possible that people could enter some values?

  6. Can this be done in a local share drive?

    My company has lot of restrictions for data sharing. We do not have access to internet, we use Intranet system.

    I want to do the same thing as shown here, I wish if can be done in a local shared drive.

    If possible then please message me.

  7. Thank you, but I have a question. How I can publishe dashboard report as powerpoint show with active slicers and dynamic data?

Leave a Reply

Your email address will not be published. Required fields are marked *