1

I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers.

(Note that in the same sheet, there are few other tables which are not of my interest and contain text and numeric values)

For these two numeric 10x10 tables in all the sheets, I need to highlight the font color to red if their value is less than 0.2.

Can someone help me how to do that in microsoft excel? What is the appropriate formula to use for conditional formatting in this case?

I don't want to go over each of these 10x10 tables individually as that is time consuming.

Improve this question

1 Answer 1

Reset to default
1

First things first: group your sheets (so you don't have to copy the formatting to each sheet).
Group worksheets
Second: mark the areas you want to format.
Third: choose cell formatting (Ctrl+1) > Numbers > Custom
Fourth: enter the format code

[Red][<0.2]General;General
Improve this answer
5
  • Thank you. This is very helpful. But, in this case I have to select the cells for each table in one of my sheets. Actually, is there a way to specify the cells instead of dragging and selecting. For eg: 10x10 cells starting at A4 and 10x10 starting at N4. For 2 such tables, what you suggested helps. But, i might want to be scalable.
    – wanderer
    Commented Dec 15, 2023 at 19:01
  • 1
    Instead of dragging you can enter addresses to be selected into the Name Box e.g. A4:J13,N4:W13 and press Enter.
    – MGonet
    Commented Dec 15, 2023 at 19:32
  • I tried doing the below in namebox to select the corresponding cells: C6:E20, J6:L20,R6:T20,Y6:AA20,C28:E42, J28:L42,R28:T42,Y28:AA42,C50:E64, J50:L64,R50:T64,Y50:AA64,C75:E89, J75:L89,R75:T89,Y75:AA89,C101:E115,J101:L115,R101:T115,Y101:AA115,C126:E140,J126:L140,R126:T140,Y126:AA140,C151:E165,J151:L165,P151:T165 It is selecting the specified cells, but the entire cells in the sheets is also getting selected.
    – wanderer
    Commented Dec 15, 2023 at 22:09
  • 1
    I don't know why this is happening. Maybe a bug in Excel. I didn't try to select so many areas at once before. It's not very convenient. In case of so many areas it is much better to define a name in the Name Manager. Open the Name Manager (Ctrl+F3), enter a name e.g. cells and below enter the definition, starting from =: =$C$6:$E$20, $J$6:$L$20, ... and then use this name in the Name Box. If you define the name without $'s you must put the active cell in A1 when using it, otherwise, the addresses will be shifted.
    – MGonet
    Commented Dec 15, 2023 at 23:24
  • Thank you for the help :)
    – wanderer
    Commented Dec 18, 2023 at 15:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .