Exceltip | Hoe kun je een (bijna) budgetoverschrijding visueel maken?

Natuurlijk kun je met je financiële achtergrond prima cijfertjes lezen en interpreteren. Maar soms is het toch erg handig als direct visueel is wat er aan de hand is!

Direct een markering als het budget overschreden wordt bijvoorbeeld. Dat regel je met voorwaardelijke opmaak. Je kunt ook al een markering krijgen als het budget dichterbij komt.

Het gebruikte voorbeeld

Hier zie je een sterk vereenvoudigd voorbeeld. Links moet je de getallen echt lezen om te zien dat de kosten het budget overschrijden (a). Rechts is dat door de celkleur al direct zichtbaar duidelijk (b).

Je kunt dat verder uitbreiden door ook de individuele kostenposten die het budget overschrijden te markeren (c).
Of door met een oranje een waarschuwing te geven wanneer je in de buurt komt van het budget en de overschrijving met een rode kleur (d).

Hoe maak je zoiets?

Je regelt dit met voorwaardelijke opmaak. Het basisprincipe hierbij is:

• Selecteer de cel(len ) die een voorwaardelijke opmaak moet(en) krijgen.
• Kies Start > Voorwaardelijke opmaak en maak dan een keuze.

Werkelijke kosten zijn groter dan het budget (situatie b)

• Je selecteert de cel met de werkelijke kosten (C6).
• Kies Start > Voorwaardelijke opmaak > Markeringsregels > Groter dan.
  • Wijzig de voorgestelde waarde die staat bij ② in de cel waar het totale budget staat: B6. Excel maakt hier een vaste celverwijzing van: $B$6.
  • Kies eventueel een andere opmaak bij ③ en OK.
    In voorbeeld c zie je dat de totale werkelijke kosten een andere opmaak hebben. Kies hiervoor bij ③ voor Aangepaste indeling en stel het dan in.

Markeer ook overschrijdingen per kostenpost (situatie c)

Als je ook de individuele kostenposten wilt markeren moet je nog een voorwaardelijke opmaak maken.

• Selecteer de cellen met de werkelijke kostenposten (C2:C4)
• Kies Start > Voorwaardelijke opmaak > Markeringsregels > Groter dan.
  • Wijzig de waarde nu in de celverwijzing =$B2.
    Als je zou verwijzen naar $B$2 zouden alle kostenposten vergeleken worden met het budget voor materiaal! Voor de andere kostenposten moet naar een andere rij verwezen worden. Het rijnummer in de celverwijzing is dus niet vast! Vandaar alleen de $ voor de kolom.

Hoe wijzig je dit in groter dan of gelijk aan?

In de lijst met opties staat wel Groter dan en Kleiner dan, maar niet groter dan of gelijk aan. Hoe regel je dat?
Als je dit achteraf wilt aanpassen, dan moet je de voorwaardelijke opmaakregel wijzigen.

• Selecteer de cel(len) en kies Start > Voorwaardelijke opmaak > Regels beheren
• Je krijg dat voor de geselecteerde cellen te zien welke opmaakregels er zijn toegepast.
  • Als je alle voorwaardelijk opmaakregels wilt zien in het werkblad wijzig je dat bij ①.
• Selecteer de regel die je wilt aanpassen en kies Regel bewerken ②.
  • Hier kun je wel groter/kleiner dan of gelijk aan kiezen ③.

Hoe markeer je dat je de werkelijke kosten benadert (situatie d)?

Er kunnen meer voorwaardelijke opmaakregels van toepassing zijn op dezelfde cel(len). Als je de overschrijding wilt aangeven met een rode kleur (situatie c) en als je dit benadert met een oranje kleur (situatie d), dan maak je voor die cel een tweede voorwaardelijke opmaak.
Als 95% van de totale budgetkosten bereikt zijn, moet het gemarkeerd worden. In zo’n situatie moet je een formule gebruiken als voorwaarde. Die formule moet altijd WAAR of ONWAAR als uitkomst hebben. Bij WAAR wordt de opmaak toegepast, anders niet.

• Selecteer de cel met de totale werkelijke kosten: C6 en kies Start > Voorwaardelijke opmaak > Nieuwe regel.
• Kies Een formule gebruiken om te bepalen welke cellen worden opgemaakt ①.
• Zet de formule bij ②: in dit geval als de cel groter of gelijk is aan 95% van de waarde van B6:
  =$C$6>=($B$6*95%)
• Kies de hierbij horende opmaak ③.

Voorrangsregels

In sommige situaties is de volgorde van de opmaakregels van belang. Bij situatie d moet de regel van de overschrijding als eerste uitgevoerd worden. Zou immers de budgetbenadering de eerste regel zijn, dan zal de overschrijdingsregel nooit getoond kunnen worden!
Regel de volgorde bij het beheren van de opmaakregels: selecteer een regel en klik op Omhoog of Omlaag.

Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken. Naast de blogs op onze website, publiceert zij ook wekelijks op YouTube een korte tip (maximaal 2-3 minuten) die meestal over Excel gaat.

---

Meer weten? Volg een van onze Excel-cursussen voor accountants en belastingadviseurs. Kijk op onze site voor meer informatie.