Formula | Chapter 3 | Month

'MONTH' formula

This formula returns the month 1-12. This formula simply picks the month in the date.

Syntax
=Month(Serial_number)
Observe the illustration below.

Month Formula ILL 1

Explanation:

Under alternative method the formula picks the month from a text string. 

'NETWORKDAYS' formula

This formula calculate working days between two dates.

Syntax

=Networkdays(Start_date, End_date, holidays)

Observe the illustration below

Networkdays ILL2

Explanation: 
The formula above returns 262 days. (Try It) 

NETWORKDAYS.INTL formula

This formula is a bit modification of the 'networkdays' formula.

Extra parameter weekend is added.

Syntax

=Networkdays.intl(Start_date, End_date, weekend, holidays)

while writing formula weekend can be picked up from the auto drop down box. 

Networkdays.intl ILL3

Explanation: 
The above formula returns 314 days. (Try it) 

'NOW'  formula

This formula returns the current date & time.

It actually accepts system's date and time. This is widely used formula. It usually don't require any parameter from user.

Syntax

=Now()

This formula above returns the current date and time.

(Since I am writing this blog at the below mentioned date and time.)

01-12-2019 10:23.

(Things to remember: This formula update itself automatically)

There are some variations available to the above formula.

Say (01-12-2019 as base date)

we calculate the 7th day date from the present base date,

formula is 

=Now()+7

Returns 8-12-2019 10:23

 =Now()-2.25 

Returns date 2 days 6 hrs ago. Since .25 represents a qtr. Each qtr comprises of 6 hrs in a day.

 'SECOND' formula

This formula returns the 'second' ranging from 0-59.

Syntax

=Second(serial_number)

Second ILL4

Formula | Chapter 2 | Edate

'EDATE' formula

Edate is used to calculate due date or maturity date.
Syntax

=Edate(start_date, months)
start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months maturity.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 25-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 25-11-2019 (date of maturity)

'EOMONTH' formula

It returns the serial number of the last day of the month before or after a specified number of months.

Syntax

=Eomonth(start_date, months)

start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months date.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 30-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 30-11-2019 (date of maturity)

'HOUR' formula

Returns the hour of a time value, and the result varies between 0 to 24 only (whole number only....no 18.25). This formula works with decimal places. Say, in case of 1.25 it ignores 1 and works with .25 only.

Syntax 

=Hour(serial_no)

For instance

=Hour(1.25) 

returns 6

Again

=Hour(.25) 

returns 6

Actually .25 of 24 is 6........... (25% of 24 is 6)

 

ILL 1

Explanation:
Observe the ILL 1 carefully.

'ISOWEEKNUM' formula 

Returns the week number for the given date.

Syntax

=ISOWEEKNUM(date)

For instance

Cell A3 has date value  1-1-2019 in date format.

=ISOWEEKNUM(A3)

Returns 1i,e first week of the year 2019.

In case of  7-01-2019 formula returns 2.

'MINUTE' formula

The formula returns the minute of a time value. Minute is represented as integer, ranging from 0-59.

The formula accepts decimal places. No whole number.

Representing percentage.

Syntax

=MINUTE(serial_number)

For instance

=MINUTE("12:35 PM")

Returns 35

 

 

Formula | Chapter 1 | Date & Time

Formulas can be accessed by clicking at the 'Formulas' tab. 

Function key and 'Insert function' button serves the same purpose. 

'Formula bar' keep track of your formula. Not only formula, what you write in a cell is highlighted in the formula bar. 

When we click Insert Function / Function, Insert Function window pops open.

Insert Function Window

All type of formula resides in 'Insert Function' window. 
And if we click select a category combo box we can find the category of formula in excel.

Date & Time 

Interestingly Excel stores dates as sequential serial numbers.

And 1st January 1900 is the base year, and the serial is 1.

Accordingly, 1st January 2008 has a serial number 39448 since 1st January 2008 is 39447 days after 1st January 1900.

'Date' formula

Returns the number that represents the date in Microsoft Excel.

Syntax

=Date(year, month, day)

Example

Date(2019,03,23).

Returns 23-03-2019.

Date function

 'Datevalue' formula

Converts the stored date into serial number.

Syntax

=DateValue("text")

Example

Date("1-1-2008")

Returns 39448

This function is mostly useful where worksheet contains dates in text format.

'Day' formula

Returns the day value from 1-31. And it calculates accordingly.

Syntax

=Day(serial_number)

Example

Day(100)

Returns 9
In fact representing 10th April. 

'Days' formula 

Calculates number of days from a base date. It can calculate backward too. Again the base date should not be later than 1-1-1900. 

Syntax

=Days(end_date, start_date)

Days360 formula 

Us or European method of approximation of days calculation from a base date.
Syntax
=Days360(start_date, end_date, method)
There are two methods
False=US method of approximation of 30 days.
True= European method.

Formula | Auto Sum | Deduct | Multiply | Divide

This is the first dedicated post on Excel Formula.
To access formulas click Formulas tab.
Even we can start with formula under Home Tab too.

Under Home Tab

Excel is always formula ready. One can start dealing with formula at any space. 

Things to remember

• Formula always starts with =
• While writing formula ignore upper case or lower case criteria, just start typing. 
• Instead of typing the cell values while writing formula one can link or denote the corresponding cell by clicking at the cell. Say, want to multiply F2 to G2, only type = and then click at F2 cell then type * lastly link G2 by clicking at G2 cell. Simple......

Lets start

Sum 

How to sum up cells with values?
Formula Syntax
=SUM(B3:B14)
(B3 to B14 cells are just imaginary)

Alternatively

=B3+B4+B5+B6.......................so on
Result will be same as before

For instance, we are going to sum up the values of cells F3 to F13
and display the summation at F14.

• Just select the cells F3 to F114.
• Under Home tab click 'Auto Sum'.

Excel will automatically make the sum of cells F3 to F13 and display the resultant figure at F14.

Alternatively
  

• At F14 cell start writing the formula as =sum(
• At this instance just select the F3 to F14 cells altogether.
• Close the bracket.
• Press Enter.

 

Deduct

Say we are going to deduct the cell value F13 from F14 cell value.
Very simple logic.
Write the formula
=F13-F14

Again
We are going to deduct 100 from cell F13 Value.
=F13-100

Multiply

In excel or precisely computer recognize * asterisk as a sign of multiplication.

Say, we want to multiply cell value F13 with G13 and display the result in H13.

At H13 cell write the formula 

=F13*G13

Division

Excel as well as Computer recognizes division with the sign '/'.

Say dividing  F3 cell with F2 cell.

Syntax is 

=F3/F2

Very simple logic.

Auto sum function is always very handy in all instance.

If we observe carefully it combines all function at once.

Observe the ILL 1 below.

ILL1

 

Explanation: 
Auto Sum function combines all.

Go to | last cell | visible cells only

Last Cell

Last cell denotes the cell that contains data and/or formatting.
Even one deletes the data and/or formatting of the cell, Last Cell function identifies that cell. 

Last Cell

Procedure

• Click any where in the sheet.
• Press Ctrl+G and click 'Special..' or Click 'Find & Select' under Home Tab and click 'Go to Special...'.
• Tick the Radio button associated with 'Last Cell' function.
• Click Ok.

Visible Cells Only

'Visible Cells Only' function identifies those cells that are visible in a range. It helps to identify the filtered rows or columns when filters are enabled in a sheet. 

Even it helps to identify any hidden row or column in the sheet.

It is useful.

Conditional Formats

'Conditional Formats' function identifies the cells with conditional format embedded.

Data Validation

Data validation function identifies cells with data validation embedded. Further sub categorized as 'All' and 'Same'.

Go to | Precedents | Dependents

Precedents and Dependents feature sometime proves useful.

Cell Precedents

This function is applicable to cells that contain formulas only.
Precedent cells are cells that contribute 'directly' or 'indirectly/All levels' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cell G1 has two precedent cells namely E1 and F1.
Indirect/All levels precedent cells don't directly contribute to formula result but used as reference. 

Precedent cells are often used to identify irregularities in formula.

How to identify precedent cells?

• Select the cell (to identify precedents)
• Press F2 - This function colorize the precedent cells but is limited to the active working sheet.
• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Precedents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut Ctrl+[
This shortcut identify precedent cells in the active sheet.

Cell Dependents

This function is applicable to cells that contain formulas only.
Dependent cells are cells that contribute 'directly' or 'indirectly/All level' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cells E1 and F1are dependent on G1. There must be at least one dependent cells in case of formula. 

Hence before deleting any formula cell one should go through the precedents and dependents options.

How to identify dependent cell? 

• Select the cell that you want to identify dependency.

• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Dependents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut

Ctrl+]

So before deleting any cell one should check the dependency too.

Alternatively

Precedents and Dependents can also be found under 'Formulas' tab.

Under Formula Tab 

ILL 1

Explanations:

G2 & G3 cell contents are the multiplications of E2 and F2, E3 and F3 respectively. 
Active the cell F2 and click 'Trace Dependents'.
Active the cell G3 and click 'Trace Precedents'.
To remove the arrows click 'Remove Arrows' (further sub categorized into 'Remove Dependent Arrows' and 'Remove Precedents Arrows')
 

Go to | Row Differences | Column Differences

Row Differences

Excel has a very important function to find out differences between various rows. The function is called Row Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

 

ILL 1 Row Differences Option

Lets illustrate with a data range.

ILL 2 Data Range

You are requested to go through the above selected data range. Where A2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly row wise. Of course some are common.
After highlighting the data range.

Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Row Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In the 2nd row content of cell C2 is different from cells A2 & B2.
One can argue that the content of cell A3 is different from cell contents of B3 & C3. So why B3 & C3 is highlighted? Why not A3? Since B3 & C3 are similar and A3 is dissimilar. 


Lets observe the data range (ILL 1) once again. 
We can see that the default active cell is A2 (white), so the basis for comparison starts from column A. 

According to the logic since the content of cells B3 & C3 are different from content's of cell A3, hence B3 & C3 has been selected.

The basis can be shifted by pressing Tab key (shifting the active white cell). Result would be different.

Again

Shifting the active cell to cell B3

Result

Explanations: 
Since the basis of comparison is shifted to cell B3, result is different from previous one.Here A3 cell's content is different from B3's content (basis) and C3's content, hence A3 has been highlighted.

Column Differences

Excel has a very important function to find out differences between various rows. The function is called Column Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

ILL3 Column Differences

Lets illustrate with a data range.

ILL4 Data Range

You are requested to go through the above selected data range. Where B2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly column wise. Of course some are common. After highlighting the data range. 


Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Column Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In case of the column C the contents are different C2 cell's content is different from cell's content of C3 & C4. Hence C3 & C4 has been highlighted.


In case of column differences the basis of comparison is row wise.
If we observe carefully the data range (ILL4) we will see the default active cell in the selected data range is cell B2 and the cell lies in 2nd row. As a result the basis of comparison is the 2nd row.

But if we move the active cell (white cell in ILL4) to 3rd row (by pressing Tab key) and follow the procedure result will be different.

Changed Result

 

Go to | Special | Blank

Microsoft Excel provides a dedicated dialogue box from where you can access some particular group of cells.

• Shortcut Ctrl+G.
• Click 'Special'.

Alternatively

• Under Home Tab click 'Find & Select'.
• Click 'Go to Special'.

Comments

You can find radio button associated with 'Comment' is by default selected when you access the Go to special dialogue box.

Click Ok.

The cells in the range with comments will be greyed out.

Constants

This option refers to cells that contain constants.

There are other sub categories.

• Numbers.
• Texts.
• logical.
• Errors.

Formulas

Formula options refer to cells that contain formulas.

There are other sub categories

• Numbers.
• Texts.
• Logical.
• Errors.

By default all are usually checked.
Say if you want to find only formula that relates to numbers check mark only 'Numbers'.

Blanks

This function denotes those cells that are blank within the data range.

It also recognizes only space/spaces within a cell but the cell is also blank. It will not consider this cell while selection. Since it is not blank technically.

Typically what I believe this function recognizes data column wise.

If the datasheet contains any cell that is non contiguous to other cells but has data in it, it will consider entire column of that cell as blank. Observe the exhibit below.

 

Blank Cells greyed out

The cell marked with Red square has spaces, Blank function didn't consider the cell while selecting blank cells. Also the column J has been considered as it has a single cell of data (Binder). Again the column I has been considered as blank as no data in it but contiguous to main chunk of data. 
But columns K, L, M etc are not considered.

Current Region

Current Region here denotes the entire list or data.

Click any where in the data range and select Current Region, excel will grey out the entire data range. Very effective way to select a large piece of data.

Current Array

It will select entire array (we will discuss array in later posts) if active cell is contained in an array.

Objects

Go to function with 'Objects' selected denotes graphical objects including charts and graphs in the worksheet.

 

Go to | Name Range

Go to lets you move swiftly to a specific line, range, cell, row, column in the workbook.

How to activate 'Go to' window? (Shortcut Ctrl+G)

• Under the Home Tab click 'Find & Select'.
• Click Go to.

Go to window

Instance 1
Selection of Cell D15.

• Under Reference text box type D15. 
• Click Ok.

One thing to remember D15 signifies the cell name as per excel. 
Cursor will move to the intersection of D column and 15th Row.

Instance 2
Selection of Cells A1 to D15.

• Under Reference text box type 'A1:D15'.
• Click Ok.

Go to function can even recognize customized name of cells, range and columns etc.

Instance 3
Selection of non contiguous cells A3 to A4 & C9 to G9 & A11 to G17.

• Under the Reference text box type A3:A4,C9:G9,A11:G17
• Click Ok

Instance 4

Selection of a particular row say 9

• Under the Reference text box type 9:9.
• Click Ok.

Again selection of multiple row say 9,10

• Under the Reference text box type 9:10
• Click Ok.

Instance 5

Selection of a particular column say D

• Under the Reference text box type D:D
• Click Ok

Again selection of columns say D & E.

• Under the Reference text box type D:E
• Click Ok.

Even Go to function can identify any name range which user provides.

• Select the range.
• In the name box type the desired name (advised to ignore digits and put underscore in between). (for instance excel_excel is the desired name selected for the sake of discussion).
• Press Enter.
• You can see in the name box 'excel_excel' has appeared as the customized name of the range previously selected.

Just highlight the desired name from the above box. 
Click Ok.

Find & Replace | Replace

Replace

Replace is an important function in Excel.
May be it is convenient to alter the cell content where the number of cell is less, but when data is huge Replace function excels.

One can alter/change/remove the content of cells by use of Replace function.

It is located in 'Find & Select' function under Home tab.
Short Cut 'Ctrl+F.'

Easy and simple format just 'Find What' and to 'Replace With'. 

Replace is an intelligent function to remove or delete any particular content of cells from the data sheet. 

Say we want to remove or delete the text 'Binder' from the datasheet. 
Just type 'Binder' in 'Find What' text box, and keep 'Replace with' text box blank. Press the button 'Replace All', it will just replace or delete all text 'Binder' from the data sheet and keep the cells blank as we didn't specify anything to fill in. 
Now the problem may arise that it will delete or replace the text 'Binder' from all cells but where there may be a text called 'Hard Binder' it will remove text 'Binder' and text 'Hard' will only be left in the cell. 

To overcome this problem

• Click the 'Options<<' button.
• Tick the box 'Match entire cell content'.

Now the replace function will ignore the text 'Hard Binder'.

Replace Format

It is an intelligent option to change the format of cells.

Say we want to make 'Binder' text into 'Bold' in all the cells where the content is 'Binder'.

• Press Ctrl+F (Find & Replace Dialogue box pops up).
• Activate Replace Tab.
• Click 'Options > >' button.
• Write 'Binder' in both the textbox (Find What & Replace With) since we are going to replace 'Binder' with 'Binder' just with different format.
• Click 'Format' button relative to 'Replace with' (Extreme Left).
• In the 'Replace Format' dialogue box.
• Activate 'Font Tab', highlight 'Bold' under Font style.
• Again Activate 'Fill' tab.
• Click 'Fill Effects'.
• Under Shading Style highlight any Variant.
• Click Ok

• Click 'Replace All'. 

All the 'Binder' text will be replaced with a special format you chosen.Under preview button one see the format that will be incorporated.
One can clear the selected format by clicking 'Format' button and Click 'Clear Format' option.

Find & Replace | Find

Find

Find is an option in Excel with the help of which we are going to find anything in the data sheet/worksheet. 
Find is a search function inbuilt in Excel.

One simply needs to specify the search criterion. Excel will do the rest.

It is located in 'Find & Select' function under Home tab.
Short Cut 'Ctrl+F.'

Previous post's data sheet is used for demonstration.

Find & Replace

Find Window

How to find? 
Say we want to find 'Smith'. 
Type Smith in 'Find what' text box. 
If you press Find Next, you can view cells one at a time where content of the cell is Smith. In fact it is not the view, the cursor goes on making each Smith's cell active when you press Find Next.

If you press 'Find All' a separate window pops below where number of find can be seen with respective cell names.
And if you click each row in the window the cursor will move to the respective location in the sheet.

Results of Find

3 Cells found. 
Now where the results are located?
In the SalesOrders(2) sheet of Filter.xlsx file. 
And the respective cell numbers are C15, C21, C24.


Links has been created for each find. Cursor transforms into 'hand' when we hover the mouse cursor above each entry. If we click any entry excel makes the respective cell active in the corresponding data sheet. 

Here excel ignores upper case and lower case sensitivity.
Since we put 'smith' for search criterion but the contents are 'Smith'.


But interestingly excel accepts any blank space into consideration.
Say if we put smith with a blank space after h (by pressing space bar) excel will fail to find any. Since no cell contained with Smith has any blank space after h. Of course if any content of cell with smith is with a blank space after h, excel will find that cell.

Again excel can find any blank space 'only'. We put a blank space in the Find What text box (by pressing space bar once), and we know a cell with Smith has a blank space after h.
Excel can find that cell along with other cells where there are blank spaces.
Excel can find special characters. Say #, @,. etc

Even excel can find digits or alphabets in any content. Say we put 2 for search, and a date 10-12-2019 is there. Excel will take into consideration this date along with other content say 72,727 etc.

Again if we put '-2' for search, excel will find this date only with other dates where search criterion matches.

You can also use wild card characters like *,? for search criterion.
Type p*n to find string of characters say pen & pencil. 
Again type c*l to find central and pencil.

Use question mark ? to find a single character.
Put p?n to find pen. 
Put 4?9 to find 449.10.

Now if we want to find ? or * what will we put as a search criteria?
Put ~ (tilde) just above the tab key in keyboard.
~? or ~* to find ? and * respectively.

Advanced find options

Click 'Options>>' in the  Find & Replace window.

Advanced Option

In advanced option One can specify the sheet or workbook to search, by clicking at 'Within' combo box. 

One can specify the pattern of search say by row or column wise. If column wise is specified excel first reads the first column then the next and so on. In row wise search excel search rows one after another.

Lookin combo box specifies whether to search formulas also including text. And if one specifies text in the combo it ignores formulas. Say 2 is multiplied with the content of the cell G1 and the formula thereof would be G1*2. Now if we search G and specifies 'text' in the drop down combo it ignores the G in the formula cells. It may be handy. 

Now one can search contents of comments by specifying 'comments' in the look in combo box. Where in the search ignores all cell that are not commented.

Another important aspect of Find is, one can specify the area of the search in the worksheet. Say we need to search a telephone number within a column of telephone numbers. We can select the column only and search. Search results will be limited to the column only.

Match Case:

As it has been said that excel ignores uppercase and lowercase in case of ordinary search. But if we tick the check box associated with Match Case, excel will be very specific about its search. Excel will make the difference between SMITH & smith. If we search for 'smith' it won't find 'SMITH'.

Match Entire Cell contents:

If we tick the check box associated with 'Match Entire cell contents' excel will again be very specific about its search. In the ordinary scenario if we want to search for 'Binder' we can put the search criteria as 'bind'. We the box is ticked it won't find 'Binder' with search criteria 'bind'. It will consider those cells where entire search criteria matches.

Find Format:

We can search and find a particular format in the worksheet or workbook. Say we want to find all the 'bold' characters in the data sheet. 

Find Format

• Click 'Format' (as above).

• Under 'Find Format' dialogue box click Font Tab. 

• Under Font Style click or highlight Bold. 

• Click Ok.

• 'Find What' text box should remain empty because we are not searching any particular text or digit. We are simply searching the bold characters and digits in the sheet.
• Click 'Find All'. 
• Even you can search for colored cell, font style, italics, underlined etc. in this manner.

Sort and Filter | Chapter 2 | Filter

Filter

To access Filter function click 'Sort & Filter' option under Home tab.

  Filter function is also available under  'Data' tab.

How to incorporate Filter function?

• Click anywhere inside the source data where you want to incorporate Filter function.
• Click Filter (as marked with Red above).

Filter option enabled

We are going to use simple data sheet to understand Filter function.
Excel Table has default Filter option enabled.


Download the data sheets here.

We are going to understand filter function with the help of some examples. 

Instance 1

Filtering the data according to Central Region.

• Click drop down arrow of Region cell (B1).
• Uncheck '(Select All)'.
• Check 'Central'.
• Click Ok.

Explanations: 
Drop down arrow of Region (B1 Cell) has been changed to a 'Filter' sign. It symbolizes that the column has been filtered. Again in respect of rows it can be seen that some rows has been hidden only. Original data is intact.
If you want to view the entire sheet without filter. 

• Click the filter symbol on Region cell.
• Check (Select All).

Instance 2

Lets filter out representative Smith's performance in the Central region.

• Central Region to filter as above.
• Click drop down arrow of Rep cell.
• Uncheck 'Select All'.
• Check 'Smith'
• Click Ok

 

Explanations:
Region and Representative column has been filtered as drop down arrow has been changed to a filter symbol. 
Now if we want to revert to the original view, then we have to remove filter from Rep first and then from Region. It is better to remove filter from the last filtered column first and then proceed accordingly.

Instance 3

Excel automatically recognizes date format.

It changes it filter box accordingly.

Lets find out the performances in Jan & Feb for 2019 only.

• Click the drop down arrow Order Date cell.
• Again Uncheck '(Select All)'.
• Click the '+' button associated with 2019.
• Check Jan & Feb.
• Click Ok.

Alternatively

Lets find out a different fashion for achieving the same result as above.

• Click the drop down arrow in Order Date cell.
• Guide the mouse pointer to 'Data Filters'.
• Click 'Between'.
• Check the radio button 'And' if not checked.
• Put the start Date 1-01-2019.
• Put the end Date 28-02-2019.
• Click Ok.

Custom auto filter dialogue box

  Explanations: 

• Adjust dates by click the icon marked with Red circle.

 

Instance 4

Lets find out in respect of the item 'Binder' the representatives who failed to achieve triple digit sales figure.

• Click the drop down arrow of 'Item' cell. 
• Uncheck Select All.
• Check Binder.
• Click Ok.
• Again click the drop down arrow of 'Total' cell.
• Hover the mouse to option 'Number Filters'.
• Click 'Less Than.......'
• Put 100 (since any amount less than 100 is double digit figure ignoring decimal places of course).
• Click Ok.

  

Custom Auto Filter

Result as follows: 

Slicers & Timeline can also be used to filter data.