Ask Your Question

Change Display of NA?

asked 2019-01-17 12:10:23 +0200

Penguin Guru gravatar image

Hi, is there any way to configure LibreOffice Calc to display NA differently? For example, as "" or "---"?

I would like to use NA in cell functions to indicate when the data that would appear in those cells cannot be referenced for expected reasons, but the way NA displays by default is just too alarming and visually intrusive (i.e. big) in many cases. Since this is a matter of personal preference, I thought there might be some way to change the way it displays on a given spreadsheet or by program default. If not, perhaps by cell formatting?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-01-17 12:14:19 +0200

Just use IFNA spreadsheet function where you want.

edit flag offensive delete link more


This works, but it ends up taking quite a long time to add this condition to every cell, often multiple times in the same cell for multiple references. I then have to maintain two versions of the file-- one with all of the IFNA() and one without, since other people might prefer the more standard format.

Since posting, I have been investigating conditional formatting. It seems like this should be able to check for NA, but I am not sure it can be used to change the cell contents (other than to make them blend in with the background).

Penguin Guru gravatar imagePenguin Guru ( 2019-01-17 13:13:55 +0200 )edit

often multiple times in the same cell for multiple references

why? isn't it enough to wrap the whole formula?

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-17 13:55:53 +0200 )edit

I mean cases where one cell references another, the one one that is being referenced could be valid data or NA, and the cell that contains the reference should produce a result that is not NA. For example, if I am summing a range with some valid and some NA, I would need to use SUM(IFNA(...)). I still want the sum value. If I have changed content of the cell that is being referenced, I have to use something like SUM(IF("---",A1,0)). Not a big difference, the problem is that I have to change every reference in every cell. Maybe I'm confused and overlooking something.

Penguin Guru gravatar imagePenguin Guru ( 2019-01-17 16:13:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-17 12:10:23 +0200

Seen: 18 times

Last updated: Jan 17