Excel's Custom Fill Series and Pick From List | |||||||||||||||||||
We help people think! |
Get more out of the QI Macros and Excel by using the Custom Fill Series and Pick From List features.Custom Fill SeriesHow 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.
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:
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 ListExcels 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:
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 MacrosUse 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.
The QI Macros for Excel includes 19 Excel macros and over 60 Excel Templates. Many Excel templates can be purchased separately or as part of the QI Macros for Excel SPC Software.
© 2007 KnowWare International Inc. (888) 468-1537 |
It contains over 70 fill in the blank templates such as the Ishikawa diagram, QFD, DOE, FMEA, PPAP, and Gage R&R for MSA. Performs ANOVA, t-test, F-test, and regression analysis.
|
|||||||||||||||||
