Excel vs Google Sheets comparison - Practice Questions

Challenge - 5 Problems

🎯 Scenario

intermediate

Comparing Formula Behavior: SUMIFS

You have a list of sales data in both Excel and Google Sheets. You want to sum sales where the region is 'West' and the sales amount is greater than 1000.

Which formula will work correctly in both Excel and Google Sheets?

A =SUMIFS(B2:B10, A2:A10, "West", B2:B10, ">" & 1000)

B =SUMIFS(B2:B10, A2:A10, "West", B2:B10, ">1000")

C =SUMIFS(B2:B10, A2:A10, "West", B2:B10, ">1000") + 0

D =SUMIFS(B2:B10, A2:A10, "West", B2:B10, ">1000") * 1

💡 Hint

Look for how criteria with comparison operators are written in formulas.

In both Excel and Google Sheets, when using comparison operators in criteria, you need to concatenate the operator with the value using &. Option A does this correctly.

📊 Formula Result

intermediate

Result of ARRAYFORMULA in Google Sheets

In Google Sheets, you enter the formula =ARRAYFORMULA(A2:A5 * B2:B5) where columns A and B contain numbers 1 to 4 in rows 2 to 5.

What is the output in cells where the formula applies?

A 1, 2, 3, 4

B 1, 2, 6, 12

C 1, 4, 9, 16

D Error: ARRAYFORMULA not supported

💡 Hint

ARRAYFORMULA applies the operation element-wise over ranges.

The formula multiplies each element in A2:A5 by the corresponding element in B2:B5. Since both ranges contain 1,2,3,4, the results are 1*1=1, 2*2=4, 3*3=9, 4*4=16.

❓ Function Choice

advanced

Which Function is NOT Available in Excel but Exists in Google Sheets?

Which of the following functions is available in Google Sheets but does NOT exist in Excel?

A VLOOKUP

B FILTER

C SUM

D IF

💡 Hint

Think about dynamic array functions introduced recently.

The FILTER function is native to Google Sheets and allows filtering data by criteria dynamically. Excel introduced FILTER only recently in newer versions, but traditionally it was not available.

❓ data_analysis

advanced

Handling Circular References

You create a formula in Excel that refers to its own cell, causing a circular reference warning. How does Google Sheets handle circular references by default?

A Google Sheets allows circular references and calculates iterative results by default.

B Google Sheets converts circular references into static values automatically.

C Google Sheets ignores circular references silently and returns zero.

D Google Sheets blocks circular references and shows an error without calculation.

💡 Hint

Think about how spreadsheet apps warn users about self-referencing formulas.

Google Sheets blocks circular references by default and shows an error message. Excel can be set to allow iterative calculations, but Google Sheets does not allow this by default.

🧠 Conceptual

expert

Collaboration Features Comparison

Which statement best describes the collaboration features difference between Excel and Google Sheets?

A Google Sheets allows real-time multi-user editing with automatic saving, while Excel requires OneDrive or SharePoint for similar features.

B Excel allows real-time multi-user editing by default without any cloud service, Google Sheets does not support multi-user editing.

C Both Excel and Google Sheets require manual saving to share changes with collaborators.

D Excel supports real-time editing only on desktop apps, Google Sheets only on mobile apps.

💡 Hint

Consider how cloud services affect collaboration in these apps.

Google Sheets is built for real-time collaboration with automatic saving in the cloud. Excel supports similar features but needs OneDrive or SharePoint to enable real-time multi-user editing.