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 to manufacturing in April, 2010 and it became available for purchase in 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 they were not well-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 in order to make them 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 Diploma Training Course
This Tutorial content is an extract from our Excel and financial modelling training course ‘Excel Diploms’. For more information about how this course can enable your Excel skills and make you more confident in financial modelling and analysis in Excel.
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. On the other hand, in Excel 2010, the number of data points is limited by available memory, rather than by a fixed number. Thus, Excel 2010 also 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 regarding charts is 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 inbuilt feature helping users create dense visualisations of their data.
Macro recording and chart elements
Another new feature that was 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 whilst 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 is 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). Thus, 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, being used 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 changes that occurred 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 will no longer be available. Instead, they will both be replaced by a Function Wizard which includes SUMIF and SUMIFS functions.
Excel 2010 – More Powerful Data Analytics
Common tasks when dealing with data 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, thus 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 articles and tutorials related to the new features in Excel 2010.