Filter gives you the ability to view data having certain value(s) in column(s).
In my previous post, about Sorting, we learnt how to reorganize data and make it more presentable. Even after sorting, the number of rows visible to you will remain same. If you want to focus on a smaller dataset, then using filters can save your day.
To apply filter, click on a cell within the dataset on which you want to apply filters. Now, there are two ways to apply filter –
- Go to Home tab and click on Sort and Filter button in Editing group. Select Filter from the dropdown menu.
- Go to Data tab and click on Filter.
As soon as you click on Filter, a downward facing arrow will appear in the cells having column titles.
Now, if we click on the arrow, we will see multiple options to filter the data in that column. Also, options to filter data will vary according to the type of data (Text, Number or Date) in the column.
When we click on the dropdown button, Excel will display distinct values available in that column. We can check or uncheck the box against distinct values displayed in the dropdown. This will make only rows having selected values visible in the sheet.
We can also see that the icon of the arrow changes when a filter is applied on that column.
Instead of manually looking for values and then selecting them for applying filter, we can also search for a value by typing in search bar. This will show all values having the keyword being searched by us. Press enter to display all the rows having that value or you can decide the value(s) that should be displayed by checking/unchecking boxes against those values.
Note – Following points can help you avoid incorrect results due to application of filters or sorting.
- Make sure that there are no empty rows or columns in the dataset/table.
- Column’s title should be preferably in a single row.
- Avoid using the same title for more than one column.
Sorting can help you extract meaning from data.
At times, we come across sheets full of data and it is really difficult to make sense out of it, usually due to randomness. Sorting can help you organize data in a more presentable format. Doing so, will give you the ability to quickly locate required details and use it for your benefit.
- Select any cell within the table which you want to sort. Note: Please make sure that there are no empty rows or columns in the table to be sorted.
- Click on Data in the toolbar.
- Under Data, you will find a section named – ‘Sort & Filter‘.
- Click on Sort. A new popup window will appear with some options to determine how data is sorted. Let’s discuss the options available for sorting.
- Add Level – In case you want data to be sorted based on values in multiple columns, the you need to add levels. Excel will sort the data based on the order of levels. We can select the Column, Sort On (Values, Cell Color, Font Color or Cell Icon) and Order (it varies depending on option selected for Sort On)
For Example –
- Level 1 – Column A – Sort On Value – A to Z
- Level 2 – Column B – Sort On Value – A to Z
- Level 3 – Column C – Sort On Value – smallest to largest
In above case data in Column A will be sorted in alphabetical order, then Column B will be sorted in alphabetical order and finally Column C from smallest to largest.
- Delete Level – Used to delete existing level.
- Copy Level – This can be used to copy an existing level and then slightly modifying the criteria such as column name, sort by or order. This is usually done when someone wants to apply similar criteria to another column(s) as well. Just changing column name for copied level will do the trick.
- Move Up or Move Down arrows – These are used to change the order/priority of Levels.
- Options – A popup window will appear if you’ll click on Options. In this window you can select Case-Sensitive option to sort data while taking case-sensitivity in consideration (Values starting with lower case letter will come before UPPERCASE). WE can also select the orientation i.e. whether data needs to be sorted from top to bottom or left to right.
- My data has headers – Excel will usually determine on its own if you have a header for the column/row, based on the difference in formatting or it being the first cell of row or column. If Excel doesn’t recognize header or considers the first cell as the header, even if it isn’t, then you can manually check or uncheck it.
Let’s learn more about Sorting using an example.
You have an Excel sheet having data about employees in your company and want to group it on the basis of departments. You also want employee names to be sorted on the basis of their experience in descending order.
In order to get data in required format, you need to apply level based sorting in following order –
- Department – A to Z
- Hire Date – Oldest to Newest
Note# Using Sort, as mentioned above, will move related cells in a row along with the column values which are sorted. It means that if Employee names are sorted then corresponding details of that employee will also move with it.
CAUTION – It would be prudent to take backup of the original sheet, especially when it is the only copy available to you.
In case you are planning to edit certain range of cells then following tip can help you save some time
1. Select the range of cells that needs to be edited. Note: We selected the cells starting from top-left column (i.e. A1 in this case).
2. Start typing and the data will start getting mentioned in the first cell i.e. A1 in this case.
3. Once you are done entering data in the first cell, press TAB key on your keyboard to move to next cell in the selected range.
4. If you have reached the last cell in a row of the selected range, then pressing TAB key will take you to first cell in next row.
Continue entering data, one cell at a time and soon you will reach the last cell in the selected range. Now, if you press the TAB key, then Excel will take you to the first cell in the selected range.
Practical Application – This technique can be used when you are supposed to enter data related to a particular person/item in each row and data in each column is of same nature like customer detail having Name, Address, Contact number, etc.
Life can be easy if you know how to navigate within an Excel Worksheet.
Following are few tips to help you quickly find your way through sheets full of data.
- Ctrl + Arrow Key – This will help you reach last/first cell having data in the sheet. Let’s say that you are at cell C5 and quickly want to reach right most cell having data in the same row, then you can simply press Ctrl and Right Arrow key. This will take you to cell F5. Similarly, you can navigate to cell C1, A5 or C10 using Ctrl and Arrow key for respective direction.
- Shift + Ctrl + Arrow Key – In case you also want to select the cells between the current and the outermost cell having data, then press Shift+Ctrl+Arrow key for respective direction.
- Name Box – If you know column & row name of the cell you want to visit, then just enter the same in Name Box and Excel will take you to that cell, irrespective of your current location on worksheet.