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.

 

Analysing Data In Excel

 

Graphic illustrating the Data Analysis Process.

 

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:

 

  • The learning curve is not steep
  • Reports or charts generated in Excel can easily be copied into presentations and Word documents
  • Workbooks can be shared easily
  • Excel has built-in features such as functions, charts and Pivot Tables which are specifically meant for data analysis
  • Excel is considered to be the traditional go-to software, used in many fields, for simple and intermediate data analysis requirements

 

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:

  • Sorting
  • Filtering
  • Charts
  • Pivot Tables
  • Conditional Formatting

Once data has been analysed you may want to collect your findings into one place, and can achieve this with Linking Data in Excel.

 

Description Of The Data Set

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.

 

Screenshot showing the sample data set.

 

Sorting Data

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.

 

Screenshot showing cell D5 selected.

 

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.

 

Screenshot showing the Ascending Sort button in the Sort & Filter Group on the Data Tab, highlighted.

 

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.

 

Screenshot showing the entire data set organized by the date hired criteria.

 

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.

 

Filtering 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.

 

Screenshot showing cell B7 selected.

 

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).

 

Screenshot showing the Filter button highlighted in the Sort & Filter Group on the Data Tab.

 

3)  The result is the following.

 

Screenshot showing the Filter arrows applied to the range.

 

4)  Select the arrow on the Salary per Annum column.

 

Screenshot showing the arrow on the Salary Per Annum column highlighted.

 

5)   Select Number Filters and Greater Than…

 

Screenshot showing Number Filters and Greater Than... options highlighted.

 

6)   Using the Custom AutoFilter Dialog Box enter the value £ 30,000 and click Ok.

 

Screenshot showing the Custom AutoFilter Dialog Box with 30000 and the Ok button highlighted.

 

7)   The result is the following.

 

Screenshot showing the filtered range.

 

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.

 

Charts

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.

 

Screenshot showing the salary data broken down by Employee Level.

 

1)   To insert a Pie Chart, select range A3:B5.

 

Screenshot showing the range A3:B5 selected.

 

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.

 

Screenshot showing the Insert Tab and the Pie Chart button highlighted.

 

3)  Under 2-D Pie select the first option as shown.

 

Screenshot showing the 2-D Pie Chart option highlighted.

 

4)  You should see the following.

 

Screenshot showing the Pie Chart.

 

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).

 

Screenshot showing Style 3 highlighted.

 

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.

 

The formatted Pie Chart.

 

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.

 

Pivot Tables

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.

 

Screenshot showing cell E4 selected.

 

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).

 

Screenshot showing the PivotTable option highlighted.

 

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.

 

Screenshot showing the PivotTable from table or range dialog box.

 

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.

 

Screenshot showing the PivotTable without any fields added.

 

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.

 

Screenshot of the Pivot Table showing the fields added and highlighted.

 

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.

 

Screenshot showing the Value Field Settings... option highlighted.

 

9)  In the Value Field Settings Dialog Box, click on the Number Format button.

 

Screenshot showing the Value Field Settings Dialog Box with the Number Format button highlighted.

 

10)  Using the Format Cells Dialog Box which should appear, select Currency. Set the decimal places to zero and choose the appropriate currency symbol.

 

Screenshot showing the Format Cells Dialog Box with decimal places highlighted.

 

11)  Click Ok and then Ok.

 

12)  The values section should now have currency format applied.

 

Screenshot showing the Currency format applied to the Values section.

 

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.

 

Conditional Formatting

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.

 

Screenshot showing cell range I4:I22 highlighted.

 

2)  Go to the Home Tab (Step 1 in the image), and in the Styles Group, select Conditional Formatting (Step 2 in the image).

 

Screenshot showing the Conditional Formatting option, in the Styles Group on the Home Tab highlighted.

 

3)   Select Icon Sets and More Rules…

 

 

Screenshot showing Icon Sets and More Rules... highlighted.

 

4)   Using the New Formatting Rule Dialog Box, select the Format all cells based on their values option.

 

Screenshot showing the Format all cells based on their values option highlighted.

 

5)   For Icon Style select, the 3 Flags style.

 

Screenshot showing the 3 Flags Style highlighted.

 

6) In the Icon Section, set the Green Icon to No Cell Icon.

 

Screenshot showing the No Cell Icon option highlighted.

 

7)  Change Type to Number and Value to >7.

 

Screenshot showing Value and Type highlighted.

 

8)  While still in the Icon Section, set the Yellow Icon to No Cell Icon. Change Type to Number and Value to >=5.

 

Screenshot showing No Cell Icon, Value and Type highlighted.

 

9)   Now click Ok.

10)   The result is the following.

 

Screenshot showing the custom Icon Set applied.

 

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.

 

Conclusion

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.