# Category: Data analysis

## Split Excel pivot table rows into separate columns

For the last few versions of Excel, the standard pivot table format is to have all of the Row fields displayed in one column.     But if you want to split your row fields into separate columns, you can switch to the “classic” pivot table view from earlier versions of Excel.   Right-click on the Read More …

## Calculating Standard Deviation with Excel

Method 1 – The longer route, with calculations explained   Firstly calculate the average (mean) of your values.       Then take the difference (variance) between each value and the mean.     Then square each variance.     Next, take the mean of the variances.     And finally, the square root of Read More …

## Random Number Generation tool in Excel

This tool can be used to rapidly populate large amounts of random numbers that adhere to various statistical rules and conditions which you can place on them. This is helpful to prove or disprove your hypothesis or to forecast how something may perform based on tried-and-tested statistical methods.   You will need to have Data Read More …

## Descriptive Statistics in Excel

When you have a lot of numeric data which you need to make sense of, the Descriptive Statistics tool can help.   To demonstrate using a simple example, my dataset is the number of each ball in the UK National Lottery (numbers 1 to 49).   Data Analysis tools must be enabled in order to Read More …

## Enabling the Data Analysis tools in Excel

Open up Excel and go to the File menu. Select Options and then Add-Ins from the left hand panel.   From the Manage drop-down list at the bottom choose Excel Add-Ins and click Go.     Click to select the Analysis ToolPak. Once you’ve done it, it will appear permanently on the Data tab in Read More …

## Regression analysis in Excel

Regression analysis is a statistical technique to assess the relationship between two or more variables. In business, you can use it to help prove whether adjusting one variable is likely to impact another e.g. if we invest in more training will our staff become more productive?   This article won’t go into the detailed mathematics behind the Read More …

## How to remove duplicates in Excel

This is another one which is easy to do in Excel when you know how, you don’t need to write any formulas because there’s an in-built function for you to use (provided your installation is Excel 2007 or later).   In this example you have a list of all the James Bond films with some Read More …