Excel tips: How to use Text Boxes

Intro:
Hello, and welcome to the CPA Australia podcast, your weekly source for accounting, education, and career and leadership discussion.

Neale Blackwood CPA:
In the September article for the, In the Black, magazine. I covered text boxes. Now text boxes are an old feature in Excel. So I thought I'd go a little bit retro after I've been doing all of the brand new features in Excel. So text boxes have been around for a long time. They have the advantage that they can be placed anywhere on the sheet. So they're not limited by the grid lines underneath. Now, you can enter pretty much any text you like in a text box and you can format individual words. So it's like a mini text editor within the text box. There is also a feature that allows you to create dynamic text boxes. So these are text boxes, that the text within them automatically updates based on a cell. And so you can link a text box to a cell.

Neale Blackwood CPA:
The downside is the formatting options are no longer available. You can only apply a format to the whole text box, when you link it to a cell. To create text boxes, you can go to the insert ribbon. So on the right hand side, there is the text box icon. If you right, click it, you can add it to your quick access toolbar. So it's always available. The keyboard shortcut to insert the default text box and you press this in sequence, the alt key, then the N and then the X, and that will put a default size text box in the middle of your sheet. And then you can resize it and move it around. Now, once you've created the text box, you can select it and then click in the formula bar and press the equal sign, and then click on the cell that you want a link to and then press enter.

Neale Blackwood CPA:
And that will then the text will appear automatically in the text box. And any changes you make to the cell will appear in the text box. In terms of resizing, there's a few ways to do it. If you hold the alt key down, when you are doing the resizing, it will snap to the grid underneath. So basically, it will line up with the grid lines underneath. So that's both the vertical and the horizontal lines. So just hold the alt key down when you resize and you'll see how easy it is to line up with the grid underneath. The other thing you can do in, especially if you want to do it to multiple text boxes is to hold the control key down and select the individual text boxes. And up the top of the screen, you'll notice a brand new ribbon will appear. And in that ribbon, you have the ability to format all of the shapes at once.

Neale Blackwood CPA:
And on the far right hand side of that ribbon, there is an option for height and width. And so you can actually type in numbers there. And there's also up and down buttons as well. If you want to just increase it a slight amount, you can use that. And that allows you to basically get all of the text boxes to be exactly the same size. Which, in a lot of cases is what you're after. In terms of selecting multiple text boxes, you just hold the control key down and then use the mouse to select individual text boxes, this groups them together. So in that same ribbon, on the right hand side, there's a group dropdown. So click that and click group. And then what that does, it creates a single object. If you like of all of the grouped text boxes. And then again, you can move that around and get that positioned exactly as you need.

Neale Blackwood CPA:
Now in the article, I wanted to have a tick and a cross displayed in the cell as well. So in the cell to do that, that's reasonably straightforward. And I demonstrated that in the companion video, that's on the website. And I just used a conditional format to insert a tick and across next to the variance cell, but that doesn't then transfer across to the text box. So what I had to do was use a little known feature of Excel. Now this is pretty secret, hardly anyone knows about this feature, it's called a linked picture. And what you do is you copy a cell. So you copy the cell that you want a link to. You can actually select a range as well, but in this case, we're just going to use a cell. So you select the cell that you want, then you copy it as normal control C.

Neale Blackwood CPA:
Now a lot of people don't realise there's a dropdown button on the paste icon. So the paste icon on the home ribbon, the far left hand side, it's one of the biggest icons on there. It's got a dropdown. So click the dropdown and the bottom right hand corner icon is, called linked picture. So click that and what will happen is, it'll paste an image on top of whatever you've copied, unless you've selected another cell. And so that image, you can move around anywhere and the image reflects what's in the cell. So if you've inserted a green tick, then that will be visible inside that little image. And if you change something. So in their example, I was changing whether we're comparing to budget or forecast and last year. And so the variance will change in terms of whether it positive or negative. And so that automatically updates the cross and the tick, and that flows through to this linked picture.

Neale Blackwood CPA:
Now the downside, the linked picture can slow your model down. So one linked picture probably won't, but if you have a lot of linked pictures, it can affect the speed of your model. So use them sparingly. Now also the linked picture is not quite as detailed as the original. So in the companion video, you can really see that. So the linked picture is a little bit more pixelated, okay. So it's not as sharp as the original cell in most cases, that's okay, because you're not trying to make things bigger. It's usually on a dashboard that this can be useful. So the beauty is you have this linked picture to a cell and you can place that anywhere that you want. So your not limited by the grid and that's demonstrated in the companion video as well.

Neale Blackwood CPA:
Now, a copying tip. So when you try and copy a text box to another sheet, if it's a linked text box, you may not get the effect you're after. So if you just create a text box, as I've mentioned it so far, so you create the text box, click in the formula bar and then click on a cell that creates a link to let's say A1. Now the problem is, when you copy the text box to another sheet, it's going to link to A1 in that sheet. Now, if that's what you want great, but normally it's not. So what you have to do, and you can use a little bit of a hack to do this. So create the text box as per normal, then click on it and select it, and then click in the formula bar. However, what you need to do is click on another sheet, okay, can be any sheet, and then click back on the sheet you're in and then click on the cell that you need to link to.

Neale Blackwood CPA:
And you'll find in the formula bar that the sheet name has been added at the front of the reference, and that's what you need. So you can hit enter there. And now that text box is ready to be copied pretty much to any sheet in the file. So you need that name at the front of reference. It's much easier to use this little hack than it is to type it. If you have spaces in your sheet name, then you got to have apostrophes around the name and things like that. So much easier just to get Excel, to do all the work for you. It's an easy hack. You just click another sheet and then click back in your current sheet, select the cell and hit enter. And you're done. Now, as well as text boxes. There are also other images and shapes that you can add in and you can link those to cells.

Neale Blackwood CPA:
So there're the shapes and there's also icons. Now in the latest version of Excel, you get an icon dropdown in the insert ribbon, and the shapes are quite useful. If you want something a little bit different then you got the shapes there, they've got rounded corners and things like that. If you want to use those, there's also a flow chart section at the bottom of the shapes drop down. So if you wanted to document a process and have the names and things linked to cell entries, then you can use the flow chart shapes and link those back to a cell as well. If you do want to document a process and you want to link to cell references. Okay so, as I said, text boxes are a really old feature. The linked picture is a secret in Excel. Not many people know about that. So feel free to share the secret, but just remember the downside is it can slow down your file. So use it sparingly. Okay, thanks for listening. I hope you found that useful.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at, www.CPAaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple podcasts, Spotify, or Stitcher.