On ExcelTV episode 24 someone mentioned a Business Insiders article where Google plans to nab 80% of Microsoft’s Office business. This sounded ludicrous because every time I’ve tried to use Google Sheets for anything serious here’s how it tends to go:

1. Within 1 hour there’s something fundamental that I need to do and I know where it’s at in Excel, but need help finding it in Google Sheets.
   

   Reflection of the madness that Google Sheets is legit for serious data management.

2. Search the internet for instruction on finding the feature in Google Sheets.
3. Search returns years of Google users complaining in forums that the feature doesn’t exist for them.
4. (Sometimes) Search uncovers several fragile hacks that people have cobbled together as a work-around.

In 3 Basics Where Excel Whoops Google, I describe 3 features that lead me on such misadventures.

I’ve tried to develop solutions in GS on behalf of friends and clients, and the result is the same: if the integrity of the data is important, GS is not where you want to be unless you’re prepared to write JavaScript.

There’s a risk that I’m an expert who’s speaking as if the rest of the world should be experts. However, I’m trying to be careful and respectfully acknowledge the reality that a lot of people are ok with GS.

WHAT’S THE BIG DEAL?

The Business Insiders article describes Google Apps, and how Google’s objective is to ensure that Microsoft Office documents can be open in Google for small edits. So, there isn’t a promise of full functionality between Excel and Google Sheets. There’s already plenty of evidence that GS can open an Excel document and features are gone and things are moved around. Once re-opened in Excel it’s not back to its initial state, it’s just a mess. So, being able to open for light editing must be taken literally. They aren’t promising that your formulas, slicers and data validation will remain intact.

I count myself among those who use Google Sheets to do light editing and reading. It’s because 1. I move my real work over to Excel or, 2. A collaborator shared a document with me to look at.  So, yes. Light editing and reading.

REVIEW OF FEATURES

Head-to-Head Google Sheets vs Excel

Below is a video where I show Excel’s UnPivot feature. Currently, it does not exist in GS, but the project started in GS. OBJECTIVE: How can Canadian area codes be matched up with their respective provinces?

Here is a link where a Google Sheets user asks for a way to “reverse pivot” (another way of saying UnPivot) and the solutions that are offered involve writing code.

And what does it mean when you start getting into writing code? Someone’s code is probably not going to work for your use. You’ll need to capture the concepts and tweak the code to fit your specific purpose. Tweaking code isn’t an issue if you aspire to have programming skill. However, most folks have data management as a secondary or tertiary responsibility. They aren’t looking to become programmers. Actually, a lot of folks would rather not handle data at all. So, dealing with code isn’t a viable solution for most.

YOU DON’T KNOW WHAT YOU DON’T KNOW

“I didn’t know Excel could do that!” someone is somewhere saying that right now. Maybe they got help making a dropdown list that’s dependent on a previous dropdown list and can see how it can help in so many other ways.

In the video above, I show a way to match Canadian area codes with Provinces. The question came to me via a Google Sheets user. He’s an aspiring data person who had a need but didn’t have the language for it. He doesn’t go about his life thinking “Text-to-columns, unpivot, tables, and dynamic spreadsheet development.”

He had a need but not the language to request the right tool. I feel like Google is relying on people to stay in that place. As long as you don’t know you’re looking for something like “cascading dropdown lists”, you’ll be forever joyous that Google is free. (Extending the 90% of a car analogy: if you don’t know that dancehall music is bass-heavy, you never miss your subwoofer.)

Google is good for a lot of things, but in this instance they remind me of Mr. Potter when he hid the money from Uncle Billy in It’s A Wonderful Life. They’re hoping for a victory by manipulating reality.

But what happens when you do outgrow GS? Then what? Getting years of data out of Google Drive isn’t as easy as listing a car on Craigslist.

HOW EASY IS IT TO ESCAPE?

It’s vexing when I find out that someone needs help with their data; there’s an easy solution in Excel; a solution doesn’t exist in GS; and the business has YEARS of data in GS. Their processes, and the document sharing are all intertwined with Google. Escape is unlikely.

The alternatives to escape are:

• Do things the long, difficult, error-prone way
• Go without
• Start learning and writing code
• Hire someone who can write the code
• Look for an add-on
• Have some data in Google Drive and have Excel data stored somewhere else
• Abandonment

TANGENT: EASE OF ESCAPE IS IMPORTANT FOR ALL DATA-RELATED SOLUTIONS

This should be taken very seriously when exploring any type of data-management solution: the cost of

1. Installing and configuring the potential application
2. Loading the data and testing, testing, testing
3. If you don’t like it, exporting your data and trying something else

Some solutions are very unforgiving in this regard. It could be an online timesheet, inventory or sales data, maintenance records … anything. It pays to do research up front before you commit to trying something. It could take weeks or months to get into these things.

SHARING

The following are comments from others who’ve expressed concern about sharing. Google Sheets set the tone for co-authorship (the ability for users to work on a document simultaneously). But that’s about it. True sharing of GS? Nope. So, is it anything short of silly that Google can suggest that part of a company can use GS and another part can use Excel? And the GS users won’t even have Excel because it saves money?

CONCLUSION

Google Sheets is good for those at the extremes: the absolute simplest data management or the JavaScript coders. For those in the middle, the allure of co-authoring comes at a price. Data integrity is not a priority.

Another perspective on the Microsoft vs Google battle royale is really corporate interest vs open source. Let’s look at what’s happening with WordPress. The dream of something for the people, at no cost and fully customizable, more and more is becoming a developers’ tool.

Because there are regular folks, hobbyists, newbie coders, and sloppy programmers can contribute to the open source libraries, it’s hard to know how long a plugin or theme with remain functional. Also: is the original developer of this plugin/add-on/theme still alive? Are they still maintaining this thing? It doesn’t matter if you’re a coder because you’re likely to be able to fix it yourself.

Google Docs is similar with it’s add-ons. Regular folks make these things as enhancements to the basic spreadsheet, and it’s a great idea. However, non-coders, have to maintain the discipline to not get too complicated because if something goes wrong, you’re on your own. Vaya con dios, buddy! If things crap out because of some obscure add-on and the developer is unresponsive, there ya’ go.

The key point is “don’t get too complicated.” But it’s hard to know what’s “too complicated” when GS is wrongly compared to Excel.

With Microsoft Excel there is a company that charges money for a closed system and the developers are interchangeable. And it’ll take a much longer time for an Excel user to need VBA compared to an enlightened GS user who’ll need JavaScript.

Let’s present this at the most extreme. Pick one:

• Pay the nanny state or
• Live in the open wilderness

Neither is bad, but you need to have a sober assessment of what you’ll need to live in the wilderness before you freeze to death under an unvalidated spreadsheet.