Microsoft Excel 2019 is a powerful management tool for many business owners. It combines the simplicity of a cell-based information system with formatting features that both highlight important data and make the spreadsheet data more accessible to clients and partners. Conditional formatting allows you to create formatting rules based on the contents of the cell. For example, you can define that a cell with a value over 100 automatically displays with a red background, allowing anyone viewing the spreadsheet to immediately identify those cells. To extend your conditional formatting to a new part of the spreadsheet, a new worksheet or even a new workbook, Excel’s “Format Painter” feature lets you easily copy conditional formatting.
Colors and Graphics
As mentioned in the intro, you can use conditional formatting to make certain values stand out with colored text and backgrounds. You can, for example, indicate problem data values in red, and favorable ones in green. Conditional formatting also lets you create bar graphics inline with your data, where the bar length gives a quick visual indicator comparing the figures of interest.
Your Data Detective
Conditional formatting can act as a “data detective,” automatically revealing the facts behind the figures. Using color, font style and other visual attributes, the spreadsheet can show which numbers are the minimum and maximum in a range, or which fall above or below a preset value. It can also indicate which values are duplicates. You can use conditional formatting to compare all manner of values, including numbers, text and dates.
Method for Excel 2019
The following method works for Excel versions 2013 through 2019. Click the cell containing the conditional format you want to copy. Click Home, then click to select Format Painter, which appears next to Copy, Paste and other editing functions. The mouse pointer will change to a paintbrush symbol.
Click and drag over the cell or range of cells that you want to receive the conditional formatting. Excel automatically copies the formatting into the range when you release the mouse button.
To copy conditional formats multiple times, double-click Format Painter. To discontinue the Format Painter mode, press the Escape (Esc) key.
Method for Excel 2010
For Excel 2010, right-click on the cell that contains the conditional formatting to move to other cells. Click Copy from the pop-up menu. Even if numerous cells contain the formatting, you only need to copy one cell.
Click the top-left cell in the area where you want to paste the formatting. Hold down the mouse button. Drag the mouse to the bottom-right cell in the paste area, then release the mouse button. Right-click anywhere inside the selected area.
To copy only the conditional formatting, move your mouse over Paste Special, then choose Paste Special from the new menu that appears. Click the radio button next to Formats and click OK.
To copy the contents and the formatting, choose All merging conditional formats. To combine your copied formats with existing conditional formats in the paste area, click the Merge Conditional Formatting button.
If your paste area includes existing conditional formatting, it will be overwritten unless you choose one of the two merging options.