Using custom lists

Do you ever find yourself getting exhausted having to repeat the same column headers or row labels each time your create a new worksheet?  This occurs when there is a list or group of headings that are repeatedly used on different spread sheets.  For example, you might frequently need to list the months of the year with breaks for quarter totals and a year total.  Your company uses the same column headers during a fiscal year.  It might be “Jan 2010″ or “Jan ’10″.  One option is to open the last spread sheet where you created the column headers and perform a copy and paste.  Another might be to create a template, that is the default Excel workbook that opens when Excel is started.  All are good options, but have limitations.  We recommend users set up their own customized lists in Excel.   It is easy to set up and saves loads of time when creating new spreadsheets.

Here’s an example of how to set up your own customized list.  The first step is to create the list to be customized on a new spreadsheet (or open an existing one with the desired headings).  Our example company uses the following time periods:

Jan ’10 | Feb ’10 | Mar ’10 | 1st Qtr ’10 | Apr ’10 | …. to |Dec ’10 | 4th Qtr ’10 | Total ’10 |

Our list on a worksheet would appear as follows: 

List to be added to Excel's custom list database

Now that we have our list, we just need to add the list to Excel’s “List” database.  First highlight the entire list.  With the list highlighted, select the “Excel Options” from the ribbon (in Excel 2007), as follows:

Select Excel Options

Then select the “Edit Custom Lists” option below:

Select Edit custome lists

This will bring up the Custom Lists dialog box.  You should see the range that you selected earlier referenced in the the Import box.  Assume that the correct range is referenced in the Import box, just select import to add your custom list to Excel’s custom list database.

Select Import

Select OK once you have imported your custom list.  To use your custom list, go to a new spread sheet and type in any member from your list.  Grab the lower right-hand corner of the cell and drag your mouse across the area to be filled in. 

Grab the lower right-hand corner with your mouse and drag

You will then be rewarded with your list automatically populated across your range.  Hope that helps make you just a little more productive!

Cheers,

Chase Morrison

Resource Planning Solutions

www.rpscgi.com

info@rpscgi.com

Advertisement