Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.

We can use a formula to convert the list below into a bulleted list in a text box. You need Excel 2016 or later to use this technique. The example file can be downloaded at the bottom of the page.

WARNING: There is a limit to how many characters can be be displayed using this technique, so always check the last notes have displayed.

There is a formula in cell A1, it is.

=CHAR(10)&CHAR(149)

The CHAR function returns a specific character based on the number used.

CHAR(10) returns the line feed character

CHAR(149) returns the bullet character

The & joins the characters together. This creates a line feed followed by a bullet point. We will use this cell to separate the entries in the list in A2:A5. You can see the result in the image below.

The text box is linked to cell C1. To link a text box to a cell – as soon as you have drawn the text box click in the Formula Bar and press = and then click the cell to link to.

The formula in cell C1 is

=TEXTJOIN(A1,1,A2:A5)

The TEXTJOIN function was added in Excel 2016. It combines/joins entries from a range and it allows you to specify a separator character(s) and whether or not to ignore blank cells. The formula above uses cell C1 as the separator and ignores blanks (1) and combines the entries in the range A2:A5.

Ignoring blanks

The Ignore blanks option (second argument use a 1) in the TEXTJOIN function is handy as you can delete an entry in the range and it doesn’t leave a blank line in the text box – see image below.

If you wanted to add a space between the bullet and the first word you can add a space to the separate in cell A1. Revised formula for cell A1.

=CHAR(10)&CHAR(149)&" "

You can download the example file at the button below.

Download example file