Formatowanie niestandardowe umożliwia nam modyfikowanie dostępnych kodów formatów, dostosowując je do swoich potrzeb. W zależności od wersji programu możemy dodać nawet do 250 niestandardowych formatów.

Podstawowy format niestandardowy składa się z 4 sekcji:

WARTOŚCI DODATNIE ; WARTOŚCI UJEMNE ; WARTOŚCI ZEROWE ; TEKST

Każda sekcja jest oddzielona od siebie znakiem średnika. Tworząc własne formatowanie niestandardowe nie musimy definiować za każdym razem wszystkich czterech sekcji. Możemy zdefiniować tylko jedną, wówczas będzie ona obowiązywała dla wszystkich sekcji bez względu na ich wartość czy znak.

Należy pamiętać, że chcąc zdefiniować tylko pierwszą i ostatnią sekcję musimy pomiędzy nimi dodać puste sekcje czyli 2 znaki średnika na pominiętą sekcję.

Formatowanie niestandardowe nigdy nie zmienia nam wartości naszych danych jedynie sposób ich wyświetlania w komórkach.

Formaty dodane przez nas są zapisane lokalnie w pliku, w którym je stworzyliśmy. W przypadku chęci wykorzystania ich w innych arkuszach należy zapisać skoroszyt jako szablon i przy jego pomocy stworzyć nowy arkusz.

Excel umożliwia nam usunięcie jedynie tych formatów, które sami dodaliśmy. Nie ma możliwości usunięcia standardowych formatów danych.

Wyświetlanie tekstu w formatowaniu niestandardowym

Jedną z możliwości jakie nam daje formatowanie niestandardowe jest dodanie swojego tekstu. Służą do tego znaki, które zebrałam w tabeli poniżej.

ZNAKFUNKCJA ZNAKU
” „służy do wyświetlenia tekstu lub liczby
@dołączanie sekcji do wpisu tekstowego
_dodawanie spacji
*powtarzanie znaków
Tabela znaków dołączania tekstu i odstępów

Wyświetlanie ciągu znaków

Dla przykładu, mamy standardowy format liczbowy, czyli 0,00. Chcemy aby przy ujemnych liczbach pojawił się tekst liczba ujemna, a przy liczbach dodatnich pojawił się napis liczba dodatnia. Zgodnie z powyższą tabelą, aby to zrobić wykorzystamy cudzysłów. Do wcześniej wspomnianego formatu liczbowego należy dodać ” liczba dodatnia” ; -0,00 ” liczba ujemna”. Całość nowego formatu powinna wyglądać następująco:

Niestandardowy format liczbowy z dodanym tekstem w formatowaniu niestandardowym
Niestandardowy format liczbowy z dodanym tekstem

W powyższym zapisie, ważne jest aby po pierwszym cudzysłowie pojawiła się spacja, wówczas będziemy mieć odstęp do liczby.

Po wpisaniu do komórki z naszym niestandardowym formatem liczby -100 wyświetli się nam w komórce -0,10 liczba ujemna. Analogicznie jak wpiszemy 100 w komórce wyświetli nam się 0,10 liczba dodatnia.

W Excelu występują znaki, które nie wymagają umieszczania w cudzysłowie aby zostały wyświetlone. Należą do nich:

  • znak dolara $
  • znak plusa i minusa + –
  • lewy i prawy nawias okrągły ( )
  • dwukropek :
  • akcent cyrkumfleksowy (tzw. daszek) ^
  • apostrof
  • lewy i prawy nawias klamrowy { }
  • znak mniejszości i większości < >
  • znak równości =
  • ukośnik /

Dodawanie sekcji tekstowej

W pierwszej kolejności, aby dodać sekcję tekstową należy wykorzystać standard tekstowy, czyli znak @. Jeżeli dodajemy sekcję do liczb, to będzie ona ostatnią sekcją. Natomiast, jeśli chcemy wyświetlić sekcję tekstową z innym tekstem, należy go wpisać między cudzysłów. Jeżeli dodamy sekcję tekstową przed liczbę cała komórka zostanie automatycznie przekonwertowana na tekst.

Dodawanie spacji

Zgodnie z powyższą tabelą znaków, aby dodać spację należy wykorzystać znak podkreślenia (tzw. podłoga) _ , a następnie wstawić znak, którego szerokością ma być spacja. Przykładowo chcemy dodać spację szerokości dużego znaku X, więc zapiszemy to jako _X.

Powtarzanie znaków

Jeżeli denerwują nas puste przestrzenie w komórkach możemy się ich pozbyć przy użyciu opcji powtarzania znaków. Aby z niej skorzystać użyjemy znaku gwiazdki * . Przykładowo chcemy by po liczbie wpisanej w komórce na całej szerokości komórki wyświetlił się znak „-” aż do końca komórki.

Czyli chcielibyśmy uzyskać taki efekt, jak na zdjęciu poniżej.

Niestandardowe formatowanie z wykorzystaniem znaku powtórzenia
Niestandardowe formatowanie z wykorzystaniem znaku powtórzenia

Powyższe wypełnienie komórki otrzymamy modyfikując format liczbowy z 0 na 0 *- . W ten sposób znak, który pojawia się po gwiazdce będzie się powtarzał, aż zapełni całą szerokość komórki.

Wykorzystanie miejsc dziesiętnych w formatowaniu niestandardowym

Jeżeli nasze dane są liczbami to przyda nam się znajomość zastosowania poniższych symboli zastępczych cyfry przy tworzeniu niestandardowych formatów liczbowych.

ZNAKFUNKCJA ZNAKU
0 (zero)wyświetla zera nieznaczące
#nie wyświetla dodatkowych zer
?dodaje znak spacji dla zer nieznaczących
. (kropka)umożliwia wyświetlenie separatora dziesiętnego w liczbie
Tabela symboli zastępczych cyfry w formatowaniu niestandardowym

Miejsca dziesiętne i cyfry znaczące

Jak wykorzystać powyższe znaki w praktyce? Wszystko zależy od tego co potrzebujemy zrobić. Załóżmy, że chcemy aby nasze liczby zawsze miały wyświetlone 4 miejsca po przecinku. W takim przypadku utworzymy format #,0000 . Nie interesują nas cyfry z lewej strony przecinka, dlatego używamy symbolu zastępczego #. Natomiast po prawej stronie przecinka chcemy, żeby wyświetlały się 4 cyfry nie zależnie od tego czy będą to zera czy inne liczby.

Innym przykładem jest sytuacja, w której chcemy aby nasze liczby zostały wyświetlone pod sobą z wyrównanymi miejscami po przecinku. Zastosowanie w tym przypadku będzie mieć znak zapytania. Tak więc, aby wyrównać liczby zgodnie z ilością miejsc po przecinku utworzymy format: ???,???.

Separator tysięcy

Separatory tysięcy wyświetla duże liczby w przyjemny dla oka sposób. Znak spacji występujący po symbolu zastępczym cyfry (# lub 0) powoduje przeskalowanie liczby przez wielokrotność jednego tysiąca. W efekcie z liczby 1200000000 mamy ładnie wyglądająca liczbę 1 200 000 000.

Formatowanie niestandardowe – określanie kolorów

W formatowaniu niestandardowym mamy możliwość określenia koloru sekcji przy użyciu jednego z 8 kolorów. Nazwa koloru musi zostać wpisana w nawiasy kwadratowe. W tabeli poniżej są przedstawione kody kolorów dostępnych w programie Excel. Określenie koloru występuje zawsze na początku danej sekcji.

[Czarny]
[Zielony]
[Biały]
[Niebieski]
[Amarantowy]
[Żółty]
[Błękitny]
[Czerwony]
Tabela kodów kolorów w formatowaniu niestandardowym

Dużo więcej możliwości kolorowania danych w zależności od zawartości komórki jest w warunkowym formatowaniu komórek, o którym szerzej napiszę w osobnym artykule.

Formatowanie niestandardowe – w praktyce

Wystarczy teorii, teraz czas przećwiczyć to wszystko w praktyce. Mamy zestawienie klientów, którzy zaciągnęli w naszym banku pożyczki na większe i mniejsze kwoty. Oprócz tego mamy informację o wysokości zadłużenia klienta oraz o jego spłacie. Poniżej zdjęcie tabeli do ćwiczeń przed formatowaniem.

Tabela początkowa do ćwiczenia z formatowaniem niestandardowym
Tabela początkowa

W punktach poniżej zapisałam wszystkie założenia jakie mają zostać zastosowane do wyświetlenia danych w powyższej tabeli.

  1. Liczby z separatorem tysięcy
  2. Liczby z dwoma miejscami po przecinku
  3. Kwoty ujemne w rubryce „winien” na czerwono
  4. Kwoty dodatnie w rubryce „winien” na zielono
  5. Wartości ujemne ze znakiem minus
  6. Zamiast zera będzie znak myślnika
  7. Liczby wyrażone jako PLN
  8. Dodanie formy grzecznościowej przed imieniem klienta

Tworzymy sekcję dodatnią

Zaczynamy od zdefiniowania sekcji dodatniej. Zaznaczam zakres komórek od E3 do E14, czyli wszystkie wiersze w kolumnie „winien” bez nagłówka i naciskam CTRL+1. Wybieram formatowanie niestandardowe i wybieram standard liczbowy czyli 0,00. Na podglądzie możemy zobaczyć jak będzie wyglądała nasza liczba. Aby dodać separator dopiszemy jeszcze # ## przed zerami. Chcemy jeszcze, żeby liczby dodatnie były koloru zielonego, po cyfrze pojawiał się skrót walutowy PLN. Więc przed wszystkimi znakami dopiszemy [Zielony]. Natomiast na końcu dopiszemy PLN w cudzysłowie.

Format sekcji dodatniej w formatowaniu niestandardowym
Format sekcji dodatniej

Tworzymy sekcję ujemną

Następnie po średniku definiujemy wartości ujemne. Chcemy, żeby wyglądały tak samo jak sekcja dodatnia dlatego skopiujemy sobie format # ##0,00″ PLN”, ale tym razem przed znakami, a bezpośrednio po średniku dopiszemy [Czerwony]. Po kolorze dopiszemy jeszcze minus. Zapis będzie wyglądać następująco:

Format sekcji ujemnej w formatowaniu niestandardowym
Format sekcji ujemnej

Tworzymy sekcję zerową

Do dotychczasowego zapisu dodajemy kolejny średnik i zaczynamy definiować sekcję zerową. Aby zamiast zera wyświetlał nam się myślnik musimy po prostu w cudzysłowie zdefiniować ten znak. W związku z powyższym, sekcja zerowa będzie wyglądać następująco:

Format sekcji zerowej w formatowaniu niestandardowym
Format sekcji zerowej

Modyfikujemy sekcję tekstową

Chcemy dodać formę grzecznościową np. Sz.P. przed imieniem klienta. Aby to zrobić zaznaczam obszar od B3 do B14, czyli wszystkie wiersze w kolumnie „klient” bez nagłówka, następnie klikam CTRL +1 i wybieram formatowanie niestandardowe. Na liście wybieram typ tekstowy czyli @, a przed nią wpisuje „Sz.P. „.

Format sekcji tekstowej w formatowaniu niestandardowym
Format sekcji tekstowej

Ostatnie szlify

Do tej pory zajmowałam się kolumnami „winien” i „klient”. Teraz przyszła pora na dwie środkowe kolumny, w których chciałabym zrealizować dwa pierwsze punkty z naszej listy rzeczy do zrobienia, czyli dodać separator tysięcy oraz dwa miejsca po przecinku. Zaznaczam obszar od C3 do D14 i otwieram formatowanie komórek skrótem klawiaturowym CTRL +1 .

Wybieram formatowanie niestandardowe i wpisuje # ##0,00. Przypomniałam sobie, że jeżeli jest zero to chciałabym, żeby wyświetlał się minus. Wiem, że w tych rubrykach nie będę mieć liczb ujemnych, dlatego też po wcześniej wpisanym formacie dodaje dwa średniki, aby zaznaczyć pustą sekcję wartości ujemnej. Następnie wpisuje „-” w sekcji zerowej. Całość zapisu wygląda następująco:

Format dodatni i zerowy z pominięciem ujemnego w formatowaniu niestandardowym
Format dodatni i zerowy z pominięciem ujemnego

Efekt końcowy

Nasze zestawienie po zrobieniu wszystkich formatów niestandardowych wygląda następująco:

Tabela po formatowaniu niestandardowym
Tabela po użyciu formatowania niestandardowego

Jeżeli ten tekst był dla Ciebie ciekawy, podziel się nim ze znajomymi. To jest najlepszy sposób na docenienie Autorki tekstu 😉 Jeśli masz pytania do tego artykułu zadaj je w komentarzu pod postem. Postaram się odpowiedzieć na wszystkie jak najszybciej.