Excel Crash Course: Dashboards, Data Analysis & Heatmaps

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:

1. Learn the difference between a range of cells and Excel structured tables

2. Develop a basic understanding of structured referencing

3. Perform and automatically update calculations on Excel tables

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:

1. Convert a range of cells into an Excel formatted table

2. Use the Power Query tool to run the unpivot operation (one of the most common transformations in data analysis)

3. Have a basic knowledge of the Power Query editor

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:

1. Unpivot columns

2. Change data types

3. Add a custom column

4. Format dates appropriately

5. Merge columns

6. Delete and replace applied steps

7. Close and apply transformations to a new worksheet

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:

1. Extract table data - columns, headers, all data - into a new worksheet

2. Find unique values within a table

3. Manipulate Excel tables to return what you want

4. Perform simple table calculations and change data types

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:

1. Use the XLOOKUP function for calculations

2. Summarize data with Pivot tables

3. Reference Excel tables in calculations

4. Convert fuel price in naira to dollars dynamically using data from 2016 to 2019

5. Structure automated calculations and dashboards

Overview of the laundry model and invoicing system

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

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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.