{"id":2181,"date":"2022-12-23T16:07:05","date_gmt":"2022-12-23T16:07:05","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=2181"},"modified":"2024-02-28T16:48:09","modified_gmt":"2024-02-28T16:48:09","slug":"how-to-copy-conditional-formatting-to-another-cell-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-copy-conditional-formatting-to-another-cell-in-excel\/","title":{"rendered":"How to Copy Conditional Formatting to Another Cell in Excel?"},"content":{"rendered":"

Copy Conditional Formatting to Another Cell in Excel is a game-changing feature that brings dynamic interactivity to your spreadsheets. By setting up rules based on the values of other cells, you can create visually intuitive dashboards, highlight critical data points, and automate the visual representation of changes and trends. This powerful tool not only enhances your data’s readability but also aids in faster decision-making and error detection. Harness the capability of conditional formatting linked to other cells to transform your data analysis, making your Excel projects more efficient and insightful than ever before.<\/p>\n

This Tutorial Covers:<\/strong><\/p>\n

    \n
  1. How to copy Conditional Formatting Using Paste Special in Excel<\/strong><\/a><\/li>\n
  2. How to copy Conditional Formatting Using Format Painter in Excel<\/strong><\/a><\/li>\n
  3. Issues when Copying Conditional Formatting<\/strong><\/a><\/li>\n<\/ol>\n

    1. How to copy Conditional Formatting Using Paste Special in Excel?<\/strong><\/h2>\n

    The conditional formatting can be copied and pasted from one cell to another just like you can copy and paste cells within the same sheet or even between sheets or workbooks.<\/p>\n

    The cell cannot be copied and pasted, as you may have noticed. Make sure to only copy and paste the conditional formatting rules into a cell after copying it (and not everything else, such as the value or the formula).<\/p>\n

    You must utilize Paste Special and ensure that you only copy and paste the conditional formatting.<\/p>\n

    Assume you have a dataset like the one below, where I’ve used conditional formatting to column B (the English score) to highlight any cells with a value lower than 60.<\/p>\n

    \"Copy<\/p>\n

    What if I wanted to use the same conditional formatting rule for the second column (which is for the Math score) such that all the cells below 60 were highlighted?<\/p>\n

    This is simple to accomplish!<\/p>\n

    The procedures to copy conditional formatting from one cell to another are described below:<\/strong><\/p>\n

    Step 1: <\/strong>Choose cell B4 and then right-click it and choose “Copy” (or press the shortcut “Control + C”)<\/p>\n

    \"Copy<\/p>\n

    Step 2:<\/strong> Choose the complete range where you wish to duplicate the conditional formatting (C2:C8 in this example)<\/p>\n

    \"Copy<\/p>\n

    Step 3:<\/strong> Anywhere in the selection, click with the right mouse button. Select “Paste Special” from the menu. The \u201cPaste Special\u201d dialog box will then be displayed.<\/p>\n

    \"Copy<\/p>\n

    Step 4:<\/strong> Select \u201cFormats\u201d from the \u201cPaste Special\u201d dialog box and then select “OK”<\/p>\n

    \"Copy<\/p>\n

    By following the steps above, the conditional formatting from column B would be copied and applied to the chosen cells in column C.<\/p>\n

    \"Copy<\/p>\n

    One thing to keep in mind is that Paste Special will copy all formatting when you use it to copy conditional formatting. Therefore, any borders or bold text that may be present in the cells would likewise be copied.<\/p>\n

    Note:<\/strong><\/p>\n

    When copying and pasting conditional formatting to cells in another sheet or even another workbook, the same procedures as before would apply.<\/p>\n

    2. How to copy Conditional Formatting Using Format Painter in Excel?<\/strong><\/h2>\n

    Using the tool known as “Format Painter,” you can copy the format from a cell (or range of cells) and then paste it.<\/p>\n

    You may also use format painter to copy and then paste conditional formatting because it is a component of formatting as well.<\/p>\n

    Imagine you had a dataset like the one below, where I’ve applied conditional formatting to the English score column to highlight any cells with a value lower than 60.<\/p>\n

    \"Copy<\/p>\n

    Following are the instructions for using Format Painter to copy conditional formatting in excel from one cell to another:<\/strong><\/p>\n

    Step 1:<\/strong> Choose the cell (or group of cells) from which you want to replicate the conditional formatting.<\/p>\n

    \"Copy<\/p>\n

    Step 2:<\/strong> Click the \u201cHome\u201d tab and simply select the \u201cFormat Painter\u201d icon under the \u201cClipboard\u201d group.<\/p>\n

    \"Copy<\/p>\n

    The cursor transforms into a painter icon, as shown below.<\/p>\n

    \"Copy<\/p>\n

    Step 3:<\/strong> Choose every cell where you want to use the copied conditional formatting. The result looks like below.<\/p>\n

    \"Copy<\/p>\n

    The format painter can be used on the same sheet, another sheet in the same workbook, or even on a different workbook once it has been active.<\/p>\n

    Format painter also replicates all the formatting, just like paste special did (including the conditional formatting).<\/p>\n

    Pro tip:<\/strong> Click on the Format painter icon twice if you want to copy the conditional formatting and paste it on numerous cells or ranges (that you can’t pick all at once). By doing so, you can continue using the format painter and repeat the formatting process (unless you hit the Escape key).\u00a0<\/strong><\/p>\n

    3. Issues when Copying Conditional Formatting<\/strong><\/h2>\n

    Conditional formatting can typically be copied and pasted from one cell to another without any issues.<\/p>\n

    However, if you chose specific cells to format using a custom formula, you might run into problems.<\/p>\n

    The formatting is applied when the formula returns TRUE for a cell and is not applied when the formula returns FALSE when using this option, which enables you to construct your own formula.<\/p>\n

    If you have used absolute or mixed references in a formula for conditional formatting, it might not copy correctly.<\/p>\n

    For instance, in the example below, I have highlighted all cells in column B with values lower than 60 using the formula =$B2=60.<\/p>\n

    \"Copy<\/p>\n

    However, it still refers to the B column when I duplicate this conditional formatting to column C, and the outcome is incorrect (as shown below).<\/p>\n

    \"Copy<\/p>\n

    So, it’s recommended to examine the formula and update the references if you copy conditional formatting from one cell to another and don’t get the desired result.<\/p>\n

    For instance, if I modify the formula in this instance to =B2=60, everything should be good.<\/p>\n

    In case you’re wondering where the formula is placed, select \u201cConditional Formatting\u201d from the \u201cHome\u201d tab. Select “New Rule” from the list of possibilities that appears. Select the \u201cUse a formula to select which cells to format\u201d option in the \u201cNew Formatting Rule\u201d dialog box.<\/p>\n

    You will see the field where you can enter the formula for the chosen range when you do this. The cell will be formatted if this formula returns TRUE for it; else, it will not.<\/p>\n

    \"Copy<\/p>\n

    Therefore, using Paste Special and Format Painter, you can replicate conditional formatting from one cell to another in Excel.<\/p>\n

    Check the custom formula that was used in it if you notice any problems with it.<\/p>\n

    Application of Copy Conditional Formatting to Another Cell in Excel<\/strong><\/h2>\n