Analytics with Excel: Number Crunching with this Simple Application

By Gaurav Vohra

Excel is a Swiss army knife of sorts, with its versatility and multipurpose capability. Widely known as a spread sheet application, it is a quite capable tool that can be used for a wide variety of analytics techniques. Unless working with large datasets, Excel can be the go-to tool for smaller organizations, where footing the bill for a complex analysis software can be challenging. While its lack of centralization and consistency hinder its use at scale, brute number crunching and running analytics techniques on not-so-big datasets is the comfort zone that Excel resides at. For non-programmers, Excel’s analytical capabilities are a boon, without which they would have to deal with lines and lines of software code.

Once you master the basics, there are some extremely interesting analytics applications of Excel:

Summarising datasets
Excel can very easily and quickly analyse small and medium datasets to generate key insights, slice and dice the data by various dimensions and across different metrics, create pivot tables to drill down deeper into the dataset and extract insights that can be shared with others.

Basic Charting
The plug-and-play charting options and modules of Excel make it easy to visualize and communicate the data & insights at just the click of a button. The user friendly interface makes it easy to present a story or point with a data backed approach.

Regression
This is where things start to get interesting. Excel’s add-ins can perform some more in-depth analytics, like linear and logistic regressions to analyse the impact of one or more independent variable on a dependent variable. One example of this is when used to understand and model a Marketing Mix Model. These can be performed once you’re ready with your dataset and some common add-ins includes the Analysis Toolpak, XLSTAT and Real Analytics.

Simulation
Excel has the ability to generate random numbers using various distributions. These can be used in simulations and data tables to conduct simulations and use the data for decision support, once the data has been simulated over thousands of iterations easily with Excel.

Excel Solver
Excel has the ability to solve Linear programming problems using the Solver add-in. The solver basically helps you find a solution which, in this case, is a set of values for variables in a problem you’re trying to solve, while adhering to pre-defined constraints and while minimising or maximising a certain objective.

The author is CEO of Jigsaw Academy, The Online School of Analytics.

Disclaimer: This website may include links to third party websites & you maybe redirected there as the user of this website. UNext is not responsible for the content of any third party websites; thus the views and opinions expressed in the publications/ third party websites are those of the authors and do not necessarily reflect the views or positions of any entities UNext represent.