Simon Sez IT

Build on the Excel skills you already have and take your expertise to new heights with this advanced-level course on Excel 2019. You will deepen your understanding of more advanced Excel features by delving into high-level consolidation, analysis and reporting of financial information.

Use the expert insight you gain from this course to tackle problem-solving differently. When you master Excel’s myriad of functions, you're equipped to successfully complete every type of task, from the most practical to the most complex. The only prerequisite for this course is an intermediate-level knowledge of Excel.

Prerequisites of the Course and Version of Excel

Course Structure

<a href="https://ssi-exercise-files.s3.amazonaws.com/office-2019/Excel-2019-Advanced/Excel+2019+Advanced+Exercise+Files.zip">Click here to download the exercise files to accompany this course</a>

Course Exercise Files

<a href="https://ssi-exercise-files.s3.amazonaws.com/office-2019/Excel-2019-Advanced/Excel+2019+Advanced+Course+Files.zip">Click here to download the demonstration files to accompany this course</a>

Course Demonstration Files

Exercise Files

The transcription consists of the entire course transcribed word-for-word, organized by chapter, and with a Table of Contents. This downloadable &amp; printable transcript provides another learning option - if you prefer reading but don’t have time to take notes.

DOWNLOAD ME: Course Transcript

Chapter 1: Introduction to Excel 2016 Advanced Course

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The instructor explains that functions are the backbone of Excel and demonstrates how to access the help topic on functions. He then shows how to use the SUM function to add up numbers in Excel, including using cell references or a range of cells as arguments. The video also covers the rules to follow when using formulas and demonstrates how to work out the value of pi and add it to the sum of numbers using nested functions. The video concludes by mentioning that the next section will focus on more sophisticated utilities.

Functions - Part 1

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video focuses on functions in Excel and covers the tools that help users find the functions they need, such as the Formulas tab and the "More functions" drop-down menu. The instructor demonstrates how to use the Insert Function dialogue box to search for functions and how to use the function arguments dialogue to enter arguments for a function. The video also covers the Formula Auto Complete feature and previews the next section, which will focus on the Auto Sum functions. Overall, the video provides a comprehensive overview of the tools and features available for using functions in Excel 2019.

Functions - Part 2

This video is part of the Microsoft Excel 2019 Advanced course and is targeted towards individuals who want to gain an in-depth understanding of the advanced features of Excel. The video tutorial focuses on the Autosum function in Excel 2019 and how it can be used to add up numbers in a column or row without selecting the cells first. The instructor demonstrates how to use Autosum on both the Home and Formulas tabs, and how it can identify entities that are not part of the calculation and discard them. The video also shows how to use other functions like count, min, and max with Autosum, and ends with a tip on how to add frequently used functions to the Quick Access toolbar for easy access.

Autosum

Chapter 2 Quiz

Chapter 2: Functions

This video is a part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video covers the most useful features of Excel that are essential for high-level consolidation analysis and reporting of financial information. By the end of the course, learners will be equipped with the skills to use Excel effectively for complex financial analysis.

How Date and Time Works

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The instructor focuses on the use of date and time functions in Excel 2019, assuming that the viewer is familiar with formatting. The instructor uses the example of creating a company invoice to demonstrate the use of date and time functions, including the Today function, the NOW function, and the Date Value function. The instructor also explains how to build dates using the date function and how to add days to a date.

Basic Date and Time Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of advanced features of Microsoft Excel. The video tutorial covers advanced Excel 2019 functions for working with dates and times. The instructor demonstrates how to add hours and minutes to a date and time using the time function, and introduces the workday function, which calculates the number of working days from a given date, taking into account holidays. The video also covers finding the difference between two dates using various functions. The speaker encourages viewers to refer to the help feature to learn more about the many date and time functions available in Excel.

Complex Date and Time Functions

Chapter 3 Quiz

Chapter 3: Date and Time Functions

This video is part of the Microsoft Excel 2019 Advanced course and is a tutorial on using text functions in Excel 2019. The target audience is individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The instructor demonstrates how to use text functions to tidy up employee data in a text file and structure it neatly in an Excel workbook. The video covers the use of the Proper, Find, Len, Left, Right, Mid, and Concatenate functions. The instructor also provides tips on hiding columns with formulas and saving the file frequently.

Using Text Functions - Part 1

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video focuses on the use of text functions in Excel 2019, specifically the SUBSTITUTE function. The instructor demonstrates how to use the function to replace specific text within a cell with new text and provides step-by-step instructions and examples for each function and formula used. The video also introduces other text functions that can be used in Excel and recommends reviewing the help documentation for more information.

Using Text Functions - Part 2

Chapter 4 Quiz

Chapter 4: Text Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video focuses on logical functions in Excel 2019, which are important in evaluating values of cells and ascertaining what to do in any given situation based on varying circumstances. The instructor demonstrates the use of the IF function, IF S function, and SWITCH function, and shows how to embed an IFS statement in an IF statement to check for valid dates. Logical functions will be used extensively in the coming sections of the course.

Using Logical Functions

Exercise 01

Chapter 5 Quiz

Chapter 5: Logical Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The instructor explains the concept of lookup and reference functions, specifically focusing on the choose function, HLOOKUP, and VLOOKUP functions. The video demonstrates how to use these functions to look up values in a table, determine quartiles, and avoid error values using the IF ERROR function. The video also explains how to use the fill-down function to apply the HLOOKUP function to all scores in the set.

HLOOKUP Function

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The instructor focuses on the V Look Up function, which is used to look up a vertical list of values in Excel. The instructor demonstrates how to use the V Look Up function to develop an invoicing system, using a catalog of parts to look up descriptions and prices. The video also covers how to use the if NA function and if statement to display relevant messages. Viewers are encouraged to use the V Look Up function and other Excel functions to improve their invoicing system.

VLOOKUP Function

Exercise 02

Chapter 6 Quiz

Chapter 6: Lookup Functions

This video is part of the Microsoft Excel 2019 Advanced course, aimed at individuals who want to enhance their knowledge of Excel's advanced features. The video covers the most useful advanced features of Excel, including high-level consolidation analysis and reporting of financial information. By the end of the course, learners will have a comprehensive understanding of Excel's advanced capabilities and be able to apply them to their work.

Financial Functions and Terminology

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video focuses on the FV or Future Value function, which calculates the future value of a saving or investment based on payments made over a period of time. The instructor demonstrates how to use the FV function to create a retirement planner and also explains two other personal financial functions, the RRI function and the P duration function. The video concludes by mentioning that the next section will cover professional financial functions.

Personal Financial Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of advanced features of Microsoft Excel for financial analysis and reporting. The instructor discusses advanced financial functions in Excel 2019, specifically splitting payments on a loan between principal and interest repayments. The video covers the use of PMT, PPMT, and IPMT functions to calculate monthly payments, principal payments, and interest payments. The instructor encourages viewers to practice using these functions in different situations. The next section will cover a simple depreciation function.

Professional Financial Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of advanced features of Microsoft Excel for financial analysis and reporting. The instructor explains the concept of depreciation in business and demonstrates how to calculate depreciation using the straight-line method in Excel. The video covers the calculation of depreciation per year, salvage value, and the life of the equipment. The instructor also shows how to calculate the value at each of the five-year ends using a formula, emphasizing the use of absolute references. The video concludes with an exercise on different approaches to depreciation.

Depreciation

Exercise 03

Chapter 7 Quiz

Chapter 7: Financial Functions

This video is a part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video covers the most useful advanced features of Excel that are essential for high-level consolidation analysis and reporting of financial information. By the end of the course, learners will be able to use Excel to perform complex financial analysis and create professional reports.

Statistical Functions for Description - Part 1

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The instructor discusses measures of spread in statistics, which are necessary to analyze a statistical sample or to compare it with another sample. The video covers statistical concepts and terms, including quartiles, interquartile range, semi interquartile range, and standard deviation. The presenter uses two sets of sales figures for the Orlando and Tampa branches to demonstrate how to calculate these measures of spread and how to use descriptive statistics in analyzing data. The video concludes by stating that descriptive statistics are useful in analyzing data, and in the next section, forecasting will be discussed.

Statistical Functions for Description - Part 2

This video is part of the Microsoft Excel 2019 Advanced course and is targeted towards individuals who want to gain an in-depth understanding of advanced features of Microsoft Excel for financial analysis and reporting. The video is a tutorial on Excel 2019 Advanced functions for forecasting. The instructor explains how to use Excel to predict or forecast the value of a dependent variable (Y) when given an independent variable (X). The video covers three different methods to perform linear regression with a single dependent variable and a single independent variable. The video concludes by mentioning that there are more powerful functions that can not only predict values but also tell us how strong the relationship between X and Y is.

Statistical Functions for Forecasting - Part 1

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video is a tutorial on using the LINEST function in Excel 2019 for forecasting and linear regression. It explains the syntax of the LINEST function and how to use it, including how to use it with other functions to model relationships that are not straightforward linear relationships. The video also explains the concept of linear regression and how it works, including the coefficient of determination and how it is calculated.

Statistical Functions for Forecasting - Part 2

Exercise 04

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video focuses on the new feature in Excel 2019 called "one click forecasting," which is intended for time-based data. The video provides a straightforward example of how to use this feature, including how to set the forecast start and end dates, choose between a line graph or a column chart, and set the confidence interval and seasonality. The resulting forecast worksheet will show a graph with the historical data and the predicted values, as well as a table with the predicted values and the lower and upper confidence bounds.

One Click Forecasting

Statistical Functions for Inference

Chapter 8 Quiz

Chapter 8: Statistical Functions

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to learn how to link data from outside sources to Excel workbooks. The instructor explains the three scenarios where this is useful and demonstrates how to link data from another workbook and between separate workbooks. The video also covers how to avoid security warnings and prompts for updates when working with linked workbooks. The video concludes by mentioning that the next section will cover connecting Access databases.

Connecting to Other Workbooks

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at users who want to learn how to connect to data in Access databases from Excel 2019. The instructor uses the Esprit de Tour database to demonstrate how to create a live connection between Excel and Access databases, which allows users to link to information held in an Access database from an Excel workbook. The video explains how to set up an automatic data refresh in Excel from an Access database and how to refresh data when opening the file. The video concludes by mentioning that the next section will cover getting data from the web.

Connecting to Access Databases

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video focuses on connecting to data sources on the web using Excel 2019 Advanced. The instructor demonstrates how to get livestock market data from a website and put it into an Excel workbook. He explains how this feature is useful for getting the latest value of an index, exchange rate, or other data. The video concludes by introducing the next section, which will cover an example of get and transform.

Connecting to Web Data Sources

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain a deeper understanding of the advanced features of Excel. The video covers the most useful advanced features of Excel that are essential for high-level consolidation analysis and reporting of financial information. By the end of the course, learners will have a comprehensive understanding of Excel's advanced features and how to use them to improve their financial analysis and reporting skills.

Get and Transform

Exercise 05

Chapter 9 Quiz

Chapter 9: Connecting to External Data

This video is part of the Microsoft Excel 2019 Advanced course and is a tutorial on how to use tables in Excel 2019 Advanced. The target audience is individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel for high-level consolidation analysis and reporting of financial information. The video explains how to create tables from existing ranges on worksheets and introduces some of the features and functionality, including styling, filtering, and auto totaling of columns. The instructor also demonstrates how to use tables in the catalog sheet and concludes by showing how to remove duplicates and convert tables back to ranges.

Introduction to Tables

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The video focuses on working with tables in Excel and covers topics such as adding and removing rows and columns, resizing tables, and selecting specific data in a table using keyboard shortcuts and the mouse. The video provides useful tips and warnings, making it informative and helpful for those looking to improve their Excel skills.

Working with Tables

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video focuses on table referencing, which makes it easier to understand cells and formulas in a table. The instructor explains how to refer to elements within a table using a convenient referencing system and demonstrates how to refer to a specific cell inside the table from outside the table. The video ends with a promise to cover table styles in the next section.

Table References

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of advanced features of Excel for financial analysis and reporting. The tutorial focuses on customizing table styles in Excel 2019. The instructor explains how to duplicate an existing style and make fundamental changes to it using the Table Style Options group. The video also demonstrates how to create a new table style from scratch and convert a table back to a range while maintaining any styling that has been applied. This video is a useful guide for anyone interested in styling tables in Excel 2019.

Table Styles

Exercise 06

Chapter 10 Quiz

Chapter 10: Tables

This video is part of the Microsoft Excel 2019 Advanced course and is a tutorial on how to use pivot tables in Excel 2019. The target audience is individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel for high-level consolidation analysis and reporting of financial information. The instructor explains how to create a pivot table using data on sales in a cafe within a convenience store and demonstrates how to use the pivot table fields and drop zones to filter and set the data out in rows or columns. The video concludes by previewing the next section, which will cover filtering by dates. Overall, the video provides a clear and concise introduction to using pivot table filtering to create customized reports.

Introduction to Pivot Tables

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video is a tutorial on creating pivot tables in Excel 2019 Advanced. The instructor explains how to add fields to the pivot table, group date fields, and add text fields to rows or columns. He also demonstrates how to control the data and formatting of the pivot table using the Group Field option and other formatting tools. The video provides a basic introduction to working with pivot tables in Excel and is useful for anyone who wants to learn how to create and format pivot tables.

Working with Pivot Tables

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at intermediate to advanced Excel users. The tutorial covers how to filter pivot tables in Excel 2019 Advanced using slices. The instructor explains how to refresh a pivot table, access pivot table options, and use the right-click menu. He also demonstrates how to filter data by branch and date, and how to group data by months and quarters. The video provides useful tips and tricks for filtering pivot tables and is suitable for those who want to gain an in-depth understanding of the most business useful advanced features of Microsoft Excel.

Filters and Slicers

This video is part of the Microsoft Excel 2019 Advanced course and focuses on Pivot Charts. It is designed for individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel for high-level consolidation analysis and reporting of financial information. The video explains how to create a Pivot Chart from a Pivot Table, how to filter data, and how to use the Pivot Chart tools to set the chart style, add chart elements, and format the chart. The video also covers how to start from scratch with data by selecting the Pivot Chart and Pivot Table option from the Insert tab.

Pivot Charts

Exercise 07

Chapter 11 Quiz

Chapter 11: Pivot Tables

What If Analysis

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video provides a tutorial on how to use the Scenario Manager in Excel 2019 to manage scenarios with more than two variables. The presenter explains how to name the entities involved in the model, replace cell references with named ranges, and use the Scenario Manager to manage scenarios. The video provides a step-by-step guide on how to create, edit, merge, and summarize scenarios. The content is specific to the use of Excel and may not be useful for those who are not familiar with the program.

Scenario Manager

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The instructor explains how to use the Goal Seek function in Excel 2019, which is used to find the input value required to achieve a specific output value in a formula. The video includes examples of using Goal Seek to find the angle whose sign is 0.6 and to find the maximum amount that can be borrowed with a monthly payment of $200. The instructor also notes the limitations of Goal Seek and previews the next section on Solver.

Goal Seek

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The video is a tutorial on how to use Solver in Excel 2019 Advanced. Solver is a free add-in that allows users to deal with complex situations by varying many input variables and putting constraints on the solution. The video uses a practical example of a cafe that needs to employ staff to cater to the peaks and troughs in business. Solver is used to find the minimum number of staff to employ overall to cover the required number of staff. The video shows how to set up Solver by selecting the objective, the cells to change, and the constraints. The video concludes by challenging viewers to come up with a better solution using the same constraints.

Solver

Exercise 08

Chapter 12 Quiz

Chapter 12: Data Analysis

This video is part of the Microsoft Excel 2019 Advanced course and focuses on graphs and charts. The target audience is individuals who have a good knowledge of working with graphs and charts and want to learn about more esoteric types of charts. The instructor demonstrates how to create area charts, including 3D area charts and 3D stacked area charts, using a workbook called Team Monthly Sales. The video ends with a preview of the next topic, surface charts. By the end of the video, learners will have a better understanding of how to create and use different types of charts in Excel.

Area Charts

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The instructor explains how to create surface charts in Excel 2019, which are used to show the relationships between three variables. The video covers how to select data, insert a surface chart, and customize it for a specific application. The instructor also explains how to fix problems with the axes and covers wireframe 3D surface charts and contour charts. The next section will cover radar charts.

Surface Charts

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Excel. The instructor explains how to use radar charts in Excel 2019 to compare employee performance against six categories. The video demonstrates how to draw a radar chart that covers the company's standards and then adds the scores for two employees, Jenny Murray and Steve DuPont. The radar chart is a visual representation of the relative performance of the two employees, not only to each other but also against the company's standard. The video ends with a brief mention of other formatting options for radar charts and a preview of the next section on bubble charts.

Radar Charts

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of the advanced features of Microsoft Excel. The instructor explains how to create bubble charts, which are used to represent three variables, with the third variable represented by the size of the symbol in the chart. The instructor uses 2015 data on the population and GDP per person of major countries in the world to demonstrate how to create a bubble chart. Different chart styles and types of bubble charts are discussed, including 3D bubble charts. The instructor also shows how to format the axis and labels, and how to move bubbles around to avoid overlap.

Bubble Charts

This video is part of the Microsoft Excel 2019 Advanced course and is targeted towards individuals who want to gain an in-depth understanding of the advanced features of Excel. The video focuses on sparklines, which are tiny charts used in financial applications to show the overall trend of something. The video demonstrates how to create sparklines in Excel 2019 using team sales 2015 data and the Commission sheet. It also explains how to format and customize sparklines using the Show group and Axis options. The video concludes by mentioning the Edit Data and Clear options available for sparklines.

Sparklines

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at individuals who want to gain an in-depth understanding of advanced features of Microsoft Excel. The video is a tutorial on how to create stock charts in Excel 2019 Advanced. The instructor explains the different components of stock prices and demonstrates how to download historical stock data and insert a stock chart using the Open High Low Close option. The video concludes with an invitation to join the instructor for exercise 9.

Stock Charts

Exercise 09

Chapter 13 Quiz

Chapter 13: Graphs and Charts

This video is part of the Microsoft Excel 2019 Advanced course and is aimed at users who want to learn about the Excel Web App, a facility available from Microsoft that allows users to edit workbooks using an internet browser instead of the Excel desktop program. The video shows how to open, edit, and save workbooks in the Excel Web App, as well as how to download and upload workbooks between the desktop and the web app. The video also discusses the limitations and capabilities of Excel Online, a web-based version of Microsoft Excel, and provides a brief tour of its interface and features.

Introduction to Excel Web App

Using the Excel Web App

Chapter 14 Quiz

Chapter 14: Excel Web App

Closing Message

Chapter 15: Course Conclusion

Mastering Excel Features

Advanced Power Pivot Functionality

Enhancing VBA Functionality

Intermediate Power BI functions

Using advanced formulas

Mastering DAX language

Excel Data Analysis

This course is for users with existing Microsoft Excel experience who want to learn more advanced features and gain a deeper understanding of the most useful features for business, including consolidation, analysis and reporting of financial information.

Microsoft Excel 2019 Advanced

What you'll learn

Explore more technology skills