Merge and center cells in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the result of merging and centering cells A1 to C1 with text?

You have text in cell A1: "Sales Report". You select cells A1 to C1 and apply Merge and Center. What will be the visible output in the merged cell?

A The text "Sales Report" appears only in cell A1, and cells B1 and C1 remain empty.

B The text "Sales Report" is duplicated in cells A1, B1, and C1.

C The text "Sales Report" appears centered across the merged cells A1:C1.

D The text disappears and the merged cells appear empty.

💡 Hint

Think about what Merge and Center does to the text in the first cell.

When you merge and center cells, the content of the upper-left cell (A1) stays visible and is centered across the merged range. Other cells' contents are removed.

🎯 Scenario

intermediate

How to keep data from multiple cells after merging?

You have values in cells A1: "Jan", B1: "Feb", and C1: "Mar". You want to merge these cells and keep all three month names visible in the merged cell. Which approach works best?

A Concatenate the values into one cell first, then merge and center.

B Copy all values into one cell manually, then merge and center.

C Select A1:C1 and click Merge and Center directly.

D Merge and Center will automatically combine all text from the selected cells.

💡 Hint

Think about how Excel handles text in multiple cells when merging.

Merge and Center keeps only the upper-left cell's content. To keep all text, you must combine it first (e.g., with CONCATENATE or &), then merge.

❓ Function Choice

advanced

Which formula combines text from cells A1, B1, and C1 for merging?

You want to merge cells A1:C1 and display combined text from these cells in the merged cell. Which formula correctly combines the text with commas?

A All of the above

B =A1 & ", " & B1 & ", " & C1

C =TEXTJOIN(", ", TRUE, A1:C1)

D =CONCATENATE(A1, ", ", B1, ", ", C1)

💡 Hint

Consider different ways to join text in Excel.

All three formulas combine text with commas. TEXTJOIN is newer and handles ranges easily, CONCATENATE and & also work.

📊 Formula Result

advanced

What happens to cell references after merging cells?

You merge cells A1 to C1. Then you enter a formula in D1: =A1. What value does D1 show?

A Zero, because merged cells cannot be referenced.

B A #REF! error because A1 is merged.

C The value from the original A1 before merging.

D The value from the merged cell A1:C1.

💡 Hint

Think about how Excel treats merged cells as one cell.

After merging, the merged area is treated as one cell with the address of the upper-left cell (A1). Formulas referencing A1 get the merged cell's value.

❓ data_analysis

expert

Identify the issue with merged cells in sorting data

You have a table with merged header cells in row 1 (A1:C1 merged). When you try to sort the data below by column A, what problem occurs?

A The merged cells split automatically before sorting.

B Excel shows an error and refuses to sort because of merged cells.

C Only the merged header row moves, data stays in place.

D The sort works normally without any issues.

💡 Hint

Think about how merged cells affect sorting operations.

Excel cannot sort ranges that include merged cells because merged cells disrupt the row structure. It shows an error and stops sorting.

Practice

1. What does the Merge and Center feature do in Excel?

easy

A. Copies the content of one cell to all selected cells.

B. Combines selected cells into one and centers the text inside.

C. Splits one cell into multiple cells and centers the text.

D. Deletes all selected cells and centers the remaining cells.

Solution

1. Step 1: Understand the Merge and Center function

   This feature combines multiple selected cells into a single larger cell.

2. Step 2: Understand text alignment after merging

   The text from the top-left cell is centered in the new merged cell.

3. Final Answer:

   Combines selected cells into one and centers the text inside. -> Option B

4. Quick Check:

   Merge and Center = Combine cells + center text [OK]

Hint: Merge cells to create one big cell with centered text [OK]

Common Mistakes:

• Thinking it splits cells instead of merging
• Assuming all cell contents are kept after merge
• Believing it copies content to all merged cells

2. Which of the following is the correct way to merge and center cells in Excel?

easy

A. Select cells -> Data tab -> Merge & Center button

B. Select cells -> Insert tab -> Merge & Center button

C. Select cells -> Home tab -> Merge & Center button

D. Select cells -> Review tab -> Merge & Center button

Solution

1. Step 1: Locate the Merge & Center button

   It is found on the Home tab in the Alignment group.

2. Step 2: Apply merge and center

   Select the cells you want to merge, then click the Merge & Center button on the Home tab.

3. Final Answer:

   Select cells -> Home tab -> Merge & Center button -> Option C

4. Quick Check:

   Merge & Center button is on Home tab [OK]

Hint: Find Merge & Center on Home tab, Alignment group [OK]

Common Mistakes:

• Looking for Merge & Center on wrong tabs
• Trying to merge from Insert or Data tabs
• Not selecting cells before clicking Merge & Center

3. You have cells A1, B1, and C1 with values "Sales", "2023", and "Q1" respectively. You select all three and click Merge and Center. What will be the content of the merged cell?

medium

A. The merged cell will be empty

B. "Sales 2023 Q1" combined in the merged cell

C. "Q1" centered across the merged cell

D. "Sales" centered across the merged cell

Solution

1. Step 1: Understand which cell content remains after merging

   Only the content of the top-left cell (A1) remains after merging.

2. Step 2: Check the content of the top-left cell

   Cell A1 contains "Sales", so this text will be centered in the merged cell.

3. Final Answer:

   "Sales" centered across the merged cell -> Option D

4. Quick Check:

   Top-left cell content remains after merge [OK]

Hint: Only top-left cell text stays after merge [OK]

Common Mistakes:

• Assuming all cell texts combine after merge
• Thinking the last cell's content remains
• Expecting merged cell to be empty

4. You tried to merge cells A2 to C2 but got an error saying "Merge cells only works on contiguous cells." What is the most likely cause?

medium

A. You selected non-adjacent cells instead of a continuous range.

B. You forgot to select the cells before clicking Merge & Center.

C. You tried to merge cells with different data types.

D. You used the wrong tab to merge cells.

Solution

1. Step 1: Understand the error message

   The error means the selected cells are not next to each other in a continuous block.

2. Step 2: Check selection of cells

   Only contiguous (adjacent) cells can be merged, so selecting non-adjacent cells causes this error.

3. Final Answer:

   You selected non-adjacent cells instead of a continuous range. -> Option A

4. Quick Check:

   Merge requires contiguous cells [OK]

Hint: Select continuous cells only before merging [OK]

Common Mistakes:

• Selecting cells with gaps between them
• Trying to merge cells from different rows or columns non-contiguously
• Ignoring the error message

5. You want to create a title across columns A to D that says "Monthly Report" centered in the merged cell. Which steps should you follow?

hard

A. Type "Monthly Report" in A1, select A1:D1, then click Merge & Center.

B. Type "Monthly Report" in A1, select B1:D1, then click Merge & Center.

C. Type "Monthly Report" in D1, select A1:D1, then click Merge & Center.

D. Type "Monthly Report" in A1, select A1:D1, then press Delete.

Solution

1. Step 1: Enter the title text in the top-left cell

   Type "Monthly Report" in cell A1, which will be the content kept after merging.

2. Step 2: Select the range to merge and apply Merge & Center

   Select cells A1 to D1 and click the Merge & Center button to combine and center the text.

3. Final Answer:

   Type "Monthly Report" in A1, select A1:D1, then click Merge & Center. -> Option A

4. Quick Check:

   Title in top-left cell + merge range + center [OK]

Hint: Type in top-left cell, select full range, then merge & center [OK]

Common Mistakes:

• Typing text outside the top-left cell
• Selecting wrong cells for merging
• Deleting cells instead of merging