### Microsoft Excel Intermediate Course Overview

Microsoft ® Excel 2016 Intermediate level is for students to learn the essentials for creating charts, styles and templates uses, and manage advanced worksheet formatting. Useful for managing large worksheets, data, creating models, and working with logical functions.

### Course Objectives

In this training you will learn intermediate MS Excel 2016 skills

• Manage Data Cells and Ranges
• Create Tables
• Perform Operations with formulas and functions
• Create Charts and Objects
• Using 3-D References
• Filtering and sorting tables
• Formatting tables

### Target Student

For professionals and business owners whose job responsibilities include managing and formatting data into Excel spreadsheets for an explanation of company data.

### Lesson 1: Manage Data Part 1

• A. Insert Data in Cells and Ranges
• B. Replace, cut, and paste data
• C. Paste data with special functions

### Lesson 2: Manage Data Part 2

• A. Using AutoFill to fill cells
• B. Insert and delete cells
• C. Merge cells
• D. Modify cell alignment and indentation
• E. Use format painter to format cells
• F. Wrap text within cells
• G. Apply cell formulas
• H. Apply cell styles

### Lesson 3: Summarize and Organize Data

• A. Insert Sparklines
• B. Outline Data
• C. Create Subtotals
• D. Apply Conditional formatting

### Lesson 4: Create Tables, Manage Table Styles and Options

• A. Create and manage tables
• B. Create an Excel table from cell range
• C. Convert a table to a cell range
• D. Add or remove table rows and columns
• E. Apply styles to tables
• F. Configure table style options
• G. Insert total rows

### Lesson 5: Filter and Sort tables

• A. Filter records
• B. Sort data by multiple columns
• C. Sort data by custom lists
• D. Change sort order
• E. Remove duplicate records

### Calculations with formulas and functions Part 1

• A. Use basic formulas and function
• B. Use absolute and mixed references
• C. Using formulas across worksheets

### Lesson 2: Calculations with Formulas and Functions Part 2

• A. Calculate with SUM function
• B. Use the MIN and MAX functions
• C. Use the COUNT functions
• D. Use the AVERAGE functions
• E. Formula and function tips

### Lesson 3: Use Conditional Functions

• A. Use the IF function
• B. Use the SUMIF function
• C. Use the AVERAGEIF function
• D. Use the COUNTIF function

### Lesson 4: Use the Text Functions Part 1

• A. Use the LEFT function
• B. Use the MID function
• C. Use the RIGHT function

### Lesson 5: Use Text Functions Part 2

• A. Use the UPPER and LOWER functions
• B. Use the PROPER function
• C. Use the CONCAT function

### Lesson 6: Create Charts and Objects

• A. Create Charts/New Chart
• C. Switch orientation of source data
• D. Use Quick Analysis to analyze data

### Lesson 7: Format Charts Part 1

• A. Resize charts
• B. Add and modify chart elements
• C. Data labels and legends
• D. Gridlines

### Lesson 8: Format Charts Part 2

• A. Apply chart layout and styles
• B. Change Chart type
• C. Create Secondary Axis
• D. Move charts to a chart sheet

### Lesson 9: Insert and Format Objects

• A. Insert text boxes and shapes
• B. Insert and modify images
• C. Add alternative text to objects
• D. Overall tips