Villkorlig formatering i Excel: Instruktioner och användning

Använd dessa instruktioner för att skapa en mängd olika format

Villkorlig formatering i Microsoft Excel är ett användbart verktyg för att göra tabeller, rapporter eller celler mer framträdande, markera ett område eller flera kolumner, analysera data, klargöra skillnader eller identifiera trender. Datarader, symboluppsättningar eller färgade skalor används för detta. Beroende på de angivna villkoren ändras visningen av celler eller cellintervallet formateras. Du kan skapa din egen för de många integrerade förhållandena.

Villkorlig formatering är vettigt och hjälper dig

Villkorlig formatering i Excel kan tillämpas på många sätt, oavsett om det gäller en Excel -tabell, ett specifikt cellintervall eller en pivottabellrapport. Olika åtgärder och formatmallar är tillgängliga för dig. Den avgörande fördelen för Excel -användare är att speciella värden eller resultat kan markeras med färgkodning. Om ett värde ändras justeras formateringen automatiskt - beroende på förutsättningarna.

info: Pivottabellen ("Pivottabell") är en speciell tabell med vilken en stor mängd data kan beräknas, sammanfattas och analyseras. Dessutom kan du snabbt göra jämförelser och identifiera trender i undersökningsdata.

Villkorlig formatering med befintliga regler för nummer, textinnehåll eller datum

Om du formaterar med en jämförelseoperator kan celler lättare hittas. Du kan göra värden mer synliga, till exempel större eller mindre än ett definierat värde eller vissa värden som ligger mellan en övre och en nedre gräns. Det betyder att önskad eller sökt data visas i färg. Snabb formatering eller avancerad formatering är tillgänglig för redigering. Med den utökade formateringen kan du arbeta med formler, till exempel för att automatiskt markera en Excel -kolumn i färg.

Exempel:

• En lagerhandläggare vill markera alla lager under 15 stycken med färgad bakgrund.
• Alla städer med mer än 150 000 invånare ska markeras i en tabell.
• Ett tillverkningsföretag vill markera alla varor som tillverkats mellan 1 januari 2022-2023 och 31 januari 2022-2023 i rött.

Snabbformatering:

Urval av en eller flera celler.

Klicka på "Start" på fliken.

Gå till pilen bredvid knappen "Villkorlig formatering" i "Stil".

Klicka på "Regler för markering av celler".

Välj önskat kommando, t.ex. B. större än, mindre än, mellan, datum etc.

Ange värden och välj format.

Utökad formatering:

1. Markera en eller flera celler i en tabell eller pivottabellrapport.

2. Gå till fliken "Hem", i gruppen Stilar, klicka på pilen bredvid "Villkorlig formatering".

3. Klicka på "Hantera regler": Dialogrutan "Manager för villkorliga formateringsregler" visas.

4. Välj åtgärd:

   • Klicka på "Ny regel": Dialogrutan "Ny formateringsregel" visas.
   • För att ändra ett format, se till att rätt kalkylblad eller tabell är valt i listrutan "Visa formateringsregel för".
   • I fältet "Gäller", gå till "Dölj dialogrutan"; välj sedan det nya cellintervallet i kalkylbladet.
   • Klicka på knappen "Expand Dialog Box".
   • Välj regeln och klicka på "Redigera regel": Dialogrutan "Redigera formateringsregel" visas.

5. Gör så här om du vill ändra intervallet för fält i värdeområdet "Tillämpa regel på" i en pivottabellrapport:

   • Urval: Klicka på "Endast dessa celler"
   • Motsvarande fält: Klicka på "Alla celler med samma fält"
   • Värdefält: Klicka på "Alla celler"

6. Under "Välj regel typ" klicka på "Formatera bara celler som innehåller"

Nu kan du utföra åtgärder under "Redigera regelbeskrivning" i listfältet "Formatera celler endast med":

Formatera efter nummer, datum eller tid:

För att göra detta, välj ett cellvärde och en jämförelseoperatör och ange nummer, datum eller tid. exempel: För "Mellan", ange 300 och 500 eller välj "Lika med" och ange ett datum.

Du kan använda en formel för att få tillbaka ett värde, datum eller tid.

Format efter text:

Välj "Specific text" och "Relational operator".

Ange text, t.ex. B. på "innehåller" ordet "guld" eller på "börjar med" ett prefix som du vill söka efter.

Högst 255 tecken kan matas in.

Värden kan returneras med hjälp av formler. För att göra detta måste formeln börja med (=). Ogiltiga formler är inte formaterade. Det är lämpligt att testa formeln för fel först.

Formatera efter datum:

• Välj datum och jämförelsedatum, t.ex. B. Igår, nästa vecka eller den här månaden

Sedan: För att ange ett format, klicka på "Format", dialogrutan "Formatera celler" visas. Välj önskat nummerformat, fyllningsformat, ram eller teckensnitt, klicka på "OK".

Det är möjligt att välja flera format. Du kan se vilka format som har valts i fältet "Förhandsgranskning".

Villkorlig formatering med databaser, färgscheman och symboluppsättningar

Innehåll och värden i celler kan visuellt uppgraderas med databaser, färgskalor eller symboluppsättningar och gör dem mer attraktiva och tydligare. Om värden i ett cellintervall ska jämföras med varandra är detta enklare med denna villkorliga formatering.

Formatera celler med datafält

Datafält gör det lättare att jämföra celler med varandra. Värdet i cellen indikeras av stapellängden. Ju högre värde, desto längre datafält, ju lägre värde, desto kortare streck. Detta gör att höga eller låga värden kan fastställas snabbt.

Snabbformatering:

Markera önskat område (cell, bord eller hela ark).

Klicka på knappen Villkorlig formatering på fliken Hem.

Gå till "datafält" och välj en fyllning.

Formatera celler med färgade ramar

Färgskalor hjälper till att enklare klassificera olika data och för att snabbt kunna jämföra innehållet med varandra. Beroende på färgskärmen visas höga eller låga värden. Du kan formatera celler med en 2- eller 3-färgskala.

2-färgskala

Snabbformatering:

Välj cell, tabell eller hela ark.

Klicka på Villkorlig formatering på fliken Start.

Gå till "färgskala" och klicka på önskat tvåfärgsskalaformat. Den övre färgen står för högre värden, den nedre färgen för lägre värden.

3-färgs skala

Den tredje färgen möjliggör visualisering av medelvärden- förutom färgrepresentation av höga och låga värden som i 2-färgskalan.

Snabbformatering:

Välj cell, tabell eller hela ark.

Klicka på Villkorlig formatering på fliken Start.

Gå till "färgskala" och klicka på önskat trefärgsskalaformat. Den övre färgen står för högre värden, den nedre färgen för lägre värden.

Formatera celler med symboluppsättningar

Med en uppsättning symboler kan data ses och klassificeras i separata kategorier. En symbol står för ett visst värdeområde. Den villkorliga formateringen i Excel erbjuder symboler med olika färg: pilar, prickar, kryss, utropstecken och mycket mer.

Snabbformatering:

Välj cell, tabell eller hela ark.

Klicka på Villkorlig formatering på fliken Start.

Gå till "Symboluppsättningar" och välj önskad symboluppsättning

Skapa formateringsregler själv

Om du inte kan hitta ett lämpligt alternativ i villkorlig formatering som erbjuds av Excel kan du enkelt skapa individuella regler för formatering.

Markera cellintervallet som villkorligt formateras.

Gå till "Villkorlig formatering" på fliken "Hem".

Välj "Ny regel" och bekräfta med "OK".

Om du inte hittar det alternativ du behöver för din egen formateringsregel kan en logisk formel hjälpa. Till exempel kan du använda en formel för att automatiskt färga Excel -celler så snart innehållet är tillgängligt. Omvänt kan tomma celler formateras villkorligt.

Exempel: Genom att använda en formel kan du jämföra data utanför det valda intervallet. Detta inkluderar till och med data som finns på ett annat kalkylblad i samma arbetsbok. Eller så kan du jämföra värden med ett resultat som returneras av en funktion. Formeln måste vara logisk och returnera "true" eller "false".

Genom att markera celler direkt i kalkylbladet kan du ange cellreferenser i en formel. Detta är absoluta cellreferenser. Men Excel kan också justera cellreferenser för varje cell i det valda området. Om du vill väljer du relativa cellreferenser.

Steg-för-steg-instruktioner för att skapa en regel

1. På fliken "Hem", gruppen "Stilar", gå till pilen bredvid "Villkorlig formatering" och klicka på "Hantera regler". Dialogrutan Villkorlig formateringsregelhanterare visas.

2. Följande åtgärder är möjliga:

   • Om du vill lägga till ett villkorat format går du till "Ny regel".
   • Ändra ett villkorligt format:
   • • Kontrollera att du har rätt kalkylblad.
     • Först, i Tillämpar på, klicka på Dölj dialogrutan. Välj sedan det nya cellområdet och gå till "Expandera dialogruta".
     • Välj önskad regel, klicka på "Redigera regel". "Redigera formateringsregel" visas.

3. Under "Välj regel typ" klickar du på "Använd formel för att bestämma cellerna som ska formateras".

   • Under "Redigera regelbeskrivning" anger du en formel i "Formatera värden för vilka denna regel är sann". Formeln börjar med (=) och måste returnera "true" (1) eller "false" (0).
   • Klicka på Format för att visa dialogrutan Formatera celler.
   • Välj nu det nummer, teckensnitt, fyllningsformat etc. som ska användas om cellvärdet är sant och bekräfta med "OK". De valda formaten visas i "Förhandsgranskning".

Exempelvis returnerar COUNTIF -formeln logiska värden. Om denna formel används kan duplikatvärden bestämmas eller innehåll som förekommer i en viss frekvens kan markeras i färg. På detta sätt kan unika föremål också hittas och markeras i färg. Andra formler som kan användas för villkorlig formatering är

• REST: så att alternerande linjefärger kan ställas in automatiskt
• IDENTISKT: Med denna formel kan data du letar efter markeras i en Excel -tabell som motsvarar ett visst värde
• FEL: markerar automatiskt tomma celler i Excel -tabellen eller gör att felvärden försvinner från arbetsboken
• NOT och ISNUMBER: Formaterar alla Excel -celler som inte innehåller siffror
• MAX: cellens största värde markeras i tabellen
• MIN: möjliggör villkorlig formatering av det minsta värdet i Excel -listan så att minimum kan ses med en blick
• VÄNSTER: beroende på en teckensträng i början formateras Excel -celler automatiskt och ges en enhetlig textfärg
• STOR och VÄNSTER: aktivera en cell med nytt innehåll att färgläggas
• HÖGER: Celler kan formateras baserat på strängens sista tecken
• IDAG: Med detta kan möten formateras i färg med hjälp av trafikljusmetoden eller datumvärden kan markeras som snart kommer att vara uppdaterade
• MÅNAD eller ÅR: låter dig dela Excel -listan i månader eller år; Med YEAR kan datumvärden för det aktuella året också markeras och årliga värden markeras
• ELLER och VECKODAG: markerar värden enligt veckodag i färg

Exempel på att skapa en regel för OCH och ELLER -kontroller:

I det här exemplet används två värden för villkorlig formatering. Om den första regeln inte gäller, gäller den andra regeln automatiskt.

Första regeln: En husköpare vill kontrollera om det planerade fastighetsköpet är i linje med hans budget. Han kan ta in upp till 80 000 euro av sina egna medel som handpenning och betala en månadslån på högst 1 200 euro. Om både handpenningen och lånebetalningen uppfyller dessa krav markeras motsvarande celler med grönt.

Andra regeln: Om antingen handpenningen eller avbetalningen inte uppfyller kraven markeras motsvarande celler med rött.

Formeln för att skapa den första regeln med färgen grön är följande:

= AND (OM ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Formeln för att skapa den andra regeln med färgen röd är följande:

= ELLER (OM ($ B $ 4 = 1200,1))

Överför och ta bort villkorlig formatering

Villkorlig formatering kan kopieras och tillämpas på andra eller nya data i kalkylbladet eller tas bort igen.

Överför villkorlig formatering

Om du vill kopiera villkorlig formatering och klistra in den i andra celler eller cellområden använder du knappen "Överför format".

Markera cellerna med den villkorliga formateringen som du vill kopiera och klistra in i andra celler och klicka på dem.

Gå nu till "Start" och sedan "Överföringsformat". Markören visas som en pensel.

Du kan lägga till villkorlig formatering till en cell eller ett cellområde genom att flytta penseln över cellen.

Med ESC avslutar du användningen av borsten.

Du kan fortfarande använda penseln för att infoga villkorlig formatering genom att dubbelklicka på "Överföringsformat".

Alternativt kan den villkorade formateringen kopieras med kortkommandon och klistras in i en annan Excel -cell.

Rensa villkorlig formatering

Man skiljer mellan en raderingsprocess i ett kalkylblad och en raderingsprocess i ett antal celler.

Radera på ett kalkylblad eller i hela kalkylbladet

Gå till "Villkorlig formatering" på fliken "Hem".

Klicka sedan på "radera regler"

Klicka sedan på "Ta bort regler i hela bladet".

Alternativt kan du ta bort den villkorliga formateringen med hjälp av dialogrutan "Välj innehåll".

Ta bort i cellområdet

Markera celler som innehåller den villkorade formatering du vill ha.

Klicka på knappen "Läsanalys förstoringsglas" (knappen finns längst ner till höger bredvid den valda datan).

Gå till "Ta bort format".

Hitta och ta bort samma villkorade format i hela kalkylbladet

Klicka på cellen med lämpligt villkorligt format.

Klicka på pilen bredvid "Sök och välj" på fliken "Hem" och klicka sedan på "Välj innehåll".

Klicka på "Villkorliga format".

Under "Datakontroll" går du till "Samma" (detta markerar alla celler med samma villkorlig formatering).

Gå till "Villkorlig formatering" på fliken "Start", sedan "Ta bort regler" och "Ta bort regler i valda celler".

Villkorlig formatering i pivottabellrapporten

Med en pivottabellrapport kan användare snabbt sammanfatta stora mängder data. Cellintervall, Excel -kalkylblad eller pivottabellrapport - villkorlig formatering fungerar i princip likadant överallt. Bara i pivottabellrapporten finns det några specialfunktioner som du bör vara medveten om:

• Vissa villkorliga format fungerar inte på fält i värdeintervallet för en pivottabellrapport.
• Om du ändrar layouten på pivottabellrapporten på något sätt, kommer den villkorade formateringen att bestå tills fälten i den underliggande informationen har tagits bort.
• Villkorlig formatering för fält i intervallet av värden kan baseras på datahierarkin. Området för denna villkorliga formatering bestäms av nästa lägre eller nästa högre nivå i hierarkin i rader för en eller flera kolumner eller i kolumner för en eller flera rader.
• Området för villkorlig formatering för fält i värdeintervallet kan definieras med tre metoder:

• • urval
  • Motsvarande fält
  • Värdefält

Standardmetoden är urval.

Områdesdefinition genom val

Gå till "Apply Formatting Rule To".

Klicka antingen på dialogrutan Ny formateringsregel eller på dialogrutan Redigera formateringsregel.

Denna metod är lämplig för en sammanhängande serie fält i värdeintervallet, t.ex. B. undersökningsdata för en region, liksom för en icke-sammanhängande serie fält i värdeintervallet, till exempel undersökningsdata för olika regioner på olika nivåer i datahierarkin.

Områdesdefinition genom ett motsvarande fält

Denna metod fungerar om du har

• vill undvika att behöva välja många orelaterade objekt
• vill villkorligt formatera flera fält i värdeintervallet för en datahierarkinivå
• Vill utesluta delresultat

Områdesdefinition genom ett värdefält

Denna metod fungerar om du har

• vill undvika att behöva välja många orelaterade objekt
• vill villkorligt formatera flera fält i värdeintervallet för alla hierarkinivåer
• Vill ta med totalsummor och delresultat.

Slutsats: Villkorlig formatering i Excel gör det lättare att hålla reda på data

Om du vill markera vissa datavärden i din Excel -tabell i färg är den villkorliga formateringen i Excel idealisk. Du har två alternativ för att tillämpa villkorlig formatering. Antingen använder du reglerna från Excel för att markera textinnehåll, numeriska värden eller datum i färg. Eller så kan du själv definiera en regel med olika förutsättningar under vilka de relevanta värdena ska markeras. Du kan enkelt och effektivt jämföra data med varandra med hjälp av datafält, färgstaplar och symboluppsättningar. Om designalternativen inte längre behövs kan du ta bort dem med några klick.

Fler artiklar om villkorlig formatering

• Hur man automatiskt genererar pilar i Excel -listor
• Visa negativa tidsvärden i Excel -tabeller
• Färg negativa procentsatser rött
• Känna igen negativa procentsatser
• Formatera siffror med ett anpassat format i färg
• Formatera Excel -celler som inte har det angivna värdet
• Lagra automatiskt celler med motsvarande valutaformat
• Markera celler i färg med hjälp av en makrofunktion
• Använd villkorlig formatering och markera data som är större än eller lika med den tredje största cellen
• Omvända sanningsvärden