Free Microsoft Excel 2013 Quick Reference

Currency & Accounting Format Mixture

We have a 2003 spreadsheet where we would like "$" to remain on the left side
of the cell, but if the cell value is "0.00" - we would like that displayed,
instead of just a dash. Can this be done?

Thanks!


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!

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.

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.

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!

Hello,

I have a lot of data which are in different currencies and I have created a new tab in my worksheet to convert all the data into a chosen currency.

The calculations work fine, but not the formating. When the currency on the scroll down list ("currency input"! B8) is on "GBP", I want all the values from column C to G in "currency converted data"!to change to £. There are up to 5 different currencies. The formatting only seems to work when I click on apply in the managed rules within conditional formatting. I would like it this to work automatically whenever the currency is changed in the scroll down list.

I don't undertsand what could avoid the formatting to update automatically

I have attached a sample spreadsheet with the same layout as my current spreadsheet. There are 3 tabs: "currency input", "currency converted data" & "data"

Any help would be highly appreciated!

Many thanks

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.

I have created a cell "A" to display the difference between two balances (cells "B" & "C") on sheet 1.

Cell "A" is formatted to be displayed as an "accounting" number with "$".
Cell "A" has also been formatted to turn red when the number is negative, green when the number is positive and no fill when the number equals zero.

Cell "B" is the sum of many numbers (checkbook type balances) from multiple other sheets all of which are set up identically.
Cell "B" has also been formatted to display as an "accounting" number.

Cell "C" is the sum (balance) of a list of numbers from sheet 1 only and is also formatted as an "accounting" number.

My question is that...
1)Cell "A" will sometimes be displayed as a "currency" number as different entries are made within other connected sheets then turn back into an "accounting" number with other entries.
2)Cell "A" will sometimes turn red when the value equals zero.

There seems to be no consistancy with which this happens. To further prove this, I have tried to enter the figure $1.10 onto a sheet "2" which cell "B" will sum and connect to cell "A". The result is an "accounting" number. When I replace the $1.10 figure with $1.11 the result is a "currency" number in cell "A". This same irregulartiy happens with cell "A"'s coloring.

Hopefully this question makes sense (a bit confusing...yes), but perhaps someone can shed some light on this.

Thanks!

My preference of formatting cells with dollar signs is to align the dollar
sign adjacent to the leftmost number rather than aligning it to the left side
of the cell. Excel’s Currency formatting feature does this whereas the
Accounting format aligns the $ to the left side of the cell.

I would like to use the toolbar to easily format a cell using the Currency
formatting style so the dollar sign is aligned as I desire rather than the
longer process of using the Excel menu etc.

The dollar sign icon on the Formatting toolbar uses the Accounting
formatting style rather than the Currency style. I cannot figure a way to
get an icon on the toolbar to format a cell using the Currency formatting.

Is there a way to get Currency formatting onto the toolbar?

When trying to select currency format Excel keeps defaulting to accounting format with decimal point. This problem suddenly occurred and, despite running Office 2007 repair, still exists.

The attachment shows what is happening, how can I get back to the "normal" £29.99 etc ?.

Can anyone tell me how to change the currency format in the styles area
to match the format in the manner as when you got to Format Cell
Currency.

For some reason the justification is in the affected cell is different
for the currency format.

If you change in the Format Cell area, the reformatted currency is
right justfied. But if you change the number format to currency in the
Style area it is not right justified but it keeps it in the accounting
format justification.

thanks,

rpm

My preference of formatting cells with dollar signs is to align the dollar
sign adjacent to the leftmost number rather than aligning it to the left side
of the cell. Excel’s Currency formatting feature does this whereas the
Accounting format aligns the $ to the left side of the cell.

I would like to use the toolbar to easily format a cell using the Currency
formatting style so the dollar sign is aligned as I desire rather than the
longer process of using the Excel menu <FORMAT> <CELL> etc.

The dollar sign icon on the Formatting toolbar uses the Accounting
formatting style rather than the Currency style. I cannot figure a way to
get an icon on the toolbar to format a cell using the Currency formatting.

Is there a way to get Currency formatting onto the toolbar?

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!

Hi All,
I am struggeling to format a column to display currency values formated with a different currency mark than the local system currency.
Right now, when I run the macro on my machine I get my local currency (Israely Shekel), and on the Client's machine I get Euro. (I need it to be in dollars)

Any help will be apreciated.

- Arnon.