MICROSOFT EXCELMICROSOFT EXCEL

May 13, 2005

Functions Covered

• Sort Command• Filter • If function• ‘And’ and ‘OR’ • Upper & Lower case• Subtotal• Round up• MID• Auditing• Goal Seek• NPV• PMT

• IPMT• PPMT• NPER• Converting Text file • Paste Special• Vlookup & Hlookup• Pivot Tables• Customize Toolbar• Protecting Worksheet• Break Links• Shortcut Keys

Sort Command

Function : Helps to arrange data in ascending / descending order either by date, alphabet or number.

Use : Should be used for entire selection to preserve data integrity. Can be used to sort upto 3 categories.

Fast Keys

Data selection : + + +

Sort : + D +S

Filter

Function : To select a value from drop down list or define range of values to be displayed.

Use : Can be used to filter different columns. Custom function can be used to define a range of items to be displayed.

Fast Keys

Filter : +D+F+F

IF Function

Function : Helps in evaluating data based on conditions specified.

Use : IF(A1=A2,1,0)1= True , 0=False

Advanced : IF(G5>1000,VLOOKUP(E5,Sheet1!

$A$12:$C$500,3,0),0)IF(C9<40,"unsatisfactory",IF(C9<70,"

needs improvemet",IF(C9<90,"satisfactory",IF(C9<100,"exemplary"))))

AND & OR

Function : Used with IF function to enable more complicated logical comparisons.

Use: IF(OR(SUM(C3:C8)>40,AVERAGE(C

3:C8)>10),"satisfactory","unsatisfactory")= One condition True

IF(AND(SUM(C3:C8)>40,AVERAGE(C3:C8)>10),"satisfactory","unsatisfactory")= Both conditions True

Upper & Lower Case

Function : Converts the text string in Upper or Lower case

Use:

Upper (Cell reference)

Lower (Cell reference)

Sub Total

Function : Want to add lines with subtotals in your P&L or balance sheet, but still need to run the total over all numbers? Don’t want to get confused with nested subtotals and totals in your spreadsheet?

Use: – At each change – Can be

character, value, etc– Function – Can be count, sum,

average product of the value defined in the range above.

– Add Subtotal to : Place where subtotal is to be defined.

Round up

Function : To round off the decimals.

Use: – ROUND(Number,Digits) ==> Round

the number (or cell) to the specified number of digits

• If Digit = 0, then Number is rounded to nearest integer

• If Digit > 0, then Number is rounded to the specified number of decimal places

• If Digit < 0, then Number is rounded to the specified number of digits left of the decimal place

– ROUNDDOWN(Number,Digits) and ROUNDUP(Number,Digits) work the same way as ROUND, but the direction of rounding is specified by the function

MID Function

Function : Gives the characters from middle of text based on starting position and length

Use: – Text :Cell reference where

function is to be applied– Start number : Position of

number which is to be extracted based on count of numbers

– Num chars : Numbers of characters from the text to be extracted

– Eg : MID(B1,3,4)

Auditing

Function : Quickly find the cells referenced by a formula and/or quickly find which cells reference a particular cell of interest.

Use: – Select View : Toolbars : Customize from the menu bar.

Check the Auditing box from the Toolbars tab– Click on the cell of interest– Select the Trace Precedents or Trace Dependents icon from

the Auditing Toolbar

Goal Seek

Function : Easily find what one input variable needs to be to achieve some desired result in a calculation

Use: – Select the calculated cell– Select Tools : Goal Seek from the menu bar– Enter the desired resulting calculation into the “To Value”

form in the dialog that appears– Enter the input cell in the “By changing cell:” form

NPV Function

Function : Helps in calculation of NPV without working out the discounted table.

Use: – Rate: The rate of discount.– Value 1 to __ : Cash flow

values for period 1 to n

PMT Function

Function : Returns the EMI of loan amount based on monthly payment

Use: – Rate: The Interest rate on

loan.– Nper : Period of loan– PV : Amount of loan– FV : Value if any after

payment of loan– Type : Whether installment

is in advance or arrears.

IPMT Function

Function : Returns the Interest payment for given period of investment based on periodic constant payment & Interest rate.

Use: – Rate: The Interest rate on loan.– Period for which interest payment

is to be determined.– Nper : Period of loan– PV : Amount of loan– FV : Value if any after payment of

loan– Type : Whether installment is in

advance or arrears.

PPMT Function

Function : Returns the Principal payment for given period of investment based on periodic constant payment & Interest rate.

Use: – Rate: The Interest rate on loan.– Period for which interest payment

is to be determined.– Nper : Period of loan– PV : Amount of loan– FV : Value if any after payment of

loan– Type : Whether installment is in

advance or arrears.

NPER Function

Function : Returns the Period for an investment based on periodic constant payment & Interest rate.

Use: – Rate: The Interest rate on loan.– Pmt : Payment to be made

each period– PV : Amount of loan– FV : Value if any after payment

of loan– Type : Whether installment is

in advance or arrears.

Converting Text File

Function : To convert a text file into excel

Use : System Generated text files.

Open>file location

Paste Special

Function : To paste either values, format, formula or comments of copied cell

Use : Right click mouse or Edit>paste special

VlookupFunctionAllows to automatically lookup a particular cell of data from a larger

data range. This is especially useful when • A large data section that contains information for multiple

records somewhere on the spreadsheet (e.g., a small database)• A calculation area somewhere else, and you need to refer to

some specific data elements for specific records

Use• To look up data pertaining to a particular value.• Parameters to be defined for look up

• Value pertaining to which data is to be found• Table array within which data lookup is to be defined• Column pertaining to value where the probable lookup value is

present• Match to be exact or nearest possible

Vlookup (Cont.)

VLOOKUP(A1,Instrument_Issued! $A$1:$C$120,3,0)

Look up Value = A1

Table Array = Instrument_Isued!$A$1:$C$120

Col_index_num = 3

Range = 0 (False)

Hlookup : To look up the values horizantlly, i.e. vale lying in the row.

Vlookup (Cont.)

Vlook up when there are more than one values in table array pertaining to the look up value.

A additional field should be added to look up value like date to make the look up value unique.

VLOOKUP(A1&B1,Instrument_Issued! $A$1:$C$120,3,0)

Pivot TableFunction: Most powerful tool

to arrange huge amounts of data in a more structured way than pure sorting. PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

Use: – Select the data range for

which pivot is required.– Select Data>pivot table– Step 1 of 3 - Click Next

Pivot Table (Cont.)

Use: – Click Next (Step 2 of 3)– Data range is defined here. The

same can be changed by clicking browse

– Step 3 of 3 – Location of the pivot table needs to be specified whether in new worksheet o existing worksheet

– In case of existing worksheet cell reference from where pivot table should be made is to be specified.

– Layout of the chart and options should be defined.

Pivot Table (Cont.)

Use : Layout – Page: A field that's assigned to a

page orientation in a PivotTable. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items. More than one page fields can be defined.

– Row: A field that's assigned a row orientation in a PivotTable. Items associated with a row field are displayed as row labels.

– Column: A field that's assigned a column orientation in a PivotTable. Items associated with a column field are displayed as column labels.

– Data: A field from a source list, table, or database that contains data that is to be summarized in a PivotTable. A data field usually contains numeric data, such as statistics or sales amounts.

Customize ToolbarFunction : To customize the toolbar

for the functions used frequently.

Use :• View>Toolbars>customize• Click on command menu• Drag items on & off toolbars

OR• Right click toolbar area

• Select Customize• Select Commands tab in

Customize dialog box• From appropriate menu, find

the command for which you want to add button

• Drag button to location on toolbar

Customize Toolbar

Some Favorite shortcuts :

Select Visible

Show All

Auto filter

Merge Cells

Freeze Panes

Paste Values

Save As

Break Links

Break Links : Vlookup function in worksheet or other links between worksheets increases the size of file due to links and calculation. Subsequent calculation in same sheet becomes time consuming.

To break links select : Edit>links>Break link>break link

OR

+E+K+B+B

Protecting WorksheetFunction : Protecting a spreadsheet or

workbook involves two steps• Designating which cells to be locked or

hidden• Protecting the spreadsheet or workbook

– Please Note :• The default for all cells in a spreadsheet if

LOCKED. So if you want the receiver of your worksheet to change the content of a cell, unlock the cell before protecting the spreadsheet

• The formulas in a cell can be seen even if the spreadsheet is lock -- UNLESS you hide that cell before protecting the spreadsheet

Use :– To lock/unlock and hide/unhide a cell, select

the cell(s) and select Format : Cell. Select the Protection tab when the dialog box appears

– To protect/unprotect a spreadsheet, select Tools : Protection : Protect Sheet

• Ctrl + Shift + ~• Ctrl + Shift + $• Ctrl + Shift + %• Ctrl + Shift + !• Ctrl + Shift + &• Ctrl + Shift + _• Ctrl + b• Ctrl + i• Ctrl + u• Ctrl + 9• Ctrl + Shift + 9• Ctrl + 0• Ctrl + Shift + 0• Ctrl + 1• Ctrl + 5• Shift + Space• Ctrl + Space

– General Num. Format– Currency format– Percentage format– Comma format– Outline border– Remove borders– Bold– Italic– Underline– Hide rows– Unhide rows– Hide columns– Unhide columns– Format Dialog Box– Strike Through– Select the entire row– Select the entire column

Keyboard ShortcutsFormatting Keys

• Ctrl + a• Ctrl + x/c/v• Ctrl + d/r• CTRL+SHIFT+*

• SHIFT+ arrow key• CTRL+SHIFT+ arrow key

• SHIFT+HOME• CTRL+SHIFT+HOME

• CTRL+SHIFT+END

– Select the entire worksheet– Cut/copy/paste– Fills cells down/right– Select the current region around the

active cell (the current region is an area enclosed by blank rows and blank columns)

– Extend the selection by one cell– Extend the selection to the last

nonblank cell in the same column or row as the active cell

– Extend the selection to the beginning of the row

– Extend the selection to the beginning of the worksheet

– Extend the selection to the last cell used on the worksheet (lower-right corner)

Keyboard Shortcuts (cont.)Formatting Keys

• Ctrl + F4• Alt + F4• Ctrl + F10• Ctrl + F9• Ctrl + F5• F6• Shift + F6• Ctrl + F6• Ctrl + Tab• Shift + F11• F11• Ctrl + s• F12• Ctrl + o• Ctrl + n• Alt + F8• Alt + F11• Alt + down arrow key

– Closes workbook window– Closes Excel– Maximizes the workbook– Minimizes the workbook– Restore window size– Next pane– Previous pane– Next window– Next window– Inserts a new sheet– Create a Quick Chart Sheet– Saves the workbook– Saves As– Opens a workbook– Creates a new workbook– Macros Dialog Box– Visual Basic Editor– Activate the drop down list

Keyboard Shortcuts (cont.)Windows & Workbook Keys

• ALT + TAB• CTRL + TAB• CTRL + Page Up/Page Down• CTRL + Home/End• CTRL + arrow key

• Ctrl + [

• Ctrl + Shift + {

• Ctrl + ]

• Ctrl + Shift + }

• F2

– Switch between applications– Switch between open Excel files– Go to previous/next worksheet– Go to the first/last cell of the

worksheet– Go to the next empty cell

– Selects cells directly referred to by formulas (Precedent Cells)

– Selects directly and indirectly referred to cells

– Selects only cells with formulas that refer directly to the active cell (Dependent Cells)

– Selects all cells within formulas that directly or indirectly refer to the active cells

– Toggle cell edit mode

Keyboard Shortcuts (cont.)Windows & Workbook

Keys

Auditing

• SHIFT+BACKSPACE

• SHIFT+PAGE DOWN

• SHIFT+PAGE UP

• CTRL+SHIFT+SPACEBAR

• CTRL+6

• CTRL+7

– If multiple cells are selected, select only the active cell

– Extend the selection down one screen

– Extend the selection up one screen

– With an object selected, select all objects on a sheet

– Alternate between hiding objects, displaying objects, and displaying placeholders for objects

– Show or hide the Standard toolbar

Keyboard Shortcuts (cont.)Auditing & Calculation

Keys

THANK YOU