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 [...]
Subscribe to RSS