Why use a dashboard?

One of the more common requests I get about putting SharePoint to good use is how to build a hyper-responsive, beautifully designed, amazingly cogent dashboard displaying all the key performance indicators (KPIs), report updates, big data analyses, and business intelligence of the day.

Feel free to add in whichever new synergistic buzz phrase is relevant to metrics; I?ve probably been asked for that too.

There?s no doubt that big-picture score sheets can be remarkably helpful to get your point across, and one that?s pleasing to the eye helps everyone understand it better, especially upper management.

Sure, you can incorporate a lot of this into SharePoint, and my favorite method is using Excel and displaying the dashboard directly on a SharePoint page.

Embedding an Excel spreadsheet is far from the only way to do a dashboard in SharePoint, but it?s definitely one of the easiest to implement quickly since most people have a basic understand of how Excel works.

But for every dashboard request I get, I send a word of caution: dashboards are only as good as the data they represent. A pretty dashboard with minimally valuable data and content isn?t worth the time or energy required to create or maintain it. Tread carefully and make sure there?s value in moving forward with a dashboard.

Dashboards are useful ways to communicate complex projects and statuses in a simple and clear way. They?re easy to read if designed right, they save time by representing massive amounts of data in one easy-to-understand place, they keep you on task by comparing actuals to projections, and they promote employee performance. Nobody wants a bad metric on the company home page or projected during a team meeting.

So, dashboards keep people honest and on the right track, and they give people who may only be tangentially connected to the work a sense of the work?s status and progress.

But they?re just a graphical way to represent data. Data without a dashboard can still be useful. But a dashboard without data is pointless.

Picking the right data to report

If you don?t already have a dashboard, you need to create one. This is the hard part. Let me repeat that: creating the dashboard is the hard part. Putting it in SharePoint is not.

You?re not just assembling pretty indicator icons, displaying large numbers, and building colorful graphics. You have to source the information somehow. You have to identify what data is representative of your current status, your progress toward your goals, and predictions for the future; and you have to do it all quantitatively. Data pulled for these reports should not be subjective.

To create a good dashboard, it?s very likely you?ll have to identify new data to use to represent these values. Getting your team together is critical to develop agreed-upon metrics, because metrics scare people. Constantly displaying a bad metric that poorly represents a project that?s actually in reasonably good shape doesn?t make that project team very happy.

Start with whichever data point best represents your business process. Is it revenue? Employees on-boarded? Support tickets completed? Include this big-ticket data point on your dashboard.

Then consider the factors that impact that data point. For revenue, it may have to do with the number of leads generated. For employees on-boarded, maybe the number of employment offers put out. Support tickets are likely impacted by training and education. What metrics align with those factors? Include these and other factors as metrics on your dashboard.

Designing your dashboard in Excel

Once you?ve agreed on the appropriate metrics and data to report out on, you?ve got to lay it all out in Excel. Your best way to start is with a pencil and paper. Sketch out how you?ll represent your data. Or sketch on 35 index cards and arrange them on a table. It?s much easier to play with physical objects than build it Excel first.

If you need some inspiration, do a quick Google search on ?business dashboards? and look at the image results. You?ll see great examples of what others have done.

When you?ve got a good layout, find a graphic designer. Seriously. It?ll really make your design a lot better. And once you have a proposal from a designer, you can work in Excel to replicate the design. Just be aware that no Excel solution will ever be as pretty as a freely designed layout because columns and rows extend through the whole sheet, so the E column, for example, will always be the same width no matter where in the sheet you are.

Below, I?ve recreated a dashboard I found online in Excel. The source data is in the ?Data? tab/sheet, and the pretty representation of that data (including the automatic icons that pop up based on whatever the source value is; this is called conditional formatting) is on the ?Dashboard? tab.

Now, let?s insert the Excel dashboard into a SharePoint page. The first section below covers SharePoint Online in the ?modern experience?. The section beyond that covers SharePoint Online and SharePoint 2016, 2013, etc. in the ?classic experience?. Scroll to the section that is appropriate for your circumstances. When you?re done doing the SharePoint part, scroll down the the 3-dot separator to continue with this article.

SharePoint Modern Experience

If you?re using SharePoint Online and prefer to make the most of the modern SharePoint experience (or you?re using an Office 365 Group/Team site), you?ll be using a modern SharePoint page. It?s significantly easier getting your dashboard up and running in SPO modern than in the classic experience.

1. If you haven?t already, upload the dashboard into a document library.
2. Open the file, place your cursor in cell A1, then save the file and close it. Do this any time you edit the file. This ensures the file will always open with A1 in the top corner as opposed to another cell, which would affect how it displays on your page.
3. Update permissions on the dashboard file. If you want only active contributors to have access to it, but leave it as read-only to everyone else, make sure you do that before you post the dashboard to a page.
4. Create a page or designate an already-existing page that the dashboard will display.

Edit the page, click the + icon to add a new web part. Select File Viewer. Choose the file from the browser that pops up.

That?s it! You may have to tweak the settings a bit here and there, but it should get you what you?re looking for. You can skip the next few sections and jump straight to PowerPoint can work too. Happy metrics reporting!

SharePoint Classic Experience

If you?re using SharePoint 2013 or 2016 (or even SharePoint Online without modern sites), you have two methods of doing this and the decision rests on 1) whether both options are available and 2) which option displays better in your opinion. Before you do either of the options, you need to ensure the Excel workbook is available in the SharePoint site you?re going to display it in.

1. If you haven?t already, upload the dashboard into a document library.
2. Open the file, place your cursor in cell A1, then save the file and close it. Do this any time you edit the file. This ensures the file will always open with A1 in the top corner as opposed to another cell, which would affect how it displays on your page.
3. Update permissions on the dashboard file. If you want only active contributors to have access to it, but leave it as read-only to everyone else, make sure you do that before you post the dashboard to a page.
4. Create a page or designate an already-existing page that the dashboard will display. Note: I suggest using a wiki page. They?re the easiest to work with in my opinion.

Copy the URL to the dashboard file. You?ll need this link to tell the web part where to pull the workbook from, so have it handy.

Excel Web Access web part

The first option for displaying the dashboard is the Excel Web Access web part. I prefer this web part over the next one. You may or may not have access to it, though.

First thing you want to do is determine if you have it. First, go into the page you?re going to display the dashboard on. Go to the ribbon and click the Page tab > Edit. Now see if you have the web part, and if so, insert one:

1. From the Ribbon, click the Insert tab > Web Part.
2. Click Business Data.
3. Click Excel Web Access.
4. Click Add.

I work in a few different SharePoint environments. One doesn?t have it, a couple do. If you don?t see it, don?t fret. Just jump to the next section and use the Page Viewer web part instead.

If you do have the web part and you inserted it, we now have to tell the web part to display the dashboard workbook.

Click to open the tool pane, as prompted in the (blank) Excel Web Access web part.

The tool pane opens on the right side of your screen and gives you an overabundance of options. Don?t get overwhelmed. Minimize all of them except ?Workbook Display? and ?Appearance?. Now follow these steps to display the dashboard:

1. Remember that URL we copied from before? Paste it in the ?Workbook:? field.
2. Under Appearance, update the ?Title? field to be whatever you?d like it to say. By default, it will be called ?Excel Web Access?, which likely isn?t very user-friendly to your colleagues.
3. Now you?ve got to play with your settings a bit to make sure the whole dashboard displays correctly. This means you should change the height and/or width of your text box to accommodate the size of your dashboard, otherwise you?ll be scrolling within the web part itself, which isn?t very slick. I always use pixels as my dimension. (Note: this can depend on your screen size and resolution, so expect things to look slightly different on other people?s computers. You should test on other PCs as well.)
4. Click Apply. Your dashboard should load if this is the first time clicking Apply. If what you did in step 3 doesn?t look right, go back to step 3 and try again.
5. Once you?re happy with the appearance, click OK.

If you need to make changes in the future, edit the page then hover over the top-right corner of the web part, click the arrow that appears, then click ?Edit Web Part?.

Page Viewer web part

The second option for displaying the dashboard is the Page Viewer web part. This is useful if the Excel Web Access web part isn?t available, or if you happen to prefer how it displays. This web part basically displays a page or file within another page. So you can do way more with this than just a dashboard. You can display another page and pretty much any Office file or a PDF directly on a SharePoint page. Pretty handy, really.

Go into the page you?re going to display the dashboard on. Go to the ribbon and click the Page tab > Edit and insert the web part:

1. From the Ribbon, click the Insert tab > Web Part.
2. Click Media and Content.
3. Click Page Viewer.
4. Click Add.

Click to open the tool pane, as prompted in the (blank) Page Viewer web part.

The tool pane opens on the right side of your screen and gives you an overabundance of options. Don?t get overwhelmed. Minimize all of them except ?Page Viewer? and ?Appearance?. Now follow these steps to display the dashboard:

1. Click the ?Web Page? radio button (not File, even though that seems most logical). Remember that URL we copied from before? Paste it in the ?Link? field. (Be sure to delete the included http://. I notice that sometimes stays even when you add text, which can cause the link not to work.)
2. Under Appearance, update the ?Title? field to be whatever you?d like it to say. By default, it will be called ?Page Viewer?, which likely isn?t very user-friendly to your colleagues.
3. Now you?ve got to play with your settings a bit to make sure the whole dashboard displays correctly. This means you should change the height and/or width of your text box to accommodate the size of your dashboard, otherwise you?ll be scrolling within the web part itself, which isn?t very slick. I always use pixels as my dimension. (Note: this can depend on your screen size and resolution, so expect things to look slightly different on other people?s computers. You should test on other PCs as well.)
4. Click Apply. Your dashboard should load if this is the first time clicking Apply. If what you did in step 3 doesn?t look right, go back to step 3 and try again.
5. Once you?re happy with the appearance, click OK.

If you need to make changes in the future, edit the page then hover over the top-right corner of the web part, click the arrow that appears, then click ?Edit Web Part?.

Which web part and why?

If you have access to both web part options, I?d say go for the Excel Web Access web part. And I have a couple reasons for that.

First, aesthetically, the Excel Web Access web part shows you less extraneous stuff. If you look at the comparison below, you can see that the Page Viewer web part literally inserts another page and all of its tool bars into your page. So in the case of an Excel workbook, it displays the ?Excel Online? tool bar and everything that comes with that. That toolbar is a full 50 pixels tall, and likely unnecessary for you.

Second, the loading time for the Page Viewer web part is significantly more (in my experience) because it?s loading additional content and functionality. For example, the black ?Excel Online? tool bar I referenced above includes the Office 365 app launcher (?the Waffle?), which is fully functional. But you don?t actually need that.

PowerPoint can work too

The upside to the Page Viewer web part is you can display any Office file live on a SharePoint page. If you happen to prefer using PowerPoint for making or presenting your dashboard, use that instead of Excel.

PowerPoint offers the advantage of allowing you to place images, graphs, and tables anywhere you want on the slide; you?re not stuck with the rows and columns setup of a spreadsheet. But you also don?t get the conditional formatting and fully featured calculations that Excel Offers. On the other hand, you can launch your slide into full screen mode by launching the slideshow. Excel can?t compete with that.

This just goes to show that there?s never a perfect solution. Each option always has its pros and cons. Maybe you?ll find that PowerPoint provides better functionality or design freedom.

A note on functionality

The options I describe in this post basically make it so nobody has to explicitly open or send an Excel spreadsheet when they?re looking at KPIs or dashboards. Because you?re working with Excel, you can add graphs, charts, equations, conditional formatting? nothing is really off limits except macros.

But the data isn?t manipulable live on the screen. The data is live, yes; as in, whenever you loaded that page, the content is as up-to-date as the file it?s displaying. And you can bounce between different sheets in the workbook. But you can?t play with the data, do what-if changes, or anything like that right in the page. You?d have to open the file for editing to pull that off.

What I will say about ?live? editing is you can edit the workbook via the Page Viewer web part, but the Excel Web Access web part makes you open Excel in the application on your computer, rather than in the browser. Another hit against the Excel Web Access web part.

This is simply a way to display information in a web portal that many of your colleagues are likely already using, without having to open Excel or some other program. It?s a good way to keep the big-picture scores in the front of everyone?s eyes because a dashboard or a portion of a dashboard can be incorporated into a department or team home page, a place where people already visit regularly.

If you want to be able to play with live data, you want to consider Power BI. Power BI now comes free with many enterprise-level Office 365 subscriptions and incorporates pretty seamlessly with SharePoint. You can even get Power BI-enabled Excel functionality like shown below, including better graphs and even maps with live data.

But if you don?t know Power BI or don?t care to learn (it?s intense), this post provides a quick, maintainable solution that almost anyone with some Excel and SharePoint skills can put into play within minutes, once the metrics have been finalized.

Further reading

1. Lavinsky, Dave. ?Executive Dashboards: What They Are And Why Every Business Needs One.? Forbes Online. 2013?09?06. Accessed 2017?01?30.
2. Gemingnani, Zach. ?5 rules for successful success metrics.? Juiceanalytics.com. 2006?04?02. Accessed 2017?01?30.