Excel for Data Analytics • Lesson 9
Charts, PivotTables & Data Visualization
Learn how data analysts transform raw sales data into summaries, charts and business insights using Excel PivotTables and data visualization.
WHY THIS MATTERS
From Numbers to Insights
Data analysis is not only about calculating numbers. An analyst must also communicate what those numbers mean.
Charts help us recognize patterns visually, while PivotTables help us summarize large datasets quickly.
Compare
Compare regions, products and business categories.
Find Patterns
Identify trends, increases, decreases and unusual values.
Communicate
Present analytical findings to managers and decision makers.
DATASET
Monthly Regional Sales
We will use this dataset throughout the lesson. Revenue is shown for four regions across four months.
| Month | North | South | East | West |
|---|---|---|---|---|
| January | ₹120,000 | ₹90,000 | ₹75,000 | ₹65,000 |
| February | ₹140,000 | ₹95,000 | ₹82,000 | ₹70,000 |
| March | ₹155,000 | ₹110,000 | ₹88,000 | ₹78,000 |
| April | ₹170,000 | ₹125,000 | ₹95,000 | ₹85,000 |
Analyst's first task
Before creating a chart, understand what the dataset contains and what question you are trying to answer.
CHECK YOUR UNDERSTANDING
You want to show how regional sales change from January to April. What type of chart is most appropriate for showing the trend over time?
STEP 1 • CHART SELECTION
Choose the Chart Based on the Question
Good visualization starts with the analytical question. The chart should make the required comparison easy to understand.
Column Chart
Useful when comparing values across categories.
Example:
Compare total revenue of North, South, East and West.
Line Chart
Useful for showing changes over time.
Example:
Show how North revenue changed from January to April.
Bar Chart
Useful for comparing categories, particularly when category names are long.
Example:
Compare sales across many product categories.
Pie Chart
Can show a simple part-to-whole relationship when there are only a few categories.
Example:
Show the percentage contribution of each region.
A ______ chart is a suitable choice when you want to show how sales change over time.
CHECK YOUR UNDERSTANDING
You want to compare total revenue between North, South, East and West. Which chart would be a suitable starting point?
STEP 2 • READ THE VISUAL
Reading a Trend
Look only at the North region:
Jan
₹120K
Feb
₹140K
Mar
₹155K
Apr
₹170K
Observation
North revenue increased every month in this dataset.
North region revenue increased every ______ from January to April.
FORMULA CHALLENGE
Using the dataset, what formula could you use in Excel to calculate the total North revenue for January to April if the values are in B2:B5?
CHECK YOUR UNDERSTANDING
What is one important advantage of visualizing sales data?
STEP 3 • PIVOTTABLES
Summarize Data With a PivotTable
A PivotTable allows an analyst to group data and calculate aggregates without manually creating every calculation.
ROWS
Region
Creates groups such as North, South, East and West.
VALUES
Sum of Revenue
Calculates total revenue for each group.
COLUMNS
Product
Can provide another analytical dimension.
To group sales by region in a PivotTable, place the Region field in the ______ area.
Example PivotTable Result
| Region | Total Revenue |
|---|---|
| North | ₹585,000 |
| South | ₹420,000 |
| East | ₹340,000 |
| West | ₹298,000 |
| Grand Total | ₹1,643,000 |
Analyst observation
North has the highest total revenue in this summary, while West has the lowest.
CHECK YOUR UNDERSTANDING
Which region has the highest total revenue in the PivotTable?
FORMULA CHALLENGE
If North revenue is ₹585,000 and total company revenue is ₹1,643,000, what Excel formula can calculate North's percentage contribution?
CHECK YOUR UNDERSTANDING
A manager wants to know how much revenue each product generated. Which PivotTable design is appropriate?
STEP 4 • MULTI-DIMENSION ANALYSIS
Analyze More Than One Dimension
Sometimes an analyst does not want only one total. The business may want to understand how regions perform across products.
Example analytical setup
ROWS
Region
COLUMNS
Product
VALUES
Sum of Revenue
To compare regions across different products, Product can be placed in the PivotTable ______ area.
CHECK YOUR UNDERSTANDING
Which statement best describes what a PivotTable does?
STEP 5 • COMBINE TOOLS
Combine PivotTables With Charts
A PivotTable can summarize the data, while a chart can communicate the summary visually.
Prepare
Make sure the data is organized.
Summarize
Create a PivotTable.
Visualize
Create a suitable chart.
Explain
Communicate the insight.
CHECK YOUR UNDERSTANDING
Why might an analyst create a chart from a PivotTable?
STEP 6 • ANALYTICAL THINKING
Avoid Misleading Visualizations
Creating a chart is not enough. The chart must represent the data clearly and answer the intended question.
Problem: Unclear Labels
A viewer cannot understand what the values or categories represent.
Fix: Use clear titles, labels and units.
Problem: Too Many Categories
Too many categories can make a chart difficult to interpret.
Fix: Simplify or group categories where appropriate.
CHECK YOUR UNDERSTANDING
Which practice helps make a business chart easier to understand?
BUSINESS CASE
Build a Regional Sales Dashboard
Imagine you are a data analyst for an electronics company. Management wants a dashboard that quickly explains regional performance.
Management wants answers to four questions
- Which region generated the most revenue?
- Which region generated the least revenue?
- How did sales change over the months?
- How can these findings be communicated clearly?
STEP 1
Prepare Data
STEP 2
Create PivotTable
STEP 3
Create Charts
STEP 4
Explain Insights
CHECK YOUR UNDERSTANDING
Management wants to compare the total revenue of all four regions. Which combination is most appropriate?
ANALYTICAL TASK
Compare Monthly Company Revenue
Calculate the company-wide revenue for each month by adding the four regions.
January
₹350,000
February
₹387,000
March
₹431,000
April
₹475,000
CHECK YOUR UNDERSTANDING
Based on the monthly totals, which month has the highest company revenue?
STEP 7 • THINK LIKE AN ANALYST
A Chart Shows What Happened — Not Always Why
Suppose North revenue increases every month. The data supports the statement that revenue increased. However, the data alone does not tell us why it increased.
Supported by the dataset
North revenue increased from January to April.
Requires more evidence
The increase happened because of a particular marketing campaign.
CHECK YOUR UNDERSTANDING
North revenue increased from January to April. Which conclusion is directly supported by the dataset?
FINAL CHALLENGE
Build the Right Analytical Story
Management asks:
"Show me which region generated the most revenue and how company sales changed from January to April."
CHECK YOUR UNDERSTANDING
Which analytical approach best answers both parts of the management question?
CHECK YOUR UNDERSTANDING
What should primarily determine the chart you choose?
Lesson 9 Complete
You can now choose suitable charts, summarize datasets with PivotTables, calculate analytical metrics and communicate business insights using Excel visualizations.