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.
The initial preparation is the first step for building a dashboard. The following steps explains :
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 before building the dashboard in Excel. In Excel, this is possible in various ways –
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.
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.
From the Initial Preparation step, prepare the data for the Excel dashboard. The data can be any of the following –
Select any of the following Excel features for the dashboard components from the given list.
Identify the static and dynamic components and the components that are to be grouped for Slicers, if any.
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 :
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 :
Excel Options dialog box appears.
You can use Excel camera as follows:
The range of cells appears with a dashed line border.
The captured region appears at that point.
Whenever you make changes to the original data, the changes will get reflected in the 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.
Whenever the workbook is updated the current date will be displayed.
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 ().
The date and time stamp will get incorporated on the dashboard and will reflect the date and time when the workbook was last updated.
The dashboard should be tested to ensure that it displays the data accurately.
The next step is to get the dashboard evaluated by sample audience, especially those who approved your mockup dashboard.
The Excel dashboard should make available to the intended audience. That can be done in several ways.
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 :
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.
To make an Excel dashboard effective, follow Some tips for effective Excel dashboards are given below:
Keep it simple.
Use Excel Conditional Formatting.
Select appropriate chart types.
Use interactive controls.
Use Excel Data Model for Big Data.
Choose appropriate colors.
Group the Excel dashboard components together.
Use Excel data hierarchies.
Avoid crowded dashboard layout.
Excel Dashboards Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Data Analysis Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Dashboards Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.