Excel 2010 comes with a number of key improvements – high performance computing, increased row limits, improved statistical functions, and more powerful data analytics with PowerPivot and Slicer.
Microsoft Office 2010 was released in April 2010, and was available for purchase from June 2010.
We have compiled a list of the key improvements implemented in Excel 2010.
Excel 2010 – more powerful computing
High performance computing (HPC)
Sometimes, we need to construct models which involve heavy data processing, or run mathematical simulations (such as the Monte Carlo methods used in probabilistic financial models and portfolio evaluation). Such models were problematic to run in Excel 2007 (and prior versions) because these versions were not designed to deal with computationally intensive operations.
Excel 2010 deals with this problem through high performance computing (HPC), enabling Excel to perform calculations in parallel. Note: Since this requires the use of ‘clusters’, you may have to modify your existing workbooks for them to be able to work in this way.
Increased row/column limits
Similar to Excel 2007, Excel 2010 came with increased limits on the number of rows and columns, thus enhancing its overall capacity.
Excel 2010 – better charts
Larger data series
Models with large data sets require us to build charts with a correspondingly large number of data points. In Excel 2007, the number of data points in a data series was strictly capped at 32,000 points. In Excel 2010, the number of data points is limited by available memory, rather than by a fixed number. Thus, Excel 2010 enables users to increase the number of data points used in a data series when constructing a chart.
Sparklines (in-cell charts)
Another element to consider with regards to charts are the appearance and size of the actual charts. A long-awaited feature was in-cell charts, referring to very small charts located in single cells. With Sparklines, Excel 2010 has this as an in-built feature, helping users create dense visualisations of their data.
Macro recording and chart elements
Another feature added to make it easier to work with charts is the ability to record macros for chart elements. In Excel 2007, no code would be produced if you were trying to record a macro while formatting a chart. Excel 2010 solves this problem, by allowing the macro to record formatting changes to Excel charts. This is useful in automating the process of formatting charts and other objects.
Excel 2010 – improved functions and algorithms
Asynchronous user-defined functions
Unlike Excel 2007, Excel 2010 allows you to write asynchronous user-defined functions, which are useful when you need to run functions that are not processor-intensive, and where the result does not have to be returned immediately (as is the case with synchronous functions). Consequently, Excel 2010 allows you to break up a user-defined function into a synchronous and an asynchronous part.
A new version of Solver
The Solver add-in had been provided in Excel 2003 and Excel 2007, for optimisation tasks. Excel 2010 features a new Evolutionary Solver, which brings in enhancements in the linear/nonlinear optimisation methods and more optimisation options.
New and improved statistical functions
One of the modifications in Excel 2010 was the implementation of new algorithms in order to improve the accuracy of mathematical and statistical functions – for example, functions used in working with statistical distributions. Aside from improving existing functions, the function library itself was extended through the addition of new functions.
New Function Wizard
Excel 2007 included the ‘Conditional Sum Wizard’ and the ‘Lookup Wizard’. In Excel 2010, formulas that were created with these wizards will still work, but the legacy Conditional Sum Wizard Add-in and the legacy Lookup Wizard Add-in are no longer available. Instead, they are replaced by a Function Wizard, which includes SUMIF and SUMIFS functions.
Excel 2010 – more powerful data analytics
Common data tasks often involve filtering and grouping, and creating interactive and dynamic reports. In Excel 2007, we had PivotTables to perform these types of tasks. Excel 2010 introduced slicers which make these tasks even easier.
Furthermore, Excel 2010 brings in another feature – the Microsoft SQL Server PowerPivot for Excel Add-in, which enables you to quickly combine data from multiple, separate sources and summarise that data. A major benefit is the fast response time, even when working with millions of rows.
Downsides with Excel 2010
Unwanted conversion of graphics, so graphics from Excel 2003 may be rendered differently in Excel 2010
The traditional menu was replaced by the ribbon, requiring users to adapt to a revised layout for shortcuts and commands
Further information on Excel 2010
The following links go to third-party articles and tutorials related to the new features in Excel 2010.