UPPER, LOWER, PROPER in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of =UPPER("Good Morning")?

You have the text Good Morning in a cell. What will the formula =UPPER("Good Morning") return?

Excel

=UPPER("Good Morning")

A "GOOD MORNING"

B "good morning"

C "Good Morning"

D "GoOd MoRnInG"

💡 Hint

UPPER converts all letters to capital letters.

The UPPER function changes all letters in the text to uppercase. So "Good Morning" becomes "GOOD MORNING".

📊 Formula Result

intermediate

What does =LOWER("HELLO WORLD") return?

Given the text HELLO WORLD, what is the result of the formula =LOWER("HELLO WORLD")?

Excel

=LOWER("HELLO WORLD")

A "hElLo WoRlD"

B "HELLO WORLD"

C "Hello World"

D "hello world"

💡 Hint

LOWER changes all letters to small letters.

The LOWER function converts all letters in the text to lowercase. So "HELLO WORLD" becomes "hello world".

📊 Formula Result

advanced

What is the output of =PROPER("john DOE")?

You have the text john DOE. What will the formula =PROPER("john DOE") return?

Excel

=PROPER("john DOE")

A "john doe"

B "JOHN DOE"

C "John Doe"

D "John DOE"

💡 Hint

PROPER capitalizes the first letter of each word and makes the rest lowercase.

The PROPER function capitalizes the first letter of each word and makes all other letters lowercase. So "john DOE" becomes "John Doe".

❓ Function Choice

advanced

Which formula converts "mIXeD CaSe" to "mixed case"?

You want to convert the text mIXeD CaSe to all lowercase letters. Which formula will do this?

A =LOWER("mIXeD CaSe")

B =PROPER("mIXeD CaSe")

C =LOWER(PROPER("mIXeD CaSe"))

D =UPPER("mIXeD CaSe")

💡 Hint

LOWER makes all letters small.

The LOWER function converts all letters to lowercase. So it changes "mIXeD CaSe" to "mixed case".

🎯 Scenario

expert

You have a list of names in mixed case. Which formula correctly formats all names as Proper Case?

You have a column with names like "aLEx smiTH", "MARy jOnEs", and "roBERT bRoWN". You want to convert all names to Proper Case (first letter capital, rest lowercase). Which formula should you use if the name is in cell A2?

A =LOWER(A2)

B =PROPER(A2)

C =UPPER(A2)

D =CONCAT(UPPER(LEFT(A2,1)),LOWER(MID(A2,2,LEN(A2))))

💡 Hint

PROPER capitalizes the first letter of each word automatically.

The PROPER function changes the first letter of each word to uppercase and the rest to lowercase, which is exactly what you want for names.

Practice

1. What does the Excel function UPPER("hello world") do?

easy

A. Capitalizes the first letter of each word: Hello World

B. Converts all letters to lowercase: hello world

C. Converts all letters to uppercase: HELLO WORLD

D. Reverses the text: dlrow olleh

Solution

1. Step 1: Understand the UPPER function

   The UPPER function changes all letters in the text to uppercase letters.

2. Step 2: Apply UPPER to the text "hello world"

   All letters become capitalized: "HELLO WORLD".

3. Final Answer:

   Converts all letters to uppercase: HELLO WORLD -> Option C

4. Quick Check:

   UPPER("hello world") = HELLO WORLD [OK]

Hint: UPPER makes all letters capital [OK]

Common Mistakes:

• Confusing UPPER with LOWER
• Thinking PROPER capitalizes all letters
• Expecting UPPER to change only first letters

2. Which of these is the correct formula to convert the text in cell A1 to lowercase in Excel?

easy

A. =LOWER(A1)

B. =LOWERCASE(A1)

C. =LOWERCASE_TEXT(A1)

D. =LOWER_TEXT(A1)

Solution

1. Step 1: Recall the correct function name for lowercase conversion

   The correct Excel function to convert text to lowercase is LOWER.

2. Step 2: Check the syntax for converting cell A1

   The correct syntax is =LOWER(A1). Other options are not valid Excel functions.

3. Final Answer:

   =LOWER(A1) -> Option A

4. Quick Check:

   LOWER function syntax = LOWER(cell) [OK]

Hint: Use LOWER() exactly to get lowercase text [OK]

Common Mistakes:

• Using LOWERCASE instead of LOWER
• Adding extra words like _TEXT
• Missing parentheses

3. What is the result of the formula =PROPER(LOWER("eXcEl LeArNiNg"))?

medium

A. EXCEL LEARNING

B. Excel Learning

C. excel learning

D. eXcEl LeArNiNg

Solution

1. Step 1: Apply LOWER to the text "eXcEl LeArNiNg"

   LOWER converts all letters to lowercase: "excel learning".

2. Step 2: Apply PROPER to the lowercase text

   PROPER capitalizes the first letter of each word: "Excel Learning".

3. Final Answer:

   Excel Learning -> Option B

4. Quick Check:

   PROPER(LOWER(text)) capitalizes first letters only [OK]

Hint: Use LOWER then PROPER to fix mixed case [OK]

Common Mistakes:

• Expecting PROPER alone to fix mixed case
• Confusing UPPER with PROPER
• Ignoring the order of functions

4. You want to capitalize the first letter of each word in cell B2, but your formula =PROPER(B2 gives an error. What is the problem?

medium

A. Cell B2 must contain numbers, not text

B. PROPER function does not work on cell references

C. You must use UPPER instead of PROPER

D. Missing closing parenthesis ")" in the formula

Solution

1. Step 1: Check the formula syntax

   The formula =PROPER(B2 is missing the closing parenthesis ")" which is required to complete the function call.

2. Step 2: Understand function requirements

   PROPER works on text in cell references, so the missing parenthesis is the cause of the error.

3. Final Answer:

   Missing closing parenthesis ")" in the formula -> Option D

4. Quick Check:

   All functions need matching parentheses [OK]

Hint: Always close parentheses in formulas [OK]

Common Mistakes:

• Forgetting closing parentheses
• Thinking PROPER can't use cell references
• Using wrong function names

5. You have a list of names in column A with inconsistent capitalization, like "joHN doE" and "MARY ann". Which formula in column B will convert each name to proper case, ensuring the first letter of each word is capitalized and the rest are lowercase?

hard

A. =PROPER(LOWER(A1))

B. =UPPER(A1)

C. =LOWER(A1)

D. =LOWER(PROPER(A1))

Solution

1. Step 1: Understand the problem with inconsistent capitalization

   Names like "joHN doE" have mixed uppercase and lowercase letters.

2. Step 2: Apply LOWER first to make all letters lowercase

   LOWER(A1) converts "joHN doE" to "john doe".

3. Step 3: Apply PROPER to capitalize first letters

   PROPER(LOWER(A1)) converts "john doe" to "John Doe" correctly.

4. Final Answer:

   =PROPER(LOWER(A1)) -> Option A

5. Quick Check:

   Use LOWER then PROPER for consistent capitalization [OK]

Hint: Use PROPER(LOWER(cell)) for clean capitalization [OK]

Common Mistakes:

• Using PROPER alone leaves some letters uppercase
• Using UPPER or LOWER alone changes all letters incorrectly
• Not nesting functions properly