Software

Office challenge: How can you apply formatting to an entire row, based on the contents of a single cell?

Test your skills against this Excel formatting challenge and learn the answer to last week's Access query challenge.
A client presented an interesting challenge this past week. She was using an Excel sheet to track projects and she wanted overdue projects to stand out. Using conditional formatting, she was able to shade a project's overdue date, but she wanted to shade the entire row, not just the actual date (a single cell). In other words, if the due date was past, she wanted the entire row to be bright red. How would you satisfy this requirement? Last week we asked… Why isn't your Access query returning all of the expected records? Many things could explain this situation, but I did specify that searching for a specific value and searching for everything but that value failed to return all the records. Assuming your first two criteria expressions are correct, there's only one possibility -- null values. Nonsy was the first to mention null values. Iansoady, Youzer, and Tony Hopkinson also answered correctly. Let's look at a quick example using Northwind, the sample database that comes with Access. Specifically, the Customers table has 91 records total. The following query, which matches the ZIP (or postal) code 97219, returns 1 record. You might assume that the other 90 records have ZIP codes other than 97219, but would you be right? To find all the records where the ZIP value is not 97219, you might use this next query. However, if you were expecting it to return 90 records, you were wrong. It returns just 89. The only (the best) explanation is that one of the records doesn't have a ZIP value at all, and that possibility is easy to forget when querying data. Now, the null might not matter. Your needs will determine whether the missing record(s) are important. If you must account for all records, expand one of your queries to include null values. You can do so by adding an OR operator to the expression in the following form: Is Null OR value Is Null OR Not value where value represents the string, date, or value that you're searching for or excluding. This last query uses Is Null to return null values and every ZIP value other than 97219. This time the query returns 90 records; all records are accounted for. For database developers, this is a basic issue. However, it's a problem I see frequently and begs the much larger question of how to deal with null values in the first place. Do you allow null values in your tables? Feel free to start a discussion about the validity of null values.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

33 comments
MikeZane
MikeZane

Assuming that the date in question is in column C and that there are field headings... 1. Go to field C2 and select Format... conditional formatting. 2. Change 'cell value is' to 'formula is' and click in the formula box. 3. Enter =$C2< Now() (Remove the space between the less than sign and the now function, I got an error when I tried to remove it). 4. Click 'Format' and select the format you want for that row/field (Red, Yellow, etc). 5. Click OK. 6. Select field C2 and select Copy. 7. Select all of row 2. 8. Select Edit, Paste Special. Select Formats, and click OK. 9. This will tamper with the existing formats. So if you have formats in row 2 that are numbers, centered, etc, you will want to fix those fields in row 2 before you go to step 10. Row 2 should look how you want the entire spreadsheet to look. Once you are happy with it... 10. Select all of row 2 again and click COPY. 11. Select all of the remaining rows in your spreadsheet and click Edit... Paste Special, Formats, and click on OK button. That should do the trick. It is a trick I use a lot.

RU7
RU7

Copy and Paste, Paste Special, Format Painter and the like are a one time action and you have to choose the cells or rows you want to shade. If the dates change, the formatting does not. With 1.1, if a project becomes overdue today, the display automatically shows it. This also addresses one of my pet peeves: shading or boarders on cells that are beyond the cells in use. If you shade a whole row or column, the shading goes to the end of the row, not just to the end of the data.

happymedia_dz
happymedia_dz

By using the Format Painter tool Select the appropriate cell and copy its format with the Format Painter then paste the format to the entier row by clicking on the header of the row.

stapleb
stapleb

Use Conditional Formatting with the formula =$A2

vijaykumardave
vijaykumardave

Click on the cell of which format you want to implement on whole the row. Just click on format painter and then click on raw number on which you want to apply the format. Task will be done.

patrick
patrick

drag the formatted cell across the entire row using the bottom-right black handle, then click the drop-down menu that just appeared and select "Fill Formatting only"

edh1215
edh1215

I know this is a little more complicated for the average user, but I did this very same thing for myself using VBA. Conditional Formatting is OK, but you can only do 3 rules... my way you can do most likely unlimited. All you do is create a new module (ALT+F11), then choose Module from the Insert menu. Add the following code and customize it for your data/sheet. Also, create a shortcut key for this sub (I used ctrl+alt+u) and you can update the formatting for the entire sheet whenever you want. '***** CODE START ***** Option Explicit Option Compare Text Dim iRow As Integer, iColumn As Integer, iColor As Integer 'Colors Const black As Integer = 0 Const white As Integer = 2 Const red As Integer = 3 Const green_drk As Integer = 10 Const green_brt As Integer = 4 Const blue_hyp As Integer = 32 Const blue_lgt As Integer = 33 Const pink_brt As Integer = 26 Const orange As Integer = 46 Const orange_lgt As Integer = 45 Const gray_drk As Integer = 16 Const gray_lgt As Integer = 15 Sub UpdateConditionalFormat() 'edh1215@gmail.com 'This can be run at any time to update the colors 'of the rows of this sheet based on certain criteria iRow = 1 'first row of sheet iColumn = 3 'set this to any column number where the cell is Do Until ActiveSheet.Cells(iRow, iColumn).Value = "" 'Want to start at the second row to bypass the header iRow = iRow + 1 Select Case ActiveSheet.Cells(iRow, iColumn).Value Case "some value" iColor = green_drk Case "some value" iColor = pink_brt Case "some value" iColor = blue_hyp Case "some value" iColor = orange Case "some value" iColor = gray_drk Case Else iColor = black End Select 'Set the font color for all cells of the current row Range("A" & iRow & ":" & "IV" & iRow).Font.ColorIndex = iColor 'Set the background color for all cells of the current row Range("A" & iRow & ":" & "IV" & iRow).Interior.ColorIndex = iColor Loop End Sub '***** CODE END *****

amasa
amasa

I have Excel 2007. I select every cell I want the format to apply to. Then I started to create a new rule for conditional formatting. I entered the one cell which criteria I wanted to use. I entered the criteria, and set the color. When I finish that editing the one cell above or below the threshold value changes every cell's color which I had selected to begin with.

C-3PO
C-3PO

Right click and copy the cell (or click on it and press CTRL-C) - select the cells to have the same formatting, right click and "paste special" - on the pop up menu, select "Formats" and click OK... voila - also works if you just want to copy the value, the formula, the comments etc.

Dave Pusey
Dave Pusey

Select the single cell, click "Format Painter" on the toolbar, then click on the row number on the left hand side to highlight the entire row. Job Done!

mike_johns
mike_johns

go to a cell and create a conditional format using less that =TODAY(); once this is done go to the cell and click on the format painter. Then highlight the cells you want to have the new format and click on the Format Painter one more time. This should conditionally format all of the highlighted cells. Try it

Katyknock
Katyknock

This is way too complex an answer for the simple question. Format painter is definitely the way to go - and if you don't want to format the whole row, but just other cells, just hold down the shift or control key when selecting cells after clicking the format painter.

RU7
RU7

First: why not select the cells to which you want to apply the conditional formatting before you do the formatting instead of copying and pasting after the fact? Second: select the cell range, not the whole row. If you select the whole row, you format to column IV in 2003 or XFD in 2007.

sparky75
sparky75

right click cell, select copy, right click desired row, select Paste Special, Formats.

Gilbertr14
Gilbertr14

Format Painter Use it all the time.

sokuhl
sokuhl

1. Select the cell with the desired format 2. Click on the format painter tool 3. Select the entire row by clicking on the row number and the format is applied.

avramreddy
avramreddy

You may try this Select the entire excel sheet. If you want an entire row to change to gray based on a value in column A for ex: "yes" then do the following In Conditional Formatting define a new rule under "Use a formula to determine which cells to format" - enter the formula =if($A1="yes",1,0). Select the format color (in this case gray) with which the row has to be highlighted once "yes" is entered in any of the cells in column A. Hope this helps. Venkat.

fledis
fledis

Yes, this could be OK. However- why Format Painter? Select all the table, for example- A1:K50. Let's say, dates are in cells A1:A50. Take Format- Conditional formatting. Change option from "Cell value is" to "Formula is" and write the formula: "=$A1 < TODAY()". Set appropriate formatting. That's it.

RU7
RU7

from the manual point of view, or if you only have a handful of cells involved. But, if you want to automate the formatting so it shows the current status whenever you look at the spreadsheet, conditional formatting is the bees' knees.

MikeZane
MikeZane

I am not sure what you mean by your first comment. Can you clarify? Second comment - its just a personal taste thing, I like having the whole row shaded in the event I end up adding new columns. They are already formatted with the conditional format. But sure, you can just select the cells that are being used if you like.

spiras
spiras

in Excel 2007, select the entire table, click Styles => Conditional Formatting => New Rules => "Use a formula to determine which cells to format", and in the "Format values where the formula is true" box enter: =($A1="yes") This is known as "Boolean-expression" formula style

RU7
RU7

The absolute column, relative row reference makes this work. The reference could be outside the formatted range if desired. That is not real useful in this case but you could start by highlighting B1:K50. An added benefit is if you select any row, including the one after the last data entry, and do an insert row, the formatted range expands. One drawback is that rows with empty date cells show up as overdue.

MikeZane
MikeZane

We have reports that are huge, sometimes 50-60k lines. Each line has to be highlighted based on the value of one of the cells in that line. This is the method we use. I am sure there are other ways to accomplish the same task tho. The original posting just asked how you would do it. That's how we do it.

RU7
RU7

I follow, agree with, and agreed with all that you said except where you say to have ALL the cells in row 2 reference $A2. That is OK when you ARE using all the cells in row 2. Otherwise, just have the cells in use reference $A2. From your description of having to split data across worksheets, I suggest upgrading to Excel 2007. If you still have to split because you need more than 16,384 columns, maybe it is database time. :-)

MikeZane
MikeZane

For some pages we do. Sometimes we exceed those and have to split them across multiple tabs. You don't want to see our reports. :-) If you use a reference such as $A2 and copy and paste that, the row will switch based on the row you copy to, but the column will stay the same. Copy it to row 3, and you get $A3, etc. In my data example, that's what I want. I want all cells in row 2 to reference the value in $A2 and format based on that value. However, when I copy it to subsequent rows, I want it to reference the A column in THAT row. So, first, I set the main column, which contains my date. Let's say, A2 has the date I am formatting the whole row for. My cell reference is $A2, not $A$2, I want the row to shift but not the column. Then I paste that format to all of the cells in that second row. Then, I make any other format change I want to row 2, say if some of the cells are money format, I set those, I set text formats, center, right alightment, and so on. I have turned row 2 into a template of how I want all of my subsequent rows in that sheet to look like. Next, I select row 2 and copy and paste the formats to subsequent rows. Go as far over, as far down as you like. It doesn't really matter. Now, if I open and review the conditional formatting for Cell B16, I will see it referencing $A16, and it will shade based on that cell value. It is pretty easy to do with vb, .NET, and xslt, as well as manually, and you don't have to keep changing it. Set it once for the sheet, and you can paste in new data whenever.

RU7
RU7

use column IV for your data. You really use 256 columns of data? [XFD or 16,384 columns in 2007] All the cells you apply the conditional formatting to use the type of referencing you choose. Each conditionally formatted cell also adds to the file size, albeit slightly. But if you have 16,000 cells in each row with unnecessary conditional formatting, it may be significant. The absolute/relative reference doesn't necessarily mean the same row as the selected cell. If you apply the conditional formatting to cells $C$3:$G$10 and refer to $A1 in the condition, the format of cells C3:G3 depends on cell A1, C4:G4 depends on A2, C5:G5 depends on A3 ... So the relative offset for each cell in the affected range is the cell in column A that is two rows above that given cell's row.

MikeZane
MikeZane

Step 11 should have said 'populated spreadsheet'. When I use this trick, I only select the subsequent rows that are filled in, i.e. rows 3 - 15 if that's all the farthest the data goes. Also, selecting all of row 2 is fine because you always refer back to the column that has the date field, that is not relative, it is fixed. Only the row is relative. In my example, I want the shading to apply to all of the cells in that row, so that when I open the spreadsheet and the date calculates, the entire row shades in to indicate that item is past due, not just the cell where the date is filled in. It would be nice if I could upload a sample spreadsheet so you could see what I meant.

MikeZane
MikeZane

setting the entire spreadsheet to point to the reference column. By using a formula that is fixed to a column but relative to a row, any row you copy it to will look at the column for that row. If you only used the section you suggest, none of the other rows would look at the date column.

RU7
RU7

Select ONLY the cells you want formatted. In other words, if you have data in columns B through R and rows 2 through 1027, make your selection B2:R1027. If you select "row 2", you will apply shading to cells A2 to IV2 (in 2003). It is worse if you are formatting whole columns because empty cells register as less than any given date so they will always format as overdue.

fledis
fledis

Step 1 should be "Select whole table" or "select whole sheet" if you predict future expanding of the table. Steps 6 - 8 are unnecessary.

pgurney
pgurney

This solution worked great and met the criteria that the shading be done automatically. Using the format painter and paste special after the fact are extra steps.

Editor's Picks