FSN Reviews Excel 2010 – no boundaries? No formulas

30th May 2010

Excel 2010 – no boundaries?  No formulas

Simon Hurst, FSN senior writer takes a look at the Excel and collaboration facilities in Microsoft 2010 and finds there is a considerable amount that can be achieved without writing any formulae at all.

If one looks beyond the hype surrounding the launch of Microsoft Office 2010 it should be regarded as a significant event. Microsoft’s video of the Office 2010/SharePoint 2010 global launch keynote certainly shows where the emphasis lies for the new products. The main theme is productivity, but collaboration is seen as an essential component for delivering that productivity, hence the combined launch for the latest versions of Office applications and Microsoft’s collaboration platform, SharePoint.

SharePoint 2010 is designed to allow organisations to share and collaborate on Office documents linked back to the enterprise's back-end data. Web services allow for interactive creation, editing and viewing of complex Excel workbooks for example. These facilities are available within an organisation that has its own SharePoint server, or more widely via a hosted solution.

Some of the collaboration possibilities are not exclusive to those with access to SharePoint. No doubt mindful of the challenge from the cloud, particularly from Google Apps, Microsoft are launching Office Web Apps with Office 2010. The Web Apps will allow access to Microsoft Office documents from within a web browser. If you are expecting the full functionality of Word or Excel in a browser window then you will be disappointed. The technical preview version of the Excel Web App includes most of the Excel Home ribbon and not a lot else. It would allow the editing or creation of a basic spreadsheet including Excel formulas and functions. It would also enable the viewing of workbooks incorporating more advanced features such as conditional formatting and PivotTables. In addition, limited data operations, such as filtering and sorting, are available. Although functionality is very restricted compared to desktop Excel, the Web App does allow multiple users to edit the same workbook with their changes appearing more or less in real time.

The Office Web Apps do not need the workbook to be hosted on SharePoint. Microsoft have added 'SkyDrive' as part of their Windows Live service. You log in to your Windows Live account and access SkyDrive to see folders available to store Office documents. You can create additional folders and new documents and you can also add existing 'local' documents by browsing to their location and choosing to upload them.


The collaboration enhancements are available from within the Office 2010 applications. The File screen now includes a 'Share' option that includes the ability to send your file via email, to save it directly to SkyDrive or to save it SharePoint or publish it to Excel Services. Whether or not you want to collaborate, saving to SkyDrive means you can make your Excel workbook available via the Internet, wherever you happen to be – from sitting on your sofa with a notebook to the 10:30am train from Kings Cross. The Web App ribbon also includes an 'Open in Excel' button. If you have Excel 2010 installed on the computer you are using then, as long as no-one else is editing the workbook, you just need to click on the button to open your SkyDrive-based workbook in normal, desktop Excel.

The new features in Excel 2010 are not limited to improved collaboration tools. For those whose use of Excel involves analysing and reporting on financial data, the latest version includes some of the most significant additional features seen in any version since Office 97.

PivotTables are Excel's Marmite – you either see them as the greatest thing since sliced bread or a sticky, unappetising mess. 2010 extends their usefulness in two ways. For those that know and love them, one of their foremost advantages is interactivity. You can swap things round and set filters to show sub-sets of your data without having to wrestle with a single Excel formula. However, for those less familiar with the workings of PivotTables, coping with multiple criteria and multiple PivotTables could quickly become confusing. The Excel 2010 'Slicers' should help overcome this. One of the best things about Slicers is their simplicity. In essence, they are little more than a formatted list box linked to one or more PivotTables. Even in this respect they do make it clearer to see the criteria that have been set, particularly where more than one item is involved. They make an even bigger difference where more than one criteria field needs to be used. If you set up more than one slicer linked to the same PivotTable, then each slicer reflects the effect of items selected in the other slicers – select a country for example, and the customer slicer will only show as valid selections customers in that country. It's when slicers are linked to more than one PivotTable that you begin to appreciate what they could help you achieve. You could set up combination of PivotTable reports and charts to display key information 'at a glance'. You could then add one or more slicers and link them to all of those reports and charts, enabling a user to be able to focus on the areas of the data important to them simply by selecting the appropriate items within the slicers. No formulas, no macros.

Where slicers cover the interactivity of PivotTables, the new PowerPivot add-in dramatically extends the capabilities of PivotTables to link to data sources. Microsoft are confident that PowerPivot can extend the number of rows of data that you can work with from 1 million to hundreds of millions – dependent on how much memory your computer possesses. It also includes tools to help relate different sources of data to one another in order to allow you to report across all those sources. In addition, it includes PivotTable and PivotGraph generation tools that allow a set of tables and charts to be created, and linked to slicers, as a single operation.

Leaving PivotTables, but sticking with reporting tools, the other major addition to Excel's functionality is the 'Sparklines' feature. Sparklines is something of a misnomer as the Excel feature includes three types of in-cell chart only one of which is a line. Excel has long provided a range of tools to display information graphically. If you use graphics to portray financial information then it's well worth having a look at the works of Edward Tufte, particularly his book 'The Visual Display of Quantitative Information'. As well as many excellent examples and suggestions regarding graphical display, it also sets out the rationale for sparklines. The Excel version of sparklines allows you to select a block of data – say the 12 monthly columns of sales figures for different sales people – and then create in-cell charts, usually in an adjacent column:


The Sparklines ribbon tab allows you to control the type of in-cell chart to use and how to format it, including the marking of high, low and negative points for example.

So, combining the new collaboration features in Excel 2010 with PowerPivot, Slicers and Sparklines, you could produce a graphical analysis of millions of transaction lines, and share it over the Internet, without writing a single formula.

If you're keen to try the latest features for yourself, he full retail release of Office 2010, together with Office Web Apps, is imminent, scheduled for June 15th.