Microsoft Excel 2013 review

Pros

  • The most powerful spreadsheet app
  • Auto-suggestions for creating charts
  • New time saving Flash Fill feature

Cons

  • Not the best for advanced stats analysis
  • A few remaining inelegancies

Excel is the second-most widely used productivity app in the world, and it's second only to Microsoft Word. If you use Excel every day, but you don't need Word or Outlook or PowerPoint or the rest of the enormous toolbox that makes up Office 2013, you don't need to buy the whole Office suite.

A long-standing but little known option makes it possible to buy Excel alone. Just visit Microsoft's Office store, scroll down until you find the tiny icons that let you buy the Office apps separately, and click on the icon to purchase Excel 2013 for £109.99. Just don't ask why Microsoft chose that price, because Microsoft isn't saying. It's a strange price, but for all the power the app offers, it's an excellent deal.

Excel 2013 deserves a longer and deeper look than we had room for in our write-up of the full Office 2013 suite, partly because Microsoft seems to have packed more new features and conveniences into Excel 2013 than into any of the other apps in the suite. Some of these new features add functions that Excel never had before, but most of them make it effortless to use features that took a lot of time, trouble, and expertise to master in earlier versions.

What's new

Some of the new features are obvious, such as the way Excel now opens multiple worksheets in separate Excel windows, each with its own ribbon interface, instead of as separate panes in a single Excel window sharing one ribbon. This makes it easy to manage different worksheets in a dual-monitor setup, while also bringing Excel into line with Word, which has used separate windows for separate documents for ages.

Other new features are under the hood, such as fifty new functions for use in formulas, including one that converts strings to numbers in a customisable way, so that "15%" appears as "0.15" without requiring a trip to the "Format cell" dialog to change a cell's appearance.

Further new tweaks streamline existing features, making it surprisingly easy for beginners to perform tasks that used to be limited to experts. When you select a block of data, a Quick Analysis icon appears at the lower right of the selection. Click on it, and Excel displays a gallery of suggested formatting, charts, totals, and much more. For example, as you move through the suggested choices, Excel displays a row or column of totals, running totals, averages, and other calculations based on the selected data.

Quick Analysis also suggests suitable charts, or custom formatting that colour-codes the data, or displays icons in each cell indicating whether the number is greater or less than the preceding cell.

The same gallery also suggests possible pivot tables for custom views of the data, making this feature more accessible than ever.

All these various options were (and still are) available from the Ribbon if you had the knowledge and patience to find them, but now Excel goes out of its way to offer them. By the way, keyboard aficionados will be glad to know that the Quick Analysis gallery, like everything else in Excel, can be opened with a keyboard shortcut, in this case Ctrl-Q.

My favourite new feature, which saves a tremendous amount of time and effort, is called Flash Fill, and it's one of many features where Excel acts using its brain, not just its raw number-crunching power. As an example, say you have a column of first names and a column of last names, and you want a single column containing cells with a last name followed by a comma, then a first name. I used to accomplish this by copying the names into Word, combining them there by replacing tabs with commas, and then copying the results back into Excel.

Now, all I need to do is go to the top row of the columns of names, containing, for example, "Arthur" and "Andersen," find an empty cell on that row, and enter "Andersen, Arthur". Then I start typing a similar combination of names on the next cell down, corresponding to the names in the second row, and Excel fills in that cell, and the whole rest of the column, with the combined names that I want. The filled-in data appears in grey until I click on an icon that invites me to confirm that I got the data I want.

You can use the same trick in reverse, too, extracting the first or last word from cells that contain multiple words, instead of combining multiple words into one cell. With some experimentation, you may find that Flash Fill is smarter than you expect. For example, if you have a column of dates such as "2012, 1995, 1987, 1990" and you enter "2000s, 1990s" in the column next to them, Excel will instantly suggest “1980s” to continue the series correctly.

What's under the hood

Some of Excel's best new features aren't visible in Excel itself because they exist only on the web. One especially nifty feature lets you add a view-in-Excel button to almost any table that you want to include on a web page. This can be a web page on your own site or a blog or anywhere else. All you need to do is to visit Microsoft's site, click a few buttons to get the two chunks of HTML code that you need, and then paste that code above and below a table in the web page.

When you upload the modified page to your website, anyone who views it will find an "Excel Interactive View" button above the table. When a visitor clicks on the button, an active view of the table opens at the top of the browser window, complete with charting options and pivot-table-style filters. Another button causes the table to open in the browser in the full Excel Web App interface – essentially a subset of Excel itself that runs in a browser. You don't even need to own a copy of Excel to use this feature, and it gives you a taste of what you'll get if you do make the purchase.

Other web-based features – which you'll need a full copy of Excel to use – include the ability to specify which parts of a worksheet will be visible or editable when you post the whole file online so that it can be viewed in the Excel Web App. (A whole worksheet viewed in the browser is of course different from a simple HTML table on a web page viewed in Excel Interactive View).

Another web-based feature that requires a full desktop-based copy of Excel is real-time collaboration on worksheets stored on Microsoft's cloud-based SkyDrive service (available free with 7GB of storage to anyone with a Microsoft account, with 20GB added if you subscribe to the Office 365 service that installs a full copy of Office 2013 on up to five computers).

Multiple collaborators can open the worksheet in their desktop copies of Excel, or in the Excel Web App, and edit different cells at the same time – with one major restriction: All the collaborators who edit the worksheet at the same time must be using either desktop Excel or the Excel Web App. Real-time collaboration won't work if some people are trying to use the Web App and others the desktop app at the same time. Other sharing features let you post a link on Facebook, LinkedIn, or Twitter, which opens a SkyDrive-stored, editable worksheet.

Add-ons

Excel 2013, like Word 2013 and Outlook 2013, supports plug-in modules that you can download from Microsoft's Office store. These are a mixed blessing. For example, Microsoft's answer to the real-time stock quotes available in Google Drive is a Bing Finance plug-in which works well with US-traded securities, but doesn't recognise many foreign firms and has limited options for creating a table of symbols and prices. When I inserted a table and then tried to remove one of its columns, Excel instantly crashed. After fifteen minutes, I clicked Cancel on the message box that told me that Excel was trying to recover my data, and had to close Excel from the Windows Task Manager. This was one of the worst app crashes I've experienced in a long time.

Knowing Microsoft, I'm sure they'll get this right eventually, but meanwhile, if you want automatically updated stock prices in a worksheet, you're better off with Google Docs, which makes this task almost effortless. Or indeed for desktop apps, there's the Quattro Pro worksheet included in Corel's WordPerfect Office or (where it's a bit harder to implement) the open source LibreOffice suite.

Now that Excel includes the terrific new Flash Fill feature I have only one major complaint about the app. The Formulas tab on the Ribbon includes buttons labelled Trace Precedents and Trace Descendants – these buttons tell Excel to draw arrows pointing to or from the current cell, and indicating other cells on the same sheet from which the current cell got its data, or other cells that use the data in the current cell.

If the linked data is on another sheet or another file, then the arrow points only to a tiny worksheet icon. If you click on the arrow which points to this icon, Excel displays a GoTo box that lets you jump to the other sheet – but when you do, you leave the sheet you started from. This made sense when screens were small and memory was limited, but everyone now has enough RAM and screen real estate to let Excel open a pane or window with an editable view of the linked sheet, without closing the sheet you started from. I'm surprised Microsoft hasn't provided this feature already.

Verdict

After almost thirty years of development, Excel has almost every feature you can imagine even if they're not yet perfectly implemented. That doesn't mean it's the last word in data analysis. If you're a scientist or someone working in quantitative finance who uses enormous data sets or needs unlimited fine-tuning in your charts, with access to every imaginable statistical tool, you'll need to roll up your sleeves and learn how to write scripts in the open source R language.

For graphics professionals, or users who want the prettiest possible tables, Apple's Mac-only Numbers app has options to create whole worksheets in boxes on a canvas, instead of each worksheet filling a separate page as in Excel and all other worksheet software.

The vast majority of users, however, will be happy with Excel, and Excel 2013 outclasses every other worksheet app in almost every way. It keeps getting more powerful, and it keeps getting easier to use, and you'll need good reasons to use anything else. As a result, we’re happy to pin a Best Buy award on Microsoft’s application.