how to plot a graph in excel using formula

Set the data range by selecting all the data.To show a linear relationship using Excel, such as density, complete the following steps:

How to Make a Linear Plot using Microsoft Excel 2010 We can see in our Q-Q plot above that the data values tend to deviate from the 45-degree line quite a bit, especially on the tail ends, which could be an indication that the data set is not normally distributed.Īlthough a Q-Q plot isn’t a formal statistical test, it offers an easy way to visually check whether or not a data set is normally distributed.To Make a Linear Plot using Microsoft Excel The way to interpret a Q-Q plot is simple: if the data values fall along a roughly straight line at a 45-degree angle, then the data is normally distributed. This will add the following line to the chart:įeel free to add labels for the title and axes of the graph to make it more aesthetically pleasing: This will produce the follow Q-Q plot:Ĭlick the plus sign on the top right-hand corner of the graph and check the box next to Trendline. Within the Charts group, choose Insert Scatter (X, Y) and click the option that says Scatter. Use the following formula to calculate the z-score for the first data value:Ĭopy the original data from column A into column E, then highlight the data in columns D and E.Īlong the top ribbon, go to Insert. Step 4: Calculate the z-score for each data value. Next, use the following formula to calculate the percentile of the first value: Step 3: Find the percentile of each data value.

Next, use the following formula to calculate the rank of the first value:Ĭopy this formula down to all of the other cells in the column: Step 2: Find the rank of each data value. If your data is not already sorted, go to the Data tab along the top ribbon in Excel, then go to the Sort & Filter group, then click the Sort A to Z icon. Note that this data is already sorted from smallest to largest. Perform the follow steps to create a Q-Q plot for a set of data.Įnter the following data into one column: This tutorial explains how to create a Q-Q plot for a set of data in Excel. In most cases, this type of plot is used to determine whether or not a set of data follows a normal distribution. A Q-Q plot, short for “quantile-quantile” plot, is often used to assess whether or not a set of data potentially came from some theoretical distribution.

How to plot a graph in excel using formula