Excel 2013 - Intermediate

Duration
4 Hours, 6 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

This course is designed to be the intermediate level of Excel 2013. Students will learn intermediate functions and formulas, be introduced to and work with PivotTables and Tables, work with Charts, work with Flash Fill, work with subtotals and outlining, and learn how to customize the Excel environment.

Course Objectives

Customizing Excel, Linking Workbooks & Worksheets, Working with Range Names, Analyzing Data with Common Logical Functions, Analyzing Data with Common Lookup Functions, Sorting and Filtering Range Data, Analyzing and Organizing with Tables, Using Conditional Formatting, Outlining with Subtotals and Grouping, Displaying Data Graphically, Understanding PivotTables, PivotCharts, and Slicers, Working with Flash Fill, Working with Date and Time Functions, Working with Common Text Functions

Course Prerequisites

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

Agenda

1 - Introduction

  • Introduction

2 - Customizing Excel

  • Customizing the Ribbon
  • Customizing the Quick Access Toolbar
  • Customizing General Formula Options
  • Customizing AutoCorrect Options
  • Customizing Save Defaults
  • Customizing Advanced Excel Options

3 - Linking Workbooks & Worksheets

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

4 - Working with Range Names

  • What Are Range Names?
  • Creating Range Names Using the Name Box and Define Name
  • Creating Range Names Using Create From Selection
  • Using the Name Manager to Manage Range Names
  • Using Range Names in Formulas

5 - Analyzing Data with Common Logical Functions

  • Working with the Most Common Logical Functions
  • Evaluating Data with the AND Function
  • Evaluating Data with the OR Function
  • Understanding IF Functions
  • Nesting AND & OR Inside an IF Function

6 - Analyzing Data with Common Lookup Functions

  • What are Lookup Functions?
  • Using VLOOKUP
  • Using HLOOKUP

7 - Sorting and Filtering Range Data

  • Understanding the Differences between Sorting and Filtering
  • Sorting Lists
  • Filtering Lists

8 - Analyzing and Organizing with Tables

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

9 - Using Conditional Formatting

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

10 - Outlining with Subtotals and Grouping

  • What are Subtotals and Grouping?
  • Creating Subtotals
  • Grouping and Ungrouping Data

11 - Displaying Data Graphically

  • What are Charts?
  • Creating Charts
  • Understanding Chart Elements
  • Modifying Charts 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

12 - Understanding PivotTables, PivotCharts, and Slicers

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

13 - Working with Flash Fill

  • What is Flash Fill?
  • Using Flash Fill and AutoFill
  • Filling Various Series

14 - Working with Date and Time Functions

  • What are Date and Time Functions?
  • Using TODAY, NOW, and DAY Functions
  • Using NETWORKDAYS and YEARFRAC Functions

15 - Working with Common Text Functions

  • What are Text Functions?1min 33sec0%
  • Using CONCATENATE
  • Using Text to Columns
  • Using LEFT, RIGHT, and MID Functions
  • Using UPPER, LOWER, and PROPER Functions

16 - 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.