Formulas and functions are the most powerful tool in Excel. Even complicated calculations can be carried out quickly with functions in Excel.
Evaluate with Excel formulas. The matrix formula that we will introduce to you in the course of this article is currently being used here.
The spreadsheet Microsoft Excel 2016 offers over 400 functions – from simple arithmetic tasks such as an addition (function SUM) up to complex evaluations of the data, in which values are compared (function INDEX).
On this website
Microsoft introduces all existing functions. This makes dealing with complex data records easy – but only if you know how formulas and functions generally work in Excel.
The functions presented here can not only be used with Excel 2013 or
use, but also work with older versions of Excel. The functions presented here also correspond to the functions in
Excel 2011 for Macintosh
If you are using a significantly older version of Office, you can buy the current version of MS Office relatively cheaply:
View Microsoft Office Home on Amazon
Buy Microsoft Office cheap – an overview
Buy Microsoft Office 2019 from 104.99 euros at Lizengo
Microsoft Office 2019 Home and Business for 179.99 euros
Includes Excel 2019, Outlook 2019, PowerPoint 2019 and Word 2019
Microsoft Office 2019 standard for 194.99 euros
Includes Excel 2019, Outlook 2019, PowerPoint 2019, Publisher 2019, Word 2019
The main innovation of Excel 2016 compared to the previous version 2013 was above all the extended visualization functions. How Office 2016 is special on Mac
explains this guide.
Almost all functions of the previous version of Windows Excel 2013 are also available on the Macintosh.
Here Excel shows you all available functions.
You can display all functions available in Excel by selecting the “Formulas” tab and then clicking the “Insert function” button. In the window that then opens, select “All” under “Select category”. Then Excel shows all available functions in the selection window below. With a mouse click you get a short description of each function.
Formulas: The basis for functions in Excel
Before we get to the Excel functions such as VLOOKUP or matrix formulas, we first explain their basis: the formulas.
You always write the formula in the formula bar and it always begins with an equal sign (=). A simple example: Let Excel calculate the result of the sum 113 + 253. To do this, click on any cell, write = 113 + 253 in the formula bar and press the Enter key. The result of the sum – 366 – is immediately displayed in the previously clicked (“activated”) cell.
Simple example of a formula. Click on the ƒx symbol for help with the typed formula
Most of the time, however, you expect values that are already in the table. They don’t enter numbers, they just describe where they are entered. To do this, use the Excel coordinate system: rows are numbered according to the scheme 1,2,3 etc., columns with A, B, C etc. are displayed alphabetically. The cell at the top left is A1, the one to the right of it B1 and the one below it A2 etc.
In the screenshot above, the number 113 is in cell A1, in A2 253. For the calculation, Excel refers to cells, the coordinates A1 and A2 of the cells are therefore called “reference”. The formula that we enter for cell A3 is = A1 + A2. The displayed result in A3 is again 366.
If you later change values in the table, you will see that the results of your formulas also adapt immediately. In this way, you can keep track of complex tables with many values. The same applies to tables that are constantly updated. You no longer use Excel as a data storage device, but as a complex evaluation program for your data.
With this type of formula use, Excel would not be able to enable really complex calculations. That’s why the software goes one step further and offers the so-called functions. An example: Instead of writing = A1 + A2 in the example above, we simply tell Excel what we want to do: we need the sum. Excel provides the SUM function with the same name for adding.
Video course “Excel Formulas & Functions & Pivot Tables Masterclass!” watch at Udemy
The result of = SUM (A1: A2 can be seen on the right screenshot.
A function consists of the capitalized name, then in brackets () individual cells or an entire cell range as well as other parameters – each with; Cut. For a cell range, enter the coordinates of the first and last cell to be taken into account, separated by a colon. The formula for the example is = SUM (A1: A2), but could also be called = SUM (A1: A23) or = SUM (A: A) if the whole column A is to be added. Without a function, the total would then be more difficult to calculate. The easiest way to capture cell areas is to mark the cells with the mouse when entering the formula instead of typing in the cell references.
All functions can be found under the Formulas tab. The function library there is sorted by groups.
On the next page we explain “complex functions”.
More tips, tricks and tools around Excel:
The 5 most important Excel macros for everyday life
Three smart Excel tools and an Excel alternative
Ten Excel tips for office professionals
Create pivot tables in Excel – Here’s how
The ten best Excel functions
The perfect Excel spreadsheet in just 8 steps
The 5 most important Excel macros for everyday life
Calculate with Excel formulas and functions