Microsoft Excel: Beginner to Advanced in animation 2026 + AI

Ever opened Excel and felt a bit lost?

You see rows, columns, buttons everywhere… ribbons at the top… boxes on the side… and somehow you’re expected to just know what everything does.

For many beginners, Excel feels confusing not because it is complicated, but because nobody explains how the interface actually works.

That is exactly what this lesson fixes.

In this video, we break down the Excel interface in the simplest way possible so you can understand how Excel is structured and how to navigate it confidently, whether you’re using Excel on Windows or Mac.

Instead of throwing technical terms at you, we use a simple idea.

Think of Excel like a house.

Once you understand the “rooms” inside that house, everything starts to make sense.

What you will learn in this video

• How Excel looks in the latest versions on both Windows and Mac
• The main parts of the Excel interface and what they actually do
• How the ribbon system works and why it organizes Excel features
• The difference between important ribbons like Home, Insert, Data, Formulas, and View
• Where to find common tools like copy, paste, formatting, and data options

You will also learn how Excel is structured internally.

This includes:

• Understanding rows and columns and how Excel stores data
• How cells work and where data is entered
• What a range is and how multiple cells are selected
• How to navigate large spreadsheets without getting lost

The video also explains two important Excel components that beginners often ignore.

• The Name Box, which shows the exact location of the active cell
• The Formula Bar, where you can see and edit the contents of a cell

Once you understand these pieces, Excel stops feeling random. The interface starts to make sense, and navigating large spreadsheets becomes much easier.

Practical outcomes from this video

• You will understand the structure of Excel before learning formulas or analysis
• You will know how to move around a spreadsheet quickly
• You will recognize where important Excel tools are located
• You will build a strong foundation for the rest of the course

Common questions this video answers

How does the Excel interface work?
What are ribbons in Excel and what do they do?
What is the difference between rows, columns, and cells in Excel?
How do I navigate Excel spreadsheets more efficiently?
Is Excel different on Windows and Mac?

If you’ve ever felt overwhelmed by the Excel interface, this lesson will clear things up and give you the confidence to move forward.

Most people open Excel thinking it will be simple. Just a spreadsheet, right?

Then the screen appears. Blank workbook, dozens of buttons, strange menus, templates everywhere. Suddenly it feels less like a simple tool and more like something you need a manual for.

This is where many beginners get stuck. They know Excel is important for work, but the interface alone can feel confusing if nobody walks you through the basics.

That is exactly what this video does.

In this lesson, we start with the absolute fundamentals of Microsoft Excel. You will see what the startup screen looks like, how workbooks and worksheets are organized, and how Excel understands different types of data like dates, numbers, and text.

Instead of jumping straight into complex formulas, this video focuses on helping you understand how Excel actually works.

Here is what you will learn in this lesson:

• What the Excel startup screen looks like when you open the application

• How to create a new blank workbook

• How to open existing Excel files from your computer

• How Excel templates work and when to use them

• The difference between a workbook and a worksheet

• How Excel organizes data using rows, columns, and sheets

• Why Excel treats numbers, dates, and text differently

• How Excel automatically formats dates when you type them

• How to copy and paste data using keyboard shortcuts like Ctrl + C and Ctrl + V

• A faster way to duplicate values using Ctrl + D

You will also see how Excel aligns different types of data automatically:

• Numbers and dates align to the right

• Text aligns to the left

• Mixed text and numbers are treated as text

Understanding this small detail helps you quickly spot data issues when working with spreadsheets.

Practical outcomes from this video:

• Navigate the Excel startup screen with confidence

• Understand how workbooks and worksheets are structured

• Enter data correctly in Excel cells

• Recognize how Excel interprets numbers, text, and dates

• Use simple shortcuts to speed up everyday tasks

Common questions this video answers:

• What does the Excel startup screen mean?

• What is the difference between a workbook and a worksheet in Excel?

• Why does Excel align numbers and text differently?

• How does Excel recognize dates automatically?

• How do I copy and paste data in Excel quickly?

• What does Ctrl + D do in Excel?

These basics might seem small, but they are the foundation of everything you will do in Excel later.

Most people think Excel work means writing long formulas.

But sometimes the smartest Excel trick needs no formula at all.

You just show Excel a pattern once… and it finishes the job for you automatically.

That feature is called Flash Fill, and it’s one of the most powerful tools in Excel that many people barely use.

In real work situations, you often need to clean or transform data quickly. Maybe you need to extract names from emails, reformat text, split information, or combine fields.

Most users immediately start searching for complicated formulas.

But Flash Fill can often solve these problems in seconds.

That’s why in this course we start with Flash Fill before moving into other Excel tools. It teaches you how Excel can recognize patterns and automate repetitive work.

What you will learn in this video

• What Flash Fill is and why it is one of the most powerful features in Excel
• Where to find Flash Fill inside the Excel Home ribbon
• How Excel detects patterns from your input
• Why Flash Fill can replace many manual formatting tasks

This feature can handle several types of data transformations at once.

For example, Flash Fill can help you:

• Extract parts of text from a cell
• Combine multiple pieces of data into a new format
• Convert text formats automatically
• Clean and restructure messy datasets
• Perform quick data manipulation without formulas

Because of its flexibility, Flash Fill is often described as the “magic wand” of Excel. It can perform multiple operations like formatting, extraction, and conversion in a single step.

Once you understand how it works, you’ll start using it in everyday Excel tasks without even thinking about it.

Practical outcomes from this video

• You will know where to find Flash Fill in Excel
• You will understand why it is useful for data cleaning and transformation
• You will see how it can replace repetitive manual work
• You will be ready to apply Flash Fill in real datasets

Common questions this video answers

What is Flash Fill in Excel and how does it work?
Where is Flash Fill located in Excel?
How can I automatically format or extract text in Excel?
Can Excel recognize patterns and complete data automatically?
Is Flash Fill better than using formulas?

You open an Excel file with hundreds or even thousands of rows. Names need to be combined. Phone numbers need formatting. Dates need to be separated.

Your first instinct might be to do it manually. Copy, type, fix, repeat.

It works for 10 rows. But for 1,000 rows? That turns into hours of repetitive work.

This is exactly where one of Excel’s most powerful time-saving features comes in: Flash Fill.

In this video, you will learn how Flash Fill works and how it can automatically recognize patterns in your data and complete tasks in seconds. Instead of writing formulas or editing rows one by one, you simply show Excel one example and let it do the rest.

This feature alone can save you hours of manual work.

What you will learn in this lesson:

• How Flash Fill works in Microsoft Excel

• How to combine first name and last name into a single column

• How to split a full name into first name and last name

• How to extract year, month, and day from dates

• How to fix text formatting and proper capitalization

• How to extract initials from names

• How to automatically format phone numbers with separators

• How to extract numbers from messy text or sentences

• How to generate email addresses from employee names

You will also see two ways to use Flash Fill:

• Using the Flash Fill option from the Excel ribbon

• Using the Ctrl + E shortcut, which is the fastest way to apply it

The idea is simple. You show Excel one example of the output you want, and Flash Fill detects the pattern and applies it to the entire dataset.

Practical outcomes from this video:

• Clean messy datasets quickly

• Combine and split text columns without formulas

• Extract useful data from unstructured text

• Standardize formatting across large datasets

• Save hours of repetitive Excel work

You will also learn an important limitation.

Flash Fill works based on patterns. If your example contains mistakes or if the data pattern is inconsistent, Excel may produce incorrect results. In those situations, providing multiple examples helps Flash Fill understand the pattern more accurately.

Common questions this video answers:

• What is Flash Fill in Excel and how does it work?

• How do I combine first name and last name in Excel quickly?

• How do I split a full name into separate columns in Excel?

• What does Ctrl + E do in Excel?

• How can I extract numbers from text in Excel?

• How do I format phone numbers automatically in Excel?

• Can Excel generate email addresses automatically from names?

Flash Fill is often one of the first features Excel users fall in love with because of how much time it saves.

Flash Fill in Excel can feel almost magical.

You type one example, press a shortcut, and Excel instantly fills the rest of the column for you.

But here’s the catch.

Flash Fill is powerful, but it’s not perfect.

If you don’t understand its limitations, it can behave in ways that feel confusing or unpredictable.

That’s exactly what this lesson explores.

In this video, we move beyond basic Flash Fill examples and look at real situations where Flash Fill works well, and where it starts to fail.

Because knowing the limits of a tool is just as important as knowing how to use it.

The first example focuses on extracting the month name from a date.

Imagine you have a column with dates like:

9/19/2018
10/01/2018

Your goal is to extract the month name, such as:

September
October

At first glance, Flash Fill should handle this easily. But when you try it, Excel struggles to recognize the pattern.

Why?

Because Flash Fill cannot convert numbers into formatted text values. It can copy patterns that already exist in the data, but it cannot interpret the number "9" and automatically translate it into the word "September".

This is one situation where formulas will work better, which we will learn later in the course.

The second example looks at a more complex scenario.

Suppose your data looks like this:

45 USD
USD 72
2.3 INR
1.5 CAD
€2

Your task is to extract both the numeric value and the currency in a consistent format.

Flash Fill can sometimes detect the pattern if you provide enough examples.

But when the structure changes, like when currency symbols appear before the number or when formats vary across rows, Flash Fill begins to struggle.

In this case, you may notice:

• Flash Fill works when patterns appear consistently in earlier rows
• It predicts results based on the examples you provide
• It fails when it encounters new structures it hasn't seen before

This is why adding additional examples sometimes helps Flash Fill complete the remaining rows correctly.

Key limitations of Flash Fill

Understanding these limits will save you a lot of frustration when working with real datasets.

Flash Fill struggles when:

• The data structure is inconsistent
• New patterns appear that were not shown in earlier examples
• You need to convert numeric values into formatted text
• Calculations are required

It’s important to remember that Flash Fill is designed mainly for data manipulation, not analysis.

It can rearrange, extract, combine, or clean text quickly, but it cannot perform mathematical calculations.

Practical outcomes from this video

• You will understand when Flash Fill works well
• You will recognize situations where Flash Fill will fail
• You will learn how providing better examples improves Flash Fill predictions
• You will know when to switch from Flash Fill to formulas

Common questions this video answers

Why is Flash Fill not working in Excel?
What are the limitations of Flash Fill?
Can Flash Fill convert numbers into text like month names?
Why does Flash Fill fail with inconsistent data formats?
Can Flash Fill perform calculations in Excel?

Once you understand both the strengths and limitations of Flash Fill, you can use it much more effectively in real Excel work.

Working with small Excel datasets is easy. The real challenge starts when the file has thousands of rows and messy data.

In this assignment, the dataset contains 10,000 rows with complex patterns. Names appear in different formats, timestamps include dates and times, and some entries have missing information. Cleaning this manually would take hours.

In this video, you will see how to handle a large and messy dataset using Flash Fill and pattern recognition.

What you will learn in this exercise:

• Extract first name and last name from complex name fields

• Generate email addresses automatically using name patterns

• Extract only the date from a combined date-time field

• Extract hour and minute while removing seconds

• Separate AM and PM values from timestamps

• Use multiple examples so Flash Fill understands complex patterns

You will also see an important lesson. Flash Fill works well when patterns are clear, but it can sometimes behave unexpectedly with large datasets or unusual cases, such as names without a last name.

Practical outcomes from this lesson:

• Clean and structure large Excel datasets quickly

• Handle inconsistent data formats using examples

• Reduce manual work when dealing with thousands of rows

• Understand when Flash Fill works and when it needs manual correction

Common questions this video answers:

• How do I clean messy Excel data with Flash Fill?

• How can I extract names and create email IDs automatically in Excel?

• How do I separate date and time from a timestamp in Excel?

• Why does Flash Fill sometimes give incorrect results?

Duplicate data can quietly ruin your analysis.

You might have a dataset with thousands of rows, but if the same customer appears multiple times, your reports, counts, and insights can become completely inaccurate.

That’s why Excel includes a simple but powerful feature called Remove Duplicates.

In this lesson, you’ll learn how to quickly identify and remove duplicate records from a dataset.

What you will learn in this video

• How to remove duplicate values from a specific column (like Customer ID)
• How to remove duplicate rows from an entire dataset
• How Excel detects duplicates based on selected columns
• Why headers are important when cleaning data

You’ll also see two practical ways to handle duplicates:

• Remove duplicates based on a single column, for example when each customer ID should appear only once
• Remove duplicates based on all columns, when the entire row is repeated

Practical outcomes from this video

• Clean messy datasets quickly
• Prevent duplicate records from affecting analysis
• Understand how Excel identifies duplicate entries

Common questions this video answers

How do I remove duplicates in Excel?
How do I remove duplicate rows but keep unique values?
How can I check duplicate customer IDs in Excel?
What does the Remove Duplicates feature do in Excel?

Sometimes data arrives in a single column when it should actually be split across multiple columns. Dates, names, phone numbers, and IDs often come in this format, making the data harder to analyze.

This is where Text to Columns in Excel becomes useful.

In this video, you will learn how this feature helps split one column of data into multiple columns quickly. While Flash Fill works well for pattern-based tasks, Text to Columns is often more reliable when the structure of the data is clearly defined.

What you will learn in this lesson:

• What the Text to Columns feature does in Excel

• How to split a date field into day, month, and year

• How to use the Fixed Width option when the data structure is predictable

• How to preview column splits before applying them

• How to set the correct destination column for the output

You will also see a practical example where phone numbers are separated from country codes using fixed-width splitting.

Practical outcomes from this video:

• Split structured data into multiple columns instantly

• Prepare messy datasets for analysis

• Understand when to use Text to Columns instead of Flash Fill

Common questions this video answers:

• What is Text to Columns in Excel?

• How do I split a column into multiple columns in Excel?

• What is the difference between Fixed Width and other splitting methods?

• Why does Text to Columns sometimes keep unwanted symbols?

Messy text data is one of the most common problems in Excel.

Sometimes everything is packed into a single column. Full names, dates, or codes that should actually be separated into multiple fields.

Manually splitting that data can take forever.

That’s where Text to Columns becomes incredibly useful.

In this lesson, you’ll learn how to split a full name column into first name and last name using Excel’s Text to Columns feature.

The challenge is that names don’t follow a fixed structure. Some names have different lengths, and sometimes there may even be middle names.

So using a fixed width approach doesn’t work well.

Instead, we use a smarter method called a delimiter.

What you will learn in this video

• How to split full names into separate columns in Excel
• The difference between Fixed Width and Delimited methods
• How Excel uses a pattern (like a space) to split text
• How delimiters automatically remove the separator from the output

In this example, the space between first and last name becomes the delimiter. Excel simply splits the text wherever it finds that space.

You’ll also see another example using dates separated by a slash, where the slash becomes the delimiter.

This approach works well when your data has a clear pattern separating values.

Practical outcomes from this video

• Quickly split full names into first and last names
• Clean and structure messy text data
• Understand when to use Fixed Width vs Delimited splitting

Common questions this video answers

How do I split full names into two columns in Excel?
What is Text to Columns in Excel?
What is the difference between Fixed Width and Delimited in Excel?
How do I split data using spaces or symbols in Excel?

Text to Columns is a useful Excel tool for splitting structured data. But like many features in Excel, it does not work perfectly in every situation.

In this video, you will learn where Text to Columns can fail and why it is important to choose the right tool for the job.

Some datasets are too messy or inconsistent for this feature to handle. For example, when numbers are embedded inside sentences or when the structure of the data changes across rows, Text to Columns struggles to extract the information correctly.

What you will learn in this lesson:

• Why Text to Columns cannot extract numbers from complex text

• Why certain datasets are better handled using Flash Fill instead

• Why splitting partial information from a column can be difficult

• How existing data in nearby columns can get overwritten during splitting

• Why Excel sometimes shows warnings when the destination column already contains data

Practical outcomes from this video:

• Understand the limitations of Text to Columns

• Avoid common mistakes that overwrite existing data

• Choose the right Excel feature depending on the dataset

Common questions this video answers:

• Why does Text to Columns not work on messy text data?

• Can Excel extract numbers from sentences using Text to Columns?

• Why does Excel warn about replacing existing data when splitting columns?

• When should I use Flash Fill instead of Text to Columns?

Understanding both the strengths and limitations of Excel tools is what makes data cleaning faster and more reliable.

Data collection sounds simple. But in reality, it’s where most data problems begin.

When people manually enter information into Excel, mistakes happen all the time. Wrong formats, extra characters, numbers in the wrong place, or incomplete entries.

And once bad data enters your dataset, your analysis becomes unreliable.

That’s why Excel provides a powerful feature called Data Validation.

Data Validation helps you control what type of data can be entered into a cell, reducing errors during data entry.

What you will learn in this video

• What Data Validation is and why it’s important for data collection
• How Excel can restrict incorrect inputs in a dataset
• How to guide users with prompts and error messages
• How to enforce specific formats like structured IDs

For example, imagine a student ID that must follow a strict format like S2XXXX. Without validation, someone might enter extra characters or a completely different format.

With Data Validation applied, Excel immediately blocks the incorrect entry and shows an error message before the data is saved.

Practical outcomes from this video

• Reduce human errors during data entry
• Ensure consistent data formats in your dataset
• Create controlled input fields for forms and templates

Common questions this video answers

What is Data Validation in Excel?
How do I restrict what users can enter in Excel cells?
How can I enforce a specific format for IDs in Excel?
How do I prevent incorrect data entry in Excel?

Excel allows you to type almost anything into a cell. Numbers, text, symbols, or even random values. While this flexibility is useful, it can also create problems when multiple people are entering data into the same file.

That is where Data Validation becomes important.

In this video, you will get introduced to the Data Validation feature in Excel, located in the Data ribbon. This tool helps control what users are allowed to enter into specific cells.

Instead of accepting any value, you can define rules and restrict the type of data that can be entered.

What you will learn in this lesson:

• Where to find Data Validation in the Excel Data ribbon

• How Excel cells normally accept any type of input by default

• How Data Validation helps control and restrict data entry

• The three main parts of the Data Validation window:

  • Settings

  • Input Message

  • Error Alert

Practical outcomes from this video:

• Understand why controlling data entry is important in Excel

• Learn the basic structure of the Data Validation tool

• Prepare to apply rules that improve data accuracy

Common questions this video answers:

• What is Data Validation in Excel?

• Where is Data Validation located in Excel?

• Why does Excel allow any value in a cell by default?

• How can I control what users type in an Excel sheet?

Bad data usually starts with bad data entry.

When people manually type information into Excel, mistakes are almost guaranteed. Extra characters, wrong formats, or values that don’t follow the required rules.

That’s where Data Validation becomes extremely useful.

In this lesson, you’ll learn how to control exactly what users can enter into a cell by setting clear validation rules.

What you will learn in this video

• How to restrict inputs using Text Length validation
• How to require exact character limits like a 12-digit password
• How to allow values only within a range, such as 10 to 16 characters
• How to set maximum limits for entries, like fewer than 20 characters

You’ll also learn how to guide users properly by adding:

• Input messages that appear when a user selects a cell
• Error alerts that stop incorrect data from being entered

This makes your Excel sheets behave more like controlled forms instead of open spreadsheets where anything can be typed.

Practical outcomes from this video

• Prevent incorrect data entry
• Enforce password or ID length rules
• Create structured and reliable datasets

Common questions this video answers

How do I limit characters in Excel cells?
How do I enforce a specific text length in Excel?
How can I show a message when someone enters data in Excel?
How do I stop users from entering invalid values in Excel?

Data Validation is useful, but applying it to just one cell is rarely enough. In most real datasets, the same rule needs to apply to every row in a column.

In this video, you will learn a simple way to extend Data Validation rules across multiple cells without repeating the setup.

Instead of opening the Data Validation window again for each row, Excel allows you to copy the validation rule from one cell and paste it across the entire column.

What you will learn in this lesson:

• How Data Validation rules become a property of a cell

• How to copy and paste Data Validation settings to other rows

• How Excel automatically applies the same validation rule to multiple cells

• How invalid entries are rejected once the rule is applied

Practical outcomes from this video:

• Apply validation rules quickly across large datasets

• Maintain consistent data entry across rows

• Avoid repeating the same setup multiple times

Common questions this video answers:

• How do I apply Data Validation to an entire column in Excel?

• Do I need to recreate Data Validation rules for every row?

• Can I copy Data Validation settings from one cell to another?

• Why does Excel reject some values after applying validation?

One challenge still remains. After applying Data Validation, it is not always obvious which cells contain validation rules.

Controlling data entry in Excel isn’t just about text or numbers. You can also control dates, time, and even predefined options.

In this lesson, you’ll learn several practical ways to use Data Validation to keep your datasets clean and structured.

What you will learn in this video

• How to allow date entries only before or after a specific date
• How Excel handles different date formats based on system settings
• How to restrict time entries, such as allowing input only after 11 AM
• How to allow time only within a specific range, like between 11 AM and 10 PM

You’ll also learn one of the most useful validation methods, List Validation.

This lets you restrict entries to predefined options.

For example, if a shop only sells Pizza, Burger, and Fries, Excel can prevent users from entering incorrect or misspelled items.

Instead of typing manually, users can simply choose from a dropdown list.

You’ll also see how to create dynamic lists by referencing cells. When new items are added to the list, they automatically appear in the dropdown without updating the validation rule.

Practical outcomes from this video

• Control how dates and times are entered in Excel
• Prevent spelling mistakes using dropdown lists
• Create dynamic data entry forms using list references

Common questions this video answers

How do I restrict date entries in Excel?
How can I control time input in Excel cells?
How do I create a dropdown list in Excel using Data Validation?
How can I update dropdown lists automatically when new items are added?

Applying Data Validation rules is useful, but users still need guidance while entering data. Otherwise, they may not know what kind of input is expected.

This is where two additional features inside Data Validation become helpful: Input Message and Error Alert.

In this video, you will learn how these options help guide users and prevent incorrect data entry.

What you will learn in this lesson:

• How to use Input Message to guide users when they select a cell

• How to add a title and instruction for the expected data

• How Error Alert stops users from entering invalid values

• The difference between a helper message and an error message

Here is how they work:

• Input Message appears when a user selects the cell. It acts like a small instruction, telling the user what type of data should be entered.

• Error Alert appears only when someone enters invalid data. It blocks the entry and shows a message explaining what went wrong.

Practical outcomes from this video:

• Reduce data entry mistakes in shared Excel files

• Guide users with clear instructions before they enter data

• Prevent incorrect values from being saved in the worksheet

Common questions this video answers:

• How do I show instructions when someone clicks a cell in Excel?

• What is the Input Message in Excel Data Validation?

• How does Error Alert prevent incorrect data entry?

• How can I restrict users to valid dates in Excel?

With these settings, Data Validation not only restricts input but also guides users and prevents mistakes before they affect your data.

If you collect time-based data in Excel, mistakes can easily happen. Someone might enter text instead of time, or record values outside the allowed working hours.

That’s where Data Validation for time becomes useful.

In this lesson, you’ll learn how to restrict time entries so Excel accepts only valid values.

What you will learn in this video

• How to allow time entries only after a specific time, such as 11:00 AM
• How to allow time within a range, for example between 11:00 AM and 10:00 PM
• How Excel validates time formats during data entry

You’ll also learn an important formatting rule.

Excel expects time inside Data Validation to use colons, not dots.

Correct format example:
11:15 AM

Incorrect format example:
11.15 AM

Using the wrong format will cause Excel to reject the validation rule.

Practical outcomes from this video

• Control business hours for data entry
• Prevent invalid time inputs in forms or trackers
• Maintain consistent time formatting in your dataset

Common questions this video answers

How do I restrict time input in Excel?
How can I allow time entries only after a specific time?
How do I create time validation between two time ranges?
Why is Excel not accepting my time format?

In many Excel files, errors happen simply because people type different values for the same category. One person types “Pizza”, another types “pizza”, someone else writes “burger meal”. Suddenly the dataset becomes messy.

This is where Data Validation Lists become extremely useful.

In this video, you will learn how to restrict a cell so that only specific values are allowed. Instead of typing freely, users must choose from a predefined list.

What you will learn in this lesson:

• How to create a drop-down list using Data Validation

• How to allow only specific values like Pizza, Burger, and Fries

• How to enter list items manually using commas

• How to create a list using cell references from another column

• How Excel automatically shows a drop-down indicator when validation is applied

Practical outcomes from this video:

• Prevent inconsistent data entry in Excel

• Standardize categories like products, departments, or status values

• Make data entry faster using drop-down selections

• Apply the same validation rule across multiple rows

Common questions this video answers:

• How do I create a drop-down list in Excel?

• How can I restrict a cell to only specific values?

• Can I use another column as a list for Data Validation?

• Why does a small drop-down arrow appear in some Excel cells?

This is one of the most commonly used data validation techniques in real business spreadsheets.

Now it’s time to apply everything you’ve learned about Data Validation.

In this assignment, you’ll create validation rules for different columns in a dataset. Each column has a specific requirement, and you need to apply the correct validation rule across all 12 rows.

Assignment criteria

• Product ID
Must contain exactly 6 characters. It can be numbers or text.

• Product Category
Only three values should be allowed: Pizza, Burger, or Fries.

• Product Cost
The value must be between 100 and 240.

• Product Margin
The minimum profit margin must be 35%.
Hint: You cannot use the Whole Number option here.

• Product Sales Date
The date must fall within the year 2025.

Your task is to choose the correct Data Validation settings for each column and apply them across the entire dataset.

Practical outcomes from this assignment

• Practice using different Data Validation rules
• Learn how to control text, numbers, percentages, and dates
• Build structured datasets that prevent incorrect entries

Common questions this assignment helps answer

How do I enforce character limits in Excel?
How do I create dropdown lists using Data Validation?
How do I restrict numbers within a specific range?
How do I validate percentage values in Excel?
How do I restrict date entries to a specific year?

Try completing the assignment on your own first.

In this video, we walk through the solution to the Data Validation assignment and review how each column was configured.

Instead of recreating the validation rules, the focus here is on understanding the logic behind each one.

Here is how the validation rules were applied:

• Product ID
  Uses Text Length validation to ensure the ID contains exactly 6 characters.

• Product Category
  Uses a List validation, allowing users to select only predefined category values.

• Product Cost
  Uses Whole Number validation with a range between 100 and 240 to restrict acceptable costs.

• Product Margin
  Uses Decimal validation instead of whole numbers.
  This is important because Excel stores percentages as decimal values.
  For example, 35% is actually stored as 0.35 in Excel.

• Product Sale Date
  Uses Date validation, restricting entries to dates before 31-Dec-2023.

You will also see a useful shortcut for applying validation across the dataset. Once the rules are set for the first row, you can simply:

• Copy the entire validated row

• Paste it across the remaining rows

Excel will automatically apply the same validation rules to the corresponding columns.

Practical outcomes from this lesson:

• Understand how different validation types work together

• Use the correct validation type for numbers, percentages, and dates

• Apply validation rules quickly across large datasets

Common questions this video answers:

• Why should percentage validation use decimals instead of whole numbers?

• How do I restrict text to a specific length in Excel?

• How do I validate numbers within a range in Excel?

• How do I apply Data Validation to multiple rows at once?

In many real datasets, simple validation rules are not enough. Sometimes the data must follow a specific format, and Excel’s standard validation options cannot enforce it.

In this video, you will see how to handle more complex Data Validation scenarios using Excel’s Custom rule option, with help from ChatGPT.

The example focuses on validating a Student ID format.

The required format is:

• The first three characters must always be “STU”

• The next four characters must be numbers

• The total length must be 7 characters

Without proper validation, users might enter incorrect values such as:

• Missing the required prefix

• Entering only numbers

• Using the wrong number of characters

To solve this, a custom validation formula is generated using ChatGPT and then applied inside Excel’s Data Validation settings.

What you will learn in this lesson:

• When standard Data Validation rules are not enough

• How to use the Custom validation option in Excel

• How ChatGPT can help generate validation formulas

• How to enforce strict input formats like STU1234

Practical outcomes from this video:

• Create validation rules for structured IDs

• Prevent incorrect formats during data entry

• Use AI tools to build complex Excel logic faster

Common questions this video answers:

• How do I enforce a specific ID format in Excel?

• Can Excel validate text and numbers together in a pattern?

• How do I use Custom Data Validation formulas?

• Can ChatGPT help generate Excel formulas?

Sometimes basic Data Validation rules are not enough.

For example, what if you want Excel to accept only the current date, the exact day someone is entering the data?

The standard Date validation options in Excel allow rules like between, greater than, or less than, but they don’t directly provide an option for “today only.”

That’s where custom Data Validation formulas become useful.

In this lesson, you’ll learn how to create a rule that allows Excel to accept only the current date at the moment of data entry.

This is useful for situations like:

• Daily trackers
• Attendance logs
• Task updates that must be recorded on the same day
• Team reports that should only capture current-day activity

Instead of using the default date options, we apply Custom Validation and use a formula that checks whether the entered date matches today’s date.

What you will learn in this video

• Why standard date validation cannot enforce “today only” entries
• How to create a custom validation rule using formulas
• How Excel compares entered values with the current system date
• Why this method works regardless of how the date is typed

For example, Excel will still accept the date even if it’s written in different formats, such as:

• 12/05/2024
• 2024-05-12

As long as the value represents today’s date, the validation rule will accept it.

Practical outcomes from this video

• Build Excel trackers that accept only current-day entries
• Prevent users from entering past or future dates
• Use custom validation formulas for advanced control

Common questions this video answers

How do I allow only today’s date in Excel?
How do I create custom Data Validation rules in Excel?
How can I prevent users from entering past or future dates?
How does Excel validate dates using formulas?

Email addresses are one of the most common pieces of data collected in spreadsheets. Unfortunately, they are also one of the most common sources of data entry errors.

People forget the domain name, add extra characters, include spaces, or type incomplete addresses. Basic Data Validation rules are often not enough to catch these mistakes.

In this video, you will see how to create a more advanced email validation rule using Excel’s Custom Data Validation along with help from ChatGPT.

Instead of defining a fixed list or simple rule, ChatGPT generates a formula that checks whether the entered value follows a valid email structure.

What you will learn in this lesson:

• How to validate email addresses in Excel using Custom Data Validation

• How ChatGPT can help generate complex validation formulas

• How Excel checks whether an entry follows an email format

• How validation rules accept properly structured addresses like

  • customer@gmail.com

  • client@google.com

You will also see an important lesson when using AI-generated validation rules. If certain criteria are not specified clearly, some invalid inputs may still pass through. For example, a validation rule might accept an email with extra spaces if that condition was not included in the prompt.

Practical outcomes from this video:

• Reduce invalid email entries in spreadsheets

• Use AI tools to create advanced Excel validation logic

• Apply structured data checks beyond Excel’s default options

Common questions this video answers:

• How do I validate email addresses in Excel?

• Can Excel check if an email format is valid?

• How do I use Custom Data Validation formulas?

• Can ChatGPT generate Excel validation formulas?

This example shows how combining Excel features with AI tools can help solve more complex data validation problems.

Excel files can contain multiple worksheets, and knowing how to manage them properly makes your work much easier.

In this lesson, you’ll learn the basic but important actions used to manage sheets in Excel.

One thing to know first. In newer versions of Excel like Office 365 or Excel 2022, a workbook usually starts with one sheet by default. Older versions often started with three sheets.

What you will learn in this video

• How to insert a new worksheet in Excel
• How Excel places new sheets based on the currently active sheet
• Two ways to rename worksheets quickly
• The 31-character limit for sheet names

You will also learn how to manage sheets more efficiently.

This includes:

• Deleting sheets and understanding that this action cannot be undone
• Hiding and unhiding sheets to control what others can see
• Sending only specific sheets without exposing your source data

One useful technique shown in this video is moving or copying a sheet to a new workbook.

Instead of copying charts or content manually, Excel can create a new file with the exact same formatting and layout. This saves time and prevents formatting issues.

Practical outcomes from this video

• Organize large Excel workbooks with multiple sheets
• Protect sensitive data by hiding sheets
• Share only the required sheets without exposing raw data
• Duplicate sheets quickly into new Excel files

Common questions this video answers

How do I add or delete sheets in Excel?
How do I rename worksheets in Excel?
Can I hide sheets before sending an Excel file?
How do I copy a sheet to a new workbook in Excel?

When working with Excel data, readability matters. If column widths are too small or row heights are uneven, the data becomes difficult to scan and interpret.

In this video, you will learn how to manage and adjust cells in Excel so your dataset becomes easier to read and work with.

The focus here is on controlling column width, row height, and cell formatting.

What you will learn in this lesson:

• The default column width and row height in Excel

• How to use AutoFit Column Width to automatically resize columns

• How to adjust multiple columns at once using Ctrl + A

• How to use AutoFit Row Height when row sizes become uneven

• How Wrap Text helps display long information inside a cell

• How to edit existing cell data by double-clicking the cell

Practical outcomes from this video:

• Make large datasets easier to read

• Reveal hidden or truncated data quickly

• Format columns and rows without manually resizing each one

• Edit values inside cells efficiently

Common questions this video answers:

• How do I automatically adjust column width in Excel?

• How do I resize all columns at once?

• What does Wrap Text do in Excel?

• How can I edit data directly inside a cell?

These small formatting tools make a big difference when handling real datasets.

Even a small dataset can become hard to read if it isn’t formatted properly.

In this lesson, you’ll learn how to clean up a messy Excel table using basic formatting techniques so the data becomes easier to read and analyze.

The goal is simple. Make the spreadsheet clear, structured, and visually organized.

What you will learn in this video

• How to fix uneven rows by disabling Wrap Text
• How to clearly separate headers from data
• How to apply bold text and fill colors for better readability
• How to align text using Excel’s alignment tools

You’ll also learn how to use one of the most helpful formatting tools in Excel, Format Painter.

Format Painter lets you copy the formatting of one cell and apply it to other cells instantly.

Two useful tips covered in this lesson:

• Clicking Format Painter once applies formatting one time
• Double-clicking Format Painter lets you apply the same formatting to multiple cells

Practical outcomes from this video

• Turn messy Excel tables into clean, readable datasets
• Create clear visual separation between headers and data
• Apply consistent formatting across large datasets quickly

Common questions this video answers

How do I format data properly in Excel?
How do I make headers stand out in Excel tables?
What does Format Painter do in Excel?
How can I copy formatting from one cell to another?

Copy and paste might sound basic, but in Excel it works a little differently than most people expect.

When you copy data in Excel, you are not just copying the visible values. Excel also copies all the properties of those cells, including formatting, colors, borders, and even formulas.

In this video, you will revisit basic copy and paste in Excel and understand what actually happens behind the scenes.

What you will learn in this lesson:

• How to select large datasets quickly using shortcuts

• How to copy data using Ctrl + C (or Command + C on Mac)

• How to paste data using Ctrl + V (or Command + V)

• How to move data from one table to another

You will also notice an important behavior. When you paste data into another area, Excel replaces the existing formatting. That means things like:

• Cell colors

• Borders

• Text formatting

• Formulas

can all be overwritten during the paste.

Practical outcomes from this video:

• Copy large datasets quickly using keyboard shortcuts

• Understand what Excel actually copies during paste

• Avoid accidentally overwriting existing formatting

Common questions this video answers:

• How do I copy and paste data in Excel?

• What does Excel copy besides the values?

• Why do my cell colors or borders disappear after pasting?

• How do I select an entire dataset quickly?

Copying and pasting in Excel is simple, but it can also create unexpected problems.

For example, when you copy a cell that contains a formula, Excel doesn’t just copy the result. It copies the formula itself.

When that formula is pasted into a new location, the cell references change. This can lead to incorrect results, especially when the formula depends on a specific range.

That’s where Paste Special becomes important.

In this lesson, you’ll learn how to paste only the final values, without copying formulas or formatting.

What you will learn in this video

• Why normal copy-paste also copies formulas
• How formula references change when pasted into new cells
• How to use Paste Special → Values to paste only the output
• How to avoid errors caused by formula references

Using Paste Values keeps only the result of the formula and removes the formula itself.

For example, if a formula calculates someone’s date of birth or age, pasting as values ensures the output stays fixed instead of recalculating incorrectly.

Practical outcomes from this video

• Prevent formula errors after copying data
• Keep only the final results when sharing data
• Convert formula outputs into static values

Common questions this video answers

How do I paste values without formulas in Excel?
What is Paste Special in Excel?
Why do formulas change when I copy and paste in Excel?
How do I copy only the result of a formula?

Sometimes you may want to share Excel data without letting others edit it. For example, when sending a report, adding data to a presentation, or inserting a table into a document.

Instead of pasting editable cells, Excel allows you to copy the data as an image.

In this video, you will learn how to use the Copy as Picture feature in Excel.

This option converts the selected cells into an image so the data cannot be modified after pasting.

What you will learn in this lesson:

• How to use Copy as Picture from the Home ribbon

• The difference between copying as shown on screen and optimized for printing

• How Excel pastes the copied data as a static image instead of editable cells

You will also see how this can be useful when pasting Excel tables into:

• PowerPoint presentations

• Word documents

• Reports or dashboards

Practical outcomes from this video:

• Share Excel data without allowing edits

• Insert spreadsheet data into presentations or documents

• Preserve formatting exactly as displayed

Common questions this video answers:

• How do I copy Excel data as an image?

• Can I paste Excel tables into PowerPoint as a picture?

• What does “Copy as Picture” do in Excel?

• What is the difference between screen view and print view when copying?

Sometimes you copy data in Excel and paste it as an image. This is useful when you want to show a table or report without letting others edit the cells.

But there’s a problem.

If the original data changes, the pasted image does not update automatically. You would normally have to copy and paste the image again every time the data changes.

Excel solves this with a feature called Linked Picture (or Paste Link).

In this lesson, you’ll learn how to paste an image that stays connected to the original data.

What you will learn in this video

• How to copy a range and paste it as a linked image
• How the image automatically updates when the source data changes
• Why linked images are useful for dashboards and reports

The image itself cannot be edited directly. But when the original data is updated and you press Enter, the linked image refreshes automatically to reflect the new information.

Practical outcomes from this video

• Create live report snapshots in Excel
• Show data visually without exposing editable cells
• Automatically update images when the underlying data changes

Common questions this video answers

How do I create a linked picture in Excel?
How can I make an image update automatically when data changes?
What is Paste Link in Excel?
How do I display live data as an image in Excel?

Sometimes you need to copy data in Excel, but you do not want everything to come along with it. Maybe you want the formulas but not the styling, or the values without the borders.

This is where Paste Special becomes useful.

Instead of pasting everything from the source cells, Paste Special lets you control exactly what gets pasted.

In this video, you will learn two practical Paste Special techniques.

What you will learn in this lesson:

• How to paste Formulas and Number Formatting only

• How to avoid copying text formatting like bold or italics

• How to paste values while removing borders and extra formatting

These options help you cleanly move data between tables without bringing unnecessary formatting.

Practical outcomes from this video:

• Keep formulas intact while adjusting formatting

• Copy datasets without messy borders or styling

• Maintain readable tables when combining data from different sheets

Common questions this video answers:

• What is Paste Special in Excel?

• How do I paste formulas without copying formatting?

• How can I remove borders when pasting data in Excel?

• How do I control what gets pasted in Excel?

Updating large datasets in Excel can become tedious if you do everything manually.

Imagine you have a list of employees with their old bonus amounts, and a separate list showing revised bonuses. Some employees received updates, while others stayed the same.

Manually checking each row and updating values would take a lot of time.

This is where Paste Special → Skip Blanks becomes very useful.

In this lesson, you’ll learn how to update only the cells that actually changed, without overwriting existing data with empty values.

What you will learn in this video

• Why normal copy-paste overwrites all cells, including blanks
• How Paste Special → Skip Blanks ignores empty cells during pasting
• How to update only the rows where new values exist

When you paste using Skip Blanks, Excel updates only the cells that contain data in the copied range. Any empty cells in the copied area are ignored.

This allows you to update values like revised bonuses without accidentally deleting existing data.

Practical outcomes from this video

• Update large datasets quickly
• Avoid overwriting data with blank cells
• Apply selective updates across tables

Common questions this video answers

How do I paste values without overwriting existing data in Excel?
What does Skip Blanks do in Paste Special?
How can I update only specific rows in Excel?
How do I copy updates without deleting existing values?

Sometimes you need to update many values in Excel at once. For example, increasing prices due to inflation or adding a fixed amount to every value in a column. Doing this manually row by row would take a lot of time.

Excel provides a powerful Paste Special operation feature that allows you to perform calculations while pasting data.

In this video, you will learn how to apply mathematical operations directly during the paste process.

What you will learn in this lesson:

• How to use Paste Special operations in Excel

• How to multiply values using Paste Special → Multiply

• How to add a fixed value to multiple cells using Paste Special → Add

• How to paste values only without copying formatting

For example:

• Multiply existing purchase amounts by 114% to adjust for inflation

• Add a fixed amount such as 114 to every value in a column

Instead of writing formulas or editing each cell, Excel applies the calculation to all selected cells at once.

Practical outcomes from this video:

• Update large datasets instantly with a single calculation

• Apply percentage increases across multiple rows

• Perform quick one-time adjustments to numeric data

Common questions this video answers:

• Can Excel perform calculations while pasting data?

• How do I multiply multiple cells by a percentage quickly?

• How do I add the same value to an entire column in Excel?

• What does the Operation option in Paste Special do?

When working with large datasets in Excel, it’s easy to lose track of important information while scrolling.

For example, if your sheet has hundreds or thousands of rows, the header row may disappear when you scroll down. Similarly, when you scroll to the right, key columns like customer names may no longer be visible.

This is where Freeze Panes becomes very useful.

In this lesson, you’ll learn how to keep important rows or columns visible while navigating large spreadsheets.

What you will learn in this video

• How to freeze the top row so headers stay visible while scrolling
• How to freeze the first column to keep key identifiers visible
• How to freeze both rows and columns at the same time
• How to unfreeze panes when you no longer need the freeze applied

You’ll also learn an important trick for freezing both rows and columns.

To freeze multiple areas, select the cell just below the row and just to the right of the column you want to freeze. Then apply Freeze Panes from the View tab.

For example, selecting C2 freezes:

• The entire first row
• The first two columns

Practical outcomes from this video

• Navigate large datasets without losing context
• Keep headers and key identifiers visible while scrolling
• Improve readability when analyzing large Excel tables

Common questions this video answers

How do I freeze rows in Excel?
How do I freeze columns in Excel?
How can I freeze both rows and columns together?
How do I unfreeze panes in Excel?

When organizing data in Excel, sometimes a single label needs to apply to multiple columns or rows. Instead of repeating the same label in several cells, Excel allows you to combine those cells into one.

This is done using the Merge and Center feature.

In this video, you will learn how merging works and when it is useful while formatting reports or headers.

What you will learn in this lesson:

• What merging cells means in Excel

• How to combine multiple cells using Merge and Center

• How Excel handles existing data when merging cells

• How to merge cells across rows or columns

• How to unmerge cells if the merge was done accidentally

For example, if you have columns like First Name and Last Name, you might want a header above them called Customer Info. Instead of duplicating the label, you can merge the cells and place the header across both columns.

Practical outcomes from this video:

• Create clean headers for grouped columns

• Improve the structure and readability of reports

• Format dashboards or summary tables more professionally

Common questions this video answers:

• What does Merge and Center do in Excel?

• How do I merge multiple cells into one in Excel?

• What happens to existing data when cells are merged?

• How do I unmerge cells in Excel?

Merging is mainly used during the final formatting stage of reports, where you want your data to look structured and easy to understand.

Large datasets are hard to interpret when every row is visible.

If your sheet contains hundreds or thousands of records, manually scanning each row to find specific information can take a lot of time.

That’s why Filtering is one of the most important features in Excel.

Filtering allows you to display only the rows that match a specific condition, hiding everything else temporarily. This makes it much easier to analyze patterns and draw insights from your data.

In this lesson, you’ll learn how filtering helps interpret data quickly.

For example, in a dataset containing pizza shop call transactions, you might want to analyze only query-related calls. Instead of scanning every row, filtering allows you to instantly view only those records and study patterns like call duration or time of day.

What you will learn in this video

• What filtering is and why it is useful for data interpretation
• Where to find the Filter option in Excel
• The two requirements for filtering:

• A proper header row

• A selected dataset
  • How to quickly select the entire dataset using Ctrl + A
  • How to apply filters instantly using the shortcut Ctrl + Shift + L

Once filters are applied, Excel adds dropdown controls to each header, allowing you to filter data based on values, conditions, or categories.

Practical outcomes from this video

• Analyze large datasets quickly
• Display only relevant information when interpreting data
• Save time when working with hundreds or thousands of records

Common questions this video answers

How do I filter data in Excel?
What is the shortcut to apply filters in Excel?
Why is filtering useful in Excel analysis?
How do I select an entire dataset quickly in Excel?

Real-world datasets are rarely small. They often contain dozens of columns and many rows, making it difficult to quickly find the information you need.

This is where Filtering in Excel becomes extremely useful.

In this video, you will learn how to apply filters to a dataset so you can view only the records that match specific conditions.

What you will learn in this lesson:

• How to select an entire dataset quickly using Ctrl + A

• How Excel automatically detects the actual data range in a sheet

• How to enable Filter from the Sort & Filter option

• How filter dropdowns appear on each column header

Once filtering is enabled, Excel lets you display only the records you want. For example, if a column contains values like Complaint, Query, and Request, you can filter the dataset to show only one category.

Filtering works by identifying unique values in each column and allowing you to choose which ones should remain visible.

Practical outcomes from this video:

• Quickly analyze large datasets

• View only relevant records without deleting data

• Understand the unique values present in a column

Common questions this video answers:

• How do I apply filters in Excel?

• Why do dropdown arrows appear in Excel headers?

• How can I show only specific records in a dataset?

• How does Excel identify unique values for filtering?

Filters work best when Excel clearly understands where your data actually starts.

In many datasets, the header row begins at the first row. But sometimes the data might start further down in the sheet, for example at row three.

In this lesson, you’ll learn how Excel handles filtering when the header is not in the first row.

Excel is often smart enough to detect where the real dataset begins. If rows above the table are mostly empty or inconsistent, Excel may automatically recognize the correct header row and apply filters there.

However, this behavior is not guaranteed.

If you accidentally select extra rows above the dataset before applying the filter, Excel may treat the wrong row as the header and place filter controls in the wrong location.

What you will learn in this video

• How Excel detects header rows automatically
• Why filtering may still work when data starts below row one
• How incorrect selections can apply filters to the wrong row
• Why selecting the dataset before filtering is the safest approach

Best practice when using filters

• Always select the actual dataset first
• Avoid including empty rows above your header
• Then apply the filter using Ctrl + Shift + L

This ensures the filter is applied exactly where you expect it.

Practical outcomes from this video

• Avoid common mistakes when applying filters
• Ensure filter dropdowns appear in the correct header row
• Work confidently with datasets that don’t start at row one

Common questions this video answers

Can Excel apply filters if the header is not in the first row?
Why did Excel apply the filter to the wrong row?
How do I make sure filters apply to the correct dataset?
What happens if I select extra rows before filtering?

Filtering becomes truly useful when answering specific business questions from a dataset.

In this example, the goal is simple: find all transactions handled by a specific agent.

Using Excel’s filter feature, you can quickly narrow down the dataset to show only the relevant records.

What you will learn in this lesson:

• How to apply a filter to the dataset using Ctrl + A and the Filter option

• How to filter data based on text values in a column

• How to select a specific item, such as Agent: Mike Ross, from the filter dropdown

Once the filter is applied, Excel hides all other records and shows only the rows where Mike Ross handled the transaction.

Practical outcomes from this video:

• Quickly isolate records related to a specific person or category

• Answer business questions directly from large datasets

• Reduce the time spent manually searching through rows

Common questions this video answers:

• How do I filter Excel data by a specific name?

• How can I show records handled by one employee?

• How do I filter text values in Excel?

• How do I reset or change a filter?

Manually selecting dates in a large dataset can take a lot of time.

Imagine you want to find all transactions that happened before January 7th. If you try to check the boxes for each date manually, you would have to scroll through dozens of entries.

Excel provides a much faster way to do this using Date Filters.

In this lesson, you’ll learn how to filter data based on a date condition instead of selecting each value manually.

What you will learn in this video

• How to apply a filter to a dataset
• How to open the dropdown filter in a date column
• How to use Date Filters → Before to filter transactions before a specific date
• How to clear existing filters before applying a new one

For example, instead of manually selecting January 1st through January 6th, you can simply apply the condition:

Date → Before → 01/07/2023

Excel will instantly display only the rows that meet that condition.

Another important detail shown in this lesson is how filtering works internally. When a filter is applied, Excel hides rows that do not match the condition. The data is not deleted and can be restored by clearing the filter.

Practical outcomes from this video

• Filter large datasets by specific date conditions
• Avoid manually selecting multiple date values
• Understand how Excel hides non-matching rows during filtering

Common questions this video answers

How do I filter dates before a specific date in Excel?
What are Date Filters in Excel?
How do I clear filters in Excel?
Does filtering delete rows in Excel?

Filtering becomes more powerful when you apply multiple conditions to the same column. This is especially useful when working with date-based data.

In this example, the task is to find all transactions that happened after January 7 and before January 12.

Excel provides a built-in way to handle this using Date Filters.

What you will learn in this lesson:

• How Excel automatically detects date columns and provides date-specific filtering options

• How to apply multiple criteria to a single column

• How to use the Between filter option to define a date range

By selecting the Between option and entering the start and end dates, Excel displays only the records that fall within that range.

Practical outcomes from this video:

• Analyze transactions within a specific time window

• Apply multiple conditions to date fields quickly

• Filter datasets using date ranges without writing formulas

Common questions this video answers:

• How do I filter Excel data between two dates?

• What is the Date Filter option in Excel?

• Can Excel filter records within a specific date range?

• How do I apply multiple conditions to one column in Excel?

Filtering isn’t limited to text or dates. You can also filter time-based data in Excel.

In this lesson, you’ll learn how to apply filters using time conditions with multiple criteria.

The task here is simple: show only the transactions that happened between 10:00 AM and 12:00 PM, regardless of the date.

What you will learn in this video

• How to apply filters to a dataset containing time values
• Why Excel shows Number Filters instead of Time Filters
• How Excel internally stores time as numeric values (decimal numbers)
• How to filter time data using the Between condition

To filter the data, you open the filter dropdown on the time column, select Number Filters → Between, and specify the range:

• Start time: 10:00 AM
• End time: 12:00 PM

Excel then displays only the rows where the transaction time falls within that range.

You’ll also notice that after applying the filter, only a portion of the dataset remains visible. For example, only about 196 rows may match the condition out of thousands of records.

Practical outcomes from this video

• Filter datasets based on time ranges
• Understand how Excel stores time internally
• Apply multiple criteria using conditional filters

Common questions this video answers

How do I filter time values in Excel?
Why does Excel show Number Filters for time columns?
How do I filter data between two times in Excel?
How can I analyze transactions within specific hours?

Filtering becomes even more powerful when you apply conditions across multiple columns at the same time. This allows you to answer more complex business questions from a dataset.

In this example, the task involves filtering data based on four different fields:

• Contact Date

• Contact Time

• Time to Answer

• Product Name

Each column applies a different condition, and Excel combines all of them to return only the rows that satisfy every rule.

What you will learn in this lesson:

• How to apply filters across multiple columns simultaneously

• How to filter dates after a specific day

• How to filter time values within a range

• How to filter numeric values using conditions like less than

• How to filter text values using the search box

The conditions used in this example include:

• Transactions after January 12

• Contact time between 11:30 AM and 3:00 PM

• Time to answer less than 100 seconds

• Product name containing the word “crispy”

Excel processes all these filters together and shows only the rows that meet every condition.

Practical outcomes from this video:

• Analyze datasets using multiple filtering conditions

• Combine date, time, numeric, and text filters together

• Quickly narrow large datasets down to meaningful results

Common questions this video answers:

• Can I filter multiple columns in Excel at the same time?

• How do I filter by date, time, and numbers together?

• How do I search for text within a filter in Excel?

• How do I apply multiple conditions to a dataset?

Filtering helps you hide irrelevant data, but sometimes you don’t want to hide anything. You simply want to rearrange the data to make it easier to interpret.

That’s where Sorting becomes useful.

Sorting allows you to organize your dataset in a logical order, such as arranging transactions by date, amount, or name.

For example, in a transaction dataset, the dates might appear randomly. You may see January 2nd, followed by January 1st, then January 3rd, making it difficult to analyze trends.

When you apply sorting on the contact date column, Excel rearranges the entire dataset so that:

• January 1st appears first
• Then January 2nd
• Then January 3rd

Excel automatically moves the entire row together, so your data remains correctly aligned.

What you will learn in this video

• What sorting is and why it is useful for data interpretation
• The difference between Filtering and Sorting
• Where to find the Sort and Filter option in Excel
• The requirement for sorting, which is selecting any cell within the dataset

Unlike filtering, which works mainly from the header row, sorting works directly on the dataset itself.

You can also apply sorting using a shortcut:

Alt + H + S

Practical outcomes from this video

• Arrange datasets in logical order
• Improve readability and pattern recognition
• Prepare data for deeper analysis

Common questions this video answers

What is sorting in Excel?
What is the difference between sorting and filtering?
How do I sort data by date in Excel?
How do I rearrange rows without breaking the dataset?

Sorting helps organize data so patterns and trends become easier to see. A common example is arranging records by date, from the earliest to the most recent.

In this video, you will learn how to sort a dataset based on a date column.

What you will learn in this lesson:

• How to sort data by oldest to newest or newest to oldest

• Why it is better to select a data cell instead of the header when sorting

• How Excel automatically detects the data type in the selected cell

For example:

• If you select the column header, Excel may treat the column as text and show options like Sort A to Z.

• If you select a cell containing a date, Excel recognizes the data type and shows options like Oldest to Newest.

Once the sort is applied, Excel rearranges the entire dataset, not just that single column, ensuring that each row stays intact.

Practical outcomes from this video:

• Organize datasets chronologically

• Identify earliest or latest records quickly

• Avoid sorting errors by selecting the correct cell

Common questions this video answers:

• How do I sort dates in Excel from oldest to newest?

• Why does Excel sometimes show A to Z instead of date sorting?

• Should I select the header or the first data cell when sorting?

• Does Excel sort only one column or the entire row?

Sometimes filtering alone is not enough. The real power comes when you combine filtering and sorting together.

In this lesson, you’ll learn how to first filter specific records and then sort the remaining data to make interpretation easier.

The task in this example is:

• Show only the transactions that happened on 24th February
• Then arrange those transactions by agent name

What you will learn in this video

• How to apply a filter to display data for a specific date
• How to manually select a date when filtering
• How to sort the filtered results using A to Z sorting
• How sorting works even after a filter has already been applied

Once the dataset is filtered to show only 24th February transactions, you can sort the Agent column alphabetically.

Excel will then rearrange the visible rows while keeping the filtered condition intact.

For example, agents like Adrian, Albion, and Harvey will appear grouped together, making it easier to see how many transactions each handled that day.

Practical outcomes from this video

• Analyze specific days within large datasets
• Quickly group records by agents or categories
• Improve data interpretation using combined tools

Common questions this video answers

How do I filter and sort data at the same time in Excel?
How do I sort filtered data in Excel?
How do I filter transactions for a specific date?
Can Excel sort data after applying a filter?

Sometimes sorting a dataset requires more than one level of organization. For example, you might want to group records by one column and then sort each group by another column.

This is where Custom Sorting in Excel becomes useful.

In this example, the task involves three steps:

• Filter transactions for 24th February

• Sort records by Agent Name

• Within each agent, sort the Contact Time from morning to evening

To achieve this, Excel’s Custom Sort option is used.

What you will learn in this lesson:

• How to combine filtering and sorting in the same dataset

• How to open the Custom Sort window in Excel

• How to apply sorting on multiple columns using Add Level

• How Excel sorts data in hierarchical order

For example:

1. First level: Sort by Agent Handled

2. Second level: Sort by Contact Time

Excel first groups all rows by agent, and then sorts the time within each agent group.

Practical outcomes from this video:

• Organize datasets using multiple sorting layers

• Maintain logical grouping while sorting

• Perform structured analysis on filtered datasets

Common questions this video answers:

• How do I sort by multiple columns in Excel?

• What is Custom Sort in Excel?

• How does the Add Level option work?

• Can I filter data and then apply multi-level sorting?

Advanced Filter in Excel | Filter Data Using Criteria Cells

Filtering data in Excel is easy… until you have to repeat it again and again.

Imagine working with a dataset where you constantly check values like ratings above a certain number. Normally, you open the filter dropdown, select values, and repeat the process every time the condition changes. It works, but it quickly becomes tedious.

This is where Advanced Filter in Excel becomes useful.

Instead of manually applying filters, you can place your filter condition in a separate cell and use Advanced Filter to filter the dataset based on that criteria.

In this video, you’ll learn how this powerful but often overlooked feature works.

What you'll learn

• Where to find Advanced Filter in Excel

• How to create a criteria range

• Why the criteria header must match the dataset header

• How to filter values like Rating > 7

• The difference between filtering in place and copying results

• A key limitation: Advanced Filter does not update automatically when criteria changes

Example shown in the video

We filter a dataset to show only rows where the rating is greater than 7, using a separate criteria cell instead of the standard filter dropdown.

This approach is helpful when you want your filtering logic in one place and want cleaner control over your dataset.

Questions this video answers

• How to use Advanced Filter in Excel

• How to filter Excel data using criteria from another cell

• What is a criteria range in Excel

• Why Advanced Filter is not dynamic

Ever tried filtering data in Excel and still couldn’t find the exact rows you needed?

You add one filter, then another, then another. Soon you’re clicking through columns repeatedly and still not getting the precise results. This usually happens when your dataset has many columns and you need multiple conditions applied at the same time.

That’s where Excel Advanced Filter with multiple criteria becomes useful.

In this video, you’ll learn how to filter a dataset using conditions across several columns. Instead of applying filters one by one, you’ll create a criteria range and let Excel return only the rows that match all your conditions.

What you’ll learn

• How to apply Advanced Filter on multiple columns in Excel

• How to create and use a criteria range

• How to filter using operators like > and <

• How to filter data based on exact values

• How to quickly change criteria and re-run the filter

Example covered

We filter a dataset based on conditions such as:

• Conversation duration greater than a specific value

• Contact type like Complaint

• Customer rating below a certain score

This method helps you quickly isolate the exact records you want for analysis.

Questions this video answers

• How do I use Advanced Filter with multiple criteria in Excel?

• How do I filter multiple columns at once?

• How do operators like greater than or less than work in filters?

• Why does Excel sometimes return no results after filtering?

If you work with large datasets, this technique can save a lot of time when analyzing data.

Excel Advanced Filter: Copy Filtered Data to Another Sheet

Sometimes you don’t just want to filter data. You want the filtered results to appear in a completely different sheet.

Many people solve this by filtering data, copying the results, and pasting them somewhere else. The problem? If the data changes later, you’re never sure whether your pasted data is still correct.

This is where Excel’s Advanced Filter becomes very useful.

In this video, you’ll learn how to filter a dataset and automatically copy only the matching rows to another worksheet without manually copying and pasting.

What you’ll learn

• How to use Advanced Filter to copy results to another sheet

• The difference between “Filter in place” and “Copy to another location”

• Why you must select a cell in the output sheet first

• How to apply multiple filtering criteria at once

• A faster way to extract data for analysis or reporting

Example covered in the video

We filter a dataset using multiple conditions such as:

• Duration greater than 300

• Contact type = Request

• Agent handle = Government

• Product price less than 100

Instead of filtering inside the original table, Excel copies only the matching rows to an output sheet, keeping the results clean and organized.

This technique is especially useful when working with large datasets, consolidated reports, or analysis sheets.

Questions this video answers

• How to copy filtered data to another sheet in Excel

• How to use Advanced Filter with multiple criteria

• Why Excel shows “copy to another location” errors

• How to extract filtered data without manual copy paste

When working with Pivot Tables in Excel, there are two things you always need:

• A structured dataset with headers

• A selected range that includes those headers

Unlike filtering or sorting, Pivot Tables require the column headers to be included because Excel uses them to understand how the data should be grouped and analyzed.

In this video, you’ll learn the basic structure of how a Pivot Table works in Excel.

Key idea explained

A Pivot Table operates through three main parts:

• Source – the original dataset

• Front end – the area where you control and view the analysis

• Back end – where Excel performs the calculations automatically

The front end contains two important sections:

• Display area where results appear

• Control panel where you decide how the data should be summarized

What you’ll see in this video

• How to create your first Pivot Table in Excel

• Why selecting the entire dataset including headers is important

• How to insert a Pivot Table from the Insert tab

• Choosing between a new worksheet or existing worksheet

• Understanding the Pivot Table layout and interface

Questions this video answers

• How do Pivot Tables work in Excel?

• What data do I need before creating a Pivot Table?

• Why must headers be included in a Pivot Table?

• What happens when you insert a Pivot Table?

How Pivot Tables Work in Excel | Understanding the Basics

Before creating your first Pivot Table, it helps to understand what actually happens behind the scenes.

When you insert a Pivot Table in Excel, two important things happen instantly.

First, Excel creates a separate area for analysis. Instead of mixing calculations with your raw dataset, Excel typically places the pivot table in a new sheet. This keeps your original data clean and untouched while you analyze it elsewhere.

Second, Excel builds a connection to your dataset.

Your data fields (the column headers) appear in the Pivot Table Fields panel. These fields act as controls that allow you to organize and summarize the data in different ways.

At this stage, no calculations have been done yet. Excel is simply ready and waiting for your instructions.

What you’ll learn in this video

• What happens when you insert a Pivot Table in Excel

• Why Excel separates raw data from analysis

• How Pivot Table Fields are created from your dataset

• How Excel performs calculations in the background

Simple example explained

If you have a dataset with:

• Fruit Name

• Price

You can drag the Price field into the Values area. Excel then goes back to the original data, calculates the sum of all prices, and instantly shows the result in the pivot table.

All calculations happen in the background, and the pivot table simply displays the final output.

Questions this video answers

• What happens when you create a Pivot Table in Excel

• What are Pivot Table Fields

• How pivot tables connect to original data

• How pivot tables calculate values automatically

Imagine you have a dataset full of sales transactions and one simple question:

What is the total sales for the month?

Instead of manually adding numbers, this is where Pivot Tables in Excel make analysis quick and easy.

In this video, we focus on one important part of Pivot Tables: the Values field.

What you’ll learn

• How to insert a Pivot Table in an existing worksheet

• How to choose the correct field for calculation

• How the Values area automatically summarizes data

Example covered

The dataset contains sales transactions for a single month. To find the total sales:

• Create a Pivot Table

• Locate the Final Price column (the actual sale amount after discounts)

• Drag it into the Values area

Excel immediately calculates the total sales for the month by summing all the transactions.

Questions this video answers

• How do I calculate total sales using a Pivot Table?

• What is the Values area in a Pivot Table?

• How does Excel automatically summarize numbers in Pivot Tables?

Pivot Table Columns in Excel | When and Why to Use the Columns Area

When working with Pivot Tables, most people understand Rows and Values. But the Columns area often creates confusion.

If one field already gives you the result you want, why place another field in Columns?

The answer is simple: Columns help you compare categories side by side.

The problem this video solves

Imagine you already have a Pivot Table showing average sales by agent. That’s useful, but it doesn’t tell you the full story.

What if you want to compare:

• Online transactions vs Physical visits

• Sales performance across different order types

• Category performance side by side

Without columns, those comparisons become difficult.

What you’ll learn in this video

• When to use the Columns area in a Pivot Table

• How to compare categories side by side

• How to remove unnecessary fields from a Pivot Table

• How Pivot Tables automatically update when fields change

Example explained

We calculate average sales by agent, then add another layer of analysis.

By placing Order Type in the Columns area, Excel splits the results into:

• Online transactions

• Physical visits

Now the pivot table clearly shows how each agent performs across both transaction types.

For example:

• One agent may perform better with online sales

• Another may generate higher revenue through physical visits

This comparison becomes instantly visible because of the Columns field.

Questions this video answers

• When should I use columns in a Pivot Table

• What does the Columns area do in Excel Pivot Tables

• How to compare categories side by side in a Pivot Table

• How to analyze sales by multiple categories in Excel

A Pivot Table can perform powerful calculations, but if the labels are unclear, the report quickly becomes confusing.

In this video, you’ll learn how to manage and rename Pivot Table labels to make your analysis easier to understand.

What you’ll learn

• How to edit labels directly inside a Pivot Table

• Why copying external text into Pivot Tables often doesn’t work

• How to rename Row Labels and Column Labels

• How clear labels improve the readability of your report

Example covered

In the Pivot Table report:

• Row Labels represent the agents handling sales

• Column Labels represent the mode of purchase such as online or offline

You’ll see how to rename these fields to something clearer like:

• Agents

• Mode of Purchase

• A report title such as Average Sales Analysis

This simple step makes the Pivot Table easier for anyone to understand.

Questions this video answers

• How do I rename Row Labels and Column Labels in a Pivot Table?

• Why can’t I paste external data into Pivot Table cells?

• How do I make Pivot Table reports clearer and easier to read?

How to Sort Pivot Table Data in Excel | Sort by Values (Highest to Lowest)

When analyzing data with Pivot Tables, the results are not always shown in the most useful order.

For example, if you calculate total sales by product, Excel may display the products alphabetically. But in most business situations, you want to see which products generate the most revenue first.

That’s where sorting Pivot Table values becomes important.

What you'll learn in this video

• How to sort Pivot Table results by values

• How to arrange results from highest to lowest

• Two simple ways to sort Pivot Table data

• How sorting helps identify top-performing products

Example explained

In this example, we calculate overall sales for each product using a Pivot Table.

By default, Excel sorts the products alphabetically. But for analysis, we sort the data based on total sales value, showing the highest-selling products at the top.

Two ways to sort Pivot Table data

Method 1: Right-click sorting

1. Select a value inside the sales column

2. Right-click

3. Choose Sort → Largest to Smallest

Method 2: Sort from Pivot Table options

1. Click the filter arrow in the row labels

2. Go to More Sort Options

3. Choose Descending order based on sales value

Both methods organize the Pivot Table so you can quickly see which products generate the most revenue.

Questions this video answers

• How to sort a Pivot Table by values

• How to sort Pivot Tables from largest to smallest

• Why Pivot Tables sort alphabetically by default

• How to rank products by sales using Pivot Tables

Sometimes you don’t want to analyze all the data in a Pivot Table. You only want results for a specific group.

In this example, the business question is simple:

What are the overall sales of crispy products?

Instead of rebuilding the Pivot Table, we simply apply a Pivot Table filter.

What you’ll learn

• How to filter data inside a Pivot Table

• How to use Label Filters

• How to quickly show results for a specific category

Example covered

In the dataset, some products are marked as crispy while others are not.

To display only crispy items:

• Open the Pivot Table filter options

• Choose Label Filter → Contains

• Enter “crispy”

Excel instantly filters the Pivot Table to show sales only for crispy products.

Types of Pivot Table filters

• Report Filter

• Label Filter

• Value Filter

In this lesson, we focused on Label Filters.

Questions this video answers

• How do I filter products in a Pivot Table?

• What is a Label Filter in Excel Pivot Tables?

• How do I filter items that contain a specific word?

Sometimes you want your entire Pivot Table to show results for only one category of data.

In this example, the business question is:

Show the sales report only for orders placed online.

Instead of placing the field in columns and filtering it there, a cleaner solution is to use a Report Filter in a Pivot Table.

What you’ll learn

• What a Report Filter in Pivot Tables is

• How to filter the entire report using one field

• Why Report Filters keep Pivot Tables clean and easy to read

Example covered

To filter the report for online orders only:

• Drag the Order Type field into the Filters area

• Use the dropdown at the top of the Pivot Table

• Select Online

The Pivot Table instantly updates, showing results only for online transactions.

If you switch the filter to Physical Visit, the report automatically updates to display offline transactions instead.

Questions this video answers

• What is a Report Filter in a Pivot Table?

• How do I filter a Pivot Table report by one category?

• What is the difference between Report Filter and Column filtering?

Pivot Table Number Formatting in Excel | Format Values the Right Way

Sometimes Pivot Tables show numbers in a way that’s hard to read. You might see long decimal values or inconsistent formatting across the report.

Many people try to fix this by selecting cells and applying formatting from the Home tab. While this works temporarily, it’s not the best approach. Some cells, like grand totals or hidden values, may not get formatted properly.

The better method is to format the Pivot Table field itself.

What you’ll learn in this video

• Why manual formatting in Pivot Tables can cause problems

• How to properly format numbers in a Pivot Table

• How to use the Number Formatting option

• How to apply formatting to an entire field at once

Example explained

In this lesson, we format the average sales values in a Pivot Table.

Instead of formatting individual cells, we:

1. Right-click on a value inside the Pivot Table

2. Select Number Formatting

3. Choose the desired format (for example, Number with two decimals)

Excel then automatically applies the formatting to the entire Pivot Table field, including totals and future updates.

Questions this video answers

• How to format numbers in Pivot Tables

• Why Pivot Table formatting resets sometimes

• What is Number Formatting in Pivot Tables

• How to format Pivot Table values correctly

Using Multiple Fields in Pivot Tables | Analyze Sales and Customer Ratings

Until now, we’ve used Pivot Tables to calculate values from one field at a time, such as total sales, average sales, or number of transactions.

But real business analysis often requires combining multiple fields to understand what’s actually happening.

For example, what if you want to see:

• Total sales for each day

• Customer satisfaction ratings for the same day

Looking at both together can reveal useful patterns.

What you’ll learn in this video

• How to use multiple fields in a Pivot Table

• How to add more than one value field to a report

• How to change calculations like Sum to Average

• How to analyze relationships between different metrics

Example explained

In this lesson, we analyze data using two fields:

• Total Sales per Day

• Average Customer Rating per Day

The steps are simple:

1. Use Date in the Rows area to group sales by day

2. Add Sales value to calculate total sales

3. Add Ratings Given to the Values area

4. Change the calculation from Sum to Average

Now the Pivot Table shows daily sales alongside customer satisfaction scores.

This makes it easier to identify patterns, such as whether higher ratings lead to higher sales.

Questions this video answers

• Can Pivot Tables use multiple fields for calculations

• How to add multiple value fields in a Pivot Table

• How to calculate averages and totals together in Excel

• How Pivot Tables help find patterns in data

Sometimes you don’t need to analyze the entire dataset. You just want to focus on the best and worst performers.

In this video, we use Pivot Tables to answer a common business question:

Which are the top 3 and bottom 3 days based on overall sales, and what were the customer ratings for those days?

What you’ll learn

• How to use Value Filters in Pivot Tables

• How to find Top N or Bottom N values

• How to analyze multiple metrics at the same time

Example covered

Using the Value Filter → Top 10 option, we adjust the settings to:

• Show Top 3 days by overall sales

• Display the average customer rating for those days

Then we repeat the same process to find the Bottom 3 days by sales.

This helps reveal interesting insights, such as days where sales were low but customer satisfaction was still high.

Questions this video answers

• How do I find the top 3 or top 10 values in a Pivot Table?

• How do I show bottom performing items in Excel?

• How do I filter Pivot Tables using Value Filters?

Pivot Table Not Updating? How to Refresh Pivot Tables in Excel

You update your dataset in Excel… but the Pivot Table still shows the old numbers.

Confusing, right?

This happens because Pivot Tables don’t automatically update when the source data changes. Even though the pivot table is connected to the dataset, it still needs to be refreshed to reflect any updates.

What you’ll learn in this video

• Why Pivot Tables don’t update automatically

• How Pivot Tables stay connected to the source data

• How to refresh a Pivot Table after data changes

• The difference between Refresh and Refresh All

Example explained

In this example, the original dataset had a sale recorded on 23rd December. After correcting the data, the date was changed to 23rd November.

However, the Pivot Table still showed 23rd December.

Why? Because the Pivot Table had not been refreshed.

To update the report:

1. Click anywhere inside the Pivot Table

2. Go to Pivot Table Analyze

3. Click Refresh or Refresh All

Once refreshed, the Pivot Table updates and reflects the corrected data.

Questions this video answers

• Why Pivot Tables don’t update automatically

• How to refresh a Pivot Table in Excel

• What is the difference between Refresh and Refresh All

• How to update Pivot Tables after changing source data

One common challenge with Pivot Tables appears when new rows are added to the dataset.

This happens because most Pivot Tables are created using a fixed data range.

What’s the problem?

When you select a range like A1:R823 while creating a Pivot Table, Excel assumes the data ends at row 823.
If new rows are added later, the Pivot Table won’t include them automatically.

What you’ll learn

• What a fixed range means in Pivot Tables

• Why new data may not appear in your report

• A simple way to make Pivot Tables more flexible

Example covered

Instead of selecting only the existing rows, you can:

• Select entire columns of the dataset

• Create the Pivot Table using that column range

This allows Excel to consider future rows that may be added later.

One side effect you may notice is a blank row appearing in the Pivot Table, which can easily be removed using the filter options.

Questions this video answers

• Why doesn’t my Pivot Table update when I add new data?

• What is a fixed range in Excel Pivot Tables?

• How can I make Pivot Tables include future rows automatically?

Using Excel Tables with Pivot Tables | Automatically Include New Data

One common problem with Pivot Tables appears when new rows are added to the source data.

You update the dataset, refresh the Pivot Table… and the new data still doesn’t appear. This happens because the Pivot Table is connected to a fixed data range.

The solution is simple: convert the dataset into an Excel Table.

What you’ll learn in this video

• Why normal data ranges create problems for Pivot Tables

• How to convert data into an Excel Table

• How tables automatically expand when new data is added

• Why tables make Pivot Tables easier to maintain

Example explained

First, the dataset is converted into a table using:

Home → Format as Table

Once converted, Excel assigns the table a name and automatically tracks its boundaries.

Now something powerful happens:

• Add a new row, the table expands automatically

• Add a new column, it becomes part of the table instantly

When the Pivot Table uses this table as its data source, any new rows added later will appear in the report simply by clicking Refresh.

Why this matters

Using tables prevents a common issue where Pivot Tables miss newly added data because the source range was fixed.

Tables ensure your Pivot Table always references the entire expanding dataset.

Questions this video answers

• Why Pivot Tables don’t include new rows

• How to use an Excel Table as a Pivot Table source

• How to automatically include new data in Pivot Tables

• What are the advantages of Excel Tables

Excel Pivot Table Slicers | Filter Multiple Pivot Tables at Once

Pivot Tables are powerful, but filtering them can quickly become frustrating.

Imagine you have multiple Pivot Table reports on the same sheet. Now you want to answer a simple question like:

• What are the total sales for online orders only?

• What about offline or physical visits?

Normally, you would apply a report filter inside a Pivot Table. The problem is that this filter only affects one Pivot Table at a time, not the entire report.

That’s where Slicers in Excel Pivot Tables come in.

What you’ll learn in this video

• What Slicers are in Pivot Tables

• How slicers act as visual filters

• How to connect one slicer to multiple Pivot Tables

• How slicers make dashboards easier to use

Example explained

In this lesson, we create a slicer using the Order Type field.

Once added, the slicer shows options like:

• Online

• Physical Visit

Clicking any option instantly filters the Pivot Table.

But the real power comes from Report Connections.

By connecting the slicer to multiple Pivot Tables on the sheet, a single click updates every report at the same time.

We also add another slicer for Agent Name, allowing us to filter the dashboard by both:

• Order Type

• Agent Performance

This makes Pivot Table reports feel more like interactive dashboards.

Questions this video answers

• What is a slicer in Excel Pivot Tables

• How to filter multiple Pivot Tables at once

• How to connect slicers to Pivot Tables

• How to build interactive Pivot Table dashboards

When working with dates in Pivot Tables, regular slicers can become difficult to use, especially if your data spans several months or years.

That’s where Timelines in Pivot Tables help.

A timeline is a special type of slicer designed specifically for date fields.

What you’ll learn

• What a Pivot Table Timeline is

• How timelines make date filtering easier

• How to analyze data by days, months, quarters, or years

Example covered

Instead of using a slicer for dates, you can:

• Select the Pivot Table

• Go to Insert Timeline

• Choose the Contact Date field

The timeline lets you quickly filter data by:

• Specific days

• Months

• Quarters

• Years

You can also connect the timeline to different Pivot Tables using Report Connections, so multiple reports update at the same time.

Questions this video answers

• What is a Timeline in Excel Pivot Tables?

• How is a Timeline different from a slicer?

• How do I filter Pivot Table data by date range?

In this lesson, we bring everything together and build a complete Pivot Table report to answer multiple business questions from the dataset.

Instead of creating each report from scratch, we create one Pivot Table and duplicate it, saving time while keeping formatting consistent.

What you’ll learn

• How to build a multi-question Pivot Table report

• How to reuse and duplicate Pivot Tables efficiently

• How to calculate key business metrics in Excel

• How to connect slicers to multiple Pivot Tables

Metrics created in the report

The report answers several questions, including:

• Overall Sales

• Average Customer Satisfaction (CSAT)

• Number of Orders

• Average Discount Given

Then the analysis is expanded to show:

• Agent-wise sales performance

• Product-wise sales performance

Adding interactive filters

To make the report interactive, we add slicers for:

• Order Type

• Contact Type

Using Report Connections, the slicers are linked to all Pivot Tables so the entire report updates instantly when a filter is selected.

Questions this video answers

• How do I build a complete Pivot Table report in Excel?

• How can I reuse Pivot Tables instead of creating new ones?

• How do I connect slicers to multiple Pivot Tables?

• How can I analyze sales by agent, product, and customer satisfaction?

Conditional Formatting in Excel | Quickly Identify Trends and Insights

Imagine looking at a column of numbers and trying to answer a simple question:

Which day had the highest sales last month?

You might start scanning the numbers from top to bottom, comparing values one by one. After a few seconds, you may guess the highest number is around 10,000, but you’re still not completely sure.

Now try another question:

Which day had the lowest sales?

Again, you’d scan through the numbers and estimate something around 4,000 or 5,000.

This highlights a common problem when working with raw data. Numbers alone are difficult for the human eye to interpret quickly.

Why Conditional Formatting matters

Humans process visual patterns much faster than plain numbers. This is where Conditional Formatting in Excel becomes powerful.

Conditional Formatting automatically applies colors, highlights, or visual indicators to cells based on their values. This helps you instantly spot:

• Highest values

• Lowest values

• Patterns or trends in the data

• Outliers and unusual numbers

Instead of manually scanning the dataset, the visual formatting immediately shows what stands out.

What you’ll learn next

In the next lesson, you’ll learn how to apply Conditional Formatting in Excel to transform plain data into clear visual insights.

Questions this video answers

• What is Conditional Formatting in Excel

• Why Conditional Formatting is useful for data analysis

• How to quickly identify highest and lowest values in Excel

• How to visually highlight important data in spreadsheets

Numbers alone can make patterns hard to spot. But when you add color, the story becomes obvious.

That’s the power of Conditional Formatting in Excel.

In this example, we analyze daily sales to answer two simple questions:

• Which was the best sales day of the month?

• Which was the worst sales day?

Once conditional formatting is applied, the highest and lowest values are highlighted with colors. Instead of scanning through numbers, you can instantly identify patterns.

For example:

• The best sales day becomes easy to spot because the highest value stands out in color.

• The worst sales day also becomes immediately visible.

This works because the human brain is naturally good at recognizing color patterns, which makes visual analysis much faster than reading raw numbers.

What you’ll learn

• What Conditional Formatting in Excel is

• How it helps quickly identify high and low values

• Why it’s useful for data storytelling and reporting

You can find this feature in Home → Conditional Formatting, where Excel offers several options such as:

• Highlight Cell Rules

• Top/Bottom Rules

• Data Bars

• Color Scales

Excel Data Bars Explained | Visual Comparison with Conditional Formatting

When working with numbers, comparing values quickly can be difficult. Looking at a column of numbers doesn’t always make it obvious which values are higher or lower.

This is where Data Bars in Excel Conditional Formatting become useful.

Data bars turn numeric values into horizontal bars inside cells, making it much easier to compare numbers visually.

What Data Bars do

When you apply a data bar, Excel first scans the entire dataset and identifies the highest value. That value gets a fully filled bar.

Every other value is then displayed as a proportion of the highest value.

For example:

• If the highest value is 1000, that cell gets a full bar

• A value of 900 will fill about 90% of the cell

• Smaller values fill proportionally less space

This instantly shows which values are larger, smaller, or close to each other.

Two types of Data Bars

Excel provides two styles of data bars:

Solid Fill Data Bars

• Cells are filled with a solid color

• The bar length shows the value comparison

• Best when you want clear visual comparisons

Gradient Data Bars

• The bar gradually fades from color to white

• Emphasizes the shading effect

• Useful when you want a softer visual style

Example scenario

Imagine tracking pocket money received over 10 days.

Applying data bars immediately shows:

• Which days had the highest amounts

• Which days had lower values

• Whether the amounts follow a pattern or appear random

Instead of reading numbers one by one, the comparison becomes instantly visible.

Questions this video answers

• What are Data Bars in Excel Conditional Formatting

• How Data Bars help compare values in Excel

• Difference between solid and gradient data bars

• When to use Data Bars for data visualization

Conditional Formatting becomes very powerful when you want to compare values visually instead of reading numbers.

In this example, we answer two questions using Data Bars in Excel.

Question 1: Compare product sales visually

To quickly compare product performance:

• Select the sales values

• Go to Home → Conditional Formatting

• Choose Data Bars

• Apply a solid fill color

Excel instantly adds bars inside the cells, making it easy to compare products.
You can quickly see which product performed best and which performed worst.

Question 2: Compare visually without showing the numbers

Sometimes you may want to show the comparison but hide the actual values.

To do that:

• Select the same cells

• Go to Conditional Formatting → Data Bars → More Rules

• Enable Show Bar Only

Now the bars remain visible, but the numbers are hidden from the sheet (they’re still visible in the formula bar).

What you’ll learn

• How to use Data Bars in Conditional Formatting

• How to visually compare values across products

• How to hide values while still showing comparisons

Questions this video answers

• How do Data Bars work in Excel?

• How do I compare numbers visually using Conditional Formatting?

• How do I hide values but keep data bars visible?

Customizing Data Bars in Excel | Conditional Formatting with Threshold Values

Data Bars are great for comparing numbers visually. But sometimes you don’t want them applied to every value. You may want the visual highlight to appear only when values cross a specific threshold.

For example, imagine analyzing product sales performance. You might want to highlight only products where sales exceed 25,000.

Instead of manually selecting a few cells, the better approach is to create a rule-based conditional format that adapts when the data changes.

What you’ll learn in this video

• How to customize Data Bar conditional formatting

• Why selecting only a few cells can break your visuals later

• How to apply formatting rules that update with Pivot Tables

• How to set minimum value thresholds

Example explained

In this scenario, we compare product performance and highlight only products where:

Total Sales > 25,000

To do this properly:

1. Select the Pivot Table values

2. Go to Conditional Formatting → Data Bars → More Rules

3. Apply the rule to all cells showing product totals

4. Set the minimum value to 25,000

5. Leave the maximum value as Automatic

Now the data bars appear only for products crossing the sales threshold.

Why this approach works better

Because the rule is applied to the entire Pivot Table field, the visualization automatically adjusts when:

• Pivot Table values change

• Filters or slicers are applied

• New data updates the report

For example, after applying a slicer:

• Only products above 25,000 sales remain highlighted

• The visualization updates instantly

Questions this video answers

• How to customize Data Bars in Excel

• How to highlight values above a number using conditional formatting

• How to apply conditional formatting in Pivot Tables

• How to create threshold-based visual comparisons

Color Scales in Excel help you visualize patterns in data using colors instead of bars. They highlight values based on how high or low they are compared to the rest of the dataset.

There are two main types of color scales in Conditional Formatting.

Three-Color Scale

A three-color scale uses three colors to represent the data range.

Here’s how it works:

• Excel scans the dataset to find the highest and lowest values

• The highest values get the darkest shade of the first color

• As the values decrease, the color gradually becomes lighter

• At the midpoint, the color transitions into a second color

• Toward the lowest values, the shading shifts to the third color

Even though it may look like many colors, Excel is actually using three colors with different shades.

Two-Color Scale

A two-color scale is simpler.

• One color represents the highest values

• Another color represents the lowest values

• Excel fills the cells with gradual shades between these two colors

This creates a quick visual gradient from low to high values.

What you’ll learn

• What Color Scales in Conditional Formatting are

• The difference between two-color and three-color scales

• How color gradients help reveal patterns in data

Questions this video answers

• What are Color Scales in Excel?

• What is the difference between two-color and three-color scales?

• How can I visually highlight high and low values in Excel?

Excel Color Scales | Visualize Data Trends with Conditional Formatting

Sometimes you want to see patterns in your data quickly, especially when comparing values across a dataset. Reading numbers one by one makes this difficult, but Color Scales in Excel make trends instantly visible.

Color scales use three colors to represent value ranges, helping you quickly identify high, medium, and low values.

What you’ll learn in this video

• What Color Scales are in Conditional Formatting

• How to apply color scales to visualize data trends

• How to highlight highest, middle, and lowest values

• How different color combinations represent different meanings

Example explained

In this example, we visualize sales trends across a dataset using color scales.

Each cell automatically receives a color based on its value:

• Highest values get the top color

• Middle values get a neutral color

• Lowest values get the bottom color

Two situations demonstrated

Situation 1: Highest values in Green

We apply a Green → White → Red color scale:

• Green = highest values

• White = middle range

• Red = lowest values

This makes it easy to quickly spot the best-performing sales numbers.

Situation 2: Highest values in Blue

For another dataset, we apply a Blue → White → Red scale:

• Blue = highest values

• White = middle values

• Red = lowest values

This helps visualize trends using a different color scheme depending on the reporting context.

Why Color Scales are useful

Color scales make it easy to:

• Identify top and bottom performers

• Spot patterns or trends in sales data

• Quickly scan large datasets without reading every number

Questions this video answers

• What are Color Scales in Excel Conditional Formatting

• How to highlight highest and lowest values using colors

• How to visualize data trends in Excel

• What different color scale combinations mean

Color Scales are useful for spotting trends, but sometimes the default settings don’t match the story you want to highlight. In those cases, you can customize the midpoint.

In this example, the goal is to highlight sales trends where the midpoint is 75% instead of the default 50%.

Customizing the midpoint with percentiles

Normally, a three-color scale works like this:

• Lowest value → first color

• 50th percentile (midpoint) → second color

• Highest value → third color

But if you want the midpoint to represent 75% of the data range, you can change it.

Steps:

• Select the data

• Go to Home → Conditional Formatting → Color Scales → More Rules

• Choose Three Color Scale

• Change the Midpoint Type to Percentile

• Set the value to 75

Now Excel highlights values differently, making higher values stand out sooner in the color scale.

Using a fixed midpoint value

Sometimes you don’t want a percentile midpoint. You want a specific number to act as the midpoint.

Example:
If ₹10,000 is the highest value, you might want ₹5,000 to always be the midpoint.

To do this:

• Open Conditional Formatting → More Rules

• Change the Midpoint Type to Number

• Enter 5000

Now any value above ₹5,000 moves toward the high color, and values below it move toward the low color.

What you’ll learn

• How to customize color scales in Excel

• How to change the percentile midpoint

• How to set a fixed numeric midpoint

Questions this video answers

• How do I change the midpoint in Excel color scales?

• How do I set a 75% midpoint in Conditional Formatting?

• How do I use a fixed value as the midpoint?

Excel Icon Sets | Visual Indicators with Conditional Formatting

Sometimes numbers alone don’t clearly show whether performance is good, average, or poor. This is where Icon Sets in Excel Conditional Formatting become useful.

Icon sets add visual indicators to your data, making it easier to quickly interpret performance without reading every number.

What Icon Sets do

Just like other conditional formatting tools, Excel first scans the dataset to identify the highest and lowest values.

But instead of filling cells with colors or bars, Excel adds icons that represent the value level.

For example, cells may display:

• Green arrows for higher values

• Yellow arrows for mid-range values

• Red arrows for lower values

This instantly shows whether a number represents positive, neutral, or negative performance.

Types of Icon Sets in Excel

Excel offers several categories of icon sets, including:

• Directional icons (arrows showing increase or decrease)

• Shapes (circles, triangles, flags)

• Indicators (traffic lights or signals)

• Ratings (stars or bars)

Each type is useful for different situations depending on how you want to communicate the data.

Example scenario

Using the pocket money dataset, Excel scans the numbers and automatically assigns icons based on value ranges.

Instead of colors or data bars, the icons show whether the amount received on a particular day was high, average, or low.

Even though Icon Sets, Color Scales, and Data Bars work similarly behind the scenes, the key difference is how the results are displayed visually.

Questions this video answers

• What are Icon Sets in Excel Conditional Formatting

• How Excel assigns icons to values

• When to use icon indicators instead of color scales

• How icon sets help interpret data faster

Icon Sets in Excel help you quickly show whether values are meeting targets or falling short.

Instead of scanning numbers, icons like arrows or symbols instantly indicate performance.

Example scenario

In this dataset, each product has a sales target of 10,000.

The goal is simple:

• Highlight products that met or exceeded the target

• Show which products did not meet the target

How the icon set is customized

First, apply an Icon Set from:

Home → Conditional Formatting → Icon Sets

Then adjust the rule using Manage Rules → Edit Rule.

Instead of using percentages (Excel’s default), we change the rule to use fixed numbers:

• Green arrow → Sales greater than 10,000

• Yellow arrow → Sales between 9,500 and 10,000

• Red arrow → Sales below 9,500

After applying the rule, each product instantly shows whether it met the sales target or not.

Why this is useful

Icon Sets are especially helpful when:

• You have target values

• You want to track performance quickly

• You need to show whether metrics are good, average, or poor

Questions this video answers

• How do Icon Sets work in Excel Conditional Formatting?

• How do I show whether a target value is met?

• How do I change Icon Set rules from percentages to numbers?

Customizing Icon Sets in Excel | Highlight Performance with Conditional Formatting

Icon Sets are powerful when you want to visually indicate performance levels instead of just showing numbers.

In many business reports, numbers alone don’t clearly show whether a value meets expectations. Icon sets solve this by adding visual signals that instantly communicate performance.

Business scenario in this example

We have a summary table showing:

• Total Sales

• Customer Satisfaction Ratings

The goal is to highlight customer satisfaction levels based on this rule:

• Above 6.5 → Good performance

• Between 6 and 6.5 → Acceptable but needs attention

• Below 6 → Not acceptable

What you’ll learn in this video

• How to customize Icon Sets in Excel

• How to use icons for performance ratings

• How to set custom threshold values

• How to visually highlight problem areas in reports

How the customization works

Instead of using the default arrow icons, we choose Rating icons to better represent customer satisfaction.

Then we define custom rules:

• Value ≥ 6.5 → Full rating icon

• Between 6 and 6.5 → Partial rating

• Value < 6 → Red cross indicator

Once applied, Excel automatically adds the icons next to each value.

Why this is useful

The icons immediately reveal where performance needs attention.

For example, if two days show the red indicator, your eyes instantly focus there. Instead of scanning the numbers, you can quickly ask:

• What happened on those days?

• Why did customer satisfaction drop?

This makes Icon Sets extremely effective for dashboards and performance monitoring.

Questions this video answers

• How to customize Icon Sets in Excel

• How to set threshold values in conditional formatting

• How to use rating icons for performance tracking

• How to highlight low performance in Excel reports

Duplicate values in data can create serious problems, especially when dealing with IDs or transaction records.

In this example, we use Conditional Formatting in Excel to quickly identify duplicate ticket IDs.

Why this matters

Each transaction should have a unique ticket ID. If the same ID appears more than once, it usually indicates a data entry error.

Manually scanning hundreds of rows to find duplicates is difficult. Conditional formatting solves this instantly.

How to highlight duplicate values

Steps:

• Select the column containing the IDs

• Go to Home → Conditional Formatting

• Choose Highlight Cell Rules → Duplicate Values

• Apply the formatting

Excel immediately highlights any duplicate ticket IDs, making them easy to spot.

For example, if ticket ID 1 appears more than once, those cells will be highlighted.

If you correct the value, the highlight disappears automatically.

Important limitation

Conditional Formatting works well when checking duplicates in a single column.

However, if you apply it to an entire dataset, Excel treats each column separately. It does not detect duplicate rows as a whole record.

This means it may highlight repeated values in individual columns even when the entire row is not duplicated.

What you’ll learn

• How to find duplicate values in Excel

• How Conditional Formatting helps identify data errors quickly

• The limitations of duplicate detection in Excel

Questions this video answers

• How do I highlight duplicate values in Excel?

• How can I quickly detect duplicate IDs in a dataset?

• Why doesn’t Excel highlight duplicate rows using conditional formatting?

Excel Conditional Formatting: Icon Sets and Top/Bottom Rules Explained

Conditional formatting is not just about colors or visual trends. Sometimes you simply want clear indicators that tell a story about your data.

This is where Icon Sets and Top/Bottom Rules in Excel become extremely useful.

They help highlight performance, identify extremes, and guide attention to the most important values.

Using Icon Sets for Visual Indicators

Icon sets are best used as performance indicators, not detailed trend analysis.

Instead of filling cells with colors or bars, Excel places icons that show whether a value is performing well or poorly.

For example, when comparing product performance across months, icon sets can quickly indicate whether a value is good, average, or weak.

You can choose from several icon types, such as:

• Check marks and crosses

• Arrows

• Flags or indicators

• Ratings and shapes

These icons allow you to keep the actual numbers visible while still adding a visual signal that draws attention.

However, icon sets compare values across the selected range. If the numbers vary widely, the comparison can sometimes be misleading. In those cases, additional customization may be required.

Highlighting Important Values with Top and Bottom Rules

Another powerful conditional formatting feature is Top/Bottom Rules.

Instead of coloring every cell, this feature highlights only the most important values in a dataset.

For example:

• Highlight the top 10 sales days in a month

• Highlight the bottom 10 performing days

• Highlight only the top 3 and bottom 3 values

Example scenarios

Scenario 1: Highlight Top 10 Sales Days

Select the dataset, then go to:

Conditional Formatting → Top/Bottom Rules → Top 10 Items

Excel automatically highlights the highest values based on the rule.

Scenario 2: Highlight Bottom 10 Sales Days

Using the same option, choose Bottom 10 Items to identify the lowest performing days.

Scenario 3: Highlight Top 3 and Bottom 3 Values

Conditional formatting allows multiple rules on the same data.

You can apply:

• One rule for Top 3 values

• Another rule for Bottom 3 values

Excel evaluates both rules simultaneously and highlights the results accordingly.

Why conditional formatting is powerful

Conditional formatting automatically updates when data changes.

If a number increases or decreases, Excel recalculates the rules and refreshes the highlighting instantly.

This makes it ideal for:

• Sales reports

• Performance dashboards

• Operational monitoring

• Data analysis

Questions this video answers

• How to use Icon Sets in Excel conditional formatting

• How to highlight top or bottom values in Excel

• How to apply multiple conditional formatting rules

• How conditional formatting updates automatically when data changes

In this walkthrough, we review the conditional formatting assignment and see how different formatting techniques help highlight insights in the report.

Instead of using a single rule everywhere, different approaches are applied depending on the type of metric and its purpose.

Summary metrics

For overall summary values, Icon Sets are used.

• The icons are linked to the goal values in the report.

• This allows the formatting to automatically indicate whether the targets are being met.

Agent performance

For agent-level analysis:

• Three-color scales are used for sales performance to highlight the best and worst performing agents.

• For number of orders, a two-color scale is used instead of three colors.
  This avoids creating negative perceptions for metrics that agents may not fully control.

Customer satisfaction

For customer satisfaction (CSAT):

• A mix of icon sets and rating-style indicators is used to show performance clearly.

Average discount

For average discount values, warning-style icons are applied.

This highlights an important business concern:

• Higher discounts reduce profit margins

• Agents should be aware when discount levels become too high

Interactive analysis

Using slicers like online vs offline orders, the report quickly reveals patterns such as:

• Some agents performing better in offline sales

• Discount levels being higher during offline transactions

• Certain products consistently performing better than others

Key takeaway

Conditional formatting turns a simple report into a visual storytelling tool.
Instead of scanning numbers, stakeholders can instantly see:

• What is performing well

• What needs attention

• Where business risks exist

Excel Learning Recap | What You’ve Achieved So Far

Let’s pause and reflect on the progress you’ve made so far.

In this part of the course, you’ve learned 28 different Excel features and applied them across 63 real business situations. Each example focused on solving practical problems using spreadsheets.

You’ve also covered the five major ways spreadsheets are used in business, including the important stage of data visualization with Conditional Formatting.

This is a major milestone in your Excel learning journey.