Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
This has been driving me nuts for a while: I have a sheet with a whole column set to the Data Format "Currency". But whenever I actually enter any amount it automatically changes the Data Format to "Number".

This really quite ridiculous. Is there any way to make the Data Format stick?
 

hg.wells

macrumors 65816
Apr 1, 2013
1,067
789
What version of Numbers are you using?

When you set the whole column formatting are you selecting the column letter at the top of the sheet?
 

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
What version of Numbers are you using?

When you set the whole column formatting are you selecting the column letter at the top of the sheet?
I am using version 12.1.
I just realised that the formatting doesn't stick because there are formulas in the cells. I often need to write over these and manually enter the currency amount. But because of the formulas the formatting always switches back to automatic after I click away from the cells.
I assume this is just one of those things that is the way it is?
 

0128672

Cancelled
Apr 16, 2020
5,962
4,783
It's not the way it is. Even if there are formulas in the cells, you can format the entire column and should not revert to something else unless it wasn't saved. Could you give us an example of one of those formula cells in which this is happening and also a screenshot of the formatting for that column?
 

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
It's not the way it is. Even if there are formulas in the cells, you can format the entire column and should not revert to something else unless it wasn't saved. Could you give us an example of one of those formula cells in which this is happening and also a screenshot of the formatting for that column?
I've done a little screen record. Hope that is even more helpful!
 

hg.wells

macrumors 65816
Apr 1, 2013
1,067
789
From the screen recording, it looked like you clicked on column G and then you clicked on “Position Size”

Select column G and then change the format for the whole column.
 

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
From the screen recording, it looked like you clicked on column G and then you clicked on “Position Size”

Select column G and then change the format for the whole column.
I cmd-clicked on "Position Size" because that is the header and contains text, not currency. But I just tried it the way you described and unfortunately it doesn't seem to make a difference.
 

0128672

Cancelled
Apr 16, 2020
5,962
4,783
I agree with @hg.wells that you have selected the whole G column and green column header is dark, and then somehow you're clicking off that before applying the formatting. Click once on the G column with the column header dark green and change the formatting while that column header is still dark before clicking any place else. And then save your change too.


Screen Shot 2022-09-28 at 9.46.07 AM.png


There's a chance you're making this more complicated than it needs to be.
 
Last edited:

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
I agree with @hg.wells that you have selected the whole G column and green column header is dark, and then somehow you're clicking off that before applying the formatting. Click once on the G column with the column header dark green and change the formatting while that column header is still dark before clicking any place else. And then save your change too.


View attachment 2082814

There's a chance you're making this more complicated than it needs to be.
Unfortunately, that does not seem to make a difference. I just tried it again: Opened the document, clicked on the column (G), changed the Data Format and saved before doing anything else. Still the same issue.
 

0128672

Cancelled
Apr 16, 2020
5,962
4,783
Unfortunately, that does not seem to make a difference. I just tried it again: Opened the document, clicked on the column (G), changed the Data Format and saved before doing anything else. Still the same issue.
I really want to help you with this. Any chance you would make another video of exactly what you just tried? The first video was very helpful.
 
  • Like
Reactions: danqi

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
I really want to help you with this. Any chance you would make another video of exactly what you just tried? The first video was very helpful.
Sure. Here is another recording. What you cannot see in it is that I did save after setting the Data Format to "Currency".
 

0128672

Cancelled
Apr 16, 2020
5,962
4,783
Sure. Here is another recording. What you cannot see in it is that I did save after setting the Data Format to "Currency".
Thanks for creating another video. If you change the formatting for a single cell in that column and save it, does that keep the change?

Also if you create a new blank Numbers doc, and change a whole column's formatting to Currency and save it, does that change stick? In other words, can we narrow it down to that document or is it a Numbers issue? I can't reproduce the issue in a new Numbers doc.
 

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
I can't reproduce it either. A guess is that your last argument "" may turn the cell into a text cell. Could you try and replace "" by 0 ?
 
  • Like
Reactions: 0128672

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
Thanks for creating another video. If you change the formatting for a single cell in that column and save it, does that keep the change?

Also if you create a new blank Numbers doc, and change a whole column's formatting to Currency and save it, does that change stick? In other words, can we narrow it down to that document or is it a Numbers issue? I can't reproduce the issue in a new Numbers doc.
Thanks for helping me in such a detailed way!
In a new document the formatting sticks until I enter that formula from my problem document. Then it is the same issue.

I can't reproduce it either. A guess is that your last argument "" may turn the cell into a text cell. Could you try and replace "" by 0 ?
This could be it. When replace "" with 0 it works as expected. But unfortunately the cells are not empty like they are supposed to but are then showing "0,00$". That leads to other problems with other formulas. Is there another way to make the cells appear truly empty without forcing a text format?
 

0128672

Cancelled
Apr 16, 2020
5,962
4,783
Thanks for helping me in such a detailed way!
In a new document the formatting sticks until I enter that formula from my problem document. Then it is the same issue.


This could be it. When replace "" with 0 it works as expected. But unfortunately the cells are not empty like they are supposed to but are then showing "0,00$". That leads to other problems with other formulas. Is there another way to make the cells appear truly empty without forcing a text format?
Sounds like what's needed is a null currency, and hopefully @GerritV can suggest something. I'll keep looking too.
 
  • Like
Reactions: danqi

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
This could be it. When replace "" with 0 it works as expected. But unfortunately the cells are not empty like they are supposed to but are then showing "0,00$". That leads to other problems with other formulas. Is there another way to make the cells appear truly empty without forcing a text format?

About these other formulas: in case they're checking for the target cell to be empty, you might change that condition into "cell is 0". And if you can live with that, give the target cell an auto-format like "if cell = 0, text color is white".
 
  • Like
Reactions: 0128672 and danqi

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
About these other formulas: in case they're checking for the target cell to be empty, you might change that condition into "cell is 0". And if you can live with that, give the target cell an auto-format like "if cell = 0, text color is white".
That's clever, I guess that should work!
 

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
Well, perhaps not the cleanest of ways to handle it (read: hack it).
If only I could reproduce it...
 

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
Well, perhaps not the cleanest of ways to handle it (read: hack it).
If only I could reproduce it...
What happens if you enter the same formula?
Code:
IFERROR((H82÷((I82÷E82−1)÷J82))×IF(C82="S";"1";"-1");"")
 

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
What happens if you enter the same formula?
Code:
IFERROR((H82÷((I82÷E82−1)÷J82))×IF(C82="S";"1";"-1");"")
Seems that, no matter what you set column G to, the individual cells take an "Automatic" formatting.
That is, until there is content - in this case, the result of your formula.
This being a number, the target cell applies the "Valuta" format.
Some of the above behaviour was unknown to me as well, but in the end it works over here.
I'm attaching a screencast.

 

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
Seems that, no matter what you set column G to, the individual cells take an "Automatic" formatting.
That is, until there is content - in this case, the result of your formula.
This being a number, the target cell applies the "Valuta" format.
Some of the above behaviour was unknown to me as well, but in the end it works over here.
I'm attaching a screencast.

View attachment 2083560
Wow, thank you for doing such extensive testing! And sorry for not getting back to you earlier, I somehow missed the notification email. :(

Valuta is Currency? It is interesting that entering a number causes the cell to switch to Currency/Valuta for you. For me it defaults to Number.
 

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
Wow, thank you for doing such extensive testing! And sorry for not getting back to you earlier, I somehow missed the notification email. :(

Valuta is Currency? It is interesting that entering a number causes the cell to switch to Currency/Valuta for you. For me it defaults to Number.
You're welcome @danqi
Valuta is indeed Currency.
I've gone through Numbers' Preferences and other settings, but cannot find an option to set a default format.
Please find the document from the screencast attached, for you to test. Who knows it's about a document property.

Created with Dutch version - assuming formulas will be automatically translated
 

Attachments

  • NumbersFormat.zip
    76.7 KB · Views: 114

danqi

macrumors regular
Original poster
Sep 14, 2010
232
19
You're welcome @danqi
Valuta is indeed Currency.
I've gone through Numbers' Preferences and other settings, but cannot find an option to set a default format.
Please find the document from the screencast attached, for you to test. Who knows it's about a document property.

Created with Dutch version - assuming formulas will be automatically translated
Thanks for the file! It indeed does work as it should be in your file. So it seems to be an issue with my specific document. I tried a bunch of stuff but could not figure out what could be causing it. Very strange. Will try some more later.
 

GerritV

macrumors 68020
May 11, 2012
2,271
2,751
Thanks for the file! It indeed does work as it should be in your file. So it seems to be an issue with my specific document. I tried a bunch of stuff but could not figure out what could be causing it. Very strange. Will try some more later.
Interesting ! Keep us posted if you like.
 
  • Like
Reactions: danqi
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.