Creating an Interactive - Dashboard Reporting Excel

1
Creating an Interactive Dashboard Reporting Excel

• Website http//www.p2w2.com/excel-dashboard-repor
  ting.php

2

• Creating interactive dashboard reporting excel is
  becoming an increasingly popular task. So, what
  is A dashboard? A dashboard is A visual
  representation of the most important information
  in your excel workbook. Currently, many excel
  users already include A summary worksheet at
  the front of their workbook to give others an
  overview of the data they will find in the rest
  of the spreadsheets. The concept behind A
  dashboard is the same however, the goal is to
  create charts, graphs, and tables instead of
  including large amount of numbers. These visual
  elements should all be on the same page so the
  information can be viewed and analyzed easily.

3

• The first step in creating a dashboard is to
  determine the most important data within the
  workbook. Ask yourself, What are the objectives
  of creating this workbook file? If you have
  several worksheets within the workbook, it may be
  a good idea to ensure that you are including data
  from each one.

4

• Please note that the information used to create
  the sample charts and graphs below are fictional
  and were created for the purpose of this post.

5
Create the Background

• With a new blank spreadsheet open, youll want to
  create a background for your dashboard. This can
  be accomplished by placing a shape with effects
  into the spreadsheet or by changing the fill
  color of the cells within the worksheet. In my
  example, I have placed a shape on my spreadsheet
  and added an inner shadow effect.

6
(No Transcript)
7
Create the Charts, Graphs, and Tables

• Now that the background is in  place, you will
  use the data within the other spreadsheets to
  create the visual elements for the dashboard.
  Once you have created each visual element,
  perform a cut and paste so that the charts,
  graphs, and tables are displayed on the dashboard
  spreadsheet. Rearrange the charts and graphs in a
  logical order and apply effects, if desired.

8
(No Transcript)
9
Creating Interactive Buttons

• Interactivity will allow users to drill down to
  see more details about the information displayed
  on the dashboard. By adding buttons onto the
  dashboard, any user that would like to visit a
  specific spreadsheet containing raw data that is
  summarized visually in the dashboard can easily
  do so with one click of the mouse. To create a
  button, add a rectangle shape to the dashboard,
  apply bevel effects, and change any other
  formatting, if desired. Add text to the shape
  that describes where the button will take the
  user, when clicked. Then, add interactivity
  by hyper linking the button shape to the
  appropriate spreadsheet. (The Hyperlink button
  can be found within the Insert tab).

10
(No Transcript)
11
Completing the Dashboard

• At this point, you will want to add any finishing
  touches to your dashboard. In this example, I may
  want to place the Penn State shield and the name
  of my department in the upper left hand corner.
  You may also want to do some testing to ensure
  that your buttons link to the correct worksheet
  and that your charts update when the data
  changes. A completed dashboard may look like this

12
(No Transcript)
13
Thank You
Dashboard Reporting Excel Website
http//www.p2w2.com/excel-dashboard-reporting.php