Sparklines in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a quick visual summary of monthly sales trends for each product to spot growth or decline patterns easily.

📊 Data: You have monthly sales data for 6 products from January to June in a table format.

🎯 Deliverable: Create sparklines next to each product row showing the sales trend from January to June.

Sample Data

Product	Jan	Feb	Mar	Apr	May	Jun
Product A	120	135	150	160	170	180
Product B	200	190	185	180	175	170
Product C	90	95	100	110	120	130
Product D	50	55	60	58	57	59
Product E	300	310	320	330	340	350
Product F	400	390	380	370	360	350

1

Step 1: Select the cell next to the first product row where you want to insert the sparkline (e.g., cell H2).

No formula needed, just select the cell.

Expected Result

Cell H2 is selected.

2

Step 2: Go to the Insert tab on the Excel ribbon, then click on Sparklines group and choose 'Line'.

No formula, use Excel menu.

Expected Result

The Create Sparklines dialog box opens.

3

Step 3: In the Create Sparklines dialog, set the Data Range to B2:G2 (sales data for Product A).

Data Range: B2:G2

Expected Result

Data range is set to the sales numbers for Product A.

4

Step 4: Set the Location Range to H2 (the cell where the sparkline will appear).

Location Range: H2

Expected Result

Location range is set to H2.

5

Step 5: Click OK to insert the sparkline for Product A.

No formula, just click OK.

Expected Result

A sparkline appears in cell H2 showing the sales trend for Product A.

6

Step 6: Copy the sparkline in H2 down to cells H3 to H7 to create sparklines for all products.

Drag the fill handle from H2 down to H7.

Expected Result

Each product row has a sparkline in column H showing its sales trend.

7

Step 7: Optionally, customize sparklines by selecting them and using the Sparkline Tools Design tab to change color or style.

No formula, use ribbon options.

Expected Result

Sparklines are visually clear and easy to interpret.

Final Result

Product   Jan  Feb  Mar  Apr  May  Jun  Trend
--------------------------------------------
Product A 120  135  150  160  170  180  ▂▃▄▅▆▇
Product B 200  190  185  180  175  170  ▇▆▅▄▃▂▁
Product C  90   95  100  110  120  130  ▂▃▄▅▆▇
Product D  50   55   60   58   57   59  ▂▃▄▅▃▄
Product E 300  310  320  330  340  350  ▂▃▄▅▆▇
Product F 400  390  380  370  360  350  ▇▆▅▄▃▂

✓Products A, C, and E show steady sales growth from January to June.

✓Products B and F show a steady decline in sales over the months.

✓Product D sales fluctuate slightly but remain mostly stable.

Bonus Challenge

Add column sparklines next to the sales data to compare monthly sales across all products for each month.

Show Hint

Select the range of sales data for each month (e.g., B2:B7 for January) and insert column sparklines in a new row below the data.