How to Do Data Analysis with Microsoft Excel

27 Jan 2021 01:43 PM By Network

MS Excel is the most important tool that was utilized for dealing with data by every industry before the arrival of tools like PowerBI and Tableau. However, nowadays also, Excel is broadly utilized for its strong data analysis capacities.

There are several methods that you may use for assessing data. Several statistical packages are accessible, incorporating Microsoft Excel that is free and can sometimes be utilized for effective, simple data analysis.

In this article, we will guide you through some of the helpful Excel methods for assessing data and make a visually attractive dashboard ready for presenting to end-users.

Methods to Analyze Data Using MS Excel

Pivot Table

The Pivot Table is one of the most crucial and widely utilized features of Excel. You can choose the whole data and insert a pivot table from the Insert menu in MS Excel. Excel utilizes Count by default. You can filter the data depending on Day, Month, or Year. Utilize the Number Format option for eliminating decimal spots or make it a whole number altogether. After preparing the pivot table, you can utilize a favorable pivot chart for visual data assessment by tapping the PivotChart option.


Once the necessitous charts are prepared, you need to make an attractive dashboard that is interactive and self-explanatory. Excel is similarly good like other analytical tools such as Tableau for making Dashboards. Copy all the charts in a new sheet. You can erase the gridlines from the Page Layout menu on the sheet. For making the dashboard interactive and attach all the charts, you can include slicers.

Choose a chart and select Slicer from the Insert menu for any needed field. Attach the slicer to every chart so the users can assess different data combinations easily. Now right click on the slicer and choose the Report Connections option. Choose needed pivot tables for creating the connection. This should be repeated for every slicer. This is how you can make extremely interactive dashboards within a few minutes in Excel.

Now let’s have a look at some helpful Excel features that will help analyze large datasets efficiently!


This is a useful function of Excel to hunt for particular data when there are several rows and columns in your dataset. Moreover, this function’s other usage is that you can use it as a clear lookup form when several users are working on a specific dataset. In Excel, you can add the Vlookup function from the Formulas menu.

There are some other lookup functions in Excel, which are similar to Vlookup() – Xlookup(), Lookup(), and Hlookup().

Drop-Down List

In MS Excel, another great feature is inserting a drop-down list to categorical data fields. This will come primarily while dealing with data by numerous users. For instance, in case multiple users assess the same sheet and the same status is being included by them, you can insert a field with all their status or names, which they can select feasibly from the drop-down list.

Data Splitting and Duplicate Data Cleaning

Text to Columns is another cool feature in Excel, which helps which managing data separated by delimiters. This is really useful while handling time and date. With this feature, you can split the time and date easily depending on the hour, day, month, year, etc. Initially, you should choose the data that you need to split, next select the Text to Columns option from the Data menu. Data splitting to columns is a 3-step procedure. In the foremost step, you can select between the fixed width and delimiters option. Secondly, you need to select the delimiter type. Thirdly, you can select the area where you need Excel for pasting the split data. Hence, you can easily split the data depending on your need with no hassle.

Data cleaning is a fundamental component of every data analysis procedure. One of the basic jobs analysts do is to remove duplicate values from the data. To deal with duplicates in Excel, choose the data range, and then click on the Remove Duplicates option from the Data menu. Here you can select which columns should be added for the data validation. After tapping on the OK button, Excel will showcase the number of duplicate values that have been erased in case it was present in the dataset.

The Necessity of Reporting All Results

While assessing data, it is important to report every result, even in case they appear irrelevant. Moreover, it is important to not bulge data analysis together and make conclusions. For instance, a researcher researching the efficacy of visual support to improve knowledge of cataracts runs a 10-question analysis to patients before and after displaying them the visual support. The researcher discovers the visual support enhances the whole number of questions answered precisely. This is a good beginning, but it’s not sufficient. The researcher must assess the outcomes of every individual query. Only understanding that the intervention improves whole knowledge gives little data regarding the intervention’s strengths and weaknesses. The intervention possibly led to a meaningful boost in the number of people knowing what a cataract is, but not the number of individuals understanding precise post-operative processes. This is vital to understand because the intervention then can be changed to better transfer the required data.

We have discussed some of the most commonly utilized Excel features that generally help in assessing data and save lots of time while pre-processing and reporting data. MS Excel has many other functions that may not have been discussed in this article. Work with visualize complex data or streamline disparate numbers, to find the absolute variety of functionalities in Excel Analytics. A powerful understanding of Excel is highly required in case you are looking forward to a career in data analytics.

Getting started is easy

Setup your first data flow within minutes.

Get Started for Free

No credit card required.