Betinget formatering i Excel: Den ultimative vejledning med eksempler

Betinget formatering er en af ​​de mest enkle, men kraftfulde funktioner i Excel -regneark.

Som navnet antyder, kan du bruge betinget formatering i Excel, når du vil fremhæve celler, der opfylder en bestemt betingelse.

Det giver dig mulighed for hurtigt at tilføje et visuelt analyselag over dit datasæt. Du kan oprette varmekort, vise stigende/faldende ikoner, Harvey -bobler og meget mere ved hjælp af betinget formatering i Excel.

Brug af betinget formatering i Excel (eksempler)

I denne vejledning viser jeg dig syv fantastiske eksempler på brug af betinget formatering i Excel:

• Identificer hurtigt dubletter ved hjælp af betinget formatering i Excel.
• Fremhæv celler med værdi større/mindre end et tal i et datasæt.
• Fremhævning af top/bund 10 (eller 10%) værdier i et datasæt.
• Fremhævning af fejl/emner ved hjælp af betinget formatering i Excel.
• Oprettelse af varmekort ved hjælp af betinget formatering i Excel.
• Fremhæv hver niende række/kolonne ved hjælp af betinget formatering.
• Søg og fremhæv ved hjælp af betinget formatering i Excel.

1. Identificer hurtigt dubletter

Betinget formatering i Excel kan bruges til at identificere dubletter i et datasæt.

Sådan kan du gøre dette:

• Vælg det datasæt, hvor du vil fremhæve dubletter.
• Gå til Hjem -> Betinget formatering -> Fremhævning af celleregler -> Duplikerede værdier.
• I dialogboksen Dupliker værdier skal du sørge for, at Duplikat er valgt i venstre rullemenu. Du kan angive det format, der skal anvendes, ved at bruge den højre rullemenu. Der er nogle eksisterende formater, som du kan bruge, eller angiv dit eget format ved hjælp af indstillingen Tilpasset format.
• Klik på OK.

Dette vil øjeblikkeligt fremhæve alle de celler, der har en dublet i det valgte datasæt. Dit datasæt kan være i en enkelt kolonne, flere kolonner eller i et ikke-sammenhængende celleområde.

Se også: Den ultimative guide til at finde og fjerne dubletter i Excel.

2. Fremhæv celler med værdi større/mindre end et tal

Du kan bruge betinget formatering i Excel til hurtigt at fremhæve celler, der indeholder værdier større/mindre end en angivet værdi. For eksempel at fremhæve alle celler med en salgsværdi på mindre end 100 millioner eller fremhæve celler med mærker, der er mindre end grænseværdien.

Her er trinene til at gøre dette:

• Vælg hele datasættet.
• Gå til Hjem -> Betinget formatering -> Fremhævelse af celleregler -> Større end … / Mindre end …
• Baseret på hvilken mulighed du vælger (større end eller mindre end), åbnes en dialogboks. Lad os sige, at du vælger indstillingen 'Større end'. Indtast nummeret i feltet til venstre i dialogboksen. Hensigten er at fremhæve celler, der har et tal større end dette angivne tal.

• Angiv det format, der skal anvendes på de celler, der opfylder betingelsen, ved hjælp af rullemenuen til højre. Der er nogle eksisterende formater, som du kan bruge, eller angiv dit eget format ved hjælp af indstillingen Tilpasset format.
• Klik på OK.

Dette ville øjeblikkeligt fremhæve alle cellerne med værdier større end 5 i et datasæt.Bemærk: Hvis du ønsker at fremhæve værdier, der er større end lig med 5, skal du anvende betinget formatering igen med kriterierne "Lig med".

Den samme proces kan følges for at markere celler med en værdi mindre end en specificeret værdi.

3. Fremhævning Top/Bottom 10 (eller 10%)

Betinget formatering i Excel kan hurtigt identificere top 10 -elementer eller top 10% fra et datasæt. Dette kan være nyttigt i situationer, hvor du hurtigt vil se de bedste kandidater efter scoringer eller tophandelsværdier i salgsdataene.

På samme måde kan du også hurtigt identificere de nederste 10 elementer eller de nederste 10% i et datasæt.

Her er trinene til at gøre dette:

• Vælg hele datasættet.
• Gå til Hjem -> Betinget formatering -> Top / bundregler -> Top 10 elementer (eller %) / nederste 10 elementer (eller %).
• Baseret på hvad du vælger, åbner det dialogboksen. Lad os sige, at du har valgt Top 10 -elementer, så åbner det en dialogboks som vist nedenfor:
• Angiv det format, der skal anvendes på de celler, der opfylder betingelsen, ved hjælp af rullemenuen til højre. Der er nogle eksisterende formater, som du kan bruge, eller angiv dit eget format ved hjælp af indstillingen Tilpasset format.
• Klik på OK.

Dette ville øjeblikkeligt fremhæve de 10 bedste elementer i det valgte datasæt. Bemærk, at dette kun fungerer for celler, der har en numerisk værdi i det.

Hvis du også har mindre end 10 celler i datasættet, og du vælger mulighederne for at fremhæve Top 10 -emner/nederste 10 elementer, så vil alle celler blive fremhævet.

Her er nogle eksempler på, hvordan den betingede formatering ville fungere:

4. Fremhævning af fejl/emner

Hvis du arbejder med mange numeriske data og beregninger i Excel, kender du vigtigheden af ​​at identificere og behandle celler, der har fejl eller er tomme. Hvis disse celler bruges i yderligere beregninger, kan det føre til fejlagtige resultater.

Betinget formatering i Excel kan hjælpe dig med hurtigt at identificere og fremhæve celler, der har fejl eller er tomme.

Antag, at vi har et datasæt som vist herunder:

Dette datasæt har en tom celle (A4) og fejl (A5 og A6).

Her er trin til at markere de celler, der er tomme eller har fejl i den:

• Vælg det datasæt, hvor du vil markere tomme celler og celler med fejl.
• Gå til Hjem -> Betinget formatering -> Ny regel.
• Vælg Brug en formel i dialogboksen Ny formateringsregel til at bestemme, hvilke celler der skal formateres.
• Indtast følgende formel i feltet i afsnittet ‘Rediger regelbeskrivelsen’:
  = ELLER (ISBLANK (A1), FEJL (A1))
  • Ovenstående formel kontrollerer alle celler for to betingelser - om den er blank eller ej, og om den har en fejl eller ej. Hvis nogen af ​​betingelserne er SAND, returnerer den SAND.
• Indstil det format, du vil anvende på de celler, der er tomme eller har fejl. For at gøre dette skal du klikke på knappen Format. Det åbner dialogboksen 'Formatceller', hvor du kan angive formatet.
• Klik på Ok.

Dette vil øjeblikkeligt fremhæve alle de celler, der enten er tomme eller har fejl i den.

Bemærk: Du behøver ikke at bruge hele området A1: A7 i formlen i betinget formatering. Ovenstående formel bruger kun A1. Når du anvender denne formel på hele området, kontrollerer Excel en celle ad gangen og justerer referencen. Når den f.eks. Kontrollerer A1, bruger den formlen = OR (ISBLANK (A1), ISERROR (A1)). Når den kontrollerer celle A2, bruger den derefter formlen = ELLER (ISBLANK (A2), FEJL (A2)). Den justerer automatisk referencen (da disse er relative referencer) afhængigt af hvilken celle der analyseres. Så du behøver ikke skrive en separat formel for hver celle. Excel er smart nok til at ændre cellereferencen helt af sig selv 🙂

Se også: Brug af IFERROR og ISERROR til at håndtere fejl i excel.

5. Oprettelse af varmekort

Et varmekort er en visuel repræsentation af data, hvor farven repræsenterer værdien i en celle. For eksempel kan du oprette et varmekort, hvor en celle med den højeste værdi er farvet grøn, og der er et skift til rød farve, når værdien falder.

Noget som vist herunder:

Ovenstående datasæt har værdier mellem 1 og 100. Celler fremhæves baseret på værdien i det. 100 får den grønne farve, 1 får den røde farve.

Her er trinene til at oprette varmekort ved hjælp af betinget formatering i Excel.

• Vælg datasættet.
• Gå til Hjem -> Betinget formatering -> Farveskalaer, og vælg et af farveskemaerne.

Så snart du klikker på varmekortikonet, vil det anvende formateringen på datasættet. Der er flere farveforløb, du kan vælge imellem. Hvis du ikke er tilfreds med de eksisterende farveindstillinger, kan du vælge flere regler og angive den farve, du ønsker.

Bemærk: På lignende måde kan du også anvende Data Bard og Ikonsæt.

6. Fremhæv hver anden række/kolonne

Det kan være en god idé at fremhæve alternative rækker for at øge læsbarheden af ​​dataene.

Disse kaldes zebralinjer og kan især være nyttige, hvis du udskriver dataene.

Nu er der to måder at oprette disse zebralinjer på. Den hurtigste måde er at konvertere dine tabeldata til en Excel -tabel. Det anvendte automatisk en farve på alternative rækker. Du kan læse mere om det her.

En anden måde er at bruge betinget formatering.

Antag, at du har et datasæt som vist herunder:

Her er trinene til at fremhæve alternative rækker ved hjælp af betinget formatering i Excel.

• Vælg datasættet. I eksemplet ovenfor skal du vælge A2: C13 (som ekskluderer overskriften). Hvis du også vil inkludere overskriften, skal du vælge hele datasættet.
• Åbn dialogboksen Betinget formatering (Hjem-> Betinget formatering-> Ny regel) [Tastaturgenvej - Alt + O + D].
• Vælg dialogboksen 'Brug en formel til at bestemme, hvilke celler der skal formateres' i dialogboksen.
• Indtast følgende formel i feltet i afsnittet ‘Rediger regelbeskrivelsen’:
  = ISODD (RÆKKE ())

• Ovenstående formel kontrollerer alle cellerne, og hvis Rækketallet i en celle er ulige, returnerer den SAND. Det angivne betingede format vil blive anvendt på alle de celler, der returnerer SAND.
• Indstil det format, du vil anvende på de celler, der er tomme eller har fejl. For at gøre dette skal du klikke på knappen Format. Det åbner dialogboksen 'Formatceller', hvor du kan angive formatet.
• Klik på OK.

Det er det! De alternative rækker i datasættet vil blive fremhævet.

Du kan bruge den samme teknik i mange tilfælde. Alt du skal gøre er at bruge den relevante formel i den betingede formatering. Her er nogle eksempler:

• Fremhæv alternative lige rækker: = ISEVEN (ROW ())
• Fremhæv alternative tilføjelsesrækker: = ISODD (ROW ())
• Fremhæv hver 3. række: = MOD (Række (), 3) = 0

7. Søg og fremhæv data ved hjælp af betinget formatering

Denne er lidt avanceret brug af betinget formatering. Det ville få dig til at ligne en Excel rockstar.

Antag, at du har et datasæt som vist herunder med produktnavn, salgsrepræsentant og geografi. Ideen er at skrive en streng i celle C2, og hvis den matcher med dataene i en eller flere celle (r), bør det blive fremhævet. Noget som vist herunder:

Her er trinene til at oprette denne søge- og fremhævningsfunktionalitet:

• Vælg datasættet.
• Gå til Hjem -> Betinget formatering -> Ny regel (Tastaturgenvej - Alt + O + D).
• I dialogboksen Ny formateringsregel skal du vælge indstillingen 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
• Indtast følgende formel i feltet i afsnittet ‘Rediger regelbeskrivelsen’:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Indstil det format, du vil anvende på de celler, der er tomme eller har fejl. For at gøre dette skal du klikke på knappen Format. Det åbner dialogboksen 'Formatceller', hvor du kan angive formatet.
• Klik på OK.

Det er det! Når du nu indtaster noget i celle C2 og trykker på enter, fremhæver det alle de matchende celler.

Hvordan virker det?

Formlen, der bruges i betinget formatering, evaluerer alle cellerne i datasættet. Lad os sige, at du indtaster Japan i celle C2. Nu ville Excel evaluere formlen for hver celle.

Formlen ville returnere SAND for en celle, når to betingelser er opfyldt:

• Celle C2 er ikke tom.
• Indholdet i celle C2 matcher nøjagtigt celleindholdet i datasættet.

Derfor fremhæves alle de celler, der indeholder teksten Japan.

Download eksempelfilen

Du kan bruge den samme logik til at oprette variationer som:

• Fremhæv hele rækken i stedet for en celle.
• Fremhæv, selvom der er en delvis match.
• Fremhæv cellerne/rækkerne, mens du skriver (dynamisk) [Du kommer til at elske dette trick :)].

Sådan fjernes betinget formatering i Excel

Når den er anvendt, forbliver betinget formatering på plads, medmindre du fjerner den manuelt. Som en bedste praksis skal du beholde den betingede formatering kun anvendt på de celler, hvor du har brug for det.

Da det er flygtigt, kan det føre til en langsom Excel -projektmappe.

Sådan fjernes betinget formatering:

• Vælg de celler, som du vil fjerne betinget formatering fra.
• Gå til Hjem -> Betinget formatering -> Ryd regler -> Ryd regler fra udvalgte celler.
  • Hvis du vil fjerne betinget formatering fra hele regnearket, skal du vælge Ryd regler fra hele arket.

Vigtige ting at vide om betinget formatering i Excel

• Betinget formatering i flygtig. Det kan føre til en langsom arbejdsbog. Brug det kun når det er nødvendigt.
• Når du kopierer indsæt celler, der indeholder betinget formatering, kopieres betinget formatering også.
• Hvis du anvender flere regler på det samme sæt celler, forbliver alle regler aktive. I tilfælde af overlapning gives den sidst anvendte regel fortrinsret. Du kan dog ændre rækkefølgen ved at ændre rækkefølgen fra dialogboksen Administrer regler.