Excel is a powerful tool that allows you to perform various calculations, analysis, and visualization of data. One of the features that Excel offers is the ability to insert shapes and text boxes in your worksheets. You can use these objects to enhance the appearance and functionality of your spreadsheets.
However, sometimes you may want to combine a shape and a text box in the same cell, so that the text is aligned with the shape. For example, you may want to add a small colored circle and some text beside it to indicate the status or category of a data point. This can be useful for creating dashboards, reports, or charts that are easy to understand and interpret.
In this article, we will show you how to add shape and text beside each other in excel cell in excel formula. We will explain the basic theory behind this technique, the procedures to follow, and a comprehensive example with real numbers. We will also provide some alternative approaches that you can use to achieve the same result.
The basic idea behind adding shape and text beside each other in excel cell in excel formula is to use the & operator and the TEXT function. The & operator is used to concatenate (join) two or more text strings or values. The TEXT function is used to format a value into a specific text format.
For example, if you want to add the text “Hello” and the value 10 in the same cell, you can use the formula:
="Hello" & 10
This will display “Hello10” in the cell.
If you want to add a space between the text and the value, you can use the formula:
="Hello" & " " & 10
This will display “Hello 10” in the cell.
If you want to format the value into a currency format, you can use the formula:
="Hello" & " " & TEXT(10,"$#,##0.00")
This will display “Hello $10.00” in the cell.
The TEXT function takes two arguments: the value to format, and the format code. The format code specifies how you want the value to be displayed as text. You can use various symbols and characters to create different format codes. For example, the format code “$#,##0.00” means that the value will be displayed with a dollar sign, a comma separator for thousands, and two decimal places.
Procedures
To add shape and text beside each other in excel cell in excel formula, you need to follow these steps:
- Insert a shape in your worksheet. You can choose any shape from the Insert tab, under the Illustrations group, and click on the Shapes button. Then, select the shape you want and draw it on your worksheet. You can resize, move, and format the shape as you like.
- Select the shape that you want to link to a cell. When it is selected, you will see small selection handles around the exterior of the shape.
- Click once in the formula bar, and type an equal sign (=).
- Click on the cell that contains the text or value you want to link to the shape, and press Enter. This will create a formula that references the cell in the shape. For example, if you want to link the shape to cell A1, the formula will be =A1.
- Insert a text box in your worksheet. You can do this from the Insert tab, under the Text group, and click on the Text Box button. Then, draw a text box on your worksheet. You can resize, move, and format the text box as you like.
- Select the text box that you want to link to a cell. When it is selected, you will see small selection handles around the exterior of the text box.
- Click once in the formula bar, and type an equal sign (=).
- Click on the cell that contains the text or value you want to link to the text box, and press Enter. This will create a formula that references the cell in the text box. For example, if you want to link the text box to cell B1, the formula will be =B1.
- Adjust the position and size of the shape and the text box, so that they are aligned and fit in the same cell. You can use the alignment and distribution tools from the Format tab, under the Arrange group, to help you with this task. You can also use the arrow keys to nudge the objects by small increments.
- Repeat steps 1 to 9 for each shape and text box that you want to add in your worksheet.
Example
Let’s see an example of how to add shape and text beside each other in excel cell in excel formula. Suppose we have the following data in our worksheet:
| Name | Score | Grade |
|---|---|---|
| Alice | 85 | B |
| Bob | 92 | A |
| Charlie | 76 | C |
| David | 65 | D |
| Eve | 88 | B |
We want to add a colored circle and the grade beside each name, to create a visual representation of the data. To do this, we can follow these steps:
- Insert a circle shape in the worksheet. To do this, go to the Insert tab, under the Illustrations group, and click on the Shapes button. Then, select the Oval shape from the Basic Shapes category, and draw a circle on the worksheet. You can resize, move, and format the circle as you like. For example, you can change the fill color, the outline color, and the outline weight of the circle.
- Select the circle that you want to link to a cell. When it is selected, you will see small selection handles around the exterior of the circle.
- Click once in the formula bar, and type an equal sign (=).
- Click on the cell that contains the grade you want to link to the circle, and press Enter. This will create a formula that references the cell in the circle. For example, if you want to link the circle to cell C2, the formula will be =C2.
- Insert a text box in the worksheet. To do this, go to the Insert tab, under the Text group, and click on the Text Box button. Then, draw a text box on the worksheet. You can resize, move, and format the text box as you like. For example, you can change the font, the font size, the font color, and the alignment of the text box.
- Select the text box that you want to link to a cell. When it is selected, you will see small selection handles around the exterior of the text box.
- Click once in the formula bar, and type an equal sign (=).
- Click on the cell that contains the grade you want to link to the text box, and press Enter. This will create a formula that references the cell in the text box. For example, if you want to link the text box to cell C2, the formula will be =C2.
- Adjust the position and size of the circle and the text box, so that they are aligned and fit in the same cell. You can use the alignment and distribution tools from the Format tab, under the Arrange group, to help you with this task. You can also use the arrow keys to nudge the objects by small increments.
- Repeat steps 1 to 9 for each circle and text box that you want to add in your worksheet. You can also copy and paste the existing objects, and then change the cell references in the formula bar.
The final result will look something like this:
| Name | Score | Grade |
|---|---|---|
| Alice | 85 | <img src=“circle.png” width=“10” height=“10” style=“vertical-align: middle;”> B |
| Bob | 92 | <img src=“circle.png” width=“10” height=“10” style=“vertical-align: middle;”> A |
| Charlie | 76 | <img src=“circle.png” width=“10” height=“10” style=“vertical-align: middle;”> C |
| David | 65 | <img src=“circle.png” width=“10” height=“10” style=“vertical-align: middle;”> D |
| Eve | 88 | <img src=“circle.png” width=“10” height=“10” style=“vertical-align: middle;”> B |
Note: The images of the circles are not actual shapes, but placeholders for illustration purposes. You can use any shape and color that you prefer.
Alternative Approaches
There are some alternative approaches that you can use to add shape and text beside each other in excel cell in excel formula. Here are some of them:
- You can use the WordArt feature instead of the text box. WordArt allows you to create decorative text effects with various styles, colors, and effects. To insert a WordArt object, go to the Insert tab, under the Text group, and click on the WordArt button. Then, choose one of the styles, and type the text you want. You can link the WordArt object to a cell using the same method as the text box.
- You can use the Conditional Formatting feature instead of the shape. Conditional Formatting allows you to apply different formats to cells based on certain criteria or rules. For example, you can use the Icon Sets option to add icons such as circles, arrows, or stars to cells based on their values. To apply conditional formatting, select the cells that you want to format, go to the Home tab, under the Styles group, and click on the Conditional Formatting button. Then, choose one of the options, and set the rules as you like. You can link the conditional formatting to a cell using the same method as the text box or the WordArt.
- You can use the CHAR function instead of the shape or the conditional formatting. The CHAR function returns a character based on a numeric code. For example, the code 149 returns a bullet symbol (•). You can use the CHAR function to add symbols to cells, and then concatenate them with the text using the & operator. For example, if you want to add a bullet symbol and the grade beside each name, you can use the formula:
=CHAR(149) & " " & C2
This will display • B in the cell.
