Tutorials References Exercises Videos Menu
Paid Courses Website NEW Pro NEW
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP HOW TO W3.CSS JAVA JQUERY C C++ C# R React

Excel Tutorial

Excel HOME Excel Introduction Excel Get Started Excel Overview Excel Syntax Excel Ranges Excel Fill
Filling Double Click to Fill
Excel Move Cells Excel Add Cells Excel Delete Cells Excel Undo Redo Excel Formulas Excel Relative Reference Excel Absolute Reference Excel Arithmetic Operators
Excel Addition Excel Subtraction Excel Multiplication Excel Division
Excel Parentheses Excel Functions

Excel Formatting

Excel Formatting Excel Format Painter Excel Format Colors Excel Format Fonts Excel Format Borders Excel Format Numbers Excel Format Grids Excel Format Settings

Excel Data Analysis

Excel Sort Excel Filter Excel Tables
Tables Table Design Table Resizing Remove Duplicates Convert Table to Range Table Style
Excel Conditional Format Excel Highlight Cell Rules
Highlight Cell Rules HCR - Greater Than HCR - Less Than HCR - Between HCR - Equal To HCR - Text That Contains HCR - Date Occurring HCR - Unique Values
Excel Top Bottom Rules
Top Bottom Rules Above Below Average
Excel Data Bars Excel Color Scales Excel Icon Sets Excel Manage Rules (CF) Excel Charts
Charts Bar Charts Stacked Bar Charts Column Charts Stacked Column Charts Pie Charts Line Charts Stacked Line Charts 100% Stacked Line Charts Radar Charts
Excel Charts Customization

Excel Case

Case: Poke Mart Case: Poke Mart, Styling

Excel Functions

AND AVERAGE AVERAGEIF AVERAGEIFS COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS IF IFS MAX MEDIAN MIN MODE OR STDEV.P STDEV.S SUM SUMIF SUMIFS VLOOKUP XOR

Excel Exercises

Excel Exercises


Excel Filter

❮ Previous Next ❯

Excel Filter

Filters can be applied to sort and hide data. It makes data analysis easier.

Note: Filter is similar to formatting a table, but it can be applied and deactivated.

The menu is accessed in the default Ribbon view or in the Data section in the navigation bar.

Applying Filter

Filters are applied by selecting a range and clicking the Filter command.

It is important to have a row of headers when applying filters. Having headers is useful to make the data understandable.

Note: Filters are applied to the top row in a range.

Like in the example below, the dedicated row is row 1.

Copy Values

Let's apply filters to the data set, step by step.

  1. Select range A1:E1

  1. Click the Sort & Filter menu

  1. Click the Filter command

New buttons have been added to the cells in the top row. This indicates that the Filter was successfully applied. The buttons can be clicked to access the different Sort & Filter options.

A Non-Working Example

Lets delete row 1 (the header row) and apply filters to the new row 1, to see what happens.

The filter is applied and has replaced the header row. It is important to dedicate a header row for the filter.

Filter options

The filter options allow for sorting and filtering.

Applying filter keeps the relationship between the columns while sorting and filtering.

Clicking the options button opens the menu.

Sorting

Ranges can be sorted and the relationship between the columns is kept.

Sort Ascending (A-Z) sorted from smallest to largest.

Sort Descending (Z-A) sorts from largest to smallest.

You can read more about Sorting in a previous chapter.

Filtering

Filters can be applied to hide and sort data.

This is helpful for analysis, to select the data that you want to see or not.


Example Filter

Use the filter option to filter on Pokemon that is Type 1, Bug.

Step by step

  1. Click the drop down menu on C1 () and choose the Filter option. This is the Column which holds the Type 1 data.

Note: "Items" are the different categories in that column; Grass, Fire, Water and so on.

All items are checked by default. The checked items are the ones that are shown. Uncheck to hide.

  1. Uncheck all items, except Bug, which is the type that we want to show.

  1. Click OK

Good job! The range was successfully sorted by Type 1, Bug. All shown Pokemons are Bug type.

Note: The unchecked rows are hidden, not deleted.

This is explained by looking at the row numbers. The numbers are jumping from 1 to 11 and 16 to 22. The rows in between are hidden.

Note: Checking the items will have the rows shown again.

Another Example

Use the filter option to filter the Pokemons which have Type 1, Bug and Type 2, Poison.

  1. Click the filter option in D1
  2. Uncheck all items except Poison
  3. click OK

That is on point! We have sorted the range based on Type 1, Bug and Type 2, Poison. The filter option is helpful to make ranges easier to analyse.



❮ Previous Next ❯