In this chapter, you’ll learn different ways to source and import your raw data into Excel. You will then start your preparation of raw data by removing duplicates added in error and filling in the missing parts of your data that will help you prepare the best dataset for analytical use.

The data preparation journey

Data preparation steps

Load and clean data

Importing files

Deduping

Flashy filling

Series filling

Starting Data Preparation in Excel

In this chapter, you’ll learn how to use text category functions to combine text strings with useful variables for better analysis. You will also utilize some of Excel's date and time functions, taking simple date columns to a new level. 

Text and date functions

Options

Text

Date & Time

Which function category?

More functions, better preparation

Equal length

Joining together

Dates and more

Functions for Data Preparation

In this chapter, you’ll learn how to use logical functions to create conditional formulas for new flags and filters for your data. You will combine the logical operators to create nested formulas, specifically using the IF function to create new category data. Additionally, you will understand how to differentiate between notes and comments in Excel and use them effectively to share information with others.

Using logical functions in formulas

Which conditional formula is right?

Logical data preparation

To be, or NOT to be

Functions two by two

Well nested IF functions

Notes and comments

Conditional Formulas

In this chapter, you’ll finish by looking at some important lookup and reference functions available within Excel, specifically the VLOOKUP and HLOOKUP functions. Additionally, you’ll be introduced to the powerful world of PivotTables, which helps you summarize and analyze large volumes of data through dynamic tables.

Lookups and PivotTables

Good arguments

Referencing and summarization

Look up, not down

Pivot. Pivot!

A category of products

Grand total percentages

Congratulations!

Lookups and Data Transformation

Exercises and Datasets

Excel Formula Translations

Course Glossary

In this course, you’ll learn how to prepare and clean raw data in Excel worksheets.  You'll learn to bring in data from different sources and prepare the data by filtering, deduping, and organizing your columns and rows to ensure subsequent analysis tasks are done as efficiently as possible.
As well as the built-in features Excel offers, you will learn to use different functions to handle and manipulate dates and text strings. You will be introduced to logical functions that will allow you to create new flags and categories within your raw data while understanding how different logical functions can be combined in nested formulas. You will also understand how to use lookup functions in Excel to bring in data from different sheets and identify specific results within large datasets. Finally, you will get your first glimpse of PivotTables - a powerful Excel feature that allows you to summarize and analyze large volumes of data using dynamic tables.

The videos contain live transcripts you can reveal by clicking "Show transcript" at the bottom left of the videos.
The course glossary can be found on the right in the resources section.
To obtain CPE credits you need to complete the course and reach a score of 70% on the qualified assessment. You can navigate to the assessment by clicking on the CPE credits callout on the right.

<h2>Starting data preparation in Excel</h2> In this course, you’ll learn how to prepare and clean raw data in Excel worksheets. This course focuses on helping you utilize the numerous features in Excel that allow you to bring in data from different sources and prepare the data by filtering, deduping, and organizing your columns and rows to ensure subsequent analysis tasks are done as efficiently as possible. <h2>Useful functions to prepare data</h2> As well as the built-in features Excel offers, you will learn to use different functions to handle and manipulate dates and text strings. <h2>Nesting logical functions</h2> You will be introduced to logical functions that will allow you to create new flags and categories within your raw data while understanding how different logical functions can be combined in nested formulas. <h2>Lookups and new table types</h2> You will also understand how to use lookup functions in Excel to bring in data from different sheets and identify specific results within large datasets. Finally, you will get your first glimpse of PivotTables - a powerful Excel feature that allows you to summarize and analyze large volumes of data using dynamic tables.

Introduction to Excel

Gain a deep understanding of how to prepare Excel data through logical functions, nested formulas, lookup functions, and PivotTables.

Data Preparation in Excel

Understand how to prepare Excel data through logical functions, nested formulas, lookup functions, and PivotTables.

Excel Fundamentals

Which function category?

Data Preparation in Excel

Latihan interaktif praktis