Excel cell formatting: a complete guide to formats and tricks

Master the cell formatting in Excel It makes the difference between a messy spreadsheet and a professional document that's easy to understand at a glance. It's not just about making it look nice: the correct formatting ensures Excel interprets the data accurately and performs calculations without errors, which is crucial for office work, competitive exams, and any serious data management.

When you apply a suitable format For each cell, you choose how numbers, dates, text, currency, percentages, or special codes appear, without changing the actual value Excel uses for calculations. Furthermore, with custom formats, you can achieve very specific results: add units, highlight negative values ​​in red, display dates in a particular style, or even hide values.

What exactly is cell formatting in Excel?

In Excel, each cell is like a container with two layersOn one hand, there's the actual value (the one used for calculations), and on the other, how that value is displayed on the screen. The "Format Cells" dialog box is the control panel where you decide how you want the contents to look.

This means you can completely change the appearance of a cell (number of decimal places, currency symbol, color, font, date, text, etc.) without altering the data that Excel uses to add, subtract, or perform any formula. Therefore, formatting is not just aesthetic, but a functional part of working with spreadsheets.

To work well with Excel, it is essential to assign the correct format for each range of cells: if a cell contains a date, it should be formatted as a date; if it contains a percentage, it should be formatted as a percentage; if it's a postal code, you might want to use a special format to avoid losing leading zeros and to understand functions like the ISOMITTED function.

If you enter numbers mixed with text (for example, "10 kg") without using formatting, Excel will no longer treat it as a number but as... alphanumeric stringAnd many operations will return errors or unexpected results. Custom formats come into play precisely to avoid this.

How to open the Format Cells dialog box

Excel offers several ways to access the format optionsso you can use whichever one is most convenient for you at any given time. They all lead to the same dialog box, where you can then choose a category (Number, Currency, Date, Custom, etc.).

You can open the Format Cells dialog box from the right-click menu: select a range, do Right-click on the cells and choose "Format Cells". This is probably the most common path used when you're already working on a specific area of ​​the sheet.

You can also find it in the ribbon: on the tab HomeWithin the "Cells" group, you'll find the "Format" option, which provides access to the cell formatting dialog box. In the "Number" group on the same tab, you can expand the options and access the advanced dialog box as well.

The quickest way, and the one you should memorize especially if you are preparing for competitive exams, is the keyboard shortcut Ctrl + 1Simply select the cell or range, press that combination, and the "Format Cells" dialog box will open directly, placing you on the "Number" tab.

Once inside, you will see on the left side the format categories (General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom). On the right, the "Type" area where the specific format is defined and the "Sample" section, which shows you how the cell value will look with the chosen format.

Basic number and text formats in Excel

The "General" category is the default format from Excel. It doesn't apply any special formatting: it displays numbers in a standard way, and depending on what you type, Excel decides whether to treat it as a number, date, text, etc. It's useful when you don't need any specific formatting, but it falls short when you want even a minimum of control.

The "Number" format is one of the most used because it allows define decimals and separatorsWith it, you can enable thousands separators, increase or decrease the number of visible decimal places (the default is usually 2), and choose how negative numbers are displayed (signed, in red, in parentheses, etc.). This greatly simplifies the interpretation of long lists of data.

The "Currency" format adds to the above the possibility of choosing a currency symbol This applies to all currencies: euros, dollars, pounds, and many more. Simply open the "Symbol" list and select the currency you want. Just like with Number, you can adjust the decimal places and the negative symbol style.

The "Accounting" format also works with currencies, but aligns the currency symbols and amounts In a special way, the symbols are placed in one column and the numbers are centered in the cell. It's a more "accounting" than aesthetically pleasing format, not always practical, but it exists for those who need that classic balance sheet or ledger presentation.

If you choose "Text" as the cell format, you force Excel to treat the content literallyExactly as you type it. This is very useful when you want to prevent things like dates, numeric codes with leading zeros, references, or specific strings from being interpreted. The downside is that, while it's in text format, that content can't be used directly in arithmetic operations.

Date and time formats

Excel's date handling has a trick to it: internally, each date is actually a number of days since 1-1-1900If you apply a number format to a date, you'll see that counter. That's why it's so important to correctly format a date so that you see what you want (day, month, year) and not some strange-looking number.

In the "Date" category you can choose different ways to show date: with numbers only, with the month in abbreviated or full text, with a short (two digits) or long (four digits) year, or various combinations. Furthermore, many formats depend on your system's regional settings: language, day/month/year format, etc.

For the time, the "Time" section offers a range of formats with different levels of detailYou can display only hours and minutes, include seconds, use 24-hour format, or the typical 12-hour format with AM/PM. You'll also find formats with an asterisk here, which adapt automatically if your Windows regional settings change.

In custom date formats, the key letters are d, m, and a. d, dd, ddd or dddd You control whether the day is displayed with one or two digits, or as an abbreviation or the full name of the day of the week. m, mm, mmm, mmmm You decide whether the month appears as a number, abbreviation, or full name, and with aa or aaaa You define whether the year seems short or long.

For times, use h for hours, m for minutes, and s for seconds, with one or two letters depending on whether you want one or two digits. For example, hh: mm: ss It will display 01:05:09, while h:m:s might appear as 1:5:9. Additionally, you can add AM/PM to work with 12-hour format and use the colon character (:) as a separator.

Percentage, fraction, scientific notation, and special formats

The "Percentage" format is essential when working with proportions or ratiosWhen applied, Excel interprets the value 0,15 as 15%, 1 as 100%, and so on, and you can define the number of decimal places to display. It is widely used in financial reports, data analysis, and Excel exams.

The "Fraction" format converts a number into a fractional representation depending on the type you select: simple fractions, eighths, sixteenths, etc. It is especially useful in mathematical, statistical or technical contexts where it makes sense to show fractions instead of long decimals.

The "Scientific" option displays values ​​in exponential notation of the type 1,23E + 06That is, 1,23 × 10 to the power of 6. It is a very practical format when dealing with very large or very small numbers, such as scientific data, measurements, engineering calculations, or advanced statistics.

Within "Special" you will find formats prepared for certain common codessuch as phone numbers, postal codes, or social security numbers (depending on the language/region). Its function is to display this data correctly, respecting leading zeros or particular structures, without you having to manually create a format.

Beyond these categories, Excel includes the "Custom" section, where you can create your own masks by mixing symbols and text. This section is the one that most often appears on exams. IT for competitive examsbecause it allows the design of almost any form of presentation that the courts can think of.

What are custom formats in Excel?

A custom format is a rule that dictates how it should be display the content from a cell, without ever modifying the internal value that Excel uses for calculations. This way, you can add units (kg, units, €), explanatory text, or colors, but the formulas continue to operate only with the numbers.

Imagine a table with quantities, unit price, unit weight, total weight, total amount, and average price. If you type "10 kg" directly into a cell in a column, that value ceases to be numeric, and formulas attempting to multiply it will return incorrect values. errorHowever, if you only type 10 and apply a custom format that displays "10 kg", Excel can still multiply that amount without problems.

Custom formats are defined in the "Custom" category within the cell formatting dialog box. You'll see a list with several options. predefined formats You can use it as is, modify it, or use it as a base to create your own. In the "Type" box, you write the formatting code, and in "Sample," you see a preview applied to the cell's current content.

The structure of a custom format is divided into up to four sections separated by semicolons (;):
Format for positives; Format for negatives; Format for zeros; Format for texts.

Defining all four parts is not mandatory. If you only want to format, for example, negative numbers, you could use something like ;-0.000;;You're leaving the positive, zero, and text sections blank, and only defining how negative values ​​will appear (three decimal places with a minus sign). Excel will use the standard format or display empty fields in the undefined sections.

Key symbols and wildcards in custom formats

For custom formats to work their magic, you need to know their alphabet of symbolsEach character has a specific behavior, and by combining them you achieve the exact presentation required in exercises, reports, or exams.

The fundamental symbols for numbers are three: 0, # and ?The zero (0) forces the display of a digit, even if it's a zero that adds no value, padding with zeros to the left or right as needed. The # symbol indicates a "significant digit": if the number has no other digits, it is not padded with zeros. The question mark (?) reserves spaces for non-significant digits, but leaves them blank, which helps align numbers by the decimal separator.

For example, a format 0 This will cause a value like 16,817 to be displayed as 17, even though internally it remains 16,817. A format # It will display only the significant digits without forcing extra zeros. With combinations like 0,0; 0,00; #,##0 or #,##0,00 you decide how many decimal places you want and whether to include the thousands separator.

The period (.) and the comma (,) have different behaviors depending on the regional configurationHowever, they are generally used as the decimal separator and thousands separator within the format. Typical example: #.##0,00 to display a number with thousands and two decimal places, so that 1234 would look like 1.234,00.

The percent symbol % inserts the % sign and adjust the value accordingly. The letters E or e followed by + or – (E+, E-, e+, e-) activate scientific notation. Other symbols such as +, -, /, parentheses, the dollar sign $, the euro symbol €, or any literal character can be displayed by entering the character as is or enclosing it in quotation marks if it is text.

If you need to repeat a character to fill the width of the cell, use the asterisk (*). For example, in a format 0,00* It will be filled with the last specified character until the available space is filled; if you write it as *.0,00This will repeat the character before the number. This is useful for creating visual effects, dotted lines, or automatic "fills".

Use of text, colors, and content mask

To mix numbers with fixed text within the format, the following are used: double quotesEverything within quotation marks will be displayed literally, while the rest of the code is interpreted as part of the formatting. For example, a format 0,00″ kg» It will make a value of 12,5 appear as 12,50 kg without you having to type "kg" in the cell.

The at symbol (@) is used to represent the textual content of the cell in custom formats. If you define a format for the fourth section (the text one) of the style "Attention: "@When the cell contains text, "Attention:" followed by the original value will be displayed. This is often used to automatically label certain fields without duplicating information.

Another powerful feature is the ability to apply colors from the format itselfThis is done by writing the color name in square brackets at the beginning of the section: , , , , , , , . You can also use , where n is a number between 0 and 56 that refers to Excel's internal color palette.

A typical example would be something like: 0,00;-0,00;»-«;»Attention: «@That format would display positive numbers in blue with two decimal places, negative numbers in red with a minus sign, zeros as a simple dash, and text in green preceded by the word Attention:.

In addition, custom formats allow mask valuesThat is, hiding them without deleting them. If you leave any of the sections empty (for example, using semicolons for positive and negative numbers, zeros, and text), Excel retains the value but doesn't display it. This can be combined with colors or special characters to create clearer reports.

Typical examples of custom formats

One of the most common templates is the format #.##0,00This format displays numbers with a thousands separator and two decimal places. The value 1234 would appear as 1.234,00; 12,3 would appear as 12,30; and a 0 would appear as 0,00. This is the classic style for amounts or quantities in professional documents.

If you want to add the euro symbol and also highlight negative values ​​in red, you could use something like #.##0 €;-#.##0 €In this case, the first part formats positive amounts with the € symbol, and the second part does the same but in red and with a negative sign. It's a quick way to spot losses or expenses at a glance.

The question mark (?) wildcard is also used to handle spaces. A format #?? This makes, for example, the number 1234 appear as "12 34", reserving spaces in the decimal part even if they are not significant values. By adjusting the number of question marks, you can control how many spaces are left and how the digits are aligned in columns.

In competitive examination contexts, it is common for them to request formats with a very specific pattern, such as «INVENTORY «#.###0,00% or similar. In these exercises, simply type the characters indicated in the instructions into the "Type" box of the custom format, combining numbers, symbols, and quotation marks as shown in the question.

Another typical situation in exams involves special dates. For example, you might be asked to subtract 75 days from a date and display the result in the appropriate format. Year.Month.Day where the year has four digits, the month two, and the day can have one or two. The appropriate custom format would be something like aaaa.mm.dso that a day 4 will be displayed as "4" and not as "04".

Practical application of Excel in competitive examinations

In the exams for Administrative or Administrative Assistant, the custom formats They are a common source of questions. They often pose exercises where Excel's default format doesn't exactly match what the problem asks for, so you have to use a custom template.

A classic example: you're given a date in a cell, asked to subtract a number of days, and display the result with a specific format (for example, year with four digits, month with two, and day with one or two). The formula for subtracting days is done directly (for example, =D2-75), and the rest is defining the custom date format, combining a, myd with periods or dashes as indicated.

Another type of test involves formatting two columns of numbers according to exact specifications. For example, one column could be displayed as #.###0,00% and another with a different pattern, often with fixed symbols interspersed. The key here is to write the code in the "Type" box without errors, respecting commas, periods, spaces, percent signs, etc.

If you understand the symbols (0, #, ?, «, «;”, colors in brackets, etc.) and understand that the underlying value doesn't change, these questions are quite affordableThe real difficulty usually lies in remembering the syntax accurately and not getting confused when typing the required format.

Working with custom formats not only helps you pass exams, but also gives your actual worksheets a very professional finish, with aligned columns, well-presented units, and data ready to be interpreted at a glance.

Mastering cell formatting in Excel, both basic and custom, allows you to precisely control how numbers, dates, text, and special codes are displayed, without compromising calculations. With practice, shortcuts like Ctrl + 1 And with good handling of symbols (0, #, ?, quotation marks, colors and sections separated by semicolons), your spreadsheets go from being simple tables to becoming clear, precise tools ready for any technical review or demanding exam.