4. Digital competences

Now that we can secure our data, let’s look at what we can do with it. For example, data can be arranged in tables and thus mathematically or statistically related to each other and presented in diagrams to become smarter from this data. A well-known programme for this (a so-called “spreadsheet programme”) is Microsoft Excel.

Excel is a spreadsheet programme that provides us with an extensive selection of the most diverse mathematical and statistical functions for processing data. These functions are predefined formulas of function arguments with which calculations are carried out and are built up according to a basic structure that is always the same (“syntax”). They start with an equal sign (=), followed by the function name and the function arguments, which are separated by a semicolon (;).

Let’s look at some important mathematical functions as examples:

In practice, this works as follows, for example. We want to display the number in cell F6 (i.e. column F, row 6) rounded up to two decimal places in cell G6. So we first select cell G6 in the grid and then go through the following steps.

1. We click on Insert Function in the Formulas
2. In the window that appears, we now select the category Maths & Trig.
3. In the Select a function list we select our desired function ROUNDUP.

Figure 2
Source: bit.academy https://portal.bitacademy.at/module/368/phase/6369/

4. Then we click OK and the dialogue box Function Arguments appears
5. Now we select our number in cell F6 as the function argument – either by mouse click or simply type
6. Under Num_digits we enter 2 digits.
7. With OK we confirm our function.

Figure 3
Source bit.academy https://portal.bitacademy.at/module/368/phase/6369/

With this, you know the basic procedure of most mathematical functions in Excel – of course, the options in the dialogue windows change according to the selected function. Tip: For functions that can affect a whole range of numbers, such as SUM, you can quickly select the relevant range by holding down the mouse button.

Excel also offers us many functions for statistical calculations. Here are the most important examples:

A statistical function in Excel works similarly to a mathematical one:

1. In the dialogue window Insert Function we select the category Statistical and there is the function RANK.EQ
2. In the Number field we write the relevant cell (in our example G12)
3. We select the area of our ranking in the field .
4. In the field Order we define whether the lowest (enter any value) or the highest number (leave the field blank) gets the rank 1.
5. With OK we confirm the function.

Figure 4
Source: https://portal.bitacademy.at/module/368/phase/6370/

Excel is also wonderfully suitable for displaying tables graphically – for example, you can display data sets about each other at a glance.

There are columns, rings, pies, bars, and many other chart types in two or three dimensions. The example in Figure 5 is a column chart.

Figure 5
Source: https://portal.bitacademy.at/module/368/phase/6392/

To create a chart in Excel:

1. We first select all the values in a table that we want to include in the chart.
2. In the Insert tab we then select Charts and the desired diagram type.
3. The diagram is now created automatically. We can add or remove data at any time by changing the selected cell range, but we can also adjust the desired design or display form by clicking on the plus symbol or the brush.

One of the most powerful tools in Excel is the ability to create and work with pivot tables. Pivot tables can be used to structure, analyse and evaluate even very large amounts of data.

Various filters and data analysis tools are used for this purpose, for example, to have an otherwise too large number of data summarised or reduced to what is necessary.

Let’s look at an example of using a pivot table. We have a small coffee company and want to analyze our coffee sales. For this we have the following table as a basis:

Figure 6
Source: https://portal.bitacademy.at/module/368/phase/6413/

We now want to use a pivot table to find out what sales we made with Lavazza coffee compared to the other manufacturers, what the sales were in February and what the total sales were in the first quarter.

1. To do this, we first click on any cell in the table and then on PivotTable in the Insert Now a corresponding dialogue window is displayed.
2. In this, the cell range of our table is usually recognised automatically – we can check this using the coloured border.
3. We now have the option of inserting the pivot table in a new worksheet or the existing one. We confirm the selection with OK. Now a new task area opens.

Figure 7
Source: https://portal.bitacademy.at/module/368/phase/6413/

4. Using drag & drop, we can now drag from a list of recognized fields (e.g. manufacturer) into the Filter, Columns, Rows or Values areas.
5. For our purposes, the manufacturing companies should be displayed as rows (to be able to compare the respective turnovers). The months are displayed as columns. The turnover figures result from the price field – we drag this into the value area. There, the SUM of the values is determined by default, but this can be changed if necessary.

Figure 8
Source: https://portal.bitacademy.at/module/368/phase/6413/

The result is a pivot table as in Figure 8. We see the sales we make of Lavazza coffee compared to the other producers (1), the total sales in February (2) and all the total sales in the first quarter (3).

A pivot table can only capture the data of the source table automatically and correctly if it has no empty rows or columns.