Why every Data Professional should master MS Excel

Whether you are an analytics pundit or a newbie wanting to work with data, you simply cannot ignore the ubiquitous Microsoft Excel.

From being a simple spreadsheet programme containing loads of data, MS Excel has emerged as a BI tool, indispensable for data professionals. Its unlimited potential to work with data and statistical analyses makes it a robust, feature-filled tool that can be leveraged to boost your productivity. With free add-ins and apps to aid you, all you need is to move beyond the basics to VBA programming and third party integration, and unleash the full functionality of MS Excel.

What are these office add-ins? They are essentially plug-ins that come with the Office, now termed Office Apps with the newer versions (2013) that are more connected to the web.  Through add-ins, you can perform many more functions that come bundled with the basic package.

So let’s explore why you need to move beyond the simple formula stuff like Vlookups and Match-Indexes to more sophisticated data and statistical analysis tools using the Excel.

As you are aware, Excel in its native format has a wide array of tools that allow computations with huge data. Native worksheet functions can transpose, multiply and invert matrices to derive their determinants. However, to extract the components you need the right add-ins to work through the steps from raw data to end product.

What does Excel do?

With a given baseline of data, Excel gives you the tools to determine best methods for forecast and use the results to make your own analysis and predictions. So instead of an automated process, you get a platform that you can tailor to your needs in diverse real-world problems –  making it an ideal platform to work with.

Mathematical and statistical functions that calculate regressions statistics, logarithms or matrix multiplications, make possible various analysis. For instance, the basic Excel worksheet functions help you build a basic model for logistic regression. At the same time, applying the Solver add-in to the model, helps extend this to logistic regression analysis. What’s more, you can arrange results as suits you. And mind you, this is barely the beginning!

Why do analysis in Excel?

  • Comes in-built with your Microsoft Office, in other words ‘FREE’.
  • Widely available and popular
  • Does not require mastering new methods of data manipulation
  • Has basic built-in statistics functions and data analysis capabilities
  • Visualisation possible on-the-fly
  • Gives the user more control
  • Comes with plenty of add-on tools and packages, both free and paid
  • Supports external tools / software that work on the Excel environment (like Mapcite) for advanced analysis and wider functionality like mapping, modeling or building BI dashboards

ExcelUseful FREE Excel Add-ins for Data Analysis

Analysis Toolpak – saves time on complex statistical or data analyses, to display results in an output table.

PowerPivot (an Excel 2013 add-in) – performs powerful data analysis from multiple data sources, shares insights and creates sophisticated data models.

Power View – builds multidimensional models that support ad-hoc data analysis and visualization; displays your data in a geographical context (maps) useful for integrated location analytics.

Data Explorer (Excel 2010) / PowerQuery (Excel 2013) – supports easy import of large array of data formats from multiple databases and sources, with various data manipulation functionalities to make data anlaysis-ready; emerging data access and data discovery tool for BI professionals

GeoFlow (Excel 2013) – deciphers big data for deep insights, automatically maps clicks, conversions and revenue, creates 3D visualizations with play button functionality to show trends over time.

Useful for marketers, and those looking to unlock insights within geospatial data

AnalysisTabs – has innovative utilities for sophisticated data analysis and dashboard creation

Now, are you still wondering why MS Excel is the mantra of every statistical and data professional today?

 

Resources:

Accelerate Excel to help run your spreadsheets 100x faster

Predictive Analytics: Microsoft Excel Paperback by Conrad Carlberg

One response to “Why every Data Professional should master MS Excel”

  1. Srinivasan says:

    Good article…meaningful insights on data analysis in MS-excel tool.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.