Custom formats
When formatting the content of a cell, one of the items that we can set up is the way the content of the cell is displayed. Since the options for creating such formats are in the Number tab of the Format Cells dialog box, the settings to be addressed are often called “formatting the number”. Although the content of a cell does not have to be a number. It can also be text, date, time or other information. Have you ever wondered: how to create custom formats?
Ako unesemo u ćeliju određenu vrednost, da bi smo odredili format zapisa broja potrebno je da otvorimo Format Cells dijalog prozor. Ovo možemo da učinimo na više načina, a najlakši je korišćenjem prečice, pomoću tastera CTRL+1. Zatim, pozicioniraćemo se na Number karticu, gde se nalaze opcije za podešavanje načina prikaza sadržaja ćelije. Sa leve strane nalazi se lista kategorija formatiranja sadržaja ćelije. Izborom kategorije Custom, a zatim unosom formata u polje Type (niza simbola koji određuju način prikaza sadržaja ćelije), pravimo korisnički definisan format. Najčešće korišćeni simboli za kreiranje korisnički definisanog formata prikazani su u tabeli:
If we enter a certain value in the cell, in order to determine the format of the number record it is necessary to open the Format Cells dialog box. This can be done in many ways, and the easiest way to do this is by using the shortcut, using the CTRL + 1 keys. Then, we will position ourselves on the Number tab, where there are options for adjusting the cell format. On the left side there is a list of categories for formatting the contents of the cell. By choosing the Custom category, then typing the format into the Type field (the string of symbols that determine how the cell’s content is displayed) we can create user-defined format. The most commonly used symbols for creating a custom format are shown in the table:
SYMBOL | MEANING |
0 | Indicates the place where the digit is located. If this digit is zero, it will always be shown as zero. |
# |
Similar to the previous case, it indicates the location where there is a digit. If it is zero, it will not be displayed. |
? | Also, denotes where a digit is located. If this number is zero, instead of the empty space, it will be displayed in order to maintain alignment within the cell. |
. | The decimal point is the symbol for separating the whole of the decimal part of the number (unless otherwise specified in the regional settings). |
, | A comma is a symbol for grouping thousands within a number (unless otherwise set in regional settings). |
= | Used to display the Equal sign. |
– | It is used to indicate the signs of negative numbers. |
+ | It is used to indicate the signs of positive numbers. |
$ | Dollar symbol. |
€ | Euro symbol. |
{ } | Curved brackets. |
_ | “Underline” (“bottom line”) represents the symbol that makes the distance to the width as wide as the preceding character. |
* | It performs a repetition of the character behind it until it completes the entire contents of the cell. |
@ | It is used to show text entered within a cell. |
” Text” | A text written between two quotation marks indicates the fixed text that will be displayed in the cell. |
[Color] | The colour name in English between parentheses indicates the colour of the cell content. The names you can use are: Red, Black, White, Green, Yellow, Blue etc. Excel supports a total of 56 colors and can be selected by entering word Color and colour number, eg [Color 33]. |
d,dd,ddd | When entering a date, it indicates different ways of displaying a date. |
m,mm,mmm | When entering a date, it indicates different ways of displaying a month. |
yy,yyyy | When entering a date, it indicates different ways of displaying a year. |
h,hh | When entering the time, it indicates the different ways of displaying the hours. |
m,mm | When entering the time, it indicates the different ways of displaying the minutes. |
s,ss | When entering the time, it indicates the different ways of displaying the seconds. |
When creating a rule for custom formats, it would be convenient to first select a format, e.g. Currency, and then click on Custom. In this way, we create a custom format by modifying some existing format. It is also possible to create more rules for user-defined formats (for example, a different record of positive and negative numbers). In this case, the rules are separated by the “;” sign, and it is possible to define up to four formats, which in turn indicate: the positive number format, the negative number format, the format for zero, the text format. The topic discussed here is quite abstract and you will understand how things work by trying different combinations of symbols and see what they acomplish.