We all do it. We make a spreadsheet and we start adding data. But what happens when you have ten empty tables and one full one? It becomes hard to find the table with your data in it. And that’s not even the worst part: your data is now scattered and doesn’t make any sense. Not to worry, we’ve got your back! This beginner’s guide will teach you how to clean and organize your data so that it makes sense again!

From how to install Excel to sorting formulas, this guide will help you learn about everything you need to know about cleaning up those messy spreadsheets. This Tutorial Covers:

#1 Get Rid of Extra Spaces

#2 Select and Treat All Blank Cells

#3 Convert Numbers Stored as Text into Numbers

#4 Remove Duplicates

#5 Highlight Errors

#6 Change Text to Lower/Upper/Proper Case

#7 Parse Data Using Text to Column

#8 Spell Check

#9 Delete all Formatting

 #10 Use Find and Replace to Clean Data in Excel

 

Worksheet Basics

Worksheets are the foundation of any Excel document. A workbook is made up of one or more worksheets, and each workbook should have at least one worksheet.

The first step to cleaning up your data is organizing your sheets. The following are some basic steps for working with sheets:

* Navigate to the sheet you would like to edit

* Click on the tab at the bottom of the screen for that sheet

* Edit what you need to do—from adding formulas to deleting rows, columns, or cells

* Save your changes by clicking “File” then “Save As.”

If you’re new to Excel, it’s a good idea not to change any formatting until you’re sure that you know what you’re doing. This will help keep things simple and clear. And remember: if something doesn’t seem right in your data, delete and re-enter it!

– #1 Get Rid of Extra Spaces – You can’t have a cell with more than one space in it so delete any extra spaces you find.

– #2 Select and Treat All Blank Cells – Select all blank cells and change them using the Formula bar; this will make sure that all your cells are treated equally.

– #3 Convert Numbers Stored as Text into Numbers – If there is any number stored as text, convert it to numbers by using the Formula bar or by right clicking on the cell and selecting “Format Cells”.

– #4 – Remove Duplicates – Check for any duplicates in your spreadsheet by sorting both columns alphabetically (down). Delete any duplicate rows that appear at the top. Keep doing this until you find no more duplicates.

– #5 Highlight Errors – Right click on any cell where an error occurs and select “Error Checking”. If there are no errors found, then there should be no highlighting applied to the cells.

– #6 Change Text to Lower/Upper/Proper

 

How to clean up messy data with just a few clicks

If you’re like me, you’ve had that moment where you’re scrolling through rows and rows of data. It’s messy. I mean, there are three extra spaces in one cell alone. You’ve tried to remove them, but for some reason they keep coming back. That’s okay! We all make mistakes now and then. All it takes is a few clicks to clean up your data.

Here are the steps you need to take:

1) Get rid of extra spaces by pressing the enter key twice after every line of text.

2) Select all cells with blank spaces by clicking on “Blank” in the Select Data bar at the top right of the screen, then click “Select All.” Next, go to Data > Text to Columns > Delimited > Comma (,)

3) Convert numbers stored as text into numbers by going to Data > Text to Columns > Delimited > Space (. )

4) Remove duplicates by going to Data > Find & Replace> Find what:= “””(.*?)(s*)(.*?)”””. Then replace with:=”””1″”>

 

How to organize data in excel

Spreadsheets are wonderful for sorting, storing, and analyzing data. The only problem is that if you’re not careful, your spreadsheet can get out of hand pretty quickly.

That’s where this guide comes in! This post will teach you how to clean up your messy spreadsheet and make it easier to use. With these tips, you’ll be able to sort and analyze your data like a pro.

 

How to remove extra spaces

Removing extra spaces is a great way to clean up your spreadsheet. If you have any extraneous spaces at the beginning or end of a cell, Excel will put those in front of or behind that data if you copy and paste it.

For example, say you have the following formula in cell A1:

=A1+B1

But after copying and pasting it to cell B2, the formula would read as follows:

=A1+B111

To remove these extraneous spaces, simply use Find and Replace to find all instances of an extra space anywhere in your spreadsheet. Then, replace it with an empty space. This will ensure that everything is consistent throughout your spreadsheet.

 

How to select, treat, and convert all blank cells

A lot of spreadsheets have blank cells—cells that are empty. And it’s easy to forget about them when you’re adding data! But you should never forget about these blank cells because they could lead to errors in your data.

Blank cells can cause problems when you sort a spreadsheet. This is because the sorting formula will only look at the numbers in the cells with any data. If there are any blank cells in your spreadsheet, then your sorting formula won’t work correctly and your table of data will be incorrect.

The solution? Select all of the selected cells and use ‘Treat Blank Cells As’ to remove all of the blank cells from your spreadsheet. In Excel, this means going through each cell and telling Excel what you want it to do if that cell is blank: either leave it blank or enter a number.

 

How to remove duplicates

Duplicates are a serious data problem. They’re common when you’re working with smaller data sets, but even if you have a lot of data, it’s easy for many duplicates to slip through the cracks.

Luckily, Excel has a built-in function that can help remove duplicates from your data set. The function is called “Remove Duplicates” and it’ll weed out all the duplicate entries in your table or spreadsheet—automatically!

You can find this function by going to the Data tab on the Ribbon and clicking “Data Tools” and then “Data Analysis”. Scroll down and find “Remove Duplicates.”

To use the Remove Duplitions Function:

1) Click on Data Analysis

2) Click on Remove Duplicate

3) Select which columns you want to delete

4) Click OK

5) Check your work before you click OK!

 

How to change text to lowercase, uppercase, or proper case

One of the most fundamental things you can do to clean up a spreadsheet is to change text. You can do this by going into “Home” and clicking on “Font”. Once there, select the option for “Change Case”. There are three options—upper case, lower case, and proper case.

Once you have changed your text to a desired case, you should format it as a title or subtitle. This will make it easier to read and present your data.

 

How spell check can clean your data

You might not think that spell check would be a helpful tool for cleaning your data, but it actually is. If you have any typos in your formulas, Excel will recognize them and give you an error. But with spell check, you can find all the errors that result from typos and grammar mistakes.

Spell check can also help clean up text that’s been copied and pasted into a spreadsheet. For example, if someone typed “We’re going to the store” and then copied and pasted it into a spreadsheet, you’ll see “we’re going to the store” as two different words when they should be merged as one word. If you didn’t notice this mistake before they saved the document, now you know how to fix it!

Another way spell check can clean up your data is by merging words together. For example, if people type “turner’s book” instead of “turner’s book,” spellcheck will merge those two words together so that the sentence reads correctly—it will read as “turners book.”

 

How find and replace can clean up your Data

Finding all instances of text in your data isn’t hard, but finding the special characters can be a hassle. Luckily, you can find and replace those pesky symbols with ease.

It’s important that you know how to find and replace items because it’ll save you time every time you’re working on a spreadsheet. For instance, if you have multiple cells containing hyphens in them, you can just find all instances of – and replace it with nothing.

This is helpful when the hyphen is supposed to be symbolizing something else or separating different data points. You could also use this process if there are any other symbols being used throughout your spreadsheet that you don’t want showing up.

While many people know how to find text using find and replace, they may not know how to select the specific cells they need to clean up. The first thing is making sure your cursor is inside the cell with the text you want to delete or change. Once selected, click on ‘Find & Replace’ (located below the ribbon) and then open ‘Find’.

Now type in what you’re looking for and select ‘Replace’. After doing this once more time, hit enter and voila!

Conclusion.

It’s time to clean up those messy spreadsheets. With these ten steps, you’ll have a spreadsheet with all the data you need and none of the extra stuff.