Become the Dashboard Master in your organization.
Take your dashboards to the next level, making them faster and more powerful with simple recorded macros.
No programming experience required!
A fast Excel dashboard
There are many ways to skin a cat, and many ways to make dashboards in Excel. The best way is always task-dependent, but a few recorded macros can replace hundreds of formulas, improve calculations and make your dashboard more reliable. You can go a long way with lookup formulas, but macros will open up a whole new world. And chances are, whatever your problem is you’ll find a macro online that solves it.
Create powerful dynamic dashboards that make your business data come to life and establishes you as the dashboard expert in your organization. With these tutorials you’ll see how to go from a blank page to a fully functional dashboard, step-by-step. This example dashboard uses many different techniques that you can use in your own projects.
How it works.
A 12-module course that shows you every single step I took to create the dashboard above. This dashboard contains:
- Forms that allow the users to interact with the data;
- How to connect your dashboard to an external data source;
- Dynamic charts that change when the data changes (based on user selections);
- Advanced Excel functions like OFFSET() and INDIRECT() ;
- Advanced pivot tables;
- Recorded macros;
- More than two hours of video;
- The Excel dashboard itself;
- The external data source.
Get my book for free!
Now, while you learn how to make Excel charts like a pro, you can apply these best practices to your dashboards. Get access to two dashboard tutorials, more than three hours of step-by-step videos that will help you unleash your creativity and create great dashboards using Excel. And you also get access a growing number of bonus files that you can explore and use to improve your Excel skills.
I’m and updating the dashboard tutorials to better reflect what I wrote in my data visualization book and to take advantage of Excel 2013 and 2016. To mark this I’m giving the Kindle version of my book away to new users. Hurry! Grab one of the remaining digital copies! Kidding, but this campaign will not last for long. Seriously.
(Two dashboard courses only $97.00 and a free electronic copy of my book. 24-month access to the tutorials, 30-day, no questions asked, full money-back guarantee. Pay securely with your Paypal account. You’ll get a message from Amazon with a link to your gift a few hours after your purchase.)
The course is right below, let’s get started!
The Demographic Dashboard VBA Tutorial
Each of the images below links to the page modules. If you need to login first, use the form in the page footer.
We'll start by creating a pivot table linked to an external data source where those region definitions are stored. When a new region is selected, only the countries belonging to that region are shown in the pivot table.
A population pyramid is an icon of population studies. To create a population pyramid in Excel you just need population by sex and age groups. Since male population is displayed to the left of the axis, you have to force the data to assume negative values. After that, the x-axis is formatted to remove the minus sign.
In population studies, we can learn a lot about the population by measuring the relative proportions of each age group (Young, Adult and Old). In the demographic dashboard, we'll use these ratios as our KPI's, and we'll compare the current country to the active region. We'll have two pivot tables, one for the country and the other for the region.
We'll see how to change a pivot table from absolute values to proportions, how to use a base field and item to make new calculations, how to make a dynamic chart using dynamic ranges and ranges that point to different data sources depending on user selections.
In this module we'll see how to setup the data source for a scatterplot and split it into three series.
We'll see how to record macros, how to edit them, how to add parameters and get values from the Control sheet and how to assign the macros to the controls. Let's consider this the alpha version of your dashboard. In the next module we'll move to the beta version.
Congratulations! Now you have a fully functional dashboard. The next step is to document your work, adding descriptions to the named ranges and comments to the macros. Then you can hide the sheets (except "Report") and test the dashboard with a small group of users.