Microsoft Excel – Spreadsheet to Business Intelligence

Hi Friends, we have been using Microsoft Office for our different purposes both at home and office. Though Excel made its entry as a substitute to the legacy spreadsheet applications, such as Lotus 1-2-3, it went a long way with versatility and user-friendliness enabling higher up data analysis.

At its core, Excel is widely used for data entry. From the novice users to Excel Pros, there is a large toolset that is available in the current versions of Excel.

Excel – Primary Data Tools

Let us start examining the basic tools that Excel provided for data processing –

• Data Ranges
• Data Tables
• Charts

You can enter data in rows and columns in a worksheet in an Excel workbook. You can either work on that data, considering it as a data range or convert it to a table for more sophisticated operations.

You can visualize the data patterns using Charts, and Excel has a Recommended Charts option that suggests you the appropriate chart types based on your data.

Next Level of Tools

When you have large data sets, you might want to aggregate and summarize the data. For this purpose, Excel introduced -

• PivotTables
• Pivot Charts

PivotTables, help you examine the different facets of the data and generate specific reports. Further, Excel allows you to change the PivotTables dynamically that facilitates portraying the data results during presentations and changing them on the fly to answer the questions that are raised.

Pivot Charts is a cousin of PivotTables and portrays the data in chart forms instead of tables.

VLOOKUP and HLOOKUP

Excel has 500+ built-in functions that enable you to perform the required operations on the raw data to produce the desired results. Of these, the LOOKUP functions got a wide usage. If your data is in two tables, and if you need to combine the data from both the tables, VLOOKUP function comes handy. For e.g. if you have a Products table and a Sales table, you can obtain the sales data for a particular product with VLOOKUP.

Though VLOOKUP was the most sought after Excel function, it has its drawbacks, which have been solved with the introduction of other tools in Excel.

Data Analysis Tools in Excel

This is where Excel began its superior performance. Excel crossed its spreadsheet boundaries by a realm of data analysis add-ins. You can find the following data analysis tools handy if your data sets are not too huge.

• What-if Analysis
• Forecasting (predicting data trends)
• Analysis Toolpak (for statistical analysis)
• Solver (for optimization and equation solving)

Big Data Evolution

Excel Pros were quite satisfied with the data analysis tools that are available, restraining to single tables of data, spanning around some hundreds of rows.

Then, Big Data has become a buzz word in the industry and anything and everything related to data started being called Big Data.

Let us pause here to understand the background of this commotion. The data sources have become vast – from a simple text message to databases containing millions of rows of data. Data is available through the web. The top managers of the companies have realized that if their decision making is based on this availability of data from various sources, not only the decisions would be fruitful, but also, they can have purview of data trends so that they can take immediate actions when necessary. This has raised the expectations of the management from the data analysts.

Data Science is the most highly paid domain now and several tools catering the needs of data analysts came into existence. But, if we observe closely, most of these tools are more into reporting with various visualizations and dashboards.

Yes, reporting is one of the tasks of a data analyst. But, before reporting, the data is to be analyzed and the key data insights are to be brought out so as to enable the decision makers focus on the appropriate and relevant information. Further, as the data is obtained from several sources, the data in its raw format might require cleaning and shaping before it is subjected to analysis.

So, the order is – Data Cleaning, Shaping, Analysis, and Reporting. This is where Microsoft paved its way through Excel.

Excel Power Tools

Microsoft introduced the Power Tools to handle the data analysis for decision making.

• Power Pivot
• Power Query
• Power View
• Power Map

Microsoft also came up with a standalone tool for Business Intelligence – Power BI.

I will cover more details on Power Tools in the next blog.