Insert a text box or shape and link a cell's content to it

- [Instructor] On this worksheet are two shapes off to the right. We've got an oval here and also a hexagon and then what appears to be a free floating number and if you look at some of the entries over in columns B and C, you can see that they somehow appear to be related. Now, these entries could have been typed but they're not. If I click the oval, look in the Formula bar. What do we see? Equals C2. Click the hexagon to the right. What do we see? B3. And sure enough as I click this number right here, this free floating 45618, click this, we see in the Formula bar, equal B2.

When you create shapes, you can associate them with a given cell. Now, let's create a shape. Insert tab, that's where these two shapes were created. Shapes is the option here. I'm gonna choose Oval. Now, when you create an oval, it could be wide, it could tall, it could be anything you want but if you want it to be a perfect circle, don't guess at it. Hold down the Shift key. As long as you've got the Shift key held down, you're on the way to creating a circle provided you let go of the mouse first.

Now, I can certainly type anything I want in here. I could type in Sales. I could type in a number, anything I want. Not a problem. If I wanna put in Sales Report, not a bad idea maybe. That's all I need. Click outside of it, it's done. If I come back and click it again, I might wanna consider going to the Home tab and centering this top bottom, the text that is and left to right and while I'm here, maybe make it bold, make it italic. Change the font to be yellow, blue, red, whatever.

Maybe that's gonna look like okay and change the font size even. Something like that. All kinds of things we could do with it. But that's just free floating text. Certainly nothing wrong with that but if we wanna pick up data that's actually in the worksheet, we can do that. So I'm gonna wipe out Sales Report here and let's imagine I've just created it. It's selected. Click in the Formula bar, type Equal and click a cell.

Notice I said cell, singular. I want this to be the sales number for February. I'll click it. Enter. Now, some of you know how to put together text and numbers. What if I tried the following though? I'm gonna click again up here in the Formula bar after that B3. I want this to be combined with some text. Now, if I had clicked in an actual cell, I could be typing right now the ampersand and then double quote and maybe a space, February.

Maybe February Sales. Double quote and I could do that if the active cell were in a particular location but I've got an object selected here, that's circle. What happens when I press Enter? It's just not acceptable, can't do that. So you can only associate an object with a single cell. Now, if I wanted to right here and I probably wouldn't really but if I type right here and I could do this for all the months, I'll just do it for January for the moment, equal this entry here.

That's gonna be the word January. I could combine that by using the ampersand, double quote, space, double quote and I'll put in another ampersand and the data here in B2. So what are we about to see in this cell? That entry. Now, unfortunately, that's not formatted. We'll get to that but I'm gonna click the circle again and this time, change the reference. Instead of going to B3, how about Equal D2. So we'd have to do it that way.

And if we really wanted to clean this up and this is almost beyond the scope of what we're trying to show here, but if we wanted to make this a little bit more readable, what we'd need to do next is embed this reference, this B2 reference, into a function called Text and then this is the text we're using. Actually, we're converting it to the text. Right now it's a number. And then we have to provide a format. Double quote, a couple of pound signs is gonna work here. That means suppress the leading zeroes if the number for example happen to be in the hundreds instead of the thousands.

We wanna make sure that all leading zeroes are suppressed. That's what the pound signs do. So I'd put in a number, something like this. We don't wanna see the pennies. That should be go enough. And a red parentheses there. And as I press Enter, that should be a little bit more readable and that circle down below should reflect that same entry. So we see what's happening down here. So we can associate any shape we draw that has text with a cell reference. Now, also in the Insert tab, you'll see Text Box and much of the time, this is the same general idea.

When you click and drag a text box, that's one approach but going back here again, I could go back to Insert, Text Box. I could just click somewhere. But in either case, I can start typing. So I could start typing right now but what if I want a cell reference? As before, I'll simply click in the Formula bar in the same way as if this were a shape, type Equal. This time, I'll just click the March entry in B4 and Enter. Now, that's a text box even though we don't see a box.

Over here, I created a text box. I clicked and dragged and saw it. Here too. I could type up in the Formula bar, Equal. I'll associate that with April and Enter. So the difference here is this is a text box where we see the box. Here's a text box where we don't. Now, I could come back here. With this being selected, go to the Format tab in the ribbon and then under Shape Outline for the box, how about No Outline? And for the moment, we still see it but then come back to Shape Fill and No Fill.

Now, clicking outside of it, this is the same as what we see over here. So there are all kinds of reasons for wanting to show at different times the content of a cell and again, it's trickier as we saw in the example here. When you're trying to pick up information from multiple cells, you essentially have to create a formula on your own outside of the source data and then use that reference as we did here. We associated this object right here, this circle, with cell D2 and you can see it in the Formula bar.

And so we've seen different techniques here for associating shapes with content and as you would expect on all these examples too, everything is dynamic so for example, this 45618 should have been 918 and as soon as I change this, any reference to that cell of course reacts immediately. See, it happened here. It's also happening on the circle.