How to get cells to show up blank rather than 0

[kevwilsondj]

Hi,

I am in the middle of creating the attached spreadsheet to track pupil progress in my school.

Most formulas are working well (I just need to get time to add the actual data in).

However, I am stuck with how to tidy up my spreadsheet.

Within the tab titled 'Screening Raw Data' you will see that the spreadsheet shows 0 in the columns titled 'difference', however no data has been input. How can I get it to display so that if there has been no data input into the test age years and months then the cell will just stay blank rather than display a difference of 0????

Similarly, when a child has missed a test I have marked this with a *. How can I get the difference columns to display this * rather than ####?

Any help would be hugely appreciated.

Thank you in advance for your support.

[AlKey]

Go to Excel Options, Advanced, scroll down to 'Display Options for this worksheet' and uncheck 'Show zeros in cells that have 0 values'

[Cerbera]

Hi Kev,

First step for hiding the "0" is to click on the office button in the top left hand corner of excel. Select "Excel Options" > then select "Advanced"

Just over half way down the options under the header "Dispaly option for this worksheet" you should see an option: Show a zero in cells that have zero value. Deselect that option.

[kevwilsondj]

thank you- this worked a treat. And what for those cells that I have a * in- how can they display a * rather than ####

[martindwilson]

its not #### it's #VALUE! just displaying # because the column is too narrow
put iferror around the formula
=IFERROR(IF(M51>0,((M51*12)+N51) - ((DATEDIF(F51,L51,"Y")*12) + DATEDIF(F51,L51,"YM")),0),"*")

[kevwilsondj]

Hi,

Thank you very much for your support everyone. I am almost there with this. However I have spotted a problem...

On the sheet titled 'screening raw data' I added the extra parts to the formula so that it showed a * in the difference column rather than #Value. This worked, but I have conditional highlighting on that should show anything less than -12 as red, between -12 and 12 orange and above 12 green.

Now that I have made changes any cell that contains a * is now turning green? How can I avoid this?

I tried going into conditional highlighting and saying if text contains * the turn cell white but then everything turns white?!

[martindwilson]

Use
=and( cell ref<> "",cell ref>12)

[kevwilsondj]

thanks for the reply martindwilson. Please can you show me what the full formula would look like (similar to what you did earlier on). My understanding of excel formula is very basic

[kevwilsondj]

Hi, you provided me with some great advice yesterday for how to stop my cells changing colour if containing a *. However, my understanding of excel is very basic. Please can you show me what the full formula would look like for one of my cells so that I can then apply to the others.
Sorry to pester you.
Kevwilsondj

[wakablahh]

In regards to keeping the cells with * white, I would create another conditional formatting rule:
Cell Value = "*" then under "format" -> "fill tab" -> select either the "no color" or "white" option then under "Applies to" select the range where you want this applied.

In regards to showing cells blank:
I would have selected the cells (or the entire worksheet) -> right-click and select format cells -> Custom -> Type: 0;-0;;@

Tried both, and both work.