Data Analysis and Interpretation made easy with Excel

Data analysis is the process of identifying, processing and modeling data with the sole purpose of extracting factors that aids in decision making. Every business sector today requires data interpretation in some form or the other. Microsoft Excel provides simple and easy to use functions to help you correctly analyze and present the data in ways that help get meaningful conclusions from the data. In this tutorial, we explore the different ways you can use Excel to analyse and interpret your data.

Qualitative vs Quantitative Data

To start the process of data analysis, the first step is to correctly classify and store your data. Data may be classified into two main forms:

Quantitative Data

Data that can be represented in the form of a number is known as quantitative data. This type of data is used for statistical analysis and gives you a greater level of accuracy in your final results. For example, if you want to classify laptops into four colors namely red, black, green and violet, you can use the quantitative data model and count the number of features available under every color. A numeric variable is computed and more complex and widely distributed statistical formulas can be created using this mode of analysis.

Qualitative Data

Data that cannot be represented in the form of a number but rather expressed as other variables like meanings of ambiguous words, gender classification, religious preferences etc are known as qualitative data. This type of data cannot be used as a statistical unit of measurement and hence accuracy is often undetermined. Taking the above example, complete description of each of the model falls under the qualitative data.

Step1 : Data Gathering

Excel allows you to gather data like measurement of distance and weight or simple polls to choose the best performing car. Auto Fill function automatically allows you to fill in the data in the cells as per the pattern previously followed in the other cells.

For example, the table below lists 3 columns of data namely company name, country name and meeting day.

Here, let’s fill the columns “Country” and “Meeting day” with Auto-Fill

1. Click on the column Country and you will see an X appear at the lower right hand corner of the cell.

2. Now drag the cells and auto fill will automatically fill in the data for you.

Similarly, you can fill in the Meeting Day column by dragging the “Monday”.

Apart from the Auto Fill function, there are several other functions in Excel that allow for smarter data gathering like DOLLAR, EXACT, FIXED, PROPER, REPT, SUBSTITUTE, VALUE etc.  We’d recommend you try out an in depth Excel Data Analytic course to understand them all.

Collating Data

If you need to collect data from several Excel sheets, ie “collating data”, you can do this easily.

1. Click on the “Review” tab and then click on “Share Workbook” button.

2. Click the check box which says “’Allow changes by more than one user at the same time. This also allows workbook merging’ check box.”

3. Go to the Advanced tab and select appropriate options for track and update changes.

4. Save the workbook and go to “Save As”. Now choose a network location which can be accessed by all the users you want to share the data with.

Step 2 : Data Processing

Excel allows you to process data using several formulas and built in functions. Some of the important functions are explained below:

1. Sort

Sort function allows you to sort the collected data in ascending or descending order. You may sort a single or more than one column. To sort, click on any cell present in the column you want to sort. Now, click on Data tab and choose ascending or descending order.

2. Conditional Formatting

In order to highlight specific cells based on certain computed values, you can use conditional formatting. To conditional format, click on the column; go to Home->Conditional Formatting and choose the type of formatting you wish to apply.

3. What-if Analysis

What-if analysis allows you to fill in data based on a specific condition. You can compare different cases and allow the data to be computed automatically. The scenario manager helps you to create different scenarios for the computation of data.

Similarly you can create the Goal Seek function to get the final result of data compilation.

4. Data Analysis ToolPak

For easy computation of statistical and financial data analysis, you can use the Data Analysis ToolPak Excel Add on. In order to install the Data Analysis ToolPak, click the office button and click on excel options.

Now, click on add ins and choose “Analysis ToolPak” and click OK.

Data Filtering

Filtering data allows you to gain insights on those values that meet a specific criteria. You can apply filters on more than one column and even apply sub filters to further refine the data.

In order to apply the filter, click on the Data tab and click on Filter. You will see a drop down arrow in the data columns which has options for filtering data based on the individual data available in each column.

For example, clicking on the drop down arrow under the Meeting Day column displays each individual data available on that column. You can check the data you need and uncheck the ones you don’t.

Step 3: Data Presentation

The last step in data analysis and interpretation is the presentation of the final computed, organised data. Excel has several Chart options that let you show your data in easy ways. In order to create a chart, select the data you want to present (select all the columns) then click on the Insert tab and choose the type of chart you want to display. The available chart formats are column, line, pie, bar, scatter, doughnet, bubble, radar etc.

The column chart given below displays student’s name and the number of ice creams they have eaten.

Understanding Correlation, Covariance and Regression

For more advanced levels of data analysis and interpretation using Excel, you need to understand the basics of correlation, covariance and regression.

Correlation– It allows you to understand the relationship between two sets of data in order to identify a possible pattern.

Covariance– It is the identification of those variables which change together in two or more sets of data.

Regression- It determines the pattern in which a change in one variable affects the value of other variable. Excel offers both single and multiple regressions.

For example, you want to find out the relationship between two sets of data. One set contains height of 100 people and the other set contains the weight. You can use Excel options for Correlation, covariance and regression in order to identify a correlation that weight of an individual increases with the height. The syntax is

Syntax

CORREL(array1,array2)

Array1     is a cell range of values.

Array2     is a second cell range of values.

Ex

=CORREL(A1:A5,B1:B5)

Similarly, you can look up other statistical functions in Excel in an Advanced course.