In case you’re constructing a big spreadsheet in Google Sheets or have to learn one which has a hefty quantity of information, it’s useful to use filters to it. A filter helps you to disguise particular knowledge (equivalent to numbers or textual content) inside a spread of cells that you choose with the intention to see what your spreadsheet seems to be like with out this data. In different phrases, it filters out the information that you simply don’t wish to see in your spreadsheet.
For instance, you possibly can design a filter that exhibits solely cells which have numbers which might be 50 or better inside them, and one other filter that exhibits solely cells containing numbers of 30 or much less. You would then change between these two filters to see your spreadsheet in these alternative ways — after which return to your spreadsheet’s authentic state with its full cell knowledge.
[ New to Google Sheets? See our Google Sheets cheat sheet ]
Filters vs. slicers
In Google Sheets, you possibly can refine your spreadsheet’s knowledge utilizing filters or slicers. A slicer does largely the identical factor as a filter, but it surely’s a toolbar that you simply embed into your spreadsheet. It makes your spreadsheet a bit extra interactive, functioning as a handy interface that you simply or others can use to filter cells.
For instance, you possibly can set a slicer subsequent to a chart or desk to let somebody utilizing your spreadsheet shortly take away values from the chart or desk and see the filtered leads to the chart or desk.
Making a filter
Choose a spread of information cells in your spreadsheet. On this instance, we’ll choose C4 to C11.
On the toolbar above your spreadsheet, click on Knowledge > Create a filter.
Contained in the left, topmost cell that you simply chosen, click on the filter (striped triangle) icon. This may open a dropdown panel with type and filter choices.
IDG
Select a sort of filter to use to the chosen cells. (Click on picture to enlarge it.)
Type the order of the chosen cell knowledge
The primary choices you see on this panel are for sorting the chosen cells. In contrast to utilizing a filter, sorting your knowledge doesn’t really disguise any of the information; it merely rearranges the cells you’ve chosen within the order you select.
You’ll be able to type the numbers or textual content contained in the cells (beneath the topmost chosen cell) in ascending or descending order. You can too type by shade if the cell background or textual content is a distinct shade out of your spreadsheet’s default colours.
In case you type the numbers or textual content through this panel, the motion is utilized instantly to the cells that you simply chosen for this filter.
Filter the chosen cell knowledge
Under the kind choices within the panel are the filtering choices for the cells you chose. You’ll be able to filter by shade (of the cell background or textual content), situation, or values.
Filter by values: This selection is expanded by default within the dropdown panel. Under the search field is a listing of all of the values (numbers or textual content objects) within the chosen cells, with a checkmark subsequent to every one. (Relying on what number of cells you chose, you may need to scroll to see all of the values.) Utilizing the search field, you possibly can seek for a selected quantity or textual content within the vary of cells that you simply chosen. You can too use the “Choose all” and “Clear” hyperlinks to test and uncheck all of the values without delay.
IDG
Uncheck the values you wish to disguise out of your spreadsheet. (Click on picture to enlarge it.)
In case you uncheck a quantity or textual content merchandise within the checklist beneath the search field and click on OK on the backside of the panel, the row that comprises the cell with the quantity or textual content you unchecked shall be eliminated out of your spreadsheet. Don’t fear — this row hasn’t been deleted. The filter you created has merely hidden this row, displaying your spreadsheet with out it.
Filter by situation: There are numerous methods to filter by situation, equivalent to displaying solely objects that include sure textual content, objects with a sure date, or objects with numbers between two explicit values. Right here’s an instance that provides you a primary concept the way it works: Let’s filter the chosen cells to point out solely objects that include numbers better than 10.
Click on Filter by situation. Click on the field with None inside it. From the lengthy menu checklist of filter variables that opens, scroll down and choose Larger than.
Contained in the entry field beneath “Larger than,” kind 10. Scroll to the underside of the panel and click on OK.
IDG
Specify the parameters of your conditional filter. (Click on picture to enlarge it.)
Your spreadsheet now exhibits solely the rows with cells that include numbers better than 10. The rows for the cells that contained numbers decrease than 10 have been hidden by the filter.
IDG
The spreadsheet after the conditional filter has been utilized. (Click on picture to enlarge it.)
Filter by shade: In case your spreadsheet is formatted with completely different textual content or background colours (not easy alternating colours), you should utilize this filter to point out solely rows of a selected shade.
Click on Filter by shade, then select both Fill Colour or Textual content Colour from the menu that seems. Choose the colour that you simply wish to retain. The rows formatted with different colours shall be hidden.
Edit a filter
If you apply any filter to your spreadsheet, the striped triangle icon within the topmost chosen cell turns right into a funnel icon. To regulate what it’s filtering, click on the funnel icon. This reopens the filter dropdown panel.
Restore your spreadsheet to its authentic (unfiltered) state
On the toolbar above your spreadsheet, click on Knowledge > Take away filter.
IDG
Choose Take away filter to return the spreadsheet to its authentic kind. (Click on picture to enlarge it.)
Be aware: In case you used this dropdown panel to type the cells that you simply chosen for this filter, the actions above will not restore them to their authentic unsorted state.
Managing your filters
You may give your filter a reputation and add extra filters, every of which may present your spreadsheet in numerous methods. You’ll be able to edit the settings for these filters or delete them.
Title a filter: On the toolbar above your spreadsheet, click on Knowledge > Filter views > Save as filter view. A black toolbar will seem alongside the highest of your spreadsheet, and your spreadsheet’s columns and row headings shall be highlighted in black. This means that you simply’re now within the filter supervisor.
IDG
The filter supervisor helps you to add, edit, identify, delete, or take different actions on filters. (Click on picture to enlarge it.)
On the left of the black toolbar, click on contained in the entry field to the proper of “Title:” and sort a reputation in your filter.
Add one other filter to your spreadsheet: Choose a spread of cells that you simply wish to create a brand new filter for.
On the toolbar above your spreadsheet, click on Knowledge > Filter views > Create new filter view. In case you weren’t already within the filter supervisor, it can seem. Kind in a reputation in your new filter at higher left.
Click on the striped triangle icon within the first cell of your new chosen cell vary and set your new filter’s parameters.
Change the vary of cells for a filter: On the black toolbar above your spreadsheet within the filter supervisor view, click on contained in the entry field to the proper of “Vary:” and edit or kind a brand new vary of cells for the filter to manage.
Exit the filter supervisor: On the higher proper, click on the X.
Swap to a different filter: By creating and naming a number of filters within the method described above, you possibly can change amongst them to view your spreadsheet in numerous methods.
On the primary toolbar above your spreadsheet, click on Knowledge > Filter views. From the menu that opens, choose the filter identify. The spreadsheet will seem with that filter utilized, and the filter supervisor will open on the similar time.
IDG
Choose a filter view from the menu to view your spreadsheet with that filter utilized. (Click on picture to enlarge it.)
Duplicate a filter: If you wish to create a brand new filter that’s primarily based on an current one, open the filter you wish to copy within the filter supervisor (click on Knowledge > Filter views and choose the filter). Click on the gear icon on the higher proper, and choose Duplicate from the menu that opens. You’ll be able to then rename and edit the brand new filter.
Delete a filter: Open the filter you wish to delete within the filter supervisor, click on the gear icon on the higher proper, and from the menu that opens, choose Delete.
Making a slicer
On the toolbar above your spreadsheet, click on Knowledge > Add a slicer. The “Slicer” sidebar will open alongside the proper. A panel (“Choose an information vary”) will seem over your spreadsheet. (In case you don’t see this panel, click on the Choose knowledge vary icon (it seems to be like a grid) on the Knowledge tab within the Slicer sidebar, and the panel will pop up.)
The panel exhibits urged knowledge ranges you can choose, or you possibly can click on within the spreadsheet and choose a spread of cells, or choose a whole column by clicking the column header. On this instance, we have now clicked to pick out C5 to C11 on the spreadsheet.
IDG
Choose an information vary for a slicer. (Click on picture to enlarge it.)
If you’ve made a variety, click on OK, and a slicer toolbar will seem over the spreadsheet.
Title the slicer: Let’s give your new slicer a novel identify. On the higher proper of the “Slicer” sidebar, click on Customise and sort a brand new identify for the slicer within the “Title” entry field.
IDG
Give the slicer a novel identify. (Click on picture to enlarge it.)
The best way to use the slicer toolbar
Click on the striped triangle icon on the slicer toolbar. This opens a dropdown panel for the slicer that appears and works just like the one used to create a filter, however with out the choices to type cells or to filter them by shade.
The steps outlined above (beneath “Filter the chosen cell knowledge”) for filtering by values and filtering by situation work the identical for a slicer dropdown panel.
IDG
The slicer toolbar helps you to or different customers filter the spreadsheet by situation or by values. (Click on picture to enlarge it.)
To regulate what the slicer is filtering, you or one other person can click on the funnel icon on the left of the slicer toolbar. Be aware that the identical slicer can filter each by situation and by values.
Managing your slicers
You’ll be able to edit, copy, delete, transfer, or resize a slicer. First, click on to pick out the slicer. A body with eight dots will seem round it.
Resize the slicer: Click on and drag considered one of these dots to resize the slicer to be bigger or smaller.
Transfer the slicer: Click on-and-hold the slicer, then drag it to a different space in your spreadsheet.
Edit, copy, or delete the slicer: Click on the three-dot icon on the higher proper of the slicer; from the menu that opens, choose the operate that you really want.
If you delete the slicer, your spreadsheet shall be reset again to its authentic state, displaying any cells that had been hidden by the slicer.
IDG
Click on the slicer’s three-dot icon to see a menu with additional actions. (Click on picture to enlarge it.)
Set default filters for the slicer: If you wish to protect the filters you’ve set for a slicer so another person will see the identical filtered knowledge by default, click on the three-dot icon on the higher proper of the slicer and choose Set present filters as default from the menu that seems.