Mikeo's Holistic Practical & Complete Excel: Intermediate
4.2 (2 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
15 students enrolled

Mikeo's Holistic Practical & Complete Excel: Intermediate

Learn the intermediate capabilities of Microsoft Excel 2016 (2010 & 2013 also)
4.2 (2 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
15 students enrolled
Created by Michael ONeill
Last updated 6/2018
English
English [Auto-generated]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 7.5 hours on-demand video
  • 4 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Students will be able to build complex tools for data input, calculation, and analysis without using any programming.
  • Learn to create complex tables and conduct what if analysis.
  • Handle Graphics and object correctly inside an Excel spreadsheet.
  • The class will design formula based interfaces that automate a golf course registration system as well as a lottery payout model.
Requirements
  • This is the intermediate class for Microsoft Excel. Students need a copy of Excel 2010, 2013, or preferably 2016, or Office 365 subscription. Students should also understand references (relative and absolute) as well as using the Excel interface.
Description

Beyond basic Excel use, intermediate Excel introduces us to the world of complex formulas and equation solving. We will explore the top-rated functions that power users use to build financial models as well as automated tools.  In this course, we will build a golf course reservation system using only formulas (no coding macros) as well and build a large statistical model for distribution of funds for a huge lottery payout.

The golf reservations system will show you real-world uses for all the formulas, interfaces, and lookups.  You will learn shortcuts for developing models and systems and will get a deep understanding of how everything ties together into one system.  For many users, the intermediate training is more like advanced, but I break the technology down into bite-sized pieces to make it easy to understand and utilize.

Who this course is for:
  • Intermediate users of Excel who want to better understand formulas, scenarios, what if analysis, graphic objects, auditing, modeling, and printing features.
Course content
Expand all 13 lectures 07:32:12
+ Introduction
1 lecture 10:43

This class is an introduction to the intermediate training class of Mikeo’s Holistic, Practical, and Complete training series on Microsoft Excel.  There are four separate classes that make up the entire series of Excel training: Beginner, Intermediate, Advanced, and Visual Basic for Applications.  Each class is sold separately.  This class is the Intermediate class.

To get the most out of this class, students should have a basic understanding of Microsoft Excel.  They should understand the differences between Relative references and absolute references, be able to define named ranges, work with the bullet in Excel, and understand the ribbons and user interface inside of Excel.  These topics are covered in depth in the Beginner version of Mikeo’s Holistic, Practical, and Complete Excel training which is sold separately.

In the intermediate class we will learn about how to create simple tables, use advanced formulas, build custom interfaces using only formulas, learn about the help systems, object layering, graphic types,  what if analysis, and we will build financial models.

This class includes well over 7 hours of hands on content.

Preview 10:43
+ Intermediate Excel
10 lectures 06:24:49

Microsoft’s Help system is significantly more advanced than previous systems and is often overlooked by experienced Microsoft Excel users who do not realize the value the help system provides. 

In this section, we will explain the help system, and how it uses the Natural Language System to interpret user queries.  The help system can provide training videos, articles, community forums, and online forums where you can post questions and usually get answers quicker than call Microsoft support.  The help system will even show you exactly where on the ribbon commands are located.

Help also allows you to recommend features for future versions of Excel, get help on previous versions of Excel, and keep up to date on the latest product changes via online posts and blogs. 

Understanding the Help System
19:25

Learn how to define tables inside of Microsoft Excel. Defined tables allow easy formatting and filtering.  Learn to set up simple equations that allow easy calculation changes.  Learn about formats for tables and how styles and colors work together to create themes. Quickly format data to look professional in just a few steps. 

This lesson uses the MikeoTraining Pricing V2.xlsx demo file.

Creating Tables
43:20

Formulas are the key component in unleashing the awesome power of Microsoft Excel. This section will teach you how to find the right formulas, insert the right variables, and get back the results you need when you need them.  It is possible to build out an entire working interface using only formulas, with no coding required.  We will take what we learn with formulas and work to build out a Golf Course reservation system as a practical way to teach this topic.

Be sure to download the Formulas.xlsx file for a good and handy reference to some of the top most used formulas, as well as to see the prototype of the golf reservation system.

Formulas
01:19:31

This is module 1 that walks you step by step through the creation of the Golf Reservation System using only formulas with no coding. There is 4 Modules total that help you learn to build this system for yourself. 

Learn how to audit your formulas. Utilize defined names for friendly names. Run error checking on your spreadsheet. Utilize VLOOKUPS, Indexes, and Offsets to manipulate your information.  Create user interfaces for interactions.


Golf App Module 1
30:53

This is module 2 that walks you step by step through the creation of the Golf Reservation System using only formulas with no coding. There is 4 Modules total that help you learn to build this system for yourself.

This section uses the formulas.xlsx demo file from Lecture 4.


Here you will learn how to create input boxes and automatically fill them with possible choices.   Utilize conditional formatting as well as rules to ensure your data is correct.  Master Autofill and flash fill. Utilize Date functions in your solutions.

Golf Appe Module 2
42:57

This is module 3 that walks you step by step through the creation of the Golf Reservation System using only formulas with no coding. There is 4 Modules total that help you learn to build this system for yourself.

This section uses the formulas.xlsx demo file from Lecture 4.

Golf App Module 3
35:25

This is module 4 that walks you step by step through the creation of the Golf Reservation System using only formulas with no coding. There is 4 Modules total that help you learn to build this system for yourself.

This section uses the formulas.xlsx demo file from Lecture 4.


Learn about data validation and input handling.  Create feedback boxes and other visuals.  See the entire solution run coherently.

Golf App Module 4
36:52

Excel has amazing powers when it comes to finding missing variables.  Learn to use the What-if analysis tools to backward solve equations with goal seek, discover missing variables, lay out data tables, and build scenario comparison evaluations.   We teach you hot to use these tools to get the data your are missing.

This section uses the formulas.xlsx demo file from Lecture 4.


What If Analysis
24:56

This section ties together all the previous sections by teaching students how to make complex financial models using formulas.   Students will learn how to lay out their models to allow easy updating of their variables.  They will create rules that watch for key information to be entered into the tables, and will generate significant results as the model is updated.

This section uses two spreadsheets.  The first is Tims Lotto.xls which shows an unformatted financial model.  The second file, New Lotto 2.xlsx, is the same data as Tims Lotto, but it is arranged and organized into a quick and easy to use tool.

Making Models
37:14

This lecture teaches how to use Microsoft’s auditing tools to find errors in your formulas, and to see the dependent and predecessor formulas that are linked to your active cells.   Students will also learn how to print their spreadsheets using advanced options, and will be able to save not only to their desktops, but also out to the cloud.

Audit Print & Save
34:16
+ Optional Holistic Topics
2 lectures 56:40

This optional holistic section of the training explains how computers use graphic types to display images inside of Excel.  You will learn the difference between Bitmaps and Vector images : JPEGs, PNGs, TIFFs, GIFs, and other types.  You will see how computers use RGB values to display up to 16.8 million colors.  

File types also can determine file size.  You will understand how lessons learned in creating MP3s in music have been applied to graphic files as well to compress file sizes down to manageable levels.   To use shadow and 3D effects, you will need to understand transparency and how alpha levels explain the difference between GIFs and PNGs.

Lastly you will learn how fonts and maps are always drawn with vector images so they can scale infinitely.  You will learn how to load different fonts so they can be utilized within Excel.

Holistic: Graphic Types
37:37

This optional holistic section of content covers how objects inside of Microsoft Excel can be manipulated and layered together.  Learn how to paint backgrounds quickly and then add illustrations including pictures, shapes, icons, and other graphic objects.    Understand the “z-order” of layering, and then manipulate the formats, colors, shadows, rotation, and the layout of all your graphic objects.  You can even edit individual points on any object to morph it’s shape using bezier curves.   Lastly learn to group you objects together for easy layout and alignment.

Link to Excel artist:  https://www.youtube.com/watch?v=OrwBc6PwAcY

Holistic: Object Layering
19:03

Let's check to see how well you are retaining the information.

Content Retention Quiz
6 questions