Data Cleaning in Excel for Beginners: Day 2 Practical Guide

 

Data Analytics Practical Day 2 — Clean and Organize Data in Excel

For SmartTechMitra · Practical / Hands-on · Beginner Level

SEO settings

• SEO title: Data Cleaning in Excel for Beginners: Day 2 Practical Guide

• Focus keyword: Data Cleaning in Excel

• URL slug: data-cleaning-in-excel-practical-day-2

• Meta description: Learn data cleaning in Excel step by step. Remove duplicates, fix missing values, correct formatting, and prepare a dataset for data analysis.

• Category: Data Analytics Course

• Tags: Excel Data Cleaning, Data Analytics Practical, Remove Duplicates, Excel Tutorial, Data PreparationData Cleaning in Excel for Beginners: Day 2 Practical Guide

Introduction

Before a data analyst creates charts, calculates sales totals, or builds a dashboard, the data must be organized and checked.

Real-world data is rarely perfect. A sales spreadsheet may contain duplicate orders, missing customer names, inconsistent product categories, extra spaces, or numbers stored as text. If these problems are ignored, the results of an analysis may be misleading.

In this practical lesson, you will learn how to clean a small sales dataset using Microsoft Excel. You do not need programming knowledge to complete the exercise.Data Cleaning in Excel

1. What is data cleaning?

Data cleaning is the process of identifying and correcting data-quality problems so that a dataset is more consistent and suitable for analysis.

Common problems include:

• Duplicate records

• Missing values

• Inconsistent spelling

• Incorrect date formats

• Extra spaces

• Numbers stored as text

• Invalid or impossible values

Cleaning does not mean changing data simply to make it look better. Every correction should be justified, and uncertain values should be investigated rather than guessed.

2. Create your first practice dataset

Open Microsoft Excel and enter the following sample data into cells A1 to F8.

Order ID	Product	Quantity	Price	City	Status
1001	LED Driver	10	500	Delhi	Delivered
1002	SMPS	5	600	Noida	Delivered
1003	DALI Driver	4	1500	Ghaziabad	Pending
1002	SMPS	5	600	Noida	Delivered
1004	LED Driver		500	Delhi	Pending
1005	dali driver	2	1500	Delhi	Delivered
1006	SMPS	3	600	Noida	Cancelled

This is a deliberately imperfect practice dataset. It contains a repeated order, a missing quantity, and inconsistent capitalization in the product name.Data Cleaning in Excel

Your objective: identify these issues and prepare a cleaner version without losing important information.

3. Step 1 — Make a backup

Before editing:

1. Open Excel.

2. Select File → Save As.

3. Save a copy named Data_Analytics_Day_2_Practice.xlsx.

4. Keep the original data on a separate worksheet named Raw Data.

5. Create another worksheet named Clean Data for your corrections.

This makes it easier to compare the original data with the cleaned version.

4. Step 2 — Format the dataset as a table

1. Select your dataset, including the headings.

2. Press Ctrl + T.

3. Confirm that My table has headers is selected.

4. Click OK.

Excel will format the dataset as a table and add filter controls to the headings.

You can now sort or filter the data more easily.

5. Step 3 — Identify duplicate records

The sample dataset contains Order ID 1002 twice with identical details.

To remove the duplicate from your working copy:

1. Select the dataset in the Clean Data worksheet.

2. Open the Data tab.

3. Select Remove Duplicates.

4. Choose the columns you want Excel to compare.

5. Click OK and review the result.

For this exercise, comparing all six columns identifies the repeated row because every field matches.

Important: Do not remove records solely because the product name or customer appears more than once. A customer can place multiple legitimate orders. Confirm the business rules before deleting anything.

6. Step 4 — Handle missing values

Order 1004 has no quantity.

To investigate it:

1. Select the Quantity column.

2. Open the filter menu.

3. Look for blank values.

4. Check the original invoice, order record, or other reliable source.

5. Enter the correct quantity only when it is verified.

If the correct value cannot be found, keep the field blank or mark it according to your reporting rules. Do not enter 0 automatically because a blank quantity and a zero quantity mean different things.

7. Step 5 — Standardize product names

The dataset contains both DALI Driver and dali driver.

These represent the same product in this example, so you can standardize the capitalization.

For a simple dataset, use Find and Replace:

1. Press Ctrl + H.

2. Enter dali driver in the Find field.

3. Enter DALI Driver in the Replace field.

4. Choose Replace All after checking the matches.

For larger datasets, Excel's PROPER, UPPER, and LOWER functions can help standardize text. However, abbreviations such as DALI, LED, and SMPS may need special handling because automatic capitalization can change their intended format.

8. Step 6 — Check numbers and prices

Review the Quantity and Price columns.

• Confirm that quantities are numeric.

• Confirm that prices are numeric and use the expected currency.

• Check for accidental spaces or symbols stored as text.

• Look for unexpected negative values or unusually large amounts.

For this dataset, you can format the Price column as currency using Home → Number → Currency.

Formatting changes how a value appears; it does not necessarily fix a value that is stored incorrectly. Verify the underlying data when calculations fail.Data Cleaning in Excel

9. Step 7 — Calculate sales value

Add a new column called Sales Value.

In the first data row, enter:

=[@Quantity]*[@Price]

If your range is not formatted as an Excel table, a normal cell formula might look like this:

=C2*D2

Copy the formula down the column.

For order 1001, the result is:

\(10 \times ₹500 = ₹5,000\)

For order 1002, the result is:

\(5 \times ₹600 = ₹3,000\)

Remember that an order with a missing quantity will not produce a reliable sales value until the quantity is resolved.Data Cleaning in Excel 

10. Step 8 — Check the final dataset

Use this checklist before analysis.

Data cleaning checklist

0 of 7

Keep the original raw data unchanged

Investigate and remove the confirmed duplicate

Resolve or document the missing quantity

Standardize product names

Check number and currency formats

Add the Sales Value formula

Review the cleaned dataset for remaining errors

11. What can you learn from the cleaned data?

Once the dataset is verified, you can start asking useful questions:

• How many orders were delivered?

• Which product generated the most sales?

• How many orders are pending?

• What is the total sales value of valid orders?

• How many orders were cancelled?

One important distinction: a cancelled order should not automatically be counted as completed revenue. Your reporting rules should define how delivered, pending, and cancelled orders are treated.

12. Practical homework

Complete these tasks in your Excel workbook:

1. Create the Raw Data and Clean Data worksheets.

2. Identify the duplicate order and explain why it is a duplicate.

3. Investigate the missing quantity.

4. Standardize the product names.

5. Add the Sales Value column.

6. Filter the data to show only delivered orders.

7. Write three observations about the dataset.

Extra challenge: Create a PivotTable that summarizes the number of orders by product.

13. Interview questions and answers

Q1. What is data cleaning?

Data cleaning identifies and corrects data-quality issues before analysis.

Q2. How do you remove duplicates in Excel?

Select the dataset, open Data → Remove Duplicates, choose the appropriate comparison columns, and review the result.

Q3. How should missing values be handled?

Investigate the source, correct the value if verified, or document that the value remains missing. Avoid guessing.

Q4. What is the difference between a blank and zero?

A blank means no value is recorded; zero is an actual numerical value.

Q5. Why should raw data be preserved?

Keeping an original copy allows you to audit changes, compare results, and recover from mistakes.Data Cleaning in Excel

14. Frequently asked questions

Can I learn data cleaning in Excel without coding?

Yes. Many common tasks can be completed with Excel menus, filters, formulas, and tables.

What should I learn after data cleaning?

Learn sorting, filtering, basic formulas, conditional formatting, and PivotTables before progressing to dashboards and SQL.Data Cleaning in Excel for Beginners: Day 2 Practical Guide

Is removing duplicates always safe?

No. Some repeated-looking rows represent legitimate transactions. Identify the correct business key and verify the records first.Data Cleaning in Excel

Conclusion

Data cleaning is one of the most practical skills in data analytics. By learning to identify duplicates, investigate missing values, standardize text, validate numbers, and calculate sales values, you can prepare more reliable data for analysis.Data Cleaning in Excel

The key habit is to verify first, correct carefully, and document your changes.Data Cleaning in Excel for Beginners: Day 2 Practical Guide