Add: WZ-873, 1st Floor, Rani Bagh, Near Corporation Bank Delhi-34

Ph: 01149126480, 9599807380, 9599807480

E-mail : ici.com4592@Gmail.com WebSite : www.indiancomputer.co.in

NIRMAL KUMAR
(Director)

“WANT TO BE SHINNING STAR IN IT & SOFTWARE INDUSTRY THEN BE A PART

OF INDIAN COMPUTER INSTITUTE AND SEE UR CARREER GROWING”
UNIT-1-MS EXCEL BASICS

Definition:
Microsoft Excel is a commercial spreadsheet application Software, written and distributed by
Microsoft. It is a tool capable of performing calculations, analyzing data and integrating information
from different programs.
By default, documents saved in Excel 2010 are saved with the .xlsx extension whereas the file
extension of the prior Excel versions was .xlsx.

IMPORTANT TERMS

 WORKBOOK
A workbook is another name for your file in Excel. It is used to store and work with data. Each
workbook contains one of more workbooks.
 WORKSHEET
A workbook is a collection of cells where you keep and manipulate the data. Each Excel
workbook can contain multiple worksheets.
 CELL
A cell is a rectangular box that occurs at the intersection of a vertical column and a horizontal
row in a workbook. You can store data in a cell such as a formula, text value, numeric value, or
date value.
 ACTIVE CELL
An active cell refers to a cell in Excel workbook that is currently selected by clicking mouse
pointer or keyboard keys. Remember only one cell can be an active cell at a time. An active
cell is bounded by a heavy border around it.

GETTING STARTED
Assuming you have Microsoft Office 2010 installed in your PC, start the excel application
following the below mentioned steps in your PC.

Step 1 − Click on the Start button.

Step 2 − Click on All Programs option from the menu.

step 3 − Search for Microsoft Office from the sub menu and click it.

Step 4 − Search for Microsoft Excel 2010 from the submenu and click it.
 This will launch the Microsoft Excel 2010 application and you will see the following excel
window.

Explore Window in Excel 2010

The following basic window appears when you start the excel application. Let us now understand
the various important parts of this window.
 File Tab
The File tab replaces the Office button from Excel 2007. You can click it to check the backstage
view, where you come when you need to open or save files, create new workbooks, print a
workbook, and do other file-related operations.
First column of the backstage view will have the following options −

S.No. Option & Description

1 Save
If an existing workbook is opened, it would be saved as is,
otherwise it will display a dialogue box asking for the workbook
name.

2 Save As
A dialogue box will be displayed asking for workbook name and
workbook type. By default, it will save in workbook 2010 format
 with extension .xlsx.

3 Open
This option is used to open an existing excel workbook.

4 Close
This option is used to close an opened workbook.

5 Info
This option displays the information about the opened workbook.

6 Recent
This option lists down all the recently opened workbooks.

7 New
This option is used to open a new workbook.

8 Print
This option is used to print an opened workbook.

9 Save & Send

This option saves an opened workbook and displays options to
send the workbook using email etc.

10 Help
You can use this option to get the required help about excel 2010.

11 Options
Use this option to set various option related to excel 2010.

12 Exit
Use this option to close the workbook and exit.
File Tab:-
It is simple to exit from the Backstage view of file tab. Either click on the File tab or press the
Esc button on the keyboard to go back to excel working mode.
 Quick Access Toolbar
You will find this toolbar just above the File tab and its purpose is to provide a convenient
resting place for the Excel's most frequently used commands. You can customize this toolbar
based on your comfort.
 Title Bar
This lies in the middle and at the top of the window. Title bar shows the program and the
workbook titles.

 Ribbon Ribbon contains commands organized in three components –

Tabs − they appear across the top of the Ribbon and contain groups of related commands.
Home, Insert, Page Layout is the examples of ribbon tabs.

Groups − They organize related commands; each group name appears below the group on
the Ribbon. For example, group of commands related to fonts or group of commands related
to alignment etc.
Commands − Commands appear within each group as mentioned above.
Help:-The Help Icon can be used to get excel related help anytime you like. This provides nice
tutorial on various subjects related to excel.
 Zoom Control
Zoom control lets you zoom in for a closer look at your text. The zoom control consists of a
slider that you can slide left or right to zoom in or out. The + buttons can be clicked to
increase or decrease the zoom factor.
 View Buttons
The groups of three buttons located to the left of the Zoom control, near the bottom of the
screen, lets you switch among excel various workbook views.
Normal Layout view − This displays the page in normal view.
Page Layout view − This displays pages exactly as they will appear when printed. This gives
a full screen look of the document.
Page Break view − This shows a preview of where pages will break when printed.
 Workbook Area
The area where you enter data. The flashing vertical bar is called the insertion point and it
represents the location where text will appear when you type.
 Row Bar
Rows are numbered from 1 onwards and keep on increasing as you keep entering data.
Maximum limit is 10, 48,576 rows.
 Column Bar
Columns are numbered from A onwards and keeps on increasing as you keep entering data.
After Z, it will start the series of AA, AB and so on. Maximum limit is 16,384 columns.
 Status Bar
This displays the workbook information as well as the insertion point location. From left to
right, this bar can contain the total number of pages and words in the document, language
etc.
You can configure the status bar by right-clicking anywhere on it and by selecting or
deselecting options from the provided list.
 Dialog Box Launcher
This appears as a very small arrow in the lower-right corner of many groups on the Ribbon.
Clicking this button opens a dialog box or task pane that provides more options about the
group.

3. Entering Data
A new workbook is displayed by default when you open an excel workbook as shown in the
below screen shot.

Workbook area is the place where you type your text. So, just keep your mouse cursor at the
text insertion point and start typing whatever text you would like to type.
We have typed only two words "Hello Excel" as shown below. The text appears to the left of
the insertion point as you type.
 There are following three important points, which would help you while typing −

 Press Tab to go to next column.

 Press Enter to go to next row.
 Press Alt + Enter to enter a new line in the same column.

 DATA ENTRY LIMIT OF EXCEL WORKBOOK

 In MS Excel, there are 1048576*16384 cells.
 MS Excel cell can have Text, Numeric value or formulas.
 An MS Excel cell can have maximum of 32000 characters.

Inserting Formula
For inserting formula in MS Excel go to the formula bar, enter the formula and then press
enter. See the screen-shot below to understand it.

Modifying Cell Content

For modifying the cell content just activate the cell, enter a new value and then press enter or
navigation key to see the changes. See the screen-shot below to understand it.
Deleting Cell Content
MS Excel provides various ways of deleting data in the worksheet. Let us see those ways.
Delete with Mouse
Select the data you want to delete. Right Click on the worksheet. Select the delete option, to
delete the data.

 Delete with Delete Key

Select the data you want to delete. Press on the Delete Button from the keyboard, it will delete
the data.
Moving Around In A Worksheet
 Moving with Keyboard
The following keyboard commands, used for moving around your worksheet, also move the
insertion point −

Keystroke Where the Insertion Point Moves

Forward one box

Back one box

Up one box

Down one box

PageUp To the previous screen

PageDown To the next screen

Home To the beginning of the current screen

End To the end of the current screen

Ctrl + To the last box containing data of the current

row./To the last cell of the worksheet of current row

Ctrl + To the first box containing data of the current

row./ To the first cell of the worksheet of current row

To the first box containing data of the current

Ctrl + column./ To the first box of the current column of the
worksheets.

To the last box containing data of the current

Ctrl + column./ To the last box of the current column of the
WORKSHEET.

Ctrl + PageUp To the worksheet in the left of the current

worksheet.

Ctrl + PageDown To the worksheet in the right of the current

worksheet.

Ctrl + Home To the beginning of the worksheet.

Ctrl + End To the end of the worksheet.

 Moving with Go To Command

Press F5 key to use Go To command, which will display a dialogue box where you will find
various options to reach to a particular box.
Normally, we use row and column number, for example K5 and finally press go to button.

Saving a Workbook
 Saving new file/workbook
Once you are done with typing in your new excel workbook, it is time to save your workbook
to avoid losing work you have done on an Excel workbook. Following are the steps to save an
edited excel workbook −
Step 1 − Click the File tab and select Save As option.

Step 2 − Select a folder where you would like to save the workbook, Enter file
name, which you want to give to your workbook and click on save.

Step 3 − Finally, click on Save button and your workbook will be saved with the entered name
in the selected folder.
 Saving Changes
There may be a situation when you open an existing workbook and edit it partially or
completely, or even you would like to save the changes in between editing of the workbook. If
you want to save this workbook with the same name, then you can use either of the following
simple options −
 Just press Ctrl + S keys to save the changes.
 Optionally, you can click on the floppy icon available at the top left corner and just above
the File tab. This option will also save the changes.
 You can also use third method to save the changes, which is the Save option available just
above the Save As option as shown in the above screen capture.
Inserting New Worksheet
Three new blank workbooks always open when you start Microsoft Excel. Below steps explain
you how to create a new Worksheet.
 You can use a short cut to create a blank WORKSHEET anytime. Try using
the Shift+F11 keys and you will see a new blank WORKSHEET
OR
 Click on ‘Insert worksheet’ tab near the WORKSHEET tabs .
 Copy Worksheet:-
Here are the steps to copy an entire workbook.

Step 1 − Right Click the WORKSHEET Name and select the Move or Copy option.

Step 2 − Now you'll see the Move or Copy dialog with select worksheet option as selected from the
general tab. Click the Ok button.

Select Create a Copy Checkbox to create a copy of the current worksheet and Press the Ok Button.
Now you should have your copied worksheet as shown below.
Now you can rename the worksheet also by double clicking on its tab.
Ranges in MS Excel
A group of cells is called a range. You designate a range address by specifying its upper-left cell
address and its lower-right cell address, separated by a colon.
Example of Ranges −
A1:B1 − Two cells, that occupy one row and two columns.
Selecting Ranges
You can select a range in several ways −
 Press the left mouse button and drag, highlighting the range. Then release the mouse
button. If you drag to the end of the screen, the worksheet will scroll.
 Press the Shift key while you use the navigation keys to select a range.

Selecting Complete Rows and Columns

When you need to select an entire row or column. You can select entire rows and columns in
much the same manner as you select ranges −
 Click the row or column heading to select a single row or column.
 To select multiple adjacent rows or columns, click a row or column border and drag to
highlight additional rows or columns.
 Spell Check:-
MS Excel provides a feature of Word Processing program called Spelling check. We can get rid
of the spelling mistakes with the help of spelling check feature.
Let us see how to access the spell check.
 To access the spell checker, Choose Review ➪Spelling or press F7.

Exploring Options
Let us see the various options available in spell check dialogue.
 Ignore Once − Ignores the word and continues the spell check.
 Ignore All − Ignores the word and all subsequent occurrences of it.
 Add to Dictionary − Adds the word to the dictionary.
 Change − Changes the word to the selected word in the Suggestions list.
 Change All − Changes the word to the selected word in the Suggestions list and changes all
subsequent occurrences of it without asking.
 AutoCorrect − Adds the misspelled word and its correct spelling (which you select from the
list) to the AutoCorrect list.

Special Symbols and Characters

If you want to insert some symbols or special characters that are not found on the keyboard in that
case you need to use the Symbols option.
Using Symbols
Go to Insert » Symbols » Symbol to view available symbols. You can see many symbols
available there like Pi, alpha, beta, etc.
Select the symbol you want to add and click insert to use the symbol
Using Special Characters
Go to Insert » Symbols » Special Characters to view the available special characters. You can see
many special characters available there like Copyright, Registered etc.

Select the special character you want to add and click insert, to use the special character.

Hiding worksheet
Here is the step to hide a worksheet.
Step − Right Click the Worksheet Name and select the Hide option. Worksheet will get hidden.

Unhiding Worksheet
Here are the steps to unhide a worksheet.
Step 1 − Right Click on any Worksheet Name and select the Unhide... option.

Step 2 − Select Worksheet Name to unhide in Unhide dialog to unhide the worksheet.
Press the Ok Button.
Now you will have your hidden worksheet back.
 UNIT-2 FORMATTING OF WORKSHEET
There are many types of formatting that can be applied to Microsoft Excel worksheets. The most
commonly used formatting commands show up on the HOME tab in three groups:

1. The Font Group. The font group commands change the appearance of text within a cell or of
the cell itself.
It Bold, Italicize and Underline the Text
To bold text in Microsoft Excel:

1. Select the cell or cells in which you wish to bold the text.
2. On the HOME tab, in the Font group, click the Bold command.

To italicize text in Microsoft Excel:

1. Select the cell or cells in which you wish to italicize the text.
2. On the HOME tab, in the Font group, click the Italic command.

To underline text in Microsoft Excel:

1. Select the cell or cells in which you wish to underline the text.
2. On the HOME tab, in the Font group, click the Underline command.

 Add Borders to Cells

To add borders to cells in Microsoft Excel:

1. Select the cell or cells to which you wish to add borders.

2. On the HOME tab, in the Font group, click the arrow to the right of the Borders command.

3. Select the type of border you wish to add from the drop down menu:
  Change Text and Cell Colors
To change the color of text in cells in Microsoft Excel:

1. Select the cell or cells in which you wish to change the color of the text.
2. On the HOME tab, in the Font group, click the arrow to the right of the Font Color command

3. Select a color from the drop down menu:

To change the fill (i.e., background) color of cells in Microsoft Excel:

1. Select the cell or cells in which you wish to change the fill color.
2. On the HOME tab, in the Font group, click the arrow to the right of the Fill Color command.

3. Select a color from the drop down menu:

 Set Font and Font Size

To change the font of text or numbers in cells in Microsoft Excel:

1. Select the cell or cells in which you wish to change the font.
2. On the HOME tab, in the Font group, click the arrow to the right of the Font command.
3. Select a font from the drop down menu:
To change the size of the font of text or numbers in cells in Microsoft Excel:

1. Select the cell or cells in which you wish to change the font size.
2. On the HOME tab, in the Font group, click the arrow to the right of the Font Size command.

3. Select a font size from the drop down menu:

 Tab Colors and Themes

1. Right-click the Sheet1 tab and select Tab Color.

2. From the Tab Color list, select a color.

3. Right-click the Sheet2 tabs and select Tab Color, and then select a color from the list.
4. From the PAGE LAYOUT tab, in the Themes group, select a theme to apply it to your worksheet.
5. Save the worksheet.

2. The Alignment Group. The alignment group commands change the position of text within
a cell or cells.
Text within cells in Microsoft Excel can be aligned both vertically (top, center and bottom) and
horizontally (left, center and right).
To align text vertically within a cell or cells in Microsoft Excel:

1. Select the cell or cells in which you wish to align the text.
2. On the HOME tab, in the Alignment group, click either the Top Align, Middle
Align or Bottom Align command:

To align text horizontally within a cell or cells in Microsoft Excel:

1. Select the cell or cells in which you wish to align the text.
2. On the HOME tab, in the Alignment group, click either the Align Text Left, Center or Align Text

3. Right command:
 Wrap Text
By default, text in Microsoft Excel remains on one line. Wrapping text is a way of getting text to
show up on multiple lines within a cell. See the following example:

To wrap text within a cell or cells in Microsoft Excel:

1. Select the cell or cells in which you wish to wrap the text.
2. On the HOME tab, in the Alignment group, click Wrap Text:

 Indent Text
Indenting text is a way of showing that one item is a sub-item of another, as demonstrated here:
Instead of indenting sub-headings with spaces, you can do so using the Increase Indent command,
which makes it a lot easier to keep all indented text equally indented.

To indent text within a cell or cells in Microsoft Excel:

1. Select the cell or cells in which you wish to indent text.

2. On the HOME tab, in the Alignment group, click the Increase Indent command:

3. You can click Increase Indent as many times as you want to achieve the indentation you desire.
4. To decrease an indent, simply click the Decrease Indent command:

 Merge & Center Text

Often a label applies to multiple columns. In these cases, it is useful to merge cells to show this. In
the following example, the years "2012" and "2013" each apply to four columns:

To merge cells in Microsoft Excel:

1. Select the cells you wish to merge.

2. On the HOME tab, in the Alignment group, click the Merge & Center command:

3.The Number Group.

The number group commands change the format of numbers and dates within a cell.
By default, numbers in Microsoft Excel do not show commas and do show the first two decimals
(unless they are 0). However, numbers can be formatted to appear in many different ways.

 Number Formats
Numbers in Excel can be formatted to show commas, show currency symbols, appear as
percentages, and more.
To display numbers with a thousand separator (a comma) in Microsoft Excel:

1. Select the cells for which you wish to display numbers with a thousands separator.
2. On the HOME tab, in the Number group, click the Comma Style command:

To display numbers with a currency symbol in Microsoft Excel:

1. Select the cells for which you wish to display a currency symbol.
2. On the HOME tab, in the Number group, click the Accounting Number Format command
(or the drop-down arrow to select a currency symbol other than the one displayed):

To display numbers as percentages in Microsoft Excel:

1. Select the cells for which you wish to display numbers as percentages.
2. On the HOME tab, in the Number group, click the Percent Style command:

Date Formats
Microsoft Excel actually stores dates as numbers, so displaying dates is really a formatting issue.
Excel allows you to display dates in many ways. For example, January 15, 2013 can be displayed
include:

1. 01/15/2013
2. 01/15/13
3. 1/15/13
4. 1/15
5. 15-Jan
6. 15-Jan-13
7. Jan-13
8. January-13
9. January 15, 2013
10. Saturday, January 15, 2013
To set or change the way dates are displayed in Microsoft Excel:

1. Select the cell or cells for which you wish to change the way dates are displayed.
2. On the HOME tab, in the Number group, click the Dialog Box Launcher:
3. In the Format Cells dialog box, in the Number tab, select Date in the Category box and
choose a format from the Type box:

4. Click OK.

 Showing Decimals
To change the number of decimals showing for numbers in Microsoft Excel:

1. Select the cell or cells for which you wish to change the number of decimals showing for
numbers.
2. On the HOME tab, in the Number group, click the Increase Decimal or the Decrease
Decimal command:
 UNIT-3-WORKING WITH FORMULAS AND FUNCTIONS (BASIC/GENERAL)

Formulas are the MAIN PART of worksheet. Without formula, worksheet will be just simple tabular
representation of data.
A formula consists of special code, which is entered into a cell. It performs some calculations and
returns a result, which is displayed in the cell.
Formulas use a variety of operators and worksheet functions to work with values and text. The
values and text used in formulas can be located in other cells, which makes changing data easy .

Elements of Formulas
A formula can be consist of any of these elements −
a.Mathematical operators, such as + (for addition) and *(for multiplication)
Example −
o =A1+A2 Adds the values in cells A1 and A2.
Values or text
Example −
o =200*0.5 Multiplies 200 times 0.15. This formula uses only values, and it always
returns the same result as 100.
Worksheet functions (such as SUM )
For Example −
=SUM (A1:A12) Adds the values in the range A1:A12.

Creating Formula:-
For creating a formula you need to type in the Formula Bar. Formula begins with '=' sign.
When building formulas manually, you can either type in the cell addresses or you can point to
them in the worksheet.
As soon as you complete a formula entry, Excel calculates the result, which is then displayed
inside the cell within the worksheet.
See the below screen shot.
 If you make an error in the formula that prevents Excel from being able to calculate the
formula at all, Excel displays an Alert dialog box suggesting how to fix the problem.
Copying Formulas in MS Excel
Copying formulas is one of the most common tasks that you do in a typical spread workbook.
Let us see this with the help of example. Suppose we want the sum of all the rows at last,
then we will write a formula for first column i.e. B. We want sum of the rows from 3 to 8 in
the 9throw.

After writing formula in the 9th row, we can drag it to remaining columns and the formula
gets copied. After dragging we can see the formula in the remaining columns as below.
 column C : =SUM(C3:C8)
 column D : =SUM(D3:D8)
 column E : =SUM(E3:E8)

 column F : =SUM(F3:F8)
 column G : =SUM(G3:G8)
4. Cell References in Formulas
Most formulas you create include references to cells or ranges.
When you use a cell (or range) reference in a formula, you can use three types of references
− relative, absolute, and mixed references.
 Relative Cell References
The row and column references can change when you copy the formula to another cell
By default, Excel creates relative cell references in formulas.
 Absolute Cell References
The row and column references do not change when you copy the formula because the
reference is to an actual cell address. An absolute reference uses two dollar signs in its
address: one for the column letter and one for the row number (for example, $A$5).

 Mixed Cell References

Both the row or column reference is relative and the other is absolute. Only one of the
address parts is absolute (for example, $A5 or A$5).
 5. Functions
Many formulas you create use available worksheet functions. These functions enable you to
greatly enhance the power of your formulas and perform calculations that are difficult if you
use only the operators
 Using Functions
When you type = sign and then type any alphabet you will see the searched functions as
below.

For example:
=max (h3:h8)
The above formula will result in the maximum value out from cell h3 to h8.
Function Arguments
All the functions use parentheses. The information inside the parentheses is the list of
arguments.
Functions vary in how they use arguments. Depending on what it has to do, a function may
use.
 No arguments − Now (), Date(), etc.
 One argument − UPPER(), LOWER(), etc.
 A fixed number of arguments − IF(), MAX(), MIN(), AVERGAGE(), etc.
 Built In Functions
MS Excel has many built in functions, which we can use in our formula. To see all the functions
by category, choose Formulas Tab » Insert Function. Then Insert function Dialog appears
from which we can choose the function.
 Functions by Categories
Let us see some of the built in functions in MS Excel.
TEXT FUNCTIONS
Lower − Converts All Characters In A Supplied Text String To Lower Case
Upper − Converts All Characters In A Supplied Text String To Upper Case
Trim − Removes Duplicate Spaces, And Spaces At The Start And End Of A Text String
Concatenate − Joins Together Two or More Text Strings.
Left − Returns A Specified Number Of Characters From The Start Of A Supplied Text String.
Mid − Returns A Specified Number Of Characters From The Middle Of A Supplied Text String
Right − Returns A Specified Number Of Characters From The End Of A Supplied Text String.
Len − Returns The Length Of A Supplied Text String
Find − Returns the position of a supplied character or text string from within a supplied text
string (case-sensitive).
Date & Time Functions
Date − Returns A Date, From A User-Supplied Year, Month And Day.
Time − Returns A Time, From A User-Supplied Hour, Minute and Second.
Now − Returns the Current Date & Time.
Today − Returns Today's Date.
STATISTICAL
Max − Returns The Largest Value From A List Of Supplied Numbers.
Min − Returns The Smallest Value From A List Of Supplied Numbers.
Average − Returns The Average Of A List Of Supplied Numbers.
Count − Returns The Number Of Numerical Values In A Supplied Set Of Cells Or Values.
Countif − Returns The Number Of Cells (Of A Supplied Range), That Satisfies A Given Criteria.
Sum − Returns The Sum Of A Supplied List Of Numbers
LOGICAL
And − Tests A Number Of User-Defined Conditions And Returns True If All Of The Conditions
Evaluate To True, Or False Otherwise
Or − Tests A Number Of User-Defined Conditions And Returns True If Any Of The Conditions
Evaluate To True, Or False Otherwise.
Not − Returns A Logical Value That Is The Opposite Of A User Supplied Logical Value Or
Expression I.E. Returns False If The Supplied Argument Is True And Returns True If The Supplied
Argument Is Fal
MATH & TRIG
Abs − Returns The Absolute Value (I.E. The Modulus) Of A Supplied Number.
Sqrt − Returns The Positive Square Root Of A Given Number.
Mod − Returns The Remainder From A Division Between Two Supplied Numbers.

UNIT-4- ADVANCE FEATURES AND FUNCTIONS OF MS-EXCEL

Following is the list of some of the important advance functions/features of Excel.
a) PMT
b) FV
c) PPMT/IPMT
d) Annuity
e) DB
f) SLN
g) HLOOKUP
h) VLOOKUP
i) INDEX
j) INDEX+MATCH
k) SUMIF
l) COUNTIF
m) GOAL SEEK
n) SUBTOTAL
o) CONDITIONAL FORMATTING
p) SEARCH BOX CREATION USING CONDITIONAL FORMATTING
q) DATA VALIDATION
r) DATA FILTER
s) DATA SORTING
t) DATA TABLES
u) PIVOT TABLES
v) CHARTS
Frequency Distribution
Let us discuss all in detail:
PMT:-
The Excel PMT function is a financial function that returns the periodic payment for a loan given the
loan amount, number of periods, and interest rate.

Syntax
=PMT (rate, nper, pv, [fv], [type])
Arguments
 Rate - The interest rate for the loan.
  Nper - The total number of payments for the loan.
 PV - the present value or total value of all loan payments now.
 fv - [optional] The future value, or a cash balance you want after the last payment is made.
 ults to 0 (zero).
 Type - [optional] When payments are due. 0 = end of period. 1 = beginning of period.
Default is 0.
.
PPMT and IPMT
Consider a loan with an annual interest rate of 5%, 2-year duration and a present value (amount
borrowed) of $20,000.
1. The PMT function below calculates the monthly payment.

Note: we make monthly payments, so we use 5%/12 for Rate and 2*12 for Per (total number of
periods).
2. The PPMT function in Excel calculates the principal part of the payment. The second argument
specifies the payment number.

Explanation: the PPMT function above calculates the principal part of the 5th payment.
3. The IPMT function in Excel calculates the interest part of the payment. The second argument
specifies the payment number.

Explanation: the IPMT function above calculates the interest part of the 5th payment.
4. It takes 24 months to pay off this loan. Create a loan amortization schedule (see picture below)
to clearly see how the principal part increases and the interest part decreases with each payment.
 Note: the principal part and the interest part always add up to the payment amount.

Annuity:-

Assume you want to purchase an annuity that will pay $600 a month, for the next 20 years. At an
annual interest rate of 6%, how much does the annuity cost?

1. Insert the PV (Present Value) function.

2. Enter the arguments.

You need a one-time payment of $83,748.46 (negative) to pay this annuity. You'll receive 240 *
$600 (positive) = $144,000 in the future. This is another example that money grows over time.

Note: we receive monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Per. The
last two arguments are optional. If omitted, Fv = 0 (no future value). If Type is omitted, it is
assumed that payments are due at the end of the period. This annuity does not take into account life
expectancy, inflation etc.
FV (future value)
Summary
The future value (FV) function calculates the future value of an investment assuming periodic,
constant payments with a constant interest rate.
Syntax
=FV (rate, nper, pmt, [PV], [type])
Arguments
 Rate - The interest rate per period.
 Nper - The total number of payment periods.
 Pmt - The payment made each period. Must be entered as a negative number.
 PV - [optional] the present value of future payments. If omitted, assumed to be zero. Must
be entered as a negative number.
 Type - [optional] when payments are due. 0 = end of period, 1 = beginning of period. Default
is 0.

DB:-
The DB function is a Financial function. The function helps in calculating the depreciation of an
asset. The method used for calculating depreciation is the Fixed Declining Balance Method for each
period of the asset’s lifetime.
syntax
=DB(cost, salvage, life, period, [month])
Arguments:

1. Cost – It is the initial cost of the asset.

2. Salvage – It is the value of the asset at the end of the depreciation.
3. Life – It is the useful life of the asset or the number of periods for which we will be
depreciating the asset.
4. Period – It is the period for which we wish to calculate the depreciation for.
5. Month (optional argument) – It specifies how many months of the year are used in the
calculation of the first period of depreciation. If omitted, the function will take the default value
of 12.
Example: Assuming we wish to calculate the depreciation for an asset with an initial cost of
$100,000. The asset’s salvage value after 5 years is $10,000.
We get the result below:
The result we got for five years is below:
 SLN:-
SLN is an Excel function that calculates the depreciation expense to be charged on an asset
under the straight-line depreciation method. As the name suggest, straight-line method charges
depreciation equally over the useful life of the asset.

Syntax
SLN (cost, salvage, life)
Arguments
a Cost represents the dollar amount of initial capitalized cost of the asset being depreciated.
b Salvage The value that can be realized from the asset after it is completely depreciated.
c Life is the total number of periods for which we expect to use the asset.

Example: The following chart illustrates use of SLN function for an asset with a cost of $30
million, salvage value (also called scrap value) of $4.5 million and useful life of 8 years.

Please note that life is entered in years because we are calculating depreciation expense
for yearly period.
Vlookup:-
VLOOKUP is an Excel function to look up and retrieve data from a specific column in table. The "V"
stands for "vertical". Lookup values must appear in the first column of the table, with lookup
columns to the right.
Syntax
=VLOOKUP (value, table, col_index, [range_lookup])
Arguments
  Value - The value to look for in the first column of a table.
 Table - The table from which to retrieve a value.
 Col index - The column in the table from which to retrieve a value.
 Range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

If you have data organized horizontally, use the HLOOKUP function.

VLOOKUP only looks right
VLOOKUP requires a lookup table with lookup values in the left-most column. The data you want to
retrieve (result values) can appear in any column to the right:
Hlookup:-

HLOOKUP is an Excel function to look up and retrieve data from a specific row in table. The "H" in
HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table,
moving horizontally to the right. HLOOKUP supports approximate and exact matching, and
wildcards (*?) for finding partial matches.
Syntax
=HLOOKUP (value, table, row_index, [range_lookup])

Arguments

 Value - The value to look up.

 Table - The table from which to retrieve data.
 Row index - The row number from which to retrieve data.
 Range_lookup - [optional] Aboolean to indicate exact match or approximate match. Default
= TRUE = approximate match.
Index:-
The Excel INDEX function returns the value at a given position in a range or array. You can use
index to retrieve individual values or entire rows and columns. INDEX is often used with the
MATCH function, where MATCH locates and feeds a position to INDEX.
Syntax
=INDEX (array, col_num, [col_num], [area_num])
Arguments
 Array - A range of cells, or an array constant.
 col_num - The row position in the reference or array.
 col_num - The column position in the reference or array.
 area_num - [optional] the range in reference that should be used.

For example, the formula

=INDEX (A1:B5, 2, 2) will return the value at the address B2
=INDEX(datatable,MATCH(value,lookup_column,FALSE),column)
Explanation
This example shows how to use INDEX and MATCH to get information from a table
based on an exact match.
In the example shown, the formula in cell H6 is:
=INDEX(B5:E9,MATCH(H4,B5:B9,FALSE),2)
Which returns 1995, the year the movie Toy Story was released?

How this formula works

This formula a uses MATCH to get the row position of Toy Story in the table, and INDEX
to retrieve the value at that row in column 2.
MATCH is configured to look for the value in H4 in column B:
=INDEX(B5:E9,4,2)
INDEX then retrieves the value at the intersection of the 4th row and 2nd column in the
array, which is "1995".
Sumif:-
The powerful SUMIF function in Excel sums cells based on one criteria.
Syntax
=SUMIF (range, criteria, [sum range])

Arguments:

 Range Required. The range of cells that you want evaluated by criteria. Cells in
each range must be numbers or names, arrays, or references that contain numbers
  Criteria Required. The criteria in the form of a number, expression, a cell
reference, text, or a function that defines which cells will be added.

For example, criteria can be expressed as 32, ">32", B5, "32", "apples".

Important: Any text criteria or any criteria that includes logical or mathematical
symbols must be enclosed in double quotation marks ("). If the criteria is numeric,
double quotation marks are not required.

 Sum range Optional. The actual cells to add, if you want to add cells other than
those specified in the range argument.

If the sum range argument is omitted, Excel adds the cells that are specified in
the range argument (the same cells to which the criteria are applied).

example:
SUMIF function below (three arguments, last argument is the range to sum) sums
values in the range B1:B5 if the corresponding cells in the range A1:A5 contain the
value 25.

Sum ifs
To sum cells based on multiple criteria (for example, circle and red), use the following SUMIFS
Function (first argument is the range to sum).
 General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average
cells based on one or multiple criteria.

Count if:-
Excel Countif function is used for counting cells within a specified range that meet a certain
criterion, or condition.
For example, you can write a COUNTIF formula to find out how many cells in your worksheet
contain a number greater than or less than the number you specify.
Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting
with a particular letter(s).
The syntax of the COUNTIF function is very simple:
Countif (range, criteria)
As you see, there are only 2 arguments, both of which are required:
 Range - defines one or several cells to count. You put the range in a formula like you usually
do in Excel, e.g. A1:A20.
 Criteria - defines the condition that tells the function which cells to count. It can be
a number, text string, cell reference or expression. For instance, you can use the criteria like
these: "10", A2, ">=10", "some text".
And here is the simplest example of Excel COUNTIF function. What you see in the image below
is the list of the best tennis players for the last 14 years. The formula =COUNTIF(C2:C15,"Roger
Federer") counts how many times Roger Federer's name is on the list:

Note. A criterion is case insensitive, meaning that if you type "roger Federer" as the criteria in
the above formula, this will produce the same result.

Excel COUNTIF function examples

As you have just seen, the syntax of the COUNTIF function is very simple. However, it allows for
many possible variations of the criteria, including wildcard characters, the values of other cells,
and even other Excel functions. This diversity makes the COUNTIF function really powerful and
fit for many tasks, as you will see in the examples that follow.
Instead of typing text, you can also use a reference to any cell containing that word or words
and get absolutely the same results, e.g. =COUNTIF (C1:C9, C7).
Similarly, COUNTIF formulas work for numbers. As shown in the screenshot below, the
formula =COUNTIF(D2:D9,5) perfectly counts cells with quantity 5 in Column D.

 COUNTIF formulas with wildcard characters (partial match)

In case your Excel data include several variations of the keyword(s) you want to count, then you
can use a wildcard character to count all the cells containing a certain word, phrase or letters
as part of the cell's contents.
Suppose, you have a list of tasks assigned to different persons, and you want to know the
number of tasks assigned to Danny Brown. Because Danny's name is written in several different
ways, we enter "*Brown*" as the search criteria =COUNTIF(D2:D10, "*Brown*").

more examples:

=COUNTIF (C2:C10,"Mr*") - count cells that begin with "Mr.".

=COUNTIF (C2:C10,"*ed") - count cells that end with the letters "ed".
The image below demonstrates the second formula in action:

Excel COUNTIF for blank and non-blank cells

These formula examples demonstrate how you can use the COUNTIF function in Excel to count the
number of empty or non-empty cells in a specified range.

COUNTIF not blank

In some of other Excel COUNTIF tutorials, you may come across formulas for counting non-blank
cells in Excel similar to this one:
=COUNTIF (range,"*")

But the fact is, the above formula counts only cells containing any text values, meaning that cells
with dates and numbers will be treated as blank cells and not included in the count!
If you need a universal COUNTIF formula for counting all non-blank cells in a specified range, here
you go:
=COUNTIF (range,"<>"&"")

This formula works correctly with all value types - text, dates and numbers - as you can see in the
screenshot below.
COUNTIF blank
COUNTIF formula for blanks (all value types):
=COUNTIF (range,"")

The above formula correctly handles numbers, dates and text values. For example, the
formula =COUNTIF (C2:C11,"") returns the number of all empty cells in the range C2:C11.
Note. Please be aware that Microsoft Excel provides another function for counting blank cells
=COUNTBLANK(range). For instance, the below formulas will produce exactly the same results as
the COUNTIF formulas you see in the screenshot above:
Count blanks: =COUNTBLANK (C2:C11)

COUNTIF greater than, less than or equal to

To count cells with values greater than, less than or equal to the number you specify, you simply add
a corresponding operator to the criteria, as shown in the table below.
Please pay attention that in COUNTIF formulas, an operator with a number are always enclosed in
quotes.

Criteria Formula Example Description

Count if greater than =COUNTIF(A2:A10,">5") Count cells where value is greater than 5.

Count if less than =COUNTIF(A2:A10,"<5") Count cells with values less than 5.

Count if equal to =COUNTIF(A2:A10,"=5") Count cells where value is equal to 5.

Count if not equal to =COUNTIF(A2:A10,"<>5") Count cells where value is not equal to 5.

Count if greater than or =COUNTIF(C2:C8,">=5") Count cells where value is greater than or
equal to equal to 5.

Count if less than or equal =COUNTIF(C2:C8,"<=5") Count cells where value is less than or
to equal to 5.

Using Excel COUNTIF function with dates

If you want to count cells with dates that are greater than, less than or equal to the date you specify
or date in another cell, you proceed in the already familiar way using formulas similar to the ones
we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me
give you just a few examples:
Apart from these common usages, you can utilize the COUNTIF function in conjunction with specific
Excel Date and Time functions such as TODAY() to count cells based on the current date.

Criteria Formula Example Description

Count dates equal to =COUNTIF(B2:B10,"6/1/2014") Counts the number of cells in the range
the specified date. B2:B10 with the date 1-Jun-2014.
Goal Seek:-
Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, Goal
Seek determines input values needed to achieve a specific goal.

Step-by-step with an example

Let's look at the preceding example, step-by-step.

Let you want to calculate the loan interest rate needed to meet your goal, you use the PMT function.
The PMT function calculates a monthly payment amount. In this example, the monthly payment
amount is the goal that you seek.

Prepare the worksheet

1. Open a new, blank worksheet.

2. First, add some labels in the first column to make it easier to read the worksheet.
a. In cell A1, type Loan Amount.
b. In cell A2, type Term in Months.
c. In cell A3, type Interest Rate.
d. In cell A4, type Payment.
3. Next, add the values that you know.
a. In cell B1, type 100000. This is the amount that you want to borrow.
b. In cell B2, type 180. This is the number of months that you want to pay off the loan.

Note: Although you know the payment amount that you want, you do not enter it as a value,

because the payment amount is a result of the formula. Instead, you add the formula to the
worksheet and specify the payment value at a later step, when you use Goal Seek.

4. Next, add the formula for which you have a goal. For the example, use the PMT function:
a. In cell B4, type =PMT (B3/12, B2, B1).

The formula refers to cells B1 and B2, which contain values that you specified in preceding steps.
The formula also refers to cell B3, which is where you will specify that Goal Seek put the
interest rate. The formula divides the value in B3 by 12 because you specified a monthly
payment, and the PMT function assumes an annual interest rate.

Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the
example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
2. In the Set cell box, enter the reference for the cell that contains the formula that you want to
resolve. In the example, this reference is cell B4.
3. In the To value box, type the formula result that you want. In the example, this is -900. Note
that this number is negative because it represents a payment.
4. In the By changing cell box, enter the reference for the cell that contains the value that you
want to adjust. In the example, this reference is cell B3.

Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you
specified in the Set cell box.

5. Click OK

Goal Seek runs and produces a result, as shown in the following illustration.

6. Finally, format the target cell (B3) so that it displays the result as a percentage.
a. On the Home tab, in the Number group, click Percentage.
Subtotal:-
a. Subtotal function in Excel ignores rows hidden by a filter or manually hidden rows.
. For example, the SUM function below calculates the total sales.

2. Apply a filter.

The SUM function includes rows hidden by a filter.

 But the SUBTOTAL function ignores rows hidden by a filter and calculates the correct result.
Note: 109 is the argument for Sum if you use the SUBTOTAL function.
Subtotal through Menu:-

Creating subtotals

The Subtotal command allows you to automatically create groups and use common functions like
SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command
could help to calculate the cost of office supplies by type from a large inventory order. It will create
a hierarchy of groups, known as an outline, to help organize your worksheet.
Your data must be correctly sorted before using the Subtotal command, so you may want to review
our lesson on Sorting Data to learn more.

To create a subtotal:

In our example, we will use the Subtotal command with a T-shirt order form to determine how
many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create
an outline for our worksheet with a group for each T-shirt size and then count the total number of
shirts in each group.
First, sort your worksheet by the data you want to subtotal. In this example, we will
create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size
from smallest to largest.

1. Select the Data tab, then click the Subtotal command.

2. The Subtotal dialog box will appear. Click the drop-down arrow for the At each
change in: field to select the column you want to subtotal. In our example, we'll
select T-Shirt Size.
3. Click the drop-down arrow for the Use function: field to select the function you want
to use. In our example, we'll select COUNT to count the number of shirts ordered in
each size.
4. In the Add subtotal to: field, select the column where you want the calculated
subtotal to appear. In our example, we'll select T-Shirt Size.
5. When you're satisfied with your selections, click OK.

6. The worksheet will be outlined into groups, and the subtotal will be listed below
each group.
Conditional Formatting:-
Conditional formatting quickly highlights important information in a spreadsheet.
Step 1: Apply Highlight Rules To Your Excel Spreadsheet
Highlight rules apply color formatting to cells that meet specific criteria that you define. They are the
most basic type of conditional formatting rule, and Excel provides a variety of preset highlight
functions.
1. Open an existing spreadsheet in Excel, or start from scratch and manually enter new data. In this
example, we’re using an inventory list that tracks the number of each item currently in stock, as well
as some additional information about each product.
2. To apply highlight rules, select the range of values you want to apply a rule to. For this example,
we want to highlight any product that’s quantity is less than 100 units. So, select the values in
the Qty. column (C4:C26).
3. From the Home tab, click Conditional Formatting on the right side of the toolbar, and
click Highlight Cells Rules from the dropdown menu. Click Less than.

4. A box will appear. Type 100 in the empty field. Click OK.

5. Your spreadsheet will now reflect this highlight rule, with the quantities less than 100 highlighted
red with red text.

Tip: You can change the color of the highlighted cell by clicking on the Format with: dropdown menu
and selecting on another option.
6. To highlight text cells, repeat step 2. This time, we want to highlight certain model types (M
compatible), so we’ll select the cells in the Item name column.
7. Click Conditional Formatting > Highlight Cells Rules > Text that Contains…

8. Type M compatible in the text box. To differentiate from our previous highlight rule, select green
fill with dark green text from the Format with: dropdown menu. Click OK.

9. Now, cells containing the text M compatible are highlighted.

10. Using these same steps and menu options, you can apply highlight rules to find Duplicate
Values, Dates, or values that are Greater than…, Equal to…, or between… values that you select.
All of these possibilities are available through the menu options.
Step 2: Create Top/Bottom Rules
Top/Bottom rules are another useful preset in Excel. These rules allow you to call attention to the
top or bottom range of cells, which you can specify by number, percentage, or average.
1. In this example, we’ll highlight the bottom five total stock values. Click the top of the Total value of
stock column to select these cells.
2. Click Conditional Formatting > Top/Bottom Rules > Bottom 10 Items… (The default number and
percent in Top/Bottom rules in Excel is 10, but you can change that number in the New Formatting
Rule box.)

3. In the box that appears, change 10 to 5, since we only want the bottom five values. Since we
already have a red fill highlight rule, click the dropdown menu and select yellow fill with dark yellow
text. Click OK.

4. Your sheet will now highlight the bottom five values in the Total value of stock column, and update
as you add to your data set.
Step 3: Apply Data Bars
Data bars apply a visual bar within each cell. The length of the bar relates the value of the cell to other cell
values in the selected range.
1. We’ll apply data bars to the Qty. column so we can easily assess the ratios of items in stock. Click the
top of the Qty. column to select this range of cells.
2. Click Conditional Formatting > Data Bars. You’ll see two options - one for Gradient Fill and one
for Solid Fill. They function identically; just select the option and color you prefer.
3. Your sheet will now reflect the added rule.

Step 4: Apply Color Scales

Color scales are similar to data bars in that they relate a cell’s values in a selected range. However,
instead of representing this relationship by the length of a bar, color scales do so with color gradients.
One color is assigned the “lowest” value and another the “highest,” with a range of colors in between.
1. We’ll apply color scales to our Selling price column. Click the top of column D to select this range.
2. Click Conditional Formatting > Color Scales. You’ll see a variety of different color ranges; select the
one you want.

3. Your spreadsheet now shows the selling prices by color - red cells are the most expensive, and green
cells are the least expensive.
Step 5: Apply Icon Sets
Icon sets apply colorful icons to data. They are simply another way to call attention to important data, and
relate cells to one another.
1. We’ll apply icon sets to the Purchase price column to show low, middle, and high priced items. Click
the top of the Purchase price column to select the range of values.
2. Click Conditional Formatting > Icon Sets. You’ll see a variety of options
for Directional, Shapes, Indicators, and Ratings icons. You can choose any of these to fit the needs of
your data. In this example, we’ll choose the first Directional option: red, yellow, and green arrows
that indicate high, middle, or low priced items.
3. Your sheet reflects this new formatting rule.

Search box:-

Here are the steps to search and highlight all the cells that have the matching text:
1. Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this
example).

2. Click the Home tab.

3. In the Styles group, click on Conditional Formatting.
4. In the drop-down options, click on New Rule.

5. In the ‘New Formatting Rule’ dialog box, click on the option ‘Use a formula to determine
6. Which cells to format’?

7. Enter the following formula: =A4=$B$1

8. Click on ‘Format..’ button.
9. Specify the formatting (to highlight cells that match the searched keyword).

10. Click OK.

Now type anything in cell B1 and press enter. It will highlight the matching cells in the dataset
that contain the keyword in B1.
Search and Highlight Rows with Matching Data
If you want to highlight the entire row instead of just the matching cells, you can do that by
tweaking the formula a little.
Below is an example where the entire row gets highlighted if the product type matches the one in
cell B1.

Here are the steps to search and highlight the entire row:
1. Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this
example).
2. Click the Home tab.
3. In the Styles group, click on Conditional Formatting.
4. In the drop-down options, click on New Rule.
5. In the ‘New Formatting Rule’ dialog box, click on the option ‘Use a formula to determine
which cells to format’.
 6. Enter the following formula: =$B4=$B$1

7. Click on ‘Format..’ button.

8. Specify the formatting (to highlight cells that match the searched keyword).
9. Click OK.

The above steps would search for the specified item in the dataset, and if it finds the matching
item, it will highlight the entire row.
Note that this will only check for the item column. If you enter a Sales Rep name here, it will not
work. If you want it to work for Sales Rep name, you need to change the formula to =$C4=$B$1

Data Validation:-
MS Excel data validation feature allows you to set up certain rules that dictate what can be entered
into a cell.
For example, you may want to limit data entry in a particular cell to whole numbers between 0 and
10. If the user makes an invalid entry, you can display a custom message as shown below.

 Validation Criteria
To specify the type of data allowable in a cell or range, follow the steps below, which shows all
the three tabs of the Data Validation dialog box.
 Select the cell or range.
 Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box
having 3 tabs settings, Input Message and Error alert.
 Settings Tab
Here you can set the type of validation you need. Choose an option from the Allow drop-down
list. The contents of the Data Validation dialog box will change, displaying controls based on
your choice.
 Any Value − Selecting this option removes any existing data validation.
 Whole Number − The user must enter a whole number only.
For example, you can specify that the entry must be a whole number greater than or equal to
50.
 Decimal − The user must enter a number.
For example, you can specify that the entry must be greater than or equal to 10 and less than
or equal to 20.
 List − the user must choose from a list of entries you provide. You have to give input ranges
and those values will appear in the drop-down list manner.
 Date − The user must enter a date.
For example, you can specify that the entered data must be greater than or equal to January 1,
2013, and less than or equal to December 31, 2013.
 Time − The user must enter a time.
For example, you can specify that the entered data must be later than 12:00 p.m.
 Text Length − The length of the data (number of characters) is limited.
For example, you can specify that the length of the entered data be 1 to 10.
 Custom − To use this option, you must supply a logical formula that determines the validity
of the user’s entry (a logical formula returns either TRUE or FALSE).
  Input Message Tab
You can set the input help message with this tab. Fill the title and Input message of the
Input message tab and the input message will appear when the cell is selected.
 Error Alert Tab
You can specify an error message with this tab. Fill the title and error message. Select
the style of the error as stop, warning or Information as per you need.

Data filter
 Single
Filtering data in MS Excel refers to displaying only the rows that meet certain conditions. (The
other rows get hidden.)
Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set
filter to do this. Follow the below mentioned steps to do this.
 Place a cursor on the Header Row.
 Choose Data Tab » Filter to set filter.

 Click the drop-down arrow in the Area Row Header and remove the check mark from Select
All, which unselects everything.
 Then select the check mark for Size 36 which will filter the data and displays data of Shoe
Size 36.
 Multiple
You can filter the records by multiple conditions i.e. by multiple column values. Suppose after
size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter
for Shoe Size, choose Color column and then set filter for color.
Advance filter:-
Excel's Advanced Filter is really helpful when it comes to finding data that meets two or more
complex criteria.
To create an advanced filter for your sheet, perform the following steps.

1. Organize the source data

For better results, arrange your data set following these 2 simple rules:
 Add a header row where each column has a unique heading - duplicate headings will cause
confusion to Advanced Filter.
 Make sure there are no blank rows within your data set.

For example,

here'how our sample table looks like:

2. Set up the criteria range
Type your conditions in a separate range on the worksheet. In practice, it's more convenient to
place it at the top and separate from the data set with one or more blank rows.
Advanced criteria notes:
  The criteria range must have the same column headings as the table / range that you want
to filter.
 Criteria listed on the same row work with the AND logic. Criteria entered on different rows
work with the OR logic.
For example, to filter records for the North region whose Sub-total is greater than or equal to
900, set up the following criteria range:
 Region: North
 Sub-total: >=900

3. Apply Excel Advanced Filter

 Select any single cell within your dataset.
 In Excel 2010 and Excel 2007, go to the Data tab > Sort & Filter group and click Advanced.

The Excel Advanced Filter dialog box will appear and you set it up as explained below.

4. Configure the Advanced Filter parameters

In the Excel Advanced Filter dialog window, specify the following parameters:
 Action. Choose whether to filter the list in place or copy the results to another location.

Selecting "Filter the list in place" will hide the rows that don't match your criteria.
If you choose "Copy the results to another location", select the upper-left cell of the range where
you want to paste the filtered rows. Make sure the destination range has no data anywhere in the
columns because all cells below the copied range will be cleared.
 List range. It's the range of cells to be filtered, the column headings should be included.

 Criteria range. It's the range of cells in which you input the criteria.
 In addition, the check box in the lower-left corner of the Advanced Filter dialog window lets you
display unique records only
In this example, we are filtering the list in place, so configure the Excel Advanced Filter
parameters in this way:

Finally, click OK, and you will get the following result:

Sorting data:-
Sorting data in MS Excel rearranges the rows based on the contents of a particular column.
To Sort the data follow the steps mentioned below.

 Select the Column by which you want to sort data.

 Choose Data Tab » Sort Below dialog appears.
  If you want to sort data based on a selected column, Choose Continue with the
selection or if you want sorting based on other columns, choose Expand Selection.
 You can Sort based on the below Conditions.
 Values − Alphabetically or numerically.->(mainly used)
 Cell Color − Based on Color of Cell.
 Font Color − Based on Font color.
 Cell Icon − Based on Cell Icon.

 Clicking Ok will sort the data.

Sorting option is also available from the Home Tab. Choose Home Tab » Sort & Filter. You can
see the same dialog to sort records.
Data Tables:-
In Excel, a Data Table is a way to see different results by altering an input cell in your formula.
Data tables are available in Data Tab » What-If analysis dropdown » Data table in MS Excel.
Data Table with Example
Now, let us see data table concept with an example. Suppose you have the Price and quantity of
many values. Also, you have the discount for that as third variable for calculating the Net Price.
You can keep the Net Price value in the organized table format with the help of the data table. Your
Price runs horizontally to the right while quantity runs vertically down. We are using a formula to
calculate the Net Price as Price multiplied by Quantity minus total discount (Quantity * Discount for
each quantity).

Now, for creation of data table select the range of data table. Choose Data Tab » What-If analysis
dropdown » Data table. It will give you dialogue asking for Input row and Input Column. Give the
Input row as Price cell (In this case cell B3) and Input column as quantity cell (In this case cell B4).
Please see the below screen-shot.
 Clicking OK will generate data table.
MIS Report:
A management information system is an information system used for decision-making, and for the
coordination, control, analysis, and visualization of information of information in an organization
and marketing.

.
Pivot Tables:-
A pivot table is essentially a dynamic summary report generated from a database..
A pivot table can help transform endless rows and columns of numbers into a
meaningful presentation of the data.
Pivot tables are very powerful tool for summarized analysis of the data.
Pivot tables are available under Insert tab » PivotTable dropdown » PivotTable.
Pivot Table Example
Now, let us see Pivot table with the help of example.
Suppose you have huge data of voters and you want to see the summarized data of
voter Information per party, then you can use the Pivot table for it.
Choose Insert tab » Pivot Table to insert pivot table. MS Excel selects the data of the
table. You can select the pivot table location as existing workbook or new workbook.

This will generate the Pivot table pane as shown below. You have various options available in
the Pivot table pane. You can select fields for the generated pivot table.

 Column labels − A field that has a column orientation in the pivot table. Each item in the
field occupies a column.
 Report Filter − You can set the filter for the report as year, then data gets filtered as per the
year.
  Row labels − A field that has a row orientation in the pivot table. Each item in the field
occupies a row.
 Values area − The cells in a pivot table that contain the summary data. Excel offers several
ways to summarize the data (sum, average, count, and so on).
After giving input fields to the pivot table, it generates the pivot table with the data as shown
below.

Charts:-
A chart is a visual representation of numeric values. Charts (also known as graphs) have been an
integral part of spread workbooks. Charts generated by early spread workbook products were
quite crude, but thy have improved significantly over the years. Excel provides you with the tools to
create a wide variety of highly customizable charts. Displaying data in a well-conceived chart can
make your numbers more understandable. Because a chart presents a picture, charts are
particularly useful for summarizing a series of numbers and their interrelationships.
Types of Charts

There are various chart types available in MS Excel as shown in the below screen-shot.

 Column − Column chart shows data changes over a period of time or illustrates comparisons
among items.
 Bar − A bar chart illustrates comparisons among individual items.
  Pie − A pie chart shows the size of items that make up a data series, proportional to the sum
of the items. It always shows only one data series and is useful when you want to emphasize
a significant element in the data.
 Line − A line chart shows trends in data at equal intervals.
 Area − An area chart emphasizes the magnitude of change over time.
 X Y Scatter − An xy (scatter) chart shows the relationships among the numeric values in
several data series, or plots two groups of numbers as one series of xy coordinates.
 Stock − This chart type is most often used for stock price data, but can also be used for
scientific data (for example, to indicate temperature changes).
 Surface − A surface chart is useful when you want to find the optimum combinations
between two sets of data. As in a topographic map, colors and patterns indicate areas that
are in the same range of values.
 Doughnut − Like a pie chart, a doughnut chart shows the relationship of parts to a whole;
however, it can contain more than one data series.
 Bubble − Data that is arranged in columns on a worksheet, so that x values are listed in the
first column and corresponding y values and bubble size values are listed in adjacent
columns, can be plotted in a bubble chart.
 Radar − A radar chart compares the aggregate values of a number of data series.
 Creating Chart
To create charts for the data by below mentioned steps.
 Select the data for which you want to create the chart.
 Choose Insert Tab » Select the chart or click on the Chart group to see various chart
types.
 Select the chart of your choice and click OK to generate the chart.

Editing Chart:-
You can edit the chart at any time after you have created it.
You can select the different data for chart input with Right click on chart » Select data. Selecting
new data will generate the chart as per the new data, as shown in the below screen- shot.

You can change the X axis of the chart by giving different inputs to X-axis of chart.
 You can change the Y axis of chart by giving different inputs to Y-axis of chart.
 Frequency Distribution:-
Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the
Analysis Toolbar to create a histogram
Remember, our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.

First, insert a pivot table. Next, drag the following fields to the different areas.
1. Amount field to the Rows area.
2. Amount field (or any other field) to the Values area.
3. Click any cell inside the Sum of Amount column.
4. Right click and click on Value Field Settings.

5. Choose Count and click OK.

6. Next, click any cell inside the column with Row Labels.
7. Right click and click on Group.

8. Enter 1 for Starting at, 10000 for Ending at, and 1000 for By.
9. Click OK.

Result:
To easily compare these numbers, create a pivot chart.
10. Click any cell inside the pivot table.
11. On the Analyze tab, in the Tools group, click PivotChart.

The Insert Chart dialog box appears.

12. Click OK.
Result:

Histogram
This example teaches you how to create a histogram in Excel.
1. First, enter the bin numbers (upper levels) in the range C3:C7.
2. On the Data tab, in the Analysis group, click Data Analysis.

Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in.
3. Select Histogram and click OK.

4. Select the range A2:A19.

5. Click in the Bin Range box and select the range C3:C7.
6. Click the Output Range option button, click in the Output Range box and select cell F3.
7. Check Chart Output.
8. Click OK.

9. Click the legend on the right side and press Delete.

10. Properly label your bins.
11. To remove the space between the bars, right click a bar, click Format Data Series and change the
Gap Width to 0%.
12. To add borders, right click a bar, click Format Data Series, click the Fill & Line icon, click Border
and select a color.
Result:
Analysis ToolPak
The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial,
statistical and engineering data analysis.
To load the Analysis ToolPak add-in, execute the following steps.
1. On the File tab, click Options.
MS Excel offers many keyboard short-cuts. Below is the list of all the major shortcut keys in
Microsoft Excel.
 Ctrl + A − Selects all contents of the worksheet.
 Ctrl + B − Bold highlighted selection.
 Ctrl + I − Italicizes the highlighted selection.
 Ctrl + K − Inserts link.
 Ctrl + U − Underlines the highlighted selection.
 Ctrl + 1 − Changes the format of selected cells.
 Ctrl + 5 − Strikethrough the highlighted selection.
 Ctrl + P − Brings up the print dialog box to begin printing.
 Ctrl + Z − Undo last action.
 Ctrl + F3 − Opens Excel Name Manager.
 Ctrl + F9 − Minimizes the current window.
 Ctrl + F10 − Maximize currently selected window.
 Ctrl + F6 − Switches between open workbooks or windows.
 Ctrl + Page up − Moves between Excel work workbooks in the same Excel document.
 Ctrl + Page down − Moves between Excel work workbooks in the same Excel document.
 Ctrl + Tab − Moves between Two or more open Excel files.
 Alt + = − Creates a formula to sum all of the above cells
 Ctrl + ' − Inserts the value of the above cell into cell currently selected.
  Ctrl + Shift + ! − Formats the number in comma format.
 Ctrl + Shift + $ − Formats the number in currency format.
 Ctrl + Shift + # − Formats the number in date format.
 Ctrl + Shift + % − Formats the number in percentage format.
 Ctrl + Shift + ^ − Formats the number in scientific format.
 Ctrl + Shift + @ − Formats the number in custom format.
 Ctrl + Arrow key − Moves to the next section of text.
 Ctrl + Space − Selects the entire column.
 Shift + Space − Selects the entire row.
 Ctrl + - − Deletes the selected column or row or cell.
 Ctrl + Shift + = − Inserts a new column or row or cell.
 Ctrl + Home − Moves to cell A1.
 Ctrl + ~ − Switches between showing Excel formulas or their values in cells.
 F2 − Edits the selected cell.
 F3 − after a name has been created F3 will paste names.
 F4 − Repeat last action.
For example, if you changed the color of text in another cell pressing F4 will change the text in
cell to the same color.
 F5 − Goes to a specific cell. For example, C6.
 F7 − Spell checks the selected text or document.
 F11 − Creates chart from the selected data.
 Ctrl + Shift + ; − Enters the current time.
 Ctrl + ; − Enters the current date.
 Alt + Shift + F1 − Inserts New Worksheet.
 Alt + Enter − While typing text in a cell pressing Alt + Enter will move to the next line
allowing for multiple lines of text in one cell.
 Shift + F3 − Opens the Excel formula window.
 Shift + F5 − Brings up the search box.
 Alt+F8 − Start recording in macro
Individual Data Lock:-
This Feature Is Used For Apply Password Entire Sheet And Individual Sheet.
 Then Go To Home Tab- Select All Sheets –Cells Group- Format Cells- Protection- Tick Unlock- Ok.

Then Select Individual Data And Go To Format Command- Format Cell- Protection- Tick Lock.
After Tick Lock Go To Home Tab- Cells Group- Format Command- Protect Sheet- Apply Password And Ok.
Adv. Filter:-
This Feature Is Used For Search Individuals Data.
Process: Go to - Data Tab- Short and Filter Group- Advanced- And Select Total Data

Select First Copied Rows- Select Second Copied Row.

Vlookup in Multiple Sheets:-
This formula is used for search individual data in multiple sheets
Process 1:- select all data and create data name “data” then go to next sheet and apply Vlookup formula
 Formula and Search Individual Data.
Apply Sumifs:-
This Formula Is Used For Search Conditioned Data.
Process: - Create Data- And Apply
=Sumif (sum _range, criteria_range1, criteria_range2, criteria2)

Adv. Filter with Macro:-

This feature is used for search individual updated data.
Process: create data then Go to –macro- record macro- feed name-ok then go to
Data Tab- Short and Filter Group- Advanced- And Select Total Data.
 Then go to insert and choose shapes and type refresh in shapes and assign macro.

RD Calculation:-
This feature is used for calculate RD Amount this formula is very useful for
financial works.
Process:- Make a data
1. Months: 1 to 24
2. Opening balance
3. Principle amount: 1000
4. Interest: =principle*10%/12 enter.
5. Principle with interest: =principle+ interest
6. Interest: = (opening balance +principle)*10%/12 enter
7. Principle with interest: =opening balance principle amount+intrest
enter