Data Filtering in Excel

by on May 2, 2017

 

Data Filtering in Excel

Data Filtering in excel will allow you to extract information quickly from large data. It is a simple but great analytical tool in excel. In this short article, we will touch on how to apply a filter in excel 2016.

Advanced Data Filter

Advanced Data Filter

Filtering data in excel is extremely easy to learn. In the above example, select the data table including the headers and then go to data tab and select the filter as highlighted in yellow. Drop down menu appears as highlighted in the below image. From Drop Down menus, you can select data for the sales team, product or individual sales figure. Data Filtering option also allows you to perform multi level filtering. For example, you can arrive at product wise sales for a particular team.

Advanced Data Filter

Advanced Data Filter

In the above image, click on the Sales Team drop-down menu. Select Sales Team A and Click OK. Image provided below for your easy guidance.

Your final report will be same as below.

Assume, you need data for product “AD-99C” for sales Team A in the above image. For that to happen, you need to click on the Product Drop down menu and only select the product “AD-99C” and Click OK and you are done.

Advanced Data Filtering

This will cover the section in case you want to copy the filter data to another sheet based on criteria. Now let look at the advanced filter function. Assume you have sales data and you want to extract data for a particular product on a different sheet.

Advanced Data Filter

Create the separate sheets where you want to place your data. Sheet 2 will be for Fruits and sheet 3 will be for vegetable reports. Now create the criteria. For example for sales for each product the criteria will be:

  1. Product

Fruits

 

2. Product

Vegetables

You will be on the sheet where you want to place your data in order for this function to work. Click in the fruit sheet and go to the data tab and select advanced in the filter options.  A dialog box will open.

Advanced Data Filter

Advanced Data Filter

Now in the “List Range” Box, you will select the data table from the sheet 1, in the “criteria range” select the criteria (A1:A2) and from the fruit sheet “Copy to” box select the cell where you want to place your data. Kindly check the option of “copy to another location”. click OK and your data will be posted there.

Advanced Data Filter

Advanced Data Filter

By following the same steps, you can create data for each product.

Filtering data by Color

In case you have data color either through conditional formatting based on criteria or colored in some other way. You can filter the data by color.

Step 1: Assume you have a sales report and you want to highlight top 10 and bottom 10 items. Select the data column, go to the conditional formatting from the home tab. Click on the top/bottom Rules and select the top 10 items. Select custom color and choose a blue color from the options and click OK. In a similar way, select bottom 10 items and choose an orange color.

Step 2: Select the data column again, go to the data tab and click on the filter.

Step 3:  Select the data column, click on drop down in the sales column, click on the filter by color. Select the color and click ok. Your data will should now have been filtered by color.

Filtering data by Color

In order to download the file, please click here.

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *