How to Fill Without Formatting in Excel When Cells Are Filtered
Question details
The user needs to fill values or formulas over a filtered Excel range without carrying over source formatting, but the Fill Without Formatting option does not appear.
- Product
- Microsoft Excel
- Device & OS
- Windows and macOS
- Scenario
- The user is dragging the fill handle or using Auto Fill on a range that contains filtered cells and expects Excel to offer Fill Without Formatting.
- Observed behavior
- Excel only displays the conditional formatting option and does not show Fill Without Formatting when filtered cells are involved.
Check whether a filter is active in the source or destination range and decide whether you can temporarily remove it. If you plan to use Paste Special, confirm whether you need to copy values, formulas, or both.
Use Paste Special to fill values or formulas without formatting
Use this workaround when you want to keep the filter active and control whether values or formulas are pasted separately.
Excel does not reliably provide Fill Without Formatting when the source or destination range contains filtered cells. Paste Special lets you copy the data you need without dragging over filtered or hidden cells.
Select the filtered cells that contain the data you want to fill, then press Ctrl+C on Windows or Command+C on Mac to copy them.
Click the first destination cell or select the destination range where the values or formulas should be filled.
On the Home tab, click the paste drop-down arrow and choose Paste Special. You can also press Ctrl+Alt+V on Windows or Control+Command+V on Mac.
In the Paste Special dialog box, select Values to paste only the cell values or Formulas to paste only the formulas, then click OK. Source formatting will not be applied.
Temporarily remove filters to restore Fill Without Formatting
Use this method if you prefer the drag-and-fill workflow and can reapply the filter afterwards.
Use WPS Spreadsheets for a Free, Compatible Excel Alternative
WPS Office is a free, lightweight Microsoft Office alternative that opens and saves Excel .xlsx files, preserves formatting, and provides a familiar ribbon and fill-handle workflow. When Excel's filtered-cell limitation interrupts your formatting task, WPS Spreadsheets offers a seamless environment for everyday spreadsheet work.
- 1. Download and install WPS Office: Go to the WPS Office download page, click Download WPS Office Free, and install WPS Spreadsheets on Windows or Mac.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file by clicking Open on the Home tab or dragging the file into the workspace.
- 3. Use Auto Fill Options: Select the source cell and drag the fill handle. When the Auto Fill Options icon appears, choose Fill Without Formatting to keep your data clean.
Frequently Asked Questions
Why does Fill Without Formatting disappear on filtered cells in Excel?
Excel restricts Auto Fill options when the source or destination range contains filtered cells. In that case, the Fill Without Formatting option may not appear and only conditional formatting options are shown.
Can I fill only values and not formatting while filters are active?
Yes. Copy the source cells, select the destination range, then go to Home > Paste > Paste Special and choose Values. This pastes the values without source formatting.
Does removing the filter delete any data?
No. Removing a filter does not delete data. It only clears the filtered view, and you can reapply the filter criteria from the Data tab after filling the cells.
Does WPS Spreadsheets support Fill Without Formatting?
In WPS Spreadsheets, you can drag the fill handle and use the Auto Fill Options button to choose Fill Without Formatting. WPS Office is a free alternative for working with Excel files.
How do I submit feedback about this Excel limitation?
In Excel for Windows, click File > Feedback and send a frown. On Mac, click Help > Feedback to report the missing Fill Without Formatting option for filtered cells.
