Excel is Microsoft’s powerful tool used for spreadsheets, data visualisation and analysis. While it doesn’t offer the same analysis functionality as coding languages such Python or R, it does offer a highly accessible, user-friendly solution to conducting basic analysis.

Navigating the program without instruction can be challenging, so we’ve consolidated the most asked questions on analysing data using Excel here. 

The steps to analysing data in excel are as follows;

1. Load data
2. Clean and sort
3. Analyse

What is a data table in Excel?

A data table is a range of cells composed of rows and columns, where you can change values in all or some of the cells. This is most commonly referred to as a data “set”. The values from the table can help you answer different questions depending on the context of your data set. 

In the below example, an ice cream store owner uses a data table to track how many ice cream cones are sold each day, by flavour.

How to import data into Excel

In Excel, you can import data from many sources. The main decision to be made here is around whether you need the data to update on its own or not.

Static data is use to describe a snapshot of data that will remain fixed. It isn’t connected to any sources, and is typically the result of a point-in-time export from another source. 

To connect static data;

1. Export the data you are wanting to use from its source. This will most commonly be in the .csv format, however XML and JSON files can be parsed too.
2. Navigate to the Data tab and select Get Data -> From File -> From Text/CSV
3. Select the file

Dynamic data arising through connecting excel using Power Query (or other) to a database that is continuously updating. To connect dynamic data;

1. Navigate to the Data tab and select Get Data -> From Database
2. Enter in the server name and port number (for example, servername:portnumber)
3. Click OK

How to sort data in Excel

Once you have data entered or imported into a table, you may wish to sort that data alphabetically, numerically or in a custom order depending on your needs.

How to sort a column in Excel

1. Click on any cell in the column that you want to sort 
2. Right-click on that cell, and select “Sort”
   • If there are columns with data next to the selected cell, that data will be sorted as well by default.

How to add a filter in Excel

1. Select “Data” from the toolbar
2. Click “Filter”
   • This will add a filter option to each of the headers

How to sort a range in Excel

1. Select the range that you want to sort
2. Right-click on that cell, and select “Sort”
   • If you select the ascending or descending command, the range of cells will be sorted by the column farthest to the left. 
   • If you want the range of cells sorted by a different column’s value or with a custom rule, choose the Custom Sort command from the Home tab or the Sort command in the Data tab. 
     • From there, you can specify the column, order, and what to sort on based on your needs.
     • From the sort dialogue box, you can also add or delete levels to sort your data within already sorted ranges.
     • Once you’re finished with your selections, click OK
     • Review your new sorting procedure and select Add

Tip: Make sure to indicate if your cell range has a header row with the checkbox at the top right of the sort dialogue box.

How to transpose data in Excel

To transpose data means to rotate it from a column to a row, or vice versa. 

1. To transpose your data, first select the range of cells you want to rearrange. 
2. Next, copy the data using either the copy command on the home tab or press Ctrl + C if you’re using WIndows or Cmnd + C if you’re using a Mac. 
3. Finally, select the first cell in the area you want to paste the rearranged data. From the home tab, click the arrow next to or underneath the Paste command. Choose the transpose option. The icon looks like a data table with arrows pointing outward in the bottom right corner. You can hover over the icons to ensure the option you choose says “Transpose”.
4. Once the data is pasted in the new arrangement, you can safely delete the original data.

How to group data in Excel

Grouping, or outlining data in Excel is a way to group rows or columns together to make data sets more concise. It gives you the ability to expand (show) or contract (hide) the grouped data within the spreadsheet. This is good for data sets with lots of detail broken into subsets, such as regional sales for Europe broken down per country, but you only need to present Europe as a single region. 

1. To group or outline data, select all the columns or rows you want to include in your group. 
2. From the Data tab, select the Outline command. 
3. You will now see +/- buttons at the top of your columns or to the left of your rows that can expand or contract the data in the group.

How to merge cells in Excel without losing data

If there are too many characters to fit in a single cell or you want to present subsequent data with a more presentable structure, you may want to merge multiple cells into one. Whether for those reasons or others, Excel allows you to perform this function, however, by default, Excel will only allow you to keep the data that exists in the top left cell of the cells you wish to merge. 

1. To merge cells, highlight the cells you wish to include and select the Merge and Centre command from the Home tab. If only one of the cells selected has any data entered, this is the first and final step.
2. If multiple cells in your selection have data entered, you’ll receive a warning message that the upper-left value will be kept and all other values will be discarded.

How to add data validation in Excel

1. Select the cells you wish to remove data validation from
2. Click Data -> Data Validation.
3. On the Settings tab, click Clear All.

How to remove data validation in Excel

1. Select the cells you wish to remove data validation from
2. Click Data -> Data Validation.

On the Settings tab, click Clear All.

How to filter data in Excel

You can filter data in Excel to only focus on the data you want to see and hide the rest.

To filter a range of data:

1. Select any cell in the range of data you want to filter
2. Select the Filter function from the Data tab
3. Select the column header row arrow
4. Based on the data you want to see, select Text Filters or Numbers Filters
5. Select which comparison you wish to filter, such as Between, Top 10, Greater Than or more
6. Set the filter criteria and press OK

If your data is in a table, a column header arrow automatically appears on the table for easy accessibility to filter data in that column. 

1. Select the column header arrow for the column you wish to filter
2. Deselect “Select All” and only check the boxes of which data in that column you want to show and click OK
3. The column header row will now appear as a filter column that enables you to toggle between the filtered and full data set.

How to split data in Excel

You can split data from one cell into 2 or more cells in new columns. 

1. Select the cell(s) you want to split
2. Navigate to the Data tab, then the Data Tools group, then click Text to Columns. 
3. From the Convert Text to Columns Wizard, select Delimited, then Next
4. Select one or more delimiters to define where you want the data to be split. Check the Data preview to ensure the data will be split how you would like, then hit Next
5. Confirm the Column data format for the new columns, then hit Finish

How to remove duplicates

A significant amount of the workload of data analysts comes down to ensuring data is of a usable level of quality. Outside of just improving the quality of any subsequent outputs that are relying on the data, certain types of analysis will not be viable with unclean data, ultimately limiting output.

Duplicate entries in a data set can bias results towards to value of the duplicate, increasing the separation between the reality and the story being told by the data. To remove duplicates;

1. Select the column you want to clean
2. Navigate to the Data tab
3. Select the “Remove Duplicates” option

How to analyse data in Excel

How to enable Analysis Toolpak / Solver Add-In

1. Navigate to the Data tab
2. Click “Analysis Tools” in the top right-hand corner
3. Check the boxes for Analysis ToolPak and Solver Add-In

After doing this you should see the two tools on the right-hand side of the Data toolbar permanently.

Analysis ToolPak allows you to conduct a number of statistical analyses techniques, including;

• Anova: Single-Factor
• Anova: Two-Factor With Replication
• Anova: Two-Factor With Replication
• Correlation
• Covariance
• Descriptive Statistics
• Exponential Smoothing
• F-Test Two-Sample for Variances
• Fourier Analysis
• Histograms
• Moving Average
• RNG
• Rank and Percentile
• Regression
• Sampling
• t-Tests
• z-Tests

How to do descriptive statistics in Excel

1. Navigate to the Data tab
2. Select Data Analysis on the right-hand side of the toolbar
3. Select “Descriptive Statistics”
   • Select the data you wish to analyse for the input range
   • By default, excel will export the data to a new worksheet