Top Excel Tips and Tricks (HACKS) to Make You a PRO (Free PDF) [PART-7]

Last Updated: February 28, 2024

- Written by Puneet

(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.

  1. First, select any of the cells from the data or select the entire data.
  2. After that, go to Data ➜ Data Tools ➜ Remove Duplicates.
  3. 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.
  4. 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.

  1. First, make column A enough wide so that the entire text can be combined into one cell.
  2. After that, select the entire range.
  3. Now, go to Home Tab ➜ Editing ➜ Fill ➜ Justify.

Related: Concatenate (Combine) Cells with a Comma

(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.

  1. All you need to do is select that column and open the find and replace dialog box.
  2. After that click on the “Replace” tab.
  3. Now here, in “Find What” enter the character you want to replace and make sure to leave “Replace with” blank.
  4. Now click on “Replace All”.

The moment you click on “Replace All” Excel will remove that particular character from the entire column.

Related: Remove First Character from a Cell in Excel

(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?

here are the simple steps you need to follow.

(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.


Complete Tip

(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.


Other ways to convert text to date

(Excel Tip-10) Negative to Positive

The easiest way to convert a negative number into a positive is by using the.