Å legge til betinget formatering i Excel lar deg bruke forskjellige formaterings alternativer på en celle, eller et celleområde, som oppfyller spesifikke betingelser du angir. Å angi slike betingelser kan hjelpe med å organisere regnearket og gjøre det enklere å skanne. Formaterings alternativene du kan bruke inkluderer endringer i skrift- og bakgrunnsfarge, skriftstiler, cellekanter og å legge til tallformatering i data.
Excel har innebygde alternativer for ofte brukte forhold som å finne tall som er større enn eller mindre enn en bestemt verdi eller å finne tall som er over eller under gjennomsnittsverdien. I tillegg til disse forhåndsinnstilte alternativene, kan du også lage tilpassede betingede formateringsregler ved hjelp av Excel-formler.
Disse instruksjonene gjelder for Excel 2019, 2016, 2013, 2010 og Excel for Microsoft 365.
Bruk av flere betingelser i Excel
Du kan bruke mer enn én regel på samme data for å teste for forskjellige forhold. For eksempel kan budsjettdata ha betingelser som gjelder formatendringer når visse utgiftsnivåer er nådd, for eksempel 50 %, 75 % og 100 %, av det totale budsjettet.
Under slike omstendigheter avgjør Excel først om de ulike reglene er i konflikt, og i så fall følger programmet en fast rekkefølge for å bestemme hvilken betinget formateringsregel som skal gjelde for dataene.
Finne data som overstiger 25 % og 50 % økning
I følgende eksempel vil to tilpassede betingede formateringsregler bli brukt på området celler B2 til B5.
- Den første regelen sjekker om dataene i cellene A2:A5 er større enn den tilsvarende verdien i B2:B5 av mer enn 25%.
- Den andre regelen sjekker om de samme dataene i A2:A5 overskrider den tilsvarende verdien i B2:B5 med mer enn 50%.
Som du kan se på bildet ovenfor, hvis en av betingelsene ovenfor er sann, vil bakgrunnsfargen til cellen eller cellene i området B1:B4 endres.
- For data der forskjellen er mer enn 25 %, endres cellebakgrunnsfargen til grønn.
- Hvis forskjellen er større enn 50 %, endres cellebakgrunnsfargen til rød.
Reglene som ble brukt for å utføre denne oppgaven vil bli lagt inn ved hjelp av dialogboksen New Formatting Rule. Begynn med å legge inn eksempeldataene i cellene A1 til C5 som vist på bildet ovenfor.
I den siste delen av opplæringen vil vi legge til formler til cellene C2:C4 som viser den nøyaktige prosentvise forskjellen mellom verdiene i cellene A2:A5 og B2:B5; dette vil tillate oss å sjekke nøyaktigheten til reglene for betinget formatering.
Angi regler for betinget formatering
Først bruker vi betinget formatering for å finne en betydelig økning på 25 prosent eller mer.
Funksjonen vil se slik ut:
=(A2-B2)/A2>25 %
- Høypunkt celler B2 til B5 i regnearket.
- Klikk på Hjem-fanen i ribbon.
- Klikk på Conditional Formatting-ikonet i ribbon for å åpne rullegardinmenyen.
-
Velg Ny regel for å åpne dialogboksen New Formatting Rule.
- Under Velg en regeltype, klikk på det siste alternativet: Bruk en formel for å bestemme hvilke celler som skal formateres.
- Skriv inn formelen nevnt ovenfor i feltet under Formater verdier der denne formelen er sann:
- Klikk Format-knappen for å åpne dialogboksen. Klikk på Fyll-fanen og velg en farge.
- Klikk OK for å lukke dialogboksene og gå tilbake til regnearket.
- Bakgrunnsfargen til cellene B3 og B5 skal endres til fargen du valgte.
Nå bruker vi betinget formatering for å finne en økning på 50 prosent eller mer. Formelen vil se slik ut:
- Gjenta de første fem trinnene ovenfor.
- Skriv inn formula oppgitt ovenfor i feltet under Formater verdier der denne formelen er sann:
- Klikk Format-knappen for å åpne dialogboksen. Klikk på Fyll-fanen og velg en annen farge enn du gjorde i forrige sett med trinn.
- Klikk OK for å lukke dialogboksene og gå tilbake til regnearket.
Bakgrunnsfargen til celle B3 skal forbli den samme, noe som indikerer at prosentforskjellen mellom tallene i cellene A3 ogB3 er større enn 25 prosent, men mindre enn eller lik 50 prosent. Bakgrunnsfargen til celle B5 skal endres til den nye fargen du valgte, noe som indikerer at prosentforskjellen mellom tallene i cellene A5 og B5 er større enn 50 prosent.
Sjekker regler for betinget formatering
For å bekrefte at reglene for betinget formatering er korrekte, kan vi legge inn formler i cellene C2:C5 som vil beregne den nøyaktige prosentforskjellen mellom tallene i områdeneA2:A5 og B2:B5.
Formelen i celle C2 ser slik ut:
=(A2-B2)/A2
- Klikk på celle C2 for å gjøre den til den aktive cellen.
- Skriv inn formelen ovenfor og trykk Enter-tasten på tastaturet.
- Svaret 10 % skal vises i celle C2, noe som indikerer at tallet i celle A2 er 10 % større enn tallet i celle B2.
- Det kan være nødvendig å endre formateringen på celle C2 for å vise svaret som en prosent.
- Bruk fyllhåndtaket for å kopiere formelen fra celle C2 til cells C3 til C5.
- Svarene for cellene C3 til C5 skal være 30 %, 25 % og 60 %.
Svarene i disse cellene viser at reglene for betinget formatering er nøyaktige siden forskjellen mellom cellene A3 og B3 er større enn 25 prosent, og forskjellen mellom cellene A5 og B5 er større enn 50 prosent.
Celle B4 endret ikke farge fordi forskjellen mellom cellene A4 og B4 er lik 25 prosent, og vår betingede formateringsregel spesifiserte at en prosentandel større enn 25 prosent var nødvendig for at bakgrunnsfargen skulle endres.
Rekkefølge for betinget formatering
Når du bruker flere regler på samme dataområde, avgjør Excel først om reglene er i konflikt. Motstridende regler er de der formaterings alternativene ikke begge kan brukes på de samme dataene.
I vårt eksempel er reglene i konflikt siden begge bruker samme formaterings alternativ - endre bakgrunnscellefargen.
I situasjonen der den andre regelen er sann (forskjellen i verdi er mer enn 50 prosent mellom to celler), så er den første regelen (verdiforskjellen er større enn 25 prosent) også sann.
Siden en celle ikke kan ha begge to forskjellige fargebakgrunner samtidig, må Excel vite hvilken betinget formateringsregel den skal gjelde.
Excels prioritetsrekkefølge sier at regelen som er høyere på listen i dialogboksen Conditional Formatting Rules Manager brukes først.
Som vist på bildet ovenfor, er den andre regelen brukt i denne opplæringen høyere på listen og har derfor forrang over den første regelen. Som et resultat er bakgrunnsfargen til celle B5 grønn.
Som standard går nye regler til toppen av listen; for å endre rekkefølgen, bruk Opp og Ned piltastene i dialogboksen.
Bruk av ikke-motstridende regler
Hvis to eller flere betingede formateringsregler ikke er i konflikt, brukes begge når betingelsen hver regel tester blir sann.
Hvis den første betingede formateringsregelen i vårt eksempel formaterte celleområdet B2:B5 med en oransje kantlinje i stedet for en oransje bakgrunnsfarge, ville ikke de to betingede formateringsreglene konflikt siden begge formatene kan brukes uten å forstyrre det andre.
Betinget formatering kontra vanlig formatering
I tilfelle av konflikter mellom betingede formateringsregler og manuelt brukte formaterings alternativer, har betinget formateringsregel alltid forrang og vil bli brukt i stedet for alle manuelt lagt til formaterings alternativer.
