Niestandardowe formatowanie komórek w Excelu

Komórka pokazuje 44800,5, a Ty chcesz widzieć 44 801, „12 kg” albo czerwoną liczbę, kiedy wynik jest ujemny. Niestandardowe formatowanie komórek w Excelu zmienia tylko to, jak liczba wygląda. Wartość w komórce zostaje ta sama i dalej liczy się w formułach.

W tym poradniku po kolei: gdzie wpisuje się własny format, czym różnią się znaki 0 i #, separator tysięcy, miejsca po przecinku, cztery sekcje formatu, dopisywanie jednostek i znaków oraz kolory.

Najwięcej daje sekcja o czterech częściach formatu. Jeden kod potrafi inaczej pokazać liczby dodatnie, ujemne, zero i tekst, a nawet ukryć zawartość komórki. Wszystkie kody z artykułu są w pliku do pobrania na końcu.

W tym poradniku:

1. Gdzie wpisać własny format liczby
2. Znaki 0 i #, czyli liczby całkowite
3. Separator tysięcy
4. Miejsca po przecinku
5. Cztery sekcje formatu: dodatnie, ujemne, zero i tekst
6. Znaki i nawiasy przy liczbie
7. Jednostki i tekst: kg, szt., zł
8. Kolory w formacie
9. Podsumowanie

Gdzie wpisać własny format liczby

Zaznacz komórki, naciśnij Ctrl + 1 (albo prawy przycisk myszy i Formatuj komórki), przejdź na kartę Liczby i wybierz kategorię Niestandardowe. Kod formatu wpisujesz w polu Typ, a nad nim od razu widać, jak będzie wyglądać pierwsza zaznaczona komórka.

Prawdziwą wartość komórki zawsze zobaczysz na pasku formuły. To dobry sposób, żeby sprawdzić, czy patrzysz na liczbę, czy tylko na jej wygląd.

Znaki 0 i #, czyli liczby całkowite

Żeby pokazać liczbę bez części ułamkowej, wpisujesz jeden z dwóch kodów:

0

#

Oba pokażą 44800,5 jako 44801. Różnią się tym, co robią, gdy cyfry nie ma: zero zawsze wyświetla cyfrę, krzyżyk tylko wtedy, gdy liczba ją ma. Komórka z wartością 0 sformatowana kodem # będzie wyglądać na pustą.

Zero przydaje się do zer wiodących. Kod 000 pokaże 7 jako 007, co przydaje się przy numerach i kodach o stałej długości.

Pamiętaj, że to tylko zaokrąglenie na ekranie. Suma kolumny liczy pełne wartości, więc może się różnić o kilka jedności od sumy tego, co widzisz.

Separator tysięcy

Odstęp między tysiącami wstawia spacja w kodzie. Są trzy warianty i każdy zachowuje się inaczej:

• # ### pokaże 44801 jako 44 801, ale zero jako pustą komórkę,
• 0 000 zawsze wyświetli co najmniej cztery cyfry, więc 7 będzie wyglądać jak 0 007,
• # ##0 to wersja, której używa się najczęściej: separator tysięcy i zawsze co najmniej jedna cyfra, więc zero pokaże się jako 0.

# ##0

Miejsca po przecinku

Po przecinku działa ta sama zasada: zero wymusza cyfrę, krzyżyk nie.

• 0,00 zawsze pokaże dwa miejsca po przecinku, czyli 5 jako 5,00,
• 0,0# pokaże jedno albo dwa miejsca, zależnie od liczby: 5 jako 5,0, a 5,25 jako 5,25,
• 0,# przy liczbie całkowitej zostawi sam przecinek na końcu, na przykład „5,”. Dlatego ten wariant lepiej omijać.

# ##0,00

Cztery sekcje formatu: dodatnie, ujemne, zero i tekst

Kod formatu może mieć do czterech części rozdzielonych średnikami. Excel czyta je zawsze w tej samej kolejności:

dodatnie;ujemne;zero;tekst

Najprościej zobaczyć to na kodzie z pliku, który zamiast liczb pokazuje numer sekcji. Liczba dodatnia wyświetli się jako 1, ujemna jako 2, zero jako 3, a tekst zostanie tekstem, bo znak @ oznacza „pokaż wpisany tekst”:

1; 2; 3; @

W praktyce sekcje przydają się do dwóch rzeczy. Pierwsza to zero pokazane jako kreska, jak w zestawieniach finansowych:

# ##0;-# ##0;"-";@

Druga to ukrywanie. Puste sekcje nie pokazują niczego, więc kod z samych średników ukrywa każdą zawartość komórki:

;;;

Komórka wygląda na pustą, ale wartość w niej jest: widać ją na pasku formuły i nadal liczy się w sumach. Jeśli chcesz ukryć same zera, a resztę zostawić, zostaw pustą tylko trzecią sekcję: 0;-0;;@.

Jedna rzecz, na której łatwo się pomylić: gdy wpisujesz własną sekcję dla liczb ujemnych, Excel nie dodaje już minusa sam. Kod 0;0 pokaże -5 jako 5. Minus trzeba dopisać w drugiej sekcji, jak w przykładach wyżej.

Znaki i nawiasy przy liczbie

Część znaków możesz wpisać w kod wprost, bez cudzysłowu: minus, plus, nawiasy, dwukropek, daszek i spację. Kod z pliku:

0!; (-0); ^-0-^

Doda wykrzyknik do liczby dodatniej (5!), liczbę ujemną weźmie w nawias (-5), a zero pokaże jako ^-0-^. Wykrzyknik jest w formacie znakiem specjalnym: wyświetla dosłownie znak, który stoi zaraz po nim.

Nawias przy liczbach ujemnych to popularny zapis w księgowości. Wersja bez minusa w środku nawiasu wygląda tak: # ##0;(# ##0).

Jednostki i tekst: kg, szt., zł

Tekst dopisany do liczby bierzesz w cudzysłów. Spacja przed jednostką też musi być w cudzysłowie, inaczej jednostka przyklei się do liczby:

0" kg";-0" kg"

Tak samo z walutą albo sztukami, na przykład # ##0,00″ zł”. Komórka nadal zawiera samą liczbę, więc da się ją sumować i porównywać. To jest cała przewaga nad wpisaniem „12 kg” wprost do komórki: wtedy Excel widzi tekst i formuły przestają liczyć.

Kolory w formacie

Na początku sekcji możesz podać kolor w nawiasie kwadratowym. Dodatnie na zielono, ujemne na czerwono, zero na niebiesko:

[Zielony]0" kg";[Czerwony]-0" kg";[Niebieski]0" kg"

W polskim Excelu nazwy kolorów wpisujesz po polsku, tak jak wyżej. Problem w tym, że te podstawowe kolory są bardzo jaskrawe: czysta zieleń i czysta czerwień w raporcie męczą oczy.

Paleta 56 kolorów: [Kolor1] - [Kolor56]

Zamiast nazwy możesz podać numer z palety Excela, od [Kolor1] do [Kolor56]. Spokojniejsza zieleń to [Kolor10], a stonowana czerwień [Kolor30]. Ten sam kod co wyżej, w łagodniejszych barwach:

[Kolor10]# ##0;[Kolor30]-# ##0;[Niebieski]0

Numerów nie da się zgadnąć, a okno formatu nie pokazuje podglądu palety. Dlatego w pliku do pobrania na końcu artykułu jest ściągawka: wszystkie 56 kodów, przy każdym liczba w tym kolorze i tło w tym samym odcieniu. Liczbę koloruje tam sam format, więc widzisz dokładnie to, co pokaże Twój Excel.

Dwie rzeczy, o których warto wiedzieć. [Kolor2] to biel, więc liczba na białym tle zniknie. A kolor z formatu wygrywa z kolorem czcionki ustawionym na wstążce: dopóki format ma kolor, zmiana czcionki na karcie Narzędzia główne nic nie da.

Zamiast podziału na dodatnie i ujemne możesz też ustawić własny próg w nawiasie. Kod [Czerwony][<50]0;0 pokaże na czerwono wartości poniżej 50, a pozostałe zwyczajnie. Przy bardziej rozbudowanych warunkach wygodniejsze jest formatowanie warunkowe.

Podsumowanie

Format niestandardowy zmienia wygląd, nie wartość. Pięć rzeczy, o które najczęściej się potyka:

• Zaokrąglenie jest tylko na ekranie. Sumy i porównania liczą pełną wartość.
• Krzyżyk ukrywa zero. Jeśli zero ma być widoczne, kod musi kończyć się na 0.
• Własna sekcja ujemna nie ma minusa. Trzeba go dopisać ręcznie.
• ;;; ukrywa, ale nie usuwa. Ukryta wartość dalej liczy się w formułach.
• Format znika po sklejeniu z tekstem. Kiedy łączysz liczbę z tekstem znakiem &, użyj funkcji TEKST (TEXT) z tym samym kodem, na przykład:

="Cena: "&TEKST(A2;"# ##0,00 zł")

Poniżej dwa pliki do pobrania: wszystkie kody z tego artykułu, wpisane do komórek z przykładowymi liczbami, oraz paleta 56 kolorów.

Nowy kurs

Tysiące, miliony i kolory w raportach: format liczb, który nie znika po odświeżeniu

✓Lekcja o niestandardowym formacie liczb: tysiące, miliony, sekcje i kolory

✓Dlaczego format liczb w tabeli przestawnej znika i jak temu zapobiec

✓Formatowanie warunkowe, które przetrwa odświeżenie

✓Gwarancja zwrotu: 30 dni

Zobacz kurs Tabele Przestawne

Gwarancja zwrotu 30 dni · wszystkie lekcje dostępne od razu