Excel Dashboards Build a Dashboard - Excel Dashboards

How Dashboard is Build?

The dashboard can build based on the specific intent of what the audience is most interested about, and these dashboard components and dashboard layout varies from case to case.

Initial Preparation

The initial preparation is the first step for building a dashboard. The following steps explains :

  • Why do you need the dashboard?: Is this dashboard for a specific task, like showing the status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will guide you in the design.
  • What purpose the dashboard will serve?: The dashboard should highlight only the data that adds value and should understand the data that is required. Anything outside that is unnecessary.
  • What is the source of data? : You should understand from where the data comes. It can just be an Excel worksheet or it can be through data connections to your Excel workbook from various dynamic data sources.
  • Who is the audience for the dashboard? :Is this for a manager, an executive, a stakeholder, an external vendor or general audience? Understand their requirements and preferences such as how much time do they have to look at the dashboard, the level of detail they expect and how they would like to digest the information. For example, while choosing a chart type, knowing the audience helps you to decide on whether you have to show relations between values or you have to draw a specific comparison.
  • Does the dashboard need to be static or dynamic? : Can the dashboard be updated periodically, say, weekly or monthly, or does it require to get updated to continuously streamline the data changes that happen at the backend? This choice will change the way you build the dashboard.
  • Does the dashboard need to be just a display or is it to be interactive? :Can the dashboard have read-only access or do you have to provide interactive controls / features that enable certain people to explore the data as required? This choice also will change the way you build the dashboard.

Now, identify the components of the dashboard. The components can be text, tables, charts, interactive controls, etc. Decide on the dashboard layout with these components.

After the above step Mockup the Excel dashboard on a PowerPoint slide or on a piece of paper and get approval for this mockup from the management and/or the key audience before you start the actual dashboard.

Organize the Data Source for the Excel Dashboard

Organize the data source before building the dashboard in Excel. In Excel, this is possible in various ways –

  • If the data is just an Excel table, establish a link to your workbook from the workbook where the data will get updated.
  • If the data is from multiple Excel tables, or if it is from various data sources, then build the Data Model in your workbook.

The data can be imported into the workbook periodically or establish data connections so as to refresh the data as when it gets updated, based on whether the dashboard has to be static or dynamic.

Set Up the Excel Dashboard Workbook

The data should be organized so that you can make workbook in structure. Insert two to three worksheets in the workbook − one worksheet for your dashboard and one or two worksheets for the data (data or PivotTable/PivotCharts or Power View Reports, which you can hide). This will help you to organize and maintain your Excel workbook.

Prepare the Data for the Excel Dashboard

From the Initial Preparation step, prepare the data for the Excel dashboard. The data can be any of the following –

  • Results from data analysis
  • Results from data exploration
  • Data resulting from computations on the input data
  • Data summarization from PivotTables or PowerPivot Tables

Select the Dashboard Components

Select any of the following Excel features for the dashboard components from the given list.

  • Tables
  • Sparklines
  • Conditional Formatting.
  • Charts
  • Slicers
  • Interactive Controls
  • PivotTables
  • PivotCharts
  • PowerPivot Tables
  • PowerPivot Charts
  • Power View Reports
  • KPIs

Identify the static and dynamic components and the components that are to be grouped for Slicers, if any.

Identify Parts of the Dashboard for Highlighting

  • Identify the parts of the dashboard that require immediate attention, such % Complete or the Current Status.
  • For this you can use bigger font and a striking font and font color.
  • The decision based on the audience for the dashboard Decide how much color you want to incorporate in your dashboard.
  • If the dashboard is for executives and/or managers, choose colors that impact the visualization of the results being displayed.
  • The background color can be added to a dashboard to make the dashboard components pop. You can use the same color code for similar charts or related results. You can use conditional formatting too.
  • The highlighting parts of the dashboard make it effective.

Build the Dashboard

In this step assemble dashboard components that you can efficiently and effectively do by using Excel camera.
Once the dashboard components are assembled, follow the given steps :

  • Give a title to the dashboard.
  • Incorporate a timestamp.
  • Include the copyright information, if required.

Using Excel Camera

This Excel camera helps to capture snapshots from your worksheets and place them in a different worksheet.

For example, you can capture a table with conditional formatting on a worksheet and place it on your dashboard. Whenever the data gets updated, the dashboard will get refreshed to display the changed data.

The Excel camera can be included as part of your Quick Access Bar as follows :

  • Right-click on the small arrow on the Quick Access Toolbar.
  • Click on More Commands in the Customize Quick Access Toolbar list.

Excel camera

Excel Options dialog box appears.

  • Click on Quick Access Toolbar.
  • Select All Commands under Choose commands from.
  • Click on Camera in the commands list.

Excel options

  • Click on the Add » button. Camera appears in the right side list.

add button

  • Click the OK button. The camera icon appears on the Quick Access Toolbar in your workbook.

camera appears

You can use Excel camera as follows:

  • Select the range of cells to be captured.
  • Click on camera on the Quick Access Toolbar.

The range of cells appears with a dashed line border.

Range cells

  • Click on the worksheet where you want to place the captured region. It can be your dashboard sheet.
  • Click at a position where you want to place it.

The captured region appears at that point.

captured region

Whenever you make changes to the original data, the changes will get reflected in the dashboard.

Date and Time Stamp on Excel Dashboard

The Date and Time Stamp can be incorporated in your dashboard to display when the data was last update. You can do this using the Excel functions TODAY () and NOW ().
To incorporate a Date Stamp, enter =TODAY () in the cell where you want to place the Date Stamp on your data worksheet.

date time stamp

Whenever the workbook is updated the current date will be displayed.

current date

  • Ensure that the cell where you entered the TODAY () function is formatted to the Date format that you want to display.
  • Capture the display with camera and place it on the dashboard.

capture display

The date on the dashboard will reflect the date when the workbook was last updated.

The Date and Timestamp can be incorporated on your dashboard in a similar way with the function NOW ().

  • Enter = NOW () in the cell where you want to place the Date and Time Stamp on your data worksheet.

date worksheet

  • Ensure that you have the right format for Date and Time.
  • Capture the display with camera and place it on the dashboard.

The date and time stamp will get incorporated on the dashboard and will reflect the date and time when the workbook was last updated.

Test, Sample, and Enhance the Dashboard

The dashboard should be tested to ensure that it displays the data accurately.

  • Test it in various possible scenarios.
  • Test for the precise updates (static or dynamic as is the case).
  • Test the interactive controls, if any.
  • Test the look and feel.

The next step is to get the dashboard evaluated by sample audience, especially those who approved your mockup dashboard.

Share the Dashboard

The Excel dashboard should make available to the intended audience. That can be done in several ways.

  • Mail the Excel dashboard workbook (You have to hide the worksheets other than the dashboard worksheet. You can also protect the workbook.).
  • Save the Excel dashboard workbook on a shared network drive.
  • Share the dashboard online.

If the Excel dashboard is static then you can mail it but if it is dynamic or has interactive controls, then it should have a connection to the backend data and hence requires sharing online.
The Excel dashboard can be shared in online with any of the following options :

  • Microsoft OneDrive : With your Windows Live account, you will get access to OneDrive where you can post and share documents.
  • New Microsoft Office Online.
  • Microsoft SharePoint.

The Excel workbook file can also save as an Acrobat Reader file (.pdf) and post it to web. But, once again this option is only for the static dashboards.

Tips for Effective Excel Dashboards

To make an Excel dashboard effective, follow Some tips for effective Excel dashboards are given below:

Keep it simple.

  • A simple, easy to understand dashboard is much more effective than a jazzy dashboard. Remember that it is the data that needs emphasis.
  • According to Glenna Shaw, strike a balance between making your dashboard attractive enough to hold your audience’s interest, but not so stylized that it obscures the information displayed.
  • Better avoid 3D effects, gradients, extra shapes and unnecessary gadgets.
  • If you can achieve emphatic display with conditional formatting or Sparklines, prefer using Tables to Charts.

Use Excel Conditional Formatting.

  • Use Excel Conditional Formatting that provides several options to automatically update based on the values in your table.

Select appropriate chart types.

  • Remember that there is no general rule for using a chart type. Sometimes conventional chart types like column chart, bar chart, doughnut chart, etc. convey the message emphatically more than the sophisticated charts that are coming into existence.
  • You can use Excel Recommend Charts command to initially evaluate the suitable chart types.
  • As you can change the chart type with a single Excel command − Change Chart Type, you can play around to visualize the display and choose the appropriate chart.

Use interactive controls.

  • Use interactive controls such as scroll bars, option (radio) buttons and check boxes that help the user easily and effectively visualize the different aspects of the data.

Use Excel Data Model for Big Data.

  • If you have large data sets from various data sources, you better use the Excel Data Model that can handle thousands of rows of data with memory optimization and can group data tables with relationships.

Choose appropriate colors.

  • Be careful in choosing the colors. Use the colors judicially to provide sufficient impact, but not override their purpose. Moreover, if the audience is likely to include color blind, restrain from using Red and Green colors. In such cases, though traffic light symbols sound effective for the data being displayed, they are not suitable for the dashboard. Use a gray scale instead.

Use Slicers.

  • Slicers are more effective than dropdown lists as they have a visual impact.
  • You can group charts, PivotTables, PivotCharts to use a common Slicer.

Group the Excel dashboard components together.

  • You can add visual interest to your dashboard by inserting a shape, such as a rectangle and placing your dashboard components that can be grouped on top of that shape. For example, if you are using a common Slicer, you can group all the dashboard components that share the Slicer.

Use Excel data hierarchies.

  • If your data has innate data hierarchies, define them in the Data Model and use them to interactively drill up and drill down the data.

Avoid crowded dashboard layout.

  • Remember that display of more information than that is necessary will overwhelm the audience and beat the focus on the actual purpose.
  • Do not include any data or chart in your dashboard, if you can do away with it.
  • This is an important checkpoint while testing your dashboard. Evaluate each dashboard component if is necessary and sufficient.
  • The dashboard components and the layout should support the single purpose of your dashboard.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Dashboards Topics