Course Offerings

Intermediate Microsoft Excel 2019

Course ID: OST-3100ADE
Overview
Course Outline
Requirements
Intended Audience
More Details

Many businesses rely on spreadsheets to manage budgets, schedules and tracking. Microsoft Excel is considered the industry standard for spreadsheets. If you use Excel regularly and want to learn more advanced functions in this powerful software, this course is for you. You learn how to harness the power of Excel's data-analysis tools and AutoFilter commands and how to create macros that eliminate repetitive tasks. Set yourself apart from the casual Excel user by adding VLOOKUP, INDEX & MATCH and other intermediate functions to your professional skill set. In addition, you learn to create macros that let you manipulate data with the push of a button. You also discover how to use Goal Seek and Solver and apply them to real-world problems.

Course Objectives

Become a more productive member of your organization

Build an indispensable skill set

Learn to fully harness the power of Microsoft Excel 2019

Outline of Instruction

Excel Charts and Graphs
Discover why Excel is such a powerful and user-friendly charting tool. To you, a workbook's numbers might tell an interesting story. But to others, that story might not be quite so obvious; they might just see plain old numbers. So, find out how to choose the right chart for telling your story and then how to create, format and edit your chart to help others clearly visualize that story.

More Excel Charts
With so many great charting features and enhancements in Excel 2019, there's no way you could study them all in a single lesson. Continue exploring Excel's charting options, looking at the lesser-known options available to you. Even though they aren't well known, these options can add tremendous value to your worksheets in the right situations.

Auto Filter and Sorting
Working with data in Excel can be quite easy – and sometimes even fun – when you know about the extra tools that are available once you have your data arranged in a table format. One of these great tools is the Auto Filter command. Learn how to use Auto Filter to limit your table information to just the records you want. Not only does it allow for finding exact matches, but it can also filter and sort based on cell shading or font color.

Goal Seeking
Normally, you use your Excel workbooks as a place to enter formulas and get answers. But what if the formula isn't returning the answer you want? If you know what you want, but you just don't know what formula will get you there, then Excel's Goal Seek is exactly what you need. This tool helps you avoid the trial-and-error approach that most Excel users go through to get to the right answer. Try multiple examples, exploring several ways to apply this great feature.

Pivot Tables
Learn how to use one of the best features of Excel: the PivotTable. There's no greater what-if analysis tool to summarize, reorganize and report data. When you practice creating a PivotTable, you discover how "pivoting" your data helps you gain valuable insights by seeing the same information from a different perspective.

Advanced PivotTables
Just like charting, Excel's PivotTables are too big a topic to fit into one lesson. Take PivotTables to the next level by taking a tour through some of Excel's more advanced techniques. Learn how quick and easy it is to group your data to give your PivotTable even more power. Then, find out how a PivotTable can serve as your source and inspiration for some really nice charts, specifically, the PivotChart. Finally, become an expert in using the Excel PivotTable filtering tool called Slicer.

Solver
Have you ever worked with a financial model in Excel and wondered how you could speculate different outcomes by changing different input amounts? The Excel Solver can do just that. Learn how to use Solver to solve a complex problem based on the criteria and constraints you provide.

Sparklines
There's nothing more exciting than learning how to master a useful and eye-catching feature in Excel. Explore in detail how to create all three types of Sparklines, as well as formatting options for each. By the end of the lesson, you will have the skills to create dashboard-like mini-charts sure to make you the envy of your office.

Macros
Find out how to use macros to turn boring, repetitive, time-consuming tasks into automated Excel functions. Tired of doing the same formatting on the same report month after month? Here's your chance to see how to record a macro that performs that time-consuming task in seconds with just one click of a button.

Introduction to Functions
If you've used Excel for a while, you know there are hundreds of functions at your disposal. Get a quick overview of all the categories of functions and the different methods you can use to create them. Then, ease into creating various functions using some from the Text category.

Math/Trig Functions
Dig a little deeper into using some of the slightly more complex functions in the Math & Trig category. Specifically, look at two popular choices, the SUMIF and COUNTIF functions, and discuss how to use SUMIFs.

VLOOKUP, INDEX and MATCH Functions
Learn how to use the extremely popular VLOOKUP function. Also, use the INDEX and MATCH functions to do something just short of amazing.

Contact Hours

CEUs

Industry Standard, State or National Certification

Certification

None

Website

None

Certification Learning Outcomes/Requirements

CE or CU Articulation

Prerequisites

Introduction to Microsoft Excel 2019/365, if needed.

Learning Supplies Needed

Hardware requirements:
This course must be taken on a PC. It is not suitable for Macs or Chromebooks.

Software requirements:
PC: Windows 10 or later
Browser: The latest version of Google Chrome or Mozilla Firefox is preferred. Microsoft Edge is also compatible.
Microsoft Excel 2019 (desktop version), available with the desktop version of Microsoft 365 or Microsoft Office Home and Student 2019 (not included in enrollment)
Adobe Acrobat Reader
Software must be installed and fully operational before the course begins.

Other:
Email capabilities and access to a personal email account

Instructional material requirements:
The instructional materials required for this course are included in enrollment and will be available online.

Clinical Site/Special Facilities

N/A

Requirements for Successful Completion

90% attendance
Students must pass 10 quizzes with a minimum grade of 70% by the second attempt. If all quizzes are not passed with 70% or better by the second attempt, the final exam score must be 70% or higher on the first attempt. The final exam must be taken by the Wake Tech course end date.