Ultimate Excel Course #9: Cleaning and Preparing Data Ready for Analysis

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
8 Lessons (51m)
    • 1. Introduction

      2:49
    • 2. Splitting Text across Multiple Columns

      7:24
    • 3. The CONCAT and CONCATENATE Functions

      6:27
    • 4. The TEXTJOIN Function - Combining Multiple Cell Values just got Easier

      4:10
    • 5. Extracting Text from a Cell

      9:34
    • 6. Using the MID and FIND Functions to Extract from Irregular Strings

      7:23
    • 7. The VALUE Function - Converting Text to Number

      3:33
    • 8. The Incredible Flash Fill Tool

      10:07

About This Class

d3c1fa46

This class is #9 in a series called the Ultimate Excel Course.

In this class you will learn how to clean and prepare your data analysis using formulas and alos some built-in Excel tools.

In this class we start by splitting cells into multiple columns, and also joining multiple columns into one cell. This is a common requirement to separate or join parts of a name, address and dates.

We will use formulas to automate these processes where possible. Some classic formulas and also some very new ones.

Formulas will be used to extract text from a cell ready for analysis. And also deal with the common problem of excess spaces, normally found preceding or trailing the cell content.

We also use formulas to change the data type of a cell value. If Excel does not recognise the type of data we are dealing with, we cannot analyse it accurately.

We then take a look at the incredible Flash Fill tool. This feature is brilliant for quickly manipulating and formatting cell values. And best of all it is easy to use.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enroll and let's do this.

Please note that this is class #9 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Functions and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot