Bedingte Formatierung für Controlling-Berichte in MS Excel 2003 und 2007

Alexander Wildt
Im Controlling ist das Berichtswesen (Reporting) ein wesentlicher Bestandteil. Es stellt die wichtigsten Informationen für verschiedene Adressaten zur Verfügung. Da in einem Unternehmen verschiedenste Daten entstehen, müssen diese im Berichtswesen durch den Controller aufbereitet werden. Neben der Verdichtung und Auswertung der Daten gehört auch die optische Aufbereitung dazu, um somit für die Adressaten wichtige Inhalte besser hervorzuheben. 

Aus diesem Grund soll in diesem Excel-Tipp vorgestellt werden, wie ein Controller mit der bedingten Formatierung von MS Excel verschiedene Formatierungen durchführen kann. 

1. Allgemeines 

In MS Excel 2003 oder früheren Versionen konnten über die Standardfunktion der bedingten Formatierung nur 3 bedingte Formatierungen eingefügt werden. Wenn diese Regel umgangen werden sollte, musste auf VBA zurückgegriffen werden. Zusätzlich waren nur einfache Effekte mit der Standardfunktion durchführbar. 

Diesbezüglich wurde in MS Excel 2007 und neuere Versionen einiges verändert. Es stehen dem Nutzer jetzt mehr als drei Formatierungen zur Auswahl und zusätzlich können verschiedene grafische Effekte ergänzt werden. Diese Neuerungen haben jedoch den Nachteil, dass die bedingten Formatierungen nicht mehr kompatibel mit MS Excel 2003 sind und somit in den älteren Versionen nicht dargestellt werden können. 

Daher werden zunächst in diesem Tipp bedingte Formatierungen vorgestellt, die in MS Excel 2003 und 2007 funktionieren und im Anschluss folgen bedingte Formatierungen, die nur ab MS Excel 2007 zur Verfügung stehen. 

Um bedingte Formatierungen einzugeben, benötigten Sie das Eingabefenster für bedingte Formatierungen. Dies können Sie wie folgt öffnen. 

MS Excel 2003 oder älter: Format/Bedingte Formatierung… 

MS Excel 2007 oder neuer: Start/Formatvorlagen/Bedingte Formatierung/Regeln verwalten…/Neue Regel 
2. Jede zweite Zeile einfärben

Häufig wird in Listen gewünscht, dass jede zweite Zeile eine andere Farbe enthält, um somit die Liste besser lesen zu können. In diesem Fall ist eine Umsatzliste gegeben.

Diese Formatierung können Sie am besten umsetzen, wenn Sie die betroffenen Zellen markieren und in den Formeleditor für bedingte Formatierung folgende Formel eintragen und die gewünschte Formatierung wählen. In diesem Beispiel sollen die zu verändernden Zeilen hellgrau eingefärbt werden.

Formel: =Rest(Zeile();2)


abb1-gefaerbete-zeilen-bed-format.jpg
Abb. 1 gefärbte Zeilen

Dies kann auch analog dazu mit Spalten durchgeführt werden, hierfür ersetzen Sie Zeile() durch Spalte().

3. Den größten oder kleinsten Wert hervorheben

Diese Funktion kann genutzt werden um zum Beispiel den größten Umsatz in einer Region hervorzuheben. Jedoch wird selten nur die einzelne Zahl benötigt, meist soll die ganze Zeile markiert werden.

In diesem Beispiel soll der größte Wert grün und der kleinste Wert rot hervorgehoben werden.

Hierfür markieren Sie die Tabelle mit den Daten und geben in den Editor folgende Formeln ein.

Formel für größten Wert: =$G5=MAX($G$5:$G$64)
Formel für kleinsten Wert: =$G5=MIN($G$5:$G$64)

Die Formel bezieht sich auf eine Wertetabelle, in der in Spalte G in den Zeilen 5 bis 64 die Werte (Umsätze) enthalten sind.

abb2-min-max-gefaerbt-bed-format.jpg 
Abb. 2 Max und Min

4. Einen bestimmten Wertebereich hervorheben

Der Controller möchte in der Umsatztabelle sämtliche Umsätze hervorheben, die zwischen zwei bestimmten Werten liegen. Der größte Grenzwert soll in Zelle N5 definiert werden und der kleinste Grenzwert in N6. Hierfür wird der Zellenbereich der Datentabelle ebenfalls markiert und im Editor für die bedingte Formatierung folgende Formel erfasst.

Formel:  =UND($K5<=$N$5;$K5>=$N$6)

Als Formatierung wurde ein gelber Hintergrund gewählt. In diesem Fall sind in Spalte K die Umsatzzahlen enthalten, beginnend in Zeile 5.

abb3-wertebereich-faerben-bed-format.jpg 
Abb. 3 Wertebereich

5. Neue Möglichkeiten ab MS Excel 2007

Neben den bisher genannten Beispielen, die in beiden Excel Versionen umsetzbar sind, sollen in diesem Punkt noch einige schnelle Formatierungen mit Bedingungen vorgestellt werden, welche ab MS Excel 2007 verfügbar sind. Dafür wird von der gleichen Umsatztabelle ausgegangen.

In diesem Tipp sollen drei Möglichkeiten vorgestellt werden.

5.1. Datenbalken

Diese Option fügt einen grafischen Balken in den Datensatz ein, der anzeigt, wie groß der Wert im Gegensatz zu den anderen Werten ist. Hierfür wird im Menü für bedingte Formatierungen die Option Datenbalken geöffnet und eine beliebige Formatierung angeklickt.

Abb4-mit-Datenbalken-bed-format.JPG
Abb. 4 Datenbalken

5.2 Farbskalen

Auch diese Formatierung kann im Menü für bedingte Formatierungen aufgerufen werden. Bei dieser Option erhalten die Werte eine Hintergrundfarbe, welche zum Beispiel einen guten oder schlechten Wert darstellt. Dabei werden zusätzlich unterschiedliche Schattierungen genutzt, um eine Aussage zu dem Wert selbst zu treffen.

abb5-farbskalen-bed-format.JPG
Abb. 5 Farbskalen

5.3 Symbolsätze

Die Symbolsätze funktionieren genauso wie die Farbskalen. Jedoch wird hierbei nicht die Hintergrundfarbe geändert, sondern ein Symbol vor dem Wert zeigt dessen Wertigkeit an.

Die Symbolsätze fügen Sie genauso wie Datenbalken oder Farbskalen ein.

abb6-symbolesaetze-bed-format.JPG
Abb. 6 Symbolsätze

Die hier vorgestellten Möglichkeiten sind nur Anregungen, natürlich können Sie weitere Regeln oder andere Kombinationen verwenden.

Die Beispieldateien bedingte Formatierungen können Sie hier herunterladen >>

 

 

 
Informieren Sie sich regelmäßig über neue Excel-Tipps  und Excel-Tools
in unserem monatlich erscheinenden >>Newsletter<<.



letzte Änderung Alexander Wildt am 17.08.2024


Autor:in


weitere Fachbeiträge des Autors | Forenbeiträge
Literaturhinweise
Excel-Tools

Bleiben Sie auf dem Laufenden mit unserem Newsletter

Tragen Sie sich für den kostenfreien und unverbindlichen Newsletter von Controlling-Portal.de ein und erhalten Sie jeden Monat aktuelle Neuigkeiten für Controller. Wir informieren Sie über neue Fachartikel, über wichtige News, aktuelle Stellenangebote, interessante Tagungen und Seminare. Wir empfehlen Ihnen spannende Bücher und geben Ihnen nützliche Excel-Tipps. Verpassen Sie nie mehr wichtige Diskussionen im Forum und stöbern Sie in Software-Angeboten, die Ihnen den Arbeitsalltag erleichtern. Beispiel-Newsletter >>

Jetzt Newsletter gratis erhalten

Premium-Stellenanzeigen



Eigenen Fachbeitrag veröffentlichen? 

Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten. Mehr Infos >>

Kommentar zum Fachbeitrag abgeben

Nur registrierte Benutzer können Kommentare posten!

Controlling- Newsletter

Neben aktuellen Neuigkeiten für Controller und neu eingegangene Fachartikel, informieren wir Sie über interessante Literaturtipps, Tagungen , Seminarangebote und stellen Ihnen einzelne Software- Produkte im Detail vor.
Werden Sie jetzt monatlich über
neue Fachbeiträge, Controlling-Tools und News informiert! Zur Newsletter-Anmeldung >>

Über 3.000 Artikel und Vorlagen

Community_Home.jpg






Werden Sie Mitglied in einer großen Controller-Community (fast 12.000 Mitglieder!) und erhalten Zugriff auf viele Inhalte und diskutieren ihre Fragen im Controlling-Forum.

Sehen Sie hier die neuesten Fachbeiträge >>

Eine neue Stelle?

Mit dem Studium fertig, Umzug in eine andere Region, Aufstiegschancen nutzen oder einfach nur ein Tapetenwechsel? Dann finden Sie hier viele aktuell offene Stellen im Controlling.
Zu den Stellenanzeigen >>

Zukunft_Aussicht_Menschen_Fernglas_pm_prometeus_315.jpg

Sie suchen einen Controller? Mit einer Stellenanzeige auf Controlling-Portal.de erreichen Sie viele Fachkräfte. weitere Informationen >>

KLR-Begriffe von A bis Z

A-C   D-F   G-I   J-L   M-R   S-U   V-Z 

Testen Sie ihr Wissen mit unseren Aufgaben und Lösungen im Bereich Kostenrechnung >>

Sie haben eine Frage?

Ratlos_Verwirrt_pm_RainerPlendl_400x275.jpg

Nutzen Sie kostenfrei das Forum für Controller und und diskutieren ihre Fragen zu Controlling-Themen.

Riesen- Ressource

Auf Controlling-Portal.de sind bereits über 1.500 Fachbeiträge veröffentlicht und ständig kommen neue dazu.

Zu den neuesten Fachbeiträgen >>
Zu den derzeit meistgelesenen Fachbeiträgen >>
Zu den Premium-Beiträgen >>

Sie möchten auch einen Fachbeitrag hier veröffentlichen? Dann erhalten Sie hier weitere Informationen >>

Amüsante Glosse

Zweifel_Denken_Frau_Brille_pm_luckyraccoon_400x300.jpg

Erfolgreiche Unternehmer einer Kleinstadt treffen sich regelmäßig im örtlichen Golfclub und diskutieren mit Ihrer Lieblingskellnerin und BWL-Studentin Pauline betriebswirtschaftliche Probleme. Amüsant und mit aktuellem Bezug geschrieben von Prof. Peter Hoberg.

Zur Serie "Neulich im Golfclub" >>

Sie möchten sich weiterbilden?

mann-treppe-up-karriere_pm_pressmaster_B10716345_400x300.jpg

In unserer Seminar-Rubrik haben wir einige aktuelle Seminar- und Kurs-Angebote für Controller, u.a. auch Kurse zum Controller / IHK zusammengestellt.

Talentpool - Jobwechsel einfach!

HR-Bewerbung-Digital_pm_yupiramos_B123251108_400x300.jpg

Tragen Sie sich kostenfrei im Talentpool auf Controlling-Portal.de ein und erhalten Jobangebote und Unterstützung beim Jobwechsel durch qualifizierte Personalagenturen.

Veranstaltungs-Tipp

roosi_anzeige_webinar_datengetriebene_entscheidungen.png
Webinar am 03.04.2025 (14–17 Uhr): Lernen Sie, aus Datenchaos smarte Entscheidungen abzuleiten – mit Praxisbeispielen, Expertenwissen, modernen Datenarchitekturen & Visualisierungstechniken. roo.si

Controlling-Software gesucht?

controlling_Dashboard_pm_ml12nan_B9707546_400x300.jpg

Verschaffen Sie sich hier einen Überblick >>
Anzeige
Buch: Kennzahlenguide für Controller

Stellenanzeigen

Teamleiter (m/w/d) Rechnungsprüfung
Techtronic Industries ist weltweit führend in der Herstellung und im Vertrieb von hochwertigen Elektrowerkzeugen für den DIY-Markt und den Profieinsatz sowie von Gartengeräten. Ein strategischer Fokus auf leistungsstarke Marken, innovative Produkte und außergewöhnliche Menschen bestimmt unseren E... Mehr Infos >>

Group Controller (m/w/d)
Wir bei TEDi arbeiten in einem der führenden Unternehmen der Nonfood-Branche. Seit der Firmen­gründung im Jahr 2004 wachsen wir nachhaltig und beschäftigen derzeit europaweit rund 35.000 Mitarbeiter (m/w/d) in über 3.200 Filialen und unserer Unternehmens­zentrale in Dortmund. Für unseren ... Mehr Infos >>

(Senior) Referent* Konzernrechnungslegung und Bilanzierung
DO WHAT YOU LOVE – als eines der größten internationalen Modeunternehmen, mit mehr als 1.200 Filialen weltweit, kreieren wir Mode für Erlebnismomente. NEW YORKER ist nicht nur ein Arbeitgeber, sondern auch ein Netzwerk aus motivierten und vielfältigen Teams, welches weitere echte Persönlichkeiten... Mehr Infos >>

Leiter/in Controlling (m/w/d)
Am Max‑Planck-Institut für Psychiatrie erforschen Wissenschaftler/innen und Kliniker/innen die Ursachen psychiatrischer Erkrankungen. In unserer Forschungs­klinik widmen wir uns der Diagnostik, Behandlung und Erforschung von psychiatrischen Erkrankungen mit einem Schwerpunkt auf therapie­resisten... Mehr Infos >>

Controller (m/w/d) − Production Controlling (m/w/d)
LITEF-Produkte sind weltweit in einer Vielzahl von Anwendungen im Einsatz. Unsere Lösungen und Erfahrungen bieten wir Kunden, die dynamische Vorgänge (Beschleunigungen und Drehungen) messen und regeln wollen, Lage und Kurs von Fahrzeugen ermitteln oder navigieren wollen – auf dem Land, in... Mehr Infos >>

Sachbearbeiter/in (m/w/d) im Finanz- und Rechnungswesen bzw. Steuerfachangestellte/r (m/w/d)
Wir, das RKW Kompetenzzentrum, unterstützen als neutraler Impuls- und Ratgeber kleine und mittlere Unternehmen. Unser Ziel ist es, die Wettbewerbs­fähigkeit und Nachhaltigkeit des deutschen Mittelstands zu verbessern. Unser Angebot richtet sich an Menschen, die ihr etabliertes Unternehmen weitere... Mehr Infos >>

Controller (m/w/d)
Egal, ob in Gebäuden, im Auto, im Zug oder im Straßenbau: Sicherer Zugang und verantwortungsvoller Umgang mit essenziellen Ressourcen – wie Wasser, Strom, Daten, Luft – ist entscheidend für unsere Lebensqualität. Genau dafür setzen wir uns als FRÄNKISCHE Group seit mehr als 115 Jahren ein. Bei un... Mehr Infos >>

Controller*in (m/w/d)
Das sind wir: Modernes Akut­kranken­haus in kommunaler Trägerschaft, Maximal­versorger, 813 Betten, 16 Kliniken, zwei Institute und ein medizi­nisches Versorgungs­zentrum. Unsere größte Stärke: Rund 2.500 engagierte Kolleginnen und Kollegen. Wir sind einer der größten Ausbil­dungs­betriebe der St... Mehr Infos >>

Weitere Stellenanzeigen im Stellenmarkt >>

Folgen Sie uns!

panthermedia_B90564160_patronestaff_310px.jpg

Controlling-Portal auf:
Facebook >> und  Linkedin >>

Veranstaltungs-Tipp

250128_dm_ReimusBanner-290x120-März.png
Erleben Sie die ReWeCo 2025 vom 8. bis 10. Mai in Mannheim: Top Keynotes, Themen wie E-Rechnung, KI & Nachhaltigkeit sowie Top-Aussteller warten auf Sie auf der größten Kongressmesse für Rechnungswesen und Controlling!   www.reweco.de

JOB- Letter

 jobletter.jpg
Sie möchten über neu eingehende Stellenangebote automatisch informiert werden? Dann können Sie unseren kostenfreien Jobletter abonnieren. Mit diesem erhalten Sie alle 14 Tage die aktuellsten Stellenanzeigen und weitere Arbeitsmarkt-News.

Kostenverfolgung Bauprojekte

Kostenverfolgung-Bauprojekte.jpg
Mit diesem einfachen Excel-Tool lässt sich die Kostenverfolgung für Bauprojekte leicht gestalten. Sie pflegen separate Listen für das Budget, Hauptaufträge, Nachträge, erwartete Kosten und Rechnungen und bekommen diese Kosten in einer Übersicht auf einem separaten Blatt nach Vergabeeinheiten und Kostengruppen zusammengefasst.

Jetzt hier für 20,- EUR downloaden >>

Excel-Tools für Controller!

Diagramme_Charts_pm_chormail@hotmail-com_B347361660_400x300.jpg

Über 500 Vorlagen direkt zum Download. Verschaffen Sie sich einen Überblick >>

Software-Tipps

QuoVadis.jpg
Der BusinessPlanner besticht seit Jahren durch seine schnelle und professionelle Umsetzung einer integrierten Unternehmensplanung. Ein besonderer Fokus liegt auf der Integration von Planbilanz und Finanzplan / Cashflow – inklusive transparentem und aussagekräftigem Reporting.
Mehr Informationen >>

LucaNet_simply_intelligent_Logo_RGB.jpg
LucaNet.Planner deckt alle Anforderungen der integrierten Unternehmensplanung, des Reportings und der Analyse ab. Setzen Sie auf eine Software, die Ihnen mit maximaler Transparenz Arbeitsprozesse erleichtert. Mit wenigen Mausklicks passen Sie vorgefertigte Strukturen an Ihre Bedürfnisse an und importieren Ist-Zahlen über fertige Schnittstellen aus Ihrem Vorsystem.  Mehr Informationen >>

idl.png
Die IDL CPM Suite ist eine Software für die Unternehmenssteuerung mit integrierten Applikationen für Konzernkonsolidierung, Finanzplanung, operative Planung, Managementreporting, regulatorisches Berichtswesen und Analyse. Herausragende Usability, hohe Automatisierung und Fachlichkeit zeichnen sie aus. Mehr Informationen >>

Weitere Controlling-Software-Lösungen im Marktplatz >>

Excel-Tool Unternehmensbewertung (Valuation Box)

Die „Valuation Box“ von Fimovi beinhaltet drei verschiedene, unabhängig voneinander verwendbare Excel-Vorlagen zur einfachen und schnellen Unternehmensbewertung. Dabei werden die Verfahren die Discounted Cashflow Methode (DCF), Venture Capital Methoden und First Chicago Methode.
Mehr Informationen >>

Break Even Analyse

BreakEven.png
Nach Eingabe der Fixkosten, der variablen Stückkosten und des Verkaufspreises wird die Break-Even-Menge sowie für alternative Absatzmengen die Kosten-, Erlös- und Gewinnwerte ermittelt. Für die tabellarische Lösung kann durch die Eingabe der Schrittweite frei festgelegt werden, für welche Absatzmengen die jeweiligen Kosten und Erlöse dargestellt werden sollen.
Mehr Informationen >>

RS Liquiditätsplanung L (Excel-Tool)

Liquiditätsplan.png
Es handelt sich hierbei um ein in Excel erstelltes Werkzeug für die Liquiditätsplanung von Kleinunternehmen sowie Freiberuflern. Die Planung erfolgt auf Basis von veränderbaren Einnahmen- und Ausgabepositionen. Detailplanungen sind auf extra Tabellenblättern für z.B. einzelne Projekte oder Produkte vorhanden. Auswertungen erfolgen in der Jahresplanung mit monatlichen Werten.
Mehr Informationen >>

Weitere über 400 Excel-Vorlagen finden Sie hier >>

Premium-Inhalte

Als Premium-Mitglied erhalten Sie Zugriff auf ausgewählte sonst kostenpflichtige Fachbeiträge und Video-Kurse für Premium-Mitglieder inklusive (über 400 Beiträge allein auf Controlling-Portal.de + Premiuminhalte auf Rechnungswesen-Portal.de).

Beispiele:

- Serie zum Berichtswesen in Unternehmen
- Kundenwertanalyse – Wie profitabel ist ein Kunde?
- Potenzialanalyse mit Excel
- Unsere amüsante Glosse: Neulich im Golfclub
- Kostenmanagement in der Logistik
- Dashboards im Kostenmanagement
- Von Total Cost of Ownership (TCO) zu TCOplus
- KLR - Aus der Praxis für die Praxis

Die Gesamtliste an Premium-Fachbeiträgen können Sie hier einsehen >>

Premium-WebinareVideo Kurs: Kennzahlen im Controlling

PantherMedia_Yuri_Arcurs200x150.jpg
Bei der Erstellung eines Kennzahlen-Cockpit, stellt sich oft die Frage, welche KPIs hier zwingend mit aufgenommen werden sollen. In diesem Online-Kurs lernen Sie Schritt für Schritt inkl. praktischer Übungsaufgaben, wie Kennzahlen im Unternehmen priorisiert und im Controlling effektiv eingesetzt werden. Zum Video-Kurs >>

Video Kurs: Controlling kompakt

Controller sind die internen Unternehmensberater im Betrieb. In diesem Controlling Kompakt-Kurs erhalten Sie mehrere Lernvideos, wie gutes Controlling funktioniert. Die Videos sind "Best-of" Auszüge aus anderen spezifischen Video-Kursen. Zum Video-Kurs >>

Video Kurs: Controlling im E-Commerce

Die Gesetze im E-Commerce sind für das Controlling anders. Die Kennzahlen müssen richtig interpretiert werden, um aus den Analysen die richtigen Maßnahmen ableiten zu können. In diesem Online-Kurs lernen Sie Schritt für Schritt, wie erfolgreiches Controlling im E-Commerce funktioniert. Zum Video-Kurs >>

Weitere Video-Kurse finden Sie hier >>

Software-Tipp

Liquiditätsplanung_Fimovi.jpgRollierende Liquiditätsplanung auf Wochenbasis. Mit der Excel-Vorlage „Liquiditätstool“ erstellen Sie schnell und einfach ein Bild ihrer operativen Liquiditätslage für die nächsten (bis zu 52) Wochen. Mehr Infos und Download >>