If you work with spreadsheets, understand how to format numbers in Excel It is not a whim: it is a pillar to calculate well and present data clearlyExcel can display amounts as currency, percentages, dates, or fractions, and knowing which category to choose changes how you read and interpret your reports.
In addition to the built-in formats, Excel allows you to create custom formats for very specific cases, from from phone numbers and postal codes to color rules and advanced masksIn the following lines, you'll see all the standard categories, what they actually do, and how to combine them with custom formatting codes to solve virtually any need.
What are number formats in Excel and why do they matter?
Number formats control how a value appears in a cell without altering the stored data. This means that 1.5 and 1,50 are the same number for calculation purposes, but they are displayed differently to improve understanding of the sheet content.
Choosing the correct format helps you avoid common errors. For example, a decimal like 0,65 can be displayed as 65% with the Percentage format, and a large integer can be shortened using scientific notation to fit in the column without losing context.
There are also subtle differences between similar categories, such as Currency and Accounting, that are easily overlooked but affect the alignment of currency symbols, negative signs, and decimalsKnowing how to distinguish them saves time and leaves reports ready to share.
Finally, keep in mind that Excel adapts certain formats to the system's regional settings (Language, date, and time). Thus, What you see may vary depending on your region; for example, the decimal separator (comma or period) or how dates and times are displayed.
The built-in formatting categories in Excel
Excel includes several predefined categories to cover almost every scenario. Below you'll find the purpose of each one and the particularities you should know to use them correctly.
General
This is the default format. It displays numbers exactly as you type them, although it may round decimals if the column is narrow, and for very long numbers (12 digits or more), uses scientific notation automaticallyIf you find that the value doesn't fit, increase the column width to avoid rounding or unwanted display changes.
Number
Designed for general quantities. It allows you to define the number of decimal places, activate or deactivate the thousands separator, and choose how negatives are displayed. With this format, you have fine control over decimals and separators, which is why it is the most used in numerical analyses that do not require a monetary symbol.
Currency
Ideal for economical values, as it places a currency symbol next to the number (€, $, etc.). You can adjust decimals and separators, and select the style for negatives. This is useful when the symbol is part of the data displayed, such as in price lists or budgets.
Accounting
Similar to Currency, but designed for accounting presentation: it aligns currency symbols and decimal points in columns, and zeros and dashes are visually arranged. If you need flawless alignment for financial reportingAccounting is the right choice over Currency.
Date
Displays serial numbers as dates. Formats with an asterisk respond to regional settings changes (e.g., from the Windows Control Panel), while formats without an asterisk respond to regional settings changes. They are independent of the systemYou can choose variants such as short, long, or customize with a 2- or 4-digit year.
Time
Converts serial numbers to times. As with dates, the asterisk formats change depending on the regional settings. You can display hour, minutes and seconds in different combinations, with a 12 or 24 hour cycle.
Percentage
Multiply the value by 100 and add the % symbol. For example, 0,65 is displayed as 65%. This allows you to set the number of decimal places, which is key when you need precision or controlled rounding in metrics reports.
Fraction
Converts a decimal to its fraction equivalent based on the selected fraction type (e.g., halves, quarters, eighths). This is useful for contexts such as recipes or measurements, where the fraction is interpreted faster than the decimal.
Scientist
Displays exponential notation by replacing part of the number with an exponent E+n. If you define 2 decimal places and type 12345678901, you'll see something like 1,23E+10. This is perfect for very large or very small magnitudes and avoid overflowing columns with endless digits.
Text
Treats the content as literal text, even if it contains digits. Use this when you don't want Excel to interpret or calculate the value, for example with codes with leading zeros like "0123" that must remain intact.
Special
Includes pre-designed masks for common uses such as postal codes, phone numbers, or social security numbers. If you need a specific format not listed here, use Custom to build it with your own mask.
Personalized
It allows you to start from an existing format code and modify it, or write one from scratch. Depending on the language/version, you can store between 200 and 250 custom formats. This is the category you go to when no integrated format solves your specific case.
Tip: Regionally Sensitive Dates and Times
The formats of Date and Time The asterisks are adapted to the system region, so they change depending on whether your computer is configured in, for example, Spain or Mexico. This is key when sharing files with colleagues: what they see may differ from what you see if you do not use a fixed format (without asterisk).
If you're interested in complete consistency, choose formats without an asterisk or define a stable custom format. This way, a date won't jump out of place. "07/10" to "10/07" due to a regional change, avoiding misunderstandings in sensitive deliverables.
Remember that dates and times are serial numbers; therefore, you can manipulate them. Subtracting 75 days from a date is as simple as =date_cell – 75, and then apply a custom format to display it however you want.
To avoid unwanted automatic interpretations (for example, when typing 10e5 or 1-2), apply the Text format to the cells first. This way, Excel will not convert the input to date or scientific notation without your permission.
Custom Formats: Complete Code Guide and Examples
When standard formats aren't enough, custom formatting comes into play. You can access it by right-clicking on the cell > Format Cells, or by Ctrl + 1 to open the dialog box and choose the Custom category.
A custom format can include up to four sections separated by semicolons (;) in the order: positive;negative;zero;textsIf you write fewer sections, Excel assumes default values ​​for the missing ones.
Wildcards and basic symbols
- #: occupies a digit position if one exists (does not display leading zeros). If the value is 0, the position is empty.
- 0: forces a digit; if it doesn't exist, 0 is displayed (useful for leading zeros and setting decimals).
- ?: reserves space for a digit or space; ideal for align decimals in columns.
- . y ,: decimal and thousands separators (adapted to your regional settings).
- %: multiply by 100 and add the percentage sign.
- E+, e+, E-, e-: scientific notation (exponential).
- + – / () : $: literal characters displayed as is.
- "text": shows the literal in quotes, for example
0" kg"to attach a fixed suffix. - @: marker for text (used in the text section to preserve and enrich strings).
- *character: repeats the character to fill the width (for example,
*-to dot to the edge). - _character: inserts a space the width of the given character (very useful for fine adjustments in reports).
Colors and palette
- : colors by name (Black, Blue, Cyan, Green, Magenta, Red, White, Yellow).
- : uses the palette index (0 to 56), for example
to apply a specific tone in legacy formats.
Dates and times in custom formats
- d, dd: day with 1 or 2 digits; ddd y dddd show abbreviation or full name of the day.
- m, mm: numerical month; mmm y MMM show abbreviation or full name of the month.
- aa, aaaa: year with 2 or 4 digits.
- h, hh: hours; mm minutes; ss seconds. You can combine them as h:mm:ss in 24-hour clock.
Practical examples of custom formats
- #.##0,00: displays thousands separator and 2 decimal places. The value 1234 will be displayed as 1.234,00 (depending on region).
- #.##0 «€»;-#.##0 «€»: positive with euro; negatives appear in red with a sign. Perfect for price lists.
- #?? o # ???: reserves spaces for aligning columns (e.g., 12 3 vs. 1 23 4). Useful for aligned squares.
- #.###0,00%: percentages with thousands and 2 decimal places, ideal for reports conversion rate or similar metrics.
- Year.Month.Day with d variable:
aaaa.mm.dIt shows the year with 4 digits, month with 2 digits and the day with 1 or 2 digits as appropriate, avoiding the leading 0 if the day is less than 10; that is, "4" instead of "04". - Special masks: A conditional rule phone can be achieved with
###\-####;(###) ###\-####to show (555) 555-1234 or, if shorter, a pattern without a prefix. Note the use of\-to force the literal script.
Remember that you can build formats by section. For example: #,##0.00;-#,##0.00;\-;@ shows positives with two decimals, negatives in red, zero as middle dash and the text as is.
How to apply and change formats without going crazy
To apply quick formatting, select the cells and use the gallery in the Number group (Home tab). If you need advanced options, open the Format Cells box with Ctrl + 1, scroll through Number, Currency, Accounting, etc., and confirm with OK.
For custom formats, go to the Custom category and type your code in the Type box. You can start from a pre-designed format and adapt it, which is easier than creating one yourself. Absolute zero if you are just starting out.
If you see ##### After applying a format, the cell is not wide enough. Widen the column by dragging the border or double-click the divider between headings to automatic adjustmentThis does not change the number, just its presentation.
When Excel interprets something you don't want (for example, 10e5 as scientific notation or 1-2 as a date), apply the Text format first and then type. This ensures that there is no automatic conversion that alters what you intended to capture.
If you need to see the code for a built-in format as a base, go to Custom and check the Type box. This is the quickest way to understand how it's built and which part you should retouch.
Real cases and typical exam exercises
In Excel tests (competitive exams or other exams), you are often asked to apply a specific mask. A classic approach is to subtract days from a date and display it with a custom pattern. For example, if in D2 you have today's date, in D3 you can put =D2-75 and then apply the format aaaa.mm.d to see year.month.day with the requested lengths.
Another common example is formatting one column as a percentage and another as a monetary mask. If you are asked something like #.###0,00% For INVENTORY1, just type that mask in Custom; for INVENTORY2, adjust the indicated string (symbols, spaces, literals in quotes) and check that the preview matches what was requested.
There are also exercises to reinforce negatives with color, for example, putting losses in red and gains in black. With a mask like #,##0.00;-#,##0.00 You will have it resolved in a clear and professional manner.
When the statement requires keeping leading zeros (codes or identifiers), apply 0 as a wildcard. For example, 00000 will ensure five digits always, transforming 123 into 00123 without changing the underlying value.
For accounting lists, remember that Accounting automatically aligns symbols and decimals. If you also need to dot to the edge, add *. or the corresponding character in the format, which generates an elegant and uniform fill line.
Advanced Tips for Robust Formats
Combine colors with simple conditions. Although custom formats are not conditional rules, you can display negatives in red and positives in blue with #,##0;-#,##0, and leave the zero as a hyphen or text as you see fit.
If you want aligned visual blocks, use the wildcard ? that reserves space without forcing a 0. Post-process columns with # ??? or variants helps the numbers stay perfectly aligned in printed reports.
To add fixed suffixes or prefixes (e.g., "kg" or "% monthly"), use literals in quotes. A format like 0,00″ kg» adds the text without affecting the value, so formulas continue to work without further adjustments.
Remember that in date formats, d shows days without a leading zero, while dd adds it. If the statement requires that the day can have one or two digits, use d instead of dd so that a 4 does not become 04.
Finally, if you work with multicultural teams, document on a supporting sheet which mask you use and why, and specify if it depends on regional configurationYou'll avoid surprises when someone opens the file in another location.
Once you master the built-in categories (General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom) and understand the wildcards (#, 0, ?, literals, colors, and sections), you can tackle budgets, metrics, and reports with impeccable presentations, minimize reading errors and respond fluently to demanding exercises such as those for competitive examinations or internal audits.
