Today we are going to discuss about filtering in excel in user interface. Will try to unbox all hidden features in filter to ease your filtering process and to improve the effectiveness.
Lot to learn, let's dive in.
Click in the cell inside the data to apply filter in any of the below methods.
Filter option is located in Data tab -> Sort & Filter group, see below picture.
Shortcut Key:
We can use shortcut key Ctrl + Shift + L to apply filter. This is toggling key stroke. we can use the same shortcut key for on and off the filter. In both of these methods filter applied in header row or first row in data.
Apply filter in middle row of data:
Select any cell in the row where you want to apply filter and press shortcut key
Shift + Space bar. This will select entire row, now press Ctrl + Shift + L.
Expand filter drop-down:
Consider that you are in I1 cell, and we already pressed Ctrl + Shift + L. Instead of clicking the drop-down using mouse, use shortcut key Alt + Down arrow to expand the filter list.
Move to search box in Filter:
After expanding the filter option, instead of pressing down-arrow key 8 times to get into search box, just press shortcut key E. Now the cursor is in search box.
Tab and Space key to select the filter criteria:
From search box area,
Press Tab to get into filter criteria and press Space bar key to uncheck and check the box.
Let me show you the navigation with one example,
We are going to apply filter for East in region column (I Column).
Currently we are in the cell A10 or somewhere else.
Press Alt + F3 to get into name box and type I1 and enter,
Now active cell is I1,
Press Ctrl + Shift + L to activate the filter,
Alt + Down arrow to expand the filter option,
Press E to reach search box,
Press Tab to move into 'Select All' check box,
Press Space bar key to uncheck the box in Select all option,
Now all the boxes are unchecked,
Press Tab until you reach East,
Press Space bar key to check the box and press Enter.
This method applies only for Texts and numbers. If the filter column contains date and you want to apply filter for '20/06/2016' then,
Press Alt + F3 to get into name box and type C1 and enter,
Now active cell is C1,
Press Ctrl + Shift + L to activate the filter,
Alt + Down arrow to expand the filter option,
Press E to reach search box,
Press Tab twice to move into 'Select All' check box,
Press Space bar key to uncheck the box in Select all option,
You can see that dates are grouped in the hierarchy, Year -> Month -> Date.
Press Down-Arrow until you reach 2016,
Press Right Arrow key to get into Month hierarchy.
Press Down-Arrow until you reach June.
Press Right Arrow key to get into date hierarchy.
Press Down-Arrow until you reach date 20.
Press Space bar key to check the box and press Enter.
In Filtering of dates,
Right arrow to expand the hierarchy and Left arrow to shrink the hierarchy.
Filter by selected cell's value:
Consider that, you are in the cell, where the cell contains 'Florida'. Now you want to apply filter for that state.
Right click on the mouse button, at the center you can see filter option, click on the right arrow key and select Filter by selected cell's value as shown below.
That's it, you applied filter for the state 'Florida'.
Shortcut key for Filter by selected cell's value:
Consider that, you are in the cell, where the cell contains 'Pete Kriz'. Now you want to apply filter for that customer name.
Press Shift + F10 + E + V.
Include Filter option in Quick access tool bar:
Right click in any of the tab names in menu bar and select Customize the Ribbon,
In Excel options dialog box,
Select Quick access toolbar, then in 'Choose commands from' option, select Commands not int the Ribbon. In the list box, select 'AutoFilter' and click Add. In the right-side list box, Auto filter gets added and click OK.
I have added AutoFilter option in 4th position in Quick access toolbar. You can move to any position based on your preference.
Now,
Consider that, you are in the cell, where the cell contains 'Seattle'. Now you want to apply filter for that city name.
After you pressed Alt, it will show the sequence of AutoFilter and just press that.
Press Alt + 4. That's it, no other strokes required.
Hack in Filter by Selected cell's value:
In sales column type the filter criteria in a cell below the last cell,
In our case, criteria are to filter the data for the records, sales are less than or equal to 100.
So, type <=100 in K52 cell and press Shift + F10 + E + V as shown below.
Our data is filtered only for the records, where sales is less than or equal to 100.
This is just the beginning; exciting stuffs are in Part-2.
Happy Filtering.
留言