Link cell contents to a shape - Microsoft Excel Tutorial

- [Instructor] Creating links to data in other cells is a great way to create summaries on a dashboard. However, you can also create links between cells and images or shapes. Doing so gives you control over how the information appears in your dashboard, and also lets you move it on the dashboard without having to update your formulas that appear in the worksheet cells. In this movie, I will show you how to create a link between a data cell and a shape. My sample file is 02_02_LinkToShape, and you can find that in the chapter two folder of the exercise files collection. I have two worksheets in this workbook, and I've started building a Dashboard. So you can see, I have revenue for gift basket cell in B3, and also warehouse expenses in cell B6. And then on the SupportTables worksheet, and I'll click this tab now. You see that I have gift basket revenue, and it's broken down by month for the year 2021. And my total is in cell E16. My goal is to create a shape that has my daily average sales for gift baskets. So I'm going to divide the number in E16 by 365, which is the number of days in the year 2021. However, you probably also noted that in cell G1, I have a label that say daily average sales. So to look ahead, the formula that I'm going to create at first will generate an error, but then we'll be able to fix it by adding a formula in cell G2. So let me show you what goes wrong, and then I'll show you how to do it correctly. So I'll go back to my dashboard. And I want to add a shape, so I'll go to the insert tab, and then I'll click illustrations. And this group will probably be expanded on your computer, but on my monitor it's not. So illustrations, I'll click shapes, and I will use just a standard rectangle, which I will draw on the body of the worksheet. I'll change its formatting by going to shape styles, click the more button, and I'll select the basic format at the top left. Now with the shape still selected, I will type = and I can start creating a formula. So I'll go to SupportTables, and the cell that I want to use is E16. And I will divide that value by 365, number of days in the year, press enter, and there is my error. This formula is missing a range reference or a defined name. And nothing to do but click OK, so I will. And the reason we got this error is because you can't refer to a table with this kind of formula. So instead, what I need to do first is we press escape, and that gets rid of the formula being edited. And I will go back to the SupportTables worksheet, and I'll scroll up to G2 type in =. And now I will click the total cell that I had before, and divide by 365, again as I did before. So now and sell G2, I have the value $185.48. And that will appear in the shape once I link to cell G2. So go back to the Dashboard worksheet, ensure that the shape is selected. Click in the formula bar, type =. Go to SupportTables, click G2, enter. And there is my value. Now I can format the shape. So for example, I currently have the text at the top left, and I can change both of those elements to center them. So I'll go to the home tab of the ribbon, again, with the shape selected. And I'll click middle align, and that is the vertical alignment, and then center, which centers the value within the shape. I'll make it bold so it's easier to read, and then I will resize the shape so that it takes up as little room as possible. And obviously, if you decrease the font size, you can make the shape even smaller. I'd also like to add a label so I'm going to create a new shape. So I'll just click away for a moment. Insert, illustrations, shapes, and I'll create another rectangle. And I will draw it above the shape I created before. And I'll click a style to use, in this case, because I have black and white, I'll do a black background with white text. And then with the shape still selected, I will type AVG Daily Revenue, and I'll need to resize the shapes so the text appears, and I'll drag it over to the side. I'm trying to make the text small enough so that it all fits on one line. So I'll click home and there we go. I've decreased the size of the text enough. And again, I will center and align middle. Move up and then I will leave the label where it is, and I will control click the shape below it to select both. Then on the shape format contextual tab, I'm going to do two things. The first is I'm going to align them center so that their middles are in the exact same place. Oh, I already gotten it pretty close. So we have a little bit of movement there, but not much. And I will also group them so that I can move them together. So I'll click group and then click group again. And you can see that there's a single outline. And when I drag them, I'm not dragging them separately, I'm dragging them together so I can move that item anywhere I want on my Dashboard. And if I want to edit the text, I can click inside of that shape and edit it normally. And when I'm done, just click away.