Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Repeating Cell Contents

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Repeating Cell Contents.

Julius is a former Lotus 1-2-3 user. He notes that in that program, if he presses the "\" key and follows it with anything, Lotus will repeat what he types to fill the entire cell. For example, if he types \12345, Lotus will show 123451234512345..., regardless of the width of the cell. Julius is wondering how to do the same in Excel.

There are a couple of ways you can do this one is with a formula, using the REPT worksheet function:

=REPT(12345,10)

This formula repeats the text "12345" ten times. This approach works well if you know exactly how many times you want to repeat the text, but not so well if you don't. This leads to the second approach, which is to use formatting for the cell. Follow these steps:

  1. Select the cell or cells you want to format.
  2. Select Cells from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Alignment tab is selected. (See Figure 1.)
  4. Figure 1. The Alignment tab of the Format Cells dialog box.

  5. Using the Horizontal drop-down list, choose Fill.
  6. Click OK.

The result is that the cell formatting in this way will repeat whatever it contains as many times as it can fit that result into the cell. If the cell is not wide enough to display even one occurrence of the result, then you will see the familiar #### markers. The cell will only display entire occurrences of the result, not partial occurrences. Thus, it will display "1234512345," but not "1234512345123."

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3214) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Repeating Cell Contents.

Related Tips:

Tame Your Data! ExcelTips: Filters and Filtering provides all the details necessary to let you manage large sets of data with confidence and ease. Its information-packed pages demonstrate how to use the two types of filters provided by Excel: AutoFilters and advanced filters. Check out ExcelTips: Filters and Filtering today!

 

Comments for this tip:

jasmine    18 Feb 2014, 01:30
How do I undo this, when I make an entry excel repeats that entry after I tab over.
Paul Whitaker    22 Jun 2012, 04:40
The "" key functio still works if you go into your Excel Options, Advanced, Lotus compatibility, and tick the "Transition Navigation Keys" check box.

Regards
Paul

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.