Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

What is MS Excel: -
Microsoft Excel is an electronic spreadsheet. You can use it to organize your datainto rows and columns.
You can also use it to perform mathematical calculations quickly It sheet are divided into two part that is
called row and column. Where we can record our data and perform arithmetical and logical calculation by
using some function or formula.

Accordingly, the course is divided into the following five (5) sections.

Section 1:- Entering Text and Numbers

Section 2:- Entering Excel Formulas and Formatting Data
Section 3:- Creating Excel Functions, Filling Cells, and Printing
Section 4:- Creating Charts
Section 5:- More on Entering Excel Formulas

Section 1: Entering Text and Numbers

This Section will introduce you to the Excel window. To begin this Section, start Microsoft Excel 2021 as
follows:-
 Click on start button.
 Search excel.
 Or press window key + R.
 Run box.
 Types excel.
 Click on ok button or press enter button.

The Microsoft Excel window appears and your screen looks similar to the one shown here.

The Microsoft Excel File Menu:-

In the upper-left corner of the Excel 2021 window is the Microsoft excel File menu. When you click the
option, a menu appears. You can use the menu to create a new file, open an existing file, save a file, print
and perform many other tasks.

The Quick Access Toolbar

pg. 1
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar gives you quick
access to commands you frequently use.

The Title Bar

Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the name of the
workbook you are currently using. At the top of the Excel window, you should see "Book 1 - Microsoft
Excel" or a similar name.

The Ribbon
In Microsoft Excel 2021, you use the Ribbon to issue commands. The Ribbon is located near the top of the
Excel window, below the Quick Access toolbar.

Worksheets
Microsoft Excel consists of worksheets. Each worksheet contains columns and rows. The columns are
lettered A to Z and then continuing with AA, AB, AC and so on; the rows are numbered 1 to 1,048,576.
The combination of a column coordinate and a row coordinate make up a cell address. For example, the cell
located in the upper-left corner of the worksheet is cell A1, meaning column A, row 1. Cell E10 is located
under column E on row 10. You enter your data into the cells on the worksheet.

The Formula Bar

The formula bar is a section in Microsoft Excel and other spreadsheet applications. It shows the contents of
the current cell and lets you create and view formulas.

The Status Bar

The Status bar appears at the very bottom of the Excel window and provides such information as the sum,
average, minimum, and maximum value of selected numbers.

Move around a Worksheet

By using the arrow keys, you can move around your worksheet. You can use the down arrow key to move
downward one cell at a time. You can use the up arrow key to move upward one cell at a time. You can use
the Tab key to move across the page to the right, one cell at a time.

The Name Box

It is use to display the address of the selected cell. You can also use the Name box to go to a specific cell. Just
type the cell you want to go to in the Name box and then press Enter.
 Type B10 in the Name box.
 Press Enter. Excel moves to cell B10.

Enter Data
In this section, you will learn how to enter data into your worksheet. First, place the cursor in the cell in
which you want to start entering data. Type some data, and then press Enter. If you need to delete, press
the Backspace key to delete one character at a time.
 Place the cursor in cell A1.
 Type Piyush Mani. Do not press Enter at this time.

Edit a Cell – (F2)

After you enter data into a cell, you can edit the data by pressing F2 while you are in the cell you wish to edit.

pg. 2
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Change "Suleman" to "Salman."

 Move to cell A1.
 Press F2.
 Use the Arrow and Backspace keys to change Suleman to Salman
 Press Enter.
Alternate Method: Editing a Cell by Using the Formula Bar
You can also edit the cell by using the Formula bar. You change "Jones" to "Joker" in the following exercise.
 Move the cursor to cell A1.
 Click in the formula or entries area of the Formula bar, and change Jones toJoker.
 Press Enter.

Alternate Method: Edit a Cell by Double-Clicking in the Cell

You can change "Joker" to "Johnson" as follows
 Move to cell A1.
 Double-click in cell A1.
 Use the Arrow and Backspace keys to change Joker to Johnson.
 Press Enter.

Wrap Text (Alt+Enter)

When you type text that is too long to fit in the cell, the text overlaps the next cell. If you do not want it to
overlap the next cell, you can wrap the text.
 Move to cell A2.
 Type Hi This is Piyush Mani Tripathi .
 Press Enter.
 Return to cell A2.
 Choose the Home tab.
 Click the Wrap Text button.
 Excel wraps the text in the cell.
Delete a Cell Entry
To delete an entry in a cell or a group of cells, you place the cursor in the cell or select the group of cells and
press Delete.
Delete a Cell Entry
 Select cells A1 to A2.
 Press the Delete key.
Save a File
This is the end of Section 1. To save your file:

 Click the File Menu. A menu appears.

 Click Save. The Save As dialog box appears.
 Click on brows
 Select your location
 Type your file name
 Click on save option.

Save A file in PDF Format

 Click the File Menu. A menu appears.
 Click Save. The Save As dialog box appears.

pg. 3
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on brows
 Select your location
 Type your file name
 Click on save as type.
 Select PDF Option
 Click on save

Save file With Password

 Click the File Menu. A menu appears.
 Click Save. The Save As dialog box appears.
 Click on browse
 Select your location
 Type your file name
 Click on Tools Option.
 Click on General Option
 Type your password (Optional)
 Click on ok
 Reenter Password To Confirm
 Click on Ok
 Click On Save

Section 2: Entering Excel Formulas and Formatting Data

A major strength of Excel is that you can perform mathematical calculations and format your data. In this
Section, you will learn how to perform basic mathematical calculations and how to format textand
numerical data. To start this Section, open Excel.

Perform Mathematical Calculations

In Microsoft Excel, you can enter numbers and mathematical formulas into cells. You enter a number or a
formula, you can reference the cell when you perform mathematical calculations such as addition,
subtraction, multiplication, or division. When entering a mathematical formula, precede the formula with an
equal (=) sign. Use the following to indicate the type of calculation you wish to perform:-
+ Addition
– Subtraction
* Multiplication
/ Division
In the following exercises, you practice some of the methods you can use toperform mathematical
calculations.

Addition, Subtraction, Multiplication and Division of Numbers

 =25+96-58
 =48+66*52
 =45*36/52
 =78-25+69

Perform Advanced Mathematical Calculations

Advanced Calculations
 Move to cell A7.
 Type =3+3+12/2*4.

pg. 4
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Press Enter.
Note: Microsoft Excel divides 12 by 2, multiplies the answer by 4, adds 3, and then adds another 3. The
answer, 30, displays in cell A7

To change the order of calculation, use parentheses (कककककक). Microsoft Excel calculates the
information in parentheses first.
 Double-click in cell A7.
 Edit the cell to read =(3+3+12)/2*4.
 Press Enter.
Note: Microsoft Excel adds 3 plus 3 plus 12, divides the answer by 2, and thenmultiplies the result by
4. The answer, 36, displays in cell A7.

AutoSum (Alt+=)
You can use the AutoSum button on the home tab to automatically add a column or row of numbers

How to use Auto Sum:-

A
1 2565
2 6586
3 2565
4 2656
5

 Select the cell A1:A5

 Click on home tab
 Click on auto Sum option in home tab (auto Sum Symbol ∑)

Align Cell Entries

When you type text into a cell, by default your entry aligns with the left side of the cell. When you type
numbers into a cell, by default your entry aligns with the right side of the cell. You can change the cell
alignment. You can center, left-align, or right-align any cell entry

Insert and Delete Columns and Rows

You can insert and delete columns and rows. When you delete a column, you delete everything in the column
from the top of the worksheet to the bottom of the worksheet.

How to delete any row and columns:-

If you want to delete column B
 Right click on column B
 Click on Delete
If you want to delete ROW 3
 Right click on Row 3
 Click on Delete

How To Insert and row and Column:-

If you insert extra column before column C
 Right Click on Column C
pg. 5
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on Insert

If you insert extra Row before Row 4

 Right Click on Row 4
 Click on Insert

What is merge and center:-

Excel has a unique button called “Merge & Center, which is used to merge two or more different cells.
How to use:-
 Select the cell A1:A5
 Click on Home Tab
 Click On Merge And Center

Sheet tabs
In excel you can have more than one sheet in a spreadsheet. At the bottom of the grid of cells in a
spreadsheet are many sheets there are in your spreadsheet.

How to insert New Sheet:-

 Right click on any one Sheet Tab
 Click on Insert

How to Delete Any Sheet Tab:-

 Right Click on any one sheet tab
 Click on delete

How to Rename Any one Sheet Tab:-

 Right click one any one sheet tab
 Click on Rename
 Type Another name
 Press Enter Button
 Or Double Click on Sheet Tab

How To Move Or Copy any Sheet on another Workbook:-

 Right click on any one sheet tab
 Click on Move or Copy
 Select your Sheet
 Click on To Book
 Click on New Book
 Click on Create a copy
 Click On Ok

How to Protect any Sheet (Anyone Only Read Nothing can be Write)
 Right click on any one sheet tab
 Click on protect sheet
 Type your Password (Optional)
 Click on ok

pg. 6
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Reenter Password To confirm

 Click on ok

How to unprotect any protect Sheet:-

 Right click on Protect sheet
 Click on Unprotect Sheet
 Type your password
 Click on ok

How to Change Sheet Tab colour

 Right click on any one sheet tab
 Click on tab color
 Select any one Color

How to Hide any one Sheet:-

 Right click on any one sheet tab
 Click on Hide

How to unhide any hide sheet:-

 Right click on any one sheet tab
 Click on unhide sheet
 Select your sheet
 Click on ok

How to Protect our Workbook: -

 Click on review tab
 Click on protect workbook
 Type your password (Optional)
 Click on ok
 Reenter password to confirm
 Click on ok

How to Unprotect our protect Workbook: -

 Click on review tab
 Click on unprotect workbook
 Type your password
 Click on ok

Section 3: Creating Excel Functions, Printing, Page Setup

Page Setup:-
Page setup is a set of specific parameters involving the display and layout of a printed page. This type of
resource is part of many modern word processing applications and other document processing software, for
instance, in Microsoft Office applications. Page Setup option is used to set the page size, page margin and
orientation of the page.

pg. 7
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

How to set excel page:-

 Click on layout tab
 Click on margin
 Click on custom margin
 Set your margin
 Set your page
 Set your orientation
 Click on ok

Print Option in Excel (Ctrl+P):-

This option is used to print any sheet We can print the current sheet, active sheet, entire workbook, selected
table, or customized worksheets.

How to print:-
 Click on file menu
 Click on print Option
 Select your printer name and model no
 Select your page range
 Click on print

Print Preview (Ctrl+F2)

Print preview in Excel is a tool used to represent the print output of the current page in Excel. This tool is
used to see if any adjustments need to be made to print the final result. The print preview only displays the
document on the screen.

What is Function
A function is a predefined formula that performs calculations using specific values in a particular order. All
spreadsheet programs include common functions that can be used for quickly finding the sum, average,
count, maximum value, and minimum value for a range of cells.

Category of function:-In Ms excel there are 11 category of function.

1. Math and trig function:-

1. ABS function:-It is use to return the absolute value a value without any sign.

A B
1 -60 =ABS(A1)
2 -55
3 44
4 63
5 -85

2. Ceiling Function:- The CEILING function in Excel rounds a given number up to the nearest multiple of
significance.

A
1 =Ceiling(32,7)
2 =Ceiling(45,6)

pg. 8
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

3 =Ceiling(25,5)
3. Floor Function:- The Excel FLOOR function is a math formula that rounds a specific number down to
the nearest specified multiple.

A
1 =Floor(32,7)
2 =Floor(45,6)
3 =Floor(25,5)

4. Even function:-It returns the even value.

A B
1 60 =Even(A1)
2 55
3 44
4 63
5 85

5. Odd function:-It returns the Odd value.

A B
1 60 =ODD(A1)
2 55
3 44
4 63
5 85

6. Fact function:-It returns the factorial of the number.

A B
1 4 =Fact(A1)
2 5
3 6
4 7
5 8

7. Fact double function:-It returns the double factorial of the number.

A B
1 4 =Factbouble(A1)
2 5
3 6
4 7
5 8

8. INT (Integer) Function:-Return the integers value, a value without decimal place.

pg. 9
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B
1 42.3656 =INT(A1)
2 51.6565
3 61.3256
4 74.2545
5 84.3256

9. LCM (lowest common multiple) Function:-It returns the lowest common multiple value.

A
1 =LCM(8,10,25)
2 =LCM(12,36,41)
3 =LCM(41,32,21)
4 =LCM(47,25,41)
5 =LCM(41,22,4)

10. Mod Function:- Return the remainder when one integer is a divided by another.

A
1 =MOD(10,5)
2 =MOD(12,36)
3 =MOD(4,45)
4 =MOD(6,78)
5 =MOD(9,48)

11. PI Function:-It is use to return the value of PI.

A
1 =PI()

12. Quotient Function:-It is use to return the Quotient value.

A
1 =QUOTIENT(8,3)
2 =QUOTIENT(12,3)
3 =QUOTIENT(4,4)
4 =QUOTIENT(5,6)
5 =QUOTIENT(6,3)

13. Product Function:-It is use to return the multiply of the value.

A
1 =PRODUCT(8,3)
2 =PRODUCT(12,3)
3 =PRODUCT(4,4)
4 =PRODUCT(5,6)
pg. 10
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

14. Quotient Function:-It is use to return the division value.

A
1 =QUOTIENT(8,3)
2 =QUOTIENT(12,3)
3 =QUOTIENT(4,4)
4 =QUOTIENT(5,6)
5 =QUOTIENT(6,3)

15. Roman Function:-It is use to convert the any number into roman number.

A B
1 4 =ROMAN(A1)
2 5
3 6
4 7
5 8

16. SQRT Function:-It is use to return the return the square root of any given number.

A B
1 4 =SQRT(A1)
2 5
3 6
4 7
5 8

Difference between formula and Function:-

Formulas are defined as a statement written by any user, either simple or complex whereas a function is a
type of formula which is pre-defined. On the other hand, functions are predefined formulas that are already
there in the sheet.

A B C D
1 Formula Function Formula Function
2 50 50 5 5
3 50 50 5 5
4 50 50 5 5
5 =A2+A3+A4 =SUM(B2:B4) =C2*C3*C4 =PRODUCT(D2:D4)

A B C D
1 Principle Amount Rate Time Simple Interest
2 1200 5 3 =A2*B2*C2/100
3 Distance Time Speed
4 142 5 =A4/B4

pg. 11
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C D E
1 Name Test-1 Test-2 Total With Formula Total With Function
2 Piyush 98 78 =B2+C2 =SUM(B2:C2)
3 Shubham 85 48
4 Saroj 89 98
5 Saloni 78 58
6 Suman 85 78
7 Shweta 65 55

 Make a cash memo (Bill) Program In excel worksheet where we have to calculate 10% Discount on
each item and Also calculate GST on Total Bill.

A B C D E F
1 Florina Compute's Shahi Market
2 Item Name Quantity Rate Amount 10% Discount After Discount our Amount
3 Mouse 12 320 =B3*C3 =D3*10% =D3-E3
4 Keyboard 23 400
5 SMPS 25 620
6 RAM 14 520
7 ROM 36 500
8 Printer 11 1200
9
10 Total Amount =SUM(D3:D8)
11 GST 18% =D10*18%
12 CGST 9% =D10*9%
13 SGST 9% =D10*9%

Cell reference:-
A cell reference or cell address is a combination of a column letter and a row number that identifies a
cell on a worksheet. For example, A1 refers to the cell at the intersection of column A and row 1.

Now there are three kinds of cell references that you can use in Excel:
1. Relative Cell References
2. Absolute Cell References
3. Mixed Cell References

1. Relative Cell References

This is the most widely used type of cell reference in formulas. Relative cell references are basic cell
references that adjust and change when copied or when using AutoFill.

A B C D
1 Item Name Quantity Rate Amount
2 Hard Disk 10 3200 =B2*C2
3 Mouse 6 210 =B3*C3
4 SMPS 8 650 =B4*C4

pg. 12
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

5 Key Board 5 350 =B5*C5

6 Mouse 6 210 =B6*C6
7 SMPS 8 650 =B7*C7

1. Absolute Cell References

An absolute cell reference is a cell reference in a spreadsheet that remains constant even if the shape or
size of the spreadsheet is changed, or the reference is copied or moved to another cell or sheet.

When you are typing your formula, after you type a cell reference - press the F4 key.

A B C D E
1 Discount Percentage 5%
2 Item Quantity Rate Discount Total Amount
3 Mouse 12 255.00 =B3*C3*$E$1 =(B2*C3)-D3
4 Key Board 15 340.00
5 LED Monitor 9 6200.00
6 RAM 3 4000.00
7 Printer 7 2500.00
8 Scanner 9 2600.00

Section 4: Creating Charts

What is chart:-
in Microsoft Excel, charts are used to make a graphical representation of any set of data. A chart is a visual
representation of data, in which the data is represented by symbols such as bars in a bar chart or lines in a
line chart.
Chart is the graphical presentation of any numerical data if we want to represent our numerical data in the
form of graphic then we can use chart.
Example-1

A B C
1 Salesman Location Commission
2 Piyush Kolkata 90300
3 Shubham Asansol 88377
4 Rajan Kolkata 79026
5 Aman Durgapur 109585
6 Suraj Durgapur 114270
7 Kamal Kolkata 84027
Ques- Create A Bar Chart Based On Salesman Name & Commission Field.
How to use:-
 select the cell A1:A7
 Press Ctrl Button
 Select the cell C1:C7
 Click on insert tab
 Select bar chart
Example-2
pg. 13
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B D E
1 Customer Code Customer Name Car No. Down Payment
2 BRP-132 Mr. Suvankar Kar WB-1236 8000
3 BRP-125 Mrs. Nivedita Nath WB-3256 4000
4 BRP-126 Md. Saharior Shaikh WB-1265 3700
5 BRP-138 Miss. Moumita Das WB-3278 12000
6 BRP-180 Mr. Tapas Hudait WB-2001 9000
Ques- Create a pie Chart of Customer name on the basis of Down Payment field only as shown below

How to use:-
 Select the cell B1:B6
 Press Ctrl Button
 Select the cell D1:D6
 Click on insert button
 Click on Pie Chart

Example-3
A B C D E
1 Folio Number Country Investment Type Due (Rs.) Total
2 780 INDIA Debentures Interest 369 15000
3 745 INDIA Deposits Interest 969 25000
4 715 INDIA Debentures Interest 99 9000
5 550 INDIA Debentures Interest 105 8500
6 700 GERMANY Deposits Interest 57 34000
7 632 USA Debentures Interest 121 18000
Ques create a Column chart like below based on county, due and total amount
How to use:-
 Select the cell B1:B7
 Press Ctrl Button
 Select the cell D1:D7
 Select the Cell E1:E7
 Click on Insert Tab
 Click on column chart
 Click on 3D column Chart

Section 5:- More on Entering Excel Formulas:-

1. Sum Function:-It is use to find out the total of the given range.
2. SUMIF Function:-This function is use to add a value on given criteria.
3. Average Function:-It is use to find out the average value of the given number or given range.
4. Average if Function:-It is use to find out the average value on a given criteria.
5. Count Function:-It is use to count the only numeric cell.

pg. 14
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

6. Counta Function:-It is use to count the non empty cell.

7. Countblank Function:-It is use to count the only blank cell.
8. Countif Function:-It counts the cells that match the given criteria.
9. Min Function:-It is use to count the minimum value on the given range.
10. Max Function:-It is use to count the maximum value on the given range.
11. Large Function:-It returns the largest value of any position.
12. Small Function:-It returns the smallest value of any position.

pg. 15
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C D E
1 Product Agent Amount Sum =SUM(C2:C9)
2 Mouse Piyush 3250 Sumif =SUMIF(A2:A9,"mouse",C2:C9)
3 Keyboard Kundan 2400 Average =AVERAGE(C2:C9)
4 SMPS Nitesh 3600 Averageif =AVERAGEIF(B2:B9,"kundan",C2:C9)
5 RAM Piyush 4500 Count =COUNT(A1:C9)
6 Mouse Kundan 2500 Counta =COUNTA(A1:C9)
7 Keyboard Nitesh 3200 Countblank =COUNTBLANK(A1:C9)
8 SMPS Piyush 1500 Countif =COUNTIF(A2:A9,"ram")
9 RAM Kundan 6000 Min =MIN(C2:C9)
10 Max =MAX(C2:C9)
11 Large =LARGE(C2:C9,3)
12 Small =SMALL(C2:C9,3)

Some More Example of Sumif Function:-

Example-1

A B C D
1 Emp. Code Name Department Gross Salary
2 E001 Amit MKT 25000
3 E002 Ravi Acct 12500
4 E003 Sanjay MKT 13600
5 E004 Rajesh MKT 45000
6 E005 Vinod Acct 25400
7 E006 Ramesh MKT 15300
1. Calculate the total gross salary of MKT department
=Sumif(C2:C7,"mkt",D2:D7) Press Enter Button.

2. Calculate the total gross salary of department.

=Sumif(C2:C7,"acct",D2:D7) Press Enter Button.
Example-2

A B C
1 salesman location Commission
2 Piyush Mani Kolkata 90300
3 Mohd. Salim Asansol 88377
4 Aman Sharma Kolkata 79026
5 Karan Ranjan Durgapur 109585
6 Raju Kumar Durgapur 114270
7 Suraj Paswan Kolkata 84027
9 Aman kumar Asansol 20000
10 Rajesh Sonkar Durgapur 12500
11 Puli Sharma Kolkata 14500

pg. 16
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

12 city commission
13 Kolkata
14 Asansol
15 Durgapur

1. =Sumif(B2:B11,A13,C2:C11) Press the enter button.

2. =Sumif(B2:B11,A14,C2:C11) Press the enter button.
3. =Sumif(B2:B11,A15,C2:C11) Press the enter button.

Example-3

A B C D E
1 Bill No. Bill Date Party Name Due Days Due Amount
2 PMT01 11-01-14 Vishal enterprise 181 30000
3 PMT02 22-08-14 Roushan Telecom 99 125000
4 PMT03 16-03-14 Andrew & Sons 117 20000
5 PMT04 01-03-14 Vishal enterprise 147 32000
6 PMT05 18-04-14 Rose Enterprise 84 46000
7 PMT06 05-03-14 Andrew & Sons 6 54000

1. Find out the total of due amount before April 2014 by suitable function.
=Sumif(B2:B7,"<=01-04-2014",E2:E7) Press the enter button

2. Find out the total of due amount After April 2014 by suitable function.
=Sumif(B2:B7,">=01-04-2014",E2:E7) Press the enter button

3. Find out the total of due amount which due days is less than 90.
=Sumif(D2:D7,"<=90",E2:E7) Press the enter button

4. Find out the total of due amount which due days is greater than 100.
=Sumif(D2:D7,">=100",E2:E7) Press the enter button

Example-4

A B C
1 ITEM DATE COST
2 Brakes 1-Jan-07 80
3 tyres 10-May-07 25
4 Brakes 1-Feb-07 80
5 service 1-Mar-07 150
6 service 5-Jan-07 300
7 window 1-Jun-07 50

1. Total cost of all brakes bought.

=Sumif(A2:A7,"brakes",C2:C7) Press the enter button

pg. 17
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

2. Total cost of all tyres bought.

=Sumif(A2:A7,"Tyres",C2:C7) Press the enter button
3. Total cost of all service bought.
=Sumif(A2:A7,"service",C2:C7) Press the enter button

Some More Example of Countif Function:-

Examle-1
A B C D E
1 Courses PIYUSH YASH SONI ANISH
2 BCA A A Fail C
3 DFA Fail A C B
4 ADV ACCT A A C B
5 DTP B B A C
6 TYPING C Fail B B
7 ADV EXCEL A C Fail A
8 INCOME TAX B B Fail Fail
9 PDP B A B C
10 OBTD GRADE A
11 OBTD GRADE B
11 FAIL AMONG
1. =Countif(B2:B9,"A")
2. =Countif(B2:B9,"B")
3. =Countif(B2:B9,"Fail")

Example-2
A B C D E
1 Bill No. Bill Date Party Name Due Days Due Amount
2 PMT01 11-01-14 Vishal enterprise 181 30000
3 PMT02 22-08-14 Roushan Telecom 99 125000
4 PMT03 16-03-14 Andrew & Sons 117 20000
5 PMT04 01-03-14 Vishal enterprise 147 32000
6 PMT05 18-04-14 Rose Enterprise 84 46000
7 PMT06 05-03-14 Andrew & Sons 6 54000

1. Count the Due days which are greater than 80.

=Countif(D2:D7,">80")
2. Count the Due Amount Which Are Greater Than 35000.
=Countif(E2:E7,">35000")
3. Count the bill date which are greater than 31-5-2014.
=Countif(B2:B7,">31-05-2014")

Sumproduct Function:-
The Sumproduct Function multiple range together and return the sum of product. Its first range to multiply
and then add.
pg. 18
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C
1 Item Name Quantity Rate
2 Mouse 12 210
3 Keyboard 23 200
4 SMPS 14 320
5 RAM 15 450
6 ROM 32 320
7
8 Total Amount =Sumproduct(B2:B6,C2:C6)

SumIFS Function:-
The SUMIFS function is a premade function in Excel, which calculates the sum of a range based on one or
more condition. (This function is used to add the value single or multiple Criteria).

AverageIFS:-
The AVERAGEIFS function is a premade function in Excel, which calculates the average of a range based on
one or more condition. (This function is used to find out the average value single or multiple Criteria).

CountIFS Functions:-
The COUNTIFS function is a premade function in Excel, which calculates the Count the value of a range based
on one or more condition. (This function is used to Count the value single or multiple Criteria).

A B C D
1 Employee Location Department Basic
2 Moumita Kolkata Acct 4500
3 Pauli Asansol Sales 7800
4 Kangna Asansol Sales 6600
5 Tuhin Kolkata Sales 5600
6 Debabrata Kolkata Acct 8900
7 Sunil Asansol Acct 8000
8
9 Location Department Total Basic
10 Kolkata Acct
11
12 Location Department Total Basic Average
13 Kolkata Acct
14
15 Location Department Count Location
16 Kolkata Acct
1. =SUMIFS(D2:D7,B2:B7,A10,C2:C7,B10)
2. =AVERAGEIFS(D2:D7,B2:B7,A13,C2:C7,B13)
3. =COUNTIFS(B2:B7,A16,C2:C7,B16)

What is fill series:-

pg. 19
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Fill series is used to fills the selected range of cells in the spreadsheet with a series of numbers, characters, or
dates. The content of the first cell in the selected range is used as the starting value for the series.

Fill series is 4 types

1. Linear series
2. Growth series
3. Date series
4. Auto fill

Linear series:-

A B
1 Series 1 Series 1
2 5 5
3 16
4 27
5 38
6 49
7 60
8 71
How to use:-
 Select the cell B2:B8
 Click on home tab
 Click on fill
 Click on series
 Click on column and linear
 click on step value box type 11
 click on ok

Growth Series:-

A B
1 series 2 series 2
2 8 8
3 56
4 392
5 2744
6 19208
7 134456
8 941192
How to use:-
 Select the cell B2:B8
 Click on home tab
 Click on fill
 Click on series
 Click on column and Growth
 click on step value box type 7
 click on ok
pg. 20
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Date Series:-

A B
1 series 3 series 3
2 1-Apr-09 1-Apr-09
3 7-Apr-09
4 13-Apr-09
5 19-Apr-09
6 25-Apr-09
7 1-May-09
8 7-May-09
How to use:-
 Select the cell B2:B8
 Click on home tab
 Click on fill
 Click on series
 Click on column, Date and day
 click on step value box type 6
 click on ok

Custom list:-
Custom lists in excel is used to sort data based on the user's choice and is especially useful when you need to
perform multiple tasks on the same data on a repetitive basis.
एएएएएए एएए एएएएए एएएएएएए एए एएएएए एएएएएएएएएए एए एएएए एए एएएए एए एएएए एए एएएएए एएएए एए एएए
एएएए एएएए एए एए एएएएए एएए एए एए एएएएएए एएएए एए एए एएएए एए एए एएएए एए एएएएएए एएएए एएएए एएएए एए
एए एएएएए एएएए एए एएएएएएएए एएएए एएए

A B
1 Mouse
2 Keyboard
3 Monitor
4 Scanner
5 Motherboard
6 Printer
How to use custom list in MS Office 2007:-
 click on office button
 click on excel option
 click on popular
 click on edit custom list
 click on list entries
 now type your list
 keyboard, mouse, monitor scanner, motherboard, printer
 click on add button
 click on ok

pg. 21
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

How to use custom list for MS Office 2013, 2016, 2019 and 2021:-
 click on file menu
 click on more
 click on options
 click on advanced
 click on edit custom list
 click on list entries
 now type your list
 keyboard, mouse, monitor scanner, motherboard, printer
 click on add button
 click on ok

Custom number format:

Custom number formats can control the display of numbers, dates, times, percentages, and other numeric
values. Using custom formats, you can do things like format dates to show month names only, format large
numbers in millions or thousands, and display negative numbers in red.
एएएएए एएएएएए एएएएएएए एएएएएएएए, एएएएएए, एएए, एएएएएएए एए एएएए एएएएएएएएएए एएएएए एए एएएएएएएए एए

एएएएएएएएए एए एएएए एएएए एएएएए एएएएएएएएए एए एएएएए एएएए, एए एएएए एएएएए एए एएए एएएएएए एए एएए एएएएएएए

एएएएएएए, एएएएए एए एएएएएए एएए एएएए एएएएएए एएए एएएएएएएएए एएएए एए एएए एएए एएए एएएएएएएएए एएएएएए

एएएएएएएएए एएएए एएएए एएए एए एएएए एएएए

A B
1 Bill No Bill No
2 JKP/INV/10-11/128
3 JKP/INV/10-11/056
4 JKP/INV/10-11/088
5 JKP/INV/10-11/142
6 JKP/INV/10-11/036
7 JKP/INV/10-11/099
How to use:-
 select the cell B1:B7
 press Ctrl+1 for open Format cell box
 click on custom
 Click on type box
 Type "JKP/INV/10-11/"000
 Click on ok

pg. 22
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B
1 Emp_ID Emp_ID
2 EM-1132 -15
3 EM-1077 -15
4 EM-1088 -15
5 EM-1070 -15
6 EM-1095 -15
7 EM-1100 -15
How to use:-
 Select the cell B1:B7
 press Ctrl+1 for open Format cell box
 click on custom
 Click on type box
 Type "EM-"0000"-15"
 Click on ok

A B
1 Name Name
2 Amit
3 Rahul
4 Pankaj
5 Vinay
6 Shailesh
7 Rohit

Ques. if anyone enter any name it will automatically add a suffix "MR."
How to use:-
 Select the cell B2:B9
 press Ctrl+1 for open Format cell box
 click on custom
 Click on type box
 Type "MR."@
 Click on ok

IF Function: -
The IF function is a premade function in Excel, which returns values based on a true or false condition. It is
type =IF(logical_test, [value_if_true], [value_if_false]).
pg. 23
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

(it have one condition and two statement if the condition is true it will execute first statement otherwise it
will execute second statement)

Basic use of IF Function:-

Example-1

A B C D E
1 Name Marks Result 50 Result 30 Result 45
2 Punit 37
3 Rahul 59
4 Pankaj 83
5 Vinay 72
6 Sumit 28
7 Vivek 42

1. Calculate result if passing marks is 50

=IF(B2>=50,"pass","fail")
2. Calculate result if passing marks is 30
=IF(B2>=30,"pass","fail")
3. Calculate result if passing marks is 45
=IF(B2>=45,"pass","fail")
Example-2

A B C D
1 Name Dept Salary Salary
2 Ajit Pur
3 Vinay Sale
4 Rahul Pur
5 Vineet Sale
6 Kabir Pur
7 Rajendra Pur

Q 1. Define Salary For The Following Dept

Pur 6000 Pur 8000
Sale 4500 Sale 12000
1. =IF(B2="pur",6000,4500)
2. =IF(B2="pur",8000,12000)

A B C
1 Name Marks Status
2 Piyush 78 =IF(B2>=50,"","Bad")
3 Shubham 29
4 Saroj 87
5 Saloni 66
6 Suman 55
7 Shweta 23
pg. 24
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

8 Shreya 78

Use Smiley in IF Formula

A B C
1 Name Marks Status
2 Piyush 78 =IF(B2>=50,"✅","❎")
3 Shubham 29
4 Saroj 87
5 Saloni 66
6 Suman 55
7 Shweta 23
8 Shreya 78
Note:- For Insert Smiley Press Window Key + Dot (.)

Advanced use of IF Formula:-

A B C D E F G H
1 Employee Location Department Basic HRA HRA HRA HRA
2 Moumita Kolkata Acct 4500
3 Pauli Asansol Sales 7800
4 Kangna Asansol Sales 6600
5 Tuhin Kolkata Sales 5600
6 Debabrata Kolkata Acct 8900
7 Sunil Asansol Acct 8000

1. HRA will be @30% of Basic for Kolkata and @20% of Basic for other location.
=IF(B2="kolkata",D2*30%,D2*20%)
2. HRA will be @40% of Basic for Acct and @25% of Basic for other Department.
=IF(C2="acct",D2*40%,D2*25%)
3. HRA will be @35% of Basic for Asansol and @15% of Basic for other location.
=IF(B2="asansol",D2*35%,D2*15%)
4. HRA will be Rs.500 for those who are not working in Sales Department.
=IF(C2="acct",500,0)

Extract Name of Fail Students With Marks

A B C
1 Name Marks Name of Fail Students
2 Anil Kumar 78 =IF(B2<40,A2&"("&B2&")","")
3 Reetu 29
4 Rohit 87
5 Teena 66
pg. 25
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

6 Sonu 55
7 Raj 23
8 Neha 78

Add Mr. Miss in Names

A B C
1 Name Gender Helper
2 Piyush Male =IF(B2="male","Mr "&A2,"Miss "&A2)
3 Saloni Female
4 Aman Male
5 Komal Female
6 Suraj Male
7 Sachin Male
8 Shreya Female

Compare Two Values Using IF Formula

A B C
1 Password Confirm Password Is Match ?
2 123123 123123 =IF(A2=B2,"yes","no")
3 23erdwed33 23erdwed33
4 asd12!!!2d asd12!!!2d
5 34fd3437 34fd3435

Increase Computer Price By 5%

A B C D
1 HSN Code Product Price New Price
2 HPP001 Computer 19000 =IF(B2="Computer",C2+C2*5%,C2)
3 HPP002 Laptop HP i5 40000
4 HPP003 Cpu 7000
5 HPP004 Computer 21000
6 HPP005 Laptop HP i7 65000
7 HPP006 Cpu 11000

Nested IF Function:-
Nested IF functions, meaning one IF function inside of another, allows you to test multiple criteria and
increases the number of possible outcomes.
(When one if function is placed inside another known As nested IF)
Example-1

A B C D
1 Name Marks Result Result Result

pg. 26
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

2 Piyush 37
3 Parul 59
4 Salim 83
5 Pooja 72
6 Aman 28
7 Ainee 42

Q 1. Calculate result on the following criteria

>=80 First >=90 First >=75 First

>=50 Second >=60 Second >=65 Second
>=30 Third >=35 Third >=50 Third
<30 Fail <35 Fail <=50 Fail

1. =IF(B2>=80,"First",IF(B2>=50,"second",IF(B2>=30,"Third","Fail")))
2. =IF(B2>=90,"First",IF(B2>=60,"second",IF(B2>=35,"Third","Fail")))
3. =IF(B2>=75,"First",IF(B2>=65,"second",IF(B2>=50,"Third","Fail")))

Example-2

A B C D
1 Name Dept Salary Salary
2 Ajit Pur
3 Vinay Sale
4 Rahul Mkt
5 Vineet Sale
6 Kabir Pur
7 Rajendra Mkt

Q 1. Define salary for the following dept

Pur 8000 Pur 20000
Sale 3500 Sale 35000
Mkt 4600 Mkt 48000

1. =IF(B2="pur",8000,IF(B2="sale",3500,4600))
2. =IF(B2="pur",20000,IF(B2="sale",35000,48000))

Example-3

A B C D E
1 Name Dept Basic DA DA
2 Piyush Sale 4500
3 Salim Pur 6500
4 Aman Pur 1600
5 Parul Sale 7300
6 Pooja Mkt 2400

pg. 27
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

7 Aniee Pur 4300

Q 1. Calculate da for the following employee

Dept DA Dept DA
Sale 30% of basic Sale 60% of basic
Pur 35% of basic Pur 45% of basic
Mkt 40% of basic Mkt 20% of basic

1. =IF(B2="sale",C2*30%,IF(B2="pur",C2*35%,C2*40%))
2. =IF(B2="sale",C2*60%,IF(B2="pur",C2*45%,C2*20%))

Example-4
A B C
1 Name Post Basic
2 Raunak 8000
3 Anshul 4000
4 Amit 12000
5 Rahul 3500
6 Anjani 6500
7 Suraj 16000

Ques 1) Post Slab Are Given Below

Basic Post
<=4000 Supervisor
>4000, <=8000 Accountant
>8000, <=12000 Executive
And > 12000 Manager
=IF(C2<=4000,"Supervisor",IF(C2<=8000,"Accountant",IF(C2<=12000,"Executive","Manager")))

Example-5

A B C D E F G
1 St Code BCA DFA TALLY ACCT TOTAL Grade
2 A1001231 65 68 75 42 =Sum(B2:E2)
3 A1001232 80 75 95 85
4 A1001233 68 63 32 75
5 A1001234 55 75 38 52
6 A1001235 33 23 43 54

Define grade for the following Criteria:-

Grade A Above 320

Grade B 240 to 320

pg. 28
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Grade C 200 to 240

Grade D Less than 200
=IF(F2>=320,"A Grade",IF(F2>=240,"B Grade",IF(F2>=200,"C Grade","D Grade")))

Example-6
A B C D
1 Student Name Course Fees No Of Instalments
2 Dipankar TALLY 24000
3 Vinod BBA 85000
4 Soumya CIA 49000
5 Salim DCA 23000
6 Deepak DFA 12000
7 Ranjan ADCA 45000

Ques 1. No Of Instalment Will Be As Follows

Fees Below 30000 4 Installment
Fees 30001 To 50000 6 Installment
Fees Above 50000 8 Installment
=IF(C2<=30000,"4 Installment",IF(C2<=50000,"6 Installment","8 Installment"))

Example-7

A B C
1 Emp Name Basic Tax
2 Salman 345000
3 Ranbir 652000
4 Ritesh 3620000
5 Karina 425000
6 Raman 230000
7 Kajal 150000

Ques 5. Calculate Tax As Per Slab Rate As Follows

Upto 200000 0% Of Basic
200001 To 500000 10% Of Basic
500001 To 1000000 20% Of Basic
Above 1000000 30% Of Basic
=IF(B2<=200000,B2*0%,IF(B2<=500000,B2*10%,IF(B2<=1000000,B2*20%,B2*30%)))

Example-8

A B C D E F
1 Emp Code Name Designation Basic Others Allowance Net Salary
2 AS001 Bikash 26500
3 AS002 Gopal 16000
4 AS003 Kunal 34200
pg. 29
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

5 AS004 Sachin 18600

6 AS005 Salim 15000
7 AS006 Piyush 25000
Ques 1. Designation Will Depend On The Mention Criteria
Basic Salary - More Than Equal 25000 "Manager". More Than Equal 18000 "Executive" And Otherwise
"Trainee"
Ques 2. Others Allowance 12% For Manager And 10% For Others On Basic
Ques 3. Calculate Net Salary

1. =IF(D2>=25000,"Manager",IF(D2>=18000,"Executive","Trainee"))
2. =IF(C2="Manager",D2*12%,D2*10%)
3. =D2+E2

Example-9
A B C D E F G H I J
1 Emp City basic Da hra GTI Deduction taxable tax net
Name income income
2 Salman Kolkata 345000 80000
3 Ranbir Delhi 652000 124000
4 Ritesh Mumbai 362000 65000
5 Karina Kolkata 425000 165000

Ques 1 da will be 35% for kolkata city and 30% for others city on basic
Ques 2. calculate gross total income (basic +da+hra)
Ques3. calculate deduction (50% of basic or hra, whichever is less)
Ques4. calculate taxable income (gross total income-deduction)
Ques 5. calculate tax as per slab rate as follows
Ques6. Calculate net income (Taxable income-Tex)
upto 200000 0% of taxable income
200001 to 500000 10% of taxable income
500001 to 1000000 20% of taxable income
above 1000000 30% of taxable income

1. =IF(B2="kolkata",C2*35%,C2*30%)
2. =SUM(C2:E2)
3. =IF(C2>E2,E2*50%,C2*50%)
4. =F2-G2
5. =IF(H2<=200000,H2*0%,IF(H2<=500000,H2*10%,IF(H2<=1000000,H2*20%,H2*30%)))
6. =H2-I2

IFS function:-
The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the
first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with
multiple conditions.

pg. 30
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

IFS एएएएएएए एएएएएए एए एए एएएए एए एए एएएए एएएएएए एएएए एएए एएए, एए एए एएए एएएए एए एए एएएए TRUE
एएएए एए एएए एएएए एएए IFS एए एएएएएएए IF एएएएएएएएए एए एएए एए एएएए एए, एए एए एएएएएए एए एएए एएएएए
एएए एएएए एएएए एएए

Example-2
A B C
1 Stu Name Marks Grade
2 Rajan 87
3 Suman 45
4 Suraj 89
5 Karann 23
6 Parul 22
7 Manish 58

Ques- Define Grade for the following Conditions.

Marks Grade
>= 80 Grade A
>=50 Grade B
>=30 C Grade
<30 Fail
Formula:-
=IFS(B2>=80,"A Grade",B2>=50,"B Grade",B2>=30,"C Grade",B2<30,"Fail")

OR Function:-
it is use to test multiple condition at a time with two statements if the any one condition is true it will execute
first statement otherwise it will execute second statement.
Example-1

A B C D E
1 Name Dept Basic Da Da
2 Ajit Sale 4500
3 Vinay Pur 6500
4 Rahul Pur 1600
5 Vineet Sale 7300
6 Kabir Sale 2400
7 Rajendra Pur 4300

Q 1.calculate da on the basis of following conditions

A. dept = sale OR basic >=5000 da will be 40% of basic
for other dept da will be 30% of basic

Q 2.calculate da on the basis of following conditions

A. dept = pur OR basic >=3500 da will be 30% of basic
for other dept da will be 25% of basic

pg. 31
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

1. =IF(OR(B2="Sale",C2>=5000),C2*40%,C2*30%)
2. =IF(OR(B2="Pur",C2>=3500),C2*30%,C2*25%)

Example-2
A B C D
1 Order No Cost Payment Type Handling Charge
2 AB001 10000 CASH
3 AB002 10000 VISA
4 AB003 20000 CHEQUE
5 AB004 50000 DELTA
6 AB005 60000 CASH
7 AB005 30000 VISA
Ques - Handling charge of Rs 50 is made on all orders paid by VISA or DELTA cards
=IF(OR(C2="Visa",C2="Delta"),50,0)
Example-3

A B C D
1 Employee Location Basic HRA
2 Piyush Kolkata 4500
3 Salim Asansol 7800
4 Kangna Asansol 6600
5 Aman Kolkata 5600
6 Apsrool Kolkata 8900
7 Neha Kolkata 3000

HRA will be Rs.1250 for Kolkata or whose basic is >=8000, otherwise Rs.750.
=IF(OR(B2="kolkata",D2>=8000),1250,750)

And function-
it is also use to test multiple condition at a time with two statements if all the condition is true it will execute
first statement otherwise it will execute second statement

Example-1

A B C D
1 Product Special Offer Order Value Discount
2 Wood Yes 2000
3 Glass No 2000
4 Cement Yes 500
5 Turf Yes 3000
6 Wood Yes 800
7 Glass No 2000
Ques - 10% discount is only given on products which are on special offer and the order value is Rs 1000 or
above.
=IF(AND(B2="Yes",C2>=1000),"10%",0)

pg. 32
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Example-2
A B C D
1 Employee Department Basic HRA
2 Piyush Acct 4500
3 Salim Sales 7800
4 Kangna CRM 6600
5 Aman Sales 5600
6 Apsrool Acct 8900
7 Neha Acct 3000
HRA will be 12% of Basic whose basic is between Rs.7000 to Rs.9000.
=IF(AND(C2>=7000,C2<=9000),C2*12%,0)

Example-3

A B C D E
1 Name Phy Che Math Result
2 Anil Kumar 78 23 45
3 Reetu 29 43 65
4 Rohit 87 45 76
5 Teena 66 65 78
6 Sonu 55 56 76
7 Raj 23 76 67
=IF(AND(B2>=40,C2>=40,D2>=40),"Pass","Fail")

Switch function: - (2019,2021)

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to
the first match found. When no match is found, SWITCH can return an optional default value.
एएएएएए एएएएए एएएएएएए एएएएए एए एएएए एए एएएएएएए एए एएए एए एएएएए एएएए एए, एए एएएए एएएएए एए
एएएएएए एएएएएए एएएए एएए एए एएए एएएएए एएएए एएएएए एए, एए SWITCH एए एएएएएएएए एएएएएएएए एएए एएएए
एएएए एए

Example-1
A B
1 Rating Grade
2 6
3 4
4 1
5 2
6 3
=SWITCH(A2,5,"V Good",4,"Good",3,"Avg",2,"Bad",1,"V Bad","Unknown")

Example-2
A B C
1 Item ID Size Size
2 100 S
3 101 M
pg. 33
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

4 102 L
5 103 S
6 104 L
7 105 M
=SWITCH(B2,"S","Small","M","Medium","L","Large")

IF Error function:-
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result
when no error is detected.
Example-1
A B C D E F
1 Name Physics Chemistry Math Average Average with if error
2 Piyush 34 45 54
3 Salim
4 Aman
5 Aniee
6 Parul
7 Saroj

1. =AVERAGE(B2:D2)
2. =IFERROR(AVERAGE(B2:D2),"")

Convert Function:- It is use to convert one unit to another unit

A B C D E
1 Item stock unit Equivalent unit Units
2 Product A 35 ft =Convert(B2,C2,E2) cm
3 Product B 48 mm cm
4 Product C 88 in cm
5 Product D 55 kg g
6 Product E 123 km m
7 Product F 233 kg g

Workday Function:-
The Excel WORKDAY function adds a specified number of workdays to a start date and returns the resulting
date. WORKDAY excludes weekends (Sat and Sun) and optionally holidays as well. You can use this function to
calculate delivery dates or completion dates that need to take into account workdays and non-workdays.

A B C D
1 Task Name Task Start Date Duration (Working Days) Task Finished Date
2 Task 1 01-01-2022 10
3 Task 2 13-01-2022 15
4 Task 3 20-01-2022 7
5 Task 4 05-01-2022 12
6 Task 5 10-01-1900 15

pg. 34
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B
8 Holidays Date Day
9 14-01-2022 Fri
10 26-01-2022 Wed
Without Holiday:- =Workday(B2,C2)
With Holiday:- =Workday(B2,C2,$A$9:$A$10)

NetworkDAYS Function:-
The NETWORKDAYS function calculates the number of working days between two given dates. It is a date and
time function that is often used in accounting and finance. NETWORKDAYS automatically excludes the
weekend (Saturday and Sunday) from the working days.

A B C D
1 Task Name Task Start Date Task End Date Total Working Day
2 Task 1 03-01-2022 20-01-2022
3 Task 2 13-01-2022 25-01-2022
4 Task 3 20-01-2022 15-02-2022
5 Task 4 05-01-2022 21-02-2022
6 Task 5 10-01-1900 28-02-2022

A B
8 Holidays Date Day
9 14-01-2022 Fri
10 26-01-2022 Wed
=NETWORKDAYS(B2,C2)
=NETWORKDAYS(B2,C2,$A$9:$A$10)

Edate Function:-
The Excel EDATE function returns a date on the same day of the month, and months in the past or future.

A B
1 Policy Date Next Renue Date After 3 Month
2 01-01-2022
3 02-01-2022
4 03-01-2022
5 04-01-2022
6 05-01-2022
7 06-01-2022
=Edate(A2,3) Next 3 Month Date
=Edate(A2,-3) Past 3 Month Date

EOmonth Function:-
The EOMONTH is a worksheet date function in Excel that calculates the end of the month for the given date
by adding a specified number of months to the arguments
A B
1 Date Last Date of Month

pg. 35
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

2 12-06-2022
3 02-01-2022
4 03-01-2022
5 04-01-2022
6 05-01-2022
7 06-01-2022
=EOMONTH(A2,0) Last Date of Current Month
=EOMONTH(A2,1) Last Date of Next Month
=EOMONTH(A2,-1) Last Date of Previous Month
=EOMONTH(A2,0)+1 First Date of Next Month

DatedIF Function:-
This function is use to Calculates the number of days, months, or years between two dates.

A B C D E F
1 Name Birth Date Today Date Age Year Age Month Age Days
2 Piyush 03-01-2002 20-01-2022
3 Shubham 13-01-2001 25-01-2022
4 Rajan 20-01-2000 15-02-2022
5 Aman 05-01-1998 21-02-2022
=DATEDIF(B2,C2,"y") For only Year
=DATEDIF(B2,C2,"m") For Only Month
=DATEDIF(B2,C2,"d") For Only Days
=DATEDIF(B2,C2,"y")&" Years "&DATEDIF(B2,C2,"m")&" Month "&DATEDIF(B2,C2,"d")&" days ")
For Day, Month, Year

Year Function: - This Function is used to Returns the year of a date in cell
Month Function: - This Function is used to Returns the month of a date in cell
Day Function: - This Function is used to Returns the Day of a date in cell

A B C D E
1 Emp ID Joining Date Joining Year Joining Month Joining Day
2 E001 01-01-2012 =Year(B2) =Month(B2) =Day(B2)

3 E002 01-03-2014
4 E003 22-01-2011
5 E004 23-01-2012
6 E005 02-02-2018
7 E006 04-01-2012

Days Function:-
The Excel DAYS function is a formula that calculates the number of days between two dates. The function
appears as follows: "=DAYS(end_date, start_date)”

A B C D
1 Task Name Task Start Date Task End Date Total Days
2 Task 1 03-01-2022 20-01-2022 =Days(C2,B2)
3 Task 2 13-01-2022 25-01-2022

pg. 36
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

4 Task 3 20-01-2022 15-02-2022

5 Task 4 05-01-2022 21-02-2022
6 Task 5 10-01-1900 28-02-2022

Weeknum Function:-
The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week
of year

A B C
1 Task Name Task Start Date Week of Year
2 Task 1 03-01-2022 =Weeknum(B2)
3 Task 2 13-01-2022
4 Task 3 20-01-2022
5 Task 4 05-01-2022
6 Task 5 10-01-1900

Switch function: - (2019 & 2021)

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to
the first match found. When no match is found, SWITCH can return an optional default value.
एक्से ल स्विच फ़ंक्शन मान ़ं की सू ची के विरुद्ध एक मान की तु लना करता है , और पहले वमलान के अनुरूप पररणाम दे ता है । जब क ई वमलान नही ़ं
वमलता है , त SWITCH एक िै कस्विक विफ़ॉल्ट मान लौटा सकता है

Example-1
A B
1 Rating Grade
2 6
3 4
4 1
5 2
6 3
Formula:-
=SWITCH(A2,5,"V Good",4,"Good",3,"Avg",2,"Bad",1,"V Bad","Unknown")

Example-2
A B C
1 Item ID Size Size
2 100 S
3 101 M
4 102 L
5 103 S
6 104 L
7 105 M
Formula:-
=SWITCH(B2,"S","Small","M","Medium","L","Large")

Trim Function:-

pg. 37
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space
characters at the start or end of the text.
एएएएएए एएएएएएएए एएएएएएए एएएएएएए एए एएएएएएएए एएएएए एएएएए एए एएए एएएए एए, एएएएएए एए एएए एएएए एए एए
एएएएए एएएएएए एए एए एएएएएएए एए एएएएएएए एए एएए एएए एएए एएएएए एएएएएएएए एएएए एएएए एएए

A B
1 Name Name
2 anil kumar =Trim(A2)
3 Amit
4 om parkash
5 ram Nehra
6 anil kumar
7 dinesh kumar

Exact Function:-
The EXACT function is a function in Microsoft Excel that compares two text strings and returns TRUE if they
are exactly the same, including their case, and returns FALSE otherwise.

A B C
1 List 1 List 2 Result
2 Pan Card Pan Card =EXACT(A2,B2)
3 Aadhar Card ID Card
4 Voter ID SIM Card
5 Passport Passport
6 Driving license DL Card
7 Bank Passbook Bank Passbook

Data Base Function in Excel: -

DSUM Function
The function DSUM in Excel is also known as the DATABASE SUM function in Excel, which is used to calculate
the sum of the given database based on a certain field.

DCOUNT Function
The Microsoft Excel DCOUNT function returns the number of cells in a column or database that contains
numeric values and meets a given criteria

DAVERAGE Function
The DAVERAGE function returns the average of the values in a column of a list or database that match the
conditions specified.

DMAX Function
The function helps find the maximum value for a specific field/column in a database for selected records
based on user-specified criteria. DMAX will return a numerical value.

DMIN Function
pg. 38
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

The function helps find the Minimum value for a specific field/column in a database for selected records
based on user-specified criteria. DMIN will return a numerical value.

DPRODUCT Function
The Excel DPRODUCT function calculates the product of values in a set of records that match criteria. The
values to multiply are extracted from a given field in the database, specified as an argument.

A B C D E
1 Item Name Company Quantity Price Amount
2 Mouse Mahindra 3 120 360
3 Keyboard TCS 6 320 1920
4 Mouse Mahindra 5 150 750
5 Keyboard TCS 8 250 2000
6 Mouse TCS 8 320 2560
7 Keyboard Mahindra 4 450 1800
8 Mouse Mahindra 5 200 1000
9 Keyboard TCS 2 600 1200

DSUM Formula With 1 condition:-

A B
10 Item Name Amount
11 Mouse =DSUM(A1:E9,E1,A10:A11)

DSUM Formula With multiple condition:-

A B C
12 Item Name Company Amount
13 Mouse Mahindra =DSUM(A1:E9,E1,A12:B13)

DAVERAGE
A B C
14 Item Name Company Average Amount
15 Mouse Mahindra =DAVERAGE(A1:E9,E1,A14:B15)

DCOUNT
A B C
16 Item Name Company Total Record
17 Mouse Mahindra =DCOUNT(A1:E9,E1,A16:B17)

DMAX
A B C
18 Item Name Company Max Amount
19 Mouse Mahindra =DMAX(A1:E9,E1,A18:B19)

DMIN
A B C
20 Item Name Company Min Amount
21 Mouse Mahindra =DMIN(A1:E9,E1,A20:B21)
pg. 39
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

DPRODUCT
A B C
22 Item Name Company Amount
23 Mouse Mahindra =DPRODUCT(A1:E9,E1,A22:B23)

Text Function:- This function is use to find out the day of any date
Concatenate function:-Use concatenate, one of the text functions, to join two or more text.
Upper Function:- this function is use to convert the text in upper case (Capital Letter)
Lower Function:- this function is use to convert the text in lower case (Small Letter)
Proper Function:- this function is use to convert the text in Proper case (Proper Letter)

A B C D E F G H
1 Name Last Name DOB Day Name/Last Name In Name In Name In
Name Block Small Proper
Letters Letter Letter
2 Somesh Singh 03-02-78
3 Arup Banerjee 05-11-88
4 Priti Nag 06-08-87
5 Surti Pandey 01-04-90
1. =Text(C2,"DDDD")
2. =Concatenate(A2," ",B2)
3. =Upper(E2)
4. =Lower(F2)
5. =Proper(G2)

Text to Column:-
Split into different column with the convert text to column wizard.

A B C
1 Software Year Price
2 Coreldraw 1993 7000
3 Ace 1995 5400
4 Office 1997 9000
5 Tally 1998 8000
6 Pagemaker 1999 5600
7 Tally 1999 10000

How to use text to column:-

 Select the cell A2:A7
 Click on data tab
 Click on text to column
 Click on next
 Click on space
 Click on finish

A B C
1 Address State Pin Code
2 Kanpur,Utter Pradesh,24125
3 Gorakhpur,Utter Pradesh,273001
pg. 40
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

4 Pune,Maharastra,232565
5 Mohali,Panjab,424568
6 Amritsar,Panjab,78789
How to use:-
 Select the address A2:A6
 Press Short Cut Key (Alt+A+E)
 Now open convert text to column Wizard
 Click on next
 Click on comma
 Click on next
 Click on finish

Macro:-
If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A
macro is an action or a set of actions that you can run as many times as you want. When you create a macro,
you are recording your mouse clicks and keystrokes.
एएए एएएए एएए Microsoft Excel एएए एएए एएएएए एएए एएएएएएए एए एएए-एएए एएएए एएए, एए एए एए एएएएएएए एए एएएएएएएए
एएएए एए एएए एएएएएए एएएएएएए एए एएएए एएएए एएएएएए एए एएएएएए एए एएएएएएएए एए एए एएएए एए एएएए एए एएएएए एएए
एएएएए एएएए एएए एएए एएएए एएएए एए एए एएएएएए एएएएए एएए, एए एए एएएए एएएए एएएएए एए एएएएएएएएएएए एएएएएएए एए
एएए एएएए एएएए
How to record macro:-
 Click on view tab
 Click on Macro
 Click on record macro
 Click on Type your macro Name
 Press Any short cut key like Ctrl+Q
 Click on store macro in
 Select this workbook
 Click on ok
 Now type your matter Like bellow

Roll No Name Marks

1 Piyush 100
2 Parul 66
3 Salim 67
4 Nitesh 68

 Click on view tab

 Click on macro
 Click on pause recording
 Now click on any one blank cell
 Press your Shortcut Key.

Lookup and References Function:-

Transpose Function:-
It is use to display the data of row into column and column data into row
pg. 41
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C
1 Emp_ID Salesman State
2 WIS01 Rama Rajasthan
3 WIS02 Krishna Gujarat
4 WIS03 Prakash Rajasthan
5 WIS04 Kanupriya Gujarat
6 WIS05 Jyoti Rajasthan
7 WIS06 Raja Tamil Nadu

How to use:-
 Select all the data
 Press Ctrl+C for copy
 Select any one blank cell
 Click on home tab
 Click on Paste
 Click on Paste special
 Click On Transpose

V lookup Function: -
It is stand for vertical lookup. Looks for a value in left most column of a table and then return a value in the
same row from a column you specify.

Example-1
A B C D E
1 C. ID Name Product Location Delivery Status
2 PMT01 Vishal Monitor Barulpur Yes
3 PMT02 Sanjay Cd Rom Garla Yes
4 PMT03 Asit Printer Ballygunj Yes
5 PMT04 Nayan Cd Writer Central Avenue Yes
6 PMT05 Rakesh Combo Drive Hazra No

A B C D E
7 C. ID Name Product Location Delivery Status
8 PMT01

Ques - prepare the data base in such a way so that whenever anyone put any customers id the respective
customer details will shown under the respective fields

Name:- =VLOOKUP(A8,A1:E6,2,0)
Product:- =VLOOKUP(A8,A1:E6,3,0)
Location:- =VLOOKUP(A8,A1:E6,4,0)
Delivery Status:- =VLOOKUP(A8,A1:E6,5,0)

Example-2
A B C D E F G
1 Sales ID Product Quantity Agent Rate VAT Discount
2 WIS01 Mouse 12

pg. 42
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

3 WIS02 LED TV 1
4 WIS03 Washing Machine 4
5 WIS04 LED TV 7
6 WIS05 Mouse 25
7 WIS06 Key Board 16
8 WIS07 Washing Machine 8
9 WIS08 LED TV 14

10 Product Agent Rate VAT Quantity Discount

11 Mouse Vinod 155 5% 1 0%
12 Key Board Deepak 225 5% 5 2%
13 LED TV Priyanka 16500 14.50% 10 5%
14 Washing Ankit 22000 14.50% 20 8%
Machine

1. Pull the data of Agent, Rate and VAT column by using suitable function.
2. Pull the data of Discount column by using suitable function.

1. Agent:- =VLOOKUP(B2,$A$10:$D$14,2,0)
2. Rate:- =VLOOKUP(B2,$A$10:$D$14,3,0)
3. VAT:- =VLOOKUP(B2,$A$10:$D$14,4,0)
4. Discount:-=VLOOKUP(C2,$F$10:$G$14,2,1)

Basic Vlookup: -
A B C D E
1 Item ID Item Name Qty Price Amount
2 100 Mouse 10 100 1000
3 101 Keyboard 11 150 1650
4 102 Scanner 12 200 2400
5 103 Hard Disk 13 250 3250
6 104 Mouse 14 300 4200
7 105 Mouse 15 350 5250
8 106 Mouse 33 120 3960

9 Item ID Item Name Amount

10 =VLOOKUP(A10,A1:E8,2,0) =VLOOKUP(A10,A1:E8,5,0)

Lookup Range:-
A B
1 Emp ID Working Hours
2 100 8 AM - 2 PM
3 105 9 AM - 3 PM
4 110 10 AM - 4 PM
5 115 11 AM - 8 PM

pg. 43
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

6 Emp ID Working Hours

7 =VLOOKUP(A7,A1:B7,2,1)

VLOOKUP multiple Tables: -

Sheet-1 Table-1
A B C D E
1 ID Emp Name Gender Age Salary
2 1 Rohit Male 22 10000
3 2 Neha Female 23 11000
4 3 Ram Male 24 12000
5 4 Rohit Male 25 13000
6 5 Sonu Male 26 14000

Sheet-2 Table-2
A B C D E
1 ID Emp Name Gender Age Salary
2 6 Aman Male 44 21500
3 7 Rohit Male 25 11000
4 8 Karan Male 34 12000
5 9 Reena Female 25 13050
6 10 Sonu Male 32 14000

Sheet-3 Table-3
A B C D E
1 ID Emp Name Gender Age Salary
2 11 Reetu Female 65 17000
3 12 Neha Female 23 11000
4 13 Suraj Male 34 12500
5 14 Piyush Male 25 13000
6 15 Kamlesh Male 26 14000

Sheet-4
A B C D E
1 ID Emp Name Gender Age Salary
2

How to use:-
Step-1 first we define our table name:-
 Select all data on sheet-1 (A1:E6)
 Click on formulas tab
 Click on define name
 Type your name (table1)
Do this work sheet-2 and Sheet-3 but define name is (table2 and table3)

Formula:-type formula on sheet-4 in the cell B2

pg. 44
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

=VLOOKUP($A$2,IF($A$2<=5,table1,IF($A$2<=10,table2,table3)),COLUMNS($A$1:B1),0)

HLookup Function:-
HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a
row for the matching data and outputting from the corresponding column.

Example-1
A B C D E
1 Emp Code EDU0001 EDU0002 EDU0003 EDU0004
2 Emp Name Sandeep Rajat Anidya Debjyoti
3 Post Examiner Examiner Back Office Back Office
4 Basic 6000 7500 4000 4500
5 Da 2400 3000 1600 1800
6
7 Emp Code EDU0003
8 Emp Name =HLOOKUP(B7,A1:E5,2,0)
9 Post =HLOOKUP(B7,A1:E5,3,0)
10 Basic =HLOOKUP(B7,A1:E5,4,0)
11 Da =HLOOKUP(B7,A1:E5,5,0)

HLookup With Automatic Rows Index and IFerror Function:-

=IFERROR(HLOOKUP($B$7,$A$1:$E$5,ROWS($A$7:A8),0),"Not Found")

Example-2
A B C D E
1 Position Executive Manager GM VP
2 Basic 7500 25000 40000 75000

3 EMPNO Position DOJ Basic

4 E01 Executive 15-08-09
5 E02 VP 20-09-09
6 E03 Manager 12-02-10
7 E04 Executive 11-08-10
8 E05 GM 28-08-10
9 E06 Executive 12-08-12
Pull the Basic Salary from the above table by using lookup & References function.
=HLOOKUP(B4,$A$1:$E$2,2,0)
Goal Seek:-
Goal seek is the process of finding the correct input value when only the output is know.

A B C D E
1 Product QTY Cost Price Selling Price Profit
2 San Disk Memory Card 15 400 520 =D2-C2
3 Samsung Pen drive 15 800 900
4 Hp-Pocket Hdd 22 5000 6200
pg. 45
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

5 Mouse (HP) 65 8000 10000

6 Keyboard(HP) 25 10000 12000
Total Profit =Sum(E2:E6)
Ques) Find out the selling price of Samsung Pen drive to make the total profit as Rs 8000 (use goal seek)
Ques) Find out the selling price of Keyboard (HP) to make the total profit as Rs 10000 (use goal seek)

How to use:-
 Select the cell E7
 Click on Data Tab
 Click on what if analysis
 Click on Goal Seek
 Click on to value box
 Type your target value (8000)
 Click by changing cell
 Click on cell D3
 Click on ok

Sorting:-
Sorting is the purpose of logically arrange the data ascending and descending order.

A B C
1 Item Name Sales Man Amount
2 Monitor Rajesh 3500
3 Keyboard Rman 300
4 Mouse Aman 230
5 UPS Vijay 2300
6 Monitor Ajay 4000
7 Keyboard Ajay 480
8 Monitor Rajesh 3500

How to use Sorting:-

 Select all the matter
 Click on data tab
 Click on short
 Click on sort by
 Select any one category
 Select your order
 A to Z or Z to A
 Click on ok

Auto Filter: -
Use the AutoFilter feature to find, show, or hide values—in one or more columns of data. You can filter based
on choices you make from a list, or search to find the data that you seek. When you filter data, entire rows will
be hidden if the values in one or more columns don't meet the filtering criteria.

pg. 46
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C D E F
1 Customer ID Name Order Date Product Location Delivery Status
2 A1480038 Vishal 01-01-07 Monitor Gorakhpur Yes
3 A1480039 Sanjay 05-05-07 Cd Rom Basti Yes
4 A1480040 Asit 03-07-07 Printer Gorakhpur Yes
5 A1480041 Nayan 25-07-07 Monitor Deoria Yes
6 A1480042 Rakesh 03-09-07 Printer Basti No
7 A1480043 Dipanjan 08-09-07 Monitor Deoria No
Ques-1 find out the record of monitor only using Auto filter
Ques-2 find out the record of monitor and printer by using auto filter

How to use:-
 Select all the data
 Click on data tab
 Click on Filter
 Now filter your data

Advance Filter:-
The Advanced Filter gives you the flexibility to extract your records to another location on the same
worksheet or another worksheet in your workbook.

Example-1
A B C D
1 DATE CATEGORY DESCRIPTION AMOUNT
2 20/11/2013 Household Cell phone 750
3 22/11/2013 Household Electricity 1235
4 04/11/2013 Pets Food 370
5 04/11/2013 Transportation Fuel 780
6 21/11/2013 Household Garbage 125
7 24/11/2013 Food Groceries 1150

8 DATE CATEGORY DESCRIPTION AMOUNT

9 Household
Extract the Household category details in another location by using advance filter
How to use:-
 Select any one blank cell
 Click on data tab
 Click on advanced
 Click on list range box
 Select list range (A1:D7)
 Click on criteria range box
 Select your criteria range (A8:D9)
 Click on copy to another location
 Click on copy to box
 Click on any one blank cell
 Click on ok.
pg. 47
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Sheet-1 Example-2
A B C D
1 Salesman Location Commission Location
2 Tapan Dey Kolkata 90300 Kolkata
3 Riya Sen Asansol 88377
4 Albert Peter Kolkata 79026
5 Abhishek Pandey Durgapur 109585
6 Soma Chatterjee Durgapur 114270
7 Rittika Singh Kolkata 84027
Extract The Details Of Kolkata City In Advance Filter Sheet

How to use:-
 Select the any one blank cell on Sheet 2
 Click on data tab
 Click on advanced
 Click on copy to another location
 Click on Sheet-1
 Click on List Range box
 Select the List range (A1:C7)
 Click on Criteria range box
 Click on Sheet-1
 Select your Criteria (D1:D2)
 Click on copy to box
 Select any one blank cell on sheet 2
 Click on ok
Example-2
A B C D E F G
1 ID Country Investment Type Due (Rs.) Country Due (Rs.)
2 780 INDIA Debentures Interest 800 INDIA >100
3 745 INDIA Deposits Interest 369
4 715 INDIA Debentures Interest 969
5 550 INDIA Debentures Interest 99
6 700 GERMANY Deposits Interest 105
7 632 USA Debentures Interest 57
Extract the details of INDIA and due is greater than 100 on advance filter sheet

How to use:-
 Select the any one blank cell on Sheet 2
 Click on data tab
 Click on advanced
 Click on copy to another location
 Click on Sheet-1
 Click on List Range box
 Select the List range (A1:D7)
 Click on Criteria range box

pg. 48
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on Sheet-1
 Select your Criteria (F1:G2)
 Click on copy to box
 Select any one blank cell on sheet 2
 Click on ok

Data Validation:-
In ms excel this option is used to entering the data in excel without any error

Example-1
A B C D
1 Day Hours worked Rate per hour Total earning
2 Monday 20
3 Tuesday 20
4 Wednesday 20
5 Thursday 20
6 Friday 20
7
8 Ques - Hour Worked Should Not Be Greater Than 10

How to use:-
 Select the cell B2:B6
 Click on data tab
 Click on data validation
 Click on settings options
 Click on allow options
 Select whole number
 Click on data options
 Select less than or equal to
 Click on maximum box type 10
 Click on ok

Example-2
A B C D
1 Day Hours worked Rate per hour Total earning
2 Monday 20
3 Tuesday 20
4 Wednesday 20
5 Thursday 20
6 Friday 20
7
8 Ques - Hour Worked Should Be Between 5 to 10

How to use:-
 Select the cell B2:B6
 Click on data tab
 Click on data validation
 Click on settings option
 Click on allow option

pg. 49
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Select whole number

 Click on data option
 Select between
 Click on minimum box type 5
 Click on maximum box type 10
 Click on ok

Example-3
A B C D
1 Day Hours Worked Rate Per Hour Total Earning
2 20
3 20
4 20
5 20
6 20
7
8 Ques - Hour Worked Should Be Less Than 10
9 Ques-In Day Column Text Should Not Be Entered More Than 6 Characters

Ques-In Day Column Text Should Not Be Entered More Than 6 Characters
How to use:-
 Select the cell A2:A6
 Click on data tab
 Click on data validation
 Click on settings option
 Click on allow option
 Select Text length
 Click on data option
 Select less than or equal to
 Click on maximum box type 6
 Click on ok

Example-4
A B C D
1 Day Hours Worked Rate Per Hour Total Earning
2 Monday 20
3 Tuesday 20
4 Wednesday 20
5 Thursday 20
6 Friday 20
7
8 Ques -1 Hour Work Should Be Greater Than 7. Set The Validation
9 In Such A Way So That Invalid Data Should Also Be Appear
10 Ques -2 Circle Invalid Data
How to use:-
 Select the cell B2:B6
 Click on data tab
 Click on data validation
 Click on settings option

pg. 50
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on allow options

 Select whole number
 Click on data option
 Select less grater or equal to
 Click on Minimum box type 7
 Click on error alert
 Click on style
 Select warning
 Click on ok
How to circle invalid data
 Select the cell B2:B6
 Click on data tab
 Click on data validation
 Click on circle Invalid data

Example-5
A B C D
1 Day Hours Worked Rate Per Hour Total Earning
2 12 20
3 7 20
4 4 20
5 6 20
6 13 20
7
8 Ques - Day Column Should Be Entered Through Drop Down List

How to use:-
 Select the cell A2:A7
 Click on data tab
 Click on data validation
 Click on setting
 Click on allow option
 Select list option
 Click on source box
 Type Sun, Mon, Tue, Wed, Tue, Fri, Sat
 Click on ok

Example-6
A B C D
1 Numbers Numbers
2 1
3 2
4 3
5 2
6 5
7 3
8
9 Ques - Set The Validation In Such A Way So It Does Not Accept Any Duplicate Value

pg. 51
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

How to use:-
 Select the cell B2:B7
 Click on data tab
 Click on data validation
 Click on setting option
 Click allow option
 Click on custom
 Click on formula box
 Type formula
 =Countif(Select the cell B2:B7 Press F4,click on cell B2)=1
 Click on ok

Paste special: - (Ctrl+Alt+V)

it is a kind of option that is use for special paste like as. Formula, Value, Format, validation, etc

Example-1
A B C D
1 Day Hours Worked Rate Per Hour Total Earning
2 Monday 12 20 =B2*C2 (240)
3 Tuesday 7 20 140
4 Wednesday 4 20 80
5 Thursday 6 20 120
6 Friday 13 20 260

Ques - paste only the values from the example 1

Ques - paste only the Formatting from the example 1
Ques - paste only the Formula from the example 1

How to use:-
 Select and copy the cell example-1 A1:D6
 Select any one blank cell
 Click on paste
 Click on paste special
 Click on value
 Click on ok.

A B C D
1 Name Test 1 Test 2 Total
2 Amit 42 75
3 Raju 58 12
4 Vinay 94 62
5 Akhil 67 23
6 Rajesh 77 44
7 Aman 87 34
Ques - Calculate The Total By Using Paste Special
How to use:-
pg. 52
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Select and copy the cell B2:B7

 Click on cell D2 and Press Ctrl+V
 Select and copy the Cell C2:C7
 Click on cell D2
 Click on home tab
 Click on paste
 Click on paste special
 Click on add
 Click on ok

Subtotal:-
In MS excel subtotal is a kind of option that is use to find out group total and grand total.

A B
1 Item Name Amount
2 Mouse 180
3 Keyboard 300
4 SMPS 450
5 SMPS 450
6 Hard Disk 4100
7 Mouse 150
8 Keyboard 2000
9 SMPS 600
10 SMPS 500
11 Hard Disk 3300

How to use:-
 Select all the matter.
 Click on data tab.
 Click on short A to Z.
 Click on subtotal.
 Click on ok button

A B C D E F G
1 Salesman Area Department Month Sale 2016 Sale 2017 Total
2 Ram north Garment Jan 1200 1100 2300
3 Shyam west Garment Mar 1100 2100 3200
4 Om east Electronics Jan 1134 2200 3334
5 Raman north Electronics Feb 1232 2100 3332
6 Deepak west Garment Mar 1150 1400 2550
7 Ram north Garment Feb 1200 1300 2500
8 Rihan west Electronics Jan 2000 1500 3500
9 Om east Furniture Jan 1280 3200 4480
10 Anil west Furniture Feb 800 1240 2040

Ques-1- calculate the month wise total sale?

Step-1
pg. 53
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

How to use:-
 Select all the data A1:G10
 Click on data tab
 Click on sort
 Click on sort by select month
 Click on order A to Z
 Click on ok

Step-2
 Select all the data A1:G10
 Click on data tab
 Click on subtotal
 Click on At each change in
 Select month
 Use function (Sum)
 Click on add subtotal to select (Total)
 Click on Ok.

Conditional formatting: -
In MS excel this option is use for format any data on the basis of condition.

Example-1
A B C D E
1 Items Total Length Value Rate Gross Value
2 Item1 504mt 504 12 =C2*D2 (6048)
3 Item2 32ft 32 17 544
4 Item3 5671mt 5671 19 107749
5 Item4 123mt 800 14 11200
6 Item5 34ft 989 19 18791
7 Item6 676ft 100 21 2100

Ques-1. format gross value greater than 10000 with green color
Ques-2. Format gross value less than 5000 with red color

How to use:-
 Select the cell E2:E7
 Click on home tab
 Click on conditional formatting
 Click on new rule
 Click on format only cell that contain
 Click on between option
 Select greater than or equal to
 Click on blank box type 10000
 Click on format
 Click on color
 Select your color
 Click on two time ok

pg. 54
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Example-2
A B C
1 Area Product Sales
2 East Tally.Erp 9 14000
3 West Paypack 12000
4 East Taxman-Etds 2200
5 West Tally.Erp 9 6500
6 North Tally.Erp 9 8000
7 North Nxt-Erp 7000
8 North Paypack 12000

Ques-1. Format Sale With Red Fill Between 5000 To 10000

Ques-2. Format Product That Contain "X" With Blue Color
Ques-3. Format Sale That Contain Duplicate Value With Red Fill
Ques-4. Format Top 2 Sale Value With Green Fill
Ques-5. Format Bottom 3 Sale Value With Red Fill

1. Format Sale With Red Fill Between 5000 To 10000

 Select the cell C2:C8
 Click on home tab
 Click on conditional formatting
 Click on new rule
 Click on format only cell that contain
 Click on between option
 Select Between
 Click on blank box type 5000
 Click on next blank box type 10000
 Click on format
 Click on fill tab
 Select your color
 Click on two time ok

2. Format Product That Contain "X" With Blue Color

 Select the cell B2:B8
 Click on home tab
 Click on conditional formatting
 Click on highlight cells rules
 Click on text that contains
 Type x
 Click on with option
 Select custom format
 Click on fill tab
 Select your color
 Click on two time ok

3. Format Sale That Contain Duplicate Value With Red Fill

 Select the cell C2:C8
pg. 55
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on home tab

 Click on conditional formatting
 Click on highlight cell rules
 Click on duplicate values
 Click on value with
 Click on custom format
 Click on fill option
 Select your color
 Click ok two time ok

4. Format Top 2 Sale Value With Green Fill

 Select the cell C2:C8
 Click on home tab
 Click on conditional formatting
 Click on top/bottom rules
 Click on top 10 items
 Type 2
 Click on with box
 Select custom format
 Select your color
 Click on two time ok

Example-3
A B C D
1 Product Company Model No Sale Price
2 Blue Tooth NOKIA 6300 7000
3 Mobile NOKIA 5200 6300
4 Blue Tooth LG Kg800 1050
5 Mobile LG Kg900 1100
6 Blue Tooth NOKIA 6030 560
7 Data Cable MOTOROLA Moto566 450
8 Data Cable MOTOROLA Moto747 490
Insert data bar on Sale Price Value

How to use:-
 Select the cell D2:D8
 Click on home tab
 Click on conditional formatting
 Click on data bars
 Select any one data bars.

Advance conditional formatting:-

Example-1
A B C D
1 Date Category Description Amount
pg. 56
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

2 20/Nov/13 Household Cell Phone 750

3 20/Nov/13 Household Electricity 1235
4 20/Nov/13 Pets Food 370
5 20/Nov/13 Transportation Fuel 780
6 20/Nov/13 Household Garbage 125
7 20/Nov/13 Food Groceries 1150
Ques-1Highlight The Description In Red Fill If The Amount Is More Than 1000
Ques-2 Highlight Entire Table In Red Fill If The Amount Is Less Than 700
Note:-(Agar entair table ko color karna hai to colomn k phele dollar lge ho row k phele nhi)

How to use:-
 Select the cell C2:C7
 Click on home tab
 Click on conditional formatting
 Click on new rule
 Click on use a formula to determine which cells to format
 Now type your formula
 =if(D2>1000,1,0)
 Click on format
 Click on fill
 Select your color
 Click on two time ok

How to use:-
 Select the cell A2:C7
 Click on home tab
 Click on conditional formatting
 Click on new rule
 Click on use a formula to determine which cells to format
 Now type your formula
 =if($D2>1000,1,0)
 Click on format
 Click on fill
 Select your color
 Click on two time ok

Example-2
A B C D
1 Folio Number Country Investment Type Due (Rs.)
2 780 INDIA Debenture Interest 369
3 745 INDIA Deposit Interest 969
4 715 INDIA Debenture Interest 99
5 550 INDIA Interest On Unclaimed 105
6 700 GERMANY Deposit Interest 57
7 632 USA Debenture Interest 121
Ques Highlight the Folio number in Black Color Background and White Color Text if Investment Type is
Debentures Interest

How to use:-
pg. 57
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Select the cell A2:A7

 =If(C2=”Debenture interest”,1,0)

Example-3
A B C D E
1 Bill No. Bill Date Party Name Due Days Due Amount
2 PMT01 11-01-15 Vishal enterprise 181 30000
3 PMT02 14-02-14 Roushan Telecom 99 125000
4 PMT03 16-03-14 Andrew & Sons 117 20000
5 PMT04 03-04-14 Vishal enterprise 147 32000
6 PMT05 22-01-14 Rose Enterprise 84 46000
7 PMT06 02-05-14 Andrew & Sons 6 54000
8 PMT07 15-05-14 Rose Enterprise 57 88000
Ques-1 highlight bill number in red fill if date is greater than 1-4-2014

How to use:-
 Select the cell A2:A8
 =If(B2>datevalue(“1-4-2014”),1,0)

Ques-2 highlight party name in black colour background and white colour text if date is less than 1-6-2014
How to use:-
 Select the cell C2:C8
 =If(B2<datevalue(“1-6-2014”),1,0)

Consolidation
Data consolidation in Excel is the process of bringing similar data from multiple sources to one place, which
can provide a unified view for better analytics. The 'Consolidate' feature in Excel can be useful when you want
to summarize the same data from multiple sources.

On sheet 1:-
A B C D
1 Salary of January
2 Name Salary TDS Net Salary
3 Piyush 15000 100 =B3-C3 Enter
4 Kaustubh 12000 120
5 Neha 10000 150
6 Shehnaz 5000 950

On Sheet 2:-
A B C D
1 Salary of February
2 Name Salary TDS Net Salary
3 Piyush 16000 1000 =B3-C3 Enter
4 Kaustubh 18000 1200
5 Neha 13200 1500
6 Shehnaz 14500 950

On sheet 3:-
pg. 58
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C D
1 Salary of March
2 Name Salary TDS Net Salary
3 Piyush 16500 160 =B3-C3 Enter
4 Kaustubh 14500 180
5 Neha 19000 140
6 Shehnaz 17800 956

On Sheet 4:-
A B C D
1 Salary of first Qtr
2 Name Salary TDS Net Salary
3 Piyush
4 Kaustubh
5 Neha
6 Shehnaz

How to use
 Select the cell B3.
 Click on data tab.
 Click on consolidation.
 Click on reference box.
 Click on sheet 1.
 Select all the numeric data like B3:D6.
 Click on add button.
 Click on sheet 2.
 Select all the numeric data like B3:D6.
 Click on add button.
 Click on sheet 3.
 Select all the numeric data like B3:D6.
 Click on ok button

Example-2
On sheet-1

A B C D E F G
1 Sales Man Jan Feb Mar Apr May Jun
2 Piyush 35232 30937 31395 15076 34936 19961
3 Nitesh 17016 21341 30831 38493 21434 31507
4 Apsarool 30022 36014 24636 32635 36814 27459
5 Aman 30124 17302 23975 33488 14402 11026
6 Deepak 36210 19437 32272 15542 18235 29515
7 Rahul 26971 26574 22041 32672 30529 30250

pg. 59
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

On Sheet-2
A B C D E F G
1 Sales Man Jan Feb Mar Apr May Jun
2 Piyush 18303 11464 20190 17304 18194 16939
3 Nitesh 24136 24189 16562 19913 13861 17589
4 Apsarool 20572 24449 14835 22331 21027 18396
5 Aman 13858 17958 14271 20035 18838 12551
6 Deepak 12356 15974 18390 19593 13969 19366
7 Rahul 14275 17160 20724 17033 19632 19185

How to use:-
 Select any one blank cell on sheet-3
 Click on data tab
 Click on consolidate
 Click on reference box
 Active this options (Top Row, Left Column, Create link to source data)
 Click on Sheet-1
 Select all the data A1:G8
 Click on add
 Click on sheet-2
 Select all data A1:G8
 Click on ok

Pivot Table:-
A pivot table is a table of statistics that summarizes the data of a more extensive table. This summary might
include sums, averages, or other statistics, which the pivot table groups together in a meaningful way. Pivot
tables are a technique in data processing.

Order ID Product Category Sales Date Country

1 Carrots Vegetables 4,270.00 02-Jan-14 United States
2 Broccoli Vegetables 8,239.00 03-Jan-15 United Kingdom
3 Banana Fruit 617.00 05-Jan-14 United States
4 Banana Fruit 8,384.00 07-Jan-14 Canada
5 Beans Vegetables 2,626.00 09-Jan-14 Germany
6 Orange Fruit 3,610.00 10-Jan-14 United States
7 Broccoli Vegetables 9,062.00 11-Jan-15 Australia
8 Apple Fruit 2,417.00 13-Jan-14 France
9 Apple Fruit 7,431.00 15-Jan-14 Canada
10 Banana Fruit 8,250.00 16-Jan-14 Germany
11 Broccoli Vegetables 7,012.00 18-Jan-15 United States
12 Carrots Vegetables 1,903.00 20-Jan-14 Germany
13 Broccoli Vegetables 2,824.00 22-Jan-14 Canada
14 Apple Fruit 6,946.00 24-Jan-14 France
15 Banana Fruit 2,320.00 27-Jan-14 United Kingdom
16 Banana Fruit 2,116.00 28-Jan-14 United States
17 Banana Fruit 1,135.00 30-Jan-14 United Kingdom
18 Broccoli Vegetables 3,595.00 30-Jan-16 United Kingdom
19 Apple Fruit 1,161.00 02-Feb-14 United States
pg. 60
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

20 Orange Fruit 2,256.00 04-Feb-14 France

How To Use:-

 Select all the data

 Click on insert tab
 Click on pivot table
 Click on pivot table range
 Click on new worksheet
 Click on ok

Pivot Table Slicer:-

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers
also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.

स्लाइसर ऐसे बटन प्रदान करते हैं वजन पर आप स्विक करके टे बल या वपि टटे बल्स क वफल्टर कर सकते हैं । त्वररत वफल्टरऱं ग के अलािा,
स्लाइसर ितत मान वफल्टरऱं ग स्वथिवत क भी इ़ं वगत करते हैं , वजससे यह समझना आसान ह जाता है वक ितत मान में िास्ति में क्या प्रदवशतत वकया जा
रहा है ।

How to use slicers:-

 Select all the data
 Press Ctrl+T (For Table)
 Click on my table has header
 Click on ok
 Click on table design tab
 Click on insert slicers
 Click on Country and Category
 Click on ok

Pivot Chat In Excel: -

PivotChart in Excel is an in-built program tool that helps you summarize selected rows and columns of data in
a spreadsheet. The visual representation of a PivotTable or any tabular data helps summarize and analyse the
datasets, patterns, and trends.

How to use:-
 Select all the data
 Click on insert tab
 Click on pivot chart
 Click on pivot chart and pivot table
 Click on new worksheet
 Click on ok

What is Table In MS Excel:-

Excel Tables are containers for your data. The definition as a table shows Excel that all the data in the columns
and rows you have marked are related. That's a big time-saving. First, you get a lot of list-related features
automatically. And second, when you add a row, all formats and formulas are added automatically.

pg. 61
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

एएएएएए एएएएएए एएएए एएएए एए एएए एएएएएए एएएए एएएएएए एए एएए एएए एएएएएएए एएएएएए एए एएएएएए एए एए एएएए
एएएएएए एएएएएएए एएएए एए एएएएएएएएए एएए एएए एएएए एएएएएएए एएएए एए एएए एए एएएए एएए एएए एएएए एएएए, एएएए
एएएए-एएएएएएए एएएए एएएए एएएएएएएए एएएएएएएए एएए एए एएएएए एएएए एए एएएएए, एए एए एएए एएएएएए एएएएएए एएए, एए
एएए एएएएएएए एए एएएएए एएएएएएएए एएए एए एएएए एएएए एएएए

A B C D E
1 Emp. Code Name Department DOJ Basic
2 E001 Amit MKT 20-08-2003 12000
3 E002 Ravi Acct 21-05-2006 15000
4 E003 Sanjay MKT 06-06-2000 10000
5 E004 Rajesh MKT 06-07-2000 12000
6 E005 Vinod Acct 25-09-2001 10000
7 E006 Ramesh MKT 21-04-2000 10000
8 E007 Kamal MKT 20-08-2003 12000
9 E008 Rajesh Acct 21-05-2006 15000
10 E009 Raman MKT 06-06-2000 10000
11 E0010 Komal MKT 06-07-2000 12000
12 E0011 Suman Acct 25-09-2001 10000
13 E0012 Sohan MKT 21-04-2000 10000
How to use:-
 Select all the data
 click on insert tab
 click on table
 click on my table has headers
 click on ok
 OR Press Ctrl+T

What is Form in Excel:-

A form contains controls, such as boxes or dropdown lists, that can make it easier for people who use your
worksheet to enter or edit data.
एएएएएए एएए एएएएएएएए एएएए एएए, एएएए एएएएए एए एएएएएएएएए एएएएएएए, एए एए एएएएए एए एएए एएएए एएएए एएएए एए
एएएएएएए एएएए एएएए एएए एएएए एएए एए एएएए एएएएएएए एए एएएएए एएएए एएएए

How to use Form:-

 click on customize Quick Access Toolbar
 click on more Commands
 click on quick access toolbar
 click on choose popular commands
 click on all commands
 select Form
 click on add Button
 click on ok

Quick Access Toolbar

pg. 62
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Some Important Trick and Tips Of MS Excel

Trick-1-how to fill blank cell at a time if the blank cell 2 or more

A B C D E F
1 School Id Student Hindi English Drawing Science
2 EDU01 Piyush Tripathi 81 56 78
3 EDU02 Shubham Mani 56 15 99
4 EDU03 Karan Gaur 66 89
5 EDU04 Sumit Sharma 72 58 68
6 EDU05 Suman Ranjan 31 55 25
How to use:-
 Select numerical data
 Press Ctrl+G
 Click on special
 Select blanks
 Click on ok
 Type Absent And Press Ctrl+Enter

Trick-2 How To get Total with Trick.

Quantity
Month Pen Pencil Books Paper
January 12 96 65 68
February 18 58 58 25
March 19 36 33 65
Total
April 22 78 42 41
May 32 65 24 25
June 54 25 12 65
Total
July 42 59 45 32
August 65 54 77 24
September 88 24 65 14
Total
October 45 14 14 69
November 65 65 74 77
December 88 25 33 25
Total
How To Use:-
 First we select all the numerical data
 Press Ctrl+G
 Click on Special option
 Select Blanks Option
 Click On ok
 Press Alt+=
pg. 63
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Trick-3-How to put Zero in front of any number.

A B
1 Order Id Sales Rate
2 PMT-01 7
3 PMT-02 2565
4 PMT-03 652
5 PMT-04 36
6 PMT-05 568
7 PMT-06 256
How To Use:-
 Select all the numerical data
 Press Right Click on Mouse
 Click on format Cell
 Click on Custom
 Remove general
 Press 5 Time 0
 Click On ok

Trick-4- How to make Positive value green colour and negative value Red colour with Trick.

A B C
1 Category Sales Profit
2 Household 458 -26
3 Household 854 290
4 Pets 140 21
5 Transportation 27 -22
6 Household 65 -33
7 Food 58 36
How To use:-
 Select all the numerical data of profit column
 Right click mouse button
 Click on format cell
 Click on custom
 Remove general
 Type [color4]##;[color3]-##

Trick-5-How to Calculate age in excel?

A B
1 Enter DOB Age in year
2 10-05-2000
3 02-06-1998
4 15-12-1996
5 16-10-1988

pg. 64
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

6 05-04-1997
7 26-08-1996
Formula:-
=TEXT(TODAY()-A2,"yy")&" years"

Trick-6-How to fill Sr.No in one series.

A
1 1
2
3
4
5
6
7
8
9
10
How to use:-
 Select the cell A1
 Press Ctrl button
 And drag your mouse with + symbol

Trick-7- How to match two lists in MS Excel?

A B C
1 List 1 List 2 Result
2 Pan Card Pan Card =EXACT(A2,B2)
3 Aadhar Card ID Card
4 Voter ID SIM Card
5 Passport Passport
6 Driving license DL Card
7 Bank Passbook Bank Passbook
8 Visa Card Rupay Card

Trick-8- How To insert cell message.

1 A B
2 ID Name
3 EDU01
4 EDU02
5 EDU03
6 EDU04
7 EDU05
How to use:-
 Select your range like B2:B7
 Click on data tab
 Click on data validation
pg. 65
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on input message

 Click on title box
 Type Information
 Enter name in Uppercase only
 Click on ok

Trick-9- How to insert any Picture in the cell.

A B C
1 Sales Staff Product Sale
2 Pan Tripathi Mobile 65983
3 Supari Uppadhyay CUP 25659
4 Surti Devi SMPS 42569
5 Jarda Khan HDD (500GB) 41256

How to Use:-
 Select any cell
 Click on review tab
 Click on new comment
 Single click on the box outline
 Right click on the mouse
 Click on format comment
 Click on colours and lines
 Click on colour options
 Click on fill effect
 Click on picture
 Click on select picture
 Select your location
 Select your picture
 Click on insert option
 Click on ok two time

Trick-10- How to Freeze any Cell.

A B C D E
1 Emp ID Name Age City Pin Code
2 EDU01 Pan Tripathi 25 Delhi 26563
3 EDU02 Supari Uppadhyay 36 Kolkata 12545
4 EDU03 Surti Devi 12 Mumbai 36589
5 EDU04 Jarda Khan 45 Jaipur 25689
6 EDU05 Sahad Kumari 26 Rajasthan 14563
How to use:-
 Click on any one cell
 Click on view tab
 Click on freeze panes
 Click on freeze top row

pg. 66
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Trick-11- How to print Headings on each page in excel.

Order ID Product Category Sales Date Country

1 Broccoli Vegetables 8,239.00 03-Jan-15 United Kingdom
2 Banana Fruit 617.00 05-Jan-14 United States
3 Banana Fruit 8,384.00 07-Jan-14 Canada
4 Beans Vegetables 2,626.00 09-Jan-14 Germany
5 Orange Fruit 3,610.00 10-Jan-14 United States
6 Broccoli Vegetables 9,062.00 11-Jan-15 Australia
7 Apple Fruit 2,417.00 13-Jan-14 France
8 Apple Fruit 7,431.00 15-Jan-14 Canada
9 Banana Fruit 8,250.00 16-Jan-14 Germany
10 Broccoli Vegetables 7,012.00 18-Jan-15 United States
11 Carrots Vegetables 1,903.00 20-Jan-14 Germany
12 Broccoli Vegetables 2,824.00 22-Jan-14 Canada
13 Apple Fruit 6,946.00 24-Jan-14 France
14 Banana Fruit 2,320.00 27-Jan-14 United Kingdom
How to use:-
 Click on page layout tab
 Click on print title
 Click on row to repeat at top
 Select 1st row
 Click on ok.

Trick-12-how to print only selected data in excel.

How to use:
 Select your data
 Click on page layout tab
 Click on print area
 Click on set print area

Trick-13-How to hide selected data in excel.

How to use:-
 Select your data
 Right click on mouse or Press Ctrl+1
 Click on format cell
 Click on custom
 Remove general type three time ;;;

Trick-14- how to add blank row in any data.

A B C D E F
1 Course PIYUSH YASH SONI ANISH Helper
2 BCA 85 89 43 55 1
3 FOA 55 32 90 61 2
4 TALLY 65 82 39 58 3
pg. 67
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

5 ACE 48 91 56 72 4
6 ADV ACCT 92 84 54 64 5
7 INCOME TAX 69 63 49 12 6
1
2
3
4
5
6
How to use:-
 Select all the data
 Click on data tab
 Click on filter or press Ctrl+Shift+L
 Click on helper column filter Button
 Click on short smallest to largest

Trick-15-how to highlight blank cell if the cell is one or more.

A B C D
1 Name BCA DFA Tally
2 Piyush 85 55 65
3 Yash 85
4 Soni 43 39
5 Ansh 61 58
6 Rose 76 87 65
7 Aniket 51 72
How to use:-
 Select all the numerical data
 Press Ctrl+G
 Click on special
 Select blank
 Now select your colour form home tab

Trick-16-How to delete duplicate value.

A B C
1 Emp. Code Name Department
2 E001 Amit MKT
3 E002 Ravi Acct
4 E003 Sanjay MKT
5 E004 Rajesh MKT
6 E005 Vinod Acct
7 E006 Ramesh MKT
How to use:-
 Select the cell C2:C7
 Click on data tab
 Click on remove duplicate

pg. 68
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on continue with the current selection.

 Click on ok

Trick-17-How to highlight duplicate value

A B C D
1 Salesman State Company YTD Sales
2 Rama Rajasthan Tata 70660
3 Krishna Gujarat Reliance 92185
4 Prakash Rajasthan Bata 29796
5 Kanupriya Gujarat Tata 86452
6 Jyoti Rajasthan Reliance 85153
7 Raja Tamil Nadu Bata 28449
8 Sujoy Uttarakhand Tata 47675
How to use:-
 Select the cell B2:B8
 Click on home tab
 Click on conditional formatting
 Click on highlight cell rules
 Click on duplicate values
 Continue with the current selection
 Select your colour
 Click on ok

Trick-18-how to insert multiple sheets on your own name at one click.

A
1 Months
2 Jan
3 Feb
4 Mar
5 Apr
6 May
7 Jun
How to use: -
 select the cell A1:A7
 click on insert table
 click on pivot table
 click on existing worksheet
 click on location box
 click on anyone blank cell
 click on ok
 select month option and drag report filter box
 click on option tab/pivot table analyze
 click on options
 click on show report filter pages
 click on ok

pg. 69
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

Trick-19- how to insert background image offline and online in MS excel.

IN MS excel 2007:-
 Click on page layout tab
 Click on background image
 Select your location
 Select your image
 Click on insert

In MS excel 2013, 2016, 2019 and 2021.

 Click on page layout tab
 Click on background image
 Now search your image
 Select your image which you are required

Top 10 Most important Asked Excel Interview Question:-

1. Length of Cells
2. Vlookup
3. Left
4. Right
5. Remove Duplicate
6. Split Cell
7. Save as PDF
8. Excel Quick Charts
9. Apply Filter
10. Conditional formatting

1. How to find out Length of any Cell

A B

1 Customer Name Total Character

2 Amit Singh
3 Suraj Kumar
4 Parul Pandey
5 Deepak Sharma
6 Sachin Sharma

Formula:- =Len(A2)

2. Vlookup (King Question of MS Excel)

Lookup Value Me Us cell ko select karna hoga jese aap ko dhudna hai
Table array Me us data cell range ko select karna hoga jha se aap ko data dhudna hai
Column index Me us data cell column ka sankhya/number ko dalna hai jha se aap data value ko
No uthana chahte hai
Range Lookup Me aapko 0 likh kar ok karna hai

pg. 70
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

A B C

1 Sales Staff Product Sale

2 Pan Tripathi Mobile 65983
3 Supari Uppadhyay CUP 25659
4 Surti Devi SMPS 42569
5 Jarda Khan HDD (500GB) 41256
6 Sahad Kumari SSD(240GB) 36565
7 Kattha Mishra Keyboard 45582
8 Chuna Sharma Mouse 41256
9 Brash Jaiswal Mouse Dell 74589
10 Loung D suija Keyboard HP 78545
11 Elachai Sonkar Monitor 85698

12 Performance of Salesman
13 =Vlookup(A13,A1:C11,3,0)

3. Left Function:-
A B C
1 Customer Name Bank Account No Enter First 4 Letter
2 Pan Tripathi 202-555-0125
3 Supari Uppadhyay 202-555-0126
4 Surti Devi 202-555-0127
5 Jarda Khan 202-555-0128
6 Sahad Kumari 202-555-0129

Formula:- =Left(A2,4) Enter

4. Right Function:-

Formula:- =Right(B2,4) Enter

5. Remove Duplicate Value:-

Duplicate Value
A B
1 Customer Name Bank Account No
2 Pan Tripathi 202-555-0125
3 Supari Uppadhyay 202-555-0126
4 Surti Devi 202-555-0127
5 Jarda Khan 202-555-0128
6 Sahad Kumari 202-555-0129
7 Pan Tripathi 202-555-0130
8 Supari Uppadhyay 202-555-0131
9 Surti Devi 202-555-0132
10 Jarda Khan 202-555-0133

pg. 71
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

11 Sahad Kumari 202-555-0134

How To Use:-
 Select the cell A1:A11
 Click on data tab
 Click on remove duplicate
 Click on continue with the current selection
 Click on remove duplicate

6. Split cell: -
Split Cell
A B C D
1 Customer Name Area City Pin
2 Pan Tripathi Golghar,Gorakhpur,273001
3 Supari Uppadhyay Mohhadipur,Gorakhpur,12564
4 Surti Devi Indranagar,Gorakhpur,273002
5 Jarda Khan Taramandal,Gorakhpur,273003
6 Sahad Kumari Alinagar,Gorakhpur,273004
How to use:-
 Select the cell B2:B11
 Press shortcut key Alt+A+E
 Or click on data tab
 Click on text to column
 Click on next
 Click on comma
 Click on next
 Click on finish

7. Save as PDF
How to use:-
 First we create our matter
 Press Ctrl+S
 Click on save as type
 Select PDF and click
 Click on save option

8. Excel Quick Chart:-

Excel Quick Chart
A B C
1 Salesman Sale Target
2 Pan Tripathi 50000 100000
3 Supari Uppadhyay 35000 100000
4 Surti Devi 45000 100000
5 Jarda Khan 15000 100000
6 Sahad Kumari 75000 100000
7 Kattha Mishra 100000 100000
8 Chuna Sharma 80000 100000
9 Brash Jaiswal 32000 100000

pg. 72
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

How to use:-
 Select the cell A1:B11
 Press Alt+F1
 Now Display Your Chart
 If you display your target value in the form of graphic
 Select Cell C1:C11
 Press Ctrl+C For copy the selected cell
 Click on your Graph
 Press Ctrl+V for paste your copy data.

9. Apply Filter:-
Apply Filter
A B C D E
1 customer ID Name product location delivery status
2 A1480038 Pan Tripathi monitor Mohhadipur yes
3 A1480039 Supari Uppadhyay cd rom taramandal yes
4 A1480040 Surti Devi printer alinagar yes
5 A1480041 Jarda Khan Cd writer indranahar yes
6 A1480042 Sahad Kumari Ram baxipur No
7 A1480043 Kattha Mishra scanner rajendranagar No
8 A1480044 Chuna Sharma monitor Gorakhnath No
9 A1480045 Brash Jaiswal printer Golghar No
How to use:-
 Select all the data
 Press Ctrl+Shift+L
 For apply filter
 Now filter your data

10. Conditional Formatting: -

Apply Conditional Formatting

A B C D E F
1 student id English Hindi Math science Total
2 EDU01 85 35 78 68 266
3 EDU01 67 74 25 42 208
4 EDU03 75 12 86 21 194
5 EDU04 32 85 43 48 208
6 EDU05 84 34 67 37 222
7 EDU06 16 94 24 24 158
8 EDU07 38 62 49 95 244
9 EDU08 46 16 38 68 168

Format total grater than 200 with Green Fill.

Format total Less than 200 with Red Fill.

How to use:-
 Select the cell F2:F9
 Click on home tab

pg. 73
Advanced Excel Book 2021 Written By Piyush Mani Tripathi Sir

 Click on conditional formatting

 Click on new rule
 Click on format only cell that contain
 Click on between option
 Select greater than or equal to
 Type 200
 Click on format option
 Click on fill
 Select your color
 Click on ok two time

pg. 74