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

How to change how Excel shows negative numbers

By default, Microsoft Excel displays negative numbers with a leading minus sign. It is good to make negative numbers easy to identify, and if you are not satisfied with this standard, Excel contains some different options for formatting negative numbers.

  Standard display of negative numbers in Exce

Excel provides a A couple of built-in ways to display negative numbers, and you can also set custom formatting. Let's dive in.

Change to another built-in negative number option

One thing to note here is that Excel will display different built-in options depending on region and language settings in your operating system.

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

  • In black, with previous minus sign
  • In red
  • In parentheses (you can choose red or black) [19659010] In the UK and many other European countries, you can usually enter negative numbers to be displayed in black or red and with or without a minus sign (in both colors) but have no option for parentheses. You can learn more about these regional settings on the Microsoft website.

    However, wherever you are, you can add additional options by customizing number formats that we will cover in the next section. [19659006] To switch to another built-in format, right-click a cell (or a selection of selected cells), and then click the "Format Cells" command. You can also press Ctrl + 1.

    In the Format Cells window, switch to the "Number" tab. To the left, select the category "Number". To the right, select an option from the "Negative numbers" list and then press "OK".

    Please note that the image below shows the options you would see in the United States. We'll talk about creating your own custom formats in the next section, so there's no problem with what you don't want to see.

    Here we have chosen to show negative values ​​in red with brackets

    This screen is much more identifiable than Excel standard.

    Creating a Custom Negative Number Format

    You can also create custom number formats in Excel. This gives you the ultimate control over how data is displayed.

    Start by right-clicking on a cell (or a selection of selected cells), and then clicking the "Format Cells" command. You can also press Ctrl + 1.

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

     Custom Number Format Window

    You can see a list of different custom formats to the right. This may seem a bit confusing at first, but it's nothing to fear.

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

    The first part is for positive values, the second for negative, the third for zero values ​​and the last section for text. You do not need to have all sections in one format.

     Structure with custom formatting

    For example, let's create a negative number format that contains all below.

    • In blue
    • In brackets
    • No decimals

    In the Type box, enter the code below.

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

      Enter a custom number format

    Each symbol has a sentence, and in this format represents the # display of a significant number and 0 is the display of a slight figure. This negative number is enclosed in parentheses and is also shown in blue. There are 57 different colors you can enter with a name or number in a custom number format rule. Remember that the semicolon separates the positive and negative numbers.

    And here is our result:

     Negative numbers in blue with brackets

    Custom formatting is a useful Excel skill to have. You can take formatting beyond the default settings in Excel that may not be enough for your needs. Formatting negative numbers is one of the most common uses of this tool.

Source link