Column transformations in Excel - Step-by-Step Guide

Introduction

Column transformations let you change or create new columns based on existing data. This helps you clean, format, or analyze your data more easily by applying simple changes to whole columns at once.

When you want to change all dates in a column to a different format.

When you need to combine first and last names from two columns into one full name column.

When you want to convert all text in a column to uppercase or lowercase.

When you want to calculate a new column, like total price from quantity and unit price columns.

When you want to remove extra spaces or unwanted characters from a column of text.

Steps

Step 1: Select the cell where you want the transformed column to start

- Worksheet area

The selected cell is active and ready for input

Step 2: Type the formula to transform the column data

- Formula bar or selected cell

The formula appears in the cell and formula bar

💡 For example, to combine first and last names in A2 and B2, type: =A2 & " " & B2

Step 3: Press Enter to apply the formula to the first row

- Selected cell

The cell shows the transformed result for that row

Step 4: Drag the fill handle down from the formula cell to copy the formula to other rows

- Bottom-right corner of the formula cell

All selected cells show transformed results for their respective rows

💡 Double-click the fill handle to auto-fill down the column if adjacent data exists

Step 5: If needed, convert formulas to values to keep the results without formulas

- Select the transformed column, right-click, choose Copy, then right-click again and choose Paste Special > Values

The column now contains fixed values instead of formulas

Before vs After

Before

Column A has first names, Column B has last names, no combined full name column

After

New Column C shows full names combining first and last names, e.g., 'John Smith'

Settings Reference

Formula calculation mode

📍 Formulas tab > Calculation options

Controls when Excel recalculates formulas after changes

Default: Automatic

Number format

📍 Home tab > Number group

Formats how numbers or dates appear in cells

Default: General

Common Mistakes

Typing a formula without using cell references and instead typing values directly

The formula will not update automatically when data changes

Always use cell references like A2 and B2 in formulas to link to data

Dragging the fill handle without selecting the correct starting cell

The formula may copy incorrectly or not fill the intended range

Select the cell with the correct formula before dragging the fill handle

Not converting formulas to values when needed for fixed data

Formulas may recalculate or cause errors if source data changes or is deleted

Use Paste Special > Values to keep only the results when formulas are no longer needed

Summary

Column transformations let you change or create new columns using formulas based on existing data.

Use formulas with cell references and fill down to apply changes to whole columns quickly.

Remember to convert formulas to values if you want to keep fixed results without formulas.

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