(Excel Tip-1) Trim
TRIM can help you to remove extra spaces from a text string. Just refer to the cell from where you want to remove the spaces and it will return the trimmed value in the result.
(Excel Tip-2) Remove Duplicates
One of the most common things we face while working with large data is “Duplicate Values”. In Excel, removing these duplicate values is quite simple. Here’s how to do this.
- First, select any of the cells from the data or select the entire data.
- After that, go to Data ➜ Data Tools ➜ Remove Duplicates.
- At this point, you have the “Remove Duplicates” window, and from this window, select/de-select the columns which you want to consider/not consider while removing duplicate values.
- In the end, click OK.
Once you click OK, Excel will remove all the rows from the selected data where values are duplicates and show a message with the number of values removed and unique values left.
(Excel Tip-3) Combine Text (Fill Justify)
Let’s say you have words in the range A1: A5 and you want to concatenate all of them in a single cell.
- First, make column A enough wide so that the entire text can be combined into one cell.
- After that, select the entire range.
- Now, go to Home Tab ➜ Editing ➜ Fill ➜ Justify.
(Excel Tip-4) Remove Specific Character
In the range, A1: A5 and you want to concatenate all of them in a single cell. Here’s how to do this with fill justify.
- All you need to do is select that column and open the find and replace dialog box.
- After that click on the “Replace” tab.
- Now here, in “Find What” enter the character you want to replace and make sure to leave “Replace with” blank.
- Now click on “Replace All”.
The moment you click on “Replace All” Excel will remove that particular character from the entire column.
(Excel Tip-5) Combine Text
So, you have text in multiple cells, and you want to combine all the text into one cell. No, this time not with fill justify. We are doing it with TEXT JOIN. If you use Office 365, there is a new function TEXTJOIN which is a game-changer when it comes to the concatenation of text.
Here’s the syntax:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
All you need to do is to add a delimiter (if any), and TRUE if you want to ignore empty cells, and in the end, refer to the range.
(Excel Tip-6) Unpivot Data
Look at the below table, you can use it as a report but you can’t use it further as raw data. No, you can’t. But if you convert this table to something like the one below you can use it easily anywhere.
But if you convert this table into something like the one below you can use it easily anywhere. So how to do this?
(Excel Tip-7) Delete Error Cells
Mostly while working with large data it is obvious to have error values but it’s not good to keep them. The easiest way to deal with these error values is to select them and delete them and these are the simple steps.
- First of all, go to Home Tab ➜ Editing ➜ Find & Replace ➜ Go To Special.
- In the Go To dialog box, select formula, and tick mark errors.
- In the end, click OK.
Once you click OK it will select all the errors and then you can simply delete all by using the “Delete” button.
(Excel Tip-8) Arrange Columns
Let’s say you want to arrange columns from the data using custom order. The normal way is to cut and paste them one by one.
But we also have an out-of-the-box way. In Excel, you can sort columns just like you sort rows and by using the same methods you can arrange them in a custom order.
(Excel Tip-9) Convert to Date
Sometimes you have dates that are stored as text and you can use them in a calculation and further analysis. To simply convert them back to valid dates you can use the DATEVALUE function.
(Excel Tip-10) Negative to Positive
The easiest way to convert a negative number into a positive is by using the.
