Talk to people who want a career in Analytics and one of the first questions they will ask is – ‘how can I have a career in Analytics with no access to softwares like SAS, SPSS etc.??  And my answer is – have you looked at excel 2010 and its capabilities w.r.t analytics? If you are like 80% of the population, the answer is ‘no way’. Excel 2010?

Let’s do a quick check of our old Excel in its not so new ‘avatar’ – Excel2010.As a spread sheet , each worksheet can hold 1,048,576 rows of data and 16,384 columns of data, way up from the 60,000 rows and approximate 110 columns in the excel of old.

There are 403 functions and we can now have 255 arguments in a function and nest 64 levels of functions per formula. So, complex customised calculations become easy.  This makes data cleaning and massaging very easy. You have a plethora of text and numeric functions and lots of date related calculations that can be done, which rival softwares like SAS, SPSS etc. Once the data is in the right form for the project, you can add-in the Analysis toolpak. This contains a set of 19 wizard driven statistical processes to use – from Descriptive stats to Annova to Regression. A judicious use of the functions and formulae and the Analysis toolpak will allow you to get nearly all the commonly used outputs for business decision making.

And then the power pivot free download is a great add-in. This increases the BI capabilities of excel multi folds and allows for merging data from various sources and manipulating the data It uses DAX – Data Analysis Expressions – which is a language that enables more complex grouping and calculations and thus, better analysis . (You can read more about this and download it from http://technet.microsoft.com/en-us/library/ff452206.aspx)

On the data visualisation front , the new features of Sparklines – which are graphs in a cell and give a very quick and ready reading of trends – is a very user  and analysis friendly addition . Since these can be used along with pivot tables, the utility is immense. It is very easy to summarise and draw conclusions to segment the reports that you will create.

Recording and using recorded Macros is much more robust in this edition of excel, ensuring that even non- coders (VBA coders) can use Macros for repetitive tasks, formatting and generation of standard codes.  Simplifying and standardising helps you to move to the next level analysis and spend more time in validation and conclusions rather than preparation.

Sounds good? Yes, and feels very good too. Try it out to get a closer look to this old and free application that we have taken for granted.

This use-ability has led to companies building paid add-ins that work on excel and my favourite is XLSTAT. As the name suggests, it simplifies the more complex statistical processes and makes them button driven. So doing a factor analysis, pareto, decision tree, cluster analysis and logistic regression becomes that much more easier.

You, of course, have to remember that excel is a competent tool for analysis but the conceptual understanding has to be built by you. Thus, the success of the project will depend on your knowledge of the subject and ability to use the tool

With an extensive online help and many forums dedicated to it, Excel 2010 has finally ‘arrived’!! I strongly believe in the potential of this software and the easy accessibility just adds to its charms.

So all you potential analysts out there – pull up your boots and get cracking …