X
Business
Home Business Enterprise Software

You've been using Excel wrong all along (and that's OK)

Microsoft doesn't mind if you stop using Excel for the wrong things, as it's been building replacements to compete with the tools trying to replace Excel for doing things it wasn't designed for.
Written by Mary Branscombe, Contributor

Video: Add a drop-down list to a cell in Microsoft Excel

The discussion about whether finance departments are moving away from Excel or remain perfectly happy with it should really have been framed more along the lines of whether they should stop using Excel for the wrong things.

EXECUTIVE GUIDE

Ignore the common but simplistic 'just use Google Docs' response. If you only need the basics, Google Sheets will give you that, but so will Excel on the iPad and the web -- and that's so far away from what enterprises use Excel for that it's a tribute to the breadth of Excel that the same product competes with so many alternatives.

In fact, that's both Excel's strength and its biggest weakness. Whatever you want to do, you can probably do it in Excel, even if there are better options for each specific alternative -- but there's virtually nothing else that can do all of it, and certainly nothing else that's on pretty much every corporate desktop, and already paid for. Excel is one of the most requested skills in job postings, so you need to know how to use it -- and how not to.

For one thing, the biggest competition for Excel continues to be the older version of Excel that you already have, probably find annoying and are using to judge the newer version by because you paid for a perpetual licence. Looking for something like Excel that has co-editing and an app store? Try Excel!

Download: Build your Excel skills with these 10 power tips (free PDF)

Excel is great for a quick chart. But if you want a more complex visualisation? The Power Query tools from Excel are moving over to Power BI; the desktop tools are already in the Power BI Desktop software and key parts of them are already in the Power BI service, which has a rather better way of sharing data with colleagues than mailing around spreadsheets.

Incidentally, thanks to co-editing and document sharing sites like Box and OneDrive for Business, you don't need to send attachments and then laboriously combine the edits, you can get people to make their changes all in the same spreadsheet.

Yes, that means changing the way things are done. Debates about replacing Excel are really more about business processes that haven't kept up with the range of tools available.

See also: Windows spotlight: 30 tips and tricks for power users

There are plenty of things you shouldn't do in Excel, even though you can. I would love it if I never had to fill in another timesheet in an Excel spreadsheet with formulae to make sure the dates are in the right year that only work when the month and date are in the US order.

Excel is not a database, although it's universally used as one. If you're doing that, FileMaker Pro is a much better bet, with some good options for creating mobile apps. If you need something specific, low-code tools like Power Apps and Salesforce Lightning are the new Excel macros. Power users can create what they need without waiting for an IT team or a business analyst to get around to it. Smart IT departments will keep an eye on that and take over and support the apps that start getting used widely -- otherwise you're just moving your Excel macro problem to another tool.

Complicated Excel macros and formulas make up the spreadsheets that drive so many businesses that Microsoft has long offered tools to find and centralise Excel spreadsheets. But they too should migrate elsewhere for production -- even if Excel will likely always be the best way to experiment with and develop them.

Tech Pro Research

Excel is not an ERP system (although I do appreciate the 'Excel Runs Production' version of that acronym). An ERP system should be your single source of truth about the state of enterprise resources and you don't want that to be compiled by some poor team merging Excel spreadsheets.

If you can't face the expensive and complex options traditional in this space, cloud options like Netsuite, Dynamics ERP, and Salesforce ERP give even smaller companies a centralised system (and there are finance specific options like Workday too). But whatever you pick, this isn't about the tool you use as much as having a business process that makes sure the information flows between systems effectively. Logic Apps -- the developer-oriented version of Power Apps that works with Azure Functions -- is a good way to build custom workflows to deal with the five percent of your business that's different from how every other company does things. Or you could build an app on top of Excel that puts guard rails around what people can do in the specific workflow you care about.

Often, Excel gets used as the interchange between systems because those systems can always export and import CSV files. Instead of leaving it to an error-prone human to do the export, merge and import, invest in automating that. Power BI can connect to so many different systems and services that you're probably exporting CSV files from that it's the ideal option for getting an overview from multiple data sources. If you don't need that, a proper ETL process (extract, transform, load) beats manual file exchange.

Similarly, handing copies of production databases full of customer information to developers as Excel files they can use when building applications is a great way to have a data breach. Use role-based access control and data masking tools so the developers can connect to the database itself for testing; you can do that directly with SQL Server or unify a more complex database environment with Delphix's data virtualisation.

Excel is a great place to clean up data and hammer it into shape before you use it in another system. The most recent versions of Excel have tools from Microsoft Research to make that easier; from the Get and Transform tools that import data from almost anywhere more cleanly than copying and pasting, to calculated columns that can work out the pattern you use to create a summary field and fill it in for all your rows at once.

The same feature is in the new Azure Machine Learning Workbench software, and the Power BI Desktop query editor has even better tools for splitting and combining columns and making your data usable, if you want to clean up your data where you're actually going to use it.

But even with all of these tools, people are going to keep using Excel - and misusing it for things it's not ideal for. I once used it to create the scripts that built the channel I ran on AOL, which had its own, unique scripting language; combining autofill to generate unique page names with dropdown lists and an export macro made programming it literally drag and drop. The beauty of Excel is that you can do that. And it's no bad thing when companies move on from the DIY option to something designed for the job, because we'll just be using Excel to work out how to arrange another set of information for the next thing we need to do, long before the perfect tool to do that gets invented.

And when we finally move off Excel to that new tool, I predict we'll still be using Excel for whatever the next thing after that is, as well as all the old things too.

Recent and related coverage

Excel errors: How Microsoft's spreadsheet may be hazardous to your health

Millions of Excel spreadsheets are used in medicine, science, economics, and finance. Yet up to 90 percent have serious -- even life threatening -- errors. Here's what you need to know and how they could improve Excel.

Enterprise software slows down digital transformation, survey suggests

'In many companies with legacy enterprise applications, ERP still stands for Excel Runs Production.'

Read more on Microsoft

Editorial standards
Show Comments

Related

fist-keyboard-gettyimages-540980164

Microsoft is annoying users again. Here's what it could do instead

stack-social-microsoft-33

Buy Microsoft Office Professional for Windows for $56: Last chance

stack-social-microsoft

Get Microsoft Office for Windows or Mac for $32 right now: Last chance