7 geweldige Excel-trucs voor aangepaste nummeropmaak (je moet het weten)

Excel Custom Number-opmaak is de kleding voor gegevens in Excel-cellen. Deze kun je aankleden zoals jij dat wilt. Het enige dat u nodig hebt, is een beetje kennis van hoe Excel Custom Number Format werkt.

Met aangepaste getalnotatie in Excel kunt u de manier waarop de waarden in de cellen worden weergegeven wijzigen, maar tegelijkertijd de oorspronkelijke waarde intact laten.

Ik kan bijvoorbeeld een datumwaarde in verschillende datumnotaties laten verschijnen, terwijl de onderliggende waarde (het getal dat de datum vertegenwoordigt) hetzelfde blijft.

In deze zelfstudie behandel ik alles over aangepaste getalnotatie in Excel, en laat ik u ook enkele zeer nuttige voorbeelden zien die u in uw dagelijkse werk kunt gebruiken.

Dus laten we beginnen!

Excel Aangepast getalnotatie Construct

Voordat ik je de geweldigheid ervan laat zien, wil ik je kort uitleggen hoe aangepaste nummeropmaak werkt in Excel.

Door het ontwerp kan Excel de volgende vier soorten gegevens in een cel accepteren:

• Een positief getal
• een negatief getal
• 0
• Tekstreeksen (dit kunnen zowel zuivere tekstreeksen als alfanumerieke reeksen zijn)

Een ander gegevenstype dat Excel kan accepteren, zijn datums, maar aangezien alle datum- en tijdwaarden worden opgeslagen als getallen in Excel, zouden deze worden gedekt als onderdeel van het positieve getal.

Voor elke cel in een werkblad in Excel kunt u opgeven hoe elk van deze gegevenstypen in de cel moet worden weergegeven.

En hieronder is de syntaxis waarin u dit formaat kunt specificeren.

;;;

Merk op dat al deze worden gescheiden door puntkomma's (;).

Alles wat u in een cel in Excel invoert, valt in een van deze vier categorieën en daarom kan er een aangepaste indeling voor worden opgegeven.

Als je alleen vermeldt:

• Eén formaat: Het wordt toegepast op alle vier de secties. Als u bijvoorbeeld alleen Algemeen schrijft, wordt dit voor alle vier de secties toegepast.
• Twee formaten: De eerste wordt toegepast op positieve getallen en nullen, en de tweede wordt toegepast op negatieve getallen. Tekstindeling wordt standaard Algemeen.
• Drie formaten: de eerste wordt toegepast op positieve getallen, de tweede wordt toegepast op negatieve getallen, de derde wordt toegepast op nul en de tekst verdwijnt omdat er niets is opgegeven voor tekst.

Als u meer wilt weten over aangepaste getalnotatie, raad ik de Office Help-sectie ten zeerste aan.

Hoe dit werkt en waar u deze indelingen kunt invoeren, wordt verderop in deze zelfstudie besproken. Houd er voorlopig rekening mee dat dit de syntaxis is voor het formaat voor elke cel

Hoe aangepaste getalnotatie werkt in Excel

Vervolgens opent u een nieuwe Excel-werkmap, standaard hebben alle cellen in alle werkbladen in die werkmap de algemene indeling waarin:

• Positieve getallen worden weergegeven zoals ze zijn (links uitgelijnd)
• Negatieve getallen worden weergegeven met een minteken (en links uitgelijnd)
• Nul wordt weergegeven als 0 (en links uitgelijnd)
• Tekst wordt weergegeven zoals het is (rechts uitgelijnd)

En als u een datum invoert, wordt deze weergegeven op basis van uw regionale instelling (in DD-MM-YYYY- of MM-DD-YYYY-indeling).

Dus standaard is de nummeropmaak van de cel:

Algemeen;Algemeen;Algemeen;Algemeen

Waar elk gegevenstype is opgemaakt als Algemeen (en volg de regels die ik hierboven heb genoemd, op basis van of het een positief getal, negatief getal, 0 of tekst is)

Nu is de standaardinstelling in orde, maar hoe verander je het formaat. Als ik bijvoorbeeld wil dat mijn negatieve getallen in rode kleur worden weergegeven of als ik wil dat mijn datums in een specifiek formaat worden weergegeven, hoe doe ik dat dan.

De vervolgkeuzelijst Opmaak in het lint gebruiken

U kunt enkele veelgebruikte getalnotaties vinden op het tabblad Start in de groep Getal.

Het is een vervolgkeuzelijst met veel van de veelgebruikte indelingen die u op een cel kunt toepassen door simpelweg de cel te selecteren en vervolgens de indeling in deze lijst te selecteren.

Als u bijvoorbeeld getallen wilt converteren naar percentages of datums wilt converteren naar een korte datumnotatie of een lange datumnotatie, dan kunt u dit eenvoudig doen met behulp van de opties in de vervolgkeuzelijst.

Dialoogvenster Cellen opmaken gebruiken

Dialoogvensters voor cellen opmaken waar u maximale flexibiliteit krijgt als het gaat om het toepassen van opmaak op een cel in Excel.

Het heeft al veel vooraf gemaakte indelingen die u kunt gebruiken, of als u uw eigen aangepaste getalnotatie wilt maken, dan kunt u dat ook doen.

Hier leest u hoe u het dialoogvenster Cellen opmaken opent;

1. Klik op het tabblad Start
2. Klik in de groep Nummer op het pictogram voor het starten van het dialoogvenster (de kleine schuine pijl rechtsonder in de groep).

Dit opent het dialoogvenster Cellen opmaken. Daarin vindt u alle indelingen op het tabblad Getal (samen met de optie om aangepaste celindelingen te maken)

U kunt ook de volgende sneltoets gebruiken om het dialoogvenster Cellen opmaken te openen:

CONTROL + 1 (allemaal de Control-toets en druk vervolgens op de 1-toets).

Als je Mac gebruikt, kun je CMD + 1

Laten we nu een paar praktische voorbeelden geven waarin we onze eigen aangepaste getalnotaties zullen maken.

Voorbeelden van het gebruik van aangepaste nummeropmaak in Excel

Laten we nu een paar praktische voorbeelden geven waarin we onze eigen aangepaste getalnotaties zullen maken.

Een celwaarde verbergen (of alle waarden in een celbereik verbergen)

Soms moet u de inhoud van de cel verbergen (zonder de rij of kolom met de cel te verbergen).

Een praktisch geval hiervan is wanneer u een Excel-dashboard maakt en er zijn een aantal waarden die u in het dashboard gebruikt, maar u wilt niet dat de gebruikers het zien. zodat u deze waarden eenvoudig kunt verbergen

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alle numerieke waarden verbergen:

Hier leest u hoe u dit doet met aangepaste opmaak in Excel:

1. Selecteer de cel of het celbereik waarvoor u de celinhoud wilt verbergen
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik op het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

   ;;;

Hoe werkt dit?

De indeling voor vier verschillende typen gegevenstypen wordt gedeeld door puntkomma's in de volgende indeling:

;;;

Bij deze techniek hebben we de opmaak verwijderd en alleen de puntkomma gespecificeerd, wat aangeeft dat we geen opmaak voor de cellen willen. Dit is een indicatie voor Excel om alle inhoud in de cel te verbergen.

Merk op dat hoewel dit de inhoud van de cel verbergt, deze er nog steeds is. dus als je getallen hebt die je hebt verborgen, kun je deze nog steeds gebruiken in berekeningen (wat dit een geweldige truc maakt voor Excel-dashboards en -rapporten).

Negatieve getallen weergeven in rode kleur

Negatieve getallen worden standaard weergegeven met een minteken (of tussen haakjes in sommige regionale instellingen).

U kunt aangepaste nummeropmaak gebruiken om de kleur van de negatieve getallen te wijzigen en ze in rode kleur te laten verschijnen.

Stel dat u een dataset heeft zoals hieronder weergegeven en dat u alle negatieve waarden in rode kleur wilt markeren.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer het cellenbereik waar u negatieve getallen in het rood wilt weergeven
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik op het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

Algemeen;[Rood]-Algemeen

Het bovenstaande formaat zorgt ervoor dat uw cellen met negatieve waarden in rode kleur worden weergegeven.

Hoe werkt dit?

Als u slechts twee notaties opgeeft als de aangepaste getalnotatie, wordt de eerste in overweging genomen voor positieve getallen en 0, en de tweede voor negatieve getallen (de volgende waarde wordt standaard de algemene notatie).

In het bovenstaande formaat dat we hebben gebruikt, hebben we tussen vierkante haken aangegeven welke kleur we dat formaat willen hebben.

Dus hoewel de negatieve getallen het algemene formaat hebben, worden ze weergegeven in rode kleur met een minteken.

Als u de negatieve getallen wilt laten stoppen in rode kleur tussen haakjes (in plaats van het minteken), kunt u het onderstaande formaat gebruiken:

Algemeen;[Rood](Algemeen)

Tekst toevoegen aan Numbers (zoals miljoenen/miljarden)

Een verbazingwekkend aspect van aangepaste nummeropmaak is dat je een voor- of achtervoegsel aan een nummer kunt toevoegen, terwijl je het originele nummer in de cel behoudt.

Ik kan bijvoorbeeld '100' laten verschijnen als '$ 100 miljoen', terwijl ik nog steeds de oorspronkelijke waarde in de cel heb, zodat ik deze in berekeningen kan gebruiken.

Stel u heeft een dataset zoals hieronder weergegeven en u wilt deze getallen weergeven met een dollarteken en het woord miljoen ervoor.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de dataset waar u de tekst aan de cijfers wilt toevoegen
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik in het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

$Algemeen "miljoen"

Houd er rekening mee dat deze aangepaste getalnotatie alleen van invloed is op getallen. Als u tekst in dezelfde cel invoert, ziet het eruit zoals het is.

Hoe werkt dit?

In dit voorbeeld heb ik aan het algemene formaat het woord miljoen tussen dubbele aanhalingstekens toegevoegd. alles wat ik tussen dubbele aanhalingstekens aan het formaat toevoeg, wordt weergegeven zoals het in de cel staat.

Ik heb ook het dollarteken voor het algemene formaat toegevoegd, zodat al mijn nummers ook het dollarteken vóór het nummer laten zien.

Opmerking: als u zich afvraagt ​​waarom het dollarteken niet tussen dubbele aanhalingstekens staat, terwijl het woord miljoen tussen dubbele aanhalingstekens staat, komt dat omdat Excel de dollar herkent als een symbool dat vaak wordt gebruikt in aangepaste getalnotatie en u niet om er dubbele aanhalingstekens aan toe te voegen. U kunt doorgaan en dubbele aanhalingstekens toevoegen aan het dollarteken, maar wanneer u het dialoogvenster voor het opmaken van cellen sluit en het opnieuw opent, zult u merken dat Excel het heeft verwijderd

Cijfers en tekst vermommen

Met aangepaste getalnotatie kunt u maximaal twee voorwaarden beoordelen en op basis van deze voorwaarden kunt u een opmaak toepassen op de cel.

Stel dat je een lijst met scores voor sommige studenten hebt, je wilt de tekst Pass of Fail tonen op basis van de score (waar elke score lager dan 35 'Fail' moet weergeven, anders moet 'Pass' worden weergegeven.

Hieronder staan ​​de stappen om scores weer te geven als Pass of Fail in plaats van de waarde met behulp van een aangepaste getalnotatie:

1. Selecteer de dataset waar je de scores hebt
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik op het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

   [<35]"Mislukt";"Geslaagd"

Hieronder ziet u hoe uw gegevens eruit zouden zien nadat u het bovenstaande formaat hebt toegepast.

Merk op dat het de waarde in de cellen niet verandert. De cel blijft de scores in numerieke waarden hebben terwijl 'Pass' of 'Fail' wordt weergegeven, afhankelijk van de celwaarde.

Hoe werkt dit?

In dit voorbeeld moet de voorwaarde worden opgegeven tussen vierkante haken. aangepaste getalopmaak evalueert vervolgens de waarde in de cel en past de opmaak dienovereenkomstig toe.

Dus als de waarde in de cel kleiner is dan 35, dan wordt het getal verborgen en in plaats daarvan wordt de tekst 'Fail' weergegeven, waarbij alle andere gevallen worden toegevoegd, wordt 'Pass' weergegeven.

Evenzo, als u een celbereik met nullen en enen wilt converteren naar TRUE's en FALSE's, kunt u het onderstaande formaat gebruiken:

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

Onthoud dat u slechts twee voorwaarden kunt gebruiken in aangepaste getalnotatie. Als u meer dan twee voorwaarden probeert te gebruiken, zal uw prompt u laten weten dat het dat formaat niet kan accepteren.

Tekst verbergen maar cijfers weergeven

Soms, wanneer u uw gegevens uit databases downloadt, omdat deze leeg zijn, worden deze gevuld met waarden zoals Niet beschikbaar of NA of -

Met aangepaste nummeropmaak kunt u alle tekstwaarden verbergen terwijl u de cijfers ongewijzigd laat. dit zorgt er ook voor dat uw originele gegevens intact zijn (omdat u de tekstwaarde niet verwijdert, verbergt u deze gewoon)

Stel dat je een dataset hebt zoals hieronder weergegeven waar je alle tekstwaarden wilt verbergen:

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de dataset waar je de data hebt
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik in het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

Algemeen; -Algemeen; Algemeen;

Merk op dat ik het algemene formaat voor getallen heb gebruikt. U kunt elk gewenst formaat gebruiken (zoals 0, 0.#, #0.0%). Merk ook op dat er een minteken (-) staat voor het tweede algemene formaat, omdat dit het formaat voor negatieve getallen vertegenwoordigt.

Hoe werkt dit?

Omdat we in dit voorbeeld alle tekstwaarden wilden verbergen, hebben we er eenvoudigweg niet de opmaak voor gespecificeerd.

En aangezien we het formaat voor de andere drie gegevenstypen (positief getal, negatief getal en nul) hebben gespecificeerd, begreep Excel dat we het formaat voor de tekstwaarde met opzet leeg hebben gelaten en dat er niets zou moeten worden weergegeven als een cel een tekst bevat draad.

Getallen weergeven als percentages (%)

Met aangepaste getalnotatie kunt u de getallen ook wijzigen en ze in percentages weergeven.

Stel dat u een gegevensset hebt zoals hieronder weergegeven, en u wilt deze getallen converteren om de equivalente procentuele waarde weer te geven:

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de cellen waar u de getallen hebt (die u naar een percentage wilt converteren)
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik in het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

0%

Dit zal de getallen veranderen in hun percentages. Het verandert bijvoorbeeld van 0,11 naar 11%.

Als u alleen een getal wilt converteren naar de equivalente percentagewaarde, kunt u dit beter doen door de percentageoptie in het dialoogvenster voor het opmaken van cellen te gebruiken.

Waar aangepaste opmaak handig kan zijn, is wanneer u meer controle wilt over hoe u de procentuele waarde wilt weergeven.

Als u bijvoorbeeld wat extra tekst met het percentage wilt weergeven, wilt u alleen negatieve percentages in rode kleur weergeven, dan is het beter om aangepaste opmaak te gebruiken

Getallen weergeven in een andere eenheid (miljoenen als miljarden of grammen als kilogrammen)

Als u met grote cijfers werkt, wilt u deze wellicht in een andere eenheid weergeven. als u bijvoorbeeld de verkoopgegevens van sommige bedrijven heeft, wilt u misschien de verkopen in miljoenen of miljarden weergeven (terwijl u de oorspronkelijke verkoopwaarde in de cellen behoudt)

Gelukkig kunt u dat eenvoudig doen met aangepaste nummeropmaak.

Stel dat u een dataset heeft zoals hieronder weergegeven, en u wilt deze verkoopwaarden in miljoenen weergeven.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de cellen waar je de nummers hebt
2. Open het dialoogvenster Cellen opmaken (sneltoets Control + 1)
3. Klik in het tabblad Nummer op de Aangepast
4. Voer het volgende formaat in het veld Type in:

0.0,,

Het bovenstaande geeft u het resultaat zoals hieronder weergegeven (waarden in miljoenen in één cijfer achter de komma):

Hoe werkt dit?

In het bovenstaande formaat geeft een nul voor het decimaalteken aan dat Excel het volledige getal moet weergeven (precies zoals het in de cel staat), en een 0 achter het decimaalteken geeft aan dat er slechts één significant cijfer achter de komma moet staan.

Dus 0.0 is altijd een getal met één cijfer achter de komma.

Als u een komma toevoegt na de 0,0, worden de laatste drie cijfers van het getal verwijderd. 123456 wordt bijvoorbeeld 123,4 (de laatste drie cijfers worden verwijderd en aangezien er 1 cijfer achter de komma moet staan, wordt er 4)

Als je erover nadenkt, is het vergelijkbaar met het delen van het getal door 1000. Dus als je een getal in de 'miljoenen'-eenheid wilt weergeven, moet je 2 komma's toevoegen, zodat het de laatste 6 cijfers zou scheren.

En als je ook het woord miljoen of het alfabet M na het nummer wilt laten zien, gebruik dan het onderstaande formaat:

0.0,, "M"