Microsoft Excel Introductory

Introduction

• Opening / Closing Screen
• The Ribbon
• Quick Access Toolbar
• Backstage View
• Getting Help
• Creating, Opening, Closing and Saving Worksheets
• Navigation a Worksheet
• Selecting Ranges
• Deleting files
• Creating folders

Editing a Worksheet

• Entering data
• Inserting & Deleting cells, columns and rows
• Adjusting columns and rows
• Copy and Paste
• Clipboard
• Find and Replace
• Undo and Redo
• AutoFill
• Flash Fill
• Custom Lists

Basic Formulas and Functions

• Basic Calculations
• AutoSum
• Editing Formulae
• Functions – Average, Min, Max, Count
• AutoCalculate
• Relative and Absolute Cells
• Circular References

Formatting Cells

• Formatting Numbers
• Formatting Data
• Adding Borders and Shading
• Format as a Table
• Format Painter

Printing

• Page layout
• Page options
• Sheet options
• Margins
• Headers & Footers
• Printing

Linking Worksheets

• Arranging Windows
• Screen Panes
• Freeze Titles
• Zoom
• Named Ranges
• Linking worksheets
• Hyperlinks

Proofing Tools

• AutoCorrect
• Spell Check
• Smart Lookup
• Hiding Data
• Worksheet Protection

Microsoft Excel Intermediate

Formatting Workbooks & Templates

• Format as Table
• Conditional Formatting
• Custom Formatting
• Templates
• Styles
• Paste Special

Charts

• Creating Charts
• Editing Charts
• Sparklines
• Quick Analysis
• 3D Maps

Databases

• Sorting Data
• Filtering
• Advanced Filter
• Database Functions
• Subtotals
• Outlining and Grouping
• Removing Duplicates

Functions

• Lookup Tables – VLOOKUP, HLOOKUP
• Logical Formula – IF, NESTED IF, AND, OR, SUMIF(S), COUNTIF(S), IFS
• Text Functions – CONCAT, PROPER, UPPER, LOWER
• Date Functions – TODAY, NOW, NETWORKDAYS

Pivot Tables

• Create a Pivot Table
• Modify a Pivot Table
• Recommended Pivot Tables
• Slicers & Timelines
• Use PivotTable Charts

Auditing

• Auditing Formulae
• Error Codes
• Rounding Errors
• Watch Window
• Circular References
• Data Validation
• Comments
• Notes

Drawings

• Working with Graphics
• SmartArt
• Shapes
• Screenshot
• Ink Equation

Other

• Default Options
• Customising the Ribbon
• Customising the Quick Access Toolbar

Microsoft Excel Advanced

Sharing Workbooks

• Check for issues
• Check accessibility
• Check compatibility
• Sharing workbooks

Macros

• Introduction to Macros
• Recording a Macro
• Editing a Macro
• Macro Playback
• Adding a Macro to a Toolbar

Multiple Workbooks

• Advantages and Disadvantages
• Linking Worksheets with Formulae
• Linking with Paste Special
  Linking with a 3D formula
• Updating Links
• Data Consolidation

Import and Export Data

• Import external data
• Get External Data
• Import from Text/CSV
• Text to Columns
• Export to Other Applications

Analytical Tools

• Goal Seek
• Scenario Manager
• Data Tables
• Solver
• Forecasting Trends
• Trend lines in Charts
• Forecast sheets

Forms

• Creating Forms
• List, Combo, Check Boxes, Spinner, Group & Option buttons

User-Defined Functions

• Defining your own functions
• Using custom functions