In this tutorial, I am going to share with you 4 quick tricks to put a circle around a number in Excel. You can easily apply these methods in any set of data to arrange the numerical values inside circles or any other shapes. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks.
Download Practice Workbook
You can download the practice workbook from here.
4 Quick Tricks to Put a Circle Around a Number in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Product ID, Product Name, and Sales (units). Although we may vary the number of columns later if that is needed.
1. Using Excel Illustrations Feature
In this first method, we will use the Excel illustrations feature to put a circle around a number. Follow the steps below to do this.
- First, go to the Insert tab and click on Shapes under Illustrations.
- Next, select the Oval shape from the list of available shapes.
- As a result, this will insert the oval shape where you click on the sheet.
- Then, select the shape and navigate to the Shape Format tab at the top of the screen.
- Next, click on the Shape Fill drop-down and select No Fill.
- Consequently, this will create a circle around the number in the dataset.
2. Inserting Excel Symbol to Put Circle Around a Number
We can also use the symbol options in Excel to put a circle around a number very easily. Let us see how to do that in a few steps.
- To begin with, go to the Insert tab and click on Text Box under Text.
- As a result, this will insert a text box on the location you click on the sheet.
- Now, again go to the Insert tab and click on Symbol under Symbols.
- Next, in the Symbol window, select the Circle symbol and click Insert.
- Immediately, you should see the circle shape inside the text box.
- Finally, move the circle to the position you desire and this will give you the final result.
3. Using Quick Access Toolbar
Another very useful option we have in Excel is the Quick Access Toolbar which gives us a quick and easy way to get to any commands. Below we shall see how to apply this to put a circle around a number very easily.
- To start with this method, click on the Quick Access Toolbar icon at the top of the screen.
- Next, click on More Commands.
- Now, in the Excel Options window, select All Commands under Choose commands from.
- Then, select the Oval option and click on Add.
- After that click OK.
- As a result, this will add the Oval shape command in the Quick Access Toolbar as in the image below.
- Now, click on the Oval shape and again click on the sheet where you want to insert the shape.
- Finally, format the shape as we saw before and you should get the following result.
4. Applying Excel VBA to Put a Circle Around a Number
If you are familiar with VBA in Excel, then you can put a circle around a number with just a few clicks. Below are the steps to achieve this.
- First of all, go to the Developer tab and click on Visual Basic.
- Next, in the new window, click on Insert and select Module.
- Then, in the Module window enter the following code:
Sub CircleToNumber() Dim p, s As Single, Sh_area, Sh_oldrange As Range Set Sh_oldrange = Selection.Cells(1) For Each Sh_area In Selection.Areas With Sh_area p = .Height * 0.1 s = .Width * 0.1 ActiveSheet.Ovals.Add Top:=.Top - p, Left:=.Left - s, _ Height:=.Height + 2 * p, Width:=.Width + 1.5 * s With ActiveSheet.Ovals(ActiveSheet.Ovals.Count) .Interior.ColorIndex = xlNone .ShapeRange.Line.Weight = 1.25 End With End With Next Sh_area Sh_oldrange.Select End Sub
In the above code, we are initially declaring Single and Range type variables. The Selection method allows us to work with the selected cells. The ActiveSheet.Ovals.Add command adds the oval shape in the set location from the top and left. Finally, Interior.ColorIndex portion sets the fill color of the shape to null.
- After that, go to the Developer tab and click on Macros.
- Now, in the new Macro window, select the macro CircleToNumber and click on Run.
- Consequently, this will add an oval to the cell you are currently in and you can convert this into a circle by resizing the shape.
Read More: How to Circle Text in Excel (3 Easy Ways)
I hope that you were able to apply the methods that I showed in this tutorial on how to put a circle around a number in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.