Harkem (one of my 12 fans) wrote me recently and asked:
“How can I use bullet points in Excel? Much of my PowerPoint presentations are made of Excel ranges that are copied and pasted as pictures. I remove the gridlines to make it look seamless in PowerPoint. I’d like to be able to use bullet points in Excel to enhance the illusion.”
Well Harkem, you’ve come to the right place. I happen to know the answer.
Let’s say I want to add bullet points to this list of topics. I could manually type in some asterisks or dashes, but that would be tedious and ugly.
There is a better way.
Step 1: Find a symbol and insert somewhere in your workbook
The first step is to find the symbol that would make an appropriate bullet point. Here I’ll choose a diamond.
After you choose the symbol, insert it into any empty cell in your worksheet.
Step 2: Copy the symbol
Highlight the inserted symbol and press Ctrl+C on the keyboard to copy the symboal.
Step 3: Add a Custom Number Format to the target text
Highlight the range of cells you would like the bullet points to be applied, right-click, and then choose Format Cells.
The Format Cells dialog box, you’ll add your own custom format.
Here, entered the diamond by pasting the copied symbol. I followed it with a space, then the “at” symbol (@).
After you confirm the custom number format, you’re bullet point will be applied to your text.
The benefit of using this technique is that you can edit the text and Excel will automatically place your bullet point.
Plus, you can copy the format to any range of cells to apply the same bullet points.
I’ve gone crazy and added all kinds of bullets to my list. I’m living the dream.
There you go Harkem. Now send me some cookies or bacon.
RELATED STUFF
- Outta My Way Formula Thingy!
- Find and Highlight Specific Values
- Convert Numbers to Military Times in Excel
- Mocking the ‘Merge & Center’ Icon
- Lazy Alternate Shading
- Limiting Movement within Your Worksheet
- Forcing Number Formatting in a Pivot Table
- Filter on Selection in Excel
- Protect Worksheet Structure with Array Formulas
- Padding Numbers with Zeros
Or bacon cookies! Or did I just guess this week’s bacon recipe?!
You can also use Alt + 7 prior to your text to insert a bullet.
Ivan: Alt+7 doesn’t do anything for me. Am I missing something?
Alt + (numeric)7 does the trick.
Alt-0149 does the same thing, and might be easier to remember. You just select a cell (don’t enter it by pressing F2) and type Alt-0149. When you let go of the Alt key, the character appears, and you can continue editing the cell.
Alt+(numeric 7) = awesome. Benefit of using the formatting, though, is that your text is still intact.
I can’t seem to figure out how to trigger the numeric 7 on my laptop.
But I still prefer the number formatting trick though.
I can’t imagine entering Alt+7 or Alt+0149 before each text entry. I would much rather enter them all, then apply bullets at once.
Also, with a number format, you can copy the bullet rule to many ranges. With Alt+7, you’re basically stuck re-entering the bullets for each new cell/range.
Yeah, the number formatting is far superior.
@ datapig
laptop should have a function key option to enable a 10-key of sorts. That should do the trick, but add that hassle for every entry…
I only use Alt+7 when I need a quick bullet. You can also use Alt+Enter to add a carriage return and then add a second bullet within the same cell. For fancier formatting I would use your methods.
By the way, I can never get any of the Alt+ methods to work on my laptop they only work on my desktop or on my laptop if it is in the docking station and hooked up to a keyboard.
One other thing I just remembered, you can use Custom Number Formatting like in your version but use Alt+7 (or Alt-0149) @ to get a bullet as part of your number format.
@Ivan:
Than’s handy
I loved this! I had no idea this could be done!
Superb! Now to go forth and ‘bulletize’ every excel file I can find.
Thanks for the excellent tip!
Regards,
Sumit
Dear Datapig, hi all,
Thanks for the great tips.
Some other symbols can be find on the dailydoseofexcel-blog
http://www.dailydoseofexcel.com/archives/2009/09/04/alt-number/
Cheerio
Tv
When i try to paste my symbol into the open format cells custom box it won’t let me. The paste option seems to be disabled the moment i right-click to get into the custom format box. I tried opening the clipboard to paste the symbol that way and even though i can see it in the clipboard list, once the format cells box is open, i can’t have access to it.
Les
If you can’t paste the symbol, use the ALT+7 trick to enter the bullet inside the custom text entry box. It works there too.
I am now trying to get a webding symbol (the camera, character #0181) inserted with this method, and can’t get it to work.
I am trying to insert this symbol after a formula.
Any ideas?
Is there a way for the text wrap to also be left aligned?
Some got crazy with the bullet points in an Excel spreadsheet I’m supposed to work on. I need them removed – please see below. These bad boys are all in one cell. (like this all through the spreadsheet! Can any give me a “quick fix?” – Thanks!
• Professional Grade Chrome Vanadium Shaft
• Triple Chrome Plated Finish Resists Corrosion
• Square Mechanic-Style Handle for Maximum Torque
• Magnetized Vapor-Blasted Tips
saved my life! works perfectly! THANK YOU
That is very slick. I have never heard of the “@” trick in custom formats. Thanks for the tip!
Thanks for this – it worked first time – excellent.
Thanks very much!