Messy Excel data can be a huge time-waster and a major roadblock to getting real insights. When you deal with an Excel workbook that’s packed with inconsistent entries, missing values, and duplicate rows, navigating it can be a really painful process. At times, it is enough to make you want to shut your laptop and call it a day. In this post, I will share my tried and tested methods for cleaning up even the messiest Excel data.
From simple tricks and powerful formulas to third-party add-ins, I will share practical tips to make your data cleaner, more accurate, and ready for analysis.
10 creative ways to use Excel beyond number crunching
Unexpected Excel use cases you won’t believe
8 Run a spell check
Let’s go over the basics first, shall we? Like all Microsoft 365 apps, Excel comes with a built-in spell checker to fix annoying typos in no time. Here’s how to use it.
- Open a workbook in Excel and selectReview at the top.
- SelectSpellingand let it run a spell check.
If you find a pattern where you have made a typo on a specific word, use theFind and Replace tool to fix it.
- Head to ExcelHome -> Searchand selectReplace.
- Enter a typo in theFind bracket. Replace it with the correct term and clickReplace Allat the bottom.
7 Remove duplicates
There is a high probability that your workbook might have duplicate data or entries without your knowledge. Instead of reviewing the data manually, you can use the built-in tool to get the job done.
- Open your Excel workbook and head to theDatatab.
- SelectRemove Duplicates under theData Toolsmenu.
- Pick a column from which you want to remove duplicates. SelectOK and you are good to go.
6 Use data validation
Data validation is like setting the group rules for your Excel cells. It ensures that only the right kind of data gets in and eliminates any errors or typos. Suppose you are adding inventory details for all your stores. You can use data validation to keep your region entries astute. Here’s how.
- Open your Excel workbook and create a new sheet. Select columns and write down the regions you want to add.
- Head toData -> Data Tools -> Data Validation.
- SelectListand click the up-arrow icon in Source.
- Select values that you want to add and selectOK.
- From now on, whenever you enter new data in the column, a drop-down menu appears. You can use it to insert entries easily.
5 Explore text to columns
Did you receive an Excel workbook that has multiple text in a single column? With a neat add-on, you can split a single column of text into multiple columns. You have the flexibility to choose how to split it up. Let’s check it in action.
- Select the column that you want to split and head toData -> Split to Column.
- You can choose the file type that best describes your data. ClickNext.
- Select the checkmark besideSpaceand check the preview at the bottom.
- ClickNext.
- In the last dialogue box, selectGeneralascolumn data format. SelectFinish.
Check your neatly arranged columns in action.
4 Delete all formatting
At times, your colleagues may go overboard with formatting options in Excel. With lots of colors, misalignment, and background shades, your workbook can feel like a total mess. Instead of selecting individual columns and rows, you can simply remove formatting from your Excel workbook with a simple trick.
Select your database in Excel, and head toHome -> Editing -> Clear -> Clear Formats.
If you have received an Excel workbook withconditional formatting, navigate toHome -> Conditional Formatting -> Clear Rules -> Clear Rules From Entire Sheet.
3 Use formulas and functions
You can even use formulas and functions to clean up extra space and non-printable characters and standardize capitalization. Let’s go over some of the top ones.
- TRIM: It removes leading, trailing, and extra spaces within a text string (=TRIM("extra spaces") returns "extra spaces").
- CLEAN:Removes non-printable characters from text
- PROPER: Capitalizes the first letter of each word in a text string (=PROPER("harvey spectre") returns "Harvey Spectre").
- UPPERandLOWER: Converts text to uppercase or lowercase.
- LEFT, RIGHT, and MID: Extracts specific portions of a text string.
In addition, you can also useCONCATENATEor&,where the function joins text strings from multiple cells. You can refer to our dedicated post to learn thetop Excel functions for your workflow.
You can even useDOLLARto convert a number to text format and apply a currency symbol for better data presentation. Similarly, theTEXTfunction converts a value to text in a specific number format.
If your workbook contains date and time information, useDATE,DATEVALUE,TIME, andTIMEVALUEfunctions to fix incorrect dates and times.
2 Divide your workbook into multiple sheets
This is another crucial part when you want to clean up messy data in an Excel workbook. Instead of jamming a single sheet with tables, PivotTables, charts, slicers, timelines, and more, use different sheets for specific purposes.
For example, you can have a single sheet for your Excel table, create another one for PivotTables and charts, and a centralized dashboard for all your charts to represent the data better.
1 Reasons for data cleaning
Check out the following reasons if you are still in two minds about cleaning up your Excel data.
- The quality of your analysis depends entirely on the quality of your data.
- At times, clean data reveals patterns, trends, and other hidden insights.
- While it may seem tedious at first glance, the practice saves you a ton of time and frustration down the line.
Get more from your data
Overall, Excel offers ample data-cleaning techniques to transform your workbook from a headache into a valuable asset. After all, clean and easy-to-read data is the foundation of accurate analysis and informed decision-making. So, what are you waiting for? Whenever you run into such a chaotic spreadsheet, apply these tricks and unlock the true potential hidden within your data.
Once your workbook data is all cleaned up, go overthese tips to add some professional touch to your spreadsheets.
