Module 1: Data Analytics with Excel

Introduction to
Data Analysis
With Excel

Private and Confidential © Career247

 DISCLAIMER
The training content and delivery of this presentation is confidential, and cannot be
recorded, or copied and distributed to any third party, without the written consent
of Career247.
 Table format
• Introduction to Excel in
Analytics
• Excel Interface & Navigation

Agenda •

•
Data Types and Basic
Formatting
Sorting & Filtering
• Working with Excel Tables
• Q&A & Wrap-Up

Private and Confidential © Career247

Introduction to Excel
in Analytics
 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

What is Excel?

Microsoft Excel is a spreadsheet application

developed by Microsoft that enables users to
organize, analyze, and visualize data using a
grid of cells arranged in rows and columns.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Uses of Excel

Business uses: Personal uses: Project management: Data management: Visualization: Analysis & Planning:
Sales tracking, Budget planning Gantt charts for Data collection and Creating graphs and What-if scenarios -
Inventory and tracking, timelines, task allocation entry, sorting and charts, dashboards forecasting trends, goal
management, grocery, shopping and monitoring, filtering data, for KPIs, visual seek and solver tools,
financial reporting, lists and travel progress tracking, etc. validating data using summaries for etc.
invoicing and planning, etc. rules, etc. reports, etc.
billing, etc.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

What is Data Analytics?

Data Analytics refers to the process of

examining, cleaning, transforming, and
model data to discover useful
information, draw conclusions, and
support decision-making.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Types of Data Analytics

Descriptive Diagnostic Predictive Prescriptive

What Why did it What will How can we

happened? happen? happen? make it happen?

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

1. Introduction to Excel in
Analytics

What is the primary goal of

data analytics?

A. To create attractive website designs

B. To examine and model data for decision-
making
C. To write and compile programming code
D. To store data securely in cloud systems

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Role of Excel in Data Analytics

Data Collection & Data Cleaning Data Transformation Data Analysis Data Visualization Reporting
Entry
Removing duplicates, Structuring raw data Using functions like Communicating insights Sharing dynamic
Manual or automated
fixing errors, into usable formats SUMIF, COUNTIF, through bar charts, pie dashboards or
import of data from
standardizing data using formulas, AVERAGEIFS, and charts, line graphs, and downloadable reports that
various sources (CSV,
formats using Excel functions, and text PivotTables for conditional formatting. update with underlying
databases, web, etc.)
tools and formulas. tools summarization data changes.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Advantages of using Excel in Analytics
Advantage Explanation

User-friendly Easy to learn and intuitive for

Interface beginners

Dynamic Analysis Tools PivotTables, filters, slicers, and formulas make

data slicing easy.

Customizable Visualizations Users can create tailored charts and

dashboards.
Flexible Data Import Can connect to CSV, TXT, web data, SQL servers, etc.

Versatility Across Domains Useful in business, healthcare, education, logistics,

Private and Confidential © Career247
etc.
 Flashback
Module 1 Introduction to Data Analysis with Excel

1. Introduction to Excel in
Analytics

Which of the following best

describes the role of Microsoft
Excel in analytics?

A. Designing websites
B. Editing video files
C. Organizing, analyzing, and visualizing data
D. Running machine learning models

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Example 01

A retail manager uses Excel to track sales data

and analyze weekly revenue performance.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Example 01

Explanation

The manager types in the number of products sold each day along with their prices into an Excel
sheet. Using simple formulas like =Total Sales, they calculate how much money was made each
day.

Then, using tools like PivotTables and charts, they quickly

see:
• Which products sold the most
• On which days sales were highest

This helps the manager decide how much stock to keep and what items to promote more.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Example 02

A public health researcher uses Excel to monitor

how a disease spreads across different districts.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

1. Introduction to Excel in Analytics

Example 02

Explanation

Each day, the researcher types in the number of new cases reported in each district. They use
conditional formatting to automatically highlight areas with high case numbers in red.

Next, they create a line chart to see how the number of cases is increasing or decreasing
over time. This makes it easy to spot which districts are most affected.

The researcher then shares these insights with health officials to help plan safety measures like
awareness campaigns or medical support.
Private and Confidential © Career247
Excel Interface
& Navigation
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

“Home” is the default tab when Excel starts.

Note for
learners:

We are using
Some visuals or
Microsoft Excel
tools may vary
365 (Office 365
slightly if you are
Desktop
using a different
version).
version.

Download Link: [Link]

Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 1: Launching Excel

Open Excel from the Start

menu or desktop shortcut.

Go to Home option or New

option.

Click on Blank Workbook to

begin.

You are now in the Excel

environment.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

SN# Section Description

I Title Bar Displays the name of your workbook.

II Quick Access Toolbar Found in the top-left. Contains Save, Undo, Redo. Customizable.
The main toolbar, organized into tabs like Home, Insert, Formulas, Data,
III Ribbon
etc.
IV Formula Bar Shows the contents or formula of the selected cell.

V Name Box Displays the address or name of the selected cell or range.

VI Worksheet Area (Grid) Where you input data – rows (1,2,3...) and columns (A,B,C...).

VII Sheet Tabs Located at the bottom, used to manage multiple sheets in a workbook.

VIII Status Bar Shows summary info like Sum, Average, Zoom slider, and view modes.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see
I. Title Bar

Displays the name of your workbook.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

II. Quick Access Toolbar

Found in the top-left. Contains Save, Undo, Redo and it is customizable.

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation

What is the Quick Access

Toolbar used for?

A. Adding charts

B. Opening Data tab

C. Save, Undo, Redo tasks

D. Navigate sheet tabs

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

III. Ribbon
• It is designed to help users quickly find the commands they need to complete tasks.

• The Ribbon in Microsoft Excel is a toolbar that contains all the commands and features organized into
–

Tabs Groups Buttons

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation
What is the primary
purpose of the Ribbon in
Excel?

A. Cell location

B. Save files

C. Access commands in tabs

D. Navigate workbooks

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

III. Ribbon
Tab
• These are the main categories on the Ribbon. Common tabs include:

Home – Contains the

most commonly used Insert – Used to Page Layout – Deals Formulas – Offers
commands (e.g., insert tables, with themes, margins, formula tools and
formatting, clipboard, charts, pictures, orientation, etc. function libraries.
number, styles). etc.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see
III. Ribbon
Tab
• These are the main categories on the Ribbon. Common tabs include:

Data – Contains tools Review – Spelling, View – Options for Automate - Run and
for data analysis, comments, how the worksheet is manage Office Scripts to
sorting, filtering. protection. displayed. automate repetitive tasks.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

III. Ribbon

Group
s
Inside each tab, commands are organized into groups. For example, in the Home tab:

Alignment Group:
Clipboard Group: Font Group: Font size, Center, wrap text,
Cut, Copy, Paste color, bold, italic merge cells

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

IV. Formula Bar

The Formula Bar is located directly under

the Ribbon.

Great for writing and debugging

formulas.

Also use it to view or edit the contents of the active cell.

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation
Which part of the Excel
interface displays the contents
or formula of a cell?

A. Name Box
B. Status Bar
C. Worksheet Grid
D. Formula Bar
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see
V. Name Box

Found to the left of the Displays the cell reference Type a reference to jump to
Formula Bar. of the selected cell (Eg - that cell (D25 + Enter).
G2).

Private and Confidential © Career247

Tip: Press Ctrl + G or F5 to bring up the Go To box.
 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation

The Name Box is used to:

A. Display sum

B. Enter formulas

C. Show address/name of cell

D. Create charts

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

VI. Worksheet Grid

Area
Found to the left of the Formula Bar.

1 2 3
Excel is organized A cell is a single Cell ranges are
into columns (A, B, box in the grid groups of cells
C...) and rows (1, (Example - A1, (Example -
2, 3...). B2). A1:A10).

Private and Confidential © Career247

Tip: Use arrow keys to move or Ctrl + Arrow to jump to data edges.
 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see
VII. Sheet Tabs

At the bottom of the screen: Sheet1,

Sheet2, etc.

Use this to organize different tables or

analyses in one file.

Right-click to - Rename (Rename),

Delete, Move/Copy

Private and Confidential © Career247

Tips: Shift + F11 inserts a new
 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation
What is the shortcut to insert
a new worksheet?

A. Ctrl + W

B. Shift + F11

C. Ctrl + N

D. Alt + Insert

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Step 2: The Excel Window – What you see

VIII. Status Bar

Displays dynamic View modes:

calculations, like - Normal, Page
Sum, Average, Count Layout, Page
Break

Zoom control

Tips: Right-click on the status bar to customize what you want to see.
Private and Confidential © Career247
 Flashback
Module 1 Introduction to Data Analysis with Excel

2. Excel Interface &

Navigation
What does the Status Bar
show when selecting
numerical data?

A. Count only
B. Highest value
C. Sum, Avg, Count (customizable)
D. Nothing

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Practical Exercise
1. Open a blank workbook and rename Sheet1 to "Sales Data".

2. Move to cell D10 using Ctrl + G → Type something.

3. Insert a new sheet (Shift + F11) → Rename to "Report".

4. Highlight a few numbers in any row → Look at the status bar

summary.

5. Click on the Data tab → Observe tools like Sort, Remove

Duplicates.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

2. Excel Interface & Navigation

Try it
1. What is the purpose of the Name Box?

2. Which tab would you use to insert a chart?

3. How do you move directly to cell D20 using the keyboard?

4. How can you summarize selected numeric data without formula?

5. Name two components of the Excel interface that help organize

data across sheets.

Private and Confidential © Career247

Data Types and
Basic Formatting
 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Data Types in Excel refer to the kinds of values a cell can contain, such as text, numbers, dates, etc.

Data Type Example Usage

Text "Hello", "Mrinal123“ Names, categories, IDs

Number 100, 45.67 Quantities, scores, values

Date/Time 17-Apr-2025, 10:30 AM Scheduling, deadlines

Boolean TRUE, FALSE Logical operations, checkboxes

Currency ₹500, $25.00 Expenses, invoices

Percentage 25%, 0.75 Growth rates, conversion rates

Private and Confidential © Career247
 Flashback
Module 1 Introduction to Data Analysis with Excel

3. Data Types and Basic

Formatting

Which of the following is not a

valid data type in Excel?

A. Text

B. Audio

C. Date

D. Boolean
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Basic Formatting refers to changing how cells look to make the data easier to read and interpret.

Formatting Type Purpose Example

Emphasize headings or
Font Style Bold, Italic
important values

Fill Color Highlight sections for better Yellow cell background

visibility
Font Color Differentiate data visually Red font for low scores

Cell Borders Define table structure Outline around totals

Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Basic Formatting refers to changing how cells look to make the data easier to read and interpret.

Formatting Type Purpose Example

Number Format Show data type clearly Currency, %, Date

Text Alignment Organize text and numbers neatly Center-aligned titles

Table Format Quickly format as a “Format as Table”

structured, filterable table option

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

3. Data Types and Basic

Formatting
Which of the following actions
is part of basic formatting in
Excel?

A. Using a VLOOKUP function

B. Applying a border to a table

C. Importing data from a text file

D. Writing a macro to automate tasks

Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Example 01

A student tracks their study schedule using

Excel with different dates and topics.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Example 01
Explanation:

The Date column is formatted using Short Date.

The Time Spent column is formatted as Number

with 1 decimal place.

The headers are in Bold with Light Blue

Fill for clarity.

Data is formatted as a table using

"Format as Table" → Style: Light 1.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Example 02

An accountant prepares an expense report using

numbers and currency formatting.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

3. Data Types and Basic Formatting

Example 02
Explanation:

The Amount Spent column is formatted as

Currency (₹) with 2 decimal places.

Approved is formatted as Boolean (TRUE/FALSE).

All cells are center-aligned.

Table has bold headers, grid borders,

and zebra striping via “Format as Table”.

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

3. Data Types and Basic

Formatting
Why is it important to apply
correct formatting (like currency
or date) to your Excel data?

A. To make the file size smaller

B. To allow proper calculations and
clarity
C. To protect the file from viruses
D. To make charts load faster

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

Data Types and Basic Formatting

1. List and describe four different data types in Excel with real-

life examples.

2. What is the role of basic formatting in improving

spreadsheet readability?

3. What are benefits of applying "Format as Table" to a

dataset in Excel?

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

Data Types and Basic Formatting

Practical Exercise

Create the table and apply the mentioned formatting: Date Task Name Hours Spent Completed Cost (₹)

Project
15-Apr-25 2.5 TRUE 150.00
1. Format the Date as Short Date. Research

16-Apr-25 Design Draft 3 TRUE 200.50

2. Use bold text and gray fill for headers.
17-Apr-25 Team Meeting 1.5 FALSE 0.00
3. Format Cost as Currency (₹) with two decimal places.

4. Format Hours Spent as Number with 1 decimal place.

5. Use "Format as Table" to give the table a structured look.

6. Apply a cell border to the entire table.

Private and Confidential © Career247
Sorting & Filtering
 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Sorting is the process of arranging data based on column values, typically in ascending or descending order.

Common types of Sorting

• Single-Level Sorting – Sorting based on one column (e.g., Alphabetical order of names).

• Multi-Level Sorting – Sorting based on more than one column (e.g., First by Department,

then by Name).

• Custom Sorting – Sorting using a user-defined order (e.g., High → Medium → Low).

• Color/Font Sorting – Sorting by cell or font color (used when data is visually tagged).

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Filtering is a technique that allows you to display only the rows that meet specific conditions, hiding the
rest temporarily.

Common types of Filtering

• AutoFilter – Basic dropdown filter used to select specific values.

• Number/Text Filters – For conditions like “greater than”, “contains”, “starts with”.

• Date Filters – Filter by date range, month, year, etc.

• Custom Filters – Combine multiple filter conditions.

• Search within Filter – Type text in the filter search box for quick filtering.
Private and Confidential © Career247
 Flashback
Module 1 Introduction to Data Analysis with Excel

4. Sorting & Filtering

What is the primary difference
between sorting and filtering?

A. Sorting deletes data, filtering arranges

it.
B. Sorting arranges data, filtering hides
data temporarily.
C. Sorting is for formulas, filtering is for
charts.
D. Sorting creates pivot tables, filtering
does not.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 01 – Multi-Level Sorting

Use Case: Hotel Manager Sorts the bookings.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 01 – Multi-Level Sorting

Use Case: Hotel Manager Sorts the bookings.

Explanation:

First sorts by Room Type, then by Check-

In Date (Old to New) to prepare the
housekeeping schedule.

Multi-level sorting helps efficiently group and

sequence operations.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 01 – Multi-Level Sorting

Steps:

• Set table Format style – Dark Teal, Light 9.

• Click the dropdown on Column Header.

• Set the Sort order.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 02 – Date & Number Filtering

Use Case:

• Date Filtering – to show upcoming check-ins after a specific date (e.g., 12-08-2024).

• Number Filtering – to filter long stays (e.g., more than 2 nights)

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 02 – Date & Number Filtering

Explanation

✓ Date Filtering shows only check-ins after 12-08-2024 to help track upcoming arrivals.

✓ Number Filtering highlights stays longer than 2 nights to focus on long-duration

guests.

✓ Both filters simplify analysis and decision-making without needing advanced formulas.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 02 – Date & Number Filtering

Date filtering steps:

1. Select the data range.

2. Click on the Filter button (Home > Sort & Filter > Filter).

3. Click the dropdown arrow on the Check-In Date column.

4. Choose Date Filters > After…Set the date: 12-08-2024

5. Click OK.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 02 – Date & Number Filtering

Number filtering steps:

1. Select the data range.

2. Click on Duration (Nights) column’s dropdown.

3. Choose Number Filters > Greater Than and set 2.

4. Click OK.

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

4. Sorting & Filtering

What type of filtering would you use

to find all records where the date is
after July 1st?

A. Text filter
B. Number filter
C. Date filter
D. Color filter

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 03 – Color based sorting

Use Case:

• An HR Executive maintains a list of employees.

• Employees who are on probation have their names written in red font, confirmed

employees are in black font.

• She wants to bring all probationary employees to the top of the list for follow-up.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 03 – Color based sorting

Before Sorting

After Sorting

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 03 – Color based sorting

Explanation

• The HR Executive uses font color sorting to group red-font (probationary) names at the top.

• This helps her quickly identify employees needing follow-up actions.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 03 – Color based sorting

Steps

• Open the Excel sheet with your data.

• Select the range of cells you want to sort (in this

case, all rows in the table).

• Home tab → Click on Sort & Filter → Choose Custom

Sort.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Example 03 – Color based sorting

Steps

In the Sort dialog box, do the following:

• Under ‘Sort by’, select Employee Name.

• Under ‘Sort On’, choose Font Color.

• Under ‘Order’, choose the font color to be appear on top.

• Select “On Top” from the dropdown next to it.

• Click OK.
Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

1. Define sorting and filtering.

2. How do they help in analyzing Excel data efficiently?

3. Describe the steps to apply a multi-level sort in Excel with an example.

4. Explain different types of filters and when to use each.

5. How does color-based sorting work, and in what real-life scenarios can it

be useful?

6. Compare and contrast number filters and text filters with examples.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

4. Sorting & Filtering

Practical Exercise
• Sort by Category → then by Units Sold (Descending)

• Apply custom sort to list "TV", "Mobile", "Audio" in that order.

• Highlight rows where Units Sold < 200, and sort by Fill Color.
Launch Units
• Filter products launched after Jan 1, 2023. Product Category Status Rating
Date Sold

• Filter products with: Phone A Mobile 2023-12-01 500 Active 4.2

TV B TV 2023-09-10 150 Discontinued 3.8

• Status = Active
Phone C Mobile 2023-10-15 300 Active 4.6
• Rating ≥ 4.0 Speaker Audio 2024-01-05 100 Active 4.0

TV X TV 2022-12-10 50 Active 3.5

Private and Confidential © Career247
Working with
Excel Tables
 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

An Excel Table is a structured range of related data

that allows for easy data management, filtering,
sorting, formatting and analysis with built-in features.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Key capabilities of Excel tables

• Automatic formatting and banded rows for readability.

• Easy sorting and filtering.

• Dynamic range expansion as you add data.

• Integrated summarization tools like Total Row.

• Use of structured names instead of cell references (e.g., =Sales[Revenue]).

• All of which improve clarity, consistency, and usability for analytics.

Private and Confidential © Career247

 Flashback
Module 1 Introduction to Data Analysis with Excel

5. Working with Excel

Tables
Which of the following is a feature
exclusive to Excel Tables?

A. Formula AutoFill

B. Conditional Formatting

C. Structured References

D. Data Validation

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Convert Data Range into an Excel Table

After conversion

• Excel applies a default table style.

• Filter arrows appear in header cells.

• “Table Design” tab appears on the ribbon.

Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Convert Data Range into an Excel Table
Steps:

1. Click and drag to select the entire range of data.

2. Go to the Ribbon.
3. Click on the Insert tab on the Ribbon.
4. Click “Table” button, in the “Tables” group.
5. Confirm the Table Range.
6. Check the box “My table has headers” if data includes
column headers.
7. Click OK.
Private and Confidential © Career247
 Flashback
Module 1 Introduction to Data Analysis with Excel

5. Working with Excel

Tables
What is a primary benefit of
converting a data range into an
Excel Table?

A. It removes all formatting

B. It disables formulas
C. It allows for structured referencing
and auto-expansion
D. It deletes column headers

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Example 01 - Attendance Tracker

Use Case: A teacher maintains a student attendance

log in a table format.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Try it

1. Define an Excel Table.

2. How is it different from a regular data range?

3. What are structured references in Excel Tables? Give examples.

4. Explain how filtering and sorting works in Excel Tables.

5. Describe how auto-expansion in Excel Tables enhances data entry

efficiency.

6. What are the advantages of Excel table for data analytics?

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

5. Working with Excel Tables

Practical Exercise
Task: Student Grades Table

Create a table with the columns, as shown:

Name Roll Subject Marks Grade
• Enter data for 10 students across 3 subjects.

• Convert it into a Table.

• Apply conditional formatting to highlight students scoring

below 75.

• Filter data to show only students with Grade “A”.

Private and Confidential © Career247
 Module 1 Introduction to Data Analytics with Excel

Summary

• Excel is widely used for data entry, cleaning, analysis, and

visualization.

• The Excel interface includes key elements like the Ribbon,

Formula Bar, and Sheet Tabs.

• Excel supports various data types, and proper formatting

improves readability and accuracy.

Private and Confidential © Career247

 Module 1 Introduction to Data Analytics with Excel

Summary

• Sorting and filtering features help users quickly

organize and analyze specific data sets.

• Excel Tables make data more manageable with

automatic formatting and dynamic updates.

Private and Confidential © Career247

Thank You!
Q&A Session