Sangeeta Nov 24, 2014 1 Comment
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?
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?
Accelerate Excel to help run your spreadsheets 100x faster
Predictive Analytics: Microsoft Excel Paperback by Conrad Carlberg