Copying and pasting cells from a conditionally formatted sheet to another file or sheet can be a bit tricky, especially if you want to keep the formatting but remove the conditional rules. In this article, we will guide you step by step on how to achieve this without losing any formatting.
Step 1: Open both the source and destination files/sheets
First, open both the source file/sheet (the one with the conditional formatting) and the destination file/sheet (where you want to paste the cells with formatting but without conditional rules).
Step 2: Select and copy the cells
In the source file/sheet, select the cells that you want to copy. You can do this by clicking and dragging your mouse over the desired cells. Alternatively, you can use the keyboard shortcut Ctrl + Shift + Arrow keys to select a range of cells.
Once you have selected the cells, right-click on the selection and choose "Copy" from the context menu. Alternatively, you can use the keyboard shortcut Ctrl + C to copy the cells.
Step 3: Paste the cells with formatting
Switch to the destination file/sheet where you want to paste the cells. Right-click on the first cell of the destination range and choose "Paste Special" from the context menu.
In the "Paste Special" dialog box, select "Values" under the "Paste" section. This will paste only the values of the cells without any formatting or conditional rules.
Next, select "Formats" under the "Paste" section. This will paste the formatting of the cells from the source sheet.
Finally, click on the "OK" button to paste the cells with formatting but without conditional rules.
Step 4: Remove the conditional rules
At this point, you have successfully copied and pasted the cells with formatting to the destination sheet. However, the conditional rules from the source sheet are still applied to the pasted cells. To remove these conditional rules, follow these steps:
- Select the pasted cells in the destination sheet.
- Go to the "Home" tab in the ribbon.
- In the "Styles" group, click on the "Conditional Formatting" button.
- From the dropdown menu, select "Clear Rules" and then choose "Clear Rules from Selected Cells" option.
This will remove the conditional rules from the selected cells, while preserving the formatting.
Step 5: Save the destination file
Once you have removed the conditional rules, it is important to save the destination file to ensure that the changes are retained. Go to the "File" tab in the ribbon and choose "Save" or use the keyboard shortcut Ctrl + S.
That's it! You have successfully copied and pasted cells from a conditionally formatted sheet to another file or sheet with formatting but without conditional rules.
Conclusion
Copying and pasting cells with conditional formatting can be a bit tricky, but by following the steps outlined in this article, you can easily achieve the desired result. Remember to carefully select the cells, use the "Paste Special" feature to paste values and formats, and remove the conditional rules from the destination sheet. With these steps, you can seamlessly transfer data between sheets/files while preserving the formatting.
References
| Number | Source |
|---|---|
| 1 | Copy conditional formatting |
| 2 | Excel Conditional Formatting |