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")