THE PINOCCHIO THEORY

In 1977 Bootsy Collins released the hit song Pinocchio Theory. Its main warning is:

If you fake the funk, your nose will grow

There are a lot of long noses in the data world–specifically with folks who continually bash Excel and write its obituary. Excel users cannot continue to let these folks funk around with Excel and funk around funkin’ with it ’til they funk it up. One person who’s recently been funkin’ with our funk is Joanna Stern who wrote a Wall Street Journal article, Do You Really Need Microsoft Office Anymore?

Joanna didn’t exactly trash Excel or Office. She did an ok overview of Office, Google and Apple products and concluded that it only makes sense to pay for Office 365 if you also want a terabyte of cloud storage.

WAIT!

There’s way more to the story than cloud storage. In the following examples I point out specific features that are only available on desktop Excel, and not available in alternative spreadsheets.

These examples were chosen because they reflect some basic data management needs that have caused some very real heartburn for people who’d gotten in deep with Google Doc and had tough decisions to make at their dead end.

3 Things In Excel & Not In Google Docs

1.  Robust Input Controls: Prevent Entry of Crap Data

The input controls (formally called Data Validation) in Excel are far more robust than in Google spreadsheet. The following example is based on a real and fundamental need: how can we set up a document that will force users to input a number that’s smaller than or equal to the number in another cell?

We have these items and quantities in the INCOMING table.
Under REQUESTS people can choose an item, the # In Stock automatically populates, and the user then Requests a certain number of the item.

In the example, the user wants 2 15-foot equipment cords when only 1 is in stock. I set this up with native Excel features (no VBA code) to compare the numbers and give the error message, as seen. The user is prevented from requesting something that doesn’t exist.

Other uses:

• Prevent users from requesting more money than is available
• Prevent users from gaming reward systems by setting small goals and later turning in results that are over 100%
• Prevent the input of dates that are beyond a specified critical date

Note

• The input controls do have to be put in place on desktop Excel. Once loaded into a OneDrive account the features are intact on Excel Online.

With Google’s spreadsheet, this control IS NOT an option unless you want to get into writing code.

2.  Interactive Web Pages

Below (and in the example in the 3rd section) is an interactive Excel document that you can play with right here on the web page. Click on the buttons (officially called Slicers) for Origin and State and the table in rows 6 through 20 will filter for your selection. To select more than one Origin or State, hold down your control key.

Google spreadsheet will allow you to embed a spreadsheet but it’ll be a static picture. To interact with the data, you have to go back to the original document in Google Drive.

Things to note about the Excel Web App

• The source document does have to be stored in a Microsoft OneDrive account.
• This has to be built on desktop Excel.
• Interactivity is possible through Excel Online or a web page.
• Updates to unprotected data can happen either on desktop Excel or Excel Online.
• Updates to protected data must happen on desktop Excel.
• This is incompatible with Mac versions of Excel. Mac users cannot build this or open it on their desktop. They can, however, interact with the data on a web page or Excel Online.
• Slicers were first available in Excel 2010.
• This document uses and Excel 2013 feature: Slicers with Tables.

 3.  Dropdown Lists Dependent on Contents in Another Cell

Dependent dropdown lists became an issue on a recent project. In Google’s spreadsheet you can have dropdown lists. But what if you want a dropdown list that retrieves a specific dropdown list in another cell? There are various hacks and rickety workarounds that people have come up with in Google’s spreadsheet. I followed one of the examples and it broke when I added new data to one of the lists.

Do a search for: dependent dropdown lists in Google spreadsheet. The results are Google Doc users who are apoplectic over the inability to make a stable dependent dropdown list. The alternative is to put EVERYTHING in a single dropdown list and hope that your users will select the right option.

Consider: If your salesperson has to complete a form and list the result of an activity, you don’t want to see that the activity was Face to Face Meeting and the result was Phone Number Disconnected. They should only have Result options that make sense with the Activity.

Below is an example of Excel’s dependent dropdown lists such that if you choose Funk or Disco, the appropriate list of bands is retrieved. That will then retrieve one of the group’s hit songs.

There is a standard, accepted way for creating dependent dropdown lists in Excel. No hacks. No workarounds.

Other examples of how dependent dropdown lists can be used:

• Choose a state and only that state’s cities will populate in the next dropdown list
• Choose a region and offices in that region will populate
• Choose a movie and retrieve a list that includes only the actors in that movie
• Choose a warehouse and retrieve it’s back-ordered items

NOTE

• The dropdown lists do need to be applied on desktop Excel and then loaded into a OneDrive account for use online.

 CONCLUSION

Joanna Stern acknowledges that Office can be like an overflowing toolbox with tools that you may never use. I agree with that. There’s also something to be said for having those tool available.

• When you need help, someone more familiar with Excel might be able to hand you one of those mystery tools that’s made for exactly what you want. With Google Docs, you can quickly hit a dead end where you need to start writing code or move over to Excel.
• If you have to collaborate with people in any serious way, they’re likely going to do it in Excel.

There will be those who espouse Open Office and want to see Microsoft shrivel up and die. They insist “Open Office can open any Excel spreadsheet.” Well … stand back away from such person. Make room because the Pinocchio Theory is in effect and their nose is about to grow. Even the OpenOffice Wiki does an honest comparison of the products in a section called: Delusional Utopian Hippies vs. Cynical Corporate Greedheads. They don’t pretend that Open Office is an Excel replacement.

The OpenOffice wiki, way back in 2003, concludes that Office and the many alternatives will be around for a very long time. The bottom line is:

What are you trying to do? What tool will work and will allow you some room to grow in your needs and skills?

For the person who can get on without Microsoft Office and Excel, fine. I’m not here to convince the world to board the Office 365 bus. The point here is that these are all tools, and the evangelists for an Excel alternative tend to be completely fakin’ the funk. They set up contrived scenarios and then beat on Excel from that very narrow perspective.

So … do you need to buy Office 365?

It depends on what you are trying to do. Just don’t fake the funk while you’re doing it.

 5 Pinocchios image by CLassicNightmare
Disco Turntable image by solenero73