Inledande nollor i Excel (samt avslutande)

Matematiskt sett så har inledande nollor i Excel (eller avslutade nollor i decimaltal) ingen betydelse så programmet tar automatiskt bort inmatande nollor i celler.

Men ibland vill man visa ett tal med inledande nollor i ett tal, eller avslutade nollor för decimaltal. Det kan vara riktnummer i telefonnummer, OCR nummer på fakturor, födelsedatum för personer födda under år 2000 eller decimaler i celler med olika valutor.

Några av dessa tekniker för att visa inledande nollor, går även att använda om du vill inleda innehållet i celler med tecknen = (lika med), + (plus) eller – (minus), då dessa tecken ofta uppfattas som en del i formel för Excel.

Eftersom detta är ett långt tips kommer här en liten innehållsförteckning 😉:

1. Inleda med apostroftecken
2. Formatera som text
3. Tusentalsavgränsning med decimaler
4. Redovisning och Valuta
5. Anpassat talformat
6. Funktionen TEXT
7. Funktionerna SAMMAN och HÖGER
8. Power Query
9. Power Pivot – kolumn
10. Power Pivot – mått

Vill du följa med och testa själv kan du ladda ner övningsfilen här.

1. Inleda med apostroftecken

Genom att inleda inmatningen av siffror i en cell med tecknet ’ (apostrof), går det att tvinga Excel att uppfatta innehållet i celler som text.

Tecknet ’ (apostrof) hittar du oftast på tangentbordet mellan tangenterna Ä och Enter.

Tips! Inleder du inmatningen i en cell med tecknet = (lika med) uppfattar Excel att du vill börja skriva in en formel, för till exempel en uträkning.

Det inledande tecknet ’ (apostrof) visas inte i cellerna eller vid utskrift, utan tecknet syns bara uppe i formelfältet, när du står med markören i en cell.

Denna metod är bra när det är någon enstaka cell, som ska visa innehåll med inledande nollor.

2. Formatera som text

Har du många celler som ska visa tal, med inledande nollor går det att byta Talformat [Number Format] på cellerna i stället (innan du matar in dina tal).

Som standard har celler talformatet Allmänt [General] och då får Excel själv försöka lista ut vad du matar in i cellerna; tal, datum, procent med mera.

Gör så här:

• Markera området med celler eller de kolumner som ska visa innehållet som text.
• Klicka på pilen bred vid knappen Talformat [Number Format] i gruppen Tal [Number] på menyfliken Start [Home].
• Välj Text [Text] längst ner i listan.

Denna metod är bra när det är flera celler som ska visa siffror med inledande eller avslutande nollor.

Nackdelen är att du behöver formatera cellerna med talformatet Text [Text] innan du matar in värdena.

Det hjälper inte att göra det i efterhand.

Tips! Observera att innehållet i cellerna nu visas justerat till vänster i cellerna, då Excel uppfattar allt innehåll som text. Det dock givetvis att högerjustera texten i cellerna.

3. Tusentalsavgränsning med decimaler

Vill du att avslutande decimala nollor ska visas, så kan du använda knappen Tusentalsavgränsning med decimaler [Comma Style], i gruppen Tal [Number] på menyfliken Start [Home] eller använda kortkommandot Ctrl + Shift + 1.

Tips! Då visas även talen med ett mellanrum, som tusentalsavgränsare.

Justera antalet decimaler med knapparna Öka/Minska decimal [Increase/Decrease Decimal] i samma grupp, Tal [Number] på menyfliken Start [Home].

Tips! Observera att innehållet i cellerna nu visas justerat till höger i cellerna, då Excel uppfattar innehållet som tal som går att använda i beräkningar.

4. Redovisning & Valuta

Du kan även använda talformaten Redovisning [Accounting] och Valuta [Currency] för att visa avslutande nollor i decimaltal.

Den stora skillnaden mellan dessa talformat är möjligheten att visa negativa tal med röd teckenfärg eller inte.

Tips! Justera antalet decimaler med knapparna Öka/Minska decimal [Increase/Decrease Decimal].

5. Anpassat talformat

För att visa inledande nollor i celler och samtidigt behålla innehållet som tal, så att innehållet hanteras som numeriska värden, går det att skapa ett anpassat Talformat [Number Format] i Excel.

Gör så här:

• Markera området med celler eller de kolumner som ska få det anpassade talformatet.
• Klicka på den lilla ikonen, i nedre högra delen av gruppen Tal [Number] på menyfliken Start [Home] eller använd kortkommandot Ctrl + 1 för att öppna dialogrutan Formatera celler [Format Cells].
• Gå till fliken Tal [Number].
• Välj Anpassat [Custom] listan under Kategori [Category].
• Fyll i det antal inledande nollor som ska visas i cellerna, i fältet under Typ [Type].
• Avsluta med OK.

Tips! Det går också att högerklicka i markeringen och välja Formatera celler [Format Cells].

Denna metod är bra när det är flera celler som ska visa siffror med inledande eller avslutande nollor. En annan fördel är att talen kan ingå i beräkningar.

6. Funktionen TEXT()

Det går också att formatera tal genom att använda funktioner i Excel. Med funktionen TEXT(värde;format) [TEXT(value,format)] kan du bestämma hur tal ska visas.

Ange först värde/tal eller en cell som innehåller ett tal använd sedan samma typ av formatering som när du skapade ett anpassat talformat, med ”dubbla citattecken runtom”.

=TEXT(A3;”00000000″)

=TEXT(A3,”00000000″)

Denna metod är bra när du vill visa siffror med inledande eller avslutande nollor på en annan plats än i kalkylarket. En nackdel är att talen kommer att formateras som just TEXT och kan inte användas i summeringar.

OBS! Tas cellerna med originalvärdena bort, fungerar inte formeln.

7. Funktionerna SAMMAN & HÖGER

Har du både tal och text som ska inledas med ett vist antal nollor så kan du använda funktionen SAMMAN [CONCAT] och även om det behövs funktionen HÖGER [RIGHT].

Istället för att använda funktionen SAMMAN [CONCAT] går det att använda specialtecknet & (och) som fungerar som tecken + (plus) fast på text istället för på siffror.

Formeln blir då:

=”00000000″&A3

eller

=SAMMAN(”00000000”;A3)

= CONCAT(”00000000”,A3)

Alla celler får då lika många inledande nollor. För att lösa det problemet går det att lägga till funktionen HÖGER [RIGHT] och ange att det är bara, i detta fall, åtta tecken som ska behållas.

=HÖGER(”00000000″&A3;8)

eller

=HÖGER(SAMMAN(”00000000”;A3);8)

= RIGHT(CONCAT(”00000000”,A3);8)

Tips! Funktionen SAMMAN [CONCAT] har ersatt funktionen SAMMANGOGA [CONCATENATE].

Funktionen SAMMANGOGA [CONCATENATE] finns fortfarande tillgänglig, för kompatibilitet med tidigare versioner av Excel.

8. Power Query funktion

Genom att använda den nya importfunktionen Power Query i Excel går det också att lägga till inledande nollor. Power Query hittar du på menyfliken Data [Data] i Excel version 2016 och senare.

Tips! Power Query finns att installera som ett tillägg till Excel version 2010 och 2013.

Gör så här:

• Se till att listan är formaterad som en Exceltabell, via menyvalet Tabell [Table] på menyfliken Infoga [Insert] eller via kortkommandot Ctrl + T.
• Stå med markören i tabellen.
• Välj Från tabell/intervall [From Table/Range] på menyfliken Data [Data].

• Se till att kolumnen Tal har datatypen Text [Text].
• Gå till menyfliken Lägg till kolumn [Add Column] inne i Power Query-redigeraren.
• Välj Anpassad kolumn [Custom Column].

• Ge den nya kolumnen ett namn, i fältet Nytt kolumnnamn [New column name]
• Skapa följande formel för att formatera kolumnen Tal med åtta nollor:

=Text.PadStart([Tal],8,”0″)

• Avsluta med OK.

• Klicka på Stäng och läs in [Close and load] för att lämna Power Query-redigeraren och läsa in värdena till Excel.

Resultatet kommer läggas på ett nytt kalkylblad.

Om det fylls på med fler tal, behöver du bara högerklicka och välja Uppdatera [Refresh] för att nya värden ska läggas till i tabellen.

Tips! Klicka på Stäng och läs in till … [Close and load to …] för att själv välja var resultatet ska visas.

9. Power Pivot – beräknad kolumn

Om du har menyfliken Power Pivot kan du läsa in en Exceltabell till Power Pivot-fönstret och aktivera en datamodell i Excel och där lägga till en beräknad kolumn.

Gör så här:

• Se till att listan är formaterad som en Exceltabell, via meny valet Tabell [Table] på menyfliken Infoga [Insert] eller via kortkommandot Ctrl + T.

I detta exempel har tabellen fått namnet Tal_tabell.

• Stå med markören i tabellen.
• Välj Lägg till i datamodell [Add to Data Model] på menyfliken Power Pivot.

• Dubbelklicka uppe i kolumnrubriken, där det står Lägg till kolumn [Add Column].
• Ge den nya kolumnen ett namn och avsluta med Enter.
• Skriv in följande formel uppe i formelfältet, för att formatera kolumnen Tal i tabellen Tal_tabell med formatkoden för åtta nollor:
  = FORMAT(Tal_tabell[Tal];”00000000″)

Tips! Funktionen FORMAT i Power Pivot-fönstret motsvarar funktionen TEXT i Excel.

• Välj att skapa en Pivottabell [Pivot Table] via menyfliken Start [Home] i Power Pivot-fönstret.

• Placera kolumnfältet med den nya anpassade talformatet i området Rader [Rows] i Pivottabellen.

Om det fylls på med fler tal, i tabellen behöver du bara välja att Uppdatera [Refresh] Pivottabellen, för att nya värden ska infogas i Pivottabellrapporten.

10. Power Pivot – beräknat Mått

Via menyfliken Power Pivot går det också att lägga till ett beräknat Mått [Measure] till datamodellen.

Gör så här:

• Se att datamodellen är aktiverad i arbetsboken.
• Gå till menyfliken Power Pivot.
• Välj Nytt Mått [New Measures] under knappen Mått [Measures].

• Ge måttet ett namn i fältet Måttnamn [Measure name].
• Fyll i följande DAX formel, för att sammanfoga innehållet i tabellen Tal_tabell och samtidigt formatera innehållet i kolumnen Tal i tabellen Tal_tabell med formatkoden för åtta nollor och infoga ett kommatecken.

=CONCATENATEX(Tal_tabell;FORMAT(Tal_tabell[Tal];”00000000″);”, ”)

• Kontrollera att du fyllt i formeln rätt, via knappen Kontrollera formel [Check formula].
• Låt Kategorin [Category] för talformatet vara Allmänt [General].
• Avsluta med OK.

• Placera måttet du skapade i området Värden [Values] i Pivottabellen.

Tips! Om det fylls på med fler tal, i tabellen går det att ställa in så att Pivottabellen uppdateras med automatik, när Excelarbetsboken öppnas.

Uppgradera och uppdatera dina kunskaper i Excel

Lärarledda kurser (både online och i klassrum) hittar du på infocell.se. Vi har Excelkurser i flera olika nivåer och inriktningar.

Effektiva och smarta e-kurser i Excel & Office hittar du på officekurs.se. 16 olika e-kurser, varav sju olika kurser i Excel.