5 Excel Hacks that Will Make You Insanely Productive

Print Friendly

 

Do you use Excel?

If yes, I am sure you know there are gazillion things that you can do in Excel. However, there are only a handful of tasks that most of us have to do on a daily basis.

Hence it makes sense to know some powerful Excel hacks that you can use in your day to day work and be super productive

Here are my top time-saving Excel hacks:

#1 Quickly Remove Duplicates in Excel

If you get your data from colleagues or databases, there is a good possibility that it may have duplicate records in it.

Don’t Worry!

Here are the steps you can use to delete all the duplicate records in a few seconds

1. Select the entire data set.

Remove duplicates excel

2. Go to the Data tab and within the Data Tools group, you will find the Remove Duplicates option. Click on it.

Data tab excel

3. In the Remove Duplicate dialog box, select the columns from which you want to delete the duplicates. If you want, you can select all the columns by clicking on the ‘Select All’ button.

4. Click OK.

As soon as you do this, Excel removes the duplicate records and show you the number of records it has deleted (in a prompt).

excel prompt

Remember this would alter the original data set. So if you want to keep the original data set intact, create a copy and the use the above steps.

#2 Use Text to column to Quickly Split Cells

Want to split a cell to get the first name and last name in separate cells? Stop doing it manually.

Here is a simple method that will easily split the names (or any such data) into separate cells.

Select the data that you want to split. Go to Data tab, and within the Data Tools group, select the Text to Columns option.

text to columns

In the Convert Text to Columns Wizard:

Step 1: Make sure delimited is selected and click Next.

delimited excel

Step 2: Select Space as the Delimiter and click Next

excel screenshot

Step 3: Select the destination cell and click Finish.

excel screenshot example

This would instantly split the first name and the last name.

splits in excel

#3 Use Freeze pane to Handle Large Data Sets

It’s frustrating to scroll back and forth again and again while working with a large dataset.As soon as you scroll down or move to the right, the headers disappear and you have no idea what a data point is for.

Let me show you a great technique to fix the rows and columns so that these are always visible

Select the cell above which you have the headers and to the left of which you have the headers. So, if I have headers in the first row and first column, I would select cell B2.

Go to View and in the Window group, click on the Freeze Panes option. From the drop down select Freeze Panes. That’s it.

freeze panes excel

Now, no matter how far you wander in the worksheet, the header would always be visible.

#4 Quickly Transpose Data Without Any Formulas

Often you would get a data set where you want the rows to be columns and columns to be rows. If you find yourself in such a situation, don’t start thinking about complex Excel formulas or codes.Just use this simple hack.

  • Select the entire dataset and copy it (use Control + C)
  • Select the cell where you want to get the transposed data.
  • Go to Home and within the clipboard group, select the Paste drop (click on the small downward pointing arrow).
  • Select paste special from the drop down.

paste special excel

  • In the Paste Special dialog box, check the Transpose option and click OK.

Transpose option in excel

This will paste the transposed data in the cell that you selected

#5 Highlight Top/Bottom 10 values in Excel

Heard of Conditional formatting in Excel? I absolutely love it!

It adds a visual layer of analysis on my boring lifeless reports. Let me show you how it works.

Suppose I have a dataset as shown below and I want to highlight the top 10 scores from this list.

dataset

Here is how you can quickly do this using conditional formatting:

Select the scores.

Go to Home tab and within styles click on Conditional Formatting.

Hover your cursor on Top/Bottom Rules and select ‘Top 10 Items…’.

Conditional Formatting.

In the Top 10 Items dialog box, specify the format. In most case, I go with the default ones. Click ok.

This would instantly highlight the bottom 10 scores from the list.

highlighted dataset

Similarly, you can highlight top 5/10 scores as well.

These are my top five Excel hacks that I use almost every day and I can confidently say it has saved me hundreds of hours of time.

What are your favorite Excel hacks? Share with me in the comments section.

Author: Sumit Bansal

This is a post by Sumit Bansal. Sumit Bansal is a spreadsheet geek and loves to share cool Excel tips through his blog and videos. Check out his Free Excel Training. When he is not busy changing the world with his blogs, he can be find wasting time on Netflix.

Share This Post On

Pin It on Pinterest

Share This