Transcript Workshop One: Managing the Worksheet Environment

Applying Formulas and Functions
Dr. Carolyn Rainey
Professor Emeritus
10/29/2013
College of Business Excel Workshops
What is Microsoft Certification?
• Microsoft Office Specialist (MOS)
• www.certiport.com
– http://www.certiport.com/Portal/desktopdefault.aspx?page=common
/pagelibrary/MOS_whatIs.html
– Microsoft Office 2010 Exam Objectives
– Exam voucher
– http://www.measureup.com/Microsoft-Office-Specialist-MOS200720102013-Exam-Voucher-P3195.aspx
10/29/2013
College of Business Excel Workshops
Overview of Workshops
• Workshop One (10/22) - Managing the Worksheet
Environment
• Workshop Two (10/24) - Creating Cell Data,
Formatting/Revising Worksheets/Workbooks
• Workshop Three (10/29) - Applying Formulas and
Functions
• Workshop Four (10/31) - Presenting Data Visually
• Workshop Five (11/5) - Data Analysis
10/29/2013
College of Business Excel Workshops
Applying Formulas and Functions
• To participate in this live demonstration:
Start a new document
• Create formulas: start with an equal sign
– Operators
– Use keyboard
– Use selection method
– Use relative, absolute, mixed cell references
– Use 3D references
10/29/2013
College of Business Excel Workshops
Applying Formulas and Functions
• Use Sum icon to add numbers
• Use Sum icon to choose Average, Count,
Max and Min; also available in Formulas group
• Use AutoCalculate
• Review Circular reference
• Display formulas using Control plus tilde
10/29/2013
College of Business Excel Workshops
Using the Function Dialog Box
• Open BlueLakeSports-03 from
http://highered.mcgraw-hill.com/sites/0073519383/student_view0/data_files.html
– MAX, MIN, MEAN
– TODAY, DATE, NOW
– RANK.AVG
– PMT
10/29/2013
College of Business Excel Workshops
Using the Function Dialog Box
•
•
•
•
•
IF function
Nested IF conditions
COUNTA—counts anything that is not blank
COUNT—counts dates and numeric entries
COUNTIF—counts cells that meet one
criterion
• COUNTIFS—counts cells that meet multiple
criteria
10/29/2013
College of Business Excel Workshops
Using the Function Dialog Box
• AVERAGEIF—finds the arithmetic mean of
the cells in a specified range that meet a
single criterion.
• AVERAGEIFS
• SUMIF
• SUMIFS—used to add cells that meet
multiple criteria
• VLOOKUP and HLOOKUP
10/29/2013
College of Business Excel Workshops
Auditing Functions
• Trace Precedents, Dependents
• Audit Formulas
• Evaluate Formula
• Watch Window
• Trace Error
• Message Window
10/29/2013
College of Business Excel Workshops
Excel Functions
• Consolidate Data
• Link Data
Example: =SUM(B5:B8)+‛[Source Workbook.xlsx]QTR1 Revenue’!$B$5
• Track Changes
– Share the workbook
– Edit a cell
– Accept/reject changes
10/29/2013
College of Business Excel Workshops
Excel Functions
• Goal Seek
– Load Excel file: Whitney
– Use Data Tab, What-if Analysis, click Goal Seek
– Set cell B14 to a Value of 72; cell B10 to change
• Scenarios
– Add scenarios
– Create/edit Scenario Report
10/29/2013
College of Business Excel Workshops
Sample CertiPrep Items
Participate in CertiPrep
simulation of exam questions.
10/29/2013
College of Business Excel Workshops