MMSTECH039: Microsoft Office Excel 2013 Advanced Essentials

Microsoft Excel 2013 Advanced Essentials

 

Microsoft Excel 2013 has lots of time-saving tools. Do you know how to make the most of them? The modules in the course will show you how! This advanced course covers a wide range of topics, including advanced formula tasks, working with named ranges, using advanced functions, resolving formula errors, managing data, outlining and grouping data, working with scenarios, using Solver, the Analysis ToolPak, PivotTable features, using PowerPivot, and developing macros.

 

Advanced Formula Tasks

Formulas are at the core of how Excel works. This module gives students insight into using relative and absolute cell references, multiple cell references, 3D references, and array formulas.

 

Working with Named Ranges

This module is all about named ranges. Students will learn what named ranges are as well as how to define, edit, and delete them. Students are also shown how to use named ranges in formulas.

 

Using Advanced Functions

Applying the correct functions to data can save a lot of work and this module shows students how to accomplish that. The functions covered in this module include PMT, FV, VLOOKUP, and HLOOKUP, as well as IF, AND, OR functions.

 

Resolving Formula Errors

Mistakes happen; it’s a fact of life! Luckily, Excel offers some tools to fix formula errors. This module covers how to trace formula precedents and dependents, show and evaluate formulas, set error checking options, use error option buttons, and run an error check.

 

Managing Data

It’s no good to have vast amounts of data if you cannot handle it efficiently. This module focuses on how to consolidate data, remove duplicate values, configure data validation, transpose data, and convert text to columns.

 

Outlining and Grouping Data

This module builds upon skills taught in the previous module with a continued look at data. Here, students will learn how to outline data, show and hide details of outlined data, group data, create subtotals, and remove outlining and grouping.

 

Creating Scenarios

Scenarios are useful to simulate data results based upon certain circumstances. This module teaches how to create, load, merge, edit, and delete scenarios, and how to create a scenario summary report.

 

Using Solver

Solver is an Excel add-in that can do a lot of the heavy lifting when it comes to dealing with a data problem. In this module, students are shown how to set up a worksheet for Solver, run Solver, generate reports and scenarios, modify Solver constraints, and set Solver options.

 

Analyzing Data

The Analysis ToolPak is another useful Excel add-in. This module shows students how to enable and use the Analysis ToolPak, how to use Goal Seek, and how to use data tables.

 

Advanced PivotTable Features

With this module, students get a look at using many features of PivotTables. The discussion covers how to create a basic PivotTable and PivotChart, use the PivotTable Fields pane, add calculated fields to a PivotTable, and sort and filter pivoted data.

 

Using PowerPivot

At the end of this module, students will know how to enable PowerPivot, import Access and Excel data into PowerPivot, integrate data with relationships, and create a PivotTable with PowerPivot data.

 

Using Macros

Macros are a wonderful time-saving device in Excel 2013. This concluding module teaches students how to record a macro, write a macro using the Visual Basic Editor, edit a macro, and run a macro.