Column transformations in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a column transformation in Excel?

A column transformation means changing or creating new data in a column by using formulas or functions to modify the original values.

beginner

How do you apply a formula to transform an entire column in Excel?

You write the formula in the first cell of the new column, then drag the fill handle down to copy the formula to other cells in that column.

beginner

What does the formula =UPPER(A2) do when applied to a column?

It changes the text in cell A2 to all uppercase letters. When copied down, it transforms all text in that column to uppercase.

beginner

How can you combine text from two columns into one using Excel formulas?

Use the =A2 & " " & B2 formula to join text from columns A and B with a space between them.

intermediate

What is the purpose of absolute references in column transformations?

Absolute references (using $ signs) keep a cell reference fixed when copying formulas down a column, useful when you want to use a constant value or cell.

Which formula converts text in cell A2 to lowercase?

A =TEXT(A2)

B =UPPER(A2)

C =PROPER(A2)

D =LOWER(A2)

The LOWER function changes all letters in the text to lowercase.

How do you copy a formula down an entire column quickly?

A Drag the fill handle down

B Double-click the fill handle

C Right-click and select copy

D Press Ctrl + Z

Double-clicking the fill handle copies the formula down automatically to match adjacent data length.

What does the formula =A2*10 do when copied down a column?

A Multiplies each value in column A by 10

B Adds 10 to each value in column A

C Divides each value in column A by 10

D Copies the value 10 to each cell

The formula multiplies the value in each cell of column A by 10.

Which symbol is used to make a cell reference absolute in Excel?

A #

B &

C $

D *

The dollar sign ($) fixes the row or column reference in formulas.

What does the formula =CONCATENATE(A2, " ", B2) do?

A Joins text from A2 and B2 with a space

B Converts text to uppercase

C Subtracts B2 from A2

D Adds numbers in A2 and B2

CONCATENATE joins text strings; here it joins A2 and B2 with a space in between.

Explain how to transform a column of names to uppercase in Excel.

Think about text functions and copying formulas.

Describe the difference between relative and absolute references when copying formulas in a column.

Consider what happens when you drag formulas down.