Module 2:
Formatting and basic formulas
Topics: cell formatting, borders and styles ~ copy paste, fill,handle and shortcuts
Basic formulas (sum,avg,min,maximum value)
Relative vs absolute reference
Excel formulas - functions
• a function is a prede ned formula that performs calculations using speci c
values in a particular order
• All spreadsheet programs include common functions that can be used for quickly
nd the sum,avg,count,maximum value and min value for a range of cells
The parts of functions
• a function should be written in a speci c way which is called syntax
• The basic syntax for a function is an equal sign (=) the function name (SUM,for
example) and one or more arguments
• Arguments contain the information you want to calculate
Q. What will be the function to find the total marks of student which is on the 9 th position ?
Q. Explain with an example what is relative reference
Q. How to find the highest and lowest value from the cell reference B2:F23 =MAX(B2:F23)
=MIN(B2:F23)
Answers
1. Sum
2. Relative reference means the cell reference changes automatically when you copy a
formula to another cell. If cell C1 has the formula:= A1 + B1
and you copy it to the next row (C2), the formula becomes:
= A2 + B2
fi
fi
fi
Functions
SUM Function: The SUM function adds a range of numbers. For example, you can use
it to calculate the total sales for a month by adding the daily sales gures.
• AVG Function: AVERAGE calculates the average of a range of numbers. It's useful for
determining the average test scores in a class or the mean of a dataset.
• IF Function: IF allows you to perform di erent calculations based on a condition. For
instance, you can use it to assign grades to students based on their scores.
• MIN : provides the lowest value of all numbers
• MAX: provides the highest value of all numbers
Relative reference and absolute reference
Relative reference
1
Absolute reference
They automatically adjusts when a They are denoted by a dollar sign $,
formula is copied remain constant no matter where the
formula is copied
Ex:
Copying a formula =A1+B1 from cell C1 Ex:
to cell C2 will change the formula to Copying a formula =$A$1+b1 from cell c1 to cell c2
=A2 +B2 becomes =$A$1 +b2
Also known as Locking cells
ff
Shortcut keys
Ctrl c Copy
Ctrl x Cut F1 Excel help
Ctrl v Paste Ctrl f1 Show / hide ribbon
Ctrl z Undo Alt + f8 Open macro dialog
Ctrl y Redo Alt +f11 Open VBA editor
Ctrl s Save
F12 Save as
Ctrl n New workbook
Ctrl +alt+v Paste special
Ctrl o Open workbook
Ctrl+’ Toggle formula view
Ctrl f Find data
Ctrl+9 Hide selected row
Ctrl h Replace data
Ctrl+shift+9 Unhide selected row
Ctrl a Select all
Ctrl p Print preview
Arrow keys Move selection
Ctrl+ arrow key Jump to edge of region
Shift+ arrow key Select cells stepwise
Ctrl space Select column
Shift space Select row
Ctrl+ page up/down Switch between sheets
F2
Alt + enter
Fill down Ctrl +0 zero Hide selected column
Ctrl d
Fill right Ctrl+shift+zero Unhide selected column
Ctrl r
Insert data Alt+shift+right arrow Group rows/columns
Ctrl +;
Insert time Alt+shift+left arrow Ungroup rows/columns
Ctrl+:
Ctrl+ shift +s Format as currency
Ctrl+shift+% Format as percentage
Ctrl t Create table
Alt F1 Create embedded chart
F11 Create chart on the new sheet
Ctrl +shift +L Remove(toggle) autofiller
Ctrl+ 1 Format cells dialog
Ctrl+shift+O Select cells with comments