Data Cleaning in Excel - Text Functions and Power Query in Excel!

Data cleaning in Excel is one of the most important things you can do in Excel. If you need to perform data analysis in Excel, then you need to use clean data.

What do we mean by clean Data in Excel? We mean data that is free from errors, formatting issues, data that have numbers stored as text, has dates stored as numbers, data with gaps in it, and every other error you could think of. This often happens in Excel when data is downloaded from a third-party system, like accounting software, a CRM, or Email Service Provider.

In this one-hour session, we show you how to use the various text functions in Excel to clean your data. We then move on to using Power Query to clean data and Power Query to join data.

If you'd like to follow along with this session, you can download the data set we use below.

✅Download the Excel Workbook and follow along ➡️

📝Excel Tutorial Markers:
0:00:00 - Introduction
0:03:37 - Why Clean Data
0:06:58 - Widen Columns in Excel
0:09:46 - Delete Blank Rows in Excel
0:10:05 - Fill Empty Cells in Excel
0:11:38 - Clear Formatting
0:12:58 - Remove Duplicates
0:14:41 - Format Columns
0:17:36 - Numbers Stored as Text in Excel
0:20:24 - Changing Case Using the Text Functions
0:26:19 - Find and replace in Excel
0:28:55 - Importing Data into Excel from a Text File
0:41:54 - Using Text Functions to Extract Data
0:51:29 - Import Data into Excel from an Access File
0:56:52 - Combining Files in Power Query
1:00:06 - Basic Clearing in Power Query
1:05:44 - Updating Queries in Power Query

✅Download the Excel Workbook and follow along ➡️

Check out our Excel 2021 tutorials for beginners playlist here

Check out our FREE 300+ hour training course playlist here ➡️

💻Watch more popular tutorials from Simon Sez IT:

✅Microsoft Office 2021 and Office 365 Training Tutorials playlist

✅Microsoft Office 2019 Training Tutorials for Beginners playlist

💬Stay in touch!

🔔Subscribe to our channel:

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel 👍🏽 ;-)

[SimonSezIT]

✅Download and Complete the Exercises that go with this video ➡


Check out our FREE 300+ hour training course playlist here ➡

[adhamdodzy]

thank you very much for updating the links

[ezeuba]

This is very rich video with lots of information... I could not understand the last part because your computer was lagging

[bobhazzard]

Thank you for this video. I just cleaned a 6744 record file with 15 columns in about 30 minutes. It formerly took 1/2 day.

[debdeepbhowmick]

Madam this very useful video for me, but I have a question that if this data set contain few blank cells along with blank rows at the same time then what is the best solution to delete all blank rows ?

[chrysanth]

Is there any chance that uploading an unstable Access file into Power Query, could corrupt the Access file itself?

[simonoganga]

Wonderful stuff, you can not analyze data that is not clean, you'll only end up with beautiful visualizations but incorrect insights

[racheldeeghan]

I need help in excel with how to create a histogram for a class.

[deroshnivallo]

Hi, I cant access the follow along material. The suggested link points me to drop box deleted files. Can you assist please?

[adhamdodzy]

Hello
how can I get the files to follow along with the video because it requires sign-up when I get to the link below
and many thanks in advance

[olusolaaiki]

Hi, i cant access the excel material to follow up with the video.

[fredericostuckenbruck]

I have never seen so bad explanation about a simple thing