4. What should I look at when choosing free Excel practice resources?

#Excel data cleaning exercises and practical cases

Key points of this article

Before cleaning, write down what type each column should be. Only with the standard can you know where the calculation errors are.
Dates, spaces, duplications, missing, splitting and merging, and category unification are the six common types of problems, which can be practiced separately.
Use counting and filtering checks after each step to avoid corrupting the original data
All processing is completed in the copy or auxiliary column first, and then the original value is replaced after confirmation.

If you are looking for practice materials for Excel data cleaning, or are preparing to deal with a batch of messy tables in internship or work, this article is for you. Many people have learned several functions, but they don't know where to start when they open the real data: some dates are text, some are real dates, there are invisible spaces at the end of the name, and the same customer is written in three ways.

This article does not talk about how to repair a specific document, but gives six common exercises and corresponding practical cases. Each question explains what problems there are in the sample data, what results are to be achieved, and how to check whether they are done correctly. You can follow this with the table you compiled, or you can use the data you exported.

After reading, you will get a set of exercise sequences and a checking process that can be used over and over again.

How to practice Excel data cleaning? One sentence conclusion

Define the "clean" standards first, and then deal with them one by one according to the question type, and the effect will be better. When practicing, don't think about solving all the problems at once, just focus on one problem at a time.

your situation	Recommended practices	reason
I just started practicing and I don’t know where to learn from	Complete the six questions in this article one by one in the order they follow.	Problems progress from shallow to deep without interfering with each other
I already know the basic functions and want to speed up	Practice repetitions and deletions first, then practice splitting and merging	These two types of problems can easily affect the summary results.
To process a real export of data	Make a copy first and work only on the copy	You can go back at any time when cleaning errors occur
Data comes from PDF table	First confirm whether the table text can be selected, and then decide on the export method.	The number on the scanned copy may be incorrectly recognized and needs to be checked.

Scenario: A customer list summarized from three channels

Suppose you want to deliver a customer list of about 30 lines. The data comes from three channels: the official website form, the offline registration form, and the PDF form sent by the partner. After the three data were merged, the column names were the same, but the contents were varied. Some dates are written as "2024.3.5", some are "2024/03/05", and some are directly entered as "March 5th". Some phone numbers have spaces and some have dashes. The same company may be written in two ways: "Huayu Technology" and "Huayu Technology" (with an extra space at the end).

When delivering, the other party required statistics by month, filtering by region, and removal of duplicate customers. The stuck part is: if the format is not unified first, the filtering and statistical results will be wrong, and it will be difficult to see where the problem is at a glance.

Complete process

Back up and set standards. Copy the original file and create a new "cleaned version" worksheet. Write down the type of each column on paper: the date column can only be dates, the amount column can only be numbers, and the region column can only be a few values listed in advance. If the original data comes from a PDF table, you can first use the PDF editor of the OmniDoc desktop client to export it to an Excel file; the scanned PDF needs to be OCRed first, and the numbers must also be checked after exporting.
Exercise 1: Unify the date. Column A in the example has a mix of text dates and real dates. First use the column sorting function or DATEVALUE and other functions to try conversion; for unrecognizable writing methods (such as "March 5th"), manually change it to a standard date, and then set the date format uniformly for the entire column. Checking method: Use COUNT to count the number of numeric cells, and use COUNTA to count the number of non-empty cells. If the two are equal, it means that all cells have been converted to dates.
Exercise 2: Remove spaces and invisible characters. Use TRIM to remove leading and trailing spaces and extra middle spaces, and use CLEAN to remove invisible characters that are often brought in when copying from web pages or PDFs. First write the formula in the auxiliary column, confirm that the result is correct, and then paste it into the value to replace the original column. Checking method: Use LEN to compare the number of characters before and after processing. The difference should be equal to the number of deleted spaces and characters.
Exercise 3: Find and process duplicate rows. First clarify the caliber of "repetition": whether the entire row is completely consistent, or whether it only looks at the mobile phone number. Use COUNTIF to count in the auxiliary column. Those with a result greater than 1 are duplicate candidates. Checking method: Screen out candidate rows, manually compare several groups, confirm that they are the same customer before deleting, and keep a backup before deleting.
Exercise 4: Complete or mark missing values. Use filter to find blank cells and count them. Content that can be derived from other fields (such as the region derived from the city) can be completed; for content that cannot be derived, fill in "to be confirmed" and do not leave it blank, otherwise these rows will be missed during perspective statistics. Checking method: Use COUNTBLANK to count the number of blanks, the result should be 0.
Exercise 5: Split and merge text columns. In the example, "Beijing-Haidian-Zhongguancun" needs to be split into three columns: province, district and road. To use the column sorting function, select the delimiter "-". To practice this in reverse, you can use the & symbol to merge the three columns back into one. Checking method: After splitting, the sum of the non-empty numbers in each column should be consistent with the original column, and randomly select 3 rows to compare with the original text.
Exercise 6: Unify category values. "Beijing", "Beijing City" and "BJ" should be unified to the same standard value. First filter the column, view all different values, create a comparison table of "original value - standard value", and then use the search function to replace them in batches. Checking method: Use a pivot table to check the number of categories, which should be equal to the number of standard values in the comparison table.
Cross-check and deliver. Use a pivot table to make statistics by month and region, and check whether the total number matches the original number of rows before cleaning (deduct duplicate rows that have been confirmed to be deleted). After confirming that it is correct, delete the auxiliary column and save it as a new file for delivery.

Where things can easily go wrong

Text date mixed with real date. The performance is that the dates are out of order when sorting or filtering. The solution is to compare COUNT and COUNTA in step 2 until they are equal.
The original data is overwritten when pasted as a value. Once the replacement is completed, the original information cannot be retrieved. The solution is to always keep the original table and all replacements are done in the clean version.
Leading zeros are lost. If the job number "00125" is displayed as "125", statistics and matching will be wrong. The solution is to set the column to text format before importing; if it is lost, use the TEXT function to fill in zeros in digits.
Long numbers are rewritten. When the ID number or bank card number exceeds 15 digits, the end will become 0 or displayed in scientific notation. The solution is to process such columns as text and set the format before importing.
PDF recognition or machine translation error. The number recognized by OCR may recognize 8 as 3, and the category name translated by machine may also be inaccurate. The solution is to conduct sampling verification after exporting, and compare the important figures one by one with the original.

🎯 Which method is suitable for you?

If you are just getting started

Follow the order of the six practice questions, do only one type of question for each question, and check the results using the counting method. Practice the three steps of "backup, processing, and checking" first, and then consider speeding up.

If you are going to work with real data in your internship

First confirm the "clean" standards with the person in charge, such as date format, category name, and repeated judgment criteria, and write them into a list before starting.

If your data mainly comes from PDF tables

First confirm whether text can be selected in the form. If you can select it, clean it directly after exporting; the scanned document needs to be OCR first, and the exported result will be sampled and checked.

If you want to create a process that can be used repeatedly

Memorize the processing order and inspection methods of the six practice questions into a template table. Next time you get new data, follow the same steps, which can save a lot of repeated judgments.

❓ Frequently Asked Questions (FAQ)

1. Where is the best place to find training data?

It is recommended that you compile a sample of about 30 lines by yourself, deliberately mixing in issues such as dates, spaces, duplications, and deletions. You can also use your own consumption records, class schedules and other daily data to practice, which is close to reality and does not involve privacy leakage.

2. Do I need to retain the original data after cleaning?

need. The original data is the basis for traceability. If there is a problem with the cleaned version, you can go back to the original table and process it again.

3. Can you ask the chatbot to help me write the cleaning formula?

Yes, but you need to actually run the formula it gives on the sample sheet and check the results using the method in step 8. You cannot trust it directly.

See if the data source is clear, whether the file size and format are easy to download, whether there is a watermark, and whether you can directly open it in Excel and maintain the original format after practice.

# Summary

To practice Excel data cleaning, the key is not to remember how many functions, but to define clean standards first, and then use counting and spot checks to verify the results. Run the six questions in the sample table completely, and then deal with the real data, it will be much smoother.

Open OmniDoc and process your form file