Day 02 of 100 Days Of Code in Data Analytics : Basic Excel Filter

Day 02 of 100 Days Of Code in Data Analytics : Basic Excel Filter

Hello readers !

This is the second day of my #100DaysOfCode in Data Analytics.

I am really fortunate to have discovered Leila Gharani's YouTube channel. I would recommend her channel to any novice since she presents the basics of MS-Excel in a very straightforward manner.

I opted to study basic and advanced filters in Excel on Day 2 of #100DaysOfCode.

Basic Excel Filter

The basic Excel filter (also known as the Excel Autofilter) allows you to see only certain rows of an Excel spreadsheet while obscuring the rest.

A drop-down menu displays in each cell of the header row when the Excel autofilter is applied to the header row of a spreadsheet. This gives you a variety of filter choices for determining which rows of the spreadsheet should be shown.

Shortcut : CTRL + SHIFT + L

The above shortcut can be used to insert filter as well as to remove the filter.

Dates, Numbers, and Texts may all be filtered with these basic filters. Date-specific filters will be applied to columns containing dates, while text-specific filters will be applied to columns containing text. Number-specific filters will be applied to columns that include numbers.

The following Excel Filters are found in the Date Filter:

  • Equals...
  • Before...
  • After...
  • Between...
  • Tomorrow
  • Today
  • Yesterday
  • Week based - Next Week, This Week, Last Week
  • Month based - Next Month, This Month, Last Week
  • Quarter based - Next Quarter, This Quarter, Last Quarter
  • Year based - Next Year, This Year, Last Year
  • Year To Date
  • All Dates in the Period
  • Custom Filter...

The following Excel filters are found in the Number Filters:

  • Simple conditions - Equals, Does Not Equal, Greater Than , Greater Than Or Equal To, Less Than, Lesser Than Or Equal To, Between.
  • Top 10... (Displays rows containing the top N values)
  • Above Average(Display numeric values that are above the average value)
  • Below Average(Display numeric values that are below the average value)
  • Custom Filter(This brings up the same dialogue box that appears when you pick individual Numeric Criteria (Equals, Does Not Equal, and so on), allowing you to define up to two numeric conditions)

The following Excel Filters are found in the Text Filter:

  • Equals...
  • Does Not Equal...
  • Begins With...
  • Ends With...
  • Contains...
  • Does Not Contain...
  • Custom Filter...

How to use AutoFilter ?

To use the Excel autofilter, follow these steps:

  1. Select the range of cells you wish to filter by clicking on them.

filter1.png

  1. Select Filter from the Data tab on the Excel ribbon (or use the CTRL+SHIFT+L shortcut). Each of your header cells will now have drop-down menus that can be used to pick the rows to be shown.

filter2.png

The contents of the cells in the filtered column may be checked using this drop-down menu. The user can choose to see all rows or cells with one or more selected values (selected by checking or unchecking entries from the list).

Screenshot (118).png

You may also filter by colour in current Excel versions. This might be the colour of the text or the backdrop of a cell.

More complicated filtering may be done with the Advanced Excel Filter than with the Excel Autofilter.

Acknowledgement

This blog simply represents my own ideas and opinions (based on my limited knowledge) and should not be used as a replacement for legitimate sources. I'd welcome corrections in the comments if I ever make a mistake or if you disagree!

End note

These were the things that I was able to explore today, and I am looking forward to doing additional hands-on learning tomorrow. If you have any further information on the aforementioned concepts, please share it in the comments section below. Don't forget to connect me on Twitter and Github.

Have a great time !