If you find yourself typing the same list of items across a
number of Microsoft Excel cells over and over again for each worksheet you add
to your workbook, you can save yourself considerable typing by creating a named
formula to enter the data for you.

For example, suppose you need to type the names of your
Sales Regions in row 2 of every worksheet. First, create the named formula by
following these steps:

  1. Press
    [Ctrl][F3].
  2. Enter
    SalesRegions in the Names In
    Workbook Text box.
  3. In
    the Refers To text box, enter the following array formula:
={"North West","NorthEast","SouthEast","South West"}
  1. Click
    Add and then click OK.

Then, in order to add this list to row 2 of your worksheet,
select the range B2:E2, type =SalesRegions in the
formula bar, and then press [Ctrl][Shift][Enter].

You can also use this method to enter labels down a column.
However, instead of using a comma to separate the regions, you would use a
semicolon, as shown below:

={"North West";"North East";"South East";"South West"}

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.