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

Repeating Cell Contents

by Allen Wyatt
(last updated October 18, 2017)

6

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. Make sure the Home tab of the ribbon is displayed.
  3. Click the small arrow icon at the bottom-right of the Alignment group. Excel displays the Alignment tab of the Format Cells dialog box. (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 (6171) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Repeating Cell Contents.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Automatic Question Numbering

Want to use Word's numbering capabilities to help you number a series of questions? Here's how to accomplish the task as ...

Discover More

Getting Help when Entering Functions

Need a quick memory jog when entering a worksheet function? Here's a shortcut that will be invaluable.

Discover More

Using the Selection and Visibility Pane

When you need to arrange objects in relation to each other, one of the handy tools that Word provides is the Selection ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Removing Borders

Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.

Discover More

Changing Fonts in Multiple Workbooks

If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it ...

Discover More

Merge and Center Not Available

What are you to do if you are trying to format a worksheet, only to find out that one of the tools you need is not ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 7?

2017-10-18 15:40:20

Dennis Hardison

Does it not seem strange that Microsoft would not have just used the "/" for repeating contents? After all, most of the Lotus features were included in Excel -- was there some proprietary association with "/" that precluded "borrowing" that feature as well? Just asking.


2017-10-18 04:26:06

Michael (Micky) Avidan

@Shreepad,
Would you be so kind to explain the purpose/use of the key combination: Ctrl+" ?
Thanks, Michael


2014-03-12 05:03:51

Shreepad S M Gandhi

Thanks Surendra...though Ctrl+" serves a different purpose.


2014-03-11 14:02:17

Surendera M. Bhanot

The other easier way to repat te contents of the cell just above, is to press <Ctrl+"> as many times for as many times you want to repeat the contents.


2014-03-11 06:20:04

Shreepad S M Gandhi

After reading the heading, I felt this is offering something to copy the cells. Anyways. It can be achieved by pressing Ctrl+D. For example, if cell A1 contains the text 'ExcelTips by AW' and you wish to re-type the same in cell A2, move the cursor to A2 and press Ctrl+D. The contents of A1 get copied in A2.
This works on formulae too, which may be used for formulae inserted for horizontal ranges. The only pre-requsite to make this shortcut work is the destination cell has to be horizontally consecutive of the source.


2014-03-10 09:38:45

Jennifer Thomas

One note -- in our environment (W7/XL2010) the REPT command works as advertised, but the Fill formatting only fills left-to-right on one line; it won't wrap text down for tall rows.

I can't guess why anyone would want to do this this anyway, but maybe that will avoid some confusion between which approach to use.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.