Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

accounting format

In Excel 2007, when formatting a column as "accounting", the dollar signs don't line up on the screen or the print-out. They used to in my earlier version of Excel. What gives? I'm using the $(English) dollar sign.


Post your answer or comment

comments powered by Disqus
Hi everyone,

Can anyone help me with custom formatting of numbers - i am trying to get a version of the "accounting" format with brackets for negatives instead of a minus sign. At the moment i am using the custom format

"£000.00;£ (00,000.00)"

but then I have to vary the number of spaces in every cell depending on how many characters are in the numbers, to get the £ at the left hand side of the cell (and it doesn't exactly line up). Is there an easier way to do this?

Thanks,

Adam

Protect sheet will not solve my problem, and neether will the otherwise excellent post at http://www.eggheadcafe.com/software/aspnet/33295991/formatting-not-protected-when-paste.aspx.

I simply need someone to help me with this code, which I have tried unsuccessfully to adopt from another ozgrid page. Even with this code in the sheet's module, when I enter a number with a dollar sign, accounting format is dropped. I really don't want that. I want it to STAY in accounting format


	VB:
	
 Range) 
     'ENFORCE ACCOUNTING FORMAT IN CELL I54
     'Do nothing if more than one cell is changed or if content is deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub 
    If Target.Address = "$A$1" Then 
         'First make sure target is a number
        If IsNumeric(Target) Then 
             'Stop potential runtime errors and halting code
             'On Error Resume Next
             'Turn off all events so operation does not put the code into a loop
            Application.EnableEvents = False 
             'HERE FOLLOWETH THE CODE WE'RE AFTER
            Target.Cells.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" 
             'Turn events back on
            Application.EnableEvents = True 
             'Allow runtime errors again
            On Error Goto 0 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have built some worksheets with check figures that should all come to zero. I use the accounting format for numbers, and in the same worksheet, some of the check figures will show a dash -, and others will show 0.00 or (0.00).

I know this can happen when the formula calculates a number with more than 2 decimal places, but if I round all of my formulas and all of the referenced cells to 2 decimal places, it should calculate zero correctly, and the accounting format should show the dash.

Does anybody know why this happens and how to fix it?

(This is not a really important issue, except that I am a perfectionist and my worksheets look sloppy if there are a mix of dashes and zeros!)

I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
"-" dash.

Can anyone suggest what I can look at to try and resolve this?

Thank you.

I use the Accounting format to align numbers and dollar signs. How can I make
zero display as the number rather than a dash?
--
- markvi

When I try to create a custom list using text cells and accounting formated
($99.99) cells, I get an error message that cells without simple text will be
ignored. What is simple text and how can I get numerical values included in
the list? They have to be numbers for future calculations.

I have Excel 2002 (XP) and want to display negative numbers in parentheses in
ACCOUNTING FORMAT instead of with a minus sign. I know this is possible as a
standard feature (I have done it before on a previous machine) and I think
there was a KB patch for this flaw. Anyone know of where I might find the
fix?

Hi Guys,

I want to use Brackets (or Parenthesis) for negative numbers using the Accounting format. now there seems to be no way to do this in Excel unless you use a Custom format.

On doing some Googling I found a format that I am now using I just want to make sure I have copied it correctly. Also I want my neg numbers to appear in Red.

Below is what I have, is this correct?

Thanks in advance.

Desired result is:


When selecting Accounting as the number format, negative figures used to
appear in brackets. That is now no longer the case. Negative figures using
the Accounting format now appear with a minus in front. I have never used
any version of Excel other than Office XP.

I just got an unbelievable behavior from excel when i did the following
value copying:
-----------------------------
Sub mycopy()
Range("A2:B2").Value = Range("A1:B1").Value
End Sub
-----------------------------

I formatted cell A1 as accounting format (or currency format $) and
input $0.0123 in A1.
format cell B1 as general format and input 0.0123 into B1.

After running the macro, you'll see the target values of A2 becomes
$0.0100, B2 is 0.0123. (i'm using excel 2003)

THe accounting/currency format just use the rounding automatically!!!
Is this a bug? or a feature?

BTW, i tried after to use ".copy" and ".PasteSpecial xlPasteValues",
that works regardless. But not knowing the limit of range copy made
million dollar difference in my analysis! DARN it!

Hello -

I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
 $    123
 $   (125)
$   1,123
 $    250
Any idea how I can use custom formatting to adjust this?

Everything lines up fine if I put in decimal places.

I am perplexed.

Thanks!

Is there a keyboard shortcut for the accounting format? I know currency is ctrl + shft + 4

I can't figure this one out (Excel 2010). On any cell that has accounting format the first number of the value only displays the right side of the number. Almost as if the number is cut in half. If I change the format to currency this does not happen.

I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
"-" dash.

Can anyone suggest what I can look at to try and resolve this?

Thank you.

1) Is there a way to include accounting formats into conditional formatting? For example, if the country column has US, the price column will be in $ format, and if it was UK it would be in £ format.

2) Is there a way to change the default list of accounting formats shown when you click on the $ button under the Home -> Number pane?

Hello,

I'm currently making a Gantt Chart with an hourly scale. I have my start time, end, time, duration, and a duration converted into decimal equivalent for 24-hours ( multiplying by 1/24)

But when I try to change the x-axis on my chart, ie maximum minimum minor units major units, the numbers are in accounting format thus 12/20/2010 6:30 PM becomes 40,403.38.

How can i fix this scale so that if i set the maximum as 1, it'll be 12:00 AM and if it was .5 12:00 PM and so on. Basically how can I change this accounting format of the axis.

Hello there,

I formatted my cells as Accounting, but when I type a zero, it doesn't display the zero, it only displays a dash. Is there a way to have it formatted as Accounting and still be able to display the zero?

Thank you,

Alex

We like to use the Accounting Format because it shows zeros as dashes which is much cleaner. The problem, however, is when we want to CENTER the data and it will only align to the right of the cell, regardless of changing the Alignment to Center or anything else. Is there a format to get a dash for zero that will let us center, or is there a way to use the Accounting Format and still be able to CENTER it??? THANKS!

Hello -

I have a textbox on a userform that people will be entering dollar values into. I'd like to have the value displayed in $ format, but when the value is copied over to another spreadsheet, I'd like it to be formatted in Accounting format. I can make it do Currency format, but I cannot figure out how to do the Accounting format.

Any suggestions would be greatly appreciated!

Thank you!

I need to format a cell so that it will always display the account code directly when managers are entering information into their requisition form. Our account format needs to be:

4444.4444.22.22

I guess dots or dashes wouldn't necessarily matter. I have tried some formats, but I don't get the results I am hoping for.

It just needs to be 4 digits, dot, 4 digits, dot, 2 digits, 2 digits.

Any suggestions? Thank you!

Hello everyone,

I have written a macro to format numbers to my company's format, which is accounting with no decimals, with and without the $. The problem I'm having is that my macro's result and Excel's accounting format are not matching for zero amounts, and after spending quite a while to figure out why, I am stumped.

Image of my problem: (I want what I highlighted, but am getting what is labeled "macro")

macro problem.jpg

As you can see, the macro result for acctg with $ doesn't put the $ at the left edge of the cell, and the "-" alignment is off for no $.

Here is my code for the two: Sub Dollar() is for accounting format, no zeros, with $. Sub Number() is for no $.

Sub Dollar()
' Keyboard Shortcut: Ctrl+Shift+D
' Note: must have * for forced right else underline won't extend!
    Selection.NumberFormat = "_($ * #,##0_);_($ * (#,##0);_(* ""$  -""?_);_(@_)"
End Sub

Sub Number()
' Keyboard Shortcut: Ctrl+Shift+N
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""   -""?_);_(@_)"
End Sub
Thank you for the help in advance. I've been searching for a while and this is driving me nuts!

I had a hard time to put a suitable title to this query, not sure if above is good enough for my query.

I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format.

Example:
Expected output = Trial 1,000

Using this formula
="Trial "&Sheet1!A1
where A1 in Sheet1 = 1000
Output is Trial 1000

Please help!

Thanks in advance :-)

I am looking at conditional number formatting depending on cells value in another area or worksheet

for example
i have one column for account , one column telling what should be the number format for the account and one column with the value for this account
I want the 3 column to use the number format depending of the value in the second column which is depending on the first column value
COL 1 COL2 COL 3 COL 3
ACCOUNT FORMAT VALUE >> AFTER FORMAT
Volume no decimal 73.90 74
Sales no decimal 100.10 100
Per Kg 2 decimal 1.35318 1.35
Margin no decimal 35.42 35
Margin % percentage 0.3538 35.38 %
Marg Kg 2 decimal 1

when the account is changed the format in col 3 should change according to col 2 (column 2 lookup in table what should be account format)

thanks

Hi everyone,
First I would like to share this code if anyone is interested in using it. It's great and it's doing exactly what I want it to do.

when I run this code it present data for these column this way:

Face Amount Annual Premium Amount
5000000 582252.33 33145.14

I want to the number to be formatted in accounting format

5,000.000 582,252.33 33,145.14

Here is the code:

Sub GroupReport()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

RptWks.Select
RptWks.Name = "PreData"

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "B").Value

oRow = oRow + 2
RptWks.Cells(oRow, "B").Value = "COMPANY"
RptWks.Cells(oRow, "C").Value = "POLICY NUMBER"
RptWks.Cells(oRow, "D").Value = "ISSUE DATE"
RptWks.Cells(oRow, "E").Value = "FACE AMOUNT"
RptWks.Cells(oRow, "F").Value = "TYPE"
RptWks.Cells(oRow, "G").Value = "ANNUAL PREMIUM"
RptWks.Cells(oRow, "H").Value = "AMOUNT"
RptWks.Cells(oRow, "I").Value = "DATE"

End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "I").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub

thanks in advance.

Ish


No luck finding an answer? You could always try Google.