Nov 24 2021 04:29 PM
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.
Nov 25 2021 12:06 AM
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.
Nov 26 2021 09:50 PM
@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
Nov 27 2021 02:33 AM
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.
Nov 27 2021 04:09 AM
Solution
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.
Nov 27 2021 03:50 PM
Nov 27 2021 09:25 PM
Nov 27 2021 04:09 AM
Solution
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.