# Excel - How to Apply Conditional Formatting to Blank Cells - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I need a way to use conditional formatting to make all the cells blank when the top shows "Game End"
Solved by T. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 31/08/2018 - 11:31
Hello, Welcome to Got it Pro.
User 31/08/2018 - 11:31
Hi
User 31/08/2018 - 11:31
I need a way to use conditional formatting to make all the cells below Game End to appear empty
Excelchat Expert 31/08/2018 - 11:31
I can see you adding some data on the right side if the screen
Excelchat Expert 31/08/2018 - 11:32
Ok
User 31/08/2018 - 11:32
the results will look like the one on the right
Excelchat Expert 31/08/2018 - 11:32
Are you going to type the Game End in that cell?
User 31/08/2018 - 11:33
which cell?
Excelchat Expert 31/08/2018 - 11:33
C4 D4?
User 31/08/2018 - 11:33
Yes in a way, I have a IF formula which will let them be "Game End" if the condition is met
User 31/08/2018 - 11:34
i just need a way to let those cells below Game End appear to be empty whenever the conditions of my IF formula are met and they show up as "Game End"
Excelchat Expert 31/08/2018 - 11:34
So we can use another if for Game End if the cell is fixed otherwise we can use conditional formating
Excelchat Expert 31/08/2018 - 11:34
Give me a 2 mins
User 31/08/2018 - 11:36
I would prefer to use conditional formatting so my formula doesnt get too long
Excelchat Expert 31/08/2018 - 11:36
ok
Excelchat Expert 31/08/2018 - 11:37
Sorry but we can not handle this with conditional formatting, I just realized numbers could be same and anywhere in the range.
Excelchat Expert 31/08/2018 - 11:38
we have to handle this with if condition
User 31/08/2018 - 11:38
what do you mean by numbers could be the same and anywhere in the range?
Excelchat Expert 31/08/2018 - 11:39
like 500, 100 can come above the Game End and Below the Game End
Excelchat Expert 31/08/2018 - 11:39
There is no logic that we can build inside conditional formatting for range of cells.
Excelchat Expert 31/08/2018 - 11:40
Can you forward me your working sheet?
Excelchat Expert 31/08/2018 - 11:41
=IF(\$F\$4="Game End","")
Excelchat Expert 31/08/2018 - 11:41
something like this, we have to use.
User 31/08/2018 - 11:41
I know that method
User 31/08/2018 - 11:41
But i am only able to populate 1 line of empty rows after "Game End"
Excelchat Expert 31/08/2018 - 11:41
How are you populating one line?
Excelchat Expert 31/08/2018 - 11:42
May be I can help in there
Excelchat Expert 31/08/2018 - 11:42
to develop the logic
Excelchat Expert 31/08/2018 - 11:42
for other rows
User 31/08/2018 - 11:42
okay
User 31/08/2018 - 11:42
can you see theres a empty row after "Game End"?
Excelchat Expert 31/08/2018 - 11:42
I can see
User 31/08/2018 - 11:42
but the following rows are not blank
User 31/08/2018 - 11:42
can you help with that?
Excelchat Expert 31/08/2018 - 11:42
but there is no formula in there
Excelchat Expert 31/08/2018 - 11:43
how one line is empty?
User 31/08/2018 - 11:43
oh
User 31/08/2018 - 11:43
you cant see the formula ?
User 31/08/2018 - 11:43
give me a sec
User 31/08/2018 - 11:43
anyway to copy from excel and paste it here with the formulas?
Excelchat Expert 31/08/2018 - 11:43
if you copy and paste formula wont copy here. I cant see the formula
Excelchat Expert 31/08/2018 - 11:43
you need to copy the formula from excel cell and past ehere
User 31/08/2018 - 11:44
okay i will send you the excel file
Excelchat Expert 31/08/2018 - 11:45
User 31/08/2018 - 11:45
ok
User 31/08/2018 - 11:45
i need all the following cells after the blank row to be blank as well
Excelchat Expert 31/08/2018 - 11:46
Ok
Excelchat Expert 31/08/2018 - 11:48
F12<C\$2,F12,C\$2
Excelchat Expert 31/08/2018 - 11:48
Do you need this logic after game end ?
Excelchat Expert 31/08/2018 - 11:48
Row 9 should not populate anything
Excelchat Expert 31/08/2018 - 11:48
that logic is on your last if statement in the formula
User 31/08/2018 - 11:49
is that logic redundant?
User 31/08/2018 - 11:49
User 31/08/2018 - 11:49
if i am still able to produce the same results, I am ok with removing it
Excelchat Expert 31/08/2018 - 11:50
I am not sure how the game works but once you get the Game End from your current logic .. all columns will be game end and the data below it should be blank.
Excelchat Expert 31/08/2018 - 11:51
let me understand the logic and I will build it.
User 31/08/2018 - 11:51
ok whatever you said make sense
User 31/08/2018 - 11:51
can you test it and check if I will still get the same result?
Excelchat Expert 31/08/2018 - 11:51
Let me see
User 31/08/2018 - 11:53
ok
User 31/08/2018 - 11:53
Basically the game will end when the current balance = 0
User 31/08/2018 - 11:54
and after that, the next row will all appear as "Game End"
Excelchat Expert 31/08/2018 - 11:54
what is it is less then the bet amount?
Excelchat Expert 31/08/2018 - 11:54
if it is*
User 31/08/2018 - 11:54
If the current balance is less than the bet amount, I will bet whatever I have
User 31/08/2018 - 11:55
Eg: If i only have \$50 left, but bet amount is \$100, I will bet everything I have, which is \$50
Excelchat Expert 31/08/2018 - 11:55
still game will continue with current balance, right? it wont end
User 31/08/2018 - 11:55
yes, game will only end when current balance = 0
Excelchat Expert 31/08/2018 - 11:56
ok
User 31/08/2018 - 12:00
Will I be able to extend the session for a 2nd time?
Excelchat Expert 31/08/2018 - 12:00
yeah
User 31/08/2018 - 12:00
okay, thanks!
Excelchat Expert 31/08/2018 - 12:00
four columns are working fine
Excelchat Expert 31/08/2018 - 12:01
i made them blank only that one logic is creating little issues and one col that if F is need to be fixed
Excelchat Expert 31/08/2018 - 12:01
stay tuned.
User 31/08/2018 - 12:01
okay, sure!
User 31/08/2018 - 12:11
Hey im sorry, theres a mistake in my formula for net balance
User 31/08/2018 - 12:11
the \$C\$3 at the last part of the formula for net balance
User 31/08/2018 - 12:12
oh wait, nvm its okay
User 31/08/2018 - 12:12
i saw wrongly
Excelchat Expert 31/08/2018 - 12:12
That colm is fine
Excelchat Expert 31/08/2018 - 12:12
Current Balance is having a little issues.
User 31/08/2018 - 12:12
Ahh I see
Excelchat Expert 31/08/2018 - 12:14
Actually you have used Random function and that can last the game for any number of times
Excelchat Expert 31/08/2018 - 12:14
so when I handle the current balance
User 31/08/2018 - 12:14
The question only wants 40 rounds actually
Excelchat Expert 31/08/2018 - 12:14
the game goes on for more than 40 rows
User 31/08/2018 - 12:15
the game will end when 40 rounds are completed OR current balance = 0 and game ends prematurely
Excelchat Expert 31/08/2018 - 12:15
User 31/08/2018 - 12:15
those are the conditions of the question unfortunately
Excelchat Expert 31/08/2018 - 12:15
Look at this sheet,
Excelchat Expert 31/08/2018 - 12:15
Col C D E G are working fine as you want
User 31/08/2018 - 12:17
what is the issue with the current balance column?
Excelchat Expert 31/08/2018 - 12:17
In F column.. with your current logic, it is looking the row above it for game end otherwise mathematical calculation is there. For example: F8 cell is checking the C7 and F7 for game end or 0
Excelchat Expert 31/08/2018 - 12:17
right?
User 31/08/2018 - 12:18
Yes, you are right
Excelchat Expert 31/08/2018 - 12:19
so within this logic, if I say both C7 and F7 are Game End then it should be blank.. which is the condition in row 12 for now and once both are game end then it should result as blank.
Excelchat Expert 31/08/2018 - 12:19
right?
User 31/08/2018 - 12:20
Yes
Excelchat Expert 31/08/2018 - 12:20
I incorporated that logic (see F6 cell) but as soon as I drag it your random function results are going more than 40 rows
User 31/08/2018 - 12:20
the basic concept is actually only once current balance = 0, the next row will all appear as "Game End"
User 31/08/2018 - 12:21
actually that doesnt have to be the logic if you can think of another logic thats fine too
User 31/08/2018 - 12:21
as long as the results are the same , I am okay with it
Excelchat Expert 31/08/2018 - 12:21
Yeah but you dont know when the game will end it could take place anytime after first bet.. so we need to handle the case for every row.. and the only logic goes with this is both C and F should be blank
Excelchat Expert 31/08/2018 - 12:21
Yeah trying
User 31/08/2018 - 12:22
okay
Excelchat Expert 31/08/2018 - 12:22
Logic is working fine but random function is creating problem.
User 31/08/2018 - 12:22
just a side thought: will I be able to use conditional formatting to make cells that have error in them to appear as empty?
Excelchat Expert 31/08/2018 - 12:23
Conditional formatting doesnt change value of a cell but changes the format of the cell
User 31/08/2018 - 12:23
i see, but what if the cell doesnt need to actually empty but instead appear empty? (using white font)
User 31/08/2018 - 12:23
that would be my very last resort
Excelchat Expert 31/08/2018 - 12:24
There isn't a logic to conditionally format
User 31/08/2018 - 12:24
I see, forget about that then
Excelchat Expert 31/08/2018 - 12:24
it is like once something is appeared make all rows after that like this that is not possible but in my current sheet that I sent you.. we can format the #Value may be
User 31/08/2018 - 12:25
yeah I think i get what you mean I faced the same problem when I was trying too
Excelchat Expert 31/08/2018 - 12:26
No you can not handle this with random function in your current logic because I tried a very easy way. Look at my current sheet all columns are working fine. Now, just need to handle the col F, so within my current sheet I gave a logic, if col E is blank then blank otherwise continue... that logic also taking the rows to more than 40
User 31/08/2018 - 12:27
so is there really no way to do this?
Excelchat Expert 31/08/2018 - 12:27
where is the logic for only 40 rows?
User 31/08/2018 - 12:27
the game only last 40 rounds
User 31/08/2018 - 12:27
that is the condition set by the question
Excelchat Expert 31/08/2018 - 12:28
yeah but that logic is not here in this sheet
User 31/08/2018 - 12:28
i only imputted round 1 to 40
User 31/08/2018 - 12:28
because there is only 40 rounds
User 31/08/2018 - 12:28
the timer is ending, will i be able to extend the timer again?
User 31/08/2018 - 12:29
can you contact me on email? soulfunk25@yahoo.com.sg
User 31/08/2018 - 12:29
i am willing to pay for the extra time
Excelchat Expert 31/08/2018 - 12:30
you extended twice?
User 31/08/2018 - 12:30
yes
Excelchat Expert 31/08/2018 - 12:30
its been 60 minutes?
User 31/08/2018 - 12:30
yes
User 31/08/2018 - 12:30
time passes fast :(
User 31/08/2018 - 12:30
can you contact me via my email?
Excelchat Expert 31/08/2018 - 12:30
oops, give the 5 star rating and raise it again, i will accept
User 31/08/2018 - 12:30
oh
Excelchat Expert 31/08/2018 - 12:30
M sorry not allowed to contact outside
User 31/08/2018 - 12:30
ok

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.