قالب وردپرس درنا توس
Home / Tips and Tricks / How to change how negative numbers appear in Excel

How to change how negative numbers appear in Excel



Microsoft Excel displays negative numbers with a leading minus sign by default. It is a good idea to easily identify negative numbers, and if you are not satisfied with this default setting, Excel offers some options for formatting negative numbers.

  Default Display of Negative Numbers in Exce

Excel Provides One There are several built-in ways to display negative numbers, and you can also set custom formatting. Let us immerse ourselves.

Switch to another built-in negative count option

Note that Excel displays several built-in options depending on the region and language settings of your operating system.

For those in the US, Excel provides the following built-in negative number display options:

  • In black preceded by minus sign
  • In red
  • In parentheses (you can choose red or black) [19659010] In In the UK and many other European countries, you can usually set negative values ​​to appear in black or red with or without minus sign (in both colors). However, you have no option for parentheses. For more information about these regional settings, visit the Microsoft Web site.

    However, regardless of your location, you can add additional options by adjusting the number format discussed in the next section. [19659006] To switch to another built-in format, right-click a cell (or range of selected cells), and then click the Format Cells command. You can also press Ctrl + 1.

    In the Format Cells window, go to the Number tab. Select the category "Number" on the left. Select an option from the Negative Numbers list on the right and click OK.

    Notice that the image below shows the options you see in the US. We'll talk about creating custom custom formats in the next section, so it will not be a problem if what you want is not displayed.

    Here we decided to display negative values ​​with red brackets

    This display is clearly identifiable as the Excel default.

    Creating a Custom Negative Number Format

    You can also create your own number formats in Excel. In this way, you have the final control over the display of the data.

    Right-click a cell (or range of selected cells), and then click the Format Cells command. You can also press Ctrl + 1.

    On the Number tab on the left, select the Custom category.

     Custom formatting window for numbers

    You will see a list of various custom formats on the right. This may seem confusing at first, but there is nothing to fear.

    Each custom format is divided into up to four sections, with each section separated by a semicolon.

    The first section is for positive values, the second for negatives, the third for null values, and the last section for text. You do not have to have all sections in one format.

     Custom formatting structure

    As an example, we create a negative number format that contains all of the following information.

    • In blue
    • In parentheses
    • No decimal places

    In the Type field, type the following code:

      #, ## 0; [Blue] (#, ## 0) 

      Entering a custom number format

    Each symbol has a meaning. In this format # represents a significant digit and 0 indicates a non-significant digit. This negative number is enclosed in brackets and also displayed in blue. There are 57 different colors that you can specify in a custom number format rule by name or number. Remember that the semicolon separates the display of positive and negative numbers.

    And here's our result:

     Negative Number in Blue with Brackets

    Custom formatting is a useful Excel capability. You can use formatting beyond the defaults provided in Excel, which may not be sufficient for your needs. Formatting negative numbers is one of the most common uses of this tool.


Source link