How to quickly show all Formulas in Google Sheets

If you built your own Google Sheet template and loaded it with formulas, you may know where they all are by heart. I mean, you built this spreadsheet structure from scratch. It's your baby. You can find the formulas while being blindfolded.

What if you were not the architect, and you’re staring at a spreadsheet someone else built? Where are the formulas located? Which cells have formulas, and which ones only have values? What’s driving what here?

If you find yourself with this burning curiosity to see what's under the hood and quickly make all formulas visible, then this tutorial posted on groovypost.com is for you. This article’s author, Ben Stockton, will take you through the step-by-step shortcuts to make these formulas come out from their hiding places.

Why use a formula in the first place? Ben explains, “Formulas are the hidden workhorse behind spreadsheets. If you’re trying to calculate something, you’ll probably expect a spreadsheet formula to figure it out for you.”

Ben explains one of many reasons for showing the formulas driving your Google Sheets template:

“What happens if things don’t seem to be working out properly?  If you’re using Google Sheets, you can quickly show the formulas you’re using. For instance, this can be useful if you’re trying to track down errors across a large spreadsheet.”

Many finance teams need no introduction to spreadsheet formulas. They are pushing their formulas to the very edge of the universe to drive and model their complex FP&A needs. If you combine massively complex formulas (and many of them at that) with large data sets, the outcome is very predictable. The performance of your spreadsheet will first slow to a crawl and then eventually crash.

In today’s complex and global economy, many FP&A teams need to process models far in excess of what their individual spreadsheets can handle. That makes sense. Why should a complex business model be limited to what an individual spreadsheet can process?

FinLogic.io is a modern and agile FP&A automation platform that gives Google Sheets superpowers. With FinLogic.io, your spreadsheet is converted as a window to a powerful and centralized data cube. That’s correct! A full-fledged cloud-based data cube. It sounds space age, doesn't it? What’s a cloud data cube, you may find yourself asking. It’s a robust and cloud-based multi-dimensional database. A database able to combine and analyze a high number of distinct dimensions within any dimensional level at the exact same time. It also does that super quick. That’s the ‘robust’ part of the equation.

While finance users love their spreadsheets, they are always faced with two primary limitations.

Spreadsheet limitation # 1

FP&A teams have a burning desire to always analyze many dimensions simultaneously at different levels, but the spreadsheet is technically limited to only two dimensions. You know those two dimensions by name: rows and columns. What happens when you need to bring a third dimension into your analysis? Things get tricky at that point. This is when you end up with 30 versions of the same model. Each new version for a new dimension that you were unable to calculate at the same time on the original analysis. Why? Because your spreadsheet is limited to only two dimensions. It’s a two-seater. There’s no comfortable seat for that third passenger. Maybe you want to analyze products against regions. That’s only two dimensions, so you can easily do that on a spreadsheet. What if you want to add a period as a third dimension? You then have to create separate versions for each period. One document for 2022, another for 2023, and so forth. Now, what happens if you need to bring in a fourth dimension and calculate everything against entities or cost centers? Now things get complex, and you start inching closer to spreadsheet hell.

With FinLogic.io, you are no longer limited to only two dimensions. FinLogic.io establishes a real-time connection with a data cube where you have unlimited dimensions to play with. You no longer need many versions of the same template. Instead of 30 versions of your Capex model, you only need a single version for the rest of time.

So what’s the second spreadsheet limitation?

Spreadsheet limitation # 2

Most finance teams use their spreadsheets as a database. Ouch! We get that you love the familiarity of the spreadsheet, but it's not a database designed to drive corporate FP&A data. Sure, you can create filters, but only if all your data is present within the spreadsheet file. You would not dare store all of your ERP data within a few local documents on your computer, but for some reason, many FP&A teams try to do exactly that. Mind-boggling?

That’s not true, you may say! My team has designed very complex links between spreadsheets to spread our data across multiple files. That’s basically a corporate system now with centralized data via links, you may try to convince yourself.

How’s that working out for you? Those links are a mirage. You may think you now have the essence of centralized data, but that could not be farthest from the truth. Spreadsheet links easily break, are complex to maintain, are slow to process, and are prone to human errors. If you combine spreadsheet links with a team that is constantly creating new spreadsheet versions, the whole thing becomes a massive spider web.

It's also not unusual for a single individual within the team to have a mental map of this spiderweb and how all the links and formulas interoperate. If that person ever exits the organization unexpectedly, the entire model becomes dysfunctional.

Is there a solution to this madness?

With FinLogic.io, you can continue to access your data through the lens of Google Sheets, but you no longer use it as a database. Isn’t that genius? Continue to allow users to access and play with their data within the spreadsheet interface and logic they know and love, but detach all the data from it. It’s not a foreign architecture to anyone who has ever logged into their Facebook or LinkedIn accounts. You use a browser to access a cloud-based database. How quickly would your browser crash if all of Facebook’s data was residing locally within your browser? That’s exactly what many finance teams attempt to do with their corporate data by storing it locally within spreadsheet files.

What does this all have to do with formulas? This article is about formulas; that’s why I clicked on it.

Let’s put it all together.

With all of your data stored in the FinLogic.io data cube, your formulas are no longer processed locally on your spreadsheet. There is nothing to process locally since all of your data is now in a powerful cloud data cube. If you’re using local Excel, those heavier calculations are limited by your computer's processing power. Can you really analyze your business's complexities in a single notebook? Even if you already use Google Sheets, you’re still limited to the processing power of that single online document.

Imagine the flexibility of designing your spreadsheet formulas within Google Sheets and calculating them in a powerful data cube instead of within the local file. That’s the benefit of giving your spreadsheet superpowers with FinLogic.io.

‍