MO-201 Complete Techniques - Microsoft Excel 2019 Expert

If you are new to macros then review section 3.6 of the course first, which explains how to record and modify macros. The purpose of this lecture is to explain the technical method to copy macros between workbooks.

Try it out for yourself using the 111TIY exercise.

This lecture explains how to link to data in other workbooks. Try it out for yourself using the 112TIY exercise.

In this lecture the management of workbook versions is explored. This is an important technique which can save you much heartache if something goes wrong with your system or if you make an unfortunate mistake. Learn how to manage the save options available in Excel.

Try it out for yourself using the 114TIY exercise.

The available options for managing macro security are presented in this lecture.

Try it out for yourself using the 113TIY exercise.

Protecting workbooks in an intelligent manner is very important, particularly when sharing information. This lecture will teach you the options that are available for data and workbook protection.

Try it out for yourself using the 121122TIY exercise.

In this lecture you will learn how to selectively protect the structure of a workbook. Try it out for yourself using the 123TIY exercise.

This topic is a regular feature of the MO-201 exam. The lecture describes the available options for the calculation of formulas in Excel. Try it out for yourself using the 124TIY exercise.

This lecture explains how to manage comments in a workbook. This is a really important technique for providing additional information to the users of your workbooks. They can also provide useful comments on the workbook contents.

Try it out for yourself using the 125TIY exercise.

In this lecture you'll learn how to set language options specific to Excel.

Try it out for yourself using the 131132TIY exercise.

Flash Fill is a great way to complete any series of data consolidation by using the 'learn by example' technique available in Excel. This is one of the most useful features of Excel when you are trying to reconfigure a large data set.

Try it out for yourself using the 211TIY exercise.

Excel Advanced Fill is a powerful feature that can save you time and effort when working with large or complex spreadsheets. It can help you to fill cells quickly and accurately with data based on specific rules or criteria, which can improve the accuracy and efficiency of your work.

In this lecture, you’ll learn how to use advanced fill for

· Linear Trends.

· Growth Trends.

· Growth Trends using checkboxes.

· Growth trends using step values.

Try it out for yourself using the 212TIY exercise.

This lecture provides a specific example to illustrate the difference between Autofill, liner trend, growth trend, growth trend using checkboxes and growth trends using step values.

There is a lot of material in this lecture.

This topic is very important for the MO201 exam.

Creating custom number formats in Excel allows you to display numbers in a format that is specific to your needs. This can include adding symbols, text, and other formatting options to make the data more readable and meaningful.

You will already be familiar with setting the currency, number of decimal places, and setting the format of negative numbers etc,

In this lecture, you’ll learn how to easily customise the format of the data in your worksheet for numbers, dates and times using the #, ?, comma and underscore.

In addition, you’ll learn how to use conditional expressions to highlight data in your worksheet.

Try it out for yourself using the 221TIY exercise.

One of my favorite topics!

Configuring data validation in Excel is a process that allows you to set rules for the types of data that can be entered into a cell or range of cells. This helps to ensure the accuracy and consistency of your data.

In this lecture, you’ll learn how to manage your data entry to make it easy for your workbook users and to ensure that the data entered into your worksheets are accurate and valid.

You will also learn how to set an input message and error message that will be displayed to the user when they enter data that violates the validation rules. The input message can be used to provide guidance or instructions for entering data, while the error message can be used to alert the user that their input is invalid.

Try it out for yourself using the 222TIY exercise.

Grouping data in Excel can help you organize and analyse large datasets more effectively, making it easier to draw insights and make data-driven decisions.

In this lecture, you’ll learn how to

· Summarise data.

· Identify patterns and trends in your data.

· Focus on important information by hiding details that are not relevant to your analysis.

· Perform calculations on subsets of your data.

Try it out for yourself using the 223224TIY exercise

Having duplicate records in your dataset can distort the data and provide a misleading depiction of the actual situation. Eliminating duplicate records can guarantee the dependability and precision of your data, as well as preserve its integrity.

In this lecture, you’ll learn how to selectively remove duplicate records from a dataset.

Try it out for yourself using the 225TIY exercise.

You can use conditional formatting to format data based on certain conditions, such as highlighting cells that contain specific text or values, or colouring cells based on a specific rule.

In this lecture, you’ll learn how define the conditions or rules that you want to apply and then specify the formatting that you want to use.

Try it out for yourself using the 231TIY exercise.

Conditional formatting rules that use formula in Excel are rules that allow you to apply custom formatting to cells based on a formula that you create.

This is a really important lecture as it is a regular feature on the MOS Excel Expert exam.

I refer to certain formulas which may be new to you, but which are explained in other areas of the Excel course. In particular I use the WEEKDAY() and the AND() functions to illustrate how the use of formulas in conditional formatting operates.

In this lecture, you’ll learn how define the conditions or rules that you want to apply using a range of functions including SUM, COUNT, AVERAGE, IF, OR and more….

Try it out for yourself using the 232TIY exercise.

This video shows you how to correctly answer the 232TIY exercise.

In this lecture, you’ll learn how edit, delete, move a rule to a different position in the list or rules or add a new rule to a range of cells in an Excel worksheet.

Try it out for yourself using the 233TIY exercise

sufficient detail for you to pass the MO201 Excel Expert Exam.

Try it out for yourself using the 311(Part 1)TIY exercise.

The SWITCH function was first introduced in Excel 2019

SWITCH functions are formula that allows you to evaluate multiple conditions and return a corresponding value based on the first condition that is true. The SWITCH function was introduced in Excel 2019 and Office 365.

AND and OR functions are logical functions in Excel. AND checks multiple values or expressions (known as arguments) and returns TRUE if ALL the arguments are true while OR returns a TRUE value if ANY of the arguments are true.

In this lecture, you’ll learn how to use the SWITCH, AND and OR functions.

Try it out for yourself using the 311(Part 2)TIY exercise.

In Excel, the SUMIF function is used to add up the values in a range of cells that meet specified criteria. The

It uses a range of cells to be evaluated, the criteria that must be met and you can also include the sum range that needs to be included if this is different than range you selected first.

SUMIFS allows you to use multiple criteria rather than just one as in SUMIF>

Similarly, AVERAGEIF averages rather than adds the cells in the range that meet the criterial specified.

In this lecture, you’ll learn how to use SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS()

Look Up Data by using VLOOKUP(), HLOOKUP(), MATCH() and INDEX() Functions

The VLOOKUP or vertical lookup function searches for a value in the first column of a selected range of cells and returns a value in the same row from a specified column.

The HLOOKUP or horizontal lookup function is like the VLOOKUP function but searches for a value in the first row of a selected range of cells and returns a value in the same column from a specified row.

In this lecture, you’ll learn how to use SUMIF(), AVERAGEIF(), COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS()

Note that the "Try It Yourself" Exercises for this Section are included 3.2.1 Part 3

I've noticed that the MO201 Excel Expert Exam has been asking a number of HLOOKUP questions recently, this lecture focusses on this particular technique in more detail.

This lecture explores the structure and mechanics of the of Index and Match functions in Excel using both the array and reference versions and the importance of sorting data if you are not using an exact match.

Try the use of all these functions out for yourself using the 321TIY exercise.

Note that the "Try It Yourself" Exercises for this Section are included 3.2.1 Part 3

This lecture explores the structure and mechanics of the of Index and Match functions in Excel using both the array and reference versions and the importance of sorting data if you are not using an exact match.

Try the use of all these functions out for yourself using the 321TIY exercise.

The NOW function returns the current date and time to the precise second and is constantly updated in your worksheet.

The TODAY function returns only the current date without a time component.

In this lecture, you’ll learn how to use the NOW() and TODAY() functions.

Try it out for yourself using the 331TIY exercise.

The main difference between Excel’s WEEKDAY() and WORKDAY() functions is that WEEKDAY() returns the day of the week as a number from 1 to 7, where 1 represents Sunday and 7 represents Saturday.

The WORKDAY() calculates a date in an Excel worksheet by excluding weekends and holidays, based on a starting date and a specified number of workdays.

In this lecture, you’ll learn how to organise data by the day of the week and how to change which day the function will regard as Day 1.

You’ll learn how to use the WORKDAY() function to determine which day a project will finish on or using the project finish date, determine which day you need to start the project.

Try it out for yourself using the 332TIY exercise.