Why advanced charts tell deeper stories in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to create advanced charts to show monthly sales trends and product category performance to help the team understand sales patterns better.

📊 Data: You have monthly sales data for three product categories over six months, including total sales and units sold.

🎯 Deliverable: Create a dashboard with a line chart showing monthly sales trends and a stacked column chart showing sales by product category each month.

Sample Data

Month	Product Category	Total Sales	Units Sold
Jan	Electronics	12000	100
Jan	Clothing	8000	200
Jan	Home Goods	6000	150
Feb	Electronics	15000	120
Feb	Clothing	7000	180
Feb	Home Goods	6500	160
Mar	Electronics	13000	110
Mar	Clothing	9000	210
Mar	Home Goods	7000	170
Apr	Electronics	16000	130
Apr	Clothing	8500	190
Apr	Home Goods	7200	175
May	Electronics	17000	140
May	Clothing	9000	220
May	Home Goods	7500	180
Jun	Electronics	18000	150
Jun	Clothing	9500	230
Jun	Home Goods	8000	190

1

Step 1: Create a summary table with total sales per month by adding sales from all product categories.

=SUMIFS($C:$C,$A:$A,A2) for each month (assuming month labels start in A2)

Expected Result

Jan: 26000, Feb: 28500, Mar: 29000, Apr: 31700, May: 33500, Jun: 35500

2

Step 2: Create a pivot table with Rows as Month, Columns as Product Category, and Values as SUM of Total Sales.

Insert > Pivot table; Rows=Month; Columns=Product Category; Values=SUM of Total Sales

Expected Result

Pivot table showing sales for Electronics, Clothing, Home Goods by month

3

Step 3: Insert a line chart using the summary table to show total sales trend over months.

Select summary table; Insert > Chart; Chart type: Line chart

Expected Result

Line chart showing total sales rising from Jan to Jun

4

Step 4: Insert a stacked column chart using the pivot table to show sales by product category each month.

Select pivot table; Insert > Chart; Chart type: Stacked column chart

Expected Result

Stacked column chart showing monthly sales split by Electronics, Clothing, Home Goods

5

Step 5: Add chart titles and axis labels to explain the data clearly.

Edit chart > Chart & axis titles; Title: 'Monthly Sales Trend' and 'Sales by Product Category'

Expected Result

Charts have clear titles and axis labels for easy understanding

Final Result

Monthly Sales Trend (Line Chart)

  36000 |                         *
  34000 |                      *  
  32000 |                   *     
  30000 |                *        
  28000 |             *           
  26000 |          *              
         +------------------------
          Jan Feb Mar Apr May Jun

Sales by Product Category (Stacked Column Chart)

  18000 |  ████  ████  ████  ████  ████  ████  Electronics
  10000 |  ████  ████  ████  ████  ████  ████  Clothing
   8000 |  ████  ████  ████  ████  ████  ████  Home Goods
         +------------------------------
          Jan   Feb   Mar   Apr   May   Jun

✓Total sales increased steadily from January to June.

✓Electronics consistently contributed the largest share of sales each month.

✓Clothing and Home Goods sales were stable but showed slight growth over time.

✓The stacked column chart helps see how each product category adds to total sales monthly.

Bonus Challenge

Create a combo chart that shows total sales as a line and units sold as columns for each month.

Show Hint

Use the pivot table data for total sales and units sold; select combo chart type and assign line to total sales and columns to units sold.