Microsoft Excel 2016 Advanced training course outline

Home PageTraining CoursesExcel Training Courses

Excel Training CoursesThis one-day course builds on the skills and concepts taught in Excel 2016: Intermediate.

Students will work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, how to import and export data, and how to query external databases. Finally, students will learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, and sharing Excel data via the Web. Delivered in AberdeenEdinburghStirlingGlasgowFifeLondonAberdeenshireDundeeAngusPerth and most UK locations.
Contact us for further details.

Course objectives

After completing this course, students will know how to:

  1. Use the IF and IFERROR functions to calculate a value based on specified criteria; use conditional functions to summarize data; use the PMT function to calculate periodic payments for a loan; use text functions to extract data strings; use date functions to calculate duration in years, months, and days; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
  2. Use the VLOOKUP and HLOOKUP functions to find values in worksheet data; use the MATCH function to find the relative position of a value in a range; use the INDEX function to find the value of a cell at a given position in a range; and use data tables to project values.
  3. Use the Data Validation feature to validate data entered in cells; and use advanced filter options to display the data you specify.
  4. Format data points in charts; create combination charts and trendlines; insert sparklines; use chart templates; and add and modify drawing objects and shapes.
  5. Create a PivotTable for analyzing and comparing large amounts of data; modify the PivotTable view by using slicers to filter data and by rearranging fields; improve the appearance of a PivotTable by changing its field settings and applying a style; and create a PivotChart to graphically display data from a PivotTable.
  6. Export data from Excel to other formats, and import data from a text file into an Excel workbook; and use Microsoft Query and the Web query feature to import data from external databases.
  7. Use the Goal Seek utility to meet a target output for a formula by adjusting the values in the input cells; and create scenarios to save various sets of input values that produce different results.
  8. Run a macro to perform tasks automatically; record macros; assign a macro to a button in the worksheet; edit a macro by editing VBA code.

Course contents

Advanced functions and formulas
Logical functions
Conditional functions
Financial functions
Text functions
Date and time functions
Array formulas
Calculation options 

Lookups and data tables
Using lookup functions 
Using MATCH and INDEX 
Creating data tables 

Advanced data management
Validating cell entries 
Advanced filtering 

Advanced charting
Chart formatting options 
Combination charts 
Graphical objects 

PivotTables and PivotCharts
Working with PivotTables 
Modifying PivotTable data 
Formatting PivotTables 
Using PivotCharts 
PowerPivot

Exporting and importing data
Exporting and importing text files 
Getting external data 

Analytical tools
Goal Seek 
Scenarios 
Instant data analysis

Macros and Visual Basic
Running and recording a macro 
Working with VBA code 

Prerequisites

Before taking this course, you should be familiar with personal computers and the use of a keyboard and a mouse. Furthermore, this course assumes that you've completed the following courses or have equivalent experience: Excel 2016: Basic and Excel 2016: Intermediate.

Target Audience

You should have some experience with Excel 2016 and should be familiar with intermediate-level tasks, such as sorting data, linking worksheets, and outlining and consolidating data. You'll get the most out of this course if your goal is to become proficient in performing advanced tasks, such as creating nested functions, working with data tables, exporting and importing data, performing what-if analyses, and recording macros.

Courseware

A course manual together with supporting exercise files will be provided and delegates will find this valuable for further study, revision and reference.

Certification Track

For comprehensive training, you should complete all of the following courses: Excel 2016: Basic, Excel 2016: Intermediate, and Excel 2016: Advanced.

Beyond Advanced

Microsoft Excel 2016 Power User

Excel VBA

What's New in Excel 2016

Excel 2016 for Windows has all the functionality and features you're used to with some added features and enhancements and the best new features from Office 2016. Here are some of the top new and improved features for Excel 2016:

Six new chart types

Visualizations are critical to effective data analysis as well as compelling storytelling. In Excel 2016, we've added six new charts—with the same rich formatting options that you are familiar with—to help you create some of the most commonly used data visualizations of financial or hierarchal information or for revealing statistical properties in your data.
Click Insert Hierarchy Chart on the Insert tab to use the Treemap or Sunburst chart, click Insert Waterfall or Stock Chart for Waterfall, or click Insert Statistical Chart for Histogram, Pareto, or Box and Whisker. 
Or click Recommended Charts > All Charts to see all the new charts.

Get and transform (Query)

Before analysis can begin, you must be able to bring in the data relevant to the business question you are trying to answer. Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data—allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel. Access them from the Get & Transform group on the Data tab.

Get and transform (Query)

Before analysis can begin, you must be able to bring in the data relevant to the business question you are trying to answer. Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data—allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel. Access them from the Get & Transform group on the Data tab.

One click forecasting

In previous versions of Excel, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (such as, FORECAST.ETS() …). This functionality is also available as a new one-click forecasting button. On the Data tab, click the Forecast Sheet button to quickly create a forecast visualization of your data series. From the wizard, you can also find options to adjust common forecast parameters, like seasonality, which is automatically detected by default and confidence intervals.

3D Maps

Our popular 3D geospatial visualization tool, Power Map, has been renamed and is now available to all Excel 2016 customers and is built into Excel. This innovative set of storytelling capabilities has been renamed 3D Maps and can be found along with other visualization tools by clicking 3D Map on the Insert tab.

PivotTable enhancements

Excel is known for its flexible and powerful analysis experiences, through the familiar PivotTable authoring environment. With Excel 2010 and Excel 2013, this experience was significantly enhanced with the introduction of Power Pivot and the Data Model, bringing the ability to easily build sophisticated models across your data, augment them with measures and KPIs, and then calculate over millions of rows with high speed. Here are some of the enhancements we made in Excel 2016, so that you can focus less on managing your data and more on uncovering the insights that matter.

Automatic relationship detection discovers and creates relationships among the tables used for your workbook’s data model, so you don’t have to. Excel 2016 knows when your analysis requires two or more tables to be linked together and notifies you. With one click, it does the work to build the relationships, so you can take advantage of them immediately.
Creating, editing and deleting custom measures can now be done directly from the PivotTable fields list, saving you a lot of time when you need to add additional calculations for your analysis.

Automatic time grouping helps you to use your time-related fields (year, quarter, month) in your PivotTable more powerfully, by auto-detecting and grouping them on your behalf. Once grouped together, simply drag the group to your PivotTable in one action and immediately begin your analysis across the different levels of time with drill-down capabilities.

PivotChart drill-down buttons allow you to zoom in and out across groupings of time and other hierarchical structures within your data.

Search in the PivotTable field list helps you get to the fields that are important to you across your entire data set.
Smart rename    gives you the ability to rename tables and columns in your workbook’s data model. With each change, Excel 2016 automatically updates any related tables and calculations across your workbook, including all worksheets and DAX formulas.

Multiple usability improvements have also been made. For example, delayed updating allows you to perform multiple changes in Power Pivot without the need to wait until each is propagated across the workbook. The changes will be propagated at one time, once the Power Pivot window is closed.
 
Multi-select slicer
Now you can select multiple items in an Excel slicer on a touch device. This is a change from prior versions of Excel where only one item in a slicer could be selected at a time using touch input. You can enter Slicer multi-select mode by using the new button located in the Slicer’s label.

Publish and share your analysis with Power BI

A report is not complete without being able to share it with the right people. Once you’re finished preparing your data analysis, you can share it with your workgroup or clients through Power BI with just one button. Once published to Power BI, use your data models to quickly construct interactive reports and dashboards. With Excel Online support built into Power BI service, you can also display your fully formatted Excel worksheets as well.

Quick Shape Formatting

This feature increases the number of default shape styles by introducing new “preset” styles in Excel.

Insert pictures with the correct orientation

With automatic image rotation, once you insert an image into Excel, it automatically rotates the picture to match the camera’s orientation. You can manually rotate the image to any position after insertion. Note that this only affects newly inserted images and does not apply to pictures in existing documents.

Do things quickly with Tell Me

You'll notice a text box on the ribbon in Excel 2016 that says Tell me what you want to do. This is a text field where you can enter words and phrases related to what you want to do next and quickly get to features you want to use or actions you want to perform. You can also choose to get help related to what you're looking for, or perform a Smart Lookup on the term you entered.


Insights into what you're working on

The Insights pane, powered by Bing, offers more than just definitions. When you select a word or phrase, right-click it, and choose Smart Lookup, the insights pane will open with definitions, Wiki articles, and top related searches from the web. You can also get to Smart Lookup any time by going to Review > Smart Lookup and entering a query there.

Ink Equations

Including math equations has gotten much easier. Now, you can go to Insert > Equation > Ink Equation, any time you want to include a complex math equation in your workbook. If you have a touch device, you can use your finger or a touch stylus to write math equations by hand, and Excel will convert it to text. (If you don't have a touch device, you can use a mouse to write, too). You can also erase and select and correct what you've written as you go.

Simpler sharing

Choose Share on the ribbon to share your spreadsheet with others on SharePoint, OneDrive, or OneDrive for Business.
These changes bring together two key aspects of collaboration: who has access to a given document and who is currently working with you on the document. Now you can view both pieces of information in one place from the Share dialog box.

 Improved version history

Now you can go to File > History to see a complete list of changes that have been made to your workbook and access earlier versions.  
Note   This feature is only supported for files stored on OneDrive for Business or SharePoint.

New themes

There are now three Office themes that you can apply : Colorful, Dark Gray, and White. To access these themes, go to File > Options > General, and then click the drop down menu next to Office Theme.

Data Loss Protection (DLP) in Excel

Data Loss Protection (DLP) is a high-value enterprise feature that is well loved in Outlook. We are introducing DLP in Excel to enable real time scan of content based on a set of predefined policies for the most common sensitive data types (e.g., credit card number, social security number, and US bank account number). This capability will also enable the synchronization of DLP policies from Office 365 in Excel, Word, and PowerPoint, and provide organizations with unified policies across content stored in Exchange, SharePoint and OneDrive for Business.  

Take an Excel 2016 course to discover more!