5 Excel-Tricks 2go

Excel ist tot – außer man fertigt für Milliarden-Konzerne 5- Jahres-Prognosen an!

Für alle unter euch, die – entgegen der Genfer Konventionen – jeden Tag in Excel modellieren und reporten, gibt es hier 5 Excel-Tricks, die euch unter Umständen dabei helfen eure Tasks ein wenig schneller zu bewältigen.

1 | Die Excel Eingabeleiste als Code-Editor verwenden

Programmierer hassen – meistens – Excel wie die Pest und Excel-Nutzer können – meistens – nicht programmieren. Dabei kann man aus beiden Welten so viel Lernen. Eine der größten Fehlerquellen in Excel sind nämlich Schachtelformeln. Z.B. eine SUMMEWENNS()-Formel mit 2 weiteren Formeln darin verschachtelt. Ein cooler Trick wie man diesen Gefahren-Herd in den Griff bekommen kann, ist mit eingerücktem Code in der Excel-Eingabe Leiste – eine Praxis, die in der Programmierung bereits seit Jahrzehnten Best Practice ist.

Ähnlich wie viele Programmiersprachen ignoriert Excel nämlich auch Leerzeichen zwischen Formeln/Funktionen und mit der Tastenkombination Alt+Eingabe kommt man in der Eingabe-Zeile in die nächste Zeile. Je nach Hierarchie der Formel empfehle ich 4, 8 oder 12 Leerzeichen als Einrückung vor dem Text.

Vorher

=
IF(
EOMONTH(maxDate;0)<=D$5;
SUMIFS(
webshop_data[Sessions];webshop_data[ref_date];">="&D$5;
webshop_data[ref_date];"<="&EOMONTH(D$5;0));""
)

Nachher

=
IF(
    EOMONTH(maxDate;0) <= D$5;
    SUMIFS(
        webshop_data[Sessions];
        webshop_data[ref_date];">="&D$5;
        webshop_data[ref_date];"<="&EOMONTH(D$5;0)
    );
    ""
)

2 | Datumsreihen mit EOMONTH()+1

Excel-Reports in Monatsscheiben sind auch bei THE BIG C Agency eine alltägliche Anforderung. Umso lästiger ist es, passende Monatsreihen für jede Tabellenansicht zu generieren. Eine simple Formel, die es uns dabei häufig erleichtert dynamische Monatsreigen zu generieren ist folgende:

=EOMONTH([StartdateToTheLeft];0)+1
=MONATSENDE([StartdatumLinks];0)+1 //deutsch

Die EOMONTH()-Funktion geht nämlich immer automisch zum 28., 30. oder 31. eines Monats und solange man zu einem Datum eine Ganzzahl addiert geht Excel davon aus, dass man ganze Tage addieren möchte. Ist das Startdatum z.B. der 01.01.2023, dann ist dessen Monatsende im gleichen Monat (Parameter 0) der 31.01.2023. Addiert man einen Tag (+1) hinzu, landet man beim 01.02.2023 – dem ersten Tag des Folgemonats.

Es gibt diverse Möglichkeiten Datumsreihen in Excel zu generieren. Z.B. via Auto-Complete oder UNIQUE() – die oben gezeigte Variante hat aber historisch das beste Maß an Dynamisierung, Flexibilität und Kontrolle bereitgestellt.

3 | Die „neue“ UNIQUE() Funktion für Tabellen-Reihen

Mit Microsoft Excel 2019 wurde die UNIQUE() (dt. EINDEUTIG()) Funktion eingeführt. Ein absoluter Time-Saver für die Identifizierung von eindeutigen Werten in einer Tabelle. Obwohl die Funktion bereits 4 Jahre alt ist, kennen sie viele Nutzer noch nicht. Für die Corporate-Guys and -Girls in der Leserschaft, die noch immer auf Excel 2016 arbeiten müssen: sucks to be you!

Sehr häufig wird die Funktion bei uns verwendet, um Tabellen Row-Header zu generieren. Mit einer Formel füllt sich somit die y-Achse einer Tabelle sehr schnell:

=UNIQUE(webshop_data[Channel group])
=EINDEUTIG(webshop_data[Channel group]) //deutsch

Vorsicht! Die Formel braucht Platz nach unten, um die Zellen automatisch zu füllen!

Spannend sind auch die Kombinationsmöglichkeiten der Unique-Funktion. Gerne gesehene Anwendungen sind:

 //Sortiert die Kanalnamen absteigend alphabetisch
=SORT(
    UNIQUE(webshop_data[Channel])
 ;;1)

//Zählt die Anzahl der eindeutigen Elemente in einer Tabellen-Spalte
=COUNTA(
    UNIQUE(webshop_data[Channel])
 )

//Listet die einzigartigen Werte horizontal statt vertikal
=TRANSPOSE(
    UNIQUE(webshop_data[Channel])
 )

4 | #N/A mit Graphen kombinieren

Excel Graphen sind etwas dumm – insbesondere für Forecast-Darstellungen. Ist ein Wert in den Daten z.B. = 0, dann ist er immer noch im Graphen zu sehen – nämlich direkt auf der Achse. Dieses Problem kann man mit dem #N/A bzw. „nicht verfügbar“ Wert in Excel-Zellen umgehen. Mit einer bisschen Formel-Magie kann ich so sogar einen IST-Wert mit einem Forecast-Wert in einem Graphen weiterführen:

=
IF(
    [SessionsActual] <> 0;
    [SessionsForecast];
    NA()
)

5 | Dropdowns über Daten-Validierung

Dieser Trick hat bereits einen Bart, trotzdem wird er nur selten in bestehenden Dokumenten genutzt. Die Excel Datenvalidierung ist in der Lage Dropdowns in Excel einzufügen ohne dabei auf VBA zurückzugreifen. Wählt man in der Datenvalidierung den Typ „List“ bzw. „Liste“ aus, wird dem User dieses interaktive Menü angeboten. Sehr beliebt ist dieser Workaround z.B. wenn man für Daten aus 3 Jahren nicht 60 Spalten belegen möchte, sondern dem Nutzer eine Tabelle mit der Auswahl eines Jahres darin anbieten möchte.

1. Wähle eine Zelle aus
2. Navigiere zu Data-Ribbon
3. Klicke Data Validation
4. Wähle "Liste" aus dem Dropdown aus
5. Hinterlege eine Cell-Range oder einen benannten Listen-Bereichen
6. Klicke "Save"

Zur Generierung der zugrunde liegenden Listen kann man übrigens wieder ganz lässig die UNIQUE() Funktion verwenden und benannte Bereiche utilisieren.