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

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.