MS Excel 2016 (EN)

It is often recommended to start looking at a dataset with a plot, prior to any statistical analysis. A plot can reveal a lot: trend, variability, clusters, subpopulations, distribution. It can also reveal mistakes in the dataset, such as aberrant values. Here is an example where local temperatures have been […]

2. Spotting aberrant data with plots

If you wish to check that your dataset has been entered properly in Excel, you may use a couple of tools like Sort & Filter and Conditional Formatting. Here is an example where local temperatures have been retrieved from a weather station at Fløyen, in Bergen for the period 1/1/2017-31/12/2017 and […]

3. Spotting aberrant data using “Sort & Filter” or “Conditional Formatting”

Providing that your data set is well organized, with categories/variables placed in columns and observations in rows, you may be able to spot aberrant data points by creating a pivot table. What pivot tables are and what they may be useful for is discussed further here. A pivot table can […]

4. Spotting aberrant data with pivot tables

A pivot table is a table that displays statistics generated from a larger table that contains your “raw” data set. This pivot table may display counts, sums, averages and other statistics describing variability. It thus allows you to get an overview over thousands of data points within a few clicks. […]

1. What is a pivot table

Everything starts with a clean dataset! A clean dataset means that: the independent and dependent variables are organised in columns, each column has a unique title (header), each row has only one entry per variable, empty cells in the table should be avoided. Let’s take an example. We conduct an […]

2. Preparing the table for a pivot table

To create a pivot table from the dataset introduced previously here (and downloadable here), select the table (A1-H721), choose Insert in the main menu, and Pivot Table. In the dialog box that shows up, you may choose the data (if it is […]

3. Creating the pivot table

In this post, we keep using the example introduced here. We had built a pivot table showing the standard length SL (mm) for each of the 9 tanks. The side menu looked like this. And the pivot table looked like this. […]

4. Displaying several statistics in a pivot table

5. Displaying a subset of a category in a pivot table

6. Displaying statistics for a combination of parameters

Before we start discussing details about creating graphs in MS Excel 2016, it is best that we agree on a few terms and concepts. What you will learn to create in this section is an embedded graph or chart. “Embedded” means that the chart appears next to your data in […]

1. Familiarize yourself with a few terms.

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

As you will soon discover, the tools that MS Excel 2016 offers in order to let you build a chart work best when your data are “packed” in tables, rows and columns. Anyway, it is always good practice to organize your dataset so that data which depend on common variables […]

2. Organize your data/series in rows, columns or tables

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Since it is your first chart, we’ll take it easy and use a single dataset. Let’s use the previous example, concentrating on temperatures in Bergen between Sept. 19th and Sept. 27th 2015. The “easy way” consists in selecting the data which has to be contained in the graph: this means […]

3. Make your first line chart

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Now that the graph has been created, you might want to personalize its appearance by adding titles, changing colors, putting a background, and so on. Let’s start by adding colors to the background of the chart area and of the plot area. First, colors: right-click on the chart area, preferably […]

4. Add title, axis names, legend, colors…

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Now that you have managed to make your first line chart, you might want to make things a bit “spicier” by adding an extra line chart to the existing one (Yeah, I know… not really what you consider “spicy”). If you had previously opted for creating your graph using the […]

5. Add a series to an existing chart

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

In the example that we have just built here, we want to keep the data series which represents temperatures as a line chart and show the rain data as vertical bars (see picture to the right). First we need to select the data series “rain” and call the option to […]

6. Modify the chart style (lines, bars, columns…)

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Here, our goal is to keep using the left Y-axis for the temperature chart, while we set up a new (secondary) Y-axis to the right side of the graph that will be dedicated to the rain chart. Right-click directly on the rain data series in the plot area to open […]

7. Add a secondary Y-axis

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Let’s now take care of the fact that the temperature line is located quite high in the plot area (top right picture). Most of the values are comprised between 12 and 16, but the chart using a range from 0 to 18 to display the chart. If we want to […]

8. Ajust the range/units of the Y-axis

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

MS Excel offers an option to draw a trendline on top of your data series. This can be quite helpful to show a trend when the data is a bit to scattered to draw conclusions or when you want to simplify the “look” of your chart. To do so, click […]

9. Add a trendline to a data series

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

It is often useful or required to show the variability of your data when making a chart. This variability can easily be translated by error bars added along your plot line or bars. The bars may represent standard deviation, standard error of the mean, confidence intervals… you choose. Let’s take […]

10. Add error bars to a data series

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

It is not so often that one gets to use a pie chart… and it is not so often that such a chart is used for the right purpose. Pie charts can be used to represent different categories of a population, polls, data referring on proportions and percentage. Let’s learn how to make […]

11. Make a pie chart

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

You may realize, looking at the pie chart that we just made previously, that the design of the chart area is rather inadequate. The chart itself is ridiculously small compared to the legend. This is essentially due to a default setting that MS Excel uses and which restricts the size/position of the […]

12. Resize the legend box

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

If you wish to highlight a specific data series in a pie chart, you may want to “extract” the corresponding quadrants/slice. Click once on the pie to activate the plot area and click once more on the slice that you want to extract. Simply drag it away from the center […]

13. Extract a slice from the pie

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

The pie chart that you’ve created previously and modified until now still looks a bit “naked” as there is no display of numerical data or scale. If you want to add the actual values from the dataset on top of the corresponding slices, select the pie by clicking on it, […]

14. Add labels to the pie chart.

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

To draw a boxplot, select your range of data, then go to the tab Insert, find the icon Insert Column or Bar Chart and select More Column Charts... In the long list of charts in the tab All Charts, click on Box & Whisker and OK. The following chart appears. As you can […]

15. How to draw a boxplot

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Multiple boxplots are quite useful charts when it comes to visualize several groups or categories, their median and variability, all at once. To draw the multiple boxplot, select the table with your data organized in columns (you may select as well the headers), then go to the tab Insert, find the […]

16. How to make a multiple boxplot

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

A scatter plot (also called scatterplot or XY plot) is a chart that displays all single data points or entries in your dataset in the form of single dots. These data points must be described in the form of a pair of numbers, one being the independent variable (X) and […]

17. How to create a scatter plot

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

Bar graphs (also called bar charts or column charts) are one of the most common type of chart along with line charts. Bar graphs are useful to rapidly visualize differences between values, groups or categories based on the relative height of rectangular bars or boxes. These bars can be either […]

18. How to create a bar graph

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

It is of course possible to show more than one series in a bar graph. This is especially easy and useful when the data series to visualize are organized according to the same categories or labels. Let's come back to our previous example. In the original table, the precipitations for 2016, […]

19. How to create a clustered bar graph

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

It is also possible to create bar graphs which help you "stacking" data in columns. This type of graph is useful when you want to compare two or more sums of elements and visualize at the same time the contribution of each element to these sums. Let's come back to […]

20. How to create a stacked bar graph

in Excel16 -3- Figures and graphs / MS Excel 2016 (EN)

To calculate the average of a dataset, you may use one of the following options: 1) divide the sum of the dataset by the number of values in this dataset (which is by definition how the average is to be calculated). The sum can be easily obtained with the […]

1. Calculate the average

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

To determine the median of a dataset, you may use one of the following options: 1) Select the whole dataset (A1:A100) and sort it from the smallest to the largest value using the Sort Smallest to Largest functionality which you will find by clicking on the Sort & Filter […]

2. Determine the median

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

To calculate the variance of a series, you may choose between using the =VAR.P(...) and =VAR.S(...) functions. Both functions provide the variance, but based on different assumptions and therefore on slightly different formulas. Use =VAR.P(...) if the dataset that you analyse is a whole population. Use =VAR.S(...) if […]

3. Calculate the variance

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

The standard deviation may be calculated using one of the following options: a) the standard deviation is actually the square root of the variance. In MS Excel, this can be written =SQRT(VAR(...)). In our case, type =SQRT(VAR(A1:A100)). b) use the formula =STDEV.S(...) or =STDEV.P(...) to get the standard deviation of the […]

4. Calculate the standard deviation

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

The standard error of the mean may be calculated by dividing the standard deviation by the square root of the number of values in the dataset. There is no direct function in MS Excel to get it automatically. Therefore, you must refer to its definition and type =STDEV(...)/SQRT(COUNT(...)). In our […]

5. Calculate the standard error of the mean (SEM)

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

To find the minimum and maximum values in the dataset, you may: a) sort the dataset (A1:A100) using the Sort Smallest to Largest functionality which you will find by clicking on the Sort & Filter icon, in the Editing section of the ribbon under the Home tab. Simply retrieve the […]

6. Find the minimum, the maximum and the range.

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

There is a way to obtain most, if not all, of the values detailed above in this page by using a tool package in MS Excel. This tool package is called “Analysis Toolpak” and provides the mean, standard error, median, standard deviation, sample variance, range, minimum, maximum, sum, count and […]

7. Use the data analysis package

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

Often, the simplest way to visualize your dataset or sample will be through a histogram, also called frequency histogram. The histogram will eventually represent the distribution of the data and the shape of the chart will most certainly give you information on whether this distribution is symetrical, bimodal, skewed… To […]

8. How to draw a histogram

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

A boxplot (box plot, or whisker plot) is a compact, but efficient way to represent a dataset using descriptive stats. This “little diagram” combines informative, standard values such as the first and third quartiles (the bottom and top of the box, respectively), the median (the flat line inside the box) […]

9. How to draw a boxplot

in Excel16 -2- Descriptive statistics / MS Excel 2016 (EN)

When first starting MS Excel 2016, the program opens an empty workbook file named by default “Book1” (as indicated in the title bar on the top of the window). This workbook contains a single empty worksheet called “Sheet1” represented by the a tab at the bottom of the window. In its most common […]

1. Starting up MS Excel 2016

Default workbooks always come with a single empty worksheet. At any time, you may add a new worksheet to your workbook just by clicking on the icon + next to the tab for the current workbook. The new sheet automatically appears to the right of the last one. Note that the combination Shift+F11 […]

2. Opening a new worksheet

Adding data into a cell does not require much effort. Simply select the cell that you want to fill in with the mouse (or enter its address in the name box), type in directly your data and press ENTER. Alternatively, you may select the cell with the mouse and use […]

3. Filling in cells with data or text

Often you will have (or feel) the need to create a table to display your data. Tables are useful when your data are defined by categories or labels. However, large tables with many rows and/or columns may easily become difficult to read unless you use borders and colors appropriately. Most of […]

4. Using borders and colors to create tables

In MS Excel, cells may be filled in with numbers and text, which may be formatted in many ways. To understand the multiple possibilities offered by Excel in terms of formatting, click on the cell of interest and press CTRL+1, or right-click on the cell of interest and choose Format […]

5. Formatting the content of the cells

One of the first things that you may want to try when it comes to calculation is performing simple arithmetic operations “somewhere” in the worksheet. To do this, select any cell in the worksheet by clicking on it or by entering its address (for example E7) in the name box. […]

6. Simple arithmetic operations

MS Excel includes a function which helps you fill in a lot of consecutive cells at once. This is called autofill. Assuming that you have to fill in the cells from A1 to A10 with 10 copies of “1”, you just need to type in 1 in A1, then select the […]

7. Creating series of numbers and filling up cells