Mastering Excel 2019 - Intermediate

Name: Mastering Excel 2019 - Intermediate
Rating: 4.5 (117 reviews)

There are two kinds of people: Those who are masters at Excel, and those who wish they were

Created byBigger Brains

Last updated 7/2023

English

EnglishSpanish

What you'll learn

Create and use named ranges
Insert functions into spreadsheets including: Date and Time functions, Logical functions, Text functions, Math functions, Database functions, and more!
Sort and filter columns of data
Create and modify Tables, and apply advanced Conditional Formatting
Create and modify charts and graphics
Apply advanced charting tools
Create a PivotTable to group and summarize data
Apply advanced PivotTable features, create a PivotChart, and filter data using Slicers and Timelines

Course content

6 sections • 53 lectures • 5h 2m total length

Introduction1:47
Master Excel 2019 intermediate techniques: create named ranges, use diverse functions, and master sorting, filtering, subtotals, tables, conditional formatting, charts, pivot tables, timelines, and chart templates with slicers.
Using Named Ranges, Part 15:00
Master named ranges in Excel by creating and naming cell groups, learning key naming rules, and using ranges for navigation, printing, and data management with quarter one and quarter two.
Using Named Ranges, Part 25:25
Create and manage named ranges in Excel by using Define Name, set scope to workbook or region, batch name from selection, and manage via Name Manager, with absolute references.
Using Named Ranges, Part 35:08
Master how to work with named ranges in Excel: use AutoSum or F3 paste names, replace formulas with named ranges like north, and apply names across quarters.
Working with Date Functions, Part 18:55
Explore how to calculate date differences in Excel using today, Yearfrac, and networkdays functions, including workdays, holidays, and weekends with practical examples.
Working with Date Functions, Part 24:17
Discover how to calculate end dates in Excel using date functions. Use add and subtract days, workday for workdays, and edate for anniversary dates while accounting for holidays.
Working with Date Functions, Part 33:37
Utilize date functions like day, month, year, and weekday to analyze order dates, and explore week numbers and ISO week numbers for deeper data insights.
Working with Time Functions5:29
Enter and format time values in Excel using shortcuts like Ctrl+; and Ctrl+Shift+:, record time in and time out, and calculate hours worked by subtracting time out minus time in.
Working with Logical Functions8:36
Mastering Excel 2019 intermediate helps you use if, and, or with true/false tests, apply discounts and bonuses, and manage automatic versus manual calculation.
Working with Conditional Functions7:34
Master conditional functions in Excel 2019 by applying countif, sumif, and sumifs to count by criteria and sum with multiple conditions.
Multiple Criteria Functions5:03
Use averageifs and maxifs to calculate average and high salaries by region and department on the paydata sheet, with dropdown filters and text criteria using wildcards.
Using Nested Functions5:13
Learn how to replace nested if statements with the IFS function for tiered discounts based on sales totals, and switch to map product lines to line managers.
Using IFS and SWITCH Functions5:56
Explore nested if statements to build tiered discounts in Excel, using the if function, logical tests for 5000 and 3000, and careful editing of nested formulas.
Working with Text Functions, Part 14:13
Learn how to split a cell into campus, building, and floor using left, mid, and right text functions, and understand when to use Flash Fill.
Working with Text Functions, Part 24:27
Use text to columns to split data into multiple columns, choosing fixed width or delimited formats from the data tab, then finish to apply the split for sorting and filtering.
Working with Text Functions, Part 33:42
Learn to combine cells into one using concat and the ampersand approach, adding spaces and punctuation, with examples like last name and first name, or building and campus.
Working with Text Functions, Part 48:14
Learn to standardize imported data with text functions like upper and proper to fix case. Use exact to compare text, replace to adjust IDs, then paste values to finalize changes.
Working with Math Functions6:59
Explore Excel math functions, including round, int, rand, randbetween, and rank.eq, and compare rounding with formatting while ranking salaries and using the sum function.
Knowledge Check

Sorting Data, Part 15:18
Master quick sorting in Excel 2019 by last name, department, date, or quantity using the Home tab, right-click, and Data tab options, while preserving headers and entry order.
Sorting Data, Part 25:29
Learn to sort data by multiple levels using the sort dialog box, adding levels like department, last name, first name, with headers and case sensitivity options.
Filtering Data, Part 15:20
Apply filters in Excel to limit data and show only relevant records. Turn on filters from the data tab, use header dropdowns, and clear all with the clear button.
Filtering Data, Part 27:11
Apply Excel 2019 filters, using date filters to target months or before dates, numeric filters for price and top/bottom percent, plus color/icon filters with conditional formatting.
Filtering Data, Part 37:55
Explore Excel's advanced filtering to create and combine criteria ranges for and/or conditions, filter in place or copy results to another sheet using exact headings.
Querying with Database Functions6:55
Discover how to use database functions like DSUM and DAVERAGE in Excel, with a named dataset Q2 Sales and a criteria range, to sum or average data based on criteria.
Using the Subtotal Function4:28
Learn to use the subtotal function in Excel 2019 to display filtered totals, contrast it with AutoSum, and understand how subtotals update as you apply filters.
Using the Subtotal Feature6:12
Use Excel's subtotal feature to group data by a column, sort first, and sum or count totals per group; explore outline levels and removing subtotals.
Knowledge Check

Creating Tables6:31
Create and manage Excel tables to streamline data formatting, filtering, and manipulation, name the table, add rows or columns inside it, and use remove duplicates and the table design options.
Modifying Table Styles6:32
Discover how to modify table styles in the design tab, create style like Develetech, and apply header, banded rows or columns, and first or last column emphasis for consistent tables.
Summarizing Data in Tables4:32
Master Excel tables: enable the total row, calculate total sales by multiplying quantity by price, and convert tables to ranges to unlock additional features.
Using the Quick Analysis Shortcut1:54
Master quick analysis in Excel 2019 by using the icon or Ctrl+Q to access formatting, charts, and totals. Create tables, pivot tables, and sparklines from selected data with ribbon shortcuts.
Customizing Conditional Formatting5:57
Master customizing conditional formatting by managing rules, setting precedence, and applying data bars with gradient fills. Configure minimum and maximum values to show 200,000 to 1.2 million.
Using Formulas with Conditional Formatting6:08
Master conditional formatting with formulas to highlight data based on values in another column, using absolute and mixed references. Learn to apply and copy formatting with format painter.
Knowledge Check

Creating Charts5:37
Learn to create and customize Excel 2019 charts to visualize trends and compare data. Use F11 or Alt+F1 for quick charts, and move or embed them with automatic updates.
Using Chart Types2:27
Learn how to change chart types in Excel 2019 using the design tab, change chart type, and chart subtypes, including setting a default chart with F11 or Alt+F1.
Changing Chart Data Source6:32
Master changing chart data sources in Excel by switching rows and columns. Adjust the data range, edit headings, and add or remove series with select data or copy-paste.
Adding Chart Elements10:00
Customize Excel charts by adding chart elements such as title, axis titles, legend, grid lines, and data labels, including pie percentages, with the chart design tab or plus menu.
Formatting a Chart4:46
Master chart formatting in Excel by applying color schemes, gradient fills, and styles to backgrounds and markers, then adjust text and data labels for clarity.
Using Advanced Chart Features4:29
Master advanced charting in Excel by creating combo charts that combine bars with lines, adding an average and percent of total on a secondary axis, with labeled axes.
Applying Trendlines4:35
Apply trendlines to charts to reveal current trends and forecast future data. Explore options (linear, exponential, logarithmic, polynomial, power, moving average), format the line, and add legends for multiple series.
Creating a Chart Template3:27
Save your chart changes as a template via right-click and apply it to other charts, using the chart templates folder, for existing or new charts, then set as default.
Knowledge Check

Inserting Graphics8:41
Mastering Excel 2019 intermediate covers inserting graphics in worksheets, using shapes, text boxes, circles, online pictures, and screenshots—plus SmartArt to create clear process visuals.
Modifying Graphics, Part 15:40
Explore styling and formatting graphics in Excel 2019, including SmartArt layouts, colors and theme variations, 3d styles, outlines, and text box and arrow formatting to make visuals pop.
Modifying Graphics, Part 26:12
Master graphics in Excel 2019 by formatting pictures with corrections, color, borders, shadows, and 3d effects; link data to charts, use SmartArt layouts, and group objects for precise alignment.
Knowledge Check

Creating a PivotTable11:31
learn to create and customize pivot tables in excel to group and summarize data by region, salesperson, and products, using rows, columns, values, and filters, with subtotals and grand totals.
Summarizing Data in a PivotTable8:00
Master pivot tables by switching between sum, average, and count, add total sales, adjust value field settings, and explore show values as options like percent of grand total and ranking.
Grouping Data in a PivotTable6:15
Master pivot tables in Excel by grouping data with year, quarters, and months, using order date as a drill-down field and customizing groups by teams and quantities.
Formatting a PivotTable6:56
Learn to format a pivot table in Excel 2019 intermediate by using pivot table styles, adjusting subtotals and grand totals, and setting number formats via value field settings.
Selecting and Moving PivotTables3:15
Move pivot tables to another sheet or a new worksheet using the Move PivotTable command, then copy them with the Select option to create independent duplicates, and delete if needed.
Changing PivotTable Options8:07
Master pivot table maintenance by refreshing data, adjusting data sources, naming pivots, and configuring empty cell display, with report filter pages for each region.
Adding Calculated Fields2:03
Learn to add a calculated field to a pivot table by naming it sales tax, using total sales multiplied by 0.04, and inserting it into the pivot field list.
Presenting Data with PivotCharts4:21
Turn pivot table data into a pivot chart, customize with the analyze tab, swap axis and legend, apply filters, and drill down to state while keeping the chart linked.
Using Slicers7:26
Learn to filter pivot tables with slicers, insert and customize slicers, use multi-select and clear filters, and connect slicers to multiple charts for synchronized filtering.
Using Timeline Slicers1:47
Explore the insert timeline feature to filter date fields in a pivot table, adjusting months and quarters, clearing filters, and switching between date views like months, quarters, and days.
Knowledge Check

Requirements

Students should have a basic knowledge of Microsoft Excel prior to taking this course

Description

Become proficient in using the world’s most popular software tool for organizing and analyzing data.

When you master Excel, you have one of the most practical and valuable skillsets in modern business. You can work wonders—from organizing lists; to creating multi-layered, interactive reports; to answering critical business questions like ROI, budget allocations, expense tracking, and more. This course builds on your existing Excel knowledge and teaches you how to efficiently manage data using charts and tables, and how to use powerful reporting tools such as Pivot Tables, Pivot Charts, Slicers, Timelines, and more.

This is our most requested training course!

As you learn to use Excel effectively, you will start to see how powerful it is in managing small or large datasets. If you are comfortable with the basics of Excel, let our Microsoft Certified Trainer Kathy Jones walk you through more advanced topics that will take your spreadsheets to the next level and help you to be more efficient in analyzing your data.

Topics covered include:

Working with named ranges
Inserting functions
Using advanced sorting and filtering techniques
Inserting Tables
Applying advanced Conditional Formatting
Inserting charts and graphics
Applying advanced charting tools

Working with Pivot Tables, Pivot Charts, Slicers, and Timelines

Over five hours of high-quality HD content in the “Uniquely Engaging”TM Bigger Brains Teacher-Learner style!

Who this course is for:

People who have a working knowledge of Excel 2019 or Excel 365, but want to use more complex functions and features to analyze data

Mastering Excel 2019 - Intermediate

What you'll learn

Explore related topics

Course content

Working with Functions18 lectures • 1hr 40min

Working with Lists8 lectures • 49min

Analyzing Data6 lectures • 32min

Visualizing Data with Charts8 lectures • 42min

Inserting Graphics3 lectures • 21min

Using PivotTables and PivotCharts10 lectures • 1hr

Requirements

Description

Who this course is for: