Preserving cell formatting when filling down

In this blog post, we're going to take a look at Excel's Fill Down function, and what that means for formatting.

The Fill Down function is one of the greatest time-savers in Excel. Create a formula with relative references, drag the formula down and watch your data complete itself automatically! It's a great tool, until formatting gets in the way and spoils the fun.

For those unfamiliar with the tool, the idea is that you select the cell with the formula to copy, click on the little black square in the bottom-right corner, and drag it down to create further formulas automatically. You can try it in the attached example.

A frustrating problem is that doing this also copies formatting, so a nicely-formatted table as in the attached loses its colours and you need to spend time re-formatting.

The solution is to use the right mouse button instead of the left. As you let go after dragging, you'll be presented with a number of options. The one you'll need is "fill without formatting" - the formula or data will be copied as before, but formatting will remain in place.

We have an online video and downloadable example file to demonstrate this e-tip - watch the video just here.