Some in the business want to get rid of the spreadsheet. They say it is prone to human error – for example, Excel users can cut and paste the wrong numbers or input the wrong formula. And spreadsheets make it near impossible to get one version of the truth, because copies of the same file proliferate and get changed and updated at different times.
Finance, on the other hand, loves Excel, or at least cannot do without it. In a CFO Innovation Asia survey last year, 74% of respondents said their company uses spreadsheets for cashflow forecasting, 73% use them for budgeting and 59% to generate what-if scenarios. The main reasons cited are ease of use and ease of customization to fit the company’s needs.
What to do? “There might be a need for more training in Excel,” says Bill Jelen, author of several books on Excel. The man known in the US as Mr. Excel
spoke CFO Innovation’s
Cesar Bacani about his favorite productivity tool, including the new Excel 2013. Excerpts:
One perennial complaint about Excel is that it is prone to human error. Do you agree?
Anything can be prone to human error. The nice thing about Excel is that somebody who is knowledgeable about Excel can always go through and see what formulas are being used and can check the logic. Whereas if you use Microsoft Access, a lot of times those queries are in a locked box where no one can see what the logic is.
Errors can happen anywhere, but at least with Excel, we have a great audit trail to follow. Every formula is visible in the formula bar, so it’s easy to trace what’s going on.
I’m just remembering the influential Harvard paper “Growth in a Time of Debt” by Carmen Rinehart and Kenneth Rogoff, which analyzed data going back 200 years on 44 countries. That 2010 research was later claimed to be flawed because of spreadsheet errors.
I hear the statistics that 20% of spreadsheets have errors, but I’m going to put forward that 20% of my spreadsheets do not have errors because I know what’s going on. There might be a need for more training in Excel.
So the key to reduce human error is to be expert in Excel. What will this entail?
You need to be able to work with Excel 20 hours a week. If half your job is in Excel, you become proficient in Excel.
I have statistics from Microsoft that says that of the people who say they “use Excel,” 20% of those people have never done anything more than open Excel and click Print. Another 20% have never done anything more than formatting and column width; they have never entered a formula.
So that’s 40% – 300 million people – who claim they use Excel that have never entered a formula in Excel.
People in finance are generally proficient in Excel. But in the modern enterprise, non-experts like sales people, marketers and operating staff are also expected to input data into Excel files, which is one reason for the errors.
The old way, the one that’s been around forever, is to try and use worksheet protection to make sure they don’t enter things in the wrong cell. The new way to solve this, starting in Excel 2010, is to publish your worksheet to either [Microsoft collaboration software] SharePoint or to the office web app.
When someone is interacting with Excel in a browser, they have very little chances of causing errors in the back-end. We can present in the browser just the cells that they need to change, their changes are instantly put into SharePoint or to SkyDrive [a Windows file sharing site that offers 7 GB of free cloud storage]. There’s much less opportunity for error when we publish on a web app.
And Excel itself will warn the user if the number entered is too big or too small or not in the right format.
Yes, data validation has been a feature in Excel for at least ten years now.
Do people need to enter data in cells at all? Is there a different way to interact with Excel for non-experts?
There are two answers to that. Built-in into Excel is something called ‘data form,’ which allows you to build data on cards. But it’s ugly. It’s very simple for anyone to use it, it’s just so ugly.
It’s also possible for someone who knows how to set up macros in Excel to design a custom form that people can use to enter data. I usually design my own little form that uses colors, the right formatting and things like that.
Because Excel is so easy to reformat and customize, there are worries that there’s no ‘one version of the truth’ across the enterprise.
Here’s the problem that you’re always going to have with the more-than-one-version-of-the- truth. I have a spreadsheet, and that spreadsheet is correct. I send that spreadsheet to five other people, and those five people might start adding records or I might add records and forget to send that back out to the five other people.
One of the downfalls of Excel is that everyone has their own little version. One of the things that Microsoft has done is by storing the Excel file up in SharePoint, everyone can be using the same file. The problem is not that many companies are using SharePoint, and so we’re still stuck with Excel files floating around.
I imagine SharePoint is additional cost.
It is, but it’s not the cost of SharePoint that is bad. It’s the cost of the person that knows how to run SharePoint. You can’t just buy SharePoint; you need to [hire] an administrator to run it and that person is expensive.
In the latest release, Excel 2013, Microsoft came out with a version of SharePoint where Microsoft administers it. You don’t have to hire a person. It’s called SharePoint 365.
I’ve experimented with it. It was interesting. I was able to get my own SharePoint site up and running without having an expensive information technology person on staff. So that might be an option in the future. I think I had to pay US$8 a month.
In Asia, one reason why some companies are sticking with Excel is that it’s essentially free; it comes with Microsoft Office.
That’s right, very little cost and you don’t need to hire a person [to train your staff]; most people have some Excel skills.
What about dealing with the volume of data? I’ve heard people complain about unwieldy Excel files. For example, I imagine the Harvard study’s spreadsheet is massive, as it contains data going back 200 years.
If you’re good at Excel you can navigate to the bottom, to the top using shortcut keys, rather than using Page Down or scrolling. If you understand the shortcut keys, you can deal with that data.
But if you’re a salesperson who needs just one data point before meeting a customer, navigating a massive file possibly can be a problem?
It could. There are many ways to filter that data, but most people who just casually use Excel wouldn’t know how to use those.
This is what software vendors tell me. Why waste your time if you’re a sales person navigating Excel? If you have our solution, we can set it up so there’s a Google map on your mobile device which will show you where the nearest customer is and how much he bought and what he needs now. You click on the map, and off you go. Is that something that you can do with Excel these days?
Actually, you can do all that in Excel, but you need to do some programming in Excel. They have a custom application that does that. It’s going to be more expensive than Excel. The macro language will let you take that address and go out and display it on Bing Maps or Google Maps. It could be done.
I’m told that in some of these packaged software, a sales person can type a query as you would in Google search, and then then answer comes up. In Excel, you need to do things to it before you get your answer.
That’s correct. The person working in finance would design pivot tables and something called ‘slicers’ to filter the data. The sales person opens the file and makes a selection from the tiles, invisible little squares – you choose the region, you choose the product, choose the product line – and it will show you the report.
But that data has to be set up by a business intelligence analyst back in the office before it gets to the sales rep.
I’ve seen some demos from Microsoft research where they’re starting to handle the queries in Excel like a Google search. But that’s not released yet.
With Excel, you get nifty charts and graphs, but in terms of moving images and audio files, that’s not something Excel can do?
What about mobility? Can I access Excel files on SharePoint, for example, on my tablet and smartphone?
It’s clunky. I have Excel running on my iPhone, but the screen is so small it is not fun at all. On the iPad, you have to have the latest version; the original iPad I have does not have the processing power [to handle Excel].
What’s your assessment of Excel 2013? How does it compare with Excel 2010?
It has some features that are really good for people who are completely new to Excel. If you don’t know how to create a chart, they’re going to take a look at your data and choose the right chart type for you. They will make it easier to create a pivot table, without even having you know that it’s a pivot table.
On the other side, for the people in finance, they’ve introduced some new add-ins such as Power Pivot and Power View that allow you to do dashboards and business intelligence using Excel.
So you buy the latest Microsoft Office suite and you get Excel 2013, too?
The answer to this is a horrible answer. The two add-ins I talked about don’t come with Office. You have to buy the standalone version of Excel in order to get those add-ins. That means you buy Microsoft Office [with Excel 2013], but then you have to buy another US$99 to buy the stand-alone to get the add-ins.
Plus the SharePoint subscription.
But you can share via the SkyDrive, if it’s a small file that you want to share with specific users.
If you’re a small company, probably. If you’re an enterprise with offices in 30 countries and you want everyone to access documents and make queries and report information and so on using mobile devices, Excel is probably not the way to do it.
You can look at the new product from Microsoft called Power BI. This is just in public preview right now, but Power BI will allow viewing on mobile devices. The original data is probably generated from Excel. We’ve seen demos of this, but this is not commercially available yet.