Unit-1.pptUnit-1.pptUnit-1.pptUnit-1.ppt

ggg

1. Advanced Microsoft Excel Advanced Microsoft Excel
CapabilitiesCapabilities
Mastering the First Year RoleMastering the First Year Role

2. Course objectives:Course objectives:
- Refresher on basic MS Excel features and functions- Refresher on basic MS Excel features and functions
- Use MS Excel functions for those procedures in which - Use MS Excel functions for those procedures in which
use of IDEA is not practicableuse of IDEA is not practicable
- Use MS Excel functions to promote the use of IDEA - Use MS Excel functions to promote the use of IDEA
- Use MS Excel to efficiently & effectively produce - Use MS Excel to efficiently & effectively produce
reports/working papersreports/working papers

3. Course outline:Course outline:
- Basic excel screen elements - Basic excel screen elements
- Data organizing tools & techniques- Data organizing tools & techniques
- Editing & formatting tools & techniques- Editing & formatting tools & techniques
- Often used advanced MS Excel formula functions- Often used advanced MS Excel formula functions
- Working with Pivot Table- Working with Pivot Table
- Using data analysis functions- Using data analysis functions

4. Basic MS Excel Screen Basic MS Excel Screen
ElementsElements

5. MS Excel 2010/2007 Basic Screen Elements

6. Formula BarFormula Bar
- Use the formula bar in validating and understanding the - Use the formula bar in validating and understanding the
formula you are working with formula you are working with
Explanation of formula and
the formula fields

7. Shortcut KeysShortcut Keys
CTRL combination shortcut keysCTRL combination shortcut keys

8. Shortcut KeysShortcut Keys
CTRL combination shortcut keysCTRL combination shortcut keys

9. Shortcut KeysShortcut Keys
Function keysFunction keys

10. Shortcut KeysShortcut Keys
Function keysFunction keys

11. Data Organizing Tools and Data Organizing Tools and
TechniquesTechniques

12. Data organizing tools and techniquesData organizing tools and techniques
- Performing calculations on filtered data - Performing calculations on filtered data
- Validating data- Validating data
- Summarizing and grouping multiple sets of data- Summarizing and grouping multiple sets of data

13. Performing calculations on filtered data Performing calculations on filtered data
- COUNTIF function- COUNTIF function
- AND function- AND function
- SUMIF function- SUMIF function

14. Performing calculations on filtered data Performing calculations on filtered data
COUNTIF functionCOUNTIF function
- Count things when a certain condition is met- Count things when a certain condition is met
COUNTIF (range, criteria)COUNTIF (range, criteria)

15. Performing calculations on filtered data Performing calculations on filtered data
COUNTIF functionCOUNTIF function
- Exercise 1: Use countif function to see how many - Exercise 1: Use countif function to see how many
students were above the score of 70 in each subjectstudents were above the score of 70 in each subject

16. Performing calculations on filtered data Performing calculations on filtered data
AND functionAND function
- Checks whether all arguments are true- Checks whether all arguments are true
AND (Logical 1, Logical 2,…..)AND (Logical 1, Logical 2,…..)

17. Performing calculations on filtered data Performing calculations on filtered data
AND functionAND function
- Exercise 2: Using the data in Exercise 1, determine the - Exercise 2: Using the data in Exercise 1, determine the
number of cash bonuses if the government has decided number of cash bonuses if the government has decided
to give cash bonus (on a per subject basis) to the school to give cash bonus (on a per subject basis) to the school
if 4 or more students get a score of 70 on a per subject if 4 or more students get a score of 70 on a per subject
basis basis

18. Performing calculations on filtered data Performing calculations on filtered data
SUMIF functionSUMIF function
- Adds up things when a certain condition is met - Adds up things when a certain condition is met
SUMIF (range, criteria, sum_range)SUMIF (range, criteria, sum_range)

19. Performing calculations on filtered data Performing calculations on filtered data
SUMIF functionSUMIF function
- Exercise 3: Using the data in Exercise 1, determine the - Exercise 3: Using the data in Exercise 1, determine the
total amount of cash bonus to be received by the school total amount of cash bonus to be received by the school

20. Data Validation Data Validation
- Useful when dealing with same set of data which needs - Useful when dealing with same set of data which needs
to be inputted into a spreadsheet. This can be done by to be inputted into a spreadsheet. This can be done by
creating drop-down lists.creating drop-down lists.
© Punongbayan & Araullo. All rights reserved.