Villkorlig formatering i Excel: Den ultimata guiden med exempel

Villkorlig formatering är en av de mest enkla men ändå kraftfulla funktionerna i Excel -kalkylblad.

Som namnet antyder kan du använda villkorlig formatering i Excel när du vill markera celler som uppfyller ett visst villkor.

Det ger dig möjlighet att snabbt lägga till ett visuellt analysskikt över din datamängd. Du kan skapa värmekartor, visa ökande/minskande ikoner, Harvey -bubblor och mycket mer med villkorlig formatering i Excel.

Använda villkorlig formatering i Excel (exempel)

I den här självstudien visar jag dig sju fantastiska exempel på användning av villkorlig formatering i Excel:

• Identifiera snabbt dubbletter med villkorlig formatering i Excel.
• Markera celler med värde större/mindre än ett tal i en datamängd.
• Markera topp/botten 10 (eller 10%) värden i en datauppsättning.
• Markera fel/ämnen med villkorlig formatering i Excel.
• Skapa värmekartor med villkorlig formatering i Excel.
• Markera var tionde rad/kolumn med villkorlig formatering.
• Sök och markera med villkorlig formatering i Excel.

1. Identifiera snabbt dubbletter

Villkorlig formatering i Excel kan användas för att identifiera dubbletter i en datamängd.

Så här kan du göra detta:

• Välj datauppsättningen där du vill markera dubbletter.
• Gå till Hem -> Villkorlig formatering -> Markera cellregler -> Dubbla värden.
• I dialogrutan Dubbla värden, se till att duplicera är markerat i den vänstra rullgardinsmenyn. Du kan ange formatet som ska tillämpas genom att använda höger rullgardinsmeny. Det finns några befintliga format som du kan använda, eller ange ditt eget format med alternativet Anpassat format.
• Klicka på OK.

Detta skulle omedelbart markera alla celler som har en duplikat i den valda datamängden. Datauppsättningen kan finnas i en enda kolumn, flera kolumner eller i ett icke-sammanhängande cellområde.

Se även: Den ultimata guiden för att hitta och ta bort dubbletter i Excel.

2. Markera celler med värde större/mindre än ett tal

Du kan använda villkorlig formatering i Excel för att snabbt markera celler som innehåller värden större/mindre än ett visst värde. Till exempel att markera alla celler med försäljningsvärde mindre än 100 miljoner, eller markera celler med märken som är lägre än passeringströskeln.

Här är stegen för att göra detta:

• Välj hela datauppsättningen.
• Gå till Hem -> Villkorlig formatering -> Markera cellregler -> Större än … / Mindre än …
• Baserat på vilket alternativ du väljer (större än eller mindre än), öppnas en dialogruta. Låt oss säga att du väljer alternativet "Större än". Ange numret i fältet till vänster i dialogrutan. Avsikten är att markera celler som har ett tal som är större än det angivna talet.

• Ange formatet som ska tillämpas på cellerna som uppfyller villkoret med hjälp av rullgardinsmenyn till höger. Det finns några befintliga format som du kan använda, eller ange ditt eget format med alternativet Anpassat format.
• Klicka på OK.

Detta skulle omedelbart markera alla celler med värden större än 5 i en datamängd.Obs! Om du vill markera värden som är större än lika med 5, bör du använda villkorlig formatering igen med kriterierna ”Lika med”.

Samma process kan följas för att markera celler med ett värde mindre än ett angivet värde.

3. Markera Top/Bottom 10 (eller 10%)

Villkorsstyrd formatering i Excel kan snabbt identifiera topp 10 -artiklar eller topp 10% från en datamängd. Detta kan vara till hjälp i situationer där du snabbt vill se de bästa kandidaterna efter poäng eller toppaffärsvärden i försäljningsdata.

På samma sätt kan du också snabbt identifiera de 10 nedre objekten eller de nedersta 10% i en datamängd.

Här är stegen för att göra detta:

• Välj hela datauppsättningen.
• Gå till Hem -> Villkorlig formatering -> Övre / nedre regler -> Topp 10 artiklar (eller %) / Nedersta 10 objekt (eller %).
• Baserat på vad du väljer, öppnas dialogrutan. Låt oss säga att du valde de 10 bästa artiklarna, då öppnade den en dialogruta enligt nedan:
• Ange formatet som ska tillämpas på cellerna som uppfyller villkoret med hjälp av rullgardinsmenyn till höger. Det finns några befintliga format som du kan använda, eller ange ditt eget format med alternativet Anpassat format.
• Klicka på OK.

Detta skulle omedelbart markera de 10 bästa objekten i den valda datamängden. Observera att detta bara fungerar för celler som har ett numeriskt värde.

Om du också har mindre än 10 celler i datamängden och du väljer alternativen för att markera topp 10 -objekt/nedre 10 -objekt, kommer alla celler att markeras.

Här är några exempel på hur den villkorade formateringen skulle fungera:

4. Markera fel/ämnen

Om du arbetar med många numeriska data och beräkningar i Excel vet du vikten av att identifiera och behandla celler som har fel eller är tomma. Om dessa celler används i ytterligare beräkningar kan det leda till felaktiga resultat.

Villkorlig formatering i Excel kan hjälpa dig att snabbt identifiera och markera celler som har fel eller är tomma.

Anta att vi har en datauppsättning som visas nedan:

Denna datamängd har en tom cell (A4) och fel (A5 och A6).

Här är steg för att markera de celler som är tomma eller har fel i dem:

• Välj datauppsättningen där du vill markera tomma celler och celler med fel.
• Gå till Hem -> Villkorlig formatering -> Ny regel.
• I dialogrutan Ny formateringsregel väljer du Använd en formel för att avgöra vilka celler som ska formateras.
• Ange följande formel i fältet i avsnittet ‘Redigera regelbeskrivning’:
  = ELLER (ISBLANK (A1), FEL (A1))
  • Ovanstående formel kontrollerar alla celler för två villkor - om den är tom eller inte och om den har ett fel eller inte. Om något av villkoren är SANT returnerar det SANT.
• Ange det format som du vill använda för de celler som är tomma eller har fel. För att göra detta, klicka på knappen Format. Det öppnar dialogrutan "Formatera celler", där du kan ange formatet.
• Klicka på Ok.

Detta skulle omedelbart markera alla celler som antingen är tomma eller har fel i den.

Notera: Du behöver inte använda hela intervallet A1: A7 i formeln i villkorlig formatering. Ovanstående formel använder endast A1. När du tillämpar denna formel på hela intervallet kontrollerar Excel en cell i taget och justerar referensen. Till exempel, när den kontrollerar A1, använder den formeln = ELLER (ISBLANK (A1), FEL (A1)). När den kontrollerar cell A2 använder den formeln = ELLER (ISBLANK (A2), FEL (A2)). Den justerar automatiskt referensen (eftersom dessa är relativa referenser) beroende på vilken cell som analyseras. Så du behöver inte skriva en separat formel för varje cell. Excel är tillräckligt smart för att ändra cellreferensen helt själv 🙂

Se även: Använda IFERROR och ISERROR för att hantera fel i excel.

5. Skapa värmekartor

En värmekarta är en visuell representation av data där färgen representerar värdet i en cell. Till exempel kan du skapa en värmekarta där en cell med det högsta värdet är färgad grön och det sker en förskjutning mot röd färg när värdet minskar.

Något som visas nedan:

Ovanstående datamängd har värden mellan 1 och 100. Celler markeras utifrån värdet i den. 100 får den gröna färgen, 1 får den röda.

Här är stegen för att skapa värmekartor med villkorlig formatering i Excel.

• Välj datauppsättning.
• Gå till Hem -> Villkorlig formatering -> Färgskalor och välj ett av färgscheman.

Så snart du klickar på värmekartaikonen tillämpas formateringen på datamängden. Det finns flera färggradienter som du kan välja mellan. Om du inte är nöjd med de befintliga färgalternativen kan du välja fler regler och ange vilken färg du vill ha.

Obs! På ett liknande sätt kan du också använda Databard- och ikonsatser.

6. Markera varannan rad/kolumn

Du kanske vill markera alternativa rader för att öka läsbarheten för data.

Dessa kallas för zebralinjer och kan vara särskilt användbara om du skriver ut data.

Nu finns det två sätt att skapa dessa zebralinjer. Det snabbaste sättet är att konvertera dina tabelldata till en Excel -tabell. Den applicerade automatiskt en färg på alternativa rader. Du kan läsa mer om det här.

Ett annat sätt är att använda villkorlig formatering.

Anta att du har en dataset som visas nedan:

Här är stegen för att markera alternativa rader med villkorlig formatering i Excel.

• Välj datauppsättningen. I exemplet ovan väljer du A2: C13 (som utesluter rubriken). Om du också vill inkludera rubriken väljer du hela datamängden.
• Öppna dialogrutan Villkorlig formatering (Hem-> Villkorlig formatering-> Ny regel) [Kortkommando - Alt + O + D].
• Välj dialogrutan "Använd en formel för att avgöra vilka celler som ska formateras" i dialogrutan.
• Ange följande formel i fältet i avsnittet ‘Redigera regelbeskrivning’:
  = ISODD (RAD ())

• Ovanstående formel kontrollerar alla celler och om RAD -numret i en cell är udda returnerar det SANT. Det angivna villkorliga formatet tillämpas på alla celler som returnerar TRUE.
• Ange det format som du vill använda för cellerna som är tomma eller har fel. För att göra detta, klicka på knappen Format. Det öppnar dialogrutan "Formatera celler", där du kan ange formatet.
• Klicka på OK.

Det är allt! De alternativa raderna i datamängden kommer att markeras.

Du kan använda samma teknik i många fall. Allt du behöver göra är att använda den relevanta formeln i villkorlig formatering. Här är några exempel:

• Markera alternativa jämna rader: = ISEVEN (ROW ())
• Markera alternativa tilläggsrader: = ISODD (ROW ())
• Markera var tredje rad: = MOD (RAD (), 3) = 0

7. Sök och markera data med villkorlig formatering

Den här är lite avancerad användning av villkorlig formatering. Det skulle få dig att se ut som en Excel rockstar.

Anta att du har en datauppsättning som visas nedan med produktnamn, säljare och geografi. Tanken är att skriva en sträng i cell C2, och om den matchar med data i någon cell (er), så bör det bli markerat. Något som visas nedan:

Här är stegen för att skapa denna sök- och markeringsfunktion:

• Välj datauppsättningen.
• Gå till Hem -> Villkorlig formatering -> Ny regel (Kortkommando - Alt + O + D).
• I dialogrutan Ny formateringsregel väljer du alternativet "Använd en formel för att avgöra vilka celler som ska formateras".
• Ange följande formel i fältet i avsnittet ‘Redigera regelbeskrivning’:
  = AND ($ C $ 2 ””, $ C $ 2 = B5)
• Ange det format som du vill använda för cellerna som är tomma eller har fel. För att göra detta, klicka på knappen Format. Det öppnar dialogrutan "Formatera celler", där du kan ange formatet.
• Klicka på OK.

Det är allt! Nu när du anger något i cell C2 och trycker på enter markerar det alla matchande celler.

Hur fungerar detta?

Formeln som används vid villkorlig formatering utvärderar alla celler i datamängden. Låt oss säga att du går in i Japan i cell C2. Nu skulle Excel utvärdera formeln för varje cell.

Formeln skulle returnera TRUE för en cell när två villkor är uppfyllda:

• Cell C2 är inte tom.
• Innehållet i cell C2 matchar exakt innehållet i cellen i datamängden.

Därför markeras alla celler som innehåller texten Japan.

Ladda ner exempelfilen

Du kan använda samma logik för att skapa variationer som:

• Markera hela raden istället för en cell.
• Markera även när det är en partiell matchning.
• Markera cellerna/raderna medan du skriver (dynamisk) [Du kommer att älska det här tricket :)].

Så här tar du bort villkorlig formatering i Excel

När den har tillämpats förblir villkorlig formatering kvar om du inte tar bort den manuellt. Som bästa praxis, behåll den villkorade formateringen endast för de celler där du behöver det.

Eftersom det är flyktigt kan det leda till en långsam Excel -arbetsbok.

Så här tar du bort villkorlig formatering:

• Markera cellerna från vilka du vill ta bort villkorlig formatering.
• Gå till Hem -> Villkorlig formatering -> Rensa regler -> Rensa regler från utvalda celler.
  • Om du vill ta bort villkorlig formatering från hela kalkylbladet väljer du Rensa regler från hela bladet.

Viktiga saker att veta om villkorlig formatering i Excel

• Villkorlig formatering i flyktig. Det kan leda till en långsam arbetsbok. Använd den bara vid behov.
• När du kopierar klistra in celler som innehåller villkorlig formatering kopieras villkorlig formatering också.
• Om du tillämpar flera regler på samma uppsättning celler förblir alla regler aktiva. Vid överlappning ges den regel som tillämpades sist. Du kan dock ändra ordningen genom att ändra ordningen från dialogrutan Hantera regler.