Using Advanced Functions and Conditional Formatting Microsoft Excel
![Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 ® Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 ®](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-1.jpg)
![Use the IF function Use structured references in formulas Nest the IF function Use the IF function Use structured references in formulas Nest the IF function](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-2.jpg)
![If the formula in cell C 1 is copied & pasted in C 2 If the formula in cell C 1 is copied & pasted in C 2](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-3.jpg)
![Use conditional formatting to highlight duplicate values Summarize data using the COUNTIF, SUMIF, Use conditional formatting to highlight duplicate values Summarize data using the COUNTIF, SUMIF,](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-4.jpg)
![Visual Overview New Perspectives on Microsoft Excel 2010 5 Visual Overview New Perspectives on Microsoft Excel 2010 5](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-5.jpg)
![Logical functions (IF, AND, and OR) determine whether a condition is true or Logical functions (IF, AND, and OR) determine whether a condition is true or](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-6.jpg)
![Entering a formula in one cell of a column automatically copies the formula Entering a formula in one cell of a column automatically copies the formula](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-7.jpg)
![Create fields that require the least maintenance Store smallest unit of data possible Create fields that require the least maintenance Store smallest unit of data possible](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-8.jpg)
![A logical function that evaluates a single condition and results in only one A logical function that evaluates a single condition and results in only one](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-9.jpg)
![Using the IF Function New Perspectives on Microsoft Excel 2010 10 Using the IF Function New Perspectives on Microsoft Excel 2010 10](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-10.jpg)
![Using the IF Function New Perspectives on Microsoft Excel 2010 11 Using the IF Function New Perspectives on Microsoft Excel 2010 11](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-11.jpg)
![A logical function that tests two or more conditions (up to 255) and A logical function that tests two or more conditions (up to 255) and](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-12.jpg)
![Using the AND Function New Perspectives on Microsoft Excel 2010 13 Using the AND Function New Perspectives on Microsoft Excel 2010 13](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-13.jpg)
![Using the AND Function New Perspectives on Microsoft Excel 2010 14 Using the AND Function New Perspectives on Microsoft Excel 2010 14](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-14.jpg)
![Using the AND Function New Perspectives on Microsoft Excel 2010 15 Using the AND Function New Perspectives on Microsoft Excel 2010 15](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-15.jpg)
![Replace specific cell or range address with the actual table name or column Replace specific cell or range address with the actual table name or column](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-16.jpg)
![Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-17.jpg)
![Visual Overview New Perspectives on Microsoft Excel 2010 18 Visual Overview New Perspectives on Microsoft Excel 2010 18](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-18.jpg)
![To allow for three or more outcomes One IF function is placed inside To allow for three or more outcomes One IF function is placed inside](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-19.jpg)
![Creating Nested IFs New Perspectives on Microsoft Excel 2010 20 Creating Nested IFs New Perspectives on Microsoft Excel 2010 20](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-20.jpg)
![Creating Nested IFs New Perspectives on Microsoft Excel 2010 21 Creating Nested IFs New Perspectives on Microsoft Excel 2010 21](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-21.jpg)
![Visual Overview New Perspectives on Microsoft Excel 2010 22 Visual Overview New Perspectives on Microsoft Excel 2010 22](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-22.jpg)
![Conditional Formatting New Perspectives on Microsoft Excel 2010 23 Conditional Formatting New Perspectives on Microsoft Excel 2010 23](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-23.jpg)
![Changes a cell’s formatting when its contents match a specified condition Can be Changes a cell’s formatting when its contents match a specified condition Can be](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-24.jpg)
![Using the Conditional Formatting Rules Manager A conditional formatting rule specifies: ◦ Type of Using the Conditional Formatting Rules Manager A conditional formatting rule specifies: ◦ Type of](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-25.jpg)
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 26 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 26](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-26.jpg)
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 27 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 27](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-27.jpg)
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 28 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 28](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-28.jpg)
![Use COUNTIF, SUMIF, and AVERAGEIF functions to calculate a conditional count, sum, or Use COUNTIF, SUMIF, and AVERAGEIF functions to calculate a conditional count, sum, or](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-29.jpg)
![Calculates the number of cells in a range that match specified criteria Sometimes Calculates the number of cells in a range that match specified criteria Sometimes](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-30.jpg)
![Adds values in a range that meet your criteria Also called a conditional Adds values in a range that meet your criteria Also called a conditional](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-31.jpg)
![Using the AVERAGEIF Function New Perspectives on Microsoft Excel 2010 32 Using the AVERAGEIF Function New Perspectives on Microsoft Excel 2010 32](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-32.jpg)
- Slides: 32
![Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 ®](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-1.jpg)
Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 ®
![Use the IF function Use structured references in formulas Nest the IF function Use the IF function Use structured references in formulas Nest the IF function](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-2.jpg)
Use the IF function Use structured references in formulas Nest the IF function Objectives New Perspectives on Microsoft Excel 2010 2
![If the formula in cell C 1 is copied pasted in C 2 If the formula in cell C 1 is copied & pasted in C 2](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-3.jpg)
If the formula in cell C 1 is copied & pasted in C 2 & C 3, the following will result Relative Cell Addresses A B C 1 4 7 =A 1+B 1 2 6 3 =A 2+B 2 3 8 1 =A 3+B 3 4 5 2 ? ?
![Use conditional formatting to highlight duplicate values Summarize data using the COUNTIF SUMIF Use conditional formatting to highlight duplicate values Summarize data using the COUNTIF, SUMIF,](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-4.jpg)
Use conditional formatting to highlight duplicate values Summarize data using the COUNTIF, SUMIF, and AVERAGEIF functions Objectives New Perspectives on Microsoft Excel 2010 4
![Visual Overview New Perspectives on Microsoft Excel 2010 5 Visual Overview New Perspectives on Microsoft Excel 2010 5](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-5.jpg)
Visual Overview New Perspectives on Microsoft Excel 2010 5
![Logical functions IF AND and OR determine whether a condition is true or Logical functions (IF, AND, and OR) determine whether a condition is true or](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-6.jpg)
Logical functions (IF, AND, and OR) determine whether a condition is true or false Conditions use a comparison operator (<, <=, =, <>, >, or >=) to compare two values Combine two or more functions in one formula to create more complex conditions Working with Logical Functions New Perspectives on Microsoft Excel 2010 6
![Entering a formula in one cell of a column automatically copies the formula Entering a formula in one cell of a column automatically copies the formula](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-7.jpg)
Entering a formula in one cell of a column automatically copies the formula to all cells in that column To modify the formula in a calculated column: ◦ Edit the formula in any cell in the column ◦ Formulas in all cells in the column are modified To edit only one cell in a calculated column: Inserting ◦ Enter a value. Calculated or a formula that Columns is different from in that column in all anothers Excel Table New Perspectives on Microsoft Excel 2010 7
![Create fields that require the least maintenance Store smallest unit of data possible Create fields that require the least maintenance Store smallest unit of data possible](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-8.jpg)
Create fields that require the least maintenance Store smallest unit of data possible in a field Apply a text format to fields with numerical text data Creating Excel Table Fields New Perspectives on Microsoft Excel 2010 8
![A logical function that evaluates a single condition and results in only one A logical function that evaluates a single condition and results in only one](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-9.jpg)
A logical function that evaluates a single condition and results in only one value Returns one value if the condition is true and another value if the condition is false Syntax: Using the IF Function New Perspectives on Microsoft Excel 2010 9
![Using the IF Function New Perspectives on Microsoft Excel 2010 10 Using the IF Function New Perspectives on Microsoft Excel 2010 10](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-10.jpg)
Using the IF Function New Perspectives on Microsoft Excel 2010 10
![Using the IF Function New Perspectives on Microsoft Excel 2010 11 Using the IF Function New Perspectives on Microsoft Excel 2010 11](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-11.jpg)
Using the IF Function New Perspectives on Microsoft Excel 2010 11
![A logical function that tests two or more conditions up to 255 and A logical function that tests two or more conditions (up to 255) and](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-12.jpg)
A logical function that tests two or more conditions (up to 255) and determines whether all conditions are true Returns the value TRUE if all logical conditions are true and the value FALSE if any or all logical conditions are false Syntax: Using the AND Function New Perspectives on Microsoft Excel 2010 12
![Using the AND Function New Perspectives on Microsoft Excel 2010 13 Using the AND Function New Perspectives on Microsoft Excel 2010 13](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-13.jpg)
Using the AND Function New Perspectives on Microsoft Excel 2010 13
![Using the AND Function New Perspectives on Microsoft Excel 2010 14 Using the AND Function New Perspectives on Microsoft Excel 2010 14](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-14.jpg)
Using the AND Function New Perspectives on Microsoft Excel 2010 14
![Using the AND Function New Perspectives on Microsoft Excel 2010 15 Using the AND Function New Perspectives on Microsoft Excel 2010 15](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-15.jpg)
Using the AND Function New Perspectives on Microsoft Excel 2010 15
![Replace specific cell or range address with the actual table name or column Replace specific cell or range address with the actual table name or column](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-16.jpg)
Replace specific cell or range address with the actual table name or column header ◦ Names or headers are simpler to identify than cell addresses A formula that includes a structured reference can be fully qualified or unqualified Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel 2010 16
![Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-17.jpg)
Using Structured References to Create Formulas in Excel Tables New Perspectives on Microsoft Excel 2010 17
![Visual Overview New Perspectives on Microsoft Excel 2010 18 Visual Overview New Perspectives on Microsoft Excel 2010 18](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-18.jpg)
Visual Overview New Perspectives on Microsoft Excel 2010 18
![To allow for three or more outcomes One IF function is placed inside To allow for three or more outcomes One IF function is placed inside](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-19.jpg)
To allow for three or more outcomes One IF function is placed inside another IF function to test an additional condition More than one IF function can be nested Creating Nested IFs New Perspectives on Microsoft Excel 2010 19
![Creating Nested IFs New Perspectives on Microsoft Excel 2010 20 Creating Nested IFs New Perspectives on Microsoft Excel 2010 20](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-20.jpg)
Creating Nested IFs New Perspectives on Microsoft Excel 2010 20
![Creating Nested IFs New Perspectives on Microsoft Excel 2010 21 Creating Nested IFs New Perspectives on Microsoft Excel 2010 21](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-21.jpg)
Creating Nested IFs New Perspectives on Microsoft Excel 2010 21
![Visual Overview New Perspectives on Microsoft Excel 2010 22 Visual Overview New Perspectives on Microsoft Excel 2010 22](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-22.jpg)
Visual Overview New Perspectives on Microsoft Excel 2010 22
![Conditional Formatting New Perspectives on Microsoft Excel 2010 23 Conditional Formatting New Perspectives on Microsoft Excel 2010 23](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-23.jpg)
Conditional Formatting New Perspectives on Microsoft Excel 2010 23
![Changes a cells formatting when its contents match a specified condition Can be Changes a cell’s formatting when its contents match a specified condition Can be](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-24.jpg)
Changes a cell’s formatting when its contents match a specified condition Can be used to: ◦ Highlight cells based on their values ◦ Add data bars that graph relative values in a range ◦ Highlight duplicate values in a column of data Conditional Formatting New Perspectives on Microsoft Excel 2010 24
![Using the Conditional Formatting Rules Manager A conditional formatting rule specifies Type of Using the Conditional Formatting Rules Manager A conditional formatting rule specifies: ◦ Type of](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-25.jpg)
Using the Conditional Formatting Rules Manager A conditional formatting rule specifies: ◦ Type of condition ◦ Type of formatting when that condition occurs ◦ Cell or range the formatting is applied to Use Conditional Formatting Rules Manager dialog box to edit existing conditional formatting rules New Perspectives on Microsoft Excel 2010 25
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 26 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 26](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-26.jpg)
Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 26
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 27 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 27](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-27.jpg)
Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 27
![Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 28 Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 28](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-28.jpg)
Using the Conditional Formatting Rules Manager New Perspectives on Microsoft Excel 2010 28
![Use COUNTIF SUMIF and AVERAGEIF functions to calculate a conditional count sum or Use COUNTIF, SUMIF, and AVERAGEIF functions to calculate a conditional count, sum, or](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-29.jpg)
Use COUNTIF, SUMIF, and AVERAGEIF functions to calculate a conditional count, sum, or average using only cells that meet a particular condition Summarizing Data Conditionally New Perspectives on Microsoft Excel 2010 29
![Calculates the number of cells in a range that match specified criteria Sometimes Calculates the number of cells in a range that match specified criteria Sometimes](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-30.jpg)
Calculates the number of cells in a range that match specified criteria Sometimes referred to as a conditional count Syntax: Using the COUNTIF Function New Perspectives on Microsoft Excel 2010 30
![Adds values in a range that meet your criteria Also called a conditional Adds values in a range that meet your criteria Also called a conditional](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-31.jpg)
Adds values in a range that meet your criteria Also called a conditional sum Syntax: Using the SUMIF Function New Perspectives on Microsoft Excel 2010 31
![Using the AVERAGEIF Function New Perspectives on Microsoft Excel 2010 32 Using the AVERAGEIF Function New Perspectives on Microsoft Excel 2010 32](https://slidetodoc.com/presentation_image_h/ab8c7beec5add03a633e9eb0f8730b9a/image-32.jpg)
Using the AVERAGEIF Function New Perspectives on Microsoft Excel 2010 32
What is an unqualified structured reference in excel
Match destination formatting excel
Advanced excel functions for finance
Microsoft official academic course microsoft excel 2016
Microsoft excel merupakan program aplikasi …. *
Excel logical operators
What is past conditional
Excel tips and tricks 2019
Advanced conditional sentences
New document word
Magnetic read and write mechanisms
Formatting quotations and dialogue
First conditional de ask
Poem using zero conditional sentences
Advanced excel formulas
Mem excel
Https://academic.microsoft.com
Bayes theorem excel
Apa format example
How should a work cited page look
Picture of formatting toolbar
Modified block format letter
Apache fop
What is apa formatting style
Xslfo
Writing internal dialogue
Gambar toolbar formatting
Gambar toolbar formatting
Sqlplus formatting commands
Which hidden formatting symbol represents a tab character
Formatting in digital communication
Sqlplus report formatting
Apa 7 purdue owl