Where Business Meets Perfection.
Follow Solfecta! Like Solfecta on Facebook! Follow Solfecta on Twitter! Follow Solfecta on LinkedIn! Follow Solfecta on Google+!

Thursday Tips and Tricks: Microsoft Excel – Using the Excel Fill Handle

Using the Excel Fill Handle

Most Excel users already know about the fill handle. If you don’t, I am referring to the small square that appears when you highlight a cell or a range of cells.

Exceltip1

If you have never used the fill handle, you are missing out on a very powerful tool. It can be used to fill data in a variety of ways.

Copying with the Fill Handle
Most people copy and paste large ranges of data that they want to repeat it across a large range. This works but it requires several more clicks or keystrokes than the Fill Handle. For example, let’s say I want to copy cell A1 which has the words “Fill Handle” all the way down to cell A10 (or cell A1000 for that matter). You could copy cell A1, select cells A2 to A10, then paste. Or, you could simply drag the Fill Handle down to cell A10.

Excel tip 2

The same concept can be used to copy multiple cells or a repeating range. For example, if I want to fill in the Quarter 1, Quarter 2, Quarter 3 and Quarter 4 for many rows in Column A, I can type these values and then drag the Fill Handle down as far as I need. However, I have to be careful that I am not implying a range (see below). If I simply type Quarters 1-4, the fill handle will assume I want to continue with Quarter 5, etc. Instead, type Quarters 1-4, then one more Quarter 1. The Fill Handle will now see this as a range I want to repeat rather than extend.

Excel tip 3

Filling a Simple Range

The most common use of the fill tool is to fill a simple range. For example, if I want to create a sequence of numbers in Column A, I can fill in the first few and then drag the Fill Handle down to fill the range. While my example is simple numbers, I could also use it to fill in alpha sequences, days of the week, months of the year, years, etc.

Exceltip4

Creating a Complex Range

The Fill Handle does not just work for simple ranges. As long as I create a recognizable range, the Fill Handle will figure it out. A simple example would be to count by 3s. Type 3, 6, and 9 then drag the Fill Handle to count by threes. Another example would be to fill every Monday for a full year. Start by filling in the first two or three dates, then drag the Fill Handle to fill the rest. As I mentioned earlier, imply a range and the Fill Handle will try to figure it out.

Experiment with the Fill Handle. It is a very useful tool that will help you accomplish Excel tasks much faster and more accurately than typing or copying and pasting.

Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+
Tags: , , , , ,

Leave a Reply