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 ‘Autofill’

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.

Excel Data Entry Tricks

Linked In member Victor Chan publishes an excellent blog at Launch Excel. In two of his recent articles,

 

Working with Data in Excel Part 1- 10 Excel Data Entry Tips Everyone Should Know

and

Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel

Victor discusses very useful Excel Data Entry techniques. Check out Victor’s articles to learn how to improve your Excel efficiency. He also offers a nicely organized collection of Excel shortcuts free if you subscribe to his blog.

Here’s another nice trick I recently learned about. Create and use a custom list to manage lists of names that you use repeatedly in a worksheet.

 

Create and Use Custom Lists

You can create and use custom list for sorting and for speeding data entry

For example, you are using an Excel Worksheet to manage shift assignments for the same group of people every week. Occasionally you need to sort the list by the names but you do not want the sorted list to be in alphabetical order. A custom list will give you that flexibility.

image

 

Start by typing the list of names, in the order you would like them, in an out of the way place. This list can be located on an otherwise unused worksheet for example. Just type one name in a cell and press enter and type the next name. When you are finished you should have a vertical list of names with no blank cells. I have created a list of six names but your list could be much longer.

If you select this list before starting the next step you will be able to import it into your custom lists with just the click of a button.

 

image

 

Now, in Excel 2010, click the File button (in 2007, click the Office button) and click Options (in 2007, Excel Options).

Select Advanced. You will need to scroll down a bit. Look for the Edit Custom Lists button and click it.

That will bring up the Custom lists dialogue. There will be at least four custom lists already there if Excel was properly installed on your system.

 

 

image

 

You should see the range you selected already filled in beside the caption, “Import list from cells.” Click the Import button and your newly created list will appear at the bottom or your custom lists.

 

 

 

 

Sort Using a Custom List 

Whenever you want to sort a list that contains these names into the order of your custom list, all you need to do is select the Sort button on the Ribbon and specify custom for the sort  level that refers to the column containing these names.

 

image

When the custom list dialogue appears, select your custom list.

Use a Custom List for Data Entry

If you have ever used autofill to create a list of months or days, you will love this added benefit of having a custom list. Once you have create a custom list, you can type one of the names form the list and then use autofill to create a list of all the names in the order of the custom list.

So, for the shift assignment example, when it comes time to create new shift assignment list, all you need to do is type one of the names from you custom list and use autofill to type the rest for you either vertically or horizontally.

Don’t Discard Your Original List

If you must occasionally add or remove names from your roster, do it in the list you built to import into custom lists. When you have that list up to date, select it and go to the Custom Lists dialogue. Click on your original custom list and delete it, then Import the revised list.

Using this technique will save you a few minutes every time you have to create a new roster for you shift assignments. The time it took you to build and import the list as a custom list will quickly be paid back ten-fold.

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.