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

Posted in ARTICLES | Tagged , , , , | Leave a comment

Why Visual Analytics is Trending

 

Tableau

It is all over the place. Analytics has gone ‘visual’ in a big way.

So what’s with Visual Analytics, the actively trending analytic topic of 2014?

Visual Analytics is a science.

Visual analytics eqnWhy Visual Analytics?

As real-world problems become more complex, human intelligence becomes a part of the data analysis process at an early stage. Background or domain knowledge needs to be combined with varied and voluminous data sources for insight into the complex problems. Sophisticated visual interfaces, allow users to exploit the data analysis capabilities of the computing model, to make well-informed decisions.

A chart or a dashboard shows results. However, a software that embeds visual analytics allows a story to unfold, and for the user to analyse and make queries. It leverages the visual perception of the user with the power of machine data analytics to derive actionable insights from huge amounts of data.

Visual analytics process

 The Visual Analytics process

 Is it any wonder that ‘Visual Analytics’ is becoming the fastest way for exploring, understanding, collaborating and analyzing data?

The Visual Analytics Software

While dashboards have always been a part of the analytic process, proprietary software like the Tableau have now rendered Visual Analytics the trendiest analytics must-haves for every firm.

So what’s so different from your Excel bar graph or Adobe Photoshop or Adobe Illustrator visualization that you have always been using?

Well, if you need data visualization capability that supports Big Data, can crunch data from multiple database sources, or run in the cloud or server – what you need is software that is intuitive, user-friendly and tells the story visually.

Step in ‘Visual Analytics software’ – the new-age code-free software that can be used by people of all skill levels. It answers questions and tells stories, unifies tables, graphs, charts, maps, time series and in a compelling interactive interface.

What the visual analytics software does:

  • embedded advanced analytics
  • ability to analyze all data –  regardless of size, location or format.
  • user-driven data blending
  • work with data from disparate sources (Salesforce, Google, on-site database, spreadsheets, etc)
  • live and interactive
  • supports desktop, hosted and cloud versions
  • on-the-fly storytelling of your data
  • is inter-disciplinary

Popular Visual Analytics Software

Tableau Public is a free data visualization software. It allows you to create visualisations and publish them on Web. You can customize labels, tool tips, interactive filters and legend displays.

Inkscape is free open-source user –friendly vector data visualisation software.

Google Public Data helps you transform public data into a simple visualisation.

SAS Visual Analytics is a proprietary enterprise use software.

Plot.io is web-based visualization software

Stat Silk is another data visualisation software that allows access to world data for customized presentation.

Many Eyes also allows you to use your own data for visualisation.

Examples of Application

  • Climate monitoring / Hurricane paths
  • Emergency response / Disaster management
  • Traffic analysis
  • Social network analysis
  • Location analytics
  • Healthcare – single patient / cohort analysis
  • Finance Stability monitoring of Govt. department
Posted in ARTICLES | Tagged , , , , | Leave a comment

Protected: Urgent Opening For Statistical Analyst – India

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urgent Opening For DATA ANALYST Fresher – Kurla

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urgent Opening For Data Analyst – Marketing Analytics – Bangalore

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urgent Opening For Data Analyst – Bangalore

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urgent Opening For Business Analyst – Bangalore

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urgent Opening For Analyst – Mumbai, Maharashtra

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urjent Opening For Analyst – SAS, Excel, VBA -New Delhi

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.

Protected: Urjent Opening For Analyst – Gurgaon

This content is password protected. To view it please enter your password below:

Posted in DATA ANALYTICS (KPO), JOB ALERTS | Enter your password to view comments.