Excel's Custom Fill Series and Pick From List

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

Get more out of the QI Macros and Excel by using the Custom Fill Series and Pick From List features.

Custom Fill Series

How many times have you typed Jan, Feb, Mar in a column? Do you create lots of charts for each plant or hospital in your company? If you are typing the same thing over and over again, there is a better (and much faster) way!

Let's say you need to create a column with Jan 05 to Dec 05. Here is how Excel's custom fill series works.

• Type Jan 2005 in a cell.
• Now click on the cell and notice that the lower right corner has a box.

• Click on the box and drag it down 11 rows.
• Presto you now have column with Jan-05 to Dec-05.

Pretty simple isn't it? Drag the box down 23 rows and get Jan-05 to Dec 06. Try this with Monday thru Friday, 1, 2, 3 or Part 1, Part 2, etc.

You can also create a pattern and Excel will follow it. Type in cell 1 and then in cell 2. Excel will detect a pattern and follow it. Here is an example of creating a column with every other day.

Select both cells and then grab the lower right box and drag down as many rows as you want. Use this to count by 2s, 5s, etc.

You can also create your own custom list. Let's say you do lots of charts with the same five locations. Denver, Los Angeles, New York, Orlando, Phoenix. If you already have these cities typed in a spreadsheet do the following:

• Click and drag over the cells with the cities to select them
• Go to Tools/Options/Custom Lists and select Import then OK
• This will create a custom list
• To access your new list, type the first value in the list (i.e. Denver), then select the cell with Denver in it and drag down the lower right box to the next four cells.

If you don't already have these values in a spreadsheet go to Tools/ Options/ Custom Lists and select Add. Type your list into the box. Then click on Add again to create the list. For more information on this functionality go to Excel's help index and type in Custom, Fill Series.

Pick From List

Excels Pivot Table function can help you summarize large spreadsheets of raw data. Unfortunately, the Pivot Table requires values to be EXACTLY the same to summarize them. Do you know how many ways there are to spell Colorado Springs? (Colo Springs, CO Springs, Colo Sprgs, CO Sprgs, etc.) How about Accounts Payable? I am sure you have your own personal favorite.

When setting up a spreadsheet for others to populate, you can create a finite list of selections for them to use and then have them access the list using Excel's Pick From List function. Here's how:

• Create your finite list of error codes, cities, etc. in a spreadsheet.
• Right click on the next cell under the list and select "Pick From List". Excel will bring a up a pull down of options from your list.
• Select one and the cell will be populated with that value.

You can show your master list or hide it. To hide it, select the rows with your list, then click on Format, Row, Hide.

Application for the QI Macros

Use the Custom Fill Series to save time when creating your data sheets. Use the Pick From List function to obtain consistent descriptions for fields that you want to total with Excels Pivot Table function and then create a Pareto or bar char.

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

Try QI Macros
FREE
For 30-Days

Or Buy It Now!