Select tickets
Course Code: 0200-002-19-W
Course Format: Instructor-Led Online
Prerequisites:
- None
At Course Completion:
In this course, you will cover how to work with large worksheets and workbooks in Microsoft Excel. Create Named cells and ranges which are used in formulas and navigation. Write formulas that refer to other worksheets and workbooks. Create 3D formulas that refer to data in other worksheets and workbooks. You will sort and filter data, use conditional formatting, and create and format tables and charts, and how to use the Quick Analysis tools. Apply protection to the worksheet, workbook, and file itself. You will work with graphics, run the Accessibility Checker and make the file Accessible (508 Compliant), create and work with comments, and finally create a custom template.
Topics:
Lesson 1: Working with Multiple Worksheets and Workbooks
• About Workbooks and Worksheets
• Inserting and Deleting Worksheets
o Inserting a Worksheet
o Deleting a Worksheet
• Selecting Worksheets
o Selecting a Single Sheet
o Selecting two or more adjacent sheets
o Selecting two or more nonadjacent sheets
o Selecting all the sheets in a workbook
• Editing Across Multiple Worksheets
o Establishing the structure
• Renaming Worksheets
• Tab Color
• Hiding and Unhiding Worksheets
o Hiding the Worksheet
o Unhiding the Worksheet
• Viewing multiple Worksheets
o Creating New Windows
o Opening New Windows in a Workbook
o Viewing Multiple Worksheets from the Same Workbook
• Opening Multiple
• Workbooks
• Viewing Workbooks and Worksheets
o Switching Windows
o Arranging the Window
o Making a Workbook Active
• Moving & Copying Worksheets
o Using the Move or Copy Dialog Box
o Using Dragging
• Hiding Workbooks
o Hiding a Window
• Viewing Data in Workbooks and Worksheets
o Freeze Panes
o Comparing Workbooks
• Closing Multiple Workbooks
o Adding the Close All button to QAT
Lesson 2: Names
• Names
o Rules for Defining Names
o Scope
• Defining Names
o Defining a Name
o Using the Name Box
o Using Define Name
o From within the Name Manager
o From Selections
o Defining Names for 3-D References
• Name Manager
o Opening and Viewing the Name Manager
• Editing Names
o Editing a Name
o Deleting a Name
• Using Names to Navigate
o The Name Box drop-down
o The GoTo dialog
• Using Names in Formulas
Lesson 3: Referring to Data in Another Worksheet/Workbook
• Referring to Another Worksheet
o Referring to Another Cell
• Referring to Other Worksheets in Formulas
o Formulas that pull data from other worksheets
• Formulas with 3-D References
o Creating a Formula Using a 3-D Reference
• Formulas Using Multiple Varied References
o Formulas Referring to Various Locations
• Referring to Another Workbook
o Creating an External Reference/Link
• Working with Links
o Updating/Editing External References/Links
o Updating a Link Manually
o Opening a Source File
o Changing the Source File
o Breaking a Link
Lesson 4: Working with Data
• Conditional Formatting
o Applying Basic Conditional Formatting
• Managing Conditional Formatting
o Editing a Rule
• Conditional Formatting Based on Formulas
o Using Formulas in Conditional Formatting
• Clearing Conditional Formats
o Clearing Conditional Formats
• Sorting
o Performing a Sort
o Performing a Custom Sort
• Custom Sorting
o Performing a Filter
• Filtering
o Performing a Text, Date and Number Filter
o Turning Off Filters
• Tables
o Creating a Table
o Using Table Tools
• Charts
o Creating a Chart
o Using Chart Tools
• Quick Analysis
Lesson 5: Protecting the Worksheet/Workbook
• Protecting Worksheets
o Protecting the Contents of a Worksheet
o Applying Worksheet Protection
• Unprotecting a Worksheet
o Unprotecting the Worksheet
• Hidden Cells
o Hidden Formulas
• Allow Users to Edit Ranges
o Setting up the ranges
• Protecting Workbooks
o Protecting a Workbook
o Unprotecting a Workbook
• Protecting the File with Encryption
o File Encryption
• Protecting the File with Passwords
o Workbook Passwords to Open/Modify
• Workbooks Versions (optional)
o Accessing Workbook Versions
o To Restore a Historical Version
o The AutoRecover Feature
o Setting the Autosave options
o Recovery of Unsaved Workbooks
Lesson 6: Graphics, Comments, and Templates
• Graphic Objects
o Picture Formats
o 3D Model Formats
• Inserting Pictures
o Inserting Pictures
• Embedding Images
o Embedding an Image Into a Cell
o Filtering data with images
o Sorting Images as Data
• Modifying Graphics
o Using the Format Tab
o Using the Format Pane
o Adding Alt Text
• Accessibility
o Running Accessibility Checker
• Comments
o Inserting a Comment
o Resizing a Comment
o Formatting a Comment
o Viewing Comments
o Moving a Comment
o Editing a Comment
o Deleting a Comment
o Printing Comments
• Templates
• Creating a Template
• Using a Template
• Editing a Template