Excel 2013 Dashboard Design

Your data is stored in Access, but you have a lot of data. To help us utilize it in Excel, we can set it up using PowerPivot.

Part of your data might be stored in the Excel Workbook, so we need to link it to our data model using PowerPivot.

You want to use data from multiple sources or tables in your dashboard and need to tie them together. Relationships and Hierarchy can link these together.

All your data is imported, but you need to perform additional calculations or functions before you can use it. We'll learn how to add calculated fields in PowerPivot.

You want to be able to see at a glance whether you are meeting certain business goals, so we'll create a KPI to measure and display this progress.

You have your data imported and preprocessed in PowerPivot, but now you need to display it in Excel. We'll create PivotTables to do this for us.

You are importing your PowerPivot data into PivotTables, but you need to use an advanced chart type to display your data. We'll create an intermediary table that will give us a higher level of control of the chart.

You have just created a PivotTable, but to use it effectively, you need to make some adjustments. We'll format, sort, and limit it to meet our desired output.

Your PivotTable is displaying the correct data, but is using the default style and options. We'll customize the table to match our desired look and feel.

You aren't using PowerPivot to manage your data model, but you still want to implement calculated fields and KPIs. We'll use PivotTable options and conditional formatting as a workaround.

After setting up your PivotTable, now you want to create a PivotChart. We'll create the chart and discuss key chart features.

PivotCharts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.

Charts can be more useful when they are combined, for example, a bar chart and a line chart to easily compare multiple metrics. We will walkthrough how to create these combo charts.

Charts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.

A written sentence can sometimes be necessary to make a strong point. We will create a dynamic sentence using static text and a formatted number from our data.

Arranging your dashboard effectively is a very important step. We'll cover techniques and tips to present our data and save you frustration in the process.

PivotTables can expand and contract with the data behind them, which can be useful, but also impractical. When you need complete control, reference the PivotTable in a range.

If you would like to highlight any range based on its value (or the value of a related cell), you can implement conditional formatting to change colors or fonts, or add icons or color bars.

You would like to show an underlying trend line without taking up much space. Sparklines allow you to create a basic chart in a single cell.

Sparkline source ranges remain static unlike normal and PivotCharts in Excel. In order to reflect the selected time period in our dashboard, we need to use named ranges and VBA code to make them dynamic.

To make an interactive experience with a dashboard based on PivotTables, we can use slicers to let the end user focus as wide or as narrow as they would like, at the click of a button.

If the default slicer settings and color scheme is not acceptable for the dashboard, we can customize them.

If we deal with a range of dates, we can easily extend filtering capabilities to the end user by including a timeline slicer.

Space can be limited on a dashboard, so drilling down or getting expanded views can be impractical on the dashboard itself. We can use hyperlinks to intuitively provide the additional information in one click.

If you need to quickly assemble a functional, interactive dashboard tied to your Excel data model or an external connection, Power View allows you to create a presentation-ready view in very few clicks.

If your data needs to be visualized in a hierarchy (for example, Category, Subcategory, and Item), consider using a drill down to provide context and details for your data.

In order to focus on just the relevant data, you can implement filters on chart elements or the entire view.

If you would like to see your data one slice at a time, you can use tiles to create a visual interface for selecting each slice.

If you would like to separate your data into slices such as tiles, but view all tiles at the same time, Multiples allows you to show all the slices together.

Your dashboard is complete, but we need to lock down the information that we don't want the users to focus on. We can do this by preventing the modification of cells and hiding sheets.

Your users may use different versions of Excel, which could cause serious functionality issues if they try to use the workbook on their own computer. Use SharePoint 2013 to allow users to navigate your dashboard reliably from their web browser.

If you need to share your dashboard on a small scale, or your dashboard does not use PowerPivot, you can conveniently host it on SkyDrive.

If your organization does not have a SharePoint 2013 server, but you need to distribute to a group who may not be using Excel 2013, use Office 365 SharePoint Online.

If you need to distribute a paper copy or PDF of your dashboard, as is sometimes necessary, utilize print area, scaling, and margins for the most optimal presentation.