Dec 19, 2013 Conditional formatting can be copied as part of the formatting of a cell by selecting the cell(s) and pressing option-command-C. The format, including conditional format rules may then be pasted to another cell or group of cells on the same table or another table on the same or another sheet by selecting the cell(s) and pressing option-command-V.
You can set Excel 2011 for Mac to change the format of a cell, cell range, table, or pivot table based on conditions you specify. Use these settings when you want a cell’s appearance to change as the result of a formula or when someone types in a worksheet. Conditional formatting was improved in many ways for Excel 2011 for Mac. There are more conditions from which to choose.
Conditions can be external to the cell you’re formatting, and conditions can be based on criteria on other worksheets. Excel 2011 has new formatting options, such as borders, solid fills, icon sets, plotting negative values, and data bars.
There are billions of possible combinations, but there are five major styles of conditional formatting, which you can find when you go to the Home tab’s Format group and click the Conditional Formatting button. Each option has a submenu, palette, or dialog associated with it.
If you apply a rule to a data set and then apply another rule, both rules will be in play. Use the Clear Rules option near the bottom of the Conditional Formatting pop-up menu to remove conditional formatting.
If you select a cell, cell range, or table before choosing an option, the formatting you apply will affect the selection. If you do not make a selection first, the conditional formatting applies to the entire worksheet.
Related Articles
- 1 Extend Column Equations in Excel
- 2 The Highlight Function in Excel
- 3 Repeating Rows in Excel
- 4 Segmentation Using Excel 2007
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.
Warning
- If your paste area includes existing conditional formatting, it will be overwritten unless you choose one of the two merging options.
References (3)
About the Author
Shawn McClain has spent over 15 years as a journalist covering technology, business, culture and the arts. He has published numerous articles in both national and local publications, and online at various websites. He is currently pursuing his master's degree in journalism at Clarion University.
Cite this Article Choose Citation Style
McClain, Shawn. 'How to Copy & Paste for Conditional Formatting in Excel.' Small Business - Chron.com, http://smallbusiness.chron.com/copy-paste-conditional-formatting-excel-54266.html. 01 March 2019.
McClain, Shawn. (2019, March 01). How to Copy & Paste for Conditional Formatting in Excel. Small Business - Chron.com. Retrieved from http://smallbusiness.chron.com/copy-paste-conditional-formatting-excel-54266.html
McClain, Shawn. 'How to Copy & Paste for Conditional Formatting in Excel' last modified March 01, 2019. http://smallbusiness.chron.com/copy-paste-conditional-formatting-excel-54266.html
Note: Depending on which text editor you're pasting into, you might have to add the italics to the site name.