By: Ben Richardson Last updated:
February 17th, 2022
Reviewed By: Maximillian Hindley
News & tips
Data Analysis is the process of cleaning, collating, examining, and modelling data to deliver information and key insights.
These insights can be used to assist with decision-making.
Data Analysts look at providing answers, to specific business-related questions, by using data.
In this post, we are going to cover a basic introduction to Data Analysis in Excel.
If you’d like to learn more about Data analysis using Excel visit one of our top-rated Microsoft Excel courses.
Data analysts use tools and software to summarize data, make predictions, visualize the data in some way, as well as generate reports. There are a variety of tools that you can use to analyse your data. These include Microsoft Excel, Tableau, Python, Jupyter Notebook, Microsoft Power BI, SAS and Talend.
Microsoft Excel is the most popular spreadsheet application in the world. There are many advantages to using Excel as a data analytics tool namely:
There are some disadvantages associated with Microsoft Excel. One is that it cannot handle large data sets with millions of points, with ease. Another is that it does not have all the automation options that some other tools on the market have.
We are going to review some simple methods, which will show you how to analyse data in Excel namely:
Once data has been analysed you may want to collect your findings into one place, and can achieve this with Linking Data in Excel.
Let’s take a closer look at our data set. It is a small database containing the Employee records of a hypothetical company.
There is a column recording Employee ID, which is a unique identifier for each row. The standard employee details such as Employee first name, last name, date hired etc. are recorded. Additionally, whether the employee works on-site or remotely is recorded.
The last column contains the results of a short survey given to each employee, which asked them to rate their level of motivation on a scale of 1 to 10. One represents demotivated while ten is highly motivated.
You can quickly rearrange your data by sorting it in a certain order. Let’s look at a simple example using our sample data set.
Situation: Let’s say we’d like to see which employees have been working at the company the longest. To do this, we can sort by the Date Hired Column.
1) First, select a single cell in column D, in this case it is cell D5.
2) Go to the Data Tab (Step 1 in the image) and in the Sort & Filter Group, click the Ascending Sort button (Step 2 in the image) which will sort the dates from Oldest to Newest.
Note: The Ascending Sort button sorts values from A to Z, smallest number to largest number or oldest to newest. The Descending Sort button sorts values from Z to A, largest number to smallest number or newest to oldest.
3) The result is the following.
The entire data set has been sorted according to the Date Hired column. We now have an answer to our question and can see which employees have been working at the company the longest.
Note: To return the data back to it’s original state press CTRL-Z on your keyboard. However, if you sort your data, save the workbook and then close the workbook. You won’t be able to do this once you re-open the workbook.
Therefore, it is advisable to always make a copy of your original data set on another sheet or have it stored in another workbook.
Taking it one step further: Excel allows you to add multiple sort levels, as well as your own custom sort order. You can sort by cell values, cell colour, font colour as well as conditional formatting icon.
If you have Microsoft 365 then you will be able to use the new dynamic array functions, SORT and SORTBY in order to sort your data.
You can filter data in Excel so that only the data that you would like to see is visible and the rest of the values are hidden. Let’s look at a simple example using our sample data set.
Situation: Let’s say we’d like to only see the Employees that earn over £ 30,000. To do this we can use the Filter option.
1) First, select any cell within the range. In this case we will select cell B7.
2) Go to the Data Tab (Step 1 in the image) and in the Sort & Filter Group, click on the Filter button (Step 2 in the image).
3) The result is the following.
4) Select the arrow on the Salary per Annum column.
5) Select Number Filters and Greater Than…
6) Using the Custom AutoFilter Dialog Box enter the value £ 30,000 and click Ok.
7) The result is the following.
We now have an answer to our question and can see which employees earn more than £ 30,000, at a glance and the rest of the data set has been hidden. We are seeing only the relevant data in other words. To remove the Filter simply press CTRL-SHIFT-L on the keyboard.
Taking it one step further: You can use the Advanced Filter when you have more complex criteria.
If you have Microsoft 365 then you will be able to use the new dynamic array FILTER function to filter your data set.
A chart is a graphical representation of key information in a data set. Charts showcase trends, patterns, and outliers in a visual way. Data visualization is part of the story-telling component of Data Analysis.
You can use charts to tell a story or to illustrate certain aspects of your data that you’d like to focus on in your spreadsheets and dashboards. Excel provides different chart types. However, the most commonly used ones for basic Data Analysis purposes are Line Charts, Pie Charts, Bar Charts, XY Scatter Charts and Column Charts.
Situation: Let’s say we’d like to see what percentage of the total annual salary bill, is made up of the salaries of senior staff. We can use a Pie Chart to showcase this in a visual manner. A Pie Chart is a great way to show the parts (slices) that make up the whole (pie). It is only used for one data series and usually when you have five or less slices.
We have another sheet which contains the salary data in the range shown below.
1) To insert a Pie Chart, select range A3:B5.
2) Go to the Insert Tab (Step 1 in the image), and in the Charts Group, click on the Pie Chart (Step 2 in the image) button.
3) Under 2-D Pie select the first option as shown.
4) You should see the following.
5) Now we will select a built-in, preformatted visual style for the chart. With the chart selected, go to the Chart Design Tab (Step 1 in the image) and in the Chart Styles Group, select Style 3 (Step 2 in the image).
6) We will enter a new chart title by clicking on the Chart Title and typing Salary Breakdown by Employee Level. The result is the following.
We can see that senior staff salaries make up 67% of the total annual salary bill.
Taking it one step further: There are other more advanced chart types available including Stock Charts, Radar Charts, Surface Charts, Treemap Charts, Sunburst Charts, Histogram Charts, Box and Whisker Charts and Funnel Charts.
If you have Microsoft 365 or newer versions of Excel then you will be able to use the Map Chart to compare values and show categories by geographical region.
Read more on how to use Stacked Bar or Column Charts here.
A Pivot Table is a powerful tool that is useful for summarizing and aggregating data, in order to extract the data of interest from a larger dataset. It is one of the most useful analytical tools in Excel.
Situation: Let’s say we’d like to see our salary data grouped according to seniority level and then further categorised by on-site versus remote staff, we can use a Pivot Table to do this.
1) To insert a Pivot Table, click on any cell in the data set. In this case we will select cell E4.
2) Go to the Insert Tab (Step 1 in the image) and in the Tables Group, click on the PivotTable option (Step 2 in the image).
3) The PivotTable from table or range Dialog Box should appear. Excel should automatically select the correct range if your data has been set up correctly.
If for some reason Excel has not selected the correct range, then you can manually select the range yourself.
4) The default location for a PivotTable to be placed is New Worksheet however, you can change this to Existing Worksheet. In this case we will leave the Default and then click Ok.
5) You should see the following on a new worksheet.
6) It doesn’t look like much at the moment, but that will soon change.
So, using the PivotTable Fields Pane, drag the field Seniority Level to the Rows area, On-site/Remote to the Columns area and the Salary Per Annum field to Values as shown.
7) Now for some cosmetic changes. To change the format of the salary values to currency, right-click on one of the salary values. In this case we will right-click cell C6.
8) Select the Value Field Settings… option.
9) In the Value Field Settings Dialog Box, click on the Number Format button.
10) Using the Format Cells Dialog Box which should appear, select Currency. Set the decimal places to zero and choose the appropriate currency symbol.
11) Click Ok and then Ok.
12) The values section should now have currency format applied.
We can now see the salary data broken down by Junior and Senior levels as well as the onsite and remote divisions.
Taking it one step further: You can do a number of other things with Pivot Tables such as add Calculated Fields, filter with Slicers and add a Report Filter.
To learn more about how to work with Pivot Tables in Excel, please visit this post.
You can use conditional formatting in Excel, to visually emphasize cells that meet a certain criteria or a condition.
Conditional formatting also allows you to Highlight Duplicate Values, more on that here.
Situation: Let’s say one of the managers at the hypothetical company would like to justify paying for a workshop offered by an external training company, to boost staff morale.
In order to visually convey her point, she would like to highlight the Motivation Level column.
The employees were previously given a survey and asked to rate their motivation level on a scale of 1 to 10. Ten means that the employee feels that they are highly motivated while one means that the employee feels demotivated.
1) To illustrate this using conditional formatting, we will highlight cell range I4:I22 as shown.
2) Go to the Home Tab (Step 1 in the image), and in the Styles Group, select Conditional Formatting (Step 2 in the image).
3) Select Icon Sets and More Rules…
4) Using the New Formatting Rule Dialog Box, select the Format all cells based on their values option.
5) For Icon Style select, the 3 Flags style.
6) In the Icon Section, set the Green Icon to No Cell Icon.
7) Change Type to Number and Value to >7.
8) While still in the Icon Section, set the Yellow Icon to No Cell Icon. Change Type to Number and Value to >=5.
9) Now click Ok.
10) The result is the following.
We can now see the red flags clearly – i.e the staff morale is low.
Therefore, the manager can argue that low staff morale will affect productivity and ultimately the company’s bottom line. Thus, she has justification for her decision to pay for the workshop.
Taking it one step further: There are many built-in conditional formatting options available, and Excel allows you to customize these options to suit your needs.
To learn more about Conditional Formatting in Excel, read this comprehensive guide.
You will often be presented with data that needs to be analysed in a meaningful way. Excel provides plenty of tools to get you started with organizing and summarizing your data.
Once you are comfortable with the standard data analysis features, you should consider taking the next step and learning about automation with VBA and Power Query.
Looking for more top tips on Excel? Read our guide here on the Transpose Function in Excel.