Excel Crash Dashboards Data Analysis Heatmaps

Learn core, business-focused Excel skills for data analysis, data visualisation and dashboard modelling in 3 hours

Last updated 2022-01-10 | 4.7

- Explore Microsoft Excel from a data science & data visualisation and data analysis perspective
- Design three dashboard systems in Excel - Twitter Activity Heatmap
- Advanced Currency Converter
- Business Invoicing Dashboard
- Learn to use Excel dynamic arrays with other functions like a pro to build automated systems

What you'll learn

Explore Microsoft Excel from a data science & data visualisation and data analysis perspective

Design three dashboard systems in Excel - Twitter Activity Heatmap

Advanced Currency Converter

Business Invoicing Dashboard

Learn to use Excel dynamic arrays with other functions like a pro to build automated systems

Manipulate pivot tables to design beautiful

custom charts and heatmap visuals

Work with Excel time intelligence and text functions to extract HOUR of DAY

DAY of WEEK etc

Tips and tricks to fine tune your Excel dashboards and visuals to supercharge your data visualisation skills

Test your data analysis skills by working on data retrieved from Twitter API

Learn HOW to maximize office productivity and potentially increase your pay

* Requirements

* Preferably Excel on Office 365 for Windows. Microsoft Excel 2010-2019 (PC/Windows)
* Mac users are welcome
* but note that the interface of Pivot Tables varies across platforms
* Knowledge of Excel for basic reporting is an added advantage but not required
* Be ready to make a leap. It's a crash course

Description

This is a 100% project-based Microsoft Excel crash course with a special focus on data analysis, designing simple dashboards and creating heatmap visuals and other charts. Maximizing office productivity is a key skill to learn. By working with modern Excel tables and learning how to extract insights from data, you'll achieve a leap in your Excel skills in 3 hours.

Supercharge your Microsoft Excel skills rapidly in this Crash Course!

As a Business Intelligence consultant, I designed the curriculum based on some of the skills needed to get you powering through your daily business needs, and I’ve designed the courses to achieve specific objectives. This course is a hack to develop advanced power-user skills in Microsoft Excel on Office 365 in a very short period of time.

Students will work on two different datasets - Twitter data and fuel prices data - to design dashboards and extract insights using Pivot tables and beautiful heat maps.

In this course you will learn how to:

Explore Microsoft Excel from a data science, data visualisation and data analysis perspective
Design three dashboard systems in Excel - Twitter Activity Heatmap, Advanced Currency Converter, Business Invoicing Dashboard
Work with Excel time intelligence and text functions to extract HOUR of DAY, DAY of WEEK etc
Manipulate pivot tables to design effective and beautiful charts and heatmap visuals
Design and customise pivot charts by adding custom images
Use the Power Query tool to manipulate and mashup data
Use certain tips and tricks to fine-tune your Excel dashboards and visuals to supercharge your data visualisation skills
Test your data analysis skills by working on data retrieved from Twitter API
Learn HOW to maximize office productivity and increase your salary with automation skills

Why Excel skills matter

Advanced Microsoft Excel skills can open up a whole myriad of career opportunities in business analysis, product management, data analysis, operations and strategy roles. As a skilled Excel user that is able to extract insights from data, you will be comfortable in working in industries such as finance, consulting, manufacturing, pharmaceutical, analytics or other business roles. Excel is the primary spreadsheet application used in financial modelling and ad-hoc data analysis.

In this 100% example-based and project-based course, you’ll develop highly marketable business-focused Excel skills in data analysis and data visualisation to help you outperform competing candidates for promotions, job opportunities, and internships. You’ll learn what it means to be the Excel go-to person having an unfair advantage in analytical skills.

How is the course structured?

The course curriculum has been designed as a project-based, crash course. You will learn rapidly by working through the Twitter dataset to understand rudimentary data analysis and design novel heatmaps. Then in the latter half of the course, you will learn how to work with modern Excel tables and manipulate them using the Power Query tool, structured referencing, and using the functions available on Office 365 version of Excel to craft automated calculations and dashboards.

The course builds on itself, meaning that as we continue forward in the course we’ll re-use the concepts from previous sections to further reinforce them. All of the material is 100% example-based and we’ll use multiple real-life examples throughout the course.

The course is composed of 18 short lectures each of which covers one concept at a time, and 2 problem-solving sessions (data challenges). The full course length is 1.8 hours of well thought out, deliberate content to get you building and designing within Excel. You will also get over 1 hour of practice problems which are very detailed and cover real-life analytical scenarios.

I designed this course to be as practical and as relatable as possible by using sample data from real-life activities. The course is complete with working files, you’ll be able to follow along practising each concept and receive a verifiable certificate of completion when you finish the course!

It's a crash course so be ready to take a leap!

Are there any course requirements or prerequisites?

Preferably Excel on Office 365 for Windows. Microsoft Excel 2010-2019 (PC/Windows)
Mac users are welcome but note that the interface of Pivot Tables varies across platforms
Knowledge of Excel for basic reporting is an added advantage but not required
Be ready to make a leap. It's a crash course

Who this course is for:

Beginner in Excel interested in data analysis
More advanced users looking to learn how to improve office productivity
Business students curious about data visualization
Business professionals interested in data analysis and modelling
Crash course enthusiasts
Python developers and coders interested in data analysis and Excel

Who this course is for:

Beginner in Excel interested in data analysis
Business students curious about data visualization
business professionals interested in data analysis and modelling
crash course beginners
python developer interested in data analysis and Excel
Business owners
Accountants

Course content

4 sections • 33 lectures

Analysis of Twitter Dataset and Designing Heatmaps 12 lectures

Introduction to the Excel crash course Preview 05:46

Welcome to this Excel Crash Course. We'll begin by taking a look at the course outline and what you should expect throughout the course.

Understanding the Twitter Dataset Preview 05:22

In this lecture, we'll do basic exploratory data analysis by:

- Applying filters

- Understanding blanks and data types

- Understanding the fields contained within the dataset

Extracting Other Details for Analysis Preview 09:08

In this lecture, we'll look at time intelligence calculations within the context of Excel by extracting:

YEAR
MONTH & MONTH NAME
HOUR OF DAY
DAY OF WEEK

We will also use some Data Science methods to remove unnecessary data from the dataset.

Get ready to learn some shortcuts as well.

Creating Pivot Tables Preview 07:58

In this lecture, we'll design some aggregates using Pivot tables.

Students will learn about:

- Pivot table fields

- Changing the aggregation summary

- Changing Pivot table options

Applying Conditional Formatting using Color Scales Preview 02:30

In this lecture, we will be adding conditional formatting to the Pivot table values. We'll choose a color scale that appropriately matches the insight that we want to extract using the heatmap.

Adding Slicers to Dashboard Preview 01:39

In this delivery, we'll be adding two slicers - YEAR and BANK - which will allow us to filter the heatmap using the values contained within the slicers.

Applying Custom Number Formats Preview 03:19

There are several custom number formats available in the Microsoft Business Intelligence sphere - Excel, Power Platform - Power BI, Power Automate & Power Apps.

In this lecture, we cover how to make an invisible text to hide the heatmap values. This allows you to create effective heatmaps with explosive and pixelated colors.

Adding Custom Images to Charts Preview 11:25

In this video, we'll apply the Twitter logo on a Pivot chart to generate the Twitter bar visual.

Students will learn tips & tricks of data visualisation within Excel to get their visuals crisp and beautiful.

As Edward Tufte stated, "above all else show the data."

Interlude - Data Manipulation Challenge Preview 01:49

This is an optional data challenge that can be achieved using a scripting language such as Python or Excel VBA.

It's not required to complete this lecture but it could be a fun and rewarding pursuit.

Adding Heatmap Legend Preview 01:32

In this lecture, we'll be cleaning up our visual and adding a legend to present more information to guide the report users.

Legends and titles are key aspects of designing effective data visualisations.

Wrapping Up - Introduction to Open Analytics Preview 02:01

We'll be wrapping up section 1 of this course which involves learning how to design dashboards using Pivot tables, heatmaps and slicers.

Data Analytics Challenge

Design a dashboard to display the yearly Twitter activities of Banks in Nigeria

Advanced Currency Converter using Dynamic Arrays 6 lectures

Introduction to Modern Excel Tables and Dynamic Arrays Preview 11:14

Welcome to this section of the Excel Crash Course and making it this far. You're making incredible progress.

In this lecture, we'll introduce the concept of Excel tables and specifically, students will:

Learn the difference between a range of cells and Excel structured tables
Develop a basic understanding of structured referencing
Perform and automatically update calculations on Excel tables

Using Power Query for Data Manipulation Preview 08:25

This lecture introduces and describes the dataset - Monthly PMS (Petrol Prices) in Nigeria - and takes students on a journey in using Power Query: the exceptional tool for data mashup and manipulation.

Essentially, students will:

Convert a range of cells into an Excel formatted table
Use the Power Query tool to run the unpivot operation (one of the most common transformations in data analysis)
Have a basic knowledge of the Power Query editor

Applying Power Query Data Transformations Preview 06:19

This lecture will solidify your understanding of some of the capabilities of Power Query for data extraction, transformation and loading (ETL).

Specifically, students will learn to:

Unpivot columns
Change data types
Add a custom column
Format dates appropriately
Merge columns
Delete and replace applied steps
Close and apply transformations to a new worksheet

Structured Table Referencing Preview 11:35

This lecture is all about learning how to manipulate modern Excel tables using structured referencing. The methods and skills gained here are applicable when learning and using the DAX language available in Power BI.

All the functions used here have been applied in an Excel version on Office 365. You may encounter errors if you use a version of Excel not available on Office 365 e.g. Office 2010 - 2019. When this happens, try using Ctrl + Shift + Enter as you key in the formula. This is the way to enter array formulas in Excel.

If you're still experiencing issues, my advice is to upgrade to the Office 365 version of Excel. There are limitless possibilities available with this version. You will unleash the power of true automation and collaboration when you do this.

Students will learn how to:

Extract table data - columns, headers, all data - into a new worksheet
Find unique values within a table
Manipulate Excel tables to return what you want
Perform simple table calculations and change data types

XLOOKUP Function and Pivot Table Calculations Preview 04:54

In this lecture, we go a little bit deeper by referencing a dollar-naira exchange rate table to perform business calculations on our fuel prices table. By taking advantage of the new XLOOKUP function, students will master all they need to know about modern Excel tables.

Students learn how to:

Use the XLOOKUP function for calculations
Summarize data with Pivot tables
Reference Excel tables in calculations
Convert fuel price in naira to dollars dynamically using data from 2016 to 2019
Structure automated calculations and dashboards

Analysis of Yearly Average PMS (Petrol) Price Across States in Nigeria

Work on creating a Pivot table and converting it to a heatmap to reveal insights.

Laundry Service Model and Invoicing Dashboard 14 lectures

Introduction to the Project Preview 01:25

Overview of the laundry model and invoicing system

Overview of the Laundry Model Project Preview 03:43

Understanding the Inputs Preview 03:01

Here we go over the parameters and inputs for building the laundry model.

Formatting the Input Tables Preview 04:11

It's important to have your inputs in the required format. For that to happen, some adjustments need to be made to re-structure the inputs.

Duplicate and Combine Tables with Power Query Preview 09:21

Power Query is a very powerful ETL (Extract, Transform and Load). In this lecture, you'll learn how to duplicate tables in 3 ways and combine tables into a single source of truth.

Rounding Digits in Power Query Preview 01:42

Learn how to round numbers to a particular decimal place in Power Query.

Load Specific Tables from Power Query Preview 01:50

In this lecture, you'll learn how to load only specific tables from your Power Query back into your Excel worksheet, leaving other tables as connections only.

Feature Extraction and Named Ranges Preview 09:57

In Machine Learning, feature extraction is the process of identifying relevant parameters that affect the behaviour and performance of your model. In our Excel model, feature extraction involves identifying the core elements from our inputs that will enable us to build a dynamic dashboard.

Combining Elements - Data Validation with Named Ranges Preview 07:49

This is where it begins to get more interesting and complex. In this lecture, you'll learn how to combine named ranges to extend the usage of data validation.

Combining Elements - Understanding the INDIRECT Function Preview 02:28

The INDIRECT function is an interesting one. In this lecture, you'll learn more about this function and how it works.

Combining Elements - Dynamic Drop-down Menu with Data Validation Preview 03:35

Combining Elements - INDEX and MATCH with Multiple Criteria Preview 11:57

While we're still on combining elements, you'll learn how to use the INDEX + MATCH formula with multiple criteria for more complex lookup cases. Knowing how to use this will greatly improve your understanding of arrays within Excel.

Understanding the MATCH Formula with Multiple Criteria Preview 05:51

In this lecture, you'll be taken step-by-step on understanding the MATCH function and why it's perfect with the INDEX function to lookup values with multiple criteria.

Business Invoice Dashboard Design Preview 06:15

This is the icing - combining all elements together to build a Business Invoice Dashboard. This is where you get to play around with designing how your invoice will look when printed or displayed.

Additional Content 1 lectures

Analyzing an On-Demand Rides Dataset Preview 11:07

This additional section takes you through data analysis using an on-demand rides dataset like Uber's data. Students will learn the different ways to perform the VLOOKUP function to retrieve values from a different table.

Included is an interesting dataset to practice all that's been learnt in this course.