Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

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.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** 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),

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:

- Select the cell or cells you want to format.
- Select Cells from the Format menu. Excel displays the Format Cells dialog box.
- Make sure the Alignment tab is selected. (See Figure 1.)
- Using the Horizontal drop-down list, choose Fill.
- Click OK.

** Figure 1.** The Alignment tab of the Format Cells dialog box.

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."

If you later want the cell to behave as normal, simply change the formatting to normal by following the same steps and choosing a different alignment setting in the Alignment tab of the dialog box.

*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.

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

i have excel cell like

282& 309

309&226

252&201

i want to count how much time repeates in column 309 itself in array

282& 309

309&226

252&201

i want to count how much time repeates in column 309 itself in array

@segenay,

Would you be kind enough to present, us, a ScreenShot of where you found the "Remove Duplicates" in Excel 97-2003(!) - the versions this tip is, very clearly, referring to ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Would you be kind enough to present, us, a ScreenShot of where you found the "Remove Duplicates" in Excel 97-2003(!) - the versions this tip is, very clearly, referring to ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Hi Somanka, highlight the values and then go to the data tab and choose "remove duplicates."

Greg, using the $ preserves the value. If you type =B$2+C$2, the 2s will stay when you drag it down. If you also need to drag left to right, uou can save the column value too by doing =$B$2+$C$2.

Greg, using the $ preserves the value. If you type =B$2+C$2, the 2s will stay when you drag it down. If you also need to drag left to right, uou can save the column value too by doing =$B$2+$C$2.

Hi Michael,

Thanks for the message. Sorry about the tardy response!

I did use this method but didn't get anything. It just returned a blank cell.

Any idea why?

Thanks again for trying.

Thanks for the message. Sorry about the tardy response!

I did use this method but didn't get anything. It just returned a blank cell.

Any idea why?

Thanks again for trying.

@SOmanka,

Suppose your list resides in A1:A9.

In cell B2 (Not B1) type the following array formula and copy down:

=IFERROR(INDEX(A$1:A$9,MATCH(,COUNTIF(B$1:B1,A$1:A$9),)),"")

To confirm an Array formula - use the "three key combination".

While holding down CTRL+SHIFT press ENTER instead of just pressing ENTER.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Suppose your list resides in A1:A9.

In cell B2 (Not B1) type the following array formula and copy down:

=IFERROR(INDEX(A$1:A$9,MATCH(,COUNTIF(B$1:B1,A$1:A$9),)),"")

To confirm an Array formula - use the "three key combination".

While holding down CTRL+SHIFT press ENTER instead of just pressing ENTER.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Hi. My question relates to a somewhat reverse process. Suppose I have the dataset as follows:

Black

Black

Black

Blue

Blue

Blue

Red

Red

Red

... and I just want to get to a list of the colours:

Black

Blue

Red

Can I use a shortcut?

Black

Black

Black

Blue

Blue

Blue

Red

Red

Red

... and I just want to get to a list of the colours:

Black

Blue

Red

Can I use a shortcut?

@Greg

You need to use an Absolute cell reference. =$B$2+$C$2.

When copied down this put the same result in each copied cell

You need to use an Absolute cell reference. =$B$2+$C$2.

When copied down this put the same result in each copied cell

I have a similar question but related to repeating (coping) the same formula down a column? For example, if I want to see the formula =B2+C2 in cells D2, D3, D4, D5 ... Currently, Excel iterates this formula down the column. For example:

D2 = B2+C2

D3 = B3+C3

D4 = B4+C4

D5 = B5+C5

D2 = B2+C2

D3 = B3+C3

D4 = B4+C4

D5 = B5+C5