Advanced Excel: Master Worksheets, Functions, & Pivot Tables

Name: Advanced Excel: Master Worksheets, Functions, & Pivot Tables
Rating: 4.5 (15 reviews)

Boost your Excel expertise with advanced tools, functions, and techniques to analyze and present data like a pro.

Created byLong Nguyen

Last updated 11/2024

English

What you'll learn

Efficiently work with multiple worksheets and workbooks, including linking and consolidating data.
Master the use of named ranges for cleaner, more effective formulas and calculations.
Enhance your proficiency with intermediate Excel functions: Dive deep into essential text, date, and conditional functions
Leverage tables and pivot tables for powerful data management and analysis.
Utilize advanced charting techniques for data visualization
Streamline workflows with excel automation tools to optimize data processing, and save valuable time

Course content

5 sections • 34 lectures • 2h 21m total length

Working with Multiple Worksheets6:42
In today's lesson, we will take a look at how to cut, copy, group, label, etc. multiple worksheets
Using 3D formulas5:16
Have you ever heard of 3D formulas in Excel? They are very efficient, let's take a look at them today.
Linking workbooks6:55
Let's talk about how to link and pull data across multiple workbooks. Please make sure that you download all the resources before you watch the video.
Using Consolidate tool - consolidate by position6:08
How can we work with data across workbooks without worrying about links? Let's learn about it today.
Using Consolidate tool - consolidate by reference4:02
Like I said in the last video, let's learn how to use Consolidate tool for workbooks that are not totally identical.
Section 1 challenge1:02
This is a little challenge for section 1. Let's try it.

Introducing Named ranges5:29
Do you know that you can name your cell or range of cells? Let's talk about it today.
Different ways to create named ranges4:47
Now that you know what a named range is, let's take a look at some ways to create it fast and easy today.
Managing named ranges4:05
What if we make a mistake or want to update or audit our named ranges? Let's talk about that today.
Using named ranges in formulas and calculations5:34
We now know how to create and manage named ranges. Let's take a look at how we can use it in our calculations.
Section 2 - challenge0:06

Text functions 1 - CONCAT, UPPER, LOWER, PROPER6:33
In today's lesson, we will learn how to use some text functions" CONCAT, UPPER, LOWER, and PROPER. We will also take a look at nested functions.
Text function 2 - LEFT, RIGHT, MID3:25
In the previous lesson, we learned how to combine text. Let's take a look at how to split it today.
Text function 3 - FIND4:03
In today's lesson, we are going to learn another funciton called FIND, and how to combine it with other functions to solve our text problems.
Some date functions - NOW, TODAY, YEARFRAC3:29
Let's talk about some functions that can help us perform date calculations
Some COUNT functions3:52
Today's lesson is about some functions that can help you count numbers of values in your data.
Counting with criteria - COUNTIFS4:24
Let's learn how to count values that meet certain criteria today.
Adding with criteria + Mixed cell references5:42
In today's lesson, we will talk about a function that can help you add up values that meet certain criteria. We will also learn how to use mixed cell references.
Using sparklines to show trends4:06
In today's lesson, let's talk about how to use sparklines to quickly show trends.
Advanced charting4:49
Let's learn how to use some advanced charting features
Section 3 - challenge 10:07
Section 3 - challenge 20:07

Creating and formatting tables4:50
In today lesson, let's take a look at how we can create and format a nice table
Working with tables4:34
Did you know that Excel tables are super easy to work with and offer some amazing features? Let's talk about that today.
Sorting and filtering in tables + The total row5:38
We know how to sort and filter data in a range. Let's learn how to operate them in a table, then combine them with the power of the total row.
Automation with tables4:47
Automation is one of the most powerful features of tables. Let's see why
Converting to range and subtotal tool4:11
Let's learn how to convert a table back to a range, then use the subtotal tool to create a snapshot report.
Section 4 - challenge0:06

Creating and Modifying a Pivot Table5:11
In today's lesson, we will learn how to create and work with a pivot table.
Value field settings5:31
Let's take a look at how we can change the way our pivot tables show and adjust our data.
Group, sort, and filter tools in Pivot table5:13
In today's lesson, we will learn how to group, sort, and filter data in our pivot table
Reporting filter pages5:57
What if we want to create multiple pivot tables for different people at the same time? Let's talk about it and something more in today's lesson.
Pivot charts5:09
Now that we know a lot about pivot tables, let's take a look at how to present it visually, using pivot charts.
Section 5 - challenge0:06

Requirements

This course requires basic Excel skills and knowledge.
You need to be willing to learn and practice a lot

Description

Take your Excel skills to the next level with this comprehensive course designed for professionals who want to enhance their data analysis and presentation capabilities. Whether you're managing multiple worksheets or diving deep into advanced functions, this course equips you with the tools to handle complex data and automate your workflows efficiently.

What You’ll Learn:

Master multiple worksheets & workbooks: Learn how to work with multiple worksheets, link workbooks, and consolidate data to streamline your tasks.
Harness the power of named ranges: Simplify complex formulas by using named ranges for better clarity and management.
Work with intermediate functions: Master essential text, date, and counting functions, plus learn how to add criteria-based calculations.
Create dynamic tables and pivot tables: Build and modify pivot tables for powerful data analysis, and use advanced charting tools to visualize trends and insights.

This course is designed to help you unlock Excel’s full potential, making you more efficient, productive, and confident when working with large datasets. Whether you’re analyzing sales data, managing project timelines, or reporting results, you’ll leave this course with the expertise to handle any Excel challenge that comes your way. With practical examples and real-world applications, you’ll be equipped to tackle complex Excel tasks with ease.

Who this course is for:

Intermediate Excel learners
Office workers, especially accountants, HR officer, and auditors
Learners who have finished my Excel Essentials For Business" course

Advanced Excel: Master Worksheets, Functions, & Pivot Tables

What you'll learn

Explore related topics

Course content

Working with Multiple Worksheets & Workbooks6 lectures • 30min

All about Named ranges5 lectures • 20min

Intermediate Excel functions11 lectures • 41min

All about tables6 lectures • 24min

All about Pivot tables6 lectures • 27min

Requirements

Description

Who this course is for: