Microsoft Excel 365 Intermediate (2 Days)

This course is designed for existing Excel 365 Spreadsheet users who already are already comfortable working efficiently with Excel (building formulas, using functions, charting data and all the fundamentals) and want to take it to a new level.

Learning Outcomes

By the end of this course students will be able to work more effectively with large worksheets, apply range names in formulas, create 3D formulas, set validation rules and apply filters, protect sheets and generate templates to save time and effort.

Prerequisites

Students should already be comfortable with creating, editing and formatting spreadsheets, building formulas and using functions, understand order of operations, print, chart and modify as required.

Using Absolute References

• Relative v’s Absolute Formulas
• Understand BEMDAS
• Absolute References
• Using Mixed References

Working with Large Worksheets

• Setting Magnification Levels
• Using Full Screen View
• Splitting Window Panes
• Freezing and Unfreezing Panes
• Using Outlining
• Adding an Outline
• Expanding/Collapsing an Outline
• Clearing an Outline

Using Range Names

• Creating Range Names
• Navigating Range Names
• Defining Range Names
• Using Range Names in Formulas
• Applying Range Names
• The Name Manager

Using Paste Special

• Working with Paste Special
• Copying Formats or Values only
• Performing Mathematical Operations
• Using Transpose
• Copying Ranges with Hidden Data
• Copying Visible Cells Only

Using Multiple Worksheets and Workbooks

• Navigating between Worksheets
• Inserting and Deleting Worksheets
• Selecting Multiple Sheets
• Grouping and Un-Grouping Sheets
• Moving and Copying Data between Worksheets
• Printing Selected Worksheets
• Creating 3D Formulas/3D Functions
• Managing Worksheets

Linking Sheets & Workbooks

• Linking Data and Linking Methods
• Updating and Managing Links
• Preventing Link Prompts
• Removing Links/Hyperlinks

Shapes and Graphics

• Inserting Pictures/Logos
• Recolouring Images/Transparency
• Working with Shapes
• Adding and Formatting Objects
• Changing Shape Fill Colours
• Adding 3D Rotation Effects
• Adding Shadows and Reflections
• Using Feathered Edges
• Shape and Text Styles

Excel Functions

• Working with Excel Functions
• Mathematical Functions
• SUBTOTAL, COUNT v’s COUNTA, SUMIF, COUNTIF, SUMIFS, COUNTIFS, TRUNC, INT, RANDBETWEEN Functions
• WORKDAY, WEEKDAY Functions
• IF, IFS Functions

Advanced Formatting

• Using Conditional Formatting
• Editing a Conditional Format
• Highlighting and Removing Duplicates
• Using Data Bars and Icon Sets
• New Conditional Format Rules
• Copying Conditional Formatting
• Find cells with Conditional Formatting
• Dynamic Highlight Bars
• Timelines with Conditional Formatting

Working with Styles

• Applying and Existing Style
• Creating/Deleting Styles
• Modifying Styles
• Importing Styles between Workbooks

Using Data Validation

• Understanding Data Validation
• Setting Data Validation Rules
• Creating Drop Down Combos Boxes

Applying Protection

• Worksheet/Workbook Protection
• Password Protecting Files
• Unprotecting Worksheets

Comments

• Creating Comments in Files
• Viewing/Print out Comments

Making your Workbooks Error Free

• Finding and Correcting Formula Errors
• Fixing Circular References
• Fixing Spelling Errors

Working with Filtered Lists

• Working with Data Lists
• Enabling a Filter
• Creating a Custom Filter
• Modifying Filter Criteria
• Sorting using a Single Key
• Sorting with Multiple Sort Keys
• Working with Tables
• Adding Dynamic Headings
• Table Tools & Formats
• Using the Table Style Gallery
• Creating a Custom Table Style
• Performing a Custom Sort
• Editing the Sort Order
• Analysing Data in a Table
• Understanding the Table Reference
• 10 Reasons to use Tables

Form Controls

• Adding Form Controls
• Different Types of Controls
• Controls linked to Cells

Templates

• Modifying the Default Workbook Template
• Saving a Workbook as a Template