Fremhæv rækker baseret på en celleværdi i Excel (betinget formatering)

Se video - Fremhæv rækker baseret på celleværdier i Excel

Hvis du foretrækker at læse skriftlig instruktion i stedet, er vejledningen herunder.

Betinget formatering giver dig mulighed for at formatere en celle (eller en række celler) baseret på værdien i den.

Men nogle gange, i stedet for bare at få cellen fremhævet, vil du måske fremhæve hele rækken (eller kolonnen) baseret på værdien i en celle.

For at give dig et eksempel har jeg nedenfor et datasæt, hvor jeg har fremhævet alle de rækker, hvor navnet på salgsrepræsentanten er Bob.

I denne vejledning viser jeg dig, hvordan du fremhæver rækker baseret på en celleværdi ved hjælp af betinget formatering ved hjælp af forskellige kriterier.

Klik her for at downloade eksempelfilen og følge med.

Fremhæv rækker baseret på et tekstkriterium

Antag, at du har et datasæt som vist nedenfor, og du vil fremhæve alle de poster, hvor salgsrepræsentanten er Bob.

Her er trinene til at gøre dette:

1. Vælg hele datasættet (A2: F17 i dette eksempel).
2. Klik på fanen Hjem.
3. Klik på Betinget formatering i gruppen Stilarter.
4. Klik på 'Nye regler'.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. I formelfeltet skal du indtaste følgende formel: = $ C2 = ”Bob”
7. Klik på knappen ‘Format’.
8. I den dialogboks, der åbnes, skal du angive den farve, du vil have markeret i rækken.
9. Klik på OK.

Dette vil fremhæve alle de rækker, hvor navnet på salgsrepræsentanten er 'Bob'.

Klik her for at downloade eksempelfilen og følge med.

Hvordan virker det?

Betinget formatering kontrollerer hver celle for den betingelse, vi har angivet, dvs. = $ C2 = ”Bob”

Så når den analyserer hver celle i række A2, vil den kontrollere, om cellen C2 har navnet Bob eller ej. Hvis den gør det, bliver den celle fremhævet, ellers gør den ikke.

Bemærk, at tricket her er at bruge et dollartegn ($) før kolonne -alfabetet ($ C1). Ved at gøre dette har vi låst kolonnen til altid at være C. Så selv når celle A2 kontrolleres for formlen, vil den kontrollere C2, og når A3 er kontrolleret for tilstanden, vil den kontrollere C3.

Dette giver os mulighed for at fremhæve hele rækken ved betinget formatering.

Fremhæv rækker baseret på et talkriterium

I eksemplet ovenfor så vi, hvordan vi kontrollerer et navn og fremhæver hele rækken.

Vi kan bruge den samme metode til også at kontrollere for numeriske værdier og fremhæve rækker baseret på en betingelse.

Antag, at jeg har de samme data (som vist nedenfor), og jeg vil fremhæve alle de rækker, hvor mængden er mere end 15.

Her er trinene til at gøre dette:

1. Vælg hele datasættet (A2: F17 i dette eksempel).
2. Klik på fanen Hjem.
3. Klik på Betinget formatering i gruppen Stilarter.
4. Klik på 'Nye regler'.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. Indtast følgende formel i formelfeltet: = $ D2> = 15
7. Klik på knappen ‘Format’. I den dialogboks, der åbnes, skal du angive den farve, du vil have rækken til at blive fremhævet i.
8. Klik på OK.

Dette vil fremhæve alle de rækker, hvor mængden er mere end eller lig med 15.

På samme måde kan vi også bruge dette til at have kriterier for datoen.

Hvis du f.eks. Vil fremhæve alle de rækker, hvor datoen er efter 10. juli2021-2022, kan du bruge nedenstående datoformel:

= $ A2> DATE (2018,7,10)

Fremhæv rækker baseret på flere kriterier (OG/ELLER)

Du kan også bruge flere kriterier til at markere rækker ved hjælp af betinget formatering.

Hvis du f.eks. Vil fremhæve alle de rækker, hvor salgsrepræsentantens navn er ‘Bob’, og mængden er mere end 10, kan du gøre det ved at følge følgende trin:

1. Vælg hele datasættet (A2: F17 i dette eksempel).
2. Klik på fanen Hjem.
3. Klik på Betinget formatering i gruppen Stilarter.
4. Klik på 'Nye regler'.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. I formelfeltet skal du indtaste følgende formel: = AND ($ C2 = ”Bob”, $ D2> 10)
7. Klik på knappen ‘Format’. I den dialogboks, der åbnes, skal du angive den farve, du vil have rækken til at blive fremhævet i.
8. Klik på OK.

I dette eksempel fremhæves kun de rækker, hvor begge betingelser er opfyldt (dette gøres ved hjælp af AND -formlen).

På samme måde kan du også bruge OR -betingelsen. Hvis du f.eks. Vil fremhæve rækker, hvor enten sælgeren er Bob, eller mængden er mere end 15, kan du bruge nedenstående formel:

= ELLER ($ C2 = "Bob", $ D2> 15)

Klik her for at downloade eksempelfilen og følge med.

Fremhæv rækker i forskellige farver baseret på flere forhold

Nogle gange vil du måske fremhæve rækker i en farve baseret på tilstanden.

For eksempel vil du måske markere alle de rækker, hvor mængden er mere end 20 i grønt, og hvor mængden er mere end 15 (men mindre end 20) i orange.

For at gøre dette skal du oprette to betingede formateringsregler og angive prioriteten.

Her er trinene til at gøre dette:

1. Vælg hele datasættet (A2: F17 i dette eksempel).
2. Klik på fanen Hjem.
3. Klik på Betinget formatering i gruppen Stilarter.
4. Klik på 'Nye regler'.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. I formelfeltet skal du indtaste følgende formel: = $ D2> 15
7. Klik på knappen ‘Format’. Indstil farven til Orange i dialogboksen, der åbnes.
8. Klik på OK.
9. I dialogboksen ‘Conditional Formatting Rules Manager’ skal du klikke på ‘Ny regel’.
10. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
11. I formelfeltet skal du indtaste følgende formel: = $ D2> 20
12. Klik på knappen ‘Format’. Indstil farven til grøn i dialogboksen, der åbnes.
13. Klik på OK.
14. Klik på Anvend (eller OK).

Ovenstående trin ville gøre alle rækker med mængde mere end 20 i grønt og dem med mere end 15 (men mindre end lig med 20 i orange).

Forståelse af reglerne:

Når du bruger flere betingelser, er det vigtigt at sikre, at betingelserne er korrekte.

I ovenstående eksempel er den grønne farvebetingelse over den orange farvebetingelse.

Hvis det er omvendt, ville alle rækker kun være farvet i orange.

Hvorfor?

Fordi en række, hvor mængden er mere end 20 (siger 23) opfylder både vores betingelser (= $ D2> 15 og = $ D2> 20). Og da Orange -tilstanden er øverst, får den præference.

Du kan ændre rækkefølgen af ​​betingelserne ved hjælp af knapperne Flyt op/ned.

Klik her for at downloade eksempelfilen og følge med.

Fremhæv rækker, hvor enhver celle er tom

Hvis du vil markere alle rækker, hvor nogen af ​​cellerne i den er tom, skal du kontrollere for hver celle ved hjælp af betinget formatering.

Her er trinene til at gøre dette:

1. Vælg hele datasættet (A2: F17 i dette eksempel).
2. Klik på fanen Hjem.
3. Klik på Betinget formatering i gruppen Stilarter.
4. Klik på 'Nye regler'.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. I formelfeltet skal du indtaste følgende formel: = COUNTIF ($ A2: $ F2, ””)> 0
7. Klik på knappen ‘Format’. Indstil farven til Orange i dialogboksen, der åbnes.
8. Klik på OK.

Ovenstående formel tæller antallet af tomme celler. Hvis resultatet er mere end 0, betyder det, at der er tomme celler i den række.

Hvis nogen af ​​cellerne er tomme, fremhæver det hele rækken.

Fremhæv rækker baseret på valg af drop -down

I de hidtil dækkede eksempler blev alle betingelserne angivet med dialogboksen Betinget formatering.

I denne del af selvstudiet vil jeg vise dig, hvordan du gør den dynamisk (så du kan indtaste betingelsen i en celle i Excel, og den vil automatisk fremhæve rækkerne baseret på den).

Nedenfor er et eksempel, hvor jeg vælger et navn fra rullemenuen, og alle rækker med det navn bliver fremhævet:

Her er trinene til at oprette dette:

1. Opret en rulleliste i celle A2. Her har jeg brugt navnene på salgsrepræsentanten til at oprette rullelisten. Her er en detaljeret guide til, hvordan du opretter en rulleliste i Excel.
2. Vælg hele datasættet (C2: H17 i dette eksempel).
3. Klik på fanen Hjem.
4. Klik på Betinget formatering i gruppen Stilarter.
5. Klik på 'Nye regler'.
6. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
7. I formelfeltet skal du indtaste følgende formel: = $ E2 = $ A $ 2
8. Klik på knappen ‘Format’. Indstil farven til Orange i dialogboksen, der åbnes.
9. Klik på OK.

Når du nu vælger et hvilket som helst navn fra rullemenuen, fremhæver det automatisk de rækker, hvor navnet er det samme, som du har valgt fra rullemenuen.

Er du interesseret i at lære mere om, hvordan du søger og fremhæver i Excel? Tjek nedenstående videoer.