Course Code: exa365
Duration: 14 hours
Prerequisites:

To ensure success, students should have practical, real-world experience creating and analyzing datasets using Excel 365. Specific tasks students should be able to perform include:

  • creating formulas and using Excel functions;
  • creating, sorting, and filtering datasets and tables;
  • presenting data by using basic charts;
  • creating and working with PivotTables.

To meet these prerequisites, students can take the following courses, or should possess the equivalent skill level:

Excel 365 Introduction

Excel 365 Intermediate

Overview:

This course builds upon the foundational and intermediate knowledge presented in the courses Excel 365 Introduction and Excel 365 Intermediate to help you get the most of your Excel experience. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.

Format of the Course:

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.

Course Customization Options

  • To request a customized training for this course, please contact us to arrange.

Audience:

Anyone looking to enhence their existing Microsoft Excel 365 skills.

By the end of this training, participants will know

  • how to customize Excel environment
  • to organize and manage large amounts of data
  • to import, connect and shape external data
  • how to use PivotTables and PivotCharts
  • to automate their work with conditional formatting and macros
Course Outline:

Adjustment of the working environment

  • Hotkeys, facilities
  • Create and modify toolbars
  • Options excel (autosave, input, etc.).
  • Options paste special (transpose)
  • Formatting (style, format painter)
  • Tool - go to

Organization of information

  • Management sheets (naming, copying, color change)
  • Assign and manage the names of cells and ranges
  • Protect worksheet and workbook
  • Secure and encrypt files
  • Collaboration and track changes, comments
  • Inspection sheet
  • Create your own templates (charts, worksheets, workbooks)

PivotTables and PivotCharts

  • Create a PivotTable
  • Analyze PivotTable Data
  • Present Data with PivotCharts
  • Filter Data by Using Timelines and Slicers

Database management (list)

  • Data consolidation
  • Grouping and outlining data
  • Sorting data (over 4 columns)
  • Advanced data filtering
  • Database Functions
  • Subtotal (partial)
  • Tables and Pivot Charts

Cooperation with other applications

  • Get External Data (CSV, TXT)
  • OLE (static and link)
  • Web Queries
  • Publication sheets on site (static and dynamic)
  • Publication PivotTables

Automation of work

  • Conditional Formatting
  • Create your own formats
  • Check the validation

Macros - automation tasks in sheets

  • Registration macros
  • Understanding and modifying the code macros (VBA items)
  • Connecting the interface macros or workbook
  • Create forms on screen or printed