Microsoft Office 2016 - Intermediate Excel

More Courses in Office Productivity

Buy Now

Microsoft Excel 2016 Intermediate: Customizing Views, Styles, and Templates

Learn how to add and customize templates and styles to really make Excel 2016 work for you
Overview/Description

Making the Microsoft Excel 2016 environment look exactly the way you want it to is easy. In this course, you will learn how to configure how you want a workbook and the work area itself to appear. You will also learn how to add and customize templates and styles to really make Excel 2016 work for you.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016

 

Expected Duration

1 Hours, 2 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

Customizing Views

  • start the course
  • modify default display options in Excel 2016
  • select workbook view options in Excel 2016
  • select window view options in Excel 2016
  • modify the Quick Access Toolbar layout in Excel 2016
  • modify the ribbon layout in Excel 2016
  • modify default column and row settings in Excel 2016

Applying Custom Styles and Templates

  • create and modify custom templates in Excel 2016
  • create custom color formats in Excel 2016
  • create and modify cell styles in Excel 2016
  • create custom form fields in Excel 2016

Practice: Visual Elements and Views

  • configure appropriate views, layout, and visual elements in Excel 2016

Microsoft Office 2016 Intermediate Excel: Creating Custom Visual Effects

Learn about applying and customizing themes and watermarks
Overview/Description

A picture may paint a thousand words but Microsoft Excel 2016 makes adding both words and pictures to your workbooks very straightforward. In this course, you will learn how to insert and style text boxes, shapes, and SmartArt. You will also learn about applying and customizing themes and watermarks.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016

 

Expected Duration

55 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

Shapes, Text Boxes, and SmartArt

  • start the course
  • add a text box to a Microsoft Excel 2016 worksheet
  • modify the look of a text box in Microsoft Excel 2016
  • add shapes and lines to a worksheet in Microsoft Excel 2016
  • insert SmartArt into a Microsoft Excel 2016 worksheet
  • edit SmartArt you have added to a Microsoft Excel 2016 worksheet

Practice: Customizing a Worksheet

  • add text, shapes, and SmartArt to a Microsoft Excel 2016 worksheet

Watermarks and Themes

  • use WordArt and images to create watermarks in Microsoft Excel 2016
  • apply a theme to a Worksheet in Microsoft Excel 2016
  • modify and save a theme in Microsoft Excel 2016

Splitting Text

  • convert text to columns in Microsoft Excel 2016

Practice: Visual Effects

  • apply a theme and a watermark to a Microsoft Excel 2016 worksheet

Microsoft Office 2016 Intermediate Excel: Working with Data

Learn about basic alphabetical and numerical sorts through color sorting and custom configured sorts
Overview/Description

Microsoft Excel 2016 gives you a number of options for sorting and organizing data. In this course, you will learn about basic alphabetical and numerical sorts through color sorting and custom configured sorts. The course also covers grouping, filtering, searching, and ensuring valid data is entered.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016.

 

Expected Duration

1 Hours, 5 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

Sorting Data

  • start the course
  • sort data alphabetically and numerically in Microsoft Excel 2016
  • sort data by cell or font color in Microsoft Excel 2016
  • configure a custom sort in Microsoft Excel 2016

Grouping Data

  • create data summaries using groups in Microsoft Excel 2016
  • calculate subtotals and grand totals in Microsoft Excel 2016

Filtering Data

  • filter data in Microsoft Excel 2016
  • refine data filters in Microsoft Excel 2016
  • create a custom filter in Microsoft Excel 2016

Data Validation

  • implement basic data validation in a Microsoft Excel 2016 worksheet
  • apply advanced data validation in Microsoft Excel 2016

Searching Data

  • search a workbook to find and replace data in Microsoft Excel 2016

Practice: Organizing Data

  • sort, group, filter, and validate data in Microsoft Excel 2016

Microsoft Office 2016 Intermediate Excel: Macros and Advanced Queries

Covers the options available when you are running macros, and also how to create your own custom macros
Overview/Description

Using macros in Excel 2016 lets you save time when performing repetitive tasks. This course covers the options available when you are running macros, and also how to create your own custom macros. You will also learn about advanced functions that allow you to perform calculations using date and time, how to look up data, and how to perform advanced conditional queries.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016

 

Expected Duration

1 Hours, 11 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

Macros

  • start the course
  • configure macro security settings in Microsoft Excel 2016
  • run a macro in Microsoft Excel 2016
  • record a simple macro in Microsoft Excel 2016
  • run a custom macro from an interface element in Microsoft Excel 2016

Practice: Working with Macros

  • create a macro in Microsoft Excel 2016

Date and Time Functions

  • use the NOW and TODAY functions in Microsoft Excel 2016
  • use the DATE function in Microsoft Excel 2016
  • use the NETWORKDAYS function in Microsoft Excel 2016

Transposing and Looking Up Data

  • use TRANSPOSE to alter a worksheet in Microsoft Excel 2016
  • look up data using the VLOOKUP and HLOOKUP functions in Microsoft Excel 2016
  • look up data using the LOOKUP function in Microsoft Excel 2016

Functions in Formulas

  • use the IF function with other functions in Microsoft Excel 2016
  • use SUMIFS, AVERAGEIFS, and COUNTIFS in Microsoft Excel 2016

Practice: Advanced Functions

  • perform advanced queries in Microsoft Excel 2016

Microsoft Office 2016 Intermediate Excel: PivotTables and Advanced Charts

PivotTables and PivotCharts let you dynamically reorganize and display your data
Overview/Description

In Microsoft Excel 2016, PivotTables and PivotCharts let you dynamically reorganize and display your data. They can summarize huge amounts of data by category without the need to input complex formulas or spend long hours manually reorganizing your spreadsheet. You can choose and change what you want to summarize. In this course, you will also learn how to use advanced chart features like trendlines and dual axis charts.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016

 

Expected Duration

52 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

PivotTables

  • start the course
  • create a PivotTable in Microsoft Excel 2016
  • modify the data in a PivotTable in Microsoft Excel 2016
  • alter the look of your PivotTable in Microsoft Excel 2016
  • filter PivotTable data with slicers in Microsoft Excel 2016

PivotCharts

  • use recommended charts to create a PivotChart in Microsoft Excel 2016
  • use PivotTable data to create a PivotChart in Microsoft Excel 2016
  • alter the data in a PivotChart in Microsoft Excel 2016

Advanced Charts

  • add trendlines to a chart in Microsoft Excel 2016
  • create a dual axis chart in Microsoft Excel 2016
  • create a chart template from an existing chart in Microsoft Excel 2016

Practice: PivotTables and PivotCharts

  • present data in PivotTables and PivotCharts

Microsoft Office 2016 Intermediate Excel: Share, Review, and Collaborate

Learning how to share your work with others, and collaborate on Excel projects
Overview/Description

Microsoft Excel 2016 provides a number of tools that allow you to share your work with others, and collaborate on Excel projects. With your Microsoft account, you can access services that facilitate online collaboration, and link to data in other workbooks and even databases. In this course, you will also learn about reviewing Excel documents and how to convey your thoughts on a document to others.

 

Target Audience

End users at all levels, including those who have little or no familiarity with Microsoft Excel and more experienced Excel users who want to learn about the new interface and features in Microsoft Excel 2016

 

Expected Duration

1 Hours, 2 minutes

Prerequisites

None

Expertise Level

Intermediate

 

Objectives

Sharing Your Work

  • start the course
  • sign in to Microsoft Excel 2016 and add a OneDrive service
  • share and publish your work in Microsoft Excel 2016

Working With Others

  • work with others on the same document in Microsoft Excel 2016
  • link to data in another Workbook in Microsoft Excel 2016
  • add and manage connections to an Access database in Microsoft Excel 2016
  • apply different levels of protection to your Microsoft Excel 2016 Workbook
  • manage multiple Workbooks in Microsoft Excel 2016

Reviewing Workbooks

  • manage changes in a Microsoft Excel 2016 Workbook
  • add and modify comments in a Microsoft Excel 2016 workbook
  • run a spellcheck on a Microsoft 2016 Workbook

Practice: Collaborating and Reviewing

  • share and review a workbook in Microsoft Excel 2016