Author: Chandoo

My name is Chandoo. I am going to make you awesome in Excel, VBA, Dashboards, Power BI & Power Pivot and answering your questions. I live in Wellington, New Zealand. It is a beautiful, small city on the southern edge of the world. I run my own Excel Consulting & Training business from here. I am married to my college sweet-heart, Jo. We have 2 kids – twins. They are 10 years old now. They are busy creating new worlds with lego bricks or playing or learning. We (Jo &I) are busy with cooking, eating, walking, talking and reading.

Unpivoting Complex Data -Matrix Reloaded

Remember Matrix Reloaded? Me neither. I bet you still remember the pains poorly formatted data can create. In this lesson, let’s take a look at a complicated pivot table format and unpivot it using Power Query. Something like this…

What is in this lesson?

Hotel KPI Dashboard

We will create a hotel performance & KPI dashboard in this lesson. It is a very complex and elaborate process. So clear sometime to watch & practice the ideas. We will create this:

Demo of hotel KPI dashboard

What is in this lesson?

This lesson is broken in 7 parts as it is quite complex and intricate. We will cover these topics:

  • Defining context, goals for hotel KPI dashboard
  • Mockup for the dashboard
  • Data collection thru Power Query – unpivoting and other tricks
  • Calculating and creating header of the dashboard
  • KPI table design, sorting thru formulas – explanation
  • Hotel comparison – dynamic picture link driven viz
  • Random reviews in the dashboard
  • Food & Beverage summary – dynamic chart
  • Closing thoughts on design, functionality, slicer formatting and interactivity

Continue reading “Hotel KPI Dashboard”

Sales Performance Dashboard – Full Tutorial

We will create a sales performance dashboard in this lesson. It is a very complex and elaborate process. So clear sometime to watch & practice the ideas. We will create this:

Demo of sales performance dashboard

What is in this lesson?

  • Defining goals for sales performance dashboard
  • Mockup for the dashboard
  • Setting up tables & pivots – calculation engine
  • Creating the visuals – Summary section
  • Dynamic picture links for showing top & bottom sales persons
  • Forecasting sales or any other metric using formulas + slicer
  • Manager & City level summary with conditional formatting and nifty number formatting tricks
  • Closing thoughts on design, functionality and interactivity

Continue reading “Sales Performance Dashboard – Full Tutorial”

Our First Dashboard – Dynamic, Interactive, Connected and Automatic

What is in this lesson?

  • Defining goals for our dashboard
  • Setting up tables & data model
  • Creating necessary pivot tables
  • Adding charts, spark-lines and slicers
  • Creating two chart logic using form control + formulas
  • Making everything look pretty and functional

Continue reading “Our First Dashboard – Dynamic, Interactive, Connected and Automatic”