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)

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

 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.

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.