Sometimes all the text could be in lower/upper case or it could be a mix of both. When you inherit a workbook or import data from text files, often the names or titles are not consistent. #6 Change Text to Lower/Upper/Proper Case Now you can manually highlight these, delete it, or type anything into it. This selects all the cells that have anВ error in it. Select Formulas and uncheck all options except Errors.Click on Special Button at the bottom left.Press F5 (this opens the Go To Dialogue box).Set the format and clickВ OK.В This highlights any error value in the selected dataset.In the Rule Description, select Errors from the drop down.In New Formatting Rule Dialogue Box select вЂFormat Only Cells that ContainвЂ™.Go to Home вЂ“> Conditional Formatting вЂ“> New Rule.There are 2 ways you can highlight Errors in Data in Excel: Related: The Ultimate Guide to Find and Remove Duplicates in Excel. If you want the original list intact, copy-paste the data at some other location and then do this. This removes duplicate values from the list. Select the Column(s) from which you want to remove duplicates and click OK.If your data has headers, ensure that the checkbox at the top right is checked.Select the data and Go to DataВ вЂ“> Remove Duplicates.Specify the formatting and all the duplicate values get highlighted.Select the data and Go to Home вЂ“> Conditional Formatting вЂ“> Highlight Cells Rules вЂ“> Duplicate Values.There can be 2 things you can do with duplicate data вЂ“ Highlight ItВ or Delete It. Here are various other ways to multiply in Excel using Paste Special. There is a lot more you can do with paste special operations options. Click OK.В This converts all the numbers in text format back to numbers.In the Paste Special Dialogue box, select Multiply (in operations category).Select Paste вЂ“> Paste Special ( Key Board Shortcut вЂ“ Alt + E + S).Select the cell/range which you want to convert to numbers.Select the cell where you typed 1, and press Control + C.Here is a fool proof way to converts these numbers stored as text back into numbers. This could create serious issues if you are using these cells in calculations. Also, some people are in the habit of using an apostrophe (вЂ) before a number toВ make it text. Sometimes when you import data from text files or external databases, numbers get stored as text. #3 Convert Numbers Stored as Text into Numbers If you want to enter 0 or Not Available in all these cells, just type it and press Control + Enter (remember if you press only enter, the value is inserted only in the active cell).

This selects all the blank cells in your data set. This opens theВ Go To Special dialogue box Click onВ SpecialвЂ¦ button (at the bottom left).Press F5 (this opens the Go To dialogue box).Thankfully, there is a way you can select all the blank cells at once. If there is a huge data set, doing this manually could take hours. You may want to fill all blank cells with вЂ0вЂ™ or вЂNot AvailableвЂ™, or may simply want to highlight it. I often face issues with blank cells in a data set that is used to create reports/dashboards. #2 Select and Treat All Blank CellsД«lank cells can create havoc if not treated beforehand. It removes leading and trailing spaces as well as the additionalВ spaces between words (except single spaces). Here is a neat way to get rid of these extra spaces вЂ“ Use TRIM Function.Д®xcelВ TRIM function takes the cell reference (or text) as the input. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible. #10 Use Find and Replace to Clean Data in ExcelД®xtra spaces are painfully difficult to spot.#6 Change Text to Lower/Upper/Proper Case.#3 Convert Numbers Stored as Text into Numbers.

Clear all formatting excel shortcut