Can't Get Excel to Accept Currency Formatting

Not sure if everyone knows this so I thought I'd add a note...

Excel keeps track of a lot of things in a cell.  Two of those things it tracks is what the cell format is and what the format of the contents of the cell is.  These are two separate things.

When it comes to numbers, you can end up with four possible combinations:

Cell format is text, number is stored as text

Cell format is text, number is stored as a number

Cell format is a number, number is stored as text

Cell format is a number, number is stored as a number

(for the purists, dates, currencies, etc. are all number formats)

To change a cell to use the cell number format AND change the contents to be treated as a number is usually a two-step process.

The first step is to change the cell format to a number format.  The second step is to then edit each cell contents.  When editing, you do not actually change the contents but you do have to go into the cell and then click save so the Excel will technically edit the contents (without actually making changes) and then save it as a number.  If you have lots of cells to edit then this quickly becomes impractical.

I have seen some interesting methods developed by people over the years to quickly switch numbers stored as text to real numbers, but you can use a build-in menu selection to do this.  The menu is Data : Text To Columns.  The trick is to keep the destination cells that same as the starting cells.  If you do that then Excel will "edit" every cell and save it back to the original cell.  When running this menu selection, choose the "General" format if you want a real number.

Please note, if you have entries that look like numbers, Excel will change them to a real number using this method.  Example, an account number that starts with leading zeros will be turned into a real number and the leading zeros will be stripped off.  So if the leading zeros are important then DON'T change that entry to a number.

Thanks, Bob! I'll try it.

Bob, thanks for your reply.

The cells that I was trying to format were in a column of figures in a spreadsheet that I had cobbled together to use as a check register for a checking account. The spreadsheet contains, among other things, columns for recording the amounts of debit and credit items (such as checks and deposits), and also a column for showing the running balance.

In the column for debits and in the column for credits, the content of the cells is typically a numeral that I type in manually, for example 9.7 or 100. Some of the cells in the column do, indeed, contain text, since I have created descriptive headings (e.g., "Debits") with lines or borders beneath them. (But I wonder whether that could be the explanation for the trouble I've had, since I have experienced essentially the same difficulty when I have tried to format not only a whole column, containing mostly numerals but also a few cells of text, but also just those cells in a column--say the 6th through the 500th cell--that contain [or that should contain] only numerals.) I am able to set the formatting in such a way that even though I only type in, say, 9.7 or 100, the program shows numerals to two decimal places in the cells that appear in my spreadsheet. So if I type in 9.7 or 100, the cell displays 9.70 or 100.00. (What I want is for the formatting to be for currency, and not just for numbers, so that the cells will display $9.70 or $100.00 when I type in 9.7 or 100, for example. It's not a really big deal for me that I can't always do that without multiple repeated attempted formattings of a few cells at a time, since the arithmetic seems to work correctly, and since I know that all the figures in the spreadsheet represent dollar amounts.

The contents of the cells in the balance column, however, is different, as you'd expect. I use a simple formula. If, for example, I want the balance to be displayed in column H on line 10, and there is nothing entered on line 10 in the debits column (column F) and if there is 100 entered on line 10 in the credits column (column G), then I take the balance from column H, line 9, subtract the amount in the debit column (actually there's nothing there to subtract, so Excel generates an error message in the form of a green flag in one corner of the cell I'm working on, but I can select multiple cells and tell Excel to ignore the error), and add the amount in the credit column. My formula, in this example, reads thus: "=H9-F10+G10 (without punctuation marks, of course)."

I'm not sure that I understand what you're asking when you write, "[W]hat currency format are you selecting, and what does the cell look like before and after you have formatted it?" But I'll take a stab at it.

After selecting some cells and choosing Format>Cells . . .  on the Menu bar, a dialog box appears, and under the Number tab, there is a list of different number format choices in a white window. I highlight "Currency" (but when the formatting doesn't work and I go back to see what it says, I usually see "Number" highlighted, so I suppose that is a default selection). There are also little menus, I guess you'd call them, shown under the Number tab in the dialog box where you can choose the number of decimal places to be displayed (I always choose 2 when doing a spreadsheet of this sort), the particular currency symbol to be displayed ($), and how negative numbers are to be shown (I prefer to use parentheses, rather than a minus sign, and to have the negative numbers shown in a black font color, rather than red). When the formatting works, the cell in the spreadsheet into which I've typed, for example, 100, and which shows 100.00, before formatting, shows $100.00, after formatting. When the formatting *doesn't* work, the cell displays 100.00, both before and after formatting.

Sorry for the length of this, and I appreciate the help.

Can you explain the content of the cells? My suspicion is that some of the cells may be text rather than actual numbers. It sounds like you are doing everything right, but the problem may be with your data. also, what currency format are you selecting, and what does the cell look like before and after you have formatted it?