Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel 2013: Up close and personal

by
10th Oct 2012
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst  takes a closer look at some of the potentially useful new features in the latest Excel prototype.

There’s a big hint about the new features Microsoft thinks are the most important in the next version of Excel. The Welcome to Excel workbook that opens when you first start Excel 2013 shows three new tricks: Flash Fill, Quick Analysis and Recommended Charts.

If you make it through these three tricks, the Learn More sheet links to further information and help, including interesting facts such as an indication that, apparently, Microsoft doesn’t think that 3D pie charts are cool any more.

Revised interface

Before looking at these and other new features in any depth, it’s worth considering the overall look of Excel 2013. If anyone was still expecting Microsoft to decide that the ribbon had been a horrible aberration and restore menus and toolbars, they will be disappointed. The ribbon made it through to its third version of Office.

But the ribbon isn’t entirely unscathed. On first acquaintance, it certainly looks rather flat and lacking definition: either “clean” or “bland”, depending on your point of view. But the alteration have the practical disadvantage of making the boundaries between different parts of the interface less obvious.

In all probability, after a few weeks using Office 2013, most users won’t notice the change that much. The pale and flat interface might not be too off-putting, but the sudden appearance of upper case letters for the ribbon tabs and status bar might take a bit more getting used to:

Excel 2013 welcome page

The new approach of animating everything you do will divide opinion too. For example, if you click a cell, the selection box travels from the previously selected cell to the one you clicked.

Flash fill

The example in the Welcome workbook includes a table of email addresses all in a similar format with first and last names separated by full stops. If you type the first name of the first two row emails in an adjacent column, Excel will recognise the pattern and fill the rest of the column with the names preceding each full stop. Unless I’ve failed to understand the feature completely, it seems to be of limited use. It doesn’t create a formula, so if you change the original cell the adjacent cell remains unchanged and as you add rows to the table, the data manipulation is not applied to new rows. It probably has its uses for helping to sort out badly formatted data as a one-time operation, but lacks the automation of the use of formulae. However, judging by the comments on the Internet, everyone else thinks it’s marvellous…

Quick Analysis

This feature is more deserving of its place in the top three. The idea of Excel helping suggest ways of analysing your data might dredge up deeply repressed memories of the infamous paper clip (or the infinitely preferable PowerPup).

Fortunately, this feature is far less intrusive. If you select an entire table of data, a reasonably restrained button appears at the bottom right hand corner, which is easy enough to ignore if you don’t want to use the feature.

On the other hand, if you do click the button it provides some useful options for adding value to the data through the use of FORMATTING, CHARTS, TOTALS, TABLES or SPARKLINES (see what I mean about those upper case letters). Each of the options displays a live preview, and you can combine multiple options.

Generally, Quick Analysis does a decent job of making users aware of the many options available for making data more informative. But there is room for improvement. For example, the sparklines option creates a set of sparklines in the adjacent cells, but it doesn’t create the sparklines within an additional table column, meaning that new rows won’t automatically have the sparkline added. That said, all you have to do is add a heading above the sparklines to achieve this:

Excel 2013 Quick Analysis

Recommended Charts

This feature has been implemented in a sensible way. Once again, the option doesn’t leap out at you paperclip-like, when you least expect and least want it, but instead sits quietly in the Charts group of the Insert ribbon without interfering with all the existing chart types.

Although not revolutionary, Excel’s choice of charts seems sensible. If you select a range with a single series of data, then a (2D) pie chart will be included together with simple bar and column charts as long as you only have a few data points. Once you extend the range to include more data points then the pie chart disappears from the recommended list.

One of the best aspects of the Recommended Charts feature is that Microsoft accepts that you might know better than it does and includes a tab on the Recommend Charts menu giving access to the full range of charts. Even better, selecting any of the chart types displays a preview of that chart with the selected data. Even if you’ve gone for a recommended chart originally, it’s very easy to override Microsoft’s suggestion.

Excel 2013 Recommended Charts feature

Other features

Aside from Microsoft’s choice of headline features there are many other changes, ranging from the ability to show Excel windows on multiple monitors to the incorporation of PowerPivot technology into Excel itself.

For accountants and auditors, one of the most radical developments could be the programmers’ response to the well documented risks associated with spreadsheet use. Excel 2013 includes two major new features to address this issue. ‘Discovery and Risk Assessment’ is (to quote Excel help) “a server application that works with Audit and Control Management Server”.

The help system explains that it can help find problems such as: “hidden cells, hidden worksheets, errors in formulas, broken links, invalid entries”. Microsoft cites the example of someone overtyping a formula in a shared workbook with a value – Discovery and Risk Assessment identifies the workbook and the error enabling swift correction.

Although this option is aimed at larger organisations, the Inquire add-in, available as part of the Professional Plus edition is designed for all users and includes some really useful features – such as the ability to compare workbooks and generate a Workbook Analysis Report. Will you be brave enough to run it?

Excel 2013 Workbook Analysis Report

If you are not yet convinced, the next instalment of this short Excel 2013 preview series will look in detail at some of these other changes as well as considering the different methods of accessing the new edition of  Excel – including the Office 365 rental model. 

Tags:

Replies (8)

Please login or register to join the discussion.

ghm
By TaxTeddy
10th Oct 2012 16:50

21st Century spreadheet

All credit to Microsoft for pushing back the boundaries. Funny thing is, I find myself drawn more and more to that old workhorse OpenOffice.

Thanks (1)
avatar
By Trevor Scott
11th Oct 2012 10:58

I’ve been using the trial licenced version of Excel & Word 2013

Visually/cosmetically it looks great, is easy to use, is stable software, isn’t demanding of my 4 year old PC. Only because I receive some sheets and documents that use its more sophisticated abilities, do I feel the need to pay out hard money for each “new” product. I personally could easily get away with using Excel and Word 95.  

Thanks (0)
ghm
By TaxTeddy
11th Oct 2012 15:48

Me too

I bet I only use 20% of what the spreadsheets are capable of .... if that.

 

http://www.cambridgetax.co.uk/ctp/Home.html

Thanks (0)
avatar
By duncanphilpstate
12th Oct 2012 16:38

Moving further away from the experienced or power user?

Sounds to me like a further move by Microsoft towards the simplistic user to the detriment of knowledgeable power users. I'm not impressed by the sound of any of these features, and I can't see why they need to fiddle with the Ribbon.

Fortunately(?) at work we're still on 2007 with no announced date to move to 2010, let along 2013, so I'll probably be retired before I have to cope with it.

Now where's that OpenOffice installer gone?

Thanks (0)
avatar
By paulwakefield1
12th Oct 2012 17:40

There look to be some useful new features

though I do find the new look hard to like (especially the "shouty" tabs").

I'll be interested to see how good the spreadsaheet auditing and comparison tools are. My third party versions, especially the spreadsheet comparison tool, will be hard to beat.

A word of warning for anybody uninstalling the Beta version of XL 2013. It doesn't do a very good job of cleaning uop the registry. It's unlikely to casue any problems but certain add-ins (such as FastExcel) can throw up errors. Scan the registry for any Office15 entries left behind. Usual caveats about the dangers of playing with the registry apply!

Thanks (0)
avatar
By Cantona1
17th Oct 2012 14:03

Simon,

Thanks for good article!

when you said "incorporation of PowerPivot technology into Excel itself!

How do these features differ from Excel 2010 power pivot?

Thanks (0)
Simon Hurst
By Simon Hurst
17th Oct 2012 21:41

PowerPivot
Hi Cantona1 - thanks for the comment about the article - much appreciated.

The areas I had in mind when making that comment were the new 'data model' feature together with Relationships. You can link an Excel 2013 workbook to muliple external data sources and add them to the 'data model'. The Relationships feature is very similar to the Create Relationship option within the PowerPivot window and allows you to say which fields link tables to fields in other tables within the data model. I'm working my way through the new features in Excel 2013 and will hopefully cover this subject in more detail in the near future.

Thanks (0)
avatar
By AndyOwl
31st Jan 2013 09:24

What's new ...

I've just linked to your Inquire article, which was useful as I haven't got Professional Plus yet, so can't run it.  Link is from long blog summarising all new features of Excel 2013.  Also, let me know if you ever publish guest articles.

Thanks (0)