MS EXCEL - Complex Formulas
A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.
The order of operations
Excel calculates formulas based on the following order of operations:
Operations enclosed in parentheses
Exponential calculations (3^2, for example)
Multiplication and division, whichever comes first
Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally.
*NOTE: with Multiplication and Division, whichever operation comes first must be executed, also with Addition and Subtraction.
Creating complex formulas
In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.
Excel follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. It then multiplies that value by the tax rate: $84.75*0.075. The result will show that the sales tax is $6.36.
Credits: Excel 2013
MS EXCEL - Freeze Pane
MS EXCEL - Freeze Pane
Used to keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes.
You may want to see certain rows or columns all the time in your worksheet, especially header cells.
By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.
1. Select the row below the row(s) you want to freeze.
2. Click the View tab on the Ribbon.
3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.
The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. Repeat the same for column freezing.
To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.
Used to keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes.
You may want to see certain rows or columns all the time in your worksheet, especially header cells.
By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.
1. Select the row below the row(s) you want to freeze.
2. Click the View tab on the Ribbon.
3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.
The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. Repeat the same for column freezing.
To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.
MS EXCEL - Formatting in Excel
MS EXCEL - Formatting in Excel
Formatting is available under Home Tab in Excel
Format Painter - Used to Copy formatting from one Cell/Range to other. Double click this
option to apply multiple time
Font Formatting - It consists of Font Name, Size and Bold/Italics/Underline option
Border Formatting - It allows you to modify cell borders
Text Formatting - It has various alignment option and Wrap Text option
Number Formatting - It gives various options of formatting Number like Currency,
Percentage, Decimals etc.
Table Formatting - Format table data, various styles available to choose from.
Cell Formatting (Cell Styles) - It allows to adjust height, width, insert and delete options
You can do formatting manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. A style is a mixture of formatting that you can apply over and over, like paint.
Formatting is available under Home Tab in Excel
Format Painter - Used to Copy formatting from one Cell/Range to other. Double click this
option to apply multiple time
Font Formatting - It consists of Font Name, Size and Bold/Italics/Underline option
Border Formatting - It allows you to modify cell borders
Text Formatting - It has various alignment option and Wrap Text option
Number Formatting - It gives various options of formatting Number like Currency,
Percentage, Decimals etc.
Table Formatting - Format table data, various styles available to choose from.
Cell Formatting (Cell Styles) - It allows to adjust height, width, insert and delete options
You can do formatting manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. A style is a mixture of formatting that you can apply over and over, like paint.
MS EXCEL - Sheet Protection, Share workbook, Workbook Protection
MS EXCEL - Sheet Protection, Share workbook, Workbook Protection
I. Worksheet Level Protection and Locking/Unlocking of Cell
1. SELECT THE CELL AND THEN RIGHT CLICK
2. CHOOSE Format Cells -> Protection
NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.
In Menu bar go to Review - Protect Sheet -> Give Password and select/unselect the various access for user
II. Workbook Level Protection
You can prevent a workbook from having its structure and windows modified or resized by another user.
Structure
Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.
Windows
Prevents the user from being able to resize or move the window.
III. File Level Protection
To password protect Excel file - Save as - Tools - General Options and can give password here to open and edit.
OPTIONAL: If you would like Excel to recommend that this file be opened as a read-only file each time it is opened, select Read-only recommended
HINT: Read-only files can be modified, but the changes cannot be saved without creating a new file.
If you no longer need to password-protect the file, you can remove the password by going to save As -> Tools -> and delete the current password, save it and replace old file.
I. Worksheet Level Protection and Locking/Unlocking of Cell
1. SELECT THE CELL AND THEN RIGHT CLICK
2. CHOOSE Format Cells -> Protection
NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.
In Menu bar go to Review - Protect Sheet -> Give Password and select/unselect the various access for user
II. Workbook Level Protection
You can prevent a workbook from having its structure and windows modified or resized by another user.
Structure
Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.
Windows
Prevents the user from being able to resize or move the window.
III. File Level Protection
To password protect Excel file - Save as - Tools - General Options and can give password here to open and edit.
OPTIONAL: If you would like Excel to recommend that this file be opened as a read-only file each time it is opened, select Read-only recommended
HINT: Read-only files can be modified, but the changes cannot be saved without creating a new file.
If you no longer need to password-protect the file, you can remove the password by going to save As -> Tools -> and delete the current password, save it and replace old file.
MS EXCEL - Text to Column
Text to Column
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert
Text to Columns Wizard’.
For example - when you want to separate a list of full names into last and first
names.
1. Select the range with full names.
2. On the Data tab, click Text to Columns.
The following dialog box appears.
3. Choose Delimited and click Next.
4. Clear all the check boxes under Delimiters except for the Comma and Space check box.
5. Click Finish.
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert
Text to Columns Wizard’.
For example - when you want to separate a list of full names into last and first
names.
1. Select the range with full names.
2. On the Data tab, click Text to Columns.
The following dialog box appears.
3. Choose Delimited and click Next.
4. Clear all the check boxes under Delimiters except for the Comma and Space check box.
5. Click Finish.
MS EXCEL - Validation
Data Validation - It allows you to define validation on cells for entering value.
Common Validations are - List, Date, Time, Text Length etc.
List Validation - It will create a drop down containing a range or defined values
Select the Cell/Range to Validate -> Goto Data -> Data Validation -> select the Validation criteria here and press OK. Now cell will take only defined values
Circle Invalid Data - It will put circle on cell containing Invalid values, when validation is defined after entering values.
Common Validations are - List, Date, Time, Text Length etc.
List Validation - It will create a drop down containing a range or defined values
Select the Cell/Range to Validate -> Goto Data -> Data Validation -> select the Validation criteria here and press OK. Now cell will take only defined values
Circle Invalid Data - It will put circle on cell containing Invalid values, when validation is defined after entering values.
Chapter 2 - MS Excel
Chapter 2 - MS Excel
1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
2. Age Calculations
3. Calculate End of Probation period using EDATE & WORKDAY
4. Calculate no. of working days between two dates (Networkdays Function)
5. Common Error types in Excel and error handling by Iferror function.
6. Substitute and Find
7. Left, Right and Mid
8. Common IS Functions : Istext, Isnumber, Isblank, Iserror
9. Len and Text
1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
2. Age Calculations
3. Calculate End of Probation period using EDATE & WORKDAY
4. Calculate no. of working days between two dates (Networkdays Function)
5. Common Error types in Excel and error handling by Iferror function.
6. Substitute and Find
7. Left, Right and Mid
8. Common IS Functions : Istext, Isnumber, Isblank, Iserror
9. Len and Text
MS EXCEL - Len and Text
MS EXCEL - Len and Text
=text(value,format_text) - Convert a value to specific text format
=text(value,format_text) - Convert a value to specific text format
MS EXCEL - Common IS Functions : Istext, Isnumber, Isblank, Iserror
IS functions, checks the specified value and returns TRUE or FALSE depending on the
outcome.
EXAMPLE - isblank
MS EXCEL - Substitute and Find
MS EXCEL - Substitute and Find
=substitute(text,old, new)
Substitute old text with new text
EXAMPLE: Singh is replaced with kumari
“=SUBSTITUTE(A89,““Singh””,““Kumari””)“ Arti Kumari Chauhan
=Find(find_text,text) Find the position of one text within other text
EXAMPLE: g is at 9 position in Arti Singh Chauhan
9 =FIND(“g”,A89)
=substitute(text,old, new)
Substitute old text with new text
EXAMPLE: Singh is replaced with kumari
“=SUBSTITUTE(A89,““Singh””,““Kumari””)“ Arti Kumari Chauhan
=Find(find_text,text) Find the position of one text within other text
EXAMPLE: g is at 9 position in Arti Singh Chauhan
9 =FIND(“g”,A89)
MS EXCEL - Common Error types in Excel and error handling by Iferror function.
MS EXCEL - Common Error types in Excel and error handling by Iferror function.
#NAME? error
The #NAME? error occurs when Excel does not recognize text in a formula.
EXAMPLE: SU to SUM
#VALUE! error
Excel displays the #VALUE! error when a formula has the wrong type of argument.
#DIV/0! error
Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.
#REF! error
Excel displays the #REF! error when a formula refers to a cell that is not valid.
EXAMPLE: Delete B column it will give error
#NAME? error
The #NAME? error occurs when Excel does not recognize text in a formula.
EXAMPLE: SU to SUM
#VALUE! error
Excel displays the #VALUE! error when a formula has the wrong type of argument.
#DIV/0! error
Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.
#REF! error
Excel displays the #REF! error when a formula refers to a cell that is not valid.
EXAMPLE: Delete B column it will give error
MS EXCEL - Calculate no. of working days between two dates (Networkdays Function)
MS EXCEL - Calculate no. of working days between two dates (Networkdays Function)
Calculate employee benefits that accrue based on the number of days worked during a
specific term. The calculation includes all weekdays (Mon - Fri)
=NETWORKDAYS(startDate,endDate,[holidays])
holidays - [optional] A list of one or more dates that should be considered non-work days.
E.g- 25 Sept 2015 is written as 25
Note that the start_date, end_date and [holidays] arguments should be input as
either:
References to cells containing dates or Dates returned from formulas.
- If you attempt to input these date arguments as text, Excel may misinterpret them, due to
different date systems, or date interpretation settings Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.
Calculate employee benefits that accrue based on the number of days worked during a
specific term. The calculation includes all weekdays (Mon - Fri)
=NETWORKDAYS(startDate,endDate,[holidays])
holidays - [optional] A list of one or more dates that should be considered non-work days.
E.g- 25 Sept 2015 is written as 25
Note that the start_date, end_date and [holidays] arguments should be input as
either:
References to cells containing dates or Dates returned from formulas.
- If you attempt to input these date arguments as text, Excel may misinterpret them, due to
different date systems, or date interpretation settings Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.
MS EXCEL - Calculate End of Probation period using EDATE & WORKDAY
MS EXCEL - Calculate End of Probation period using EDATE & WORKDAY
Returns no of month before or after the start date.
=EDATE(Start_date,Months) , Months can be positive or negative.
So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then
enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then
close your brackets “)” and press enter.
Done.
Returns no of month before or after the start date.
=EDATE(Start_date,Months) , Months can be positive or negative.
So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then
enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then
close your brackets “)” and press enter.
Done.
MS EXCEL - Age Calculations
MS EXCEL - Age Calculations
=datedif(d1,d2,“x”)
(input in cells)
D1- old date
D2- new date
x- It can be Y, M, D, YM, MD
Y- Return no of year between 2 dates
M- Return no. of months between 2 dates
D - Return no. of days between 2 dates
YM- Return no. of month in current year
MD- Return no of days in current months
Example –
Date1 - 10/29/1992 (Written in cell C14)
Date2 - 10/25/2015 (Written in cell C16)
=CONCATENATE(DATEDIF(C14,C16,“Y”),” Years “,DATEDIF(C14,C16,“YM”),”
Months “,DATEDIF(C14,C16,“MD”),” Days”)
22 Years 11 Months 26 Days
=datedif(d1,d2,“x”)
(input in cells)
D1- old date
D2- new date
x- It can be Y, M, D, YM, MD
Y- Return no of year between 2 dates
M- Return no. of months between 2 dates
D - Return no. of days between 2 dates
YM- Return no. of month in current year
MD- Return no of days in current months
Example –
Date1 - 10/29/1992 (Written in cell C14)
Date2 - 10/25/2015 (Written in cell C16)
=CONCATENATE(DATEDIF(C14,C16,“Y”),” Years “,DATEDIF(C14,C16,“YM”),”
Months “,DATEDIF(C14,C16,“MD”),” Days”)
22 Years 11 Months 26 Days
Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
|
A
|
B
|
C
|
D
|
|
TODAY
|
Returns Current Date
|
=TODAY()
|
11/8/015
|
|
NOW
|
Returns Current Date
|
=NOW()
|
11/29/2015
17:43
|
|
MONTH
|
Returns numeric month from date
|
=MONTH (D4)
|
11
|
|
YEAR
|
Returns year in YYYY format
from date
|
=YEAR(D4)
|
2015
|
|
DAY
|
Returns numeric day from date
|
=DAY(D4)
|
29
|
|
DATE
|
=DATE(YEAR,MONTH,DAY)
|
=DATE(2015,4,1)
|
4/1/2015
|
Count Functions in MS Excel
Count Functions in MS Excel
Count, Counta, Countblank, Countif
Count - Count only numeric value in a range not text
Counta- Calculates all except blank and including space in a range
Countif - Count no. of cell within a range that meet the given condition.
Countblank - Count blank cells space will be counted as zero.
BACK TO CHAPTER 1
Count, Counta, Countblank, Countif
Count - Count only numeric value in a range not text
Counta- Calculates all except blank and including space in a range
Countif - Count no. of cell within a range that meet the given condition.
Countblank - Count blank cells space will be counted as zero.
BACK TO CHAPTER 1
Concatenate in MS Excel
Concatenate in MS Excel
CONCATENATE is a text function used to join two or more text strings into one string.
Syntax: CONCATENATE(text1, [text2], …)
CONCATENATE is a text function used to join two or more text strings into one string.
Syntax: CONCATENATE(text1, [text2], …)
Format Painter in MS Excel
Format Painter in MS Excel
Format Painter copies formatting from one place and applies it to another quickly. It is usefull in Tables, Value and Header formatting. Double click the Format Painter button to apply the same formatting to multiple cells.
FIRST 10 MS EXCEL KEYBOARD SHORTCUTS
FIRST 10 EXCEL MS KEYBOARD SHORTCUTS
1. Ctrl+Z Undo
2. Ctrl+Y Redo
3. Ctrl+C Copy
4. Ctrl+V Paste
5. Ctrl+S Save
6. Ctrl+N New Workbook
7. Ctrl+O Open
8. Ctrl+F Find
9. Ctrl+P Print
10. Ctrl+A Select All
1. Ctrl+Z Undo
2. Ctrl+Y Redo
3. Ctrl+C Copy
4. Ctrl+V Paste
5. Ctrl+S Save
6. Ctrl+N New Workbook
7. Ctrl+O Open
8. Ctrl+F Find
9. Ctrl+P Print
10. Ctrl+A Select All
SUM, AVERAGE, PRODUCT, POWER (AN INTRODUCTION TO EXCEL MATH FUNCTIONS)
MS Excel Math Functions
SUM, AVERAGE, PRODUCT, POWER
SUM, AVERAGE, PRODUCT, POWER
Formula
|
Input
|
Output
|
SUM
|
=SUM(3,4,5)
|
12
|
AVERAGE
|
=AVERAGE(2,4,6)
|
4
|
PRODUCT
|
=PRODUCT(2,3,4)
|
24
|
CARET
|
=POWER(2,2)
|
4
|
MS EXCEL- Relative Reference and Absolute Reference
MS EXCEL- Relative Reference and Absolute Reference
Relative references
A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references.
For example, if you copy or fill a relative reference in cell D5 to cell D6, it automatically adjusts from =B5*C5 (shown in the above photo) to =B6*C6 (shown below.)
Absolute references
An absolute cell reference in a formula, such as $E$4, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell D6 to cell D7, it stays the same in both cells: =$E$4.
For example, if you copy or fill an absolute reference in cell D6 to cell D7, the relative cells automatically adjusts from =(B6*C6)*$E$4 to =(B6*C6)*$E$4, however notice that the absolute reference remains the same in both cells: =$E$4.
Operators in MS Excel
Operators in MS Excel
There are four different types of Operators in Excel: Arithmetic, Comparison, Text Concatenation and Reference.
Arithmetic Operators
To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results,use the following arithmetic operators.
There are four different types of Operators in Excel: Arithmetic, Comparison, Text Concatenation and Reference.
Arithmetic Operators
To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results,use the following arithmetic operators.
Arithmetic
Operator
|
Meaning
|
Operation
Performed
|
Output
|
+ (plus sign)
|
Addition
|
3+3
|
6
|
- (minus sign)
|
Subtraction
|
3-1
|
2
|
-
|
Negation
|
-1
|
-1
|
* (asterisk)
|
Multiplication
|
3*3
|
9
|
/ (forward slash)
|
Divison
|
3/2
|
1.5
|
% (percent sign)
|
Percent
|
20%
|
.20
|
^ (caret)
|
Exponentiation
|
3^2
|
9
|
Comparison Operators
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value -either TRUE or FALSE.
Comparison
Operator
|
Meaning
|
Example
|
= (equal sign)
|
Equal
to
|
A1=B1
|
> (greater than sign)
|
Greater
than
|
A1>B1
|
< (less than sign)
|
Less
than
|
A1<B1
|
>= (greater than or equal to sign)
|
Greater
than or Equal to
|
A1>=B1
|
<= (greater than or equal to sign)
|
Less
than or Equal to
|
A1<=B1
|
<> (not equal to sign)
|
Not
Equal to
|
A1<>B1
|
Text Concatenation Operator
Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.
Text
Operator
|
Meaning
|
Example
|
& (ampersand)
|
Connects,
or concatenates, two values to produce one continuous text value
|
"North" & "Wind"
results in "Northwind"
|
Reference Operators in MS Excel
Combine ranges of cells for calculations with the following operators.
Note: AND,OR etc are logical functions not logical operators as they return value on passing arguments.
BACK TO CHAPTER 1
Combine ranges of cells for calculations with the following operators.
Reference Operator
|
Meaning
|
Example
|
: (colon)
|
Range operator, which produces one
reference to all the cells between two
references, including the two
references.
|
B5:B15
|
, (comma)
|
Union operator, which combines
multiple references into one reference
|
SUM(B5:B15,D5:D15)
|
(space)
|
Intersection operator, which produces
one reference to cells common to the
two references
|
B7:D7 C6:C8
|
Note: AND,OR etc are logical functions not logical operators as they return value on passing arguments.
BACK TO CHAPTER 1
Subscribe to:
Posts (Atom)