Excel and statistics for physical scientists

Published 15th February 2017 by Andy Connelly. Last updated 9th May 2017.


Excel is anything but ideal for use in statistical work. It can do many amazing things; however, it can be quite opaque as to what is actually happening and difficult to find the correct function. For this reason I have picked out the key functions for the statistical analysis discussed here. You can find more functions here: http://www.excelfunctions.net/Excel-Statistical-Functions.html

I have attached an an Excel file (excel_andyjconnelly_v1) to this post with many of the functions used in a relevant setting.

DISCLAIMER: I am not an expert on statistics. The content of this blog is what I have discovered through my efforts to understand the subject. I have done my best to make the information here in as accurate as possible. If you spot any errors or admissions, or have any comments, please let me know.

Basic functions


Basic statistical calculations


Trend line functions

trendConfidence Limits

Using Excel you can manually calculate the confidence interval or you can use the function for calculating it directly. However, the descriptions of these functions are very unclear and I’m still not sure how much I trust them – I have edited the descriptions below and they seem to do what I describe. I have attached an Excel file (excel_andyjconnelly_v1) to this post with both a manual confidence interval calculation and the Excel function. conf_tconf_norm

Distribution Tables

You can also access the t-distribution tables:



And the f-distribution tables:



Tests of Probability


Data analysis tools

Another way to carry out statistical tests in Excel there is an Add-in called “Data Analysis Tools” accessible through Options if it is not automatically present. This gives you various outputs, however, the output does not automatically update if you change the values you are analysing.


Further reading

  1. Data analysis for chemstry: An introductory guide for students and laboratory scientists, Hibbert & Gooding, 2006
  2. http://www.excelfunctions.net/Excel-Statistical-Functions.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s