Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Posts Tagged ‘Excel Features’

Excel 2016 A Small Surprise with Autofill

Autofill has long been one of my favourite Excel productivity features. It is the fastest way I know to copy cell contents to multiple cells. If you have an existing column of data and want to one or more formulas to adjacent columns, double-clicking the fill handle adds a whole new layer of productivity.

This video illustrates this tip and the small surprise that Excel 2016 introduced that affects this feature. By default, autofill reproduces a series; in other words if the cell that you want to use as the source data for autofill contains a value like, Room 101, autofilling this cell down a column will give new values like Room 102, Room 103, Room 104 ….

That’s all well and good, but what if you wanted to assign a number of people to the same room, Room 101? In previous versions of Excel, immediately after autofilling, Excel displayed the Paste Options button. The surprised that Excel 2016 introduced is that the default does not display Paste Options.

That can prove very frustrating, especially if you autofilling cells across may rows to many columns. Fortunately, the solution is relatively simple: enable the Paste Options button by modifying your Excel Options to Show Paste Options button when content is pasted.

image

Check it out!

Are You a OneNote Fan?

Have a look at this article. It has some valuable tips on note-taking. Although the article is addressed to medical students, anyone who needs to take notes of any kind will find some excellent tips here.

Streamline Workbook Creation with Autofill

Quote of The Day

Ask not what the world needs. Ask what makes you come alive… then go do it. Because what the world needs is people who have come alive.

Howard Thurman

One day I was teaching an Introduction to Excel class when one of the participants questioned why it was important to ‘do everything quickly.’ I had discussing ways to efficiently create a workbook. I took the comment as coming from the perspective that many North Americans and Canadians, specifically, live life at too fast a pace. I happen to agree with that point of view but at the same time I prefer to get the drudgery of a task quickly out of the way so that I can dedicate more time to parts of the task that I find more interesting.

Autofill is an Excel feature that many self-taught users are not aware of. It’s easy to miss the tiny clue that the feature even exists. Autofill has two main purposes in Excel, extending a series, and copying formulas.

The Autofill handle

Unless you look very carefully, it’s easy to miss the autofill handle at the bottom right corner of the selection outline.

Once you have found the handle, you also need to watch the shape of your mouse pointer carefully:

 

When the mouse pointer resembles a black plus-sign, you are pointing at the autofill handle. You are ready to autofill. If the mouse pointer is any other shape, you are not  ready to autofill:

imageTo perform the autofill, point your mouse at the autofill handle, then press and hold  the left mouse button. Then move your hand in the direction that you want to fill, either vertically or horizontally.

Extending Series

Excel recognizes certain data as belonging to one or other series. Weekday names, month names, expressions like ‘Qtr 1’ or ‘1st Quarter’, these are all series that autofill can automatically extend for you. Here is a brief video demonstration in which I create a series of month names for an income and expense worksheet. (I recorded

 

this demo using Excel 2007 so there are some screen features that you may not see if you are using a version of Excel earlier than 2007. The basic autofill techniques and results are the same however. In the demo, I started with the abbreviation for the month of January. If I had entered the full name, then autofill would have inserted the un-abbreviated name for each month into the cell. Note also that the case of the starting cell determines the case of all of the autofilled cells.

Copying Formulas

If the only thing autofill could do was to extend series, saving us from some typing drudgery, it would still be a useful tool that every Excel user should be aware of.

However, autofill can do more. Try autofilling a formula, instead of the first cell of a series. You will find that  autofilling may faster than copying and pasting formulas when you want to apply a formula across several rows or columns

Advanced Autofill TechniquesRemember that Autofill can extend any series as far as you need.  Now, here are some specialized techniques.

  • create a series of sequential numbers
    • in Excel 2000, enter the beginning number in a cell and then hold the <Ctrl> key down while you drag the autofill handle.
    • in Excel XP (2002) and later, enter the beginning number in a cell, drag the autofill handle, then click the fill options button and select series.
    • if you want a sequential series to the right or left of a column of data, enter the starting value in the cell to the left or right of the column containing the data, point to the fill handle and double click.
  • create an intermittent series
    • to create a series of even numbers, enter the first number of the series into one cell, the next number in the series in the next cell down or to the right (depending on the direction in which you want to fill), select both cells and then fill.
    • to create a series of five weekdays with no weekend days, create a series from the first day to the last day you want to include in the week, copy that last of days and paste it in the immediate next cell, then select all of the cells that have a day name in them and fill for as many weeks as you need.

This video demonstrates these techniques.