How to Create Excel Dashboards – 4

Previous Part Build Excel Dashboards – 3
We will Add Calculations and Formulas
Add CalculationsSince this is a raw extract of actual transactions, it may need to add additional calculations and columns to create useful metrics.

For e.g in our sample file, we have the “Unit Price” and “Quantity” columns. We will create calculation to derive the “Amount” [...]

Excel Dashboards for Web

Put your Excel Dashboards on the web
Create your excel dashboards on Desktop and then easily put them on the web or intranet.
Download the web dashboard files

Web Dashboard Documentation

The documentation is a complete printable reference guide for converting desktop to web dashboard and has a reference section for chart properties to create advanced charts.
Download web dashboard [...]

Excel ODBC – Access ODBC

Alternative Video Tutorial or Follow the steps below
Sometimes, due to some improper setup of Microsoft Excel or incomplete components for Microsoft Office, direct connection to Excel files may not be possible.
This tutorial provides an alternative method to connect to the Excel Files. This method also applies to any new format of Excel Files or Microsoft [...]

How to build Excel Dashboards – 12

Previous – Excel Dashboard Tutorial – 11
How do you share your Excel Dashboard with users?
Now that you have built the dashboard, you can share the dashboard in multiple ways.

” Create a PDF output of the dashboard and send as email attachment.
Click on the PDF icon on the toolbar as shown below and it will [...]

How to build Excel Dashboards – 11

Previous – Excel Dashboard Tutorial

Summary
We built the above dashboard completely from scratch.

We defined Calculations
We defined date hierarchies
We built Date Filters
We built Date Parameters
We built Bar Charts
We built Pie Charts
We cleaned the dashboard layout

Next – Share Excel Dashboard with other Users
Read More

How to build Excel Dashboards – 10

Previous – Excel Dashboard Tutorial – 9
Cleanup – Resize and Organize
Give appropriate names to each portlet.
During the chart creation if we did not provide names, now is the time to provide them.
Right click on each title and click on “Edit”

 
Scale down the Size

 
Note, you can drag the corners of each portlet to resize the frames [...]

How to build Excel Dashboards – 9

Previous – Excel Dashboard Tutorial – 8
Finish the Dashboard Design and Development
By now, your dashboard is quite chaotic. You have objects all around your dashboard canvas with different sizes. We will start the process of finishing our design.

 
Before proceeding, we save a copy of the dashboard.

Navigate to menu File ‘ Save As

 
Give a name [...]

How to build Excel Dashboards – 8

Previous – Excel Dashboard Tutorial – 7
Create Pie Chart
We will now create a pie chart by product name

 
It is nice to know that the revenue distribution is not dominated by a single product.

We can now change the “Category name parameter” to see the product distribution for other category

By Category = Beverages

Summary of steps so far
” [...]

How to build Excel Dashboards – 7

Previous – Excel Dashboard Tutorial – 6
Create Bar Chart

Bar charts reveal important trend in your data. The very important reason we split the date into year, quarter, month, week and day components is to see if there is a trend in the sales or order information.

Right click on the Qlet title (”Name” ), and [...]

How to build Excel Dashboards – 6

Previous – Excel Dashboards – 5
Create Year and Category Parameters
Remember, we have not built any charts yet. It is very important that we build our foundation Qlet and its corresponding parameters so that all of the drop down parameters can be leveraged in the charts.

It definitely helps to plan your dashboard development. Since the dashboard [...]