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

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
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
Working with Date Functions, Part 18:55
Working with Date Functions, Part 24:17
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
Working with Logical Functions8:36
Working with Conditional Functions7:34
Multiple Criteria Functions5:03
Using Nested Functions5:13
Using IFS and SWITCH Functions5:56
Working with Text Functions, Part 14:13
Working with Text Functions, Part 24:27
Working with Text Functions, Part 33:42
Working with Text Functions, Part 48:14
Working with Math Functions6:59
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
Filtering Data, Part 15:20
Filtering Data, Part 27:11
Filtering Data, Part 37:55
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
Using the Subtotal Feature6:12
Knowledge Check

Creating Charts5:37
Using Chart Types2:27
Changing Chart Data Source6:32
Adding Chart Elements10:00
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
Applying Trendlines4:35
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

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
Formatting a PivotTable6:56
Selecting and Moving PivotTables3:15
Changing PivotTable Options8:07
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
Using Slicers7:26
Using Timeline Slicers1:47
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: