Chart & Visualization Issues

Format Debits and Credits in Excel with a Custom Number Format

John WilsonJohn Wilson Nov 20, 2026 869 views

Question details

Display dollar values as debits and credits using a custom number format.

Format Debits and Credits in Excel with a Custom Number Format
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants a compact balance sheet that labels positive and negative amounts as debits and credits.
Observed behavior
Positive and negative values should appear with Dr and Cr labels according to the chosen accounting convention.
Before you start

Confirm which sign represents a debit and which represents a credit in your balance sheet. Make sure the amounts are stored as numbers rather than text.

Solution 1Recommended

Apply a Custom Debit and Credit Number Format in Excel

Use this format when positive amounts represent debits and negative amounts represent credits.

The first format section controls positive values and, when no zero section is specified, zeros. The second controls negative values. The format changes the display, not the underlying numbers.

1
Select the amounts

In the worksheet, select the cells containing the debit and credit values.

2
Open custom formatting

Right-click the selection, choose Format Cells, and select Custom on the Number tab.

3
Enter the format

In the Type box, enter $ #,##0.00 "Dr";$ #,##0.00 "Cr", then select OK.

4
Check the labels

Review the selected cells: positive values should display Dr and negative values should display Cr, without a minus sign.

Apply a Custom Debit and Credit Number Format in Excel
Using the opposite sign convention: If positive values are credits and negative values are debits, switch the labels in the two sections: $ #,##0.00 "Cr";$ #,##0.00 "Dr".
Format balance sheets in WPS

Display Debits and Credits in WPS Spreadsheets

Use a custom number format in WPS Spreadsheets to label positive and negative dollar amounts as debits and credits while keeping the values available for calculations.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select the values: Select the cells containing the amounts you want to label.
  3. 3. Set a custom format: Right-click the selection, choose Format Cells, and select Custom on the Number tab. Enter $ #,##0.00 "Dr";$ #,##0.00 "Cr" in the Type box and select OK.
  4. 4. Save the workbook: Check the displayed labels, then use File > Save As to save the workbook in your preferred format.
Show Dr and Cr labels in a compact balance sheetKeep underlying numeric values unchangedOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formatting controls
microsoft office alternative - wps office

Frequently Asked Questions

Does the custom format change the values used in calculations?

No. It changes only the display. Negative values remain negative and can still be used in formulas.

How do I display positive amounts as credits instead?

In Format Cells > Custom, use $ #,##0.00 "Cr";$ #,##0.00 "Dr". The first section formats positive values and the second formats negative values.

How are zero values displayed?

With the two-section format, zero uses the first section and displays as a debit. To show zero without a label, add a third section: $ #,##0.00 "Dr";$ #,##0.00 "Cr";$ #,##0.00.

Why do negative credit amounts appear without a minus sign?

The negative format section does not include a minus sign, so Excel displays the amount with Cr instead. The underlying value remains negative.