Data Cleaning, Analysis & Management Tools in Excel

Leila Gharani
A free video tutorial from Leila Gharani
Microsoft MVP, Bestselling Instructor
4.6 instructor rating • 9 courses • 164,455 students

Lecture description

In this section we’ll take a look at more Excel features that will help us organize and structure our data, so it makes analysis easier later on.

Learn more from the full course

Excel Essentials for the Real World (Complete Excel Course)

Microsoft Excel Beginner to Professional. Includes Pivot Tables, Power Query, NEW Formulas... (Excel 365, 2019 & 2016)

10:43:04 of on-demand video • Updated November 2020

  • Master the ESSENTIAL Excel formulas and features to accelerate your career
  • Learn Excel from scratch or fill in the gaps to become immediately confident
  • Learn to use Pivot Tables, Pivot Charts, Slicers and Time Lines for Interactive Excel Reports
  • Hacks and hidden Excel features from a Microsoft MVP with 15+ years of Experience
  • Explore the potential of Power Query. You'll be surprised at what you achieve with a bit of Power Query knowledge.
  • Get lifetime access to a proven and state-of-the-art Excel course including challenges, quizzes & exercises
  • Learn what's new in Excel for Microsoft 365 and how these changes impact the way you use Excel
  • Updated to include the new FILTER, XLOOKUP, SORT & UNIQUE functions
English We've already seen some features that help us clean data. By clean data, I mean convert raw data into information we can use into proper datasets. For example, this is not a proper dataset. We have too much information in one column. It's not going to be easy to analyze the data by just division or by just region. It also has empty rows in between. This one on the other hand is a proper dataset. Each record is separate in its own cell. We have proper column headers. We have no empty rows in between. This way it's easy to use formulas like "SUMIFS" to get totals by division or by region. We've already learned a technique that helps us to get this done. Flash Fill. To split each category in its own column I just need to give Excel one example, sometimes more examples, then apply Flash Fill. It's a quick way to get this data into proper format. In this section we'll take a look at more Excel features that will help us organize and structure our data so it makes analysis easier later on. We'll start with sorting data. I'll also cover a trick you can use to go back to your original unsorted list, if you need to. We'll also take a look at grouping your data and automatically adding sub totals inside your dataset. This is actually really helpful especially if you work in accounting. Filtering is another great Excel feature so you can extract what you need from larger datasets. Some datasets require a lot more work to get them in proper format. You might need to delete a lot of empty rows, fill empty cells with values, remove duplicates, and find and replace certain cells. Even find and replace specific formatting with another one. So let's say the color scheme for the company you work with changes, you need to change the colors you use in your file. Knowing this feature which I admit is quite hidden is going to save you a ton of time. Now once you get to your proper dataset, it's good practice to convert it to an official Excel table. Why? You're going to get a lot of benefits from doing that one click. We'll cover these towards the end of the section. Lots of great tools coming up, so let's get started.