Fill without formatting in MS Excel

When you use the AutoFill handle in Excel, as well as copying your formula it also copies your formatting.

In the example shown here, the worksheet was already formatted with a border around the table. When you use the AutoFill handle to replicate the formula in cell C3 down through range C4 to C11, a little symbol shows when you leave go of the mouse. If you ignore this symbol and move to a different cell, you will find that the bottom border in cell C11 is no longer there.

If however, you open up the dropdown next to the symbol, you will find you have three options:

If you select the third of these options, Fill Without Formatting, the border will remain and your formula will be correctly copied down.

Find out more about our Excel training London, UK or our Excel training in the Dorset, Hampshire, Wiltshire areas of the UK.

Find out more about our Excel training Sydney, Australia.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , , . Bookmark the permalink.

4 Responses to Fill without formatting in MS Excel

  1. D. Mitchell says:

    Is there a way to set the default to fill without formatting?

  2. jdonbavand says:

    Not without the use of a macro. If you drag with your right mouse button, instead of your left, as you leave go, you will be given options as to whether you want to fill without formatting though.

  3. Heather says:

    I speculate the reason why you labeled this blog, “Fill without formatting in MS Excel | ifonlyidknownthat”.

    In any event I really loved the article!Many thanks,Beatriz

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s