Forum Discussion
Coloring spreadsheed numbers automatically
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.
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.
6 Replies
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.
The image is of a custom number format based upon the UK accounting format.
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..."
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:
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
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.
