After we’ve already covered topics like shortcuts, monitoring windows and macros in previous blogs, this is now the last part of our blog series about Excel.

Subtotal

 Do you know this? You have created a table and formed a formula (COUNTA, SUM or so) over all the rows. Now you filter the table by a criterion. Result: The formula does not adapt and still includes all rows in the calculation, regardless of whether they are shown or hidden due to the filtering.

The solution at this point is subtotal: =SUBTOTAL(function_num;ref_1). The function of the subtotal, i.e. the formula on which the partial result is based, is determined by means of a number, whereby among other things 2 stands for COUNTA, 9 for SUM – Excel shows you the possible selection when creating the formula. Now the calculation automatically adapts to the filtered values when filtering the table.

XLOOKUP

The VLOOKUP function is always used when you want to compare or merge several Excel tables. The V stands for vertical, so that the search or comparison is performed on a column basis. The counterpart on a row basis is the HLOOKUP for the search / adjustment on row level.

New and currently in rollout for Office 365 is XLOOKUP, which has the potential to replace VLOOKUP, HLOOKUP and INDEX/MATCH functions in the future. It offers several advantages compared to the old formulas:

• Vertical and horizontal searches can be combined with only one formula.
• You can search both right and left (or both up and down).
• There is no need to count the output range.

At this point we only want to introduce the basic formula in brief. Online you will find many detailed tutorials with examples for various applications.

=XLOOKUP (search criterion;search matrix;return matrix;[if-not-found];[compare modus];
[search modus])

Search criterion:     Which value should be searched for?

Search matrix:         Column/row in which to search for the value

Return matrix:         Column/row from which the return value is to be used

If_not_found:         (optional) Which value should be displayed if the search criterion is not found (instead of an error)

Compare modus:   (optional) Selection „exact match“, „exact match or next higher/lower value“, „wildcard character match”

Search modus:        (optional) Selection of search direction (from first to last or vice versa with or without consideration of a sorting)

Disadvantage: According to the current status, XLOOKUP will initially only be usable for Office 365 and will not be rolled out in Office 2019 (or older versions). Unfortunately, it is also not backwards compatible, so that problems can occur when files are to be shared between organizations with and without a 365-subscription using the formula.

Power Pivot

Do you already work with pivot tables in Excel? This is a special type of table with the ability to display and evaluate data according to different breakdowns and viewing angles without having to make changes to the original data. In this way, large amounts of data can be condensed into manageable views. If you have not worked with pivots before, you should take a closer look at this topic to get a better overview of your data.

But do you know Power Pivot, either? This is a function in Excel that was initially available as an add-in since version 2010 and has been installed automatically since version 2013. Basically, Power Pivot includes the same features as a „normal“ pivot table, but offers additional functions and advantages:

• Merge multiple data sources (of different data types) into a linked data model
• No limitation of the number of records
• High speed of evaluations
• Creation of individually calculated fields
• Use of hierarchies

One of our favorite functions: A normal pivot table uses the value field function „COUNT“ to determine the number of records with contents but is not able to name the number of different contents. This is easily done by Power Pivot using the world field function „Discrete Count“.

We hope that you enjoyed our small Excel blog series and that we were perhaps able to show you the one or other trick in using the software.

 

Jessica Murawski

Consultant, ADCONIA GmbH