7 fantastiske Excel -tilpassede tallformat -triks (du må vite)

Excel Custom Number -formatering er klær for data i excel -celler. Du kan kle disse slik du vil. Alt du trenger er litt kunnskap om hvordan Excel Custom Number Format fungerer.

Med tilpasset nummerformatering i Excel kan du endre måten verdiene i cellene vises på, men samtidig beholde den opprinnelige verdien intakt.

For eksempel kan jeg få en datoverdi til å vises i forskjellige datoformater, mens den underliggende verdien (tallet som representerer datoen) forblir den samme.

I denne opplæringen vil jeg dekke alt om tilpasset tallformat i Excel, og også vise deg noen veldig nyttige eksempler som du kan bruke i det daglige arbeidet.

Så la oss komme i gang!

Excel Custom Number Format Construct

Før jeg viser deg hvor fantastisk det er, la meg kort forklare hvordan tilpasset tallformatering fungerer i Excel.

Ved design kan Excel godta følgende fire typer data i en celle:

• Et positivt tall
• et negativt tall
• 0
• Tekststrenger (dette kan inkludere rene tekststrenger så vel som alfanumeriske strenger)

En annen datatype som Excel kan godta er datoer, men siden alle dato- og klokkeslettverdier er lagret som tall i Excel, vil disse bli dekket som en del av det positive tallet.

For en hvilken som helst celle i et regneark i Excel, kan du angi hvordan hver av disse datatypene skal vises i cellen.

Og nedenfor er syntaksen der du kan spesifisere dette formatet.

;;;

Vær oppmerksom på at alle disse er atskilt med semikolon (;).

Alt du skriver inn i en celle i Excel vil falle inn i en av disse fire kategoriene, og derfor kan et spesifisert format for det spesifiseres.

Hvis du bare nevner:

• Ett format: Den brukes på alle fire seksjoner. For eksempel, hvis du bare skriver Generelt, vil det bli brukt for alle fire seksjoner.
• To formater: Den første brukes på positive tall og nuller, og den andre brukes på negative tall. Tekstformat blir som standard Generelt.
• Tre formater: Den første brukes på positive tall, den andre brukes på negative tall, den tredje brukes på null, og teksten forsvinner da ingenting er spesifisert for tekst.

Hvis du vil lære mer om tilpasset nummerformatering, vil jeg anbefale Office Hjelp -delen.

Hvordan dette fungerer og hvor du skal angi disse formatene, blir dekket senere i denne opplæringen. For øyeblikket må du bare huske på at dette er syntaksen for formatet for en hvilken som helst celle

Hvordan tilpasset tallformat fungerer i Excel

Deretter åpner du en ny Excel -arbeidsbok, som standard har alle cellene i alle regnearkene i arbeidsboken det generelle formatet der:

• Positive tall vises som det er (justert til venstre)
• Negative tall er vist med et minustegn (og justert til venstre)
• Null vises som 0 (og justert til venstre)
• Tekst vises som den er (justert til høyre)

Og hvis du skriver inn en dato, vises den basert på din regionale innstilling (i DD-MM-ÅÅÅÅ eller MM-DD-ÅÅÅÅ-format).

Så som standard er cellens nummerformatering:

Generelt; Generelt; Generelt; Generelt

Hvor hver datatype er formatert som Generelt (og følg reglene jeg nevnte ovenfor, basert på om det er et positivt tall, negativt tall, 0 eller tekst)

Nå er standardinnstillingen ok, men hvordan endrer du formatet. For eksempel, hvis jeg vil at mine negative tall skal vises i rød farge eller jeg vil at datoene mine skal vises i et bestemt format, hvordan gjør jeg det?

Bruk Format -rullegardinmenyen i båndet

Du kan finne noen av de ofte brukte tallformatene i kategorien Hjem i gruppen Nummer.

Det er en rullegardinmeny som viser mange av de ofte brukte formatene du kan bruke på en celle ved ganske enkelt å velge cellen og deretter velge formatet fra denne listen.

For eksempel, hvis du vil konvertere tall til prosent eller konverterte datoer til kort datoformat eller langt format datoformat, kan du enkelt gjøre det ved å bruke alternativene i rullegardinmenyen.

Bruke dialogboksen Formatceller

Formater celler dialogbokser der du får maksimal fleksibilitet når det gjelder å bruke formater på en celle i Excel.

Den har allerede mange forhåndsdefinerte formater som du kan bruke, eller hvis du vil lage ditt eget tilpassede nummerformat, kan du også gjøre det.

Slik åpner du dialogboksen Formatceller;

1. Klikk på kategorien Hjem
2. I Nummer-gruppen klikker du på ikonet for startboks for dialogboksen (den lille vippepilen nederst til høyre i gruppen).

Dette åpner dialogboksen Formater celler. I den finner du alle formater i kategorien Nummer (sammen med muligheten til å lage tilpassede celleformater)

Du kan også bruke følgende hurtigtast til å åpne dialogboksen for formatceller:

CONTROL + 1 (alle kontrolltastene og trykk deretter på 1 -tasten).

Hvis du bruker Mac, kan du bruke CMD + 1

La oss nå ha et par praktiske eksempler der vi skal lage våre egne tilpassede tallformater.

Eksempler på bruk av tilpasset tallformatering i Excel

La oss nå ha et par praktiske eksempler der vi skal lage våre egne tilpassede tallformater.

Skjul en celleverdi (eller Skjul alle verdier i en rekke celler)

Noen ganger må du kanskje skjule innholdet i cellen (uten å skjule raden eller kolonnen som har cellen).

Et praktisk eksempel på dette er når du oppretter et Excel -dashbord og det er et par verdier du bruker i dashbordet, men du vil ikke at brukerne skal se det. slik at du ganske enkelt kan skjule disse verdiene

Anta at du har et datasett som vist nedenfor, og du vil skjule alle de numeriske verdiene:

Slik gjør du dette ved hjelp av tilpasset formatering i Excel:

1. Velg cellen eller celleområdet du vil skjule celleinnholdet for
2. Åpne dialogboksen Formater celler (hurtigtastkontroll + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

   ;;;

Hvordan virker dette?

Formatet for fire forskjellige typer datatyper er delt med semikolon i følgende format:

;;;

I denne teknikken har vi fjernet formatet og bare spesifisert semikolon, noe som indikerer at vi ikke vil ha noen formatering for cellene. Dette er en indikasjon for Excel for å skjule alt innhold som er der i cellen.

Vær oppmerksom på at mens dette skjuler innholdet i cellen, er det fortsatt der. så hvis du har tall du har skjult, kan du fortsatt bruke disse i beregninger (noe som gjør dette til et flott triks for Excel -dashbord og rapporter).

Vis negative tall i rød farge

Som standard vises negative tall med et minustegn (eller innenfor parentes i noen regionale innstillinger).

Du kan bruke tilpasset tallformatering til å endre fargen på de negative tallene og få dem til å vises i rød farge.

Anta at du har et datasett som vist nedenfor, og du vil markere alle negative verdier i rød farge.

Nedenfor er trinnene for å gjøre dette:

1. Velg celleområdet der du vil vise negative tall i rødt
2. Åpne dialogboksen Formater celler (hurtigtastkontroll + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

Generelt; [Rød] -Generell

Formatet ovenfor får cellene dine med negative verdier til å vises i rød farge.

Hvordan virker dette?

Når du bare angir to formater som det egendefinerte tallformatet, vurderes det første som positive tall og 0, og det andre regnes som negative tall (neste verdi blir som standard det generelle formatet).

I formatet ovenfor som vi har brukt, har vi spesifisert fargen som vi vil at formatet skal ta opp i firkantede parenteser.

Så mens de negative tallene tar det generelle formatet, vises de i rød farge med et minustegn.

Hvis du vil få de negative tallene til å stoppe i rød farge i parentes (i stedet for minustegnet), kan du bruke formatet nedenfor:

Generelt; [Rød] (Generelt)

Legg til tekst i tall (for eksempel millioner/milliarder)

En fantastisk ting med tilpasset tallformatering er at du kan legge til et prefiks eller en suffikstekst til et tall, mens du fortsatt beholder det originale nummeret i cellen.

For eksempel kan jeg få ‘100’ til å vises som ‘100 millioner dollar’, mens jeg fortsatt har den opprinnelige verdien i cellen, slik at jeg kan bruke den i beregninger.

Anta at du har et datasett som vist nedenfor, og du vil vise disse tallene med et dollartegn og ordet million foran det.

Nedenfor er trinnene for å gjøre dette:

1. Velg datasettet der du vil legge til teksten i tallene
2. Åpne dialogboksen Formater celler (hurtigtastkontroll + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

$ General "Million"

Vær oppmerksom på at dette tilpassede tallformatet bare vil påvirke tall. Hvis du skriver inn tekst i samme celle, ser den ut som den er.

Hvordan virker dette?

I dette eksemplet, i det generelle formatet, har jeg lagt til ordet million i doble anførselstegn. alt jeg legger til i doble anførselstegn til formatet vises som i cellen.

Jeg la også til dollartegnet før det generelle formatet, slik at alle tallene mine også viser dollartegnet før tallet.

Merk: Hvis du lurer på hvorfor dollartegnet ikke er i doble anførselstegn mens ordet Million står i doble anførselstegn, er det fordi Excel gjenkjenner dollar som et symbol som ofte brukes i tilpasset tallformatering og ikke krever at du å legge til doble anførselstegn til den. Du kan fortsette og legge til doble anførselstegn i dollartegnet, men når du lukker dialogboksen for formatceller og åpner den igjen, vil du legge merke til at Excel har fjernet den

Skjul tall og tekst

Med tilpasset nummerformatering kan du vurdere maksimalt to forhold, og basert på disse betingelsene kan du bruke et format på cellen.

Anta for eksempel at du har en liste over poeng for noen studenter, du vil vise teksten Godkjent eller Ikke bestått basert på poengsummen (der poengsummen mindre enn 35 skal vise "Ikke bestått", ellers bør den vise "Bestått".

Nedenfor er trinnene for å vise poengsummer som Godkjent eller Ikke bestått i stedet for verdien ved å bruke egendefinert tallformat:

1. Velg datasettet der du har poengsummene
2. Åpne dialogboksen Formater celler (hurtigtast Control + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

   [<35] "Mislykkes"; "Bestått"

Nedenfor ser du hvordan dataene dine ville se ut etter at du har brukt formatet ovenfor.

Vær oppmerksom på at det ikke endrer verdien i cellene. Cellen fortsetter å ha poengene i numeriske verdier mens den viser "Bestått" eller "Mislykket" avhengig av celleverdien.

Hvordan virker dette?

I dette eksemplet må betingelsen spesifiseres innenfor rammen. tilpasset nummerformatering evaluerer deretter verdien i cellen og bruker formatet deretter.

Så hvis verdien i cellen er mindre enn 35, er tallet skjult, og i stedet vises teksten "Mislykket", og alle andre tilfeller legges til, "Pass" vises.

På samme måte, hvis du vil konvertere en rekke celler som har 0 og 1 til SANN og FALSK, kan du bruke formatet nedenfor:

[= 0] "FALSE"; [= 1] "SANN"

Husk at du bare kan bruke to betingelser i tilpasset nummerformatering. Hvis du prøver å bruke mer enn to betingelser, kommer det til å gi deg beskjed om at det ikke kan godta det formatet.

Skjul tekst, men vis tall

Noen ganger, når du laster ned dataene dine fra databaser siden de er tomme, er de fylt med verdi, for eksempel Ikke tilgjengelig eller NA eller -

Med tilpasset nummerformatering kan du skjule alle tekstverdiene mens tallene beholdes som de er. dette sikrer også at de originale dataene dine er intakte (ettersom du ikke sletter tekstverdien, gjemmer du dem bare)

Anta at du har et datasett som vist nedenfor der du vil skjule alle tekstverdiene:

Nedenfor er trinnene for å gjøre dette:

1. Velg datasettet der du har dataene
2. Åpne dialogboksen Formater celler (hurtigtastkontroll + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

Generell; -Generell; Generell;

Vær oppmerksom på at jeg har brukt det generelle formatet for tall. Du kan velge hvilket som helst format (for eksempel 0, 0. #, #0.0%). Vær også oppmerksom på at det er et negativt tegn (-) før det andre generelle formatet, ettersom det representerer formatet for negative tall.

Hvordan virker dette?

I dette eksemplet, siden vi ønsket å skjule alle tekstverdiene, spesifiserte vi ganske enkelt ikke formatet for det.

Og siden vi spesifiserte formatet for de tre andre datatypene (positivt tall, negativt tall og null), forstod Excel at vi med hensikt har forlatt formatet for tekstverdien tomt, og det skal ikke vise noe hvis en celle har en tekst streng.

Vis tall som prosent (%)

Med tilpasset nummerformatering kan du også endre tallene og få dem til å vises i prosent.

Anta at du har et datasett som vist nedenfor, og du vil konvertere disse tallene til å vise tilsvarende prosentverdi:

Nedenfor er trinnene for å gjøre dette:

1. Velg cellene der du har tallene (som du vil konvertere til prosent)
2. Åpne dialogboksen Formater celler (hurtigtastkontroll + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

0%

Dette vil endre tallene til prosentandelen. For eksempel endres det fra 0,11 til 11%.

Hvis alt du vil gjøre er å konvertere et tall til den tilsvarende prosentverdien, vil en bedre måte å gjøre dette på være å bruke prosentalternativet i dialogboksen formatceller.

Der tilpasset formatering kan være nyttig, er når du vil ha mer kontroll over hvordan du vil vise prosentverdien.

For eksempel, hvis du vil vise litt ekstra tekst med prosentandelen, alt du vil vise negative prosentandeler i rød farge, er det bedre å bruke tilpasset formatering

Vis tall i en annen enhet (millioner som milliarder eller gram som kilo)

Hvis du jobber med store tall, kan det være lurt å vise disse i en annen enhet. for eksempel, hvis du har salgsdata fra noen selskaper, vil du kanskje vise salget i millioner eller milliarder (mens du fortsatt beholder den opprinnelige salgsverdien i cellene)

Heldigvis kan du enkelt gjøre det med tilpasset nummerformatering.

Anta at du har et datasett som vist nedenfor, og du vil vise disse salgsverdiene i millioner.

Nedenfor er trinnene for å gjøre dette:

1. Velg cellene der du har tallene
2. Åpne dialogboksen Formater celler (hurtigtast Control + 1)
3. Klikk på Tilpasset i kategorien Nummer
4. Skriv inn følgende format i Type -feltet:

0.0,,

Ovenstående vil gi deg resultatet som vist nedenfor (verdier i millioner i ett siffer etter desimalen):

Hvordan virker dette?

I formatet ovenfor angir en null før desimalen at Excel skal vise hele tallet (nøyaktig slik det er i cellen), og en 0 etter desimalen indikerer at det bare skal vise et signifikant siffer etter desimaltegnet.

Så 0.0 vil alltid være et tall med ett siffer etter desimalpunktet.

Når du legger til et komma etter 0.0, vil det barbere av det siste tresifrede tallet. For eksempel vil 123456 bli 123,4 (de tre siste sifrene fjernes, og siden det må vise 1 siffer etter desimalpunktet, viser det 4)

Hvis du tenker over det, ligner det på å dele tallet med 1000. Så hvis du vil vise et tall i "millioner" -enheten, må du legge til 2 kommaer, slik at det vil barbere av de siste 6 sifrene.

Og hvis du også vil vise ordet million eller alfabetet M etter tallet, bruk formatet nedenfor

0,0 ,, "M"