Change the way negative numbers are displayed. Select the cell or range of cells that you want to format with a negative number style. If you're using Windows, press Ctrl+1. If you're using a Mac, press +1. In the Category box, click either Number or Currency. Under Negative numbers, select an.

How to display negative numbers in brackets in Excel?

Have you ever noticed that Excel can display your negative numbers with brackets? Excel’ Format Cells feature can finish this job perfectly. To know more about this operation, please read the following article.

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment..
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Display negative numbers in brackets in Excel

Supposing you have a list of numbers which contains both positive and negative numbers as below screenshot shown, and you can put the brackets around the negative numbers as following steps:

1. Select the number cells, right click, and choose Format Cells from the context menu, see screenshot:

2. In the Format Cells dialog box, under Number tab, click Number option, and then choose the decimal places as you need, (normally, you need to choose the largest decimal places of your existing data, so that your numbers are not be changed). And then click (1234.210) in the Negative numbers list box, see screenshot:

3. Then click OK to close this dialog, and the selected negative numbers have been displayed around brackets, see screenshots:

Notes:

Enable tls 1.2 on chrome for mac. 1. After applying the formatting, all the numbers are displayed as the same decimal places that you are set.

2. If there are all whole numbers in your worksheet, you can set the Decimal places as 0 in the Format Cells dialog.

Related articles:

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges..
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Introduction

Number formats control how numbers are displayed in Excel. The key benefit of number formats is that they change how a number looks without changing any data. They are a great way to save time in Excel because they perform a huge amount of formatting automatically. As a bonus, they make worksheets look more consistent and professional.

Video: What is a number format

What can you do with custom number formats?

Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red.

Where can you use custom number formats?

Many areas in Excel support number formats. You can use them in tables, charts, pivot tables, formulas, and directly on the worksheet.

• Worksheet - format cells dialog
• Pivot Tables - via value field settings
• Charts - data labels and axis options
• Formulas - via the TEXT function

What is a number format?

A number format is a special code to control how a value is displayed in Excel. For example, the table below shows 7 different number formats applied to the same date, January 1, 2019:

The key thing to understand is that number formats change the way numeric values are displayed, but they do not change the actual values.

Where can you find number formats?

On the home tab of the ribbon, you'll find a menu of build-in number formats. Below this menu to the right, there is small button to access all number formats, including custom formats:

This button opens the Format Cells dialog box. You'll find a complete list of number formats, organized by category, on the Number tab:

Note: you can open Format Cells dialog box with the keyboard shortcut Control + 1.

General is default

By default, cells start with the General format applied. The display of numbers using the General number format is somewhat 'fluid'. Excel will display as many decimal places as space allows, and will round decimals and use scientific number format when space is limited. The screen below shows the same values in column B and D, but D is narrower and Excel makes adjustments on the fly.

How to change number formats

You can select standard number formats (General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text) on the home tab of the ribbon using the Number Format menu.

Note: As you enter data, Excel will sometimes change number formats automatically. For example if you enter a valid date, Excel will change to 'Date' format. If you enter a percentage like 5%, Excel will change to Percentage, and so on.

Shortcuts for number formats

Excel provides a number of keyboard shortcuts for some common formats:

See also: 222 Excel Shortcuts for Windows and Mac

Where to enter custom formats

At the bottom of the predefined formats, you'll see a category called custom. The Custom category shows a list of codes you can use for custom number formats, along with an input area to enter codes manually in various combinations.

When you select a code from the list, you'll see it appear in the Type input box. Here you can modify existing custom code, or to enter your own codes from scratch. Excel will show a small preview of the code applied to the first selected value above the input area.

Note: Custom number formats live in a workbook, not in Excel generally. If you copy a value formatted with a custom format from one workbook to another, the custom number format will be transferred into the workbook along with the value.

How to create a custom number format

To create custom number format follow this simple 4-step process:

1. Select cell(s) with values you want to format
2. Control + 1 > Numbers > Custom
3. Enter codes and watch preview area to see result
4. Press OK to save and apply

Tip: if you want base your custom format on an existing format, first apply the base format, then click the 'Custom' category and edit codes as you like.

How to edit a custom number format

You can't really edit a custom number format per se. When you change an existing custom number format, a new format is created and will appear in the list in the Custom category. You can use the Delete button to delete custom formats you no longer need.

Warning: there is no 'undo' after deleting a custom number format!

Structure and Reference

Excel custom number formats have a specific structure. Each number format can have up to four sections, separated with semi-colons as follows:

This structure can make custom number formats look overwhelmingly complex. To read a custom number format, learn to spot the semi-colons and mentally parse the code into these sections:

1. Positive values
2. Negative values
3. Zero values
4. Text values

Not all sections required

Although a number format can include up to four sections, only one section is required. By default, the first section applies to positive numbers, the second section applies to negative numbers, the third section applies to zero values, and the forth section applies to text.

• When only one format is provided, Excel will use that format for all values.
• If you provide a number format with just two sections, the first section is used for positive numbers and zeros, and the second section is used for negative numbers.
• To skip a section, include a semi-colon in the proper location, but don't specify a format code.

Characters that display natively

Some characters appear normally in a number format, while others require special handling. The following characters can be be used without any special handling:

Escaping characters

Some characters won't work correctly in a custom number format without being escaped. For example, the asterisk (*), hash (#), and percent (%) characters can't be used directly in a custom number format – they won't appear in the result. The escape character in custom number formats is the backslash (). By placing the backslash before the character, you can use them in custom number formats:

Placeholders

Certain characters have special meaning in custom number format codes. The following characters are key building blocks:

Zero (0) is used to force the display of insignificant zeros when a number has fewer digits than than zeros in the format. For example, the custom format 0.00 will display zero as 0.00, 1.1 as 1.10 and .5 as 0.50.

Pound sign (#) is a placeholder for optional digits. When a number has fewer digits than # symbols in the format, nothing will be displayed. For example, the custom format #.## will display 1.15 as 1.15 and 1.1 as 1.1.

Question mark (?) is used to align digits. When a question mark occupies a place not needed in a number, a space will be added to maintain visual alignment.

Period (.) is a placeholder for the decimal point in a number. When a period is used in a custom number format, it will always be displayed, regardless of whether the number contains decimal values.

Comma (,) is a placeholder for the thousands separators in the number being displayed. It can be used to define the behavior of digits in relation to the thousands or millions digits.

Asterisk (*) is used to repeat characters. The character immediately following an asterisk will be repeated to fill remaining space in a cell.

Underscore (_) is used to add space in a number format. The character immediately following an underscore character controls how much space to add. A common use of the underscore character is to add space to align positive and negative values when a number format is adding parentheses to negative numbers only. For example, the number format '0_);(0)' is adding a bit of space to the right of positive numbers so that they stay aligned with negative numbers, which are enclosed in parentheses.

At (@) - placeholder for text. For example, the following number format will display text values in blue:

See below for more information about using color.

Automatic rounding

It's important to understand the Excel will perform 'visual rounding' with all custom number formats. When a number has more digits than placeholders on the right side of the decimal point, the number is rounded to the number of placeholders. When a number has more digits than placeholders on the left side of the decimal point, extra digits are displayed. This is a visual effect only; actual values are not modified.

Number formats for TEXT

To display both text along with numbers, enclose the text in double quotes ('). You can use this approach to append or prepend text strings in a custom number format, as shown in the table below.

Number formats for DATES

Dates in Excel are just numbers, so you can use custom number formats to change the way they display. Excel many specific codes you can use to display components of a date in different ways. The screen below shows how Excel displays the date in D5, September 3, 2018, with a variety of custom number formats:

Number formats for TIME

Times in Excel are fractional parts of a day. For example, 12:00 PM is 0.5, and 6:00 PM is 0.75. You can use the following codes in custom time formats to display components of a time in different ways. The screen below shows how Excel displays the time in D5, 9:35:07 AM, with a variety of custom number formats:

Note: m and mm can't be used alone in a custom number format since they conflict with the month number code in date format codes.

Number formats for ELAPSED TIME

Elapsed time is a special case and needs special handling. By using square brackets, Excel provides a special way to display elapsed hours, minutes, and seconds. The following screen shows how Excel displays elapsed time based on the value in D5, which represents 1.25 days:

Number formats for COLORS

Excel provides basic support for colors in custom number formats. The following 8 colors can be specified by name in a number format: [black] [white] [red][green] [blue] [yellow] [magenta] [cyan]. Color names must appear in brackets.

My invoice download. Easily monitor customer accounts, manage inventory,track business expenses, and print monthly statements for all of yourcustomers with a single click.Now you can have the #1 Best Selling Invoice Software that is easy-to-use,affordable and-as with all MySoftware products-comes with a money backguarantee. .In business, few things are more important than invoices and estimates. Getthem wrong, and you don't get paid. Get them right, and you'll ensure thatyour business maintains the cash flow it needs to grow.Let MyInvoices & Estimates Deluxe effectively manage your billing and cashcollection by helping you create and process professional, accurate invoicesand estimates in minutes.

Colors by index

In addition to color names, it's also possible to specify colors by an index number (Color1,Color2,Color3, etc.) The examples below are using the custom number format: [ColorX]0'▲▼', where X is a number between 1-56:

The triangle symbols have been added only to make the colors easier to see. The first image shows all 56 colors on a standard white background. The second image shows the same colors on a gray background. Note the first 8 colors shown correspond to the named color list above.

Apply number formats in a formula

Although most number formats are applied directly to cells in a worksheet, you can also apply number formats inside a formula with the TEXT function. For example, with a valid date in A1, the following formula will display the month name only:

The result of the TEXT function is always text, so you are free to concatenate the result of TEXT to other strings: