Help with Excel financial spreadsheet formatting (1 Viewer)

[Ronald Epstein]

Hey Guys!

I use Excel for recording my monthly income/expenses.

Using the AUTOSUM tool is perfect for adding everything up.

However, lately, it has not been working reliably and I think it's because I have not formatted the cells themselves in the correct format.

When dealing with monetary entries (eg: $1,200.00) which of the following is best to format the entire spreadsheet in:

GENERAL, NUMBER or CURRENCY?

CURRENCY seems to be the best method as it introduces proper accounting formats.

However, for some reason, the AUTOSUM doesn't properly add up all the numbers.

I see on some of my number boxes there is a partial green diagonal indication that indicates the dollar amount is in text mode rather than number mode. Not sure how that happened as I believe I preformatted all the cells in CURRENCY format.


I just need a recommendation for the best format

 

[David Weicker]

I use Currency.

However if I Paste in a value from another source, I sometimes have accidentally copied in extra spaces, which converts the cell to Text.

If I remove the spaces (either by editing the cell, or by doing a Replace All ‘ ‘ with ‘’), it generally corrects the issue.

 

[Sam Posten]

I use Currency.

However if I Paste in a value from another source, I sometimes have accidentally copied in extra spaces, which converts the cell to Text.

If I remove the spaces (either by editing the cell, or by doing a Replace All ‘ ‘ with ‘’), it generally corrects the issue.

Does a 'Paste As' option help here?

 

[Scott Merryfield]

The number display format shouldn't affect how the formula calculates the numbers, Ron. Personally, I have never used the AUTOSUM tool, but used the SUM formula all the time with numbers formatted as currency without any issues (planning and maintaining my department's budget was part of my job duties for many, many years). Are there any non-numeric cells within the range you are trying to use the function with?

 

[Rob_Ray]

I only have this problem when the column of numbers is in an Excel spreadsheet that has been converted from a non-Excel source where spaces are embedded with the numbers. The autosum function can't always add the column of numbers. My workaround works perfectly: I add a new column (I will call Column B) to the right of the column giving me problems (I will call Column A). I place the following formula in cell B1: "=A1+0". Then I copy that formula to the remaining cells in Column B. I hide Column A and successfully use autosum in Column B.

 

[DaveF]

Hey Guys!

I use Excel for recording my monthly income/expenses.

Using the AUTOSUM tool is perfect for adding everything up.

However, lately, it has not been working reliably and I think it's because I have not formatted the cells themselves in the correct format.

When dealing with monetary entries (eg: $1,200.00) which of the following is best to format the entire spreadsheet in:

GENERAL, NUMBER or CURRENCY?

CURRENCY seems to be the best method as it introduces proper accounting formats.

However, for some reason, the AUTOSUM doesn't properly add up all the numbers.

I see on some of my number boxes there is a partial green diagonal indication that indicates the dollar amount is in text mode rather than number mode. Not sure how that happened as I believe I preformatted all the cells in CURRENCY format.


I just need a recommendation for the best format

Sounds like you're copy/pasting text data and bringing along extraneous spaces. Excel then preserves the spaces and interprets the entries as text, and so won't do math on it.

Otherwise, calculations are not affected by cell formatting.

 

[DaveF]

Also, you're on a Mac. Have you looked at using Numbers? It's really great for a variety of household uses, especially if you want to make it easy to read and understand. (I use Excel a lot, so I know of where I speak )