What You Will Learn In This Course:
Using Microsoft Excel 2 Modules
CLC 0202 (Advanced)

Macros

You will learn what macros are and what they’re used for, and then create your own macro.

Enabling Data Analysis Tools

Excel's Data Analysis Tools can be useful for complex statistical or engineering analyses. However, by default, the tools are disabled, so we cover how to enable them.

VLookup & HLookup

You will learn what the VLookup and HLookup functions are used for, and then practice using them in a worksheet.

PivotTables, Charts, and Filtering Data

You will use Pivot Tables to make summary reports, format the reports, apply filters, and make a chart.

Protecting Workbooks

You will learn about the options Excel has for protecting workbooks, and then use some of those options on a workbook.

Intro to What-If Analysis

You will learn what a What-If Analysis is and what it's used for, and then perform a What-If Analysis using some of the tools available in Microsoft Excel.

Goal Seek

You will use the Goal Seek tool to quickly pinpoint exact values for formula inputs to answer complex analysis questions.

Histograms

You will learn what histograms are, and then use Excel's Histogram tool to create one.

Data Table

The Data Table tool can be used to quickly run, record, and compare what-if analyses based on a range of values for one or two of the input cells. In this tutorial, you will use this tool to create one-input and two-input data tables.

Scenario Manager

The Scenario Manager can be used to record and compare entire what-if analyses. This means that, unlike data tables, the Scenario Manager allows you to record values for all input cells. In this tutorial, you'll learn how to use the Scenario Manager to create, edit, and consolidate scenario analyses, as well as generate summary reports for side-by-side comparison.

Solver

The Solver is an Excel add-in developed by Frontline Systems—a company that specializes in advanced analytics. It improves on the Goal Seek tool, allowing users to receive multiple input values in order to reach a specified result. In this tutorial, you'll learn how to recognize what problems are appropriate for the Solver, as well as how to set up the Solver for meaningful analysis.

Sort and Filter

In order to organize and find specific information from mass amounts of data you need to understand how to use the Sort and Filter features in Excel. In this video you will be taught how to use the Sort tool to rearrange a data set a few different ways: alphabetically, numerically, and then both ways sequentially. Excel has some preset sort functions that can speed things up, so we’ll explore that option as well and specify custom sort functions. We’ll do this in columns of our data set, as well as demonstrate how it could be done in rows. Next, we’ll move on to the Filter tool. We’ll use it to hide some information in our data set so it’s easier to manage, then we’ll look at how to update information in our data set after it’s been filtered.

Importing Data

There are times you will need to import data from sources outside of Excel. In this tutorial we will go through the steps of importing data from plain text files, comma-separated value files, and Microsoft Access databases.

Export to Other Office Applications

Exporting data is necessary when you want to utilize the workbook-like data construction that Excel offers in other Microsoft Office applications. You can export graphs, charts, and even the main cells of your workbook. Exporting can be as simple as copying and pasting the information, but in this tutorial we are also going to demonstrate how you can link the data in a way that allows you to update your Excel file. This way, the other file where the data is located can be easily updated as well.