How to Fill Without Formatting in Excel
To fill without formatting in Excel, drag the fill handle to extend your values, then click the Auto Fill Options smart tag that appears and choose Fill Without Formatting. This copies the values, numbers, or formulas across the range while leaving the destination cells’ original colors, borders, and fonts untouched.
As a frequent Microsoft Excel user, you might have come across the need to fill cells with specific text or numerical values without carrying over the formatting of the source cells. This scenario is common when working with large datasets, and it can be time-consuming to reformat each cell manually. In this blog post, we will guide you through the simple process of filling cells without formatting in Excel, saving you time and streamlining your workflow.
Introduction
Microsoft Excel is widely used for managing large amounts of information. When working with data, you might come across the need to fill cells without carrying over the formatting of the source cells. This is a great feature that saves you time and effort in formatting your datasets. Excel provides an easy way to fill cells without the source cells’ formatting in a few simple steps. In this post, we will guide you through the process of filling cells without formatting in Excel.
Step 1: Select the Cell Range
To fill cells without formatting in Excel, you need to specify the range of cells that you want to fill. Select the cells that you want to modify by clicking the first cell in the range, dragging the cursor to the last cell, and then releasing the mouse button.
Step 2: Open the Fill Dialogue Box
Once you have selected the cell range, next, open the Fill dialogue box. You can do this through the Excel ribbon by clicking on the “Home” tab and then clicking on the ‘Fill’ menu dropdown. Click on the “Fill” option to open the Fill dialogue box.
Step 3: Fill the Cells without Formatting
In the Fill dialogue box, you will see four filling options. Choose the “Without Formatting” option, which is pictured as a ‘1’ with no formatting. Once you have selected the Without Formatting option, click on the “OK” button.
Step 4: Verify the Results
Your selected cell range will be filled with the values from the first cell in the range. You can now verify that the cells are filled correctly without carrying over any of the source cells’ formatting.
Filling cells without formatting in Excel can save you a lot of time when working with large datasets. The process is a straightforward and simple process that can be done in a few clicks. Following these easy steps will ensure that your data is consistent and formatted correctly.
The Fill Handle Method (Drag, Then Choose “Fill Without Formatting”)
The fastest everyday method uses the fill handle — the small green square in the bottom-right corner of a selected cell. Select the source cell or range, hover over the fill handle until the cursor turns into a thin black cross, then drag down or across to extend the values. By default this drag copies both the contents and the formatting.
To strip out the formatting, look for the small Auto Fill Options button (a smart tag) that appears just below the bottom-right of the filled range as soon as you release the mouse. Click it and a short menu opens with options such as Copy Cells, Fill Series, Fill Formatting Only, and Fill Without Formatting. Choose Fill Without Formatting and Excel instantly re-applies the values while restoring each destination cell’s original look. This is the same AutoFill behavior you already use for series and patterns — you are simply telling Excel to leave the visual styling alone.
Right-Click Drag for More Control
If you would rather decide before you release the mouse, drag the fill handle with the right mouse button instead of the left. When you let go, Excel pops up a context menu in place, listing Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, and more. Picking Fill Without Formatting here does the same thing as the smart tag, but it can feel cleaner because you make the choice as part of a single gesture rather than hunting for the button afterward. This right-click drag trick works the same way whether you are filling text, numbers, dates, or formulas.
Copy a Formula Without Formatting (Paste Special → Formulas)
Filling without formatting also applies to formulas. If you want to copy a formula into a new range but keep that range’s existing borders, shading, or number format, use Paste Special → Formulas. Copy the source cell with Ctrl + C, select the destination range, then press Ctrl + Alt + V (or right-click and choose Paste Special) and select the Formulas option before clicking OK. Excel pastes the formula logic — with relative references adjusting as expected — but does not overwrite the destination’s formatting. Choosing Values instead pastes only the calculated results, which is the right pick when you want numbers rather than live formulas.
Using Ctrl+D and Ctrl+R (and How They Carry Formatting)
Two keyboard shortcuts fill quickly but behave differently from the smart tag. Ctrl + D fills the contents of the top cell down into the selected cells below it, and Ctrl + R fills the leftmost cell’s contents to the right. Both are great for repeating a value or formula in a hurry — but be aware that they copy formatting along with the contents. There is no “without formatting” variant of these shortcuts, so if you need to preserve the destination’s look, fall back to the fill handle’s Fill Without Formatting option or to Paste Special instead.
The Reverse: Fill Formatting Only
Sometimes you want the opposite result — copy a cell’s colors, borders, and number format without touching its values. The same Auto Fill Options menu (and the right-click drag menu) includes a Fill Formatting Only choice for exactly this. Drag the fill handle, then pick Fill Formatting Only to paint the source cell’s styling across the range while leaving the existing data in place. It behaves much like the Format Painter, giving you a quick way to standardize the appearance of a column without rewriting its contents.
When to use Fill Without Formatting
As you work on different datasets in Microsoft Excel, you might come across the need to copy and paste values from one cell range to another. When you copy and paste values, you will notice that the formatting of the source cells is also copied along. This may not be ideal for the new dataset you are working on. In this instance, you can use the fill without formatting option to ensure that you copy only the values without any formatting.
Other Options under Fill Dialogue Box
The fill dialogue box in Excel contains other options besides ‘without formatting’. These options include filling cells based on a series of numbers, dates, formatting, or even using a custom list. However, if you want to fill without formatting, you choose the first option labeled ‘without formatting’.
Keyboard Shortcut for Fill Without Formatting
Excel provides a keyboard shortcut for filling cells without formatting, which can make the process even faster. To use this, select the cell range you want to fill then press “ALT + E + I + W” and press Enter. Doing this will fill your selected cell range without any formatting.
Use the Paste Special Function
Another way to fill cells without formatting is to use the paste special function. Copy the data you want to fill, place the cursor on the first cell you want to fill, right-click, and then choose Paste Special under the options list. Select the ‘Values’ option from the ‘Paste Special’ dialog box, then click on “OK” to paste only the values into your cells. This is the most reliable way to paste values without the underlying formulas or formatting, and it is well worth learning the basic paste workflow if you do this often.
Windows vs. Mac
The methods above are nearly identical across platforms, with a few keyboard differences. On Windows, copy with Ctrl + C and open Paste Special with Ctrl + Alt + V; the ribbon path is Home → Fill, and the Alt + E + I + W shortcut opens the right-button-drag style options. On a Mac, copy with Cmd + C and open Paste Special with Ctrl + Cmd + V; the fill handle, Auto Fill Options smart tag, and right-click drag menu all work the same way. The legacy Alt + E + I + W key sequence is a Windows-only feature, so Mac users should rely on the fill handle’s Fill Without Formatting option or Paste Special.
In Conclusion
Whether you want to copy and paste data or fill a range of cells using specific values, using the fill without formatting option in Excel can simplify your work. It ensures that the data you copy or fill is precise and formatted consistently. You can use the available options under the fill dialog box or the keyboard shortcut to fill cells without formatting, saving you time and effort.
Frequently Asked Questions
Here are some frequently asked questions regarding filling cells without formatting in Excel:
Can I fill only part of the cells without formatting?
Yes, you can select the range of cells that you want to fill without formatting, so you don’t have to apply the option to your entire worksheet if you don’t need it. Simply select the cells that you want to modify and follow the steps outlined in this post.
Do I have to use the fill dialogue box to fill without formatting?
No, you don’t have to use the fill dialogue box to fill without formatting in Excel. Another option is to use the paste special function, which is often faster and more convenient. Simply copy the data you want to fill, right-click on the cell you want to fill, select paste special, then choose values, and hit OK.
Can I use the fill without formatting option on a single cell?
Yes, you can absolutely use the fill without formatting option on a single cell or a range of cells. Simply select the cell or cells that you want to modify, then follow the steps we outlined in this post.
Is the fill without formatting option available in Google Sheets?
Yes, Google Sheets also has a fill without formatting option that works in the same way as Excel. Select the cells you want to fill, then right-click and choose “Paste values only” to fill without formatting.
Can I undo a fill without formatting in Excel?
Yes, you can undo a fill without formatting by pressing CTRL + Z or clicking on the undo button. This will take your worksheet back to the state before the fill was performed, so you can make any necessary changes.
How do I autofill without changing the format of existing cells?
Use the fill handle to drag your values across the range, then click the Auto Fill Options smart tag that appears and choose Fill Without Formatting. This is the cleanest way to autofill a series or pattern while leaving each destination cell’s existing colors, borders, and number formatting intact. If you frequently fill the same recurring sequence, you can also set up a custom AutoFill list so the values populate automatically.
