Conditional formatting is a handy tool in Excel that can help you to add visual highlights to data to identify trends, highlight errors or perform analysis on sets of data. With conditional formatting it’s easy to apply a formula to a range of cells which tells Excel to format the data one way if the formula result is TRUE and format it another way if the result is FALSE. If you are looking for help on setting up conditional formatting, you might want to check out this article.
For the most part, you’ll apply formatting to a range of rows or columns of data, and use a fixed formula or setting to highlight the data, however, it becomes more complicated when you want to apply the conditional formatting to a series of data (i.e. non-consecutive cells, rows or columns), or if you want to copy the same conditions to other pages or areas of a worksheet.
In a recent example, I had a spreadsheet charting monthly sales figures, broken down by user, comparing each user against an average. The data was laid out into a report which meant I wanted my conditional formatting to only be applied to every other row. This meant I couldn’t set up my conditional formatting in the usual way, by simply selecting all of the rows and applying the format and was faced with having to highlight and format each row manually one at a time – for 100s of rows!!
But of course if you know me, you’ll know my motto that we’ll never do something the long way if there’s a more efficient way around it! And that’s where I remembered the Format Painter… This nifty little tool literally saved me hours of work and I was able to format my entire report in a matters of minutes, here’s how:
Setup your primary formatting
First of all, you need to setup your conditional formatting on the first range of data. In the example below I’ve selected the first cell in my dataset on my report and set it to highlight any sales that are below target.
I now want to copy this same formatting to the corresponding lines for the other users and you won’t believe how easy it is!
Copy to Paintbrush
Simply click into the cell that is already setup with the conditional formatting you want to copy then go to the ribbon > Home tab > Edit Group> Format Painter
You will see your cursor change to a paintbrush and the formatting from the cell you previously clicked into has now been copied to the paintbrush. One thing to note is that if your conditional formatting contains a formula with relative and absolute references, you might need to adjust these after copying to make sure that the formatting is applied correctly to the other cells.
Next all you need to do is click the cells or drag the paintbrush across the rows and columns you want to apply your formatting to. You can also hold down CTRL whilst clicking to highlight a series of cells, rows or columns.
Top Tip: Instead of holding CTRL you can also double-click on Format Painter button if you want to keep the paintbrush open to paste the formatting into other cells.
When you’re done, simply press Esc to hang up your paintbrush!
That’s all there is to it! And here’s the finished result: