SOLVED

Coloring spreadsheed numbers automatically

Copper Contributor

I am tracking stocks using the EXCEL spreadsheet.  I would like to be able to have ALL negative numbers (representing losses) show up in bold red color automatically throughout the spreadsheet.

An added feature is to take an existing spreadsheet without this feature and add it via a global highlighting to the spreadsheet.

6 Replies

Hi @phoenix98604

 

you can create a conditional formatting rule for this.

First, highlight the range of cells where this rule should apply

Then open menu "Home | Conditional Formatting | New Rule..."

DTE_0-1637827335975.png

Then select the rule type "Use a formula to determine..." and enter the formula, which is in my example:

=A1<0

Please note, that the cell reference is based on your active cell in your highlighted area. So if your active cell is D500, then use

=D500<0

Click the "Format..."-button and change the font as you like:

DTE_1-1637827497884.png

 

It's a simple rule that you just need to create in any other worksheet or file where you need it.

 

@Martin_Weiss  Thank you for your help. However,  I am not sure I fully understand your advice.  How does one determine the "active cell"?  I want the entire highlighted spreadsheet to be affected.  Do you choose the upper left cell in the highlighted area for example?

Your advice sounds good - I just have a question regarding how you determine the active cell.

I am looking forward to hear your reply.

Art

@phoenix98604 

Number formats also allow colours to be set, with red commonly used for negative numbers. The format can be applied to entire sheets by selecting the marker to the top-left of the sheet. The problem is that you may also have counts or percentages on the same sheet which will need their own styles to be applied.

image.png

The image is of a custom number format based upon the UK accounting format.

best response confirmed by phoenix98604 (Copper Contributor)
Solution

Hi @phoenix98604 

 

the active cell is always the one sitting on the top left corner of your selection. In the following screenshot, the selected range is B3:D10, but the active cell is only B3.

 

So in the formula for the formatting rule, you would have to reference only B3 and it will apply dynamically to all other cells in the range B3:D10

 

I hope this makes it a bit clearer to you.

So you peg the upper left cell and the lower right cell of the area to be highlighted. My spreadsheet is fairly large (A - R) and 1 - 120 rows. I will give that a try. Thanks alot!
That made the color change for all numbers less than zero. Thanks very much for your help you mysterious techno-knight. :)
1 best response

Accepted Solutions
best response confirmed by phoenix98604 (Copper Contributor)
Solution

Hi @phoenix98604 

 

the active cell is always the one sitting on the top left corner of your selection. In the following screenshot, the selected range is B3:D10, but the active cell is only B3.

 

So in the formula for the formatting rule, you would have to reference only B3 and it will apply dynamically to all other cells in the range B3:D10

 

I hope this makes it a bit clearer to you.

View solution in original post