Microsoft Excel Masterclass

Course Outline

Navigation of the Excel Interface

• Starting Excel
• The Excel Tab Interface
• The Main Components of the Workbook Screen
• Understanding the Backstage View
• Accessing the Backstage View
• Understanding Dialog Boxes
• Launching Dialog Boxes
• Understanding the Quick Access Toolbar
• Adding Commands to the QAT
• Changing the Location of the Bar
• Adding a New Command to the Toolbar
• Removing a Command from the Quick Access Bar
• Understanding the Status Bar

Use of Lists and Tables

• Understanding Lists
• Applying a Filter
• Performing an Alphabetical Sort
• Performing a Numerical Sort
• Creating a Table
• Converting Selection to a Data Table
• Inserting or Deleting Table Records
• Deleting Table Records
• Removing Duplicates
• Modifying a Table
• Sorting Data in a Table
• Naming Tables
• Resizing Tables
• Converting a Table to a Range
• Deleting a Table

Conditional Formatting

• Understanding Conditional Formatting
• Highlight Cells Containing Values
• Highlighting Cells Containing Text
• Highlighting Duplicate Values
• Using Top and Bottom Rules
• Using Data Bars
• Using Colour Scales
• Using Icon Sets
• Creating Custom Rules
• The Conditional Formatting Rules Manager
• Clearing Rules

Using Formulas and Creating Pivot Tables

• Using Sparklines
• Using Formulas in Excel
• Exploring Excel Functions
• Insert Function Button
• Working with Names and Ranges
• Creating a Named Reference
• Removing a Named Reference
• What If Formulas
• Common Logical Functions
• Arguments used within If Functions
• Creating a PivotTable from an Excel Data List
• Add/Remove/Reorganise Data Fields
• Changing the PivotTable Design
• Refresh Data/Change Data Source
• Creating and Modifying a Pivot Chart
• VLOOKUP
• Zooming In and Out Quickly
• Undo, Redo, and Repeat with Keystrokes