How do I create a dashboard

Create and share a dashboard with Excel and Microsoft groups

A dashboard is a visual representation of key metrics that enables you to view and analyze your data in one central location. In addition to providing a consolidated view of data, dashboards provide an opportunity for self-service business intelligence, where users can filter the data to see only what is important to them. In the past, when creating reports in Excel, it was often necessary to create multiple reports for different people or departments, depending on their needs.

Overview

This topic explains how to use multiple PivotTables, PivotCharts, and PivotTable tools to create a dynamic dashboard. Then we give users the ability to quickly filter the data the way they want using slicers and a timeline so that your PivotTables and Charts can automatically expand and close to show only the information users want to see. In addition, you can quickly refresh the dashboard as you add or update data. This makes it very convenient as you only need to generate the dashboard report once.

For this example, we're going to create four PivotTables and PivotCharts from a single data source.

Now that your dashboard is created, we'll show you how to share it with others by creating a Microsoft group. We have also prepared an interactive Excel workbook that you can download and then carry out the steps described here yourself.

Download the tutorial workbook for the Excel dashboard.

Retrieving your data

  1. You can copy and paste data directly into Excel, or you can set up a query from a data source. We have the query for this topic Sales analysis from the Northwind TradersTemplate used for Microsoft Access. If you want to use them, you can open Access, too file > New > Change to "Search for 'Northwind'" and create the template database. After you've done that, you can access any of the queries in the template. Since we've already put this data in the Excel workbook, you don't need to worry if you don't have Access.

  2. Check that your data is structured properly and that it is not missing any rows or columns. Each line should represent a single record or item. If you need help setting up a query or if your data needs editing, see Get and Transform in Excel.

  3. If you haven't already, format your data as an Excel spreadsheet. Data from Access is automatically imported into a table.

Creating PivotTables

  1. Select any cell within your data range and switch to Insert > PivotTable > New worksheet. For more details, see Create a PivotTable to Analyze Worksheet Data.

  2. Add the PivotTable fields you want, and then format them as needed. This PivotTable is the foundation for others, so spend some time making any necessary adjustments to style, report layout, and general formatting so that you don't have to do it multiple times. For more information, see: Designing the Layout and Format of a PivotTable.

    In this case, we've compiled a top-level sales summary by product category and sorted it in descending order by the Sales field.

    For more details, see Sorting Data in a PivotTable or PivotChart.

  3. After you've created your master PivotTable, select it, copy it, and paste it into blank areas on the worksheet as many times as needed. In our example, these PivotTables can change rows but not columns, so we're going to put them on the same row with a blank column between each row. However, you may find that if you can expand columns, you need to place your PivotTables one below the other.

    Important: PivotTables cannot overlap. You should therefore ensure in your design that there is enough space between the PivotTables so that they can be expanded and collapsed when filtering, adding or removing values.

    At this point, you could give your PivotTables meaningful names to convey what they do. Otherwise, Excel will automatically name them "PivotTable1", "PivotTable2" etc. You can select each PivotTable, then too PivotTable tools > Analyze switch and in the field PivotTable name enter a new name. This becomes important when you want to connect your PivotTables to slicers and timeline controls.

Creating PivotCharts

  1. Click anywhere on the first PivotTable and switch to PivotTable tools > Analyze > PivotChart > "Select a chart type:". We have selected a combination chart with "Sales" as the "Column Chart (Grouped)" and "% Total" as the line chart to be drawn on the secondary axis.

  2. Select the chart and then on the PivotChart tab- Tools the desired size and format. For more information, see our series on formatting charts.

  3. Repeat this process for each of the remaining PivotTables.

  4. Now you should rename your PivotCharts as well. Switch to PivotChart tools > Analyze, and enter in the field Chart name a new name.

Add slicers and a timeline

Slicers and timelines allow you to quickly filter your PivotTables and PivotCharts. That way, you can only view the information that is important to you.

  1. Select any PivotTable and switch to PivotTable tools > analyze > filter > Data Insert cut, and then check each item that you want to use for a slicer. For this dashboard we selected category, product name, employee and customer name. When you click OK, the slicers will be added in the center of the screen and stacked on top of each other so you will have to rearrange and resize them as needed.

  2. Slicer options - If you click on any slicer, you can go to Data cutting tools > Options switch and select different options, e.g. B. Style template and the number of columns to be displayed. You can align multiple slicers by holding down Ctrl + clicking them to select them, and then clicking the Tools for Slicer Tools tab Align use.

  3. Data cut links Slicers are only associated with the PivotTable that you used to create it, so you need to select each slicer and then to Dates Cutting tools > Options > Report connections and check which PivotTables should be connected to the individual PivotTables. Slicers and schedules can control PivotTables on any worksheet, even if the worksheet is hidden.

  4. Adding a Timeline - Select any PivotTable, switch to PivotTable tools > Analyze > filter > Insert timelineand review each item that you want to use. For this dashboard we have selected "Order Date".

  5. Timeline options - Click on the timeline, switch to Timeline tools > Options, and choose options such as Style, Header and labeling out. Select the option Report links to link the timeline to the PivotTables of your choice.

Learn more about slicers and timeline controls.

Further steps

Your dashboard is now functionally complete, but you probably still need to rearrange it the way you want it and make final adjustments. For example, you could add a report title or background. On our dashboard, we've added shapes around the PivotTables, and on the View tab, we've cleared the Headings and Gridlines check boxes.

Be sure to test each slicer and timeline to make sure your PivotTables and PivotCharts are behaving properly. There may be situations in which certain options cause problems when one PivotTable tries to adjust and overlap another, but it cannot and therefore an error message is displayed. These issues should be addressed before distributing your dashboard.

Once you have your dashboard set up, you can click the "Share Dashboard" tab at the beginning of this topic to see how to distribute it.

Congratulations on successfully creating your dashboard! In this step, we're going to show you how to set up a Microsoft group to share your dashboard with. We're going to pin your dashboard to the top of the document library for your group in SharePoint so your users can easily access it anytime.

Save your dashboard to the group

If you haven't already saved your dashboard workbook to the group, you can move it there. If the workbook is already saved in the group's file library, you can skip this step.

  1. Go to your group in Outlook 2016 or Outlook on the web.

  2. Click in the ribbon Filesto access the group's document library.

  3. Click the button in the ribbon Uploadand upload your dashboard workbook to the library.

Add the workbook to your group's SharePoint Online team site

  1. If you accessed the document library from Outlook 2016, click in the navigation pane on the left begin. If you accessed the document library from Outlook on the web, click at the right end of the ribbon More > Website.

  2. In the navigation pane on the left, click Documents.

  3. Find your dashboard workbook and click the selection circle to the left of its name.

  4. When you have selected the dashboard workbook, select from the ribbon Pin at the top out.

Whenever your users come to the Documents page of your SharePoint Online team site, your dashboard worksheet is now right at the top. You can click on it and instantly access the latest version of the dashboard.

Tip: In addition, your users can access your group's document library, including your dashboard workbook, through the Outlook Groups mobile app.

See also

Are there any questions we haven't answered here?

Visit the Microsoft Answers Community.

We listen to you!

This article was last published by Ben and Chris on March 16, 2017 rated as a result of your feedback. If you found it helpful, and especially if you haven't, please use the feedback controls below and leave us constructive feedback so we can improve it further. Many Thanks!