How do I make a recurring worksheet in Excel - MS Excel Tips and Tricks

How do I make a recurring worksheet in Excel - MS Excel Tips and Tricks
Page content

REPT and Automatically Entered Text

Having things repeat within a worksheet can save you time by eliminating the need to retype the information over and over again. But, how do you get your information to repeat? Here are a few formulas that will help you create recurring information within an Excel worksheet.

First of all, if you’re creating a list where you know that you’re going to have the same text repeating within an Excel worksheet, the REPT function will come in very handy. You can use this to fill in a particular cell with a certain number of instances of a text string. To do this, follow the following formula: REPT(text,number_times). The text in this case would be the information that you want to repeat and the number of times is how often that you want to repeat this particular text.

Excel will also automatically input text that has been entered previously. For example, if you typed “LinkedIn Text” once, the next time that you go to type in this phrase, Excel will read what you are typing and offer to finish the text. This will save you time, and keep you from hitting copy and paste.

Fill Commands

Another way to quickly add recurring data to a worksheet is with the Fill command. You can do this via the Fill Handle in Excel 2007.

  • Go to the Microsoft Office Button, and select Excel Options.
  • Go to Advanced, Editing Options and Enable Fill handle and cell drag-and-drop. This will display the fill handle.
  • Drag the Fill Handle where you would like your text to repeat. The Auto Fill Options button will show up so that you can choose how you would like the text to be filled.

To fill an adjacent cell with the same data or formulas, do the following:

  • Select the empty cell that you would like to fill.
  • Go to Home -> Editing->Fill and select down, right, up or left.

Custom Fill Series

Another way to create recurring data or a recurring worksheet is with the custom fill series. This can be used with either existing data or data that you create. You can always change the information within a custom fill series. What’s nice about a custom fill series is that you can use it from worksheet to worksheet. The downside is that you can only create a custom fill series containing text or text mixed with numbers. If you want to create a custom list with numbers, they will have to be formatted as text.

Select the number of cells that will encompass all the numbers that you want to format as text.

  • Go to Home -> Number Group -> Number Format -> Text.
  • You can now type the numbers in cells formatted as text.

Next, you can create your custom fill series.

With existing text:

Select the items that you want in your fill series.

  • Go to Microsoft Office Button -> Excel Options -> Popular.
  • Click Top options for working with Excel and Edit Customs Lists.
  • Verify the correct information is selected, and click Import.
  • Select OK.
  • Select a cell, and then type the item with which you want to start your custom fill series.
  • Drag the fill handle, and select the cells that you want to fill.

With a new list of items:

  • Go to Microsoft Office Button -> Excel Options -> Popular.
  • Click Top options for working with Excel and Edit Customs Lists.
  • Go to New List, and type your entries into the List entries box.
  • Click enter after each of your entries.
  • When your list is done, select Add -> OK.
  • Click OK again.
  • Select a cell, and then type the item with which you want to start your custom fill series.
  • Drag the fill handle, and select the cells that you want to fill.

Creating a Template

If you want your own worksheet to repeat, you can copy and paste your formulas into a new worksheet and create a template that you can use over and over again. To create an Excel template, follow these instructions.

  • Pick the worksheet that you want to use as a template.
  • Go to the Microsoft Office Button, and click Save As.
  • In the Save as type box, select Template.
  • Once you have created your template, you can insert it into a new worksheet by right-clicking the sheet tab of the new worksheet, and clicking Insert.
  • Then double-click your template.