Adding bullet to each line in cell

Excel Tips - Bullet Point 1

Does it sound familiar to you? Are you using Excel as word processing tool?

If you have experienced the task shown above, you should know how tedious and time-consuming it could be as you cannot convert each line into bullet point by clicking one button as if in Word or PowerPoint.

Luckily enough, there is a workaround trick for this task.  (Supposed you have input each new line by using Alt+Enter) Excel Tips - Bullet Point

The trick is about Find and Replace, obviously.  What is not so clear to you is what I input in the dialogue box for “Find what” and “Replace with:”.  😛

Step by step illustration

1. Select the range where you need to add bullet to the beginning of each new line

Excel Tips - Bullet Point 1

 2. Ctrl+H to open the Find and Replace dialogue box

Here’s the tricky points

2.1 Click into the Find what:

  • Hold Ctrl Key and press j

(note: you may notice nothing, but at a closer look you will see a tiny blinking dot at the bottom of the input box)

2.2 Click into the Replace with:

  • Hold Ctrl Key and press j
  • Hold Alt key and press 7 (on numpad of your keyboard) and then release

2.3 Click Replace All

Explanation of the actions

Ctrl+J is the way to input line break in Find and Replace

In a regular cell, if you want to insert a line break, you need to press Alt+Enter.  However you cannot do that in the Find and Replace, where you need “Ctrl+J” instead to insert a line break.

Alt+7 (on the numpad) is the easiest way to insert the round bullet

You don’t have to use this round bullet as your bullets.  Try explore different Alt+Number combination and pick the bullet you like.

You may also be interested in my earlier post Input special characters in Excel by pressing Alt + Numpad

Having understood what Ctrl+J and Alt+7 does, it’s easy to understand what we did in the Find and Replace dialogue box.  We instructed Excel to Find all line break in the selection; then replace it with a line bread AND a round bullet.

Excel Tips - Bullet Point

However we are only 90% done.  As you see the action performed did not take care of the first line in cell because the beginning of first line is not a result of a line break.  So how to tackle that?

Here comes another formatting trick

  1. Select the range
  2. Right Click –> Format Cell
  3. Custom format:
  • @   by typing Alt+7 (on numpad) followed by @

(Note: you may leave a space in-between or not depending how you would like to present your bullet points, but at least it should be consistent what you did to the rest.)

As simple as this. 🙂

Having said that, if we are talking about only a few cells, I would actually suggest you add the bullet to the first line cell by cell because custom format can drive some people nuts.  😛

Excel is not intended for word processing.  So better not to complain its stupidity in area of word processing.

 

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

4 Responses to Adding bullet to each line in cell

  1. Michael (Micky) Avidan says:

    Hi, Wong,
    It seems that NO Alt+Enter was pressed before the AAA.
    If my assumption is correct how did you manage to find a Ctrl+J as a Prefix before the AAA ?
    Thanks,
    Micky

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s