not able to do conditional Formatting

[khurram703]

I am not able to apply conditional formatting on the below table as query is applied on this column.

What i want is, to highlight values that are less than 500 but excel is not highlighting those cells.

Any help  will be appreciated!

Unlocked Time Left

N/A

N/A

437

N/A

N/A

N/A

N/A

N/A

460

N/A

N/A

566

N/A

N/A

1302

1349

N/A

175

N/A

N/A

518

N/A

[#2]

@khurram703  maybe attach the file

[#3]

@mtarler 

As requested. File is attached, i want to apply conditional filtering on column D in the attached file on values less than 500

[#4]

here you go,
somehow numbers were stored as 'text', I just converted them back to numbers.

[#5]

@khurram703 

That could be the rule with formula as

[#6]

@bhushan_z 

Can you please specify the steps u followed as keeping header to General and other rows to Number is still not working on the sheet which i have but the one you attached is working fine. I  don't know  what is the issue?

[#7]

@Sergei Baklan 

Thanks your solution is working fine but Can u please explain why we need to put -- in the formula in excel

Secondly why this solution is not working when we highlight/select the complete D column and then apply conditional formatting instead of applying formula to D2 cell and then choosing other rows!

Sorry, i m not a very good user of excel and trying to learn new things!

[#8]

For quick solution, just copy column D from my file and 'Paste as Values' in your file.

FYI: Somehow just 'edit' any cell and hit 'Enter' is fixing it

[#9]

@bhushan_z 

Thanks very much brother but the main aim to post this question is to learn sth from you people. I don't want copy paste thing, i want  to do this myself next time so that i don't disturb you people next time so can u please tell me what is the problem exactly and how to fix this?

Sorry to bother u!

[#10]

@khurram703 it is always good to learn rather than copy-paste :)

here you go,

1) Copy column D and 'Paste as Value' to column J

2) in column J, select from from first number in column (which is 437) till down

3) Once selected, you will see a small green symbol on left side of the number, click on that

4) Click 'Convert to Number' (refer screenshot below)

5) Copy Column J and 'Paste as Value' to Column D

6) Delete column J

[#11]

@khurram703 

1) Double dash converts texts which represents the numbers into numbers

2) In the rule formula you always shall start from top left cell of the range to which the formula is applied. Thus if to entire column D, we shall use D1 in the formula