5 Excel Tips for People Who Work with Data

Excel is an amazing tool to record, manage, analyse data. It has many great tools and features that help you do advanced data analysis and reporting.

In this article, I will share 7 Excel tips that will help people who work with data in Excel on a regular basis.

#1 – Use Pivot Tables

Pivot Tables can be a great tool when you have a lot of data and you want to analyse it.

Pivot Tables allow you to quickly slice and dice the data so you can summarize it quickly. It is a great tool when creating reports for management.

You don’t need any advanced skills to use Pivot Tables. It’s has a simple drag and drop interface where you can select data dimensions and it will instantly summarize it for you.

#2 – Use Freeze Panes to Lock Headers

One of the frustrating things about working with large datasets is to scroll back and forth to keep track of data points.

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.

There is a feature to Freeze Panes in Excel that solves this issue.

Here are the ways to use Freeze Panes to lock header rows/columns:

• Select the cell above which you have the headers and to the left of which you have the headers. So, if you have headers in the first row and first column, 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,when you scroll away from headers, it would still always be visible.

#3 Use Filters to Drill Down the Data Set

Excel filters are great when you have a huge data set and you want to filter only a part of it.

For example, suppose you have the sales transaction records of many items, but if you want to only see the transactions from a particular item, then you can use filters.

It simply hides all the other rows so you can only see the one that you have filtered.

Here are the steps to enable the filter in a data set:

• Select any cell in the header row.
• Go to the Data tab and click on Filter.

This will make the filter available for all the cells in the header row. Now you can click on the filter icon (a downward pointing arrow icon) and select the items that you want to get filtered.

#4 Remove Duplicates in a Few Seconds

Duplicates often find their way into data sets. These could be problematic if the data is further used in calculations.

Here are the steps to quickly get rid of duplicate records:

• Select the entire dataset from which you want to remove duplicates.
• Go to the Data tab and click on Remove Duplicates.
• In the Remove Duplicate dialog box, check the ‘My data has headers’ option (only if your data has headers), select the columns from which you want to remove the duplicates and then click OK.

It will instantly remove all the duplicate records and show a prompt to tell you how many records have been deleted.

#5 –Use Lookup Function to Fetch Values

Excel has a couple of really useful lookup functions (such as VLOOKUP, INDEX, MATCH, HLOOKUP).

These lookup functions are useful when you have a huge data set and you want to fetch a specific set of data points.

Using the lookup functions, you can specify what you are looking for and the formula would scan the entire data set and fetch that value.

To give you a simple example, if you have a dataset of scores by students in 3 subjects, and you want to know how much Tom scored in Math, you can do that using the lookup functions.

These are my top five Excel tips for people who work with large datasets.