Why structured data enables analysis in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to analyze monthly sales data to find trends and identify the best-selling products.

📊 Data: You have a table with sales records including Date, Product Name, Region, Units Sold, and Sales Amount.

🎯 Deliverable: Create a clean, structured table and use formulas to summarize total sales by product and by month.

Sample Data

Date	Product Name	Region	Units Sold	Sales Amount
2024-01-05	Notebook	East	10	200
2024-01-15	Pen	West	25	50
2024-02-10	Notebook	East	15	300
2024-02-20	Pen	North	30	60
2024-03-05	Notebook	South	20	400
2024-03-15	Pen	East	40	80
2024-04-01	Notebook	West	25	500
2024-04-10	Pen	South	35	70

1

Step 1: Format the sales data as a table to organize it clearly.

Select the data range including headers and press Ctrl+T to create a table.

Expected Result

Data is formatted as a structured table with filter buttons on each column.

2

Step 2: Add a new column to extract the month from the Date for monthly analysis.

=TEXT([@Date],"yyyy-mm")

Expected Result

A new column shows the year and month like '2024-01', '2024-02', etc.

3

Step 3: Create a summary table listing each Product and Month combination.

List unique products and months manually or using UNIQUE function if available.

Expected Result

Summary table has rows for each product and month combination.

4

Step 4: Calculate total sales amount for each product and month using SUMIFS.

=SUMIFS([Sales Amount],[Product Name],$A2,[Month],$B2)

Expected Result

Each cell shows the total sales amount for that product in that month.

5

Step 5: Use filters or conditional formatting to highlight the best-selling products per month.

Apply conditional formatting rules to highlight highest values in each month column.

Expected Result

Best-selling products per month are visually highlighted.

Final Result

Product   | 2024-01 | 2024-02 | 2024-03 | 2024-04
-----------------------------------------------
Notebook  | 200     | 300     | 400     | 500
Pen       | 50      | 60      | 80      | 70

✓Notebook sales increase steadily from January to April.

✓Pen sales are lower but consistent across months.

✓Structured data with clear columns and formulas makes it easy to summarize and find trends.

Bonus Challenge

Create a chart that shows monthly sales trends for each product.

Show Hint

Use the summary table and insert a line chart with months on the x-axis and sales on the y-axis.

Practice

1. Why is having clear headers in a spreadsheet important for data analysis?

easy

A. Headers prevent the spreadsheet from being saved incorrectly.

B. Headers make the spreadsheet look colorful and attractive.

C. Headers help identify what each column represents, making analysis easier.

D. Headers automatically calculate totals for each column.

Solution

1. Step 1: Understand the role of headers

   Headers label each column, telling us what data is inside, like 'Name' or 'Date'.

2. Step 2: Connect headers to analysis

   Formulas and tools use headers to know which data to work on, so clear headers help avoid confusion.

3. Final Answer:

   Headers help identify what each column represents, making analysis easier. -> Option C

4. Quick Check:

   Clear headers = easier analysis [OK]

Hint: Headers label data columns clearly for easy formula use [OK]

Common Mistakes:

• Thinking headers add colors automatically
• Believing headers calculate totals by themselves
• Assuming headers affect file saving

2. Which of the following is the correct way to refer to a column header in an Excel table formula?

easy

A. =SUM(Table1[Sales])

B. =SUM(Sales)

C. =SUM(#Sales)

D. =SUM(Table1#Sales)

Solution

1. Step 1: Recall Excel structured reference syntax

   In Excel tables, columns are referenced as TableName[ColumnName], for example Table1[Sales].

2. Step 2: Check each option

   =SUM(Table1[Sales]) uses correct syntax. Options B, C, and D do not follow Excel table reference rules.

3. Final Answer:

   =SUM(Table1[Sales]) -> Option A

4. Quick Check:

   TableName[ColumnName] = correct reference [OK]

Hint: Use TableName[ColumnName] to refer columns in formulas [OK]

Common Mistakes:

• Omitting the table name
• Using # instead of brackets
• Mixing syntax symbols incorrectly

3. Given a table named 'SalesData' with columns 'Product' and 'Amount', what will the formula =SUM(SalesData[Amount]) return?

medium

A. The total sum of all values in the 'Amount' column.

B. The number of rows in the 'SalesData' table.

C. The first value found in the 'Amount' column.

D. An error because the formula is incorrect.

Solution

1. Step 1: Understand the SUM function

   SUM adds all numbers in the specified range or column.

2. Step 2: Analyze the structured reference

   SalesData[Amount] refers to the entire 'Amount' column in the 'SalesData' table.

3. Final Answer:

   The total sum of all values in the 'Amount' column. -> Option A

4. Quick Check:

   SUM(column) = total sum [OK]

Hint: SUM with structured reference adds all column values [OK]

Common Mistakes:

• Thinking it counts rows instead of summing
• Expecting a single cell value
• Assuming formula syntax is wrong

4. You have a table named 'Inventory' with columns 'Item' and 'Quantity'. The formula =SUM(Inventory[Quant]) returns an error. What is the most likely cause?

medium

A. SUM cannot be used with structured references.

B. The column name 'Quant' does not exist in the table.

C. The table name 'Inventory' is misspelled.

D. The formula needs to use parentheses around the table name.

Solution

1. Step 1: Check the column name in the formula

   The formula uses 'Quant' but the actual column is 'Quantity'.

2. Step 2: Understand structured reference errors

   If the column name is wrong, Excel returns a reference error because it can't find 'Quant'.

3. Final Answer:

   The column name 'Quant' does not exist in the table. -> Option B

4. Quick Check:

   Wrong column name = error [OK]

Hint: Check exact column names in structured references [OK]

Common Mistakes:

• Assuming SUM can't use structured references
• Ignoring spelling mistakes in names
• Adding unnecessary parentheses

5. You have a structured table named 'Expenses' with columns 'Date', 'Category', and 'Amount'. You want to calculate the total 'Amount' for the category 'Travel'. Which formula correctly uses structured references to do this?

hard

A. =SUMIFS(Expenses[Category], Expenses[Amount], "Travel")

B. =SUM(Expenses[Amount] = "Travel")

C. =SUMIF(Expenses[Category], Expenses[Amount], "Travel")

D. =SUMIFS(Expenses[Amount], Expenses[Category], "Travel")

Solution

1. Step 1: Understand SUMIFS syntax

   SUMIFS sums values in one column based on criteria in another column: SUMIFS(sum_range, criteria_range, criteria).

2. Step 2: Match formula to problem

   We sum 'Amount' where 'Category' equals "Travel". =SUMIFS(Expenses[Amount], Expenses[Category], "Travel") matches this correctly.

3. Final Answer:

   =SUMIFS(Expenses[Amount], Expenses[Category], "Travel") -> Option D

4. Quick Check:

   SUMIFS(sum_range, criteria_range, criteria) = correct [OK]

Hint: Use SUMIFS with structured refs: sum column, criteria column, criteria [OK]

Common Mistakes:

• Mixing order of arguments in SUMIFS
• Using SUMIF with wrong argument order
• Trying to compare inside SUM