21- The Final Transformation: One Click That Will Change Your Life Forever

In chapter one, I set the stage: the world needs Excel Pros more than anyone realizes yet, and needs us dramatically more than ever (thanks to the twin forces of ‘Big Data’ and economic pressure).

In order to meet that need, and claim proper recognition (and compensation!) for doing so, I said we required a dramatic expansion in the power of our toolset. Specifically, I highlighted four key problems with ‘traditional’ Excel that are holding us back:

1. Too much manual effort goes into creating and maintaining traditional Excel reports.

2. Integration of multiple data sources into unified insight is particularly tedious.

3. Truly “Big” Data does not fit because of the 1 million row limit.

4. Excel has an image problem that undermines the perceived importance of your work.

In the pages between that first chapter and this one, I have shown you how PowerPivot addresses the first three problems:

1. The centralized, sophisticated, and ‘portable’ logic provided by DAX measures cuts manual effort to 10% or less of traditional levels.

2. Integration of multiple data sources now requires only three clicks for each new data source. This is thanks to the power of Relationships, which even solve the dreaded ‘granularity mismatch’ problem such as that posed by ‘Budget vs. Actuals’ situations.

3. Hundreds of millions of rows of data can be addressed and analyzed.

Along the way, I hope you’ve also seen that PowerPivot lives up to my analogy of ‘biplane upgraded to jet plane, but with a familiar cockpit.’

However, I haven’t yet addressed the fourth problem: image. In this chapter, I will explain what that image problem is, and how PowerPivot can provide a brand new perception of our work.

The Hidden Credibility Thief Steals From You Every Day

Excel Pros are at war every day with an unseen enemy, one that does more harm to us as professionals than every other problem combined. More than office politics. More than even the most dreadful formula mistake.

This sinister force operates right under our noses, hidden in plain sight, all day, every day. I will first unmask this villain, and then explain how we will put him away forever.

Let’s start in an innocuous place: once upon a time, there was a spreadsheet…

A Common Practice: Making Spreadsheets Look Less Like… Spreadsheets

Here’s a relatively sophisticated PowerPivot workbook that I created.

Figure 350 This PowerPivot workbook tells retailers their top competitors in certain demographics.

It’s a slick looking workbook (if I do say so myself), and offers slick functionality. But let’s make it look a little bit less ‘spreadsheet-y.’

Figure 351 Turn off Gridlines and Headers.

Figure 352 Looking less like a spreadsheet!

I’ll even park the selected cell behind that slicer up top, so that I don’t see it:

Figure 353 Cell A4 is behind the slicer, so you don’t see a selected cell.

Figure 354 Looking good! But there are a few lingering ‘spreadsheet’ artifacts…

Have I Succeeded? Does It No Longer Seem Like a Spreadsheet?

Um, no. I’ve done everything I can and still have not succeeded. Let’s highlight some of the things I can’t control:

Figure 355 Toolbar, File Menu, the Ribbon, Filename ending in ‘XLSX’, Microsoft Excel’s name itself (a dead giveaway) and a Formula Bar!

This is definitely still Excel. No one is going to miss that.

Why Does It Matter?

Why do we care that it still looks like Excel? Why do we bother with even the minor aesthetic steps I went through? For that matter, why is a post on formatting spreadsheets (http://ppvt.pro/FormatXL) – one that doesn’t contain the word “PowerPivot” – one of the top 5 all-time most viewed posts on PowerPivotPro.com?

Well, spreadsheets are what we do. They are where our work meets the world. Indeed, those spreadsheets running around are almost synonymous with us: very often, when a consumer is looking at one of them, they are inherently equating the spreadsheet with your contributions to the team. In the consumer’s head, the spreadsheet is you; and since we all know that appearances matter a lot more than they should, we put a lot of effort into formatting. As our spreadsheets are our representatives to the rest of the organization (maybe even to customers and partners), the way our spreadsheets look reflects on us.

That’s an important point to keep in mind. However, we’re going to dig a few steps deeper.

Sharing It with Others

Okay, I’ve got the spreadsheet set up as nicely as I can. Now it’s time to share. So I fire up my email and compose a little message:

Figure 356 Does this look familiar to you? I bet it does.

Wow, look at all that. All those instructions. The do’s and don’ts. Above all, the unmet desires of my poor coworkers who just want the data in a convenient format.

On the Receiving End: Excel = Word = PowerPoint

All right. Now let’s put ourselves in someone else’s shoes: one of our consumers, who receives the spreadsheet. Let’s call him Jim.

Jim comes back to his desk to find these three messages at the top of his inbox:

Figure 357 “Hmm, looks like a few things for me to review.”

Then Jim opens those messages:

Figure 358 Three messages, three coworkers, three Office documents: “Oh great. Attached documents. At least they’re all probably short.”

That word ‘document’ – it covers a lot of ground doesn’t it? Here’s a Word doc that is just edits to a longstanding document, and a slide deck that’s a Frankenstein’s monster of copy/paste from other decks. And sitting right next to both of those is just another Office doc, produced in just another Office app.

This tight psychological association between Word, Excel, and PowerPoint is quite unfortunate, for they are actually very different. Word and PowerPoint documents are containers for static content – stuff that was entered via one method or another (typing, pasting, clicking for formatting, etc.) A well-constructed spreadsheet, by contrast, is an application in itself. Excel Pros, whether we realize it or not, are programmers. We produce logical systems – our spreadsheets are machines that we created.

Excel Pros are the dominant business programmers of the world. We are engineers. Our spreadsheets are, in and of themselves, a form of software – software that happens to be created in an environment known as Excel, and that happens to also run (for consumers) in that same environment.

Yet this close association with the other Office apps, and with the notion of a ‘document’, dumbs that all down in the eyes of the consumer. It’s not their fault: this is just how the human brain digests the unfamiliar – it filters it through the lens of the familiar. Docs are docs. Office apps are Office apps.

Even at Microsoft, on the Office team, people who worked on Word, PowerPoint and / or Outlook really didn’t regard Excel as being any different. (Except the people who worked on Excel – we were definitely regarded as different. That may also sound familiar to you).

From Magical to Mundane

It is clear then that our perceived value is even damaged by the fact that it’s the same Excel for creating docs as it is for consuming docs. Why? Because Excel has a million buttons and control surfaces – ways by which consumers can become confused. By which they can break things.

While we might hope that this complexity would heighten the respect for our skills, consumers’ lack of understanding actually just harms the reputation of Excel; and, by extension, its users. My friend Dick Moffat complained to me recently that “back in the day” someone could call themself an Excel Developer and it was a “cool” thing to say. Being an Excel Pro was a cutting edge profession that could earn you a top-notch living, even as a freelancer. Yet, he lamented, very few people call themselves that today.

Something has clearly eroded over the years. When the spreadsheet first exploded onto the scene, it felt like absolute magic – it came in with the original PC tide, an absolutely revolutionary force.

People have been making pretty spreadsheets since the 1980s, and since the 1990s they’ve been emailing them around. Or saving them to file shares. In other words, treating them just like Word docs.

Throughout that entire timeframe, the notion that Excel = PowerPoint = Word has been pervasive; and constantly reinforced.

Furthermore, the focus of the world, since about 1996, has been rapidly moving away from the desktop, and onto … the internet.

The Web: From Enemy to Opportunity

Siphoning Mindshare Away From Us Since 1996

Part of the problem we face is not an Excel problem – it is a desktop problem, period. Consider the following charts that I ran on Indeed.com’s job trends search:

Figure 359 Job listing trends since 2005 for three different kinds of programmer.

I wish Indeed.com’s data went back farther, but this still shows the point: desktop development isn’t exactly the rage. The only thing cutting into web development as a career is mobile development.

Here’s another:

Figure 360 Excel Analyst versus Web Developer.

Interesting that Excel has been holding steady, and Web Developer didn’t ‘pass’ it until late 2006.

Web Developers dominate the job listings because web applications are what the world demands. Although none of us are ‘desktop developers’ in the sense of this chart, this reflects a broader shift in the way the world does its computing, which does affect us.

Erosion of the desktop’s perceived importance means erosion of Excel’s perceived importance (but not usage). When I arrived at Microsoft in 1996, expense reporting was driven by an Excel template. By 2000, the Excel template was gone, replaced by a pure web-based format.

If Excel can be removed from a process, it has been by now. What’s left is the stuff I talked about in the introduction – people who are embedded in the business, know the business, and are able to directly provide digested data (using Excel).

To view the graph above, and run your own searches like this, visit http://ppvt.pro/XLvsWebDev.

Our Bridge to the Future

Let’s return to the scenario of me emailing around a report, and this time, imagine me sending the following instead:

Figure 361 This feels very different than emailing a document, especially on the receiving end.

Jim opens the mail, and right off the bat he’s more enthusiastic about clicking a link than opening an attachment. The web just feels ‘light.’ Documents that open in desktop apps feel heavy by comparison.

He clicks the link, and sees:

Figure 362 Interactive web application displaying the data.

This is a superior experience in many ways:

• ‘Lighter’ than opening a document, so right from the beginning the consumer feels more engaged, and less taxed.

• Requires no installation – it’s a thing you visit, not something you bring to you (and configure!) before you can use it.

• Works on virtually every operating system – no apologies or disclaimers required in my email.

• Does not suffer from a close association with ‘content containers’ like Word and PowerPoint.

• Feels modern, unlike the 1980s technology of Excel docs.

• Less intimidating and error-prone, since it provides a clean consumer experience, without the clutter and danger of the tools that were used to produce the report.

• Quite simply feels much more legitimate – web-based applications are not the sort of thing the average office worker can produce. They require teams to build. And basically every computing experience that we trust these days is a web experience.

The screenshot above is from a company named Tableau, a company that many consider to be a competitor to Excel and PowerPivot. Why do I include them, of all people? Because I want to clearly highlight what we have lacked.

That is, until now. :-)

Back to My Retail Competitive Spreadsheet

Let’s circle back to my final formatted version:

Figure 363 Best we can do on the desktop.

Then let’s flip over to my browser, where I’m looking at some workbooks stored on a cloud website.

Figure 364 A cloud website where some XLSX files are stored. Note the highlighted “Add Document” link.

I click that Add Document link and am prompted for a file path:

Figure 365 Prompt for file to upload.

I browse to the location where I have saved my retail competitive workbook, then click OK:

Figure 366 Just the usual upload to a web storage location.

And, as expected, my file is now stored up there as well:

Figure 367 Workbook uploaded successfully.

Flip to Consumer View

Up until now I’ve been looking at the ‘behind the scenes’ mode of this website, a place where people like me can put workbooks that we’ve made.

However, this is a very special kind of website, and has a much bigger purpose than just storing workbooks. This website is a PowerPivot server website!

To show you what I mean, let’s look at the homepage of the site; a place that all of my consumers can see, and which is geared toward their needs. The link to the homepage is here:

Figure 368 Link back to the consumer-oriented homepage.

Figure 369 Note the retailer competitive link! Clicking that gives me…

Figure 370 The workbook! But is it just a picture? Let’s click a slicer.

Figure 371 Let’s click Food Lion instead of CVS.

Figure 372 Yes, it is fully interactive. In my browser. (Even though this computer does not have PowerPivot installed!)

I’ll slice it to Whole Foods next:

Figure 373 Note how Walgreens jumps to #2, and Trader Joe’s jumps all the way into the fourth slot.

I can also ‘drill across’ to another detailed report by clicking the hyperlinks on the left, such as the ‘Trader Joe’s’ entry circled in the image above.

Figure 374 This hyperlink is just a HYPERLINK() formula in Excel that references into the pivot, but the consumer doesn’t know that…

Figure 375 Opens a new browser tab and shows me a completely different report, but parameterized to the Retailer I picked on the slicer (Whole Foods) and the Retailer whose hyperlink I clicked (Trader Joe’s).

Want More Evidence?

Here’s another PowerPivot application on that same demo site:

Figure 376 A calendar chart implemented in an Excel PowerPivot workbook.

And here’s that same application displaying on an iPad!

Figure 377 Just open Safari (the iOS browser) on the iPad and navigate to this web application.

What’s in a name?

Notice how I keep using the word ‘application’ instead of ‘workbook’ or ‘spreadsheet?’ That’s how your consumers will think of this, too, as long as you describe it as such from the beginning. Tell them you’ve “built a new PowerPivot application and it’s available on the web to use.” Don’t say that you’ve “uploaded a workbook.” Got it?

To try this application in your own browser, visit http://ppvt.pro/CalChartDemo. To see how it was built in Excel/PowerPivot (and download the original workbook so you can modify it to fit your data), see this post: http://ppvt.pro/CalChartHowTo.

We can now answer the question, ‘what is the one click that will change your life as an Excel Pro?’ It’s the first time you click ‘Upload Document’ on a PowerPivot server.

The net effect is this: you’ve built an interactive, data-driven web application. It is available to whoever you decide (you have to give them access; it is not wide open to the world). It would take a team of programmers weeks (if not months) to deliver something of equivalent quality.

The fact that Excel/PowerPivot was your programming environment is not something you have to keep secret, but as long as you don’t constantly use old-fashioned words like ‘doc’ and ‘spreadsheet’ with your consumers, web delivery shifts perception dramatically in your favor. Do not waste this.

Beyond Perception: Other Server Benefits

• An end to the distribution problem – even just getting workbooks to your consumers is often a lot of work with normal Excel. With the server, you put the workbook one place and then send around a single URL to the application.

• Automatic refresh – you can schedule your applications on the server to refresh themselves with the latest data, without you (or anyone else) having to intervene. When you go on vacation, you can actually be on vacation.

• Security – you can provide users with interactive access to the application but still prevent them from downloading the workbook. Thus your formulas and other sensitive business logic, as well as all the data itself, is safely secured on the server, instead of running around on everyone’s laptop, threatening every day to be leaked.

• One version of the truth – because everyone is always using the server copy, there is no doubt that everyone is using the latest. Nor is there any doubt they are using the same formulas as everyone else. If you’re using the same URL, it’s the same application.

How Do You Get a Server?

Right now there are a few options; more may open up over time.

1. You can buy SharePoint Enterprise Server plus SQL Server BI Edition from Microsoft and install your own server. Unless you have a lot of experience here (and a deep budget), this won’t work for you.

2. You can use Office 365. At the time of writing, this offering is still in beta and not formally available. Nor has anything related to pricing been disclosed. I do know that there are some limitations – files must be no bigger than 10MB, you cannot auto refresh anything except linked tables, and the overall aesthetics still scream ‘spreadsheet.’

3. Third Party Cloud Offerings. I'm not going to promote any particular offerings here but there are a few out there. Also, I might just have something up my sleeve as well. Keep an eye on
http://ppvt.pro/CloudPPV for news.

The future for Excel Pros is bright, and it’s here. Take off in your new jet plane and enjoy the ride.