For example, in the screen shot above, I could copy the regions and products headings from B2: C4, and paste them into cell B10: C12, for the latest day’s data.The Excel table automatically expands to include the new rows, and fills in the formulas in column F. And that copy and paste trick is what I’ve been doing every day, for months on end.
Get the detailed steps on my Conditional Formatting page.The previous solution is listed below, in case you’d rather try that. I’m a big fan of Excel’s conditional formatting, and often use it to highlight cells on a worksheet.It’s also useful for alerting people to problems, and other devious plots, like hiding cells’ contents, until other cells are filled correctly.But this week I ran into a conditional formatting crisis, and had to start from scratch.
Fortunately, the fix didn’t take too long, but with complex formatting, things could have been much worse.
Just select a column and choose a number format from the Number pane in the Home ribbon: However, things can become troublesome when trying to apply number formatting to an existing range of data.
You can update the format of a cell, but sometimes it won’t automatically refresh with the new formatting.
But, instead of one simple rule, there were hundreds of rules!
You can see the tiny scroll box at the bottom right in the screen shot below.
The red border really makes the list easier to read, especially if I’m looking at it before my morning coffee!