Excel 365 - Intermediate

Duration
4 Hours, 30 Minutes

 

Delivery Method
Self-Paced

Educate 360 Pro from New Horizons

This class is part of a 12‑month Educate 360 Pro Subscription

Course Overview

In this course, students will learn how to link workbooks and worksheets, work with range names, sort and filter range data, and analyze and organize with tables. Students will also apply conditional formatting, outline with subtotals and groups, display data graphically with charts and sparklines. Additionally, students will also understand PivotTables, PivotCharts, and slicers and work with advanced PivotTables and PowerPivot features.

Course Objectives

Linking Workbooks and Worksheets, Working with Range Names, Sorting, Filtering, and Subtotaling, Analyzing and Organizing with Tables, Using Conditional Formatting, Displaying Data Graphically, Understanding PivotTables, PivotCharts, and Slicers, Working with Advanced PivotTables and PowerPivot Features

Course Prerequisites

The recommended pre-requisite for this course is Excel 365 Introduction course or equivalent knowledge or experience.

Agenda

1 - Introduction

  • Introduction

2 - Linking Workbooks and Worksheets

  • Linking Workbooks and Worksheets
  • Linking Workbooks with 3D References
  • Understanding the Consolidate Feature

3 - Working with Range Names

  • What are Range Names?
  • Using the Name Box and Define Name
  • Using Create from Selection

4 - Sorting, Filtering, and Subtotaling

  • Differences Between Sorting and Filtering
  • Sorting Lists
  • Filtering Lists
  • Creating Subtotals

5 - Analyzing and Organizing with Tables

  • Creating Tables and Understanding the Benefits
  • Understanding the Elements of a Table
  • Formatting a Table
  • Sorting and Filtering Tables
  • Filtering with Slicers
  • Calculating with Tables
  • Removing Erroneous Table Data
  • Exporting, Refreshing, and Converting Tables

6 - Using Conditional Formatting

  • What is Conditional Formatting?
  • Highlight Cells and Top Bottom Rules
  • Data Bars, Icon Sets, and Color Scales
  • Using Custom Fonts and Colors
  • Using Custom Conditional Formatting
  • Modifying or Removing Conditional Formatting

7 - Displaying Data Graphically

  • What are Charts?
  • Creating Charts
  • Understanding Chart Elements
  • Modifying Chart Elements
  • Changing and Moving Charts
  • Filtering a Chart
  • Formatting Charts
  • Adjusting Numbering
  • Creating Dual Axis Charts
  • Forecasting with Trendlines
  • Creating a Chart Template
  • Displaying Trends with Sparklines

8 - Understanding PivotTables, PivotCharts, and Slicers

  • What is a PivotTable?
  • Creating a PivotTable
  • Working with the PivotTable Fields Pane
  • Data Organization and Analysis with PivotTables
  • Formatting PivotTables
  • Creating a PivotChart
  • Modifying and Formatting a PivotChart
  • Adding Slicers and Timeline Slicers
  • Formatting Slicers
  • Using the Ideas Feature

9 - Working with Advanced PivotTables and PowerPivot Features

  • Using the PivotTable and PivotChart Wizard
  • Adding a Calculated Field
  • Adding a Calculated Item
  • Applying Conditional Formatting to a PivotTable
  • Creating Filter Pages for a PivotTable
  • Enabling the PowerPivot Add-In

10 - Conclusion

  • Course Recap

What is Educate 360 Pro?

Educate 360 Pro is a 12-month subscription that provides unlimited access to full-day or short-duration live classes, on-demand video courses, class recordings, and quick reference cards. New Horizons offers more learning solutions for Microsoft Office than any other training provider and they are all included in your Educate 360 Pro subscription.

  • Unlimited enrollments into over 90 full-day and short-duration live training classes
  • Over 750 hours of on-demand video based content
  • A full library of digital quick reference cards for your favorite Office applications
  • Access to class recordings so you never have to worry about missing a scheduled class

Benefits of an Educate 360 Pro Subscription

Our Educate 360 Pro subscription is built to fit in and around your schedule. It will change the way you think about training.

  • Ask questions and get immediate answers during our full-day and short-duration live classes
  • Learn how to perform specific tasks instead of wasting time learning things you already know
  • Collaborate with fellow students and gain insight into new ways to solve problems
  • View on-demand courses with instructors that never leave your screen
  • Utilize course assessments to validate new skills
  • Take full day training classes on your schedule by accessing our class recordings
  • Revisit a prior class to further enhance your skills

Need more information? Click here to learn more about Educate 360 Pro.