Excel 2016 Intermediate

Name: Excel 2016 Intermediate
Rating: 4.6 (238 reviews)

Expand your Excel abilities with charts, outlines, workbooks and much more.

Created byIntellezy Trainers

Last updated 4/2018

English

What you'll learn

Custom Excel
Link Workbooks and Worksheets
Work with Range Names
Analyze Data with Common Logical Functions
Analyze Data with Common Lookup Functions
Sort and Filter Range Data
Analyze and Organize with Tables
Use Conditional Formatting
Outline with Subtotals and Grouping
Display Data Graphically
Understand PivotTables and PivotCharts and Filter with Slicers
Work with Flash Fill
Work with Date and Time Functions
Work with Common Text Functions

Course content

17 sections • 75 lectures • 4h 0m total length

Introduction1:24
Learn to customize the Excel environment, link workbooks with formulas, and work with named ranges. Analyze data with logical and lookup functions, and explore sorting, filtering, tables, and pivot charts.

Customizing the Ribbon6:09
Customizing the Quick Access Toolbar3:46
Customize the quick access toolbar by adding frequently used commands and rearranging items. Right-click commands in the ribbon to add them, and place the toolbar above or below the ribbon.
Customizing the General and Formula Options2:28
Customizing the AutoCorrect Options3:31
Explore how to customize AutoCorrect in Excel 2016, enable options like capitalizing sentences and replacing text as you type, and manage entries to fix common misspellings.
Customizing the Save Defaults2:25
Customizing Advanced Excel Options2:49

Linking Workbooks and Worksheets4:55
Link values across sheets and workbooks with formulas and the proper syntax, including workbook names in brackets, sheet names with exclamation points, and the relevant cell references.
Linking Worksheets with 3D References2:54
Understanding the Consolidate Feature4:44
Master consolidating data across multiple sheets using Excel 2016's Consolidate feature, applying sum or average, leveraging leftmost-column or top-row labels, and linking to source data.

What are Range Names?2:43
Master named ranges in Excel by naming a continuous cell block, using it in formulas as absolute references and navigation bookmarks, and applying basic creation rules.
Creating Range Names Using the Name Box and Define Name6:46
Create named ranges in excel using the name box and define name tool, with examples like Annex and Annex West. Manage scope and edits in name manager.
Creating Range Names Using Create from Selection2:39
Learn how to create multiple named ranges from top row using the create from selection tool in Excel 2016 intermediate, then use those named ranges in formulas to sum columns.

Working with the Most Common Logical Functions2:31
Evaluating Data with the AND Function3:15
Evaluating Data with the OR Function3:07
Explore how the or function in Excel 2016 evaluates data by returning true when any condition is met, with practical examples comparing or to and and showing real-world filters.
Understanding IF Functions4:19
Master the IF function by understanding its syntax and applying conditional logic to calculate commissions, using logical tests, value if true, and value if false.
Nesting AND and OR Inside an IF Function5:57

What are Lookup Functions?1:41
Learn how to retrieve data from other sheets or workbooks using lookup functions. Compare horizontal and vertical lookups, HLOOKUP and VLOOKUP, and when to apply each based on headings.
Using VLOOKUP7:39
Learn how VLOOKUP and HLOOKUP work, focusing on lookup value, table array, column index, and exact versus approximate matches using named ranges. Handle missing data with clear NA errors.
Using HLOOKUP2:22

Creating Tables and Understanding the Benefits2:28
Understanding the Elements of a Table2:38
Formatting a Table2:36
Sorting Tables2:05
Sort excel 2016 tables by single or multiple fields using quick sort options, color sorting, or the data tab's custom sort to arrange by A to Z, oldest to newest.
Filtering Tables2:38
learn to filter Excel tables in 2016 intermediate, using top filters, custom text filters, and search to select agents; apply single or multiple filters and clear them when needed.
Filtering with Slicers2:49
Calculating with Tables3:42
Removing Erroneous Table Data2:08
Exporting, Refreshing, and Converting Tables2:06

What is Conditional Formatting?1:18
Conditional Formatting: Highlight Cells and Top Bottom Rules4:23
Conditional Formatting: Data Bars, Icon Sets, and Color Scales3:55
Using Custom Fonts and Colors2:15
Using Custom Conditional Formatting2:46
Modifying or Removing Conditional Formatting2:04
Explore how to use the rule manager to view, create, edit, delete, and reorder conditional formatting rules; learn about precedents and clearing rules from cells or the entire sheet.

Requirements

Excel 2016 Introduction course or equivalent knowledge or experience

Description

This course is designed to be the intermediate level of Excel 2016. Students will learn how to link workbooks and worksheets, create named ranges and utilize them in formulas, build Logical functions such as IF, AND, and OR, and use Lookup functions to locate and compare data. Students will also be introduced to and work with Excel’s Table feature, learning to create and modify Tables. Students will also create and modify PivotTables and PivotCharts to analyze large data sets, sort the data, and use Slicers and Timeline Slicers to filter the data. Additionally, students will create and modify Charts, work with Flash Fill, work with subtotals and outlining, and learn how to customize the Excel environment.

With nearly 10,000 training videos available for desktop applications, technical concepts, and business skills that comprise hundreds of courses, Intellezy has many of the videos and courses you and your workforce needs to stay relevant and take your skills to the next level. Our video content is engaging and offers assessments that can be used to test knowledge levels pre and/or post course. Our training content is also frequently refreshed to keep current with changes in the software. This ensures you and your employees get the most up-to-date information and techniques for success. And, because our video development is in-house, we can adapt quickly and create custom content for a more exclusive approach to software and computer system roll-outs.

This course aligns with the CAP Body of Knowledge and should be approved for 4 recertification points under the Technology and Information Distribution content area. Email info@intellezy.com with proof of completion of the course to obtain your certificate.

Who this course is for:

Students who want to take their Excel knowledge to the next level.

Excel 2016 Intermediate

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 1min

Customizing Excel6 lectures • 21min

Linking Workbooks and Worksheets3 lectures • 13min

Working with Range Names and Advanced Formulas3 lectures • 12min

Analyzing Data with Common Logical Functions5 lectures • 19min

Analyzing Data with Common Lookup Functions3 lectures • 12min

Sorting and Filtering Range Data3 lectures • 9min

Analyzing and Organizing with Tables9 lectures • 23min

Using Conditional Formatting6 lectures • 17min

Outlining with Subtotals and Grouping3 lectures • 6min

Requirements

Description

Who this course is for: