December 3, 2020

How to add bullet points and serial numbers in Excel

In this lesson we will learn different techniques of adding Bullets points in Excel. For example we have a table containing couple of rows consisting of Player names, their acquired points in a Game and their next level eligibility status in Tick or Cross mark. Here we will add Bullet point before every Player name.

  • Now first select one cell where you need to add the Bullet. Here we have selected the cell B1.
  • Now click on Insert tab in the menu bar and then click on ‘Symbol’ in the Symbols group in the toolbar.

  • In Symbol pane we need to select the Font as ‘Normal text’ and the subset as ‘Geometric Shapes’.
  • You will find different types of bullets here like round solid, round empty, square solid and square empty bullet points as follows.

  • We can repeat the above process or copy the bullets in other cells. Below we have shown different type of bullets we added before Player names.

There is another process to add Bullet points in excel as follows.

  1. First select the cell where you need the Bullet point.
  2. Then put the below formula and press enter.
=CHAR(149)

3. It will add the solid round Bullet in the cell as follows.

4. Now let’s say we have a list of Birds in Column A and we need to add Bullet points for each of those. We can use the below formula to insert Bullet points for each bird in the list in a different column(here in column D) as follows.

=CHAR(149)&" "&A2

This is the last process to add Bullet points we will describe here is to copy paste the bullet points from MS Word or powerpoint to excel as follows.

  1. In MS-Word Bullets are available in Home tab under Paragraph group. Insert bullet points as per your choice and select the same and copy.

2. You can directly paste it in any cell in your spreadsheet.

3. Now using this technique you can create a custom format to add Bullet points in a group of cells in your spreadsheet as per your requirement. 

4. First copy the Bullet from your excel cell by double click on the cell to open it in edit mode and then select the Bullet and copy as follows.

5. Now the Bullet point is in your clipboard. Now select the cell where you need to create the Bullet list and then right click and ‘Format Cells’.

6. In Number format Click ‘Custom’. Then in Type box enter the below. The Bullet point should be pasted from clipboard as you have last copied from your spreadsheet cell.

  • @

7. Now click OK.

8. The custom format has been applied to the cell B1. Now any characters we type in this cell will lead by a bullet point. And you can also drag the cell to automatically continue the Bullet list for the following cells. Remember, once you have a bullet in your spreadsheet you can always copy that in any number of cells.

Now in this same lesson we will also learn how to automatically add serial numbers in excel.

  • In the below table let’s say we need to add serial numbers for every row in the left most column. First enter 1 in first cell A2.
  • Then drag the content of the cell A2 till cell A10.
  • Then click the square in the down right corner of the selection. A pane will appear. Select ‘Fill Series’.

  • As you can see below, the serial numbers have been added in A2:A10. If more number of rows are added, the serial numbers can be dragged in the same way as mentioned above. The serial numbers will be automatically incremented by 1 in each following cells from the starting cell from where you begin to drag.

Other than the above mentioned method, there are couple of alternate ways to work with serial numbers in Excel as follows.

  1. By default a spreadsheet contains row numbers which can work as serial numbers for each of your row entries in your table.
  2. Another faster method is to first put 1 to the cell from where you want to start the serial. 
    • Then in Home tab click ‘Fill’ icon in Editing group.

    • Now Click ‘Series’.

    • Now in the pane first select Column as you want the serial numbers in column A and then you have to add the step value and the stop value. Step value means how you would like to increment the serial. If you want the series to be incremented by 1 then put 1 here. In stop value you need to add the last number where you want to stop the series. Now click OK.

    • As you can understand with this method we can add serial numbers in rows by just selecting the Rows in above pane and also the series can be incremented or even decremented by any positive or negative number you put in step value. For example below we have added the series in a row starting with 100 and decremented by 10.

    • Not only numerical series, we can add a series of dates/days/months/years as well with this process. In below example we added the series of dates incremented by 2 in a row.

3. Even other than the Fill Series pane we can also add serial numbers incremented by a specific number except 1 with the below method.

  • In first 2 consecutive cells type the first 2 numbers in the pattern you want. Like in below example we add 1 & 4 in first 2 cells in a column from where we want to start the series. So it can be understood, the serial numbers we initiated are to be incremented by 3. Now select both of the first 2 cells and drag below. You can see the following numbers are also automatically added in a gap of 3 only.

  • Now if you want each serial number is to be incremented in multiple of 2 then you can first enter the first number in your first cell like here we started the serial from the number 2 in cell B1 and then in the next cell add the formula as =B1*2 and then drag the formula in the following cells.

4. Now another way of adding serial numbers is to enter the function =ROW() in a cell and drag it in the following cells of the same column as follows. Since the ROW() function fetches the current row number hence if you want to start from 1 from a row which is not the 1st row in your spreadsheet, then you need to subtract the number which makes the result as 1. Like in our below example we have started the serial from 4th row hence we subtracted 3 from the current row number using formula =ROW()-3 to start the serial from 1.

  • Same for adding serial numbers in row. There you need to use the function =COLUMN() as follows. It will also fetch the current column number for each cell which can act as serial number.

5. If you are looking for the way of adding serial numbers in Roman digits then enter =ROMAN(ROW())in a cell and drag it in the following cells of the same column as follows. Since here we have started from 15th row, the formula returns the roman serial numbers accordingly.

  • Same for adding Roman serial numbers in row. There you need to use the function =ROMAN(COLUMN()) as follows. It will fetch the current column number in Roman. In our below example we started our series from 5th column hence we used the formula Column()-4 to subtract 4 from the current column number to start the series from 1 and then with ROMAN() function we converted the same into Roman number.

6. The last method we will mention in this lesson to add serial numbers is by using the below formula.

=COUNTA(B$1:B1)

As here we are adding serial numbers in column A for the data starting from cell B1 then in cell A1 we need to add the above formula and drag it for the following cells in the same column.

Remember, if there is any blank data in column B then duplicate serial numbers will be generated due to that.

Leave a Reply

Your email address will not be published. Required fields are marked *