Course Overview
This course will teach students advanced concepts and formulas in Microsoft Excel 2013. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students will also learn about Excel's many collaboration features and import and export data to and from their workbooks.
Course Objectives
Using SUMIF, AVERAGEIF, and COUNTIF, Using Advanced Lookup Functions, Using Complex Logical and Text Functions, Formula Auditing, What-If Analysis Tools, Worksheet and Workbook Protection, Advanced Use of PivotTables and PowerPivot, Automating with Macros, Working with Form Controls, Ensuring Data Integrity, Collaborating in Excel, Importing and Exporting Data to a Text File
Agenda
1 - Introduction
2 - Using SUMIF, AVERAGEIF, and COUNTIF
- Summarizing Data with SUMIF
- Summarizing Data with AVERAGEIF
- Summarizing Data with COUNTIF
3 - Using Advanced Lookup Functions
- Using VLOOKUP with TRUE to Find an Approximate Match
- Using HLOOKUP with TRUE to Find an Approximate Match
- Using the INDEX Function
- Using the MATCH Function
- Creating a Combined INDEX and MATCH Formula
- Comparing Two Lists with VLOOKUP
- Comparing Two Lists with a Combined VLOOKUP and ISNA
4 - Using Complex Logical and Text Functions
- Creating a Nested IF Function
- Using the IFERROR Function
- Using the LEN Function
- Using the TRIM Function
- Using the SUBSTITUTE Function
5 - Formula Auditing
- Showing Formulas
- Tracing Cell Precedents and Dependents
- Adding a Watch Window
- Error Checking
6 - What-If Analysis Tools
- Using the Scenario Manager
- Using Goal Seek
- Analyzing with Data Tables
7 - Worksheet and Workbook Protection
- Protection Overview
- Excel File Password Encryption
- Allowing Specific Worksheet Changes
- Adding Protection to Only Certain Cells in a Worksheet
- Additional Protection Features
8 - Advanced Use of PivotTables and PowerPivot
- Using the PivotTable and PivotChart Wizard
- Adding a Calculated Field
- Adding a Calculated Item
- Applying Conditional Formatting to a PivotTable
- Filters in the PivotTable Fields Pane
- Creating Filter Pages for a PivotTable
- Enabling the PowerPivot Add-In
9 - Automating with Macros
- What are Macros?
- Displaying the Developer Tab & Enabling Macros in Excel
- Creating a Basic Formatting Macro
- Running a Macro
- Assigning a Macro to a Button
- Creating a More Complex Macro
- Viewing and Editing the VBA Code for an Existing Macro
- Adding a Macro to the Quick Access Toolbar
10 - Working with Form Controls
- What are Form Controls?
- Adding Spin Buttons and Check Boxes to a Spreadsheet
- Adding a Combo Box to a Spreadsheet
11 - Ensuring Data Integrity
- What is Data Validation?
- Restricting Data Entry to Whole Numbers
- Restricting Data Entry to a List
- Restricting Data Entry to Specific Text Lengths
- Restricting Data Entry to a Date
- Composing Input Messages
- Composing Error Alerts
- Finding Invalid Data
- Editing and Deleting Data Validation Rules
12 - Collaborating in Excel
- Working with Comments
- Printing Comments and Errors
- Sharing a Workbook
- Tracking Changes in a Workbook
- Working with Versions
- Sharing Files Via Email
13 - Importing and Exporting Data to a Text File
- Importing a Text File into Excel
- Exporting Data to a Text File
14 - Conclusion