Column transformations in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to prepare a clean sales report by transforming raw data columns into more useful formats for analysis.

📊 Data: You have a sales data table with columns: Order ID, Customer Name, Product, Quantity, Price per Unit, and Order Date (in text format).

🎯 Deliverable: Create new columns to calculate Total Sales, extract Month and Year from Order Date, and create a Customer Initials column.

Sample Data

Order ID	Customer Name	Product	Quantity	Price per Unit	Order Date
1001	John Smith	Notebook	3	5	2024-01-15
1002	Mary Johnson	Pen	10	1.5	2024-02-20
1003	James Lee	Backpack	1	45	2024-01-30
1004	Patricia Brown	Notebook	2	5	2024-03-05
1005	Michael Davis	Pen	5	1.5	2024-02-25
1006	Linda Wilson	Backpack	2	45	2024-03-10
1007	Robert Miller	Notebook	1	5	2024-01-18
1008	Barbara Moore	Pen	7	1.5	2024-02-28

1

Step 1: Create a new column 'Total Sales' to calculate the total price for each order by multiplying Quantity and Price per Unit.

=D2*E2

Expected Result

For Order ID 1001, Total Sales = 3 * 5 = 15

2

Step 2: Create a new column 'Order Month' to extract the month number from the Order Date.

=MONTH(F2)

Expected Result

For Order Date 2024-01-15, Order Month = 1

3

Step 3: Create a new column 'Order Year' to extract the year from the Order Date.

=YEAR(F2)

Expected Result

For Order Date 2024-01-15, Order Year = 2024

4

Step 4: Create a new column 'Customer Initials' to get the initials from Customer Name (first letter of first and last name).

=UPPER(LEFT(B2,1) & MID(B2,FIND(" ",B2)+1,1))

Expected Result

For Customer Name 'John Smith', Customer Initials = 'JS'

Final Result

OrderID | Customer Name | Product  | Quantity | Price | Order Date | Total Sales | Order Month | Order Year | Customer Initials
-------------------------------------------------------------------------------------------------------------
1001    | John Smith    | Notebook | 3        | 5     | 2024-01-15 | 15          | 1           | 2024       | JS
1002    | Mary Johnson  | Pen      | 10       | 1.5   | 2024-02-20 | 15          | 2           | 2024       | MJ
1003    | James Lee     | Backpack | 1        | 45    | 2024-01-30 | 45          | 1           | 2024       | JL
1004    | Patricia Brown| Notebook | 2        | 5     | 2024-03-05 | 10          | 3           | 2024       | PB
1005    | Michael Davis | Pen      | 5        | 1.5   | 2024-02-25 | 7.5         | 2           | 2024       | MD
1006    | Linda Wilson  | Backpack | 2        | 45    | 2024-03-10 | 90          | 3           | 2024       | LW
1007    | Robert Miller | Notebook | 1        | 5     | 2024-01-18 | 5           | 1           | 2024       | RM
1008    | Barbara Moore | Pen      | 7        | 1.5   | 2024-02-28 | 10.5        | 2           | 2024       | BM

✓Total Sales column helps quickly see revenue per order.

✓Order Month and Order Year columns allow grouping sales by time periods.

✓Customer Initials provide a short identifier for customers.

Bonus Challenge

Create a new column 'Sales Category' that labels orders as 'High' if Total Sales is above 20, otherwise 'Low'.

Show Hint

Use the IF function: =IF(G2>20, "High", "Low")

Practice

1. What does the formula =A2 & " " & B2 do when applied in a new column?

easy

A. Adds the numbers in cells A2 and B2

B. Combines the text from cells A2 and B2 with a space between

C. Subtracts the value in B2 from A2

D. Multiplies the values in A2 and B2

Solution

1. Step 1: Understand the & operator in Excel

   The & operator joins (concatenates) text strings together.

2. Step 2: Analyze the formula =A2 & " " & B2

   This formula joins the content of A2, a space " ", and the content of B2 into one text string.

3. Final Answer:

   Combines the text from cells A2 and B2 with a space between -> Option B

4. Quick Check:

   Concatenate text with & = combined text [OK]

Hint: Use & to join text with spaces easily [OK]

Common Mistakes:

• Thinking & adds numbers instead of joining text
• Forgetting to add space between texts
• Using + instead of & for text

2. Which of these formulas correctly converts the text in cell A2 to uppercase in a new column?

easy

A. =UPPER(A2)

B. =PROPER(A2)

C. =LOWER(A2)

D. =TEXT(A2, "UPPER")

Solution

1. Step 1: Identify the function to convert text to uppercase

   The UPPER() function changes all letters in a text string to uppercase.

2. Step 2: Check each option

   LOWER() makes text lowercase, PROPER() capitalizes first letters, TEXT() formats numbers, so only UPPER(A2) is correct.

3. Final Answer:

   =UPPER(A2) -> Option A

4. Quick Check:

   UPPER() = uppercase text [OK]

Hint: Use UPPER() to change text to all caps [OK]

Common Mistakes:

• Using LOWER() instead of UPPER()
• Confusing PROPER() with uppercase
• Trying to use TEXT() for text case

3. Given the data in columns A and B: A2=10, B2=5, what is the result of the formula =A2*B2+2 in a new column?

medium

A. 52

B. 50

C. 12

D. 15

Solution

1. Step 1: Calculate the multiplication part

   Multiply A2 (10) by B2 (5) to get 50.

2. Step 2: Add 2 to the product

   Add 2 to 50, resulting in 52.

3. Final Answer:

   52 -> Option A

4. Quick Check:

   10*5+2 = 52 [OK]

Hint: Multiply first, then add according to order of operations [OK]

Common Mistakes:

• Adding before multiplying (wrong order)
• Confusing cell references
• Ignoring the +2 part

4. You want to create a new column that shows the full name by combining first name in A2 and last name in B2 with a space. Which formula has an error?

medium

A. =A2 & " " & B2

B. =CONCATENATE(A2, " ", B2)

C. =A2 + " " + B2

D. =TEXTJOIN(" ", TRUE, A2, B2)

Solution

1. Step 1: Understand text joining in Excel

   Text strings are joined with & or CONCATENATE or TEXTJOIN functions, not with +.

2. Step 2: Identify the incorrect formula

   Formula D uses + to join text, which causes an error because + is for numbers.

3. Final Answer:

   =A2 + " " + B2 -> Option C

4. Quick Check:

   Use & or CONCATENATE for text, not + [OK]

Hint: Use & or CONCATENATE, never + for text join [OK]

Common Mistakes:

• Using + to join text strings
• Forgetting quotes around space
• Mixing number and text operators

5. You have a column with dates in A2:A10. You want a new column that shows the month name (like "January") for each date. Which formula should you use in B2 and drag down?

hard

A. =MONTH(A2)

B. =YEAR(A2)

C. =DAY(A2)

D. =TEXT(A2, "mmmm")

Solution

1. Step 1: Understand the goal

   You want the full month name, not just the number.

2. Step 2: Check each formula's output

   MONTH() returns month number, DAY() returns day number, YEAR() returns year number. TEXT(A2, "mmmm") converts date to full month name text.

3. Final Answer:

   =TEXT(A2, "mmmm") -> Option D

4. Quick Check:

   TEXT(date, "mmmm") = month name [OK]

Hint: Use TEXT(date, "mmmm") for full month name [OK]

Common Mistakes:

• Using MONTH() which returns number, not name
• Confusing DAY() or YEAR() with month
• Not using quotes correctly in TEXT format