Inhoud
In dit artikel vindt u twee snelle manieren om de kleur van een cel te wijzigen op basis van de waarde in Excel 2013, 2010 en 2007. Ook leert u hoe u formules in Excel gebruikt om de kleur van lege cellen te wijzigen of cellen met formulefouten.
Iedereen weet dat om de vulkleur van een enkele cel of een heel bereik in Excel te wijzigen, je gewoon op de knop moet klikken Opvulkleur (Opvulkleur). Maar wat als u de vulkleur van alle cellen met een bepaalde waarde moet wijzigen? Bovendien, wat als u wilt dat de vulkleur van elke cel automatisch verandert als de inhoud van die cel verandert? Verderop in het artikel vindt u antwoorden op deze vragen en krijgt u een aantal handige tips die u zullen helpen bij het kiezen van de juiste methode om elk specifiek probleem op te lossen.
De kleur van een cel in Excel dynamisch wijzigen op basis van de waarde?
De vulkleur verandert afhankelijk van de waarde van de cel.
probleem: U hebt een tabel of gegevensbereik en u wilt de opvulkleur van cellen wijzigen op basis van hun waarden. Bovendien is het noodzakelijk dat deze kleur dynamisch verandert, als gevolg van veranderingen in de gegevens in de cellen.
Besluit: Gebruik voorwaardelijke opmaak in Excel om waarden groter dan X, kleiner dan Y of tussen X en Y te markeren.
Stel dat u een lijst met gasprijzen in verschillende staten heeft en dat u prijzen wilt die hoger zijn dan € 3.7, werden rood gemarkeerd en kleiner of gelijk aan € 3.45 - groente.
Opmerking: De schermafbeeldingen voor dit voorbeeld zijn gemaakt in Excel 2010, maar in Excel 2007 en 2013 zijn de knoppen, dialoogvensters en instellingen precies hetzelfde of iets anders.
Dus, hier is wat je stap voor stap moet doen:
- Selecteer de tabel of het bereik waarin u de celvulkleur wilt wijzigen. In dit voorbeeld markeren we $B$2:$H$10 (kolomkoppen en de eerste kolom met de namen van de staten zijn niet geselecteerd).
- Klik op de Home (Home), in de sectie Stijlen (Stijlen) klik Conditionele opmaak (Voorwaardelijke opmaak) > Nieuwe regels (Maak een regel).
- Boven aan het dialoogvenster Nieuwe opmaakregel (Opmaakregel maken) in het veld Selecteer een regeltype (Selecteer regeltype) selecteer Formatteer alleen cellen met (Maak alleen cellen op die bevatten).
- Onderaan het dialoogvenster in het vak Formatteer alleen cellen met (Maak alleen cellen op die aan de volgende voorwaarde voldoen) Stel de voorwaarden voor de regel in. We kiezen ervoor om alleen cellen op te maken met de voorwaarde: Celwaarde (celwaarde) – groter dan (meer) - 3.7zoals weergegeven in de onderstaande afbeelding.Druk dan op de knop Maat (Formaat) om te selecteren welke vulkleur moet worden toegepast als aan de opgegeven voorwaarde wordt voldaan.
- In het dialoogvenster dat verschijnt Cellen opmaken (Cellen opmaken) tabblad Vulling (Vul) en kies een kleur (we kozen voor roodachtig) en klik op OK.
- Daarna keer je terug naar het venster Nieuwe opmaakregel (Een opmaakregel maken) waar in het veld Voorbeschouwing (Voorbeeld) toont een voorbeeld van uw opmaak. Als u tevreden bent, klikt u op OK.
Druk dan op de knop Maat (Formaat) om te selecteren welke vulkleur moet worden toegepast als aan de opgegeven voorwaarde wordt voldaan.
Het resultaat van uw opmaakinstellingen ziet er ongeveer zo uit:
Omdat we een andere voorwaarde moeten instellen waarmee we de vulkleur in groen kunnen veranderen voor cellen met waarden kleiner dan of gelijk aan 3.45en druk nogmaals op de knop Nieuwe regels (Regel maken) en herhaal stap 3 tot 6 om de gewenste regel in te stellen. Hieronder ziet u een voorbeeld van de tweede regel voor voorwaardelijke opmaak die we hebben gemaakt:
Als alles klaar is - klik op OK. Je hebt nu een mooi opgemaakte tabel waarmee je in één oogopslag de maximale en minimale gasprijzen in verschillende staten kunt zien. Goed voor ze daar, in Texas!
Tip: Op dezelfde manier kunt u de letterkleur wijzigen, afhankelijk van de waarde van de cel. Open hiervoor het tabblad doopvont (Lettertype) in het dialoogvenster Cellen opmaken (Cell Format) zoals we in stap 5 hebben gedaan en selecteer de gewenste letterkleur.
Een constante celkleur instellen op basis van de huidige waarde?
Eenmaal ingesteld, verandert de vulkleur niet, ongeacht hoe de inhoud van de cel in de toekomst verandert.
probleem: U wilt de kleur van een cel aanpassen op basis van de huidige waarde en u wilt dat de vulkleur hetzelfde blijft, zelfs als de waarde van de cel verandert.
Besluit: Vind alle cellen met een specifieke waarde (of waarden) met behulp van de tool Vind alle (Alles zoeken) en verander vervolgens de opmaak van de gevonden cellen met behulp van het dialoogvenster Cellen opmaken (celformaat).
Dit is een van die zeldzame taken waarvoor geen uitleg in de Excel-helpbestanden, forums of blogs staat en waarvoor geen directe oplossing bestaat. En dit is begrijpelijk, aangezien deze taak niet typisch is. En toch, als u de celvulkleur permanent moet wijzigen, dat wil zeggen voor eens en voor altijd (of totdat u deze handmatig wijzigt), volgt u deze stappen.
Zoek en selecteer alle cellen die aan een bepaalde voorwaarde voldoen
Hier zijn verschillende scenario's mogelijk, afhankelijk van het type waarde dat u zoekt.
Als u cellen met een bepaalde waarde wilt kleuren, bijvoorbeeld 50, 100 or 3.4 – dan op het tabblad Home (Home) in de sectie Editing (Bewerken) klik Zoek Selecteer (Zoeken en markeren) > VIND DE PLEK DIE PERFECT VOOR JOU IS (Vind).
Voer de gewenste waarde in en klik op Vind alle (Vind alle).
Tip: Aan de rechterkant van het dialoogvenster Zoeken en vervangen (Zoeken en vervangen) er is een knop Opties (Opties), door op te drukken krijgt u toegang tot een aantal geavanceerde zoekinstellingen, zoals: match Case (hoofdlettergevoelig) en Match volledige celinhoud (hele cel). U kunt jokertekens gebruiken, zoals de asterisk (*) om elke tekenreeks te matchen, of het vraagteken (?) om een enkel teken te matchen.
Wat betreft het vorige voorbeeld, als we alle benzineprijzen moeten vinden van 3.7 naar 3.799, dan stellen we de volgende zoekcriteria in:
Klik nu op een van de gevonden items onderaan het dialoogvenster Zoeken en vervangen (Zoeken en vervangen) en klik op Ctrl + Aom alle gevonden items te markeren. Druk daarna op de knop Fermer (Dichtbij).
Zo kunt u alle cellen met een bepaalde waarde (waarden) selecteren met de optie Vind alle (Alles zoeken) in Excel.
In werkelijkheid moeten we echter alle benzineprijzen vinden die hoger zijn dan € 3.7. Helaas de tool Zoeken en vervangen (Find and Replace) kan ons hierbij niet helpen.
Wijzig de vulkleuren van geselecteerde cellen met behulp van het dialoogvenster Cellen opmaken
Je hebt nu alle cellen met de gegeven waarde (of waarden) geselecteerd, we hebben dit zojuist gedaan met de tool Zoeken en vervangen (Zoek en vervang). Het enige dat u hoeft te doen, is de vulkleur voor de geselecteerde cellen instellen.
Een dialoogvenster openen Cellen opmaken (Celformaat) op een van de volgende 3 manieren:
- druk Ctrl + 1.
- door met de rechtermuisknop op een geselecteerde cel te klikken en het item in het contextmenu te selecteren Cellen opmaken (celformaat).
- tab Home (Home) > Cellen. Cellen. (cellen) > Maat (Formaat) > Cellen opmaken (celformaat).
Pas vervolgens de opmaakopties naar wens aan. Deze keer zetten we de vulkleur voor de verandering op oranje
Als u alleen de vulkleur wilt wijzigen zonder de rest van de opmaakopties aan te raken, klikt u op de knop Opvulkleur (Vul kleur) en kies de gewenste kleur.
Hier is het resultaat van onze opmaakwijzigingen in Excel:
In tegenstelling tot de vorige methode (met voorwaardelijke opmaak), zal de op deze manier ingestelde vulkleur nooit zonder uw medeweten veranderen, ongeacht hoe de waarden veranderen.
Wijzig de vulkleur voor speciale cellen (leeg, met een fout in de formule)
Net als in het vorige voorbeeld kunt u de vulkleur van specifieke cellen op twee manieren wijzigen: dynamisch en statisch.
Gebruik een formule om de vulkleur van speciale cellen in Excel te wijzigen
De celkleur verandert automatisch, afhankelijk van de waarde van de cel.
U zult deze methode hoogstwaarschijnlijk in 99% van de gevallen gebruiken om het probleem op te lossen, dat wil zeggen dat de vulling van de cellen zal veranderen in overeenstemming met de door u opgegeven voorwaarde.
Laten we bijvoorbeeld de benzineprijstabel opnieuw nemen, maar deze keer zullen we nog een paar staten toevoegen en enkele cellen leeg maken. Kijk nu hoe u deze lege cellen kunt vinden en hun vulkleur kunt wijzigen.
- Op het tabblad Geavanceerd Home (Home) in de sectie Stijlen (Stijlen) klik Conditionele opmaak (Voorwaardelijke opmaak) > Nieuwe regels (Maak een regel). Net als in de 2e stap van het voorbeeld Hoe dynamisch de kleur van een cel te veranderen op basis van zijn waarde.
- In het dialoogvenster Nieuwe opmaakregel (Maak een opmaakregel) selecteer een optie Gebruik een formule om te bepalen welke cellen formatteren (Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt). Verder het veld in Formatteer waarden waar deze formule waar is (Formaat waarden waarvoor de volgende formule waar is) voer een van de formules in:
- om de vulling van lege cellen te wijzigen
=ISBLANK()=ЕПУСТО() - om de arcering te wijzigen van cellen die formules bevatten die een fout retourneren
=ISERROR()=ЕОШИБКА()
Omdat we de kleur van lege cellen willen veranderen, hebben we de eerste functie nodig. Voer het in, plaats de cursor tussen de haakjes en klik op het bereikselectiepictogram aan de rechterkant van de regel (of typ het gewenste bereik handmatig):
=ISBLANK(B2:H12)=ЕПУСТО(B2:H12) - om de vulling van lege cellen te wijzigen
- Klik op de knop Maat (Formaat), selecteer de gewenste vulkleur op het tabblad Vulling (Vul in), en klik vervolgens op OK. Gedetailleerde instructies worden gegeven in stap 5 van het voorbeeld "De kleur van een cel dynamisch wijzigen op basis van de waarde." Een voorbeeld van de voorwaardelijke opmaak die u instelt, ziet er ongeveer zo uit:
- Als je tevreden bent met de kleur, klik dan op OK. U zult zien hoe de gemaakte regel onmiddellijk op de tabel wordt toegepast.
De vulkleur van speciale cellen statisch wijzigen
Eenmaal geconfigureerd, blijft de vulling ongewijzigd, ongeacht de waarde van de cel.
Als u een permanente vulkleur wilt instellen voor lege cellen of cellen met formules die fouten bevatten, gebruikt u deze methode:
- Selecteer een tabel of bereik en klik op F5om het dialoogvenster te openen gaan naar (Springen), druk dan op de knop special (Markeren).
- In het dialoogvenster Ga naar Special (Selecteer een groep cellen) vink de optie aan blanks (Lege cellen) om alle lege cellen te selecteren.Als u cellen met formules met fouten wilt markeren, vinkt u de optie aan: Formules (formules) > fouten (Fouten). Zoals je op de afbeelding hierboven kunt zien, zijn er nog veel meer instellingen voor je beschikbaar.
- Wijzig ten slotte de vulling van de geselecteerde cellen of stel andere opmaakopties in met behulp van het dialoogvenster Cellen opmaken (Cellen opmaken), zoals beschreven in De vulling van geselecteerde cellen wijzigen.
Als u cellen met formules met fouten wilt markeren, vinkt u de optie aan: Formules (formules) > fouten (Fouten). Zoals je op de afbeelding hierboven kunt zien, zijn er nog veel meer instellingen voor je beschikbaar.Vergeet niet dat op deze manier gemaakte opmaakinstellingen behouden blijven, zelfs wanneer lege cellen worden gevuld met waarden of fouten in formules worden gecorrigeerd. Het is moeilijk voor te stellen dat iemand deze kant op moet, behalve voor de doeleinden van het experiment 🙂