Using conditional formatting to hide text in a range A1:N50, by referencing values in a separate single row P1:P50
Our take
This question matters because it shows how quickly a spreadsheet can move from simple data entry into a miniature rules engine. The user wants to hide values across a range when each cell is less than or equal to the reference value at the bottom of its own column. That is a reasonable business need, not an edge case. Similar frustrations appear in discussions like "The operating system is not presently configured to run this application", "Aggravated by Excel changing numbers to an exponential", and "How to calculate a value based on multiple dropdown lists", where the real issue is not the spreadsheet itself, but the amount of hidden logic users are expected to manage manually.
The practical answer is simpler than the forum post suggests. If the data range is B2:L7 and the reference row is row 9, select the full range B2:L7, then apply a conditional formatting rule with a formula such as =B2<=B$9. The key is the mixed reference. B2 is relative, so it changes as the rule moves across rows and columns. B$9 locks only the row, which means each column compares against its own bottom reference: B2 against B9, C2 against C9, and so on. To hide the text visually, the formatting can set the font color to white, or the custom number format can use ;;; to suppress displayed values. Conditional formatting does not truly delete or hide the underlying value, but it can make the cell appear hidden to the reader.
This is exactly the kind of workflow that reveals both the power and the limits of traditional spreadsheets. The formula itself is not complex once you understand relative and absolute references, but that understanding is not evenly distributed across teams. A single misplaced dollar sign can turn a scalable rule into a column-by-column maintenance problem. That friction is why spreadsheet work often feels more fragile than it should. Users are not struggling because
Hi folks,
I have a set of data across multiple rows/columns, for which I'm trying to use conditional formatting to hide text if it meets various criteria. I have no problem with simple criteria like "value must exceed 0.01". However, one set of criteria is that the value for all the data in a column, must exceed a reference value at the very bottom of the column.
Sample image here for a smaller dataset.
For example, in the sample image, all of the values B2:B7 which are less than or equal to B9 should be hidden, all the values C2:C7 which are less than or equal to C9 should be hidden, etc.
I'm aware that I could do this on a column-by-column basis with if-then statements. But is there an efficient way for me to use a formula in Conditional Formatting that will allow me to do this efficiently across the entire dataset?
Referring to the sample image, I would select all data B2:L7, select Conditional Formatting, and then New Rule. From here, I've tried using "Format only cells that contain", using the criterion "Less than or equal to", and then I tried entering B9:L5, but it returns an error saying that the reference can only be a single cell.
I can't seem to figure out how to use the "Use a formula to determine which cells to format" to do this and would greatly appreciate help.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting for highlighting cellsI have two very lengthy columns, let’s call them column A and column B. I need to highlight all of the cells in column B whose content does not appear in column A, but there are 80+ cells I’d have to check and it’d take me a really long time. I’m in no way an expert on excel but I do know I’d have to use conditional formatting, I just don’t know how to build the formula for this :( submitted by /u/Nandolvs [link] [comments]
- Conditional formatting with formulasI am trying to create a spreadsheet to track ordered parts and their status, including their requested delivery date. What I'm trying to sort out, and failing at, is setting a conditional format so that when the requested delivery date is less than X days out (say 14 for now), I want it to highlight the cell red so I can check on the status of the order. This is my current rule configuration, which I set by selecting the column so it auto populates as it's filled in. https://preview.redd.it/n4g68xoihzsg1.png?width=409&format=png&auto=webp&s=6715ebba2e873e171fad3b862ff3a7d188b4b3a1 Inconveniently, it's doing the exact opposite of what I want: https://preview.redd.it/h7a7in7ohzsg1.png?width=294&format=png&auto=webp&s=1168efcd78872a5158cd045e6c3ea642c1328152 In this instance, ONLY 4/13 should be highlighted (I'm not worried about the top row at this point, I'll fix that after). I've even tried creating another column to do the math, and use that cell for the formula, and that's not working either. It seems like it should be straight forward, but I apparently lack the formatting skill. submitted by /u/Senior_Cheesecake155 [link] [comments]
- Conditional formatting based on a checkboxHowdy y'all. I'm trying to format a spreadsheet being used for a golf outing. It's not a big deal, but it will satisfy my OCD if I can click my "PAID" checkbox, and it will highlight the row with the person who has paid. Should be fairly simple and straight-forward, but it's giving me some headaches. So far with all of my formatting, I've been able to just use IF statements to make everything work, but in this situation, it's not going to work that way. Excel AND the internet want me to just use the formula in the conditional formatting window =M7=TRUE (and obviously cascade this down for each line beyond 7), however this is only highlighting the initial cell; when selecting B7:I7, the formula will only cause B7 to format, and the rest will not, even though when I click on each individual cell, it's showing that the formula is applied to those other cells. I'm really trying to not have to individually format like 500 cells if I can avoid it. Also, if I cannot come to a solution, I guess I will just suffer without the highlighting. lol Thanks ahead of time! submitted by /u/RayDanger2020 [link] [comments]
- Conditional formatting based on range of values in different column?https://preview.redd.it/l3guc8q9wfog1.png?width=1324&format=png&auto=webp&s=ec9fba2147eebc33af9e108241f82cb9163cf512 I've tried several different ways to do this but clearly I'm missing something. I have spreadsheet that shows the hours scheduled on all the machines at our facility. The dates in row 2 auto populate based on today's date. I want to make a rule that changes the formatting of rows 3 - 12 if any of the dates in row 2 are company holiday. For example, if B2 matches any of the dates in R3 through R12, I want B3 through B12 to fill in black with red text, so that way it shows that date is blacked out because we're closed, but if someone accidentally scheduled an order that day the machine hours will show in red so they know to reschedule it. On the original version of this spreadsheet I made a few years ago I had the same issue and end up just using =B$2=$R$3, so for each day I had to make 10 different rules for every holiday in column R. I knew that definitely was not the best way to do it but I was tired of messing with it and just needed it to work. I am updating this spreadsheet and it now shows the next 60 days instead of the next 14 days and I know there's got to be a better solution than creating 10 different rules for each day. I know =B$2=$R$3:$R$12 does NOT work and I think I'm starting to smell burnt toast. submitted by /u/BluestockingLife [link] [comments]