Yearly Archives: 2016

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)

Når man starter opp MS Excel 2016 åpner programmet en tom arbeidsbok og filen har standardnavnet “Bok1” (som indikert i toppen av vinduet, tittellinje). Arbeidsboken inneholder et enkelt regneark kalt “Ark1”, representert av fanen nederst i vinduet. Når man åpner programmet inneholder MS Excel vanligvis: – En menylinje (Fil, Hjem, […]

1. Kom i gang med MS Excel 2016

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

En standard arbeidsbok kommer alltid med et enkelt tomt arbeidsark. Du kan når som helst legge til nye regneark to arbeidsboken din ved å klikke på + ikonet ved siden av fanen for det gjeldende arbeidsarket. Det nye arket kommer automatisk til høyre for det siste arket. Man kan […]

2. Hvordan åpne et nytt arbeidsark

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Å legge data inn i en celle krever ikke mye arbeid. Du velger cellen du vil fylle med et museklikk (eller trykker inn koordinatene i navneboksen), skriv inn dataene og trykk ENTER. Alternativt kan du velge cellen med et museklikk og bruke formellinjen (ved siden av fx) til å skrive […]

3. Hvordan fylle celler med data og tekst

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Ofte må du (eller føler for) å lage en tabell for å vise dataene dine. Tabeller er nyttige når dataene er definert i kategorier. Store tabeller med mange rader og/eller kolonner kan være vanskelig å lese hvis du ikke bruker kantlinjer og farger på en passende måte. De fleste verktøyene […]

4. Hvordan bruke kantlinjer og farger til å lage tabeller

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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 Excel16 -1- Get Started / MS Excel 2016 (EN)

I Excel kan celler fylles med tall og tekst som kan bli formatert på flere måter. Du kan enten høyre-klikke på valgte celle(r) og så velge Formater celler..., eller du kan bruke hurtigtastene CTRL+1 . Da får du opp en meny (se bilde til høyre), og her kan du f.eks. […]

5. Hvordan formatere innholdet i celler

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

En av de første tingene du kanskje vil prøve ut er når det kommer til å regne er å gjennomføre en enkel aritmetisk operasjon et sted i regnearket. For å gjøre det, klikk på en celle eller skriv inn cellens koordinater (f. eks. E7) i navneboksen. Deretter kan du skrive […]

6. Enkle regneoperasjoner

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

I Excel finnes den en funksjon som hjelper deg å fylle inn mange påfølgende celler helt enkelt. Denne funksjonen heter autofyll. Forutsatt at du skal fylle inn cellene A1 til A10 med 10 kopier av tallet “1”, trenger du bare å skrive inn 1 i A1, velge cellen, klikke på […]

7. Hvordan enkelt lage tallserier

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

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

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Hvis du vet hvordan du kan gjennomføre enkle regneoperasjoner OG hvordan du fyller inn serier, vil du lett kunne bruke ulike regneoperasjoner på lange lister med data med bare noen på få klikk. La oss anta at du vil gange opp en hel serie tall fra 1 til 10 med […]

8. Hvordan bruke regneoperasjoner på (lengre) lister med data

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

If you know how to perform simple arithmetic operations AND how to fill in series, you will be able to apply operations to long lists of data in a few clicks. Let’s assume that you want to multiply a whole series of numbers ranging from 1 to 10 by 5. […]

8. Applying operations to (long) lists of data

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Mange funksjoner og regneoperasjoner er allerede programmert i Excel. Så lenge du vet navnet på funksjonen, og dens rolle, kan du gjennomføre tyngre kalkulasjoner på bare noen klikk. Når du klikker på fx-symbolet ved siden av navneboksen, åpner du en dialogboks som vil hjelpe deg å finne funksjonen du trenger […]

9. Hvordan bruke formler og funksjoner

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

Many functions and arithmetic operations are pre-programmed in MS Excel. Providing that you know the name and role of a function, you may perform heavy calculations within a few clicks. At any time, click on the symbol fx next to the name box to open a dialog box which will […]

9. Using formulas and functions

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Det finnes plenty med snarveier som gjør ting enkelt i Excel. To av de som kommer til å bli brukt om og om igjen når du blir litt mer rutinert er kopier CTRL+C og lim inn CTRL+V. Disse er spesielt viktige når man f.eks. jobber med større mengder data, lager […]

10. Hvordan kopiere og lime inn cellens innhold, formel og format

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)

There are plenty of shortcuts to make things easy in MS Excel. Two of these will be used over and over again when you will be handling massive amounts of data, making tables and more: CTRL+C (copy) and CTRL+V (paste). A third one will be useful as well from time […]

10. Copy-pasting cell contents, formulas and formats

in Excel16 -1- Get Started / MS Excel 2016 (EN)

Du trenger ikke være OCD (obsessiv-kompulsiv lidelse) for å organisere og rydde i dataene dine. Det er faktisk en veldig god praksis å sortere tabeller ved hjelp av parametre og variabler. Excel har et nyttig verktøy som kan rydde i henhold til en eller flere variabler av ditt valg. […]

11. Hvordan sortere data

in Excel16-NO -1- Første steg / MS Excel 2016 (NO)