Consolidating data from multiple sheets in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula consolidating data?

You have three sheets named Jan, Feb, and Mar. Each sheet has sales data in cells A2:A5. The formula =SUM(Jan!A2:A5, Feb!A2:A5, Mar!A2:A5) is entered in a summary sheet. What does this formula return?

A The sum of all sales values from A2 to A5 across Jan, Feb, and Mar sheets combined

B The sum of sales values only from the Jan sheet A2 to A5

C The sum of sales values from the summary sheet cells A2 to A5

D An error because ranges from multiple sheets cannot be summed this way

💡 Hint

Think about how SUM works with multiple ranges from different sheets.

The formula sums all values in the ranges A2:A5 from each sheet Jan, Feb, and Mar, adding them together into one total.

❓ Function Choice

intermediate

Which function best consolidates data from multiple sheets into one list?

You want to create a combined list of all product names from sheets named Store1, Store2, and Store3. Each sheet has product names in column A. Which function is best to consolidate all product names into one column in a summary sheet?

A Use <code>=VLOOKUP()</code> to find products across sheets

B Use <code>=CONCATENATE()</code> to join product names from each sheet

C Use <code>=SUM()</code> to add product names

D Use <code>=FILTER()</code> combined with <code>INDIRECT()</code> to pull product lists dynamically

💡 Hint

Think about how to pull lists dynamically from multiple sheets.

FILTER combined with INDIRECT can dynamically pull and combine lists from multiple sheets into one consolidated list.

❓ data_analysis

advanced

Analyzing consolidated sales data from multiple sheets

You consolidated monthly sales data from sheets Jan, Feb, and Mar into a summary sheet using =SUM(Jan!B2:B10, Feb!B2:B10, Mar!B2:B10). You want to find which month had the highest total sales. How can you do this efficiently?

A Use <code>=MAX(Jan!B2:B10, Feb!B2:B10, Mar!B2:B10)</code> to find the highest single sale value

B Calculate each month's total separately with <code>=SUM(Jan!B2:B10)</code>, <code>=SUM(Feb!B2:B10)</code>, <code>=SUM(Mar!B2:B10)</code> and compare

C Use <code>=SUM(Jan:Mar!B2:B10)</code> to get the highest monthly total

D Use <code>=AVERAGE(Jan!B2:B10, Feb!B2:B10, Mar!B2:B10)</code> to find the highest month

💡 Hint

Think about how to get totals per month before comparing.

You must sum each month's data separately to get totals, then compare those totals to find the highest month.

🎯 Scenario

advanced

Scenario: Consolidating dynamic data ranges from multiple sheets

You have sheets named Week1, Week2, and Week3. Each week has a different number of sales entries in column A. You want a formula in a summary sheet that sums all sales from all weeks, even if the number of rows changes weekly. Which approach works best?

A Use <code>=SUM(Week1!A2:A5, Week2!A2:A5, Week3!A2:A5)</code> fixed range

B Use <code>=SUM(Week1!A2:A100, Week2!A2:A100, Week3!A2:A100)</code> assuming max 100 rows

C Use <code>=SUM(INDIRECT("Week1!A2:A"&COUNTA(Week1!A:A)), INDIRECT("Week2!A2:A"&COUNTA(Week2!A:A)), INDIRECT("Week3!A2:A"&COUNTA(Week3!A:A)))</code>

D Use <code>=SUM(Week1!A:A, Week2!A:A, Week3!A:A)</code> to sum entire columns

💡 Hint

Think about how to dynamically adjust ranges based on data length.

Using INDIRECT with COUNTA dynamically adjusts the range to sum only the filled rows in each sheet.

📊 Formula Result

expert

What error does this formula produce when consolidating data?

You try to consolidate data with the formula =SUM(Jan:Mar!A2:A5) in Excel. What happens when you enter this formula?

A It returns the sum of A2:A5 from all sheets between Jan and Mar inclusive

B It returns a #REF! error because 3D references cannot be used with SUM

C It returns a #NAME? error because the sheet names are invalid

D It returns a #VALUE! error because ranges across sheets must be summed individually

💡 Hint

Think about how Excel handles 3D references in SUM.

Excel supports 3D references like Jan:Mar!A2:A5 in SUM to sum the same range across multiple sheets between Jan and Mar.

Practice

1. What is the main purpose of consolidating data from multiple sheets in Excel?

easy

A. To combine data from different sheets into one summary

B. To delete duplicate sheets automatically

C. To create charts from a single sheet only

D. To protect sheets with passwords

Solution

1. Step 1: Understand consolidation concept

   Consolidation means combining data from multiple sheets to get a summary.

2. Step 2: Identify the purpose

   It helps to see combined totals or summaries in one place.

3. Final Answer:

   To combine data from different sheets into one summary -> Option A

4. Quick Check:

   Consolidate = Combine data [OK]

Hint: Think 'combine' when you hear consolidate [OK]

Common Mistakes:

• Confusing consolidation with deleting sheets
• Thinking it only creates charts
• Assuming it protects sheets

2. Which formula correctly sums cell A1 from sheets named Sheet1, Sheet2, and Sheet3?

easy

A. =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1)

B. =SUM(A1:Sheet3!A1)

C. =SUM(Sheet1:A1, Sheet2:A1, Sheet3:A1)

D. =SUM(Sheet1!A1:A3)

Solution

1. Step 1: Understand sheet references in formulas

   To sum cells from different sheets, use sheetname!cell syntax separated by commas.

2. Step 2: Check each option

   =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1) correctly sums A1 from each sheet individually.

3. Final Answer:

   =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1) -> Option A

4. Quick Check:

   SUM(sheet!cell, ...) = correct [OK]

Hint: Use sheetname!cell for cross-sheet references [OK]

Common Mistakes:

• Using range syntax across sheets incorrectly
• Missing exclamation mark after sheet name
• Trying to sum ranges that don't exist

3. Given Sheet1!A1=10, Sheet2!A1=20, Sheet3!A1=30, what is the result of =SUM(Sheet1!A1:Sheet3!A1)?

medium

A. 60

B. 10

C. Syntax Error

D. #REF! error

Solution

1. Step 1: Understand range syntax across sheets

   Excel does not allow ranges like Sheet1!A1:Sheet3!A1 across multiple sheets in SUM.

2. Step 2: Identify the error

   This causes a #REF! error because the range is invalid.

3. Final Answer:

   #REF! error -> Option D

4. Quick Check:

   Invalid cross-sheet range = #REF! error [OK]

Hint: Cross-sheet ranges like Sheet1!A1:Sheet3!A1 cause errors [OK]

Common Mistakes:

• Assuming cross-sheet ranges work like normal ranges
• Expecting sum of all values without correct syntax
• Ignoring error messages

4. You want to sum cell B2 from sheets Jan, Feb, and Mar using =SUM(Jan!B2, Feb!B2, Mar!B2), but it returns 0. What is the likely problem?

medium

A. Formula syntax is incorrect

B. Sheet names are misspelled or missing

C. Cells B2 are empty in all sheets

D. SUM function cannot add multiple sheets

Solution

1. Step 1: Check sheet names in formula

   If sheet names are misspelled or do not exist, Excel treats references as zero.

2. Step 2: Verify cell contents

   If cells have values but formula returns 0, likely sheet names are wrong.

3. Final Answer:

   Sheet names are misspelled or missing -> Option B

4. Quick Check:

   Wrong sheet names = zero result [OK]

Hint: Double-check sheet names spelling in formulas [OK]

Common Mistakes:

• Assuming syntax error instead of name error
• Not verifying cell contents
• Thinking SUM can't add multiple sheets

5. You have monthly sales data in sheets named Jan, Feb, Mar in cell C5. You want a summary sheet cell A1 to show total sales for these months. Which formula correctly consolidates this data?

hard

A. =SUM(Jan:C5, Feb:C5, Mar:C5)

B. =SUM('Jan:Mar'!C5)

C. =SUM(Jan:Mar!C5)

D. =SUM(Jan!C5:Mar!C5)

Solution

1. Step 1: Understand 3D reference syntax

   Using Jan:Mar!C5 sums cell C5 across all sheets from Jan through Mar.

2. Step 2: Check each option

   =SUM(Jan:Mar!C5) uses correct 3D reference syntax without quotes.

3. Final Answer:

   =SUM(Jan:Mar!C5) -> Option C

4. Quick Check:

   3D reference = SUM(Jan:Mar!C5) [OK]

Hint: Use 3D references like Jan:Mar!Cell to sum across sheets [OK]

Common Mistakes:

• Adding quotes around sheet range
• Using invalid range syntax across sheets
• Trying to sum ranges instead of single cells