Web App Dashboard

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!