Betinget formatering i Excel: Den ultimate guiden med eksempler

Betinget formatering er en av de mest enkle, men kraftige funksjonene i Excel -regneark.

Som navnet antyder, kan du bruke betinget formatering i Excel når du vil markere celler som oppfyller en spesifisert betingelse.

Det gir deg muligheten til raskt å legge til et visuelt analyselag over datasettet ditt. Du kan lage varmekart, vise økende/synkende ikoner, Harvey -bobler og mye mer ved å bruke betinget formatering i Excel.

Bruke betinget formatering i Excel (eksempler)

I denne opplæringen vil jeg vise deg syv fantastiske eksempler på bruk av betinget formatering i Excel:

• Identifiser duplikater raskt ved hjelp av betinget formatering i Excel.
• Marker celler med verdi større/mindre enn et tall i et datasett.
• Fremhever topp/bunn 10 (eller 10%) verdier i et datasett.
• Uthev feil/blanke ved hjelp av betinget formatering i Excel.
• Opprette varmekart ved bruk av betinget formatering i Excel.
• Merk hver niende rad/kolonne ved hjelp av betinget formatering.
• Søk og uthev ved hjelp av betinget formatering i Excel.

1. Identifiser duplikater raskt

Betinget formatering i Excel kan brukes til å identifisere duplikater i et datasett.

Slik kan du gjøre dette:

• Velg datasettet der du vil markere duplikater.
• Gå til Hjem -> Betinget formatering -> Fremheve celleregler -> Dupliser verdier.
• I dialogboksen Dupliser verdier, kontroller at Dupliser er valgt i venstre rullegardinmeny. Du kan angi formatet som skal brukes ved å bruke høyre rullegardinmeny. Det er noen eksisterende formater du kan bruke, eller spesifiser ditt eget format ved hjelp av alternativet Tilpasset format.
• Klikk OK.

Dette vil umiddelbart markere alle cellene som har en duplikat i det valgte datasettet. Datasettet ditt kan være i en enkelt kolonne, flere kolonner eller i et ikke-sammenhengende celleområde.

Se også: Den ultimate guiden for å finne og fjerne dubletter i Excel.

2. Marker celler med verdi større/mindre enn et tall

Du kan bruke betinget formatering i Excel til raskt å markere celler som inneholder verdier større/mindre enn en spesifisert verdi. For eksempel å markere alle celler med en salgsverdi på mindre enn 100 millioner, eller å markere celler med merker som er lavere enn terskelen.

Her er trinnene for å gjøre dette:

• Velg hele datasettet.
• Gå til Hjem -> Betinget formatering -> Fremheve celleregler -> Større enn … / Mindre enn …
• Basert på hvilket alternativ du velger (større enn eller mindre enn), åpnes en dialogboks. La oss si at du velger alternativet 'Større enn'. Skriv inn nummeret i feltet til venstre i dialogboksen. Hensikten er å markere celler som har et tall større enn dette angitte tallet.

• Angi formatet som skal brukes på cellene som oppfyller betingelsen ved å bruke rullegardinmenyen til høyre. Det er noen eksisterende formater du kan bruke, eller spesifiser ditt eget format ved hjelp av alternativet Tilpasset format.
• Klikk OK.

Dette vil umiddelbart markere alle cellene med verdier større enn 5 i et datasett.Merk: Hvis du ønsker å markere verdier større enn lik 5, bør du bruke betinget formatering igjen med kriteriene "Lik til".

Den samme prosessen kan følges for å markere celler med en verdi mindre enn en spesifisert verdi.

3. Fremhever topp/bunn 10 (eller 10%)

Betinget formatering i Excel kan raskt identifisere topp 10 -elementene eller topp 10% fra et datasett. Dette kan være nyttig i situasjoner der du raskt vil se de beste kandidatene etter poengsum eller topp avtaleverdier i salgsdataene.

På samme måte kan du også raskt identifisere de 10 nederste elementene eller de nederste 10% i et datasett.

Her er trinnene for å gjøre dette:

• Velg hele datasettet.
• Gå til Hjem -> Betinget formatering -> Topp / bunnregler -> Topp 10 elementer (eller %) / nederste 10 elementer (eller %).
• Basert på det du velger, åpnes dialogboksen. La oss si at du valgte Topp 10 -elementene, så åpnet den en dialogboks som vist nedenfor:
• Angi formatet som skal brukes på cellene som oppfyller betingelsen ved å bruke rullegardinmenyen til høyre. Det er noen eksisterende formater du kan bruke, eller spesifiser ditt eget format ved hjelp av alternativet Tilpasset format.
• Klikk OK.

Dette vil umiddelbart markere de 10 beste elementene i det valgte datasettet. Vær oppmerksom på at dette bare fungerer for celler som har en numerisk verdi.

Hvis du også har mindre enn 10 celler i datasettet, og du velger alternativene for å markere Topp 10 elementer/Nederste 10 elementer, så vil alle cellene bli uthevet.

Her er noen eksempler på hvordan den betingede formateringen ville fungere:

4. Uthevende feil/blanks

Hvis du jobber med mange numeriske data og beregninger i Excel, vet du viktigheten av å identifisere og behandle celler som har feil eller er tomme. Hvis disse cellene brukes i ytterligere beregninger, kan det føre til feilaktige resultater.

Betinget formatering i Excel kan hjelpe deg med raskt å identifisere og markere celler som har feil eller er tomme.

Anta at vi har et datasett som vist nedenfor:

Dette datasettet har en tom celle (A4) og feil (A5 og A6).

Her er trinn for å markere cellene som er tomme eller har feil i den:

• Velg datasettet der du vil markere tomme celler og celler med feil.
• Gå til Hjem -> Betinget formatering -> Ny regel.
• Velg Bruk en formel i dialogboksen Ny formateringsregel for å bestemme hvilke celler som skal formateres.
• Skriv inn følgende formel i feltet i "Rediger regelbeskrivelsen" -delen:
  = ELLER (ISBLANK (A1), FEIL (A1))
  • Formelen ovenfor kontrollerer alle cellene for to forhold - om den er blank eller ikke, og om den har en feil eller ikke. Hvis noen av betingelsene er SANN, returnerer den SANN.
• Angi formatet du vil bruke på cellene som er tomme eller har feil. For å gjøre dette, klikk på Format -knappen. Den åpner dialogboksen "Formatceller", hvor du kan angi formatet.
• Klikk Ok.

Dette vil umiddelbart markere alle cellene som enten er tomme eller har feil i den.

Merk: Du trenger ikke å bruke hele området A1: A7 i formelen i betinget formatering. Ovennevnte formel bruker bare A1. Når du bruker denne formelen for hele området, kontrollerer Excel en celle om gangen og justerer referansen. For eksempel, når den sjekker A1, bruker den formelen = ELLER (ISBLANK (A1), ISERROR (A1)). Når den sjekker celle A2, bruker den formelen = ELLER (ISBLANK (A2), FEIL (A2)). Den justerer automatisk referansen (ettersom disse er relative referanser) avhengig av hvilken celle som analyseres. Så du trenger ikke skrive en egen formel for hver celle. Excel er smart nok til å endre cellereferansen helt av seg selv 🙂

Se også: Bruker IFERROR og ISERROR for å håndtere feil i excel.

5. Opprette varmekart

Et varmekart er en visuell representasjon av data der fargen representerer verdien i en celle. For eksempel kan du lage et varmekart der en celle med den høyeste verdien er farget grønn, og det er et skifte mot rød farge når verdien synker.

Noe som vist nedenfor:

Datasettet ovenfor har verdier mellom 1 og 100. Celler utheves basert på verdien i det. 100 får den grønne fargen, 1 får den røde fargen.

Her er trinnene for å lage varmekart ved bruk av betinget formatering i Excel.

• Velg datasettet.
• Gå til Hjem -> Betinget formatering -> Fargeskalaer, og velg ett av fargevalgene.

Så snart du klikker på varmekartikonet, vil det bruke formateringen på datasettet. Det er flere fargegradienter du kan velge mellom. Hvis du ikke er fornøyd med de eksisterende fargevalgene, kan du velge flere regler og angi fargen du vil ha.

Merk: På lignende måte kan du også bruke datasett og datasett.

6. Marker annenhver rad/kolonne

Det kan være lurt å markere alternative rader for å øke lesbarheten til dataene.

Disse kalles sebralinjer og kan være spesielt nyttig hvis du skriver ut dataene.

Nå er det to måter å lage disse sebralinjene på. Den raskeste måten er å konvertere tabelldataene til en Excel -tabell. Den brukte automatisk en farge på alternative rader. Du kan lese mer om det her.

En annen måte er å bruke betinget formatering.

Anta at du har et datasett som vist nedenfor:

Her er trinnene for å markere alternative rader ved bruk av betinget formatering i Excel.

• Velg datasettet. I eksemplet ovenfor velger du A2: C13 (som ekskluderer overskriften). Hvis du også vil inkludere overskriften, velger du hele datasettet.
• Åpne dialogboksen Betinget formatering (Hjem-> Betinget formatering-> Ny regel) [Tastaturgenvei - Alt + O + D].
• Velg dialogboksen "Bruk en formel for å bestemme hvilke celler som skal formateres" i dialogboksen.
• Skriv inn følgende formel i feltet i "Rediger regelbeskrivelsen" -delen:
  = ISODD (RAD ())

• Formelen ovenfor sjekker alle cellene, og hvis RAD -nummeret i en celle er oddet, returnerer den SANN. Det angitte betingede formatet vil bli brukt på alle cellen som returnerer TRUE.
• Angi formatet du vil bruke på cellene som er tomme eller har feil. For å gjøre dette, klikk på Format -knappen. Den åpner dialogboksen "Formatceller", der du kan angi formatet.
• Klikk OK.

Det er det! De alternative radene i datasettet vil bli uthevet.

Du kan bruke samme teknikk i mange tilfeller. Alt du trenger å gjøre er å bruke den relevante formelen i den betingede formateringen. Her er noen eksempler:

• Marker alternative jevne rader: = ISEVEN (ROW ())
• Marker alternative leggingsrader: = ISODD (RAD ())
• Merk hver 3. rad: = MOD (RAD (), 3) = 0

7. Søk og uthev data ved hjelp av betinget formatering

Denne er litt avansert bruk av betinget formatering. Det ville få deg til å se ut som en Excel rockstar.

Anta at du har et datasett som vist nedenfor, med produktnavn, salgsrepresentant og geografi. Tanken er å skrive en streng i celle C2, og hvis den samsvarer med dataene i en eller flere celler, bør det bli uthevet. Noe som vist nedenfor:

Her er trinnene for å lage denne søke- og uthevingsfunksjonaliteten:

• Velg datasettet.
• Gå til Hjem -> Betinget formatering -> Ny regel (Tastaturgenvei - Alt + O + D).
• I dialogboksen Ny formateringsregel velger du alternativet 'Bruk en formel for å bestemme hvilke celler som skal formateres'.
• Skriv inn følgende formel i feltet i "Rediger regelbeskrivelsen" -delen:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Angi formatet du vil bruke på cellene som er tomme eller har feil. For å gjøre dette, klikk på Format -knappen. Den åpner dialogboksen "Formatceller", der du kan angi formatet.
• Klikk OK.

Det er det! Når du skriver inn noe i celle C2 og trykker på enter, vil det markere alle matchende celler.

Hvordan virker dette?

Formelen som brukes i betinget formatering evaluerer alle cellene i datasettet. La oss si at du kommer inn i Japan i celle C2. Nå ville Excel evaluere formelen for hver celle.

Formelen vil returnere TRUE for en celle når to betingelser er oppfylt:

• Celle C2 er ikke tom.
• Innholdet i celle C2 samsvarer nøyaktig med innholdet i cellen i datasettet.

Derfor blir alle cellene som inneholder teksten Japan uthevet.

Last ned eksempelfilen

Du kan bruke den samme logikken til å lage varianter som:

• Merk hele raden i stedet for en celle.
• Marker selv om det er en delvis kamp.
• Merk cellene/radene mens du skriver (dynamisk) [Du kommer til å elske dette trikset :)].

Slik fjerner du betinget formatering i Excel

Når den er brukt, forblir betinget formatering på plass med mindre du fjerner den manuelt. Som en god praksis, behold den betingede formateringen bare for cellene der du trenger det.

Siden det er flyktig, kan det føre til en treg Excel -arbeidsbok.

Slik fjerner du betinget formatering:

• Velg cellene du vil fjerne betinget formatering fra.
• Gå til Hjem -> Betinget formatering -> Slett regler -> Fjern regler fra utvalgte celler.
  • Hvis du vil fjerne betinget formatering fra hele regnearket, velger du Fjern regler fra hele arket.

Viktige ting å vite om betinget formatering i Excel

• Betinget formatering i flyktig. Det kan føre til en treg arbeidsbok. Bruk den bare når det er nødvendig.
• Når du kopierer lim inn celler som inneholder betinget formatering, blir betinget formatering også kopiert.
• Hvis du bruker flere regler på det samme settet med celler, forblir alle reglene aktive. I tilfelle overlapping, får regelen som ble brukt sist. Du kan imidlertid endre rekkefølgen ved å endre rekkefølgen fra dialogboksen Administrer regler.