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.
Here are the steps you can use to delete all the duplicate records in a few seconds
1. Select the entire data set.
2. Go to the Data tab and within the Data Tools group, you will find the Remove Duplicates option. Click on it.
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).
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.
In the Convert Text to Columns Wizard:
Step 1: Make sure delimited is selected and click Next.
Step 2: Select Space as the Delimiter and click Next
Step 3: Select the destination cell and click Finish.
This would instantly split the first name and the last name.
#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.
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.
- In the Paste Special dialog box, check the Transpose option and click OK.
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.
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…’.
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.
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.