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.