Column transformations in Excel - Dashboard Guide

Dashboard Mode - Column transformations

Dashboard Goal

This dashboard helps you transform and analyze sales data by creating new columns with formulas. You will see total sales, sales tax, and sales after tax for each product.

Sample Data

Product	Price	Quantity Sold
Notebook	5	100
Pen	2	300
Backpack	20	50
Calculator	15	40
Ruler	3	150

Dashboard Components

• Total Sales Column: Formula in D2: =B2*C2 (multiplies Price by Quantity Sold)
• Sales Tax Column (10%): Formula in E2: =D2*0.1 (calculates 10% tax on Total Sales)
• Sales After Tax Column: Formula in F2: =D2+E2 (adds Total Sales and Sales Tax)
• Summary Table: Shows total revenue before tax, total tax collected, and total revenue after tax using these formulas:
  • Total Revenue Before Tax (cell B8): =SUM(D2:D6)
  • Total Tax Collected (cell B9): =SUM(E2:E6)
  • Total Revenue After Tax (cell B10): =SUM(F2:F6)

Dashboard Layout

+-----------------------------+
|        Sales Data Table      |
|  (Columns A to C, rows 1-6) |
+-----------------------------+
| Total Sales | Sales Tax | Sales After Tax |
| (Cols D-F, rows 1-6)          |
+-----------------------------+
|        Summary Table          |
| (Cells A8:B10)               |
+-----------------------------+

Interactivity

You can change the Quantity Sold or Price values in the data table. When you do, the Total Sales, Sales Tax, and Sales After Tax columns update automatically. The summary table below also updates to show new totals.

Self Check

If you change the Quantity Sold for the Pen from 300 to 400, which components update?

• The Total Sales for Pen updates (D3).
• The Sales Tax for Pen updates (E3).
• The Sales After Tax for Pen updates (F3).
• The summary totals in cells B8, B9, and B10 update to reflect the new totals.

Key Result

Dashboard showing sales data with transformed columns for total sales, tax, and after-tax sales plus summary totals.

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