In this simple step by step guide you will learn how to easily use the formula for concatenate in Excel. There are a few different approaches we can take when looking to merge strings of characters together using Excel. The built in formula for this makes it super easy to simply merge cell after cell, or include additional formatting and characters for readability. The Excel CONCAT Formula The syntax for this one is very straight forward and is as follows: =CONCAT(Text1, Text2) That is it. There is also a similar formula called concatenate but this is a legacy function. Let’s look at a quick e ..read more

Exponential Smoothing is a way of smoothing out time series data in order to more easily see trends. On top of this it then allows us to forecast future values as well due to the overall trends appearing less messy. This guide will run through how to perform Exponential Smoothing in Excel, and visualise our results. How to use the Exponential Smoothing Function The first thing we will need to do is see if the Data Analysis Toolpak is installed. If you head up to the data tab in the ribbon, and see the following Data Analysis option on the right hand side then we are good to go. If you do not ..read more

Excel makes it super easily to find quartiles within a dataset thanks to a handy built in formula that does the work for us. This guide will run through the formula that is used and break down the syntax with an example. The QUARTILE Formula To find quartiles in Excel simply use =QUARTILE(array, quart) and select the cells for our array of data, followed by the quartile value that you are looking to identify from min, 1st, median, 3rd and max. The five accepted values for quart in our formula are as follows: 0 – Minimum Value 1 – First Quartile (25th Percentile) 2 – Median Value (50th Percent ..read more

Excel makes it really easy to calculate a range of statistical values, including Standard Error. Our guide will run through two different approaches that we can take – one using a formula, and the other using the data analysis toolpak. What is the formula for Standard Error? The formula for standard error takes the standard deviation of a set of values and divides it by the square root of the count of the number of values. In order to calculate standard error in Excel using a formula we will need three sets of numbers: Our list of initial values Standard Deviation of these values Square Root ..read more

Sometimes the data we are working with in Excel is not always split out in the way we want. We may have a series of numbers or words for example that should be across multiple cells, but in reality are all sitting within a single one. Assuming that there is a consistent delimiter between each value such as a space, comma, dash or so on then Excel has a powerful function called Text to Columns which automatically splits this out across columns – but what we don’t have is an equivalent funtion to split this over rows. Luckily there is a quick workaround to this. In order to split text to rows in ..read more

The FORECAST function in Excel makes it super easy to predict upcoming values when presented with a series of existing inputs. When conducting a forecast, Excel does so via a linear regression model. All we need to do is provide x values, y values, and the x value that we want to test and Excel will do the rest for us. The FORECAST Function Formula To start with, let’s take a look at the formula itself that Excel uses to conduct a forecast. The syntax is as follows: =FORECAST.LINEAR(x, known_y’s, known_x’s) The three required fields are: x: The point of data that we want to forecast a value a ..read more

Excel has some of really handy built in formulas that take away a lot of the manual work to calculate values. Included in these are formulas to quickly calculate Mean, Median and Mode. This simple guide will run through how to calculate each of the three. Calculating Mean Before we start with the formulas involved, let’s take a quick look at our sample dataset. Here we have 11 values representing various sales by two employees at a company: Starting off with Mean. Calculating a mean in Excel can be done in two different ways. First is a standard calculation where we would individually sum eac ..read more

Depending on what kind of data we are working with the values aren’t always going to be one hundred percent accurate. Sometimes the values represented in a chart, especially forecasted values over time, may have a slight range of error that could be associated with them. When creating visualizations using this data it can be really handy to have a way that this potential range of error is also factored in – this is where error bars come in super handy! In this simple step by step guide we will run through how to add error bars to charts in Excel. What are Error Bars? Error bars are basically w ..read more

Probability is a measure that is calculated to determine the likelihood of something occuring. In this guide we will run through how to perform this analysis in Excel using a quick and easy formula. Calculating Probability Using the PROB Formula To calculate probability in Excel all we need to do is use the PROB formula. The syntax for this formula is as follows: =PROB(x_range, prob_range, [lower_limit], [upper_limit]) Let’s break down each element of this: x_range: The range of values associated with the data we are measuring prob_range: A probability associated with each individual x value ..read more

A T-Test is used to calculate whether there is a significant difference in the mean between two data sets. This can be either done by comparing two separate groups, or done in a way where we look at the same group but with different values at different points – for example before and after results. Generally, our aim is to determine if there is no significant difference between the means, in which case the p value will be > 0.05. When we see the value sit lower than 0.05 then the difference between our two means is significant. In this simple guide we will run through how to perform a T-Tes ..read more