Google Sheets formulas can be frustrating for a few reasons. One is when you’re trying to remember what to use on the fly. And you might not remember the exact name of a formula. But if you do figure out the name, you might not remember the syntax of what it needs. Then if you know the name, and the syntax, you might not know the proper way to write the data.

There are over 500 formulas in Google Sheets. There’s no way you can memorize all of them, and their use cases, and their syntax. On top of that many can be paired together in interesting ways. But this causes even more confusion as some formulas do some weird things. For example, using ARRAYFORMULA means you have to rewrite your cell references.

Five most frustrating google sheets formulas to new and advanced users:

1. VLOOKUP: The bain of data managers existence.
2. INDEX/MATCH: Not easier to use alternative to Vlookup
3. SUMIF: Most Googled formula
4. CONCATENATE: How do you spell that?
5. ARRAYFORMULA: it’s so powerful but hard to figure out.
   

Notable Mentions

PROPER: This is to capitalize each word in a sentence. That doesn’t seem proper spelling.

QUERY: This formula is so powerful, but it’s such a monster and everybody knows it. Ben Collins has an entire course dedicated to learning QUERY.

IMPORTRANGE: This is great to get information from one file to another file, but it does seem like you’re importing data from somewhere else other than a sheet.

The Five Most Frustrating Formulas

ARRAYFORMULA

This formula can be challenging for some users because it allows you to apply a formula to an entire range of cells at once. This can be useful for saving time and streamlining your workflow, but it can also be confusing if you're not familiar with how arrays work in Google Sheets. Additionally, ARRAYFORMULA may not work correctly with certain types of formulas or data formats, which can make troubleshooting difficult.

CONCATENATE

This is hard only because it’s such a weird word. Probably the weirdest spelling of a formula for what it does. It’s very basic. It combines the values you put in the formula. It can be used when you want to customize the delimiter, instead of using JOIN. And it’s simple to use, as you know the order you put the values in, will be the order of the output.

SUMIF

This is by far the most googled formula. It’s because it’s quite confusing that you need to run a criteria on a range and then select the range you want to add up. SUMIF can be challenging if you're not familiar with how logical operators and conditional statements work in Google Sheets.

Once you understand how to use SUMIF, it can be a powerful tool for analyzing and summarizing data in your spreadsheets. You can use it to filter and extract specific data points, calculate averages, or identify trends and patterns in your data. Additionally, if you need to apply more complex criteria or conditions to your data, you can use the SUMIFS formula, which allows you to specify multiple criteria and conditions for your data range.

INDEX/MATCH

I commonly reference this formula pair as an alternative to VLOOKUP. But it’s not that much easier to use. In fact you might say INDEX / MATCH is harder to use because you need to understand structured data and you also have to do it with two formulas combined. If you’re not familiar with the fact that you can combine formulas, this is a hard formula to grok.

But making INDEX/MATCH work is the work of wizardly. It’s like an act of sorcery the first time you get it working.

VLOOKUP

This takes the cake. The frustration cake. VLOOKUP can be confusing for Google Sheets users because it requires a clear understanding of how the data is structured and what information is being searched for. The specificity that the searched unique key column is the first one, the one on the left, makes this formula extremely difficult to grok.

I have belief that it was intended to be easy to use. It was probably built this way because it’s a common practice in data management and analysis. But gosh, I don’t know why it’s just damn confusing.

But once you do get VLOOKUP working, you feel great. You feel like a goddamn god. You feel like you’ve mastered sheets.

In conclusion

Because of the vast formulas Google Sheets comes with installed and ready to use, it’s quite a powerful tool. But it can also cause quite frustrating moments in your work. With great power comes great learning. You need to learn the syntax, and use cases of these powerful formulas to fully make them work. But when you do, it’s like pure fire spitting from your finger tips. You’ll feel like a million bucks. You’ll feel like a real wizard.

Whether you're analyzing sales data, creating financial reports, or tracking project progress, these formulas can help you save time and make better decisions.

So while it may take some practice and patience to master the more challenging formulas in Google Sheets, it's definitely worth the effort. With a little bit of dedication and effort, you can become a true wizard of data analysis, and use the full power of Google Sheets to unlock insights and drive better outcomes for your business or personal projects.

It's hard Learning Google Sheets!

I know it's hard! How hard is it you ask?

Learn about how hard!

Get Better by becoming a Better Sheets Member

Join Better Sheets to get better at these formulas through step-by-step tutorials. Learn from me: that google sheets guy in the office.