This post will provide step-by-step instructions on how to stop Autofill from applying fill color to extended cells, allowing you to copy data without altering the formatting of your worksheet.
Table of Contents
1. What is the Autofill Color Default When Copying Cells?
We may notice that if we enter some cells with background color continuously, when you enter a value in the next adjacent cell, the cell is filled with background color as well. This autofill function is very useful if we want to enter values in unique format in worksheet, but on the other side, if we just want to mark some cells with background color, autofill function is annoying.
Autofill function is turned on in excel by default. For example, in below screenshot, if we enter Pass with yellow background several times, when entering Pending in cell C7, it will be auto filled with color which is not what we expect. And this also applies for cell C8 and even C9, C10… if you enter value in it.
So, if we want to stop autofill in excel, we can do some settings before we enter a value.Note: the Autofill color default may vary depending on your Excel version or settings. If you have customized your Excel settings or theme, the default fill color for Autofill may be different from the default setting.
2. Stop Autofill Color for Cells in Excel
If you want to stop Autofill color when copying cells in Microsoft Excel Spreadsheet, and you can do the following steps:
Step 1: Click File->Options.
Step 2: In Excel Options window, click Advanced in the left panel.
Step 3: Under Editing options, uncheck “Extend data range formats and formulas”. Then click OK to save the update.
Now when you entering value in next cell in column C, background color will not be auto filled.
My office is Microsoft 2016. So, my operation is based on this version of Excel. You can see the details above and set stop autofill function base on your Excel version accordingly.
- Uncheck the option, formats and formulas are also stopped applying for extend data range.
- After re-launching Excel, the option is still unchecked unless you enable it again.
- This setting applies for all worksheets in this workbook.
3. Fill Color Not Working
If Fill color is not working in your worksheet, and you can check the Fill color options to make sure the correct fill color option is selected.
You can access the fill color options by going to the HOME tab and clicking on the Fill Color button.
You need to make sure that “No Fill” is not selected.
You can easily prevent Autofill from applying fill color to extended cells by setting Excel options in Excel. This simple method can save you time and frustration when working with large worksheets.