Tech

How to calculate with Excel functions


Formulas and functions are the most powerful tool in Excel. Even complicated calculations can be carried out quickly with functions in Excel.

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

Excel 2016
use, but also work with older versions of Excel. The functions presented here also correspond to the functions in

Excel 2011 for Macintosh
.

Tip:

If you are using a significantly older version of Office, you can buy the current version of MS Office relatively cheaply:

Shopping cart iconView 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.

Enlarge

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

Enlarge

Simple example of a formula. Click on the ƒx symbol for help with the typed formula

© 2014

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.

On the left the Fornel = A1 + A2, on the right the result

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.

Conditional formatting

Conditional formatting

With the “Conditional Formatting” in the Excel start menu, you can specify rules and values ​​on the basis of which Excel designs your table. For example, if you enter “Less than” 50 and select “Light red fill 2”, then Excel stores all values ​​in the table that are less than 50 light red.

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.

Relative and absolute cell references

Relative and absolute cell references

© Vierfarben Verlag

By default, Excel automatically adjusts cell addresses when filling in rows and columns (relative cell references). If you want to avoid this and insert fixed, absolute cell references, you have to insert a dollar sign in the name of the desired cell.

Excel functions

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.

Enlarge

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.

Book tip: Excel 2016. Instructions in pictures

The four-color book

The book is published by Vierfarben-Verlag, which belongs to Rheinwerk Verlag GmbH (formerly Galileo Press)

Excel 2016. The instructions in pictures for 9.90 euros
in the series “See how it works”. On around 360 pages, the two authors offer a comprehensive overview of Excel 2016 in all its facets. The peculiarity of this book: It is a picture-by-picture instructions, each function described in the book is presented with a screenshot. The associated explanations are right next to the screenshot. This procedure is therefore particularly suitable for readers who attach great importance to consistent and meaningful illustrations and who do not want to read long passages of text. This book is also very suitable for beginners in Excel. The authors deal with the quite complex topic “formulas and functions” on 73 pages, that is, quite comprehensively.

Note:

All functions can be found under the Formulas tab. The function library there is sorted by groups.

Functional assistant

Functional assistant

You can use the function wizard to search for a suitable function. To do this, click on the fx icon (insert function) on the far left of the menu bar in the Formulas tab. A small window called “Insert function” opens. Enter your search term in the input field under “Search function”, in our example this is “Link texts”. Then click OK”. And Excel shows you suitable functions.

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

Tags

Related Articles

Back to top button
Close
Close