Is there any way to stop auto-formatting in Numbers?

[danqi]

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]

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]

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]

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]

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]

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]

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]

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.

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

 

[danqi]

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]

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.

 

[danqi]

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]

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]

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 ?

 

[danqi]

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]

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.

 

[GerritV]

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".

 

[danqi]

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]

Well, perhaps not the cleanest of ways to handle it (read: hack it).
If only I could reproduce it...

 

[danqi]

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?

IFERROR((H82÷((I82÷E82−1)÷J82))×IF(C82="S";"1";"-1");"")

 

[GerritV]

What happens if you enter the same formula?

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]

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]

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

 

[danqi]

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]

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.

 

[danqi]

Interesting ! Keep us posted if you like.

Will post if I find out something new!