7 Excel Tips to Make You More Productive at Work

Excel is used in almost all the big corporations and a lot of small and medium businesses.

If you work in an office and have to deal with data, there is an extremely high chance you have Microsoft Excel installed on your system.

In the past five years, we have seen a data explosion.

According to a study by Domo, 2.5 quintillion data is generated every day. And that number is only going to go up every single day.

And what happens when this much data is generated? There needs to be a way to make sense of this data and get insights from it.

And this is where tools like Excel come in so handy.

While most of the people use Excel for simple data entry work only, Excel is actually built to do a lot of analysis (and can now handle millions of rows of data as well).

In this article, I will cover 7 simple, yet extremely useful, tips that can help you save time in your day to day work.

Let’s get started!

Using Filters to Drill Down Large Data Sets

Imagine you’re working with a huge sales data worksheet and your walks over and asks – “Can you quickly tell me how many sales Mark manages in Q2?”

There is no need to panic.

Take a deep breath and use Excel filters.

Excel Data Filter allows you to quickly filter a data based on a text value, number value, or date value.

So all you need to do is:

1. Apply Data Filters
2. Filter the data where the name is Mark
3. Filter the Date that lies in Q2

And Ta-Da!

You’ll have your answer in seconds.

You can find the Excel Data Filter option in the Data tab in the ribbon.

Using a Drop Down to Make Data Entry Easy

Drop Downs are amazing when it comes to data entry in Excel.

Expecting your users to manually enter data in Excel and make sure the data is consistent is too much to ask.

Instead, you can simply create a drop-down list and allow the users to select what they want to enter in a cell, and it will get entered instantly.

Here are the steps to create a drop down list in Excel:

1. Select the cell in which you want to create the drop-down
2. Click the Data tab.
3. Click on Data Validation.
4. In the Data Validation dialog box, select list and specify the source (cells that have the items you want to show in the drop down).
5. Click OK.

Remove Duplicates with a Click on a Button

When working with data, there is always a chance that there would be duplicates.

To allow you to quickly remove duplicate records from a data set, there is an inbuilt functionality in Excel that will do it.

Suppose you have a dataset as shown below from which you want to remove duplicate records:

Here are the steps to remove duplicates from this dataset:

1. Select the entire dataset.
2. Click the Data tab in the Excel ribbon.
3. Click on Remove Duplicates icon.
4. In the Remove Duplicates dialog box, select the columns you want to include when identifying duplicates.
5. Click OK.

The above steps would instantly remove all the rows that have duplicates in it.

Find and Replace Text (without tireless scrolling and searching)

Sometimes, you may need to change a few cells by replacing the existing data point with something else.

Doing this manually can take up a lot of space and is also error-prone.

Thankfully, there is a way to quickly find a specific text and replace it with something else (in seconds).

This can be done using the Find and Replace functionality in Excel.

Suppose you have the following dataset and you want to replace the word HR with Human Resources:

Here are the steps to do this:

1. Select the data in which you want to make the replacement.
2. Use the keyboard shortcut Control + H (hold the control key and then press H).
3. In the Find and Replace dialog box that opens, enter the text you want to find in the ‘Find What’ field and the text you want to replace in the ‘Replace with’ field.
4. Click on Replace All.

The above steps would instantly find and replace all the text in the dataset.

Analyze Data with Pivot Tables

Pivot Table is an absolutely amazing tool.

It allows you to summarize a huge amount of data in a matter of seconds.

And the best part is that you don’t need to know anything in Excel to use this.

If you can drag and drop header names into boxes (which I am sure you can), you can easily analyze data using Pivot Tables.

The benefit of using a Pivot Table (apart from the fact that it quickly summarizes the data) is that allows you to quickly create scenarios and summaries.

Imagine you have a sales meeting and your boss asks you which product line sold the best in Q1.

You can easily create a pivot table and let him know the answers in seconds.

And now if he asks you to tell him the product that did worse in Q2, you can simply change the pivot table headers and you’ll have your answer in less than 2 seconds.

And the best part is, you don’t need to know any formula or code in Excel to do this.

Fetch Data from anywhere using VLOOKUP

Lock Row Headers and Columns (to Avoid Going Back and Forth)

Analyze Data with Pivot Tables