Microsoft Excel (2019) Certification Exam
TLDRThis tutorial video, hosted by Mike, a proponent of accessible learning, is designed to prepare viewers for the Excel 2019 or MO-200 exam. It offers a practice exam with six projects to familiarize viewers with tasks such as navigating and manipulating data, utilizing new Excel functions like CONCAT, and applying advanced features like sparklines and freeze panes. The video also covers chart creation, data sorting, and conditional formatting, aiming to enhance the viewers' proficiency with Excel for their certification exam.
Takeaways
- 📚 The video provides a practice exam for the Excel 2019 or MO-200 exam, with six projects to help users prepare for the real exam.
- 👋 Mike, the teacher, emphasizes accessible learning and introduces the tutorial with a focus on helping viewers pass the exam.
- 🎉 Patreon supporters are acknowledged, and an invitation is extended for viewers to access practice files by becoming patrons.
- 🔍 The tutorial covers various Excel skills, including navigating to a range, formatting numbers, deleting table rows, and using functions like CONCAT.
- 📈 Chart elements and new Excel functions are discussed, which are crucial for the exam and for handling real-world data analysis.
- 📝 The importance of understanding how to find and work with ranges in Excel is highlighted, as it's a common task in the exam.
- 🔢 Task-specific instructions are given, such as formatting numbers to no decimal places, which is a basic yet important skill in Excel.
- 💾 The script explains how to manipulate data within tables, including deleting rows and calculating averages, which are practical exam tasks.
- 📑 Tips are provided for tasks like freezing panes and removing table functionality, which are part of the advanced features in Excel.
- 📈 Advanced tasks like creating email lists with CONCAT function and removing duplicates from a table are also covered in the tutorial.
- 📊 The video concludes with instructions on how to create charts and modify them to include data tables and remove legends, showcasing the comprehensive nature of the exam preparation.
Q & A
What is the main purpose of the video?
-The main purpose of the video is to help viewers prepare for the Excel 2019 or MO-200 exam by providing a practice exam with six projects that cover tasks similar to those on the actual exam.
Who is the presenter of the video and what does he believe in?
-The presenter of the video is Mike, who is a teacher that believes in accessible learning to help people unlock new opportunities.
What is the first task in the Excel practice exam?
-The first task is to navigate to the range named 'total' and delete the contents of the selected cell.
How does the video suggest highlighting a large range of cells quickly?
-The video suggests typing the range directly into the name box, such as 'H6:H20', and pressing enter to quickly highlight a large range of cells.
What is the CONCAT function used for in the video?
-The CONCAT function is used to create an email list by joining the last name of each patron with the address '@patrion.com'.
How do you remove the table functionality from a table in Excel, according to the video?
-To remove the table functionality from a table, click inside the table, go to the 'Table Design' tab, and click 'Convert to Range'.
What tip does the video give for freezing panes in Excel?
-The video tips to select the row below what you want to freeze, not the row you want to freeze. For example, to freeze row 5, you should select row 6.
What is the purpose of the 'MAX' function used in the video?
-The 'MAX' function is used to return the highest value in a column, as demonstrated by calculating the highest income in the 'total' column.
How can you filter a table in Excel to only show rows that start with a specific text, according to the video?
-You can use the text filters option in the filter menu, select 'begins with', and type the specific text, such as 'FA'.
What function is used to display 'Yes' or 'No' based on a condition, and how is it applied?
-The 'IF' function is used to display 'Yes' if the value in the 'average' column is greater than 10, and 'No' if it is not. The function is applied by typing '=IF([cell] > 10, 'Yes', 'No')'.
What is a quick way to highlight non-adjacent ranges of cells to create a chart, according to the video?
-A quick way is to highlight the first range of cells, then hold down the Control key and highlight the second range of cells.
How do you add an alternative text description to a chart in Excel?
-To add an alternative text description, click on the chart, go to the 'Format' tab, click the 'Alt Text' icon, and enter the description.
Outlines
📊 Excel 2019 and MO-200 Exam Preparation
The video introduces a practice exam designed to help viewers prepare for the Excel 2019 and MO-200 exams. It includes six projects that simulate tasks similar to those found on the actual exam, covering chart elements, new Excel functions, and other necessary skills. The instructor, Mike, emphasizes accessible learning and provides a shout out to Patreon supporters, offering full access to practice files for becoming a patron. The tasks include navigating to a cell range, formatting numbers, deleting table rows, calculating averages, creating email lists with CONCAT function, and removing table functionality.
📝 Excel Tasks: Formatting, Sorting, and Data Handling
This section covers various Excel tasks such as freezing panes to keep row five and the title visible while scrolling, extending formulas, removing formatting, applying banded rows for table styling, performing multi-sort on columns, using MAX function to find the highest value, and removing duplicate invoice numbers from a table. The tasks are designed to improve the viewer's proficiency in Excel for their exams, with detailed instructions on how to accomplish each one.
📈 Advanced Excel Techniques for Data Analysis
The paragraph explains how to configure a worksheet for printing specific ranges, filter data based on criteria, use the IF function for conditional display of 'Yes' or 'No', perform simple arithmetic operations, create charts with specific axis labels, and add alt text descriptions for accessibility. It also discusses the use of sparklines to represent data visually and the application of traffic light conditional formatting to visualize data ranges.
🎨 Customizing Excel Interface and Charts
The video script details the process of customizing the Excel interface by changing the alignment of merged cells, generating codes from text functions, applying conditional formatting with traffic lights, altering table styles, matching chart colors with table styles, and displaying data tables under series in charts without legend keys. These steps aim to enhance the visual presentation and data analysis capabilities within Excel.
📌 Excel Functions and Data Visualization
This part of the script focuses on using the LEFT function to generate codes, applying traffic light conditional formatting to visualize data, changing table styles, matching chart colors with table styles, and displaying data labels above chart columns. It also covers the process of importing data from a text file and using the first row as headers, adjusting column widths, and moving pie charts to new chart sheets with specific names.
📋 Excel Chart Customization and Data Labeling
The viewer is guided through modifying chart axis titles, removing legends from charts, and displaying data labels above columns. Additionally, the script explains how to copy formatting using the Format Painter tool, name tables within Excel, and use named ranges in formulas to avoid errors and streamline calculations. The importance of using named ranges in formulas for the Excel 2019 exam is highlighted.
🔢 Excel 2019 Exam Preparation and Resource Sharing
The final paragraph discusses the use of name ranges in formulas to simplify tasks and avoid mistakes, as emphasized in the Excel 2019 exam. It provides an example of summing up different named ranges and using the COUNTBLANK function to calculate missing entries. The instructor encourages viewers to practice and offers resources such as Patreon access to practice files and a Udemy course for further preparation. The video concludes with an invitation for viewers to share their success stories in the comments.
Mindmap
Keywords
💡Excel 2019
💡MO-200 exam
💡Chart elements
💡CONCAT function
💡Table functionality
💡Data tools
💡Format Painter
💡Named ranges
💡COUNTBLANK function
💡Conditional formatting
Highlights
Introduction of a practice exam with six projects to prepare for Excel 2019 or MO-200 exam.
Explanation of how to navigate to a range total and delete cell contents in Excel.
Demonstration of formatting numbers to no decimal places in a specified range.
Guide on removing a table row for a specific patron without altering other content.
Tutorial on calculating the average income from a total column using the AVERAGE function.
Instruction on creating an email list by joining last names with '@patrion.com' using the CONCAT function.
Process of removing table functionality from a table and converting it to a normal range.
Technique to freeze row five and the title so they remain visible while scrolling vertically.
Method to extend a formula in a cell to the end of a table column using the auto fill handle.
Clearing all formatting from a worksheet using conditional formatting options.
Adding banded rows to a table to automatically update formatting when new rows are inserted.
Performing a multi-sort on two columns to sort data based on multiple criteria.
Using the MAX function to find the highest net amount value in a column.
Removing duplicate invoice number values from a table while keeping other records intact.
Configuring a worksheet to print only a specific range using the print area option.
Filtering out values in a column that don't start with a certain prefix using text filters.
Using the IF function to display 'Yes' or 'No' based on whether a value is greater than a certain number.
Creating a clustered column chart with product descriptions as horizontal axis labels.
Adding alt text description to a chart for accessibility purposes.
Inserting column sparklines to visually represent units sold from January to March.
Aligning text to the left within a merge cell range for better presentation.
Generating a code using the LEFT function to display the first two letters of a text string.
Applying traffic light conditional formatting to visually represent data with color codes.
Changing table style to 'Olive Green, Table style medium' for aesthetic improvement.
Matching chart colors with table colors using the monochromatic color palette.
Displaying a data table under the series of a chart without legend keys.
Importing data from a text file and using the first row as headers in Excel.
Adjusting column width to a specific point for better data presentation.
Moving a pie chart to its own chart sheet and naming it for organization purposes.
Modifying a chart to have 'Dollar Amount' as the primary vertical axis title.
Removing a chart's legend and displaying values as data labels above the columns.
Copying formatting from one worksheet to another using the format painter tool.
Naming a table in Excel and understanding the difference between naming a table and a range.
Using named ranges within a formula to add together multiple ranges for accuracy.
Utilizing the COUNTBLANK function to calculate missing entries in a column.
Encouragement to share success and seek further practice on Patreon for additional resources.
Transcripts
Browse More Related Video
Excel for Intermediate Users - The Complete Course
basics of Excel - most popular tool for Analytics
Top 30 Excel 2016 Tips, Tricks, Shortcuts, Functions & Formulas
Tutorial on Narbonne Suite on Mac in excel
Excel Formulas and Functions | Full Course
Top 10 Advanced Excel Tricks for Data Analysis - FREE Masterclass with Sample Files
5.0 / 5 (0 votes)
Thanks for rating: