Excel Tips n Tricks: Tip 6 (Excel 2013 - Dynamic Value in Any Shape)

Sunny Sharma
11y
9.7k
0
0

Article

Hello! Hope you're doing well. Here I have another trick that you will love to learn and practice.

I think we all know how to add text in a shape or in a Smart Shape, but would it be nice if the text inside a shape could be dynamically changed reflecting a value in a cell in your worksheet? Want to learn how? Follow on..

Add any shape of your choice as you always do. Refer to the picture below created in Excel 2013 if you are unfamiliar with doing that.

Procedure to add a shape in worksheet

Go to "Insert" > "Shapes" then select a shape of your choice.

Picture 1

Your cursor will change to in a select area handle. Click and drag your pointer to draw the shape. Now you have a shape added in your worksheet.

Picture 2

You can see that "Format" Tab (menu) is automatically activated as soon as you draw he shape. Format the shape in any way you want.

Pick a cell (any cell) and enter a value, for example a name "Manish". I use cell "H2" for the demo.

To add text, right-click on the shape and select "Edit Text"

Picture 3

Here comes the trick now

Do not type directly inside the shape (like we always do), any text entered this way will be static.

Instead, click inside the Formula Bar and enter a formula that points to the cell with the desired value, for example "=H2" and hit Enter.

(Refer to the picture below):

Picture 4

Voila! You're done. You have added the text that will change dynamically depending on the value in cell H2. This way you can assign a value from another sheet even from another workbook and the shape will reflect its value dynamically each time the value in the cell changes.

Remember that you cannot assign a formula directly as text in a shape, you can only point it to a range reference or a defined name.

A sample is attached with this article. Try changing the cell value and observe the text changing accordingly.

Hope you enjoyed learning this trick :)

Feedback and comments are highly appreciated. Follow for more!!