Add a formula to an Excel Shape.
Transcript of the video:
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Excel Charts Chapter 11; Shape Formula.
Hey! All right, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, I have a really simple Spreadsheet today.
We have a goal of 1.5 million, we're currently at 1,234,567.
And I'm gonna build a little motivating formula here, so, equal "We are at..." space, close the quotes and then an ampersand, I'm gonna use the text function here to do a little calculation.
Text B2 divided by B1 and then I'm gonna format that as a percentage, so 0.0%.
All right, and then I'm gonna close, finish off this sentence. So, another concatenation character and then in quotes, space, so we're at, some percent of our goal. Close the quotes.
I want to capitalize this, so I'll go back and edit that.
Here we go, so we're at 82.3% of our goal, how's that? Does that motivate you? Yeah, me neither.
All right, so let's see if we can really dress this up, a little bit.
We'll go to Insert, where we have the Shapes here.
I'm going to choose a interesting shape.
I'll go with this, maybe the banner shape, nice and long, the whole nice long sentence.
We'll draw a banner there and let's choose a better color than that one.
I'll go with Red. All right, now the formula, where the information for the banner is going to come from, is in cell B4.
So I'm going to click, in the Formula Bar, type equal B4 and press Enter and now you see that the value that's calculated in B4 shows up on our banner.
All right, now a couple things we can do here.
We can make that quite larger and also vertical Align: Center, Horizontal Align: Center.
Maybe we can choose a different Fonts like a Headline Font, I'll go with that one.
All right, so there we have it, at this point now, we can kind of move that to be the center of attention and then every day when we come in and update the new number...
the banner will automatically update, all right.
So a very nice way to add a headline and that headline has text that's dynamically calculated by Excel.
Now, the one thing you have to be really careful about here, and I've tried this before is, I click on the text box and I try and build a formula, you know, where I actually do the concatenation and the calculation in that and that will not work.
Now this has to be pointing to a single cell, that cell has to be the one to be calculated actually...
Hey, I want to thank you for stopping by. I will see you next time for another netcast from MrExcel.
Excel Charts Chapter 11; Shape Formula.
Hey! All right, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, I have a really simple Spreadsheet today.
We have a goal of 1.5 million, we're currently at 1,234,567.
And I'm gonna build a little motivating formula here, so, equal "We are at..." space, close the quotes and then an ampersand, I'm gonna use the text function here to do a little calculation.
Text B2 divided by B1 and then I'm gonna format that as a percentage, so 0.0%.
All right, and then I'm gonna close, finish off this sentence. So, another concatenation character and then in quotes, space, so we're at, some percent of our goal. Close the quotes.
I want to capitalize this, so I'll go back and edit that.
Here we go, so we're at 82.3% of our goal, how's that? Does that motivate you? Yeah, me neither.
All right, so let's see if we can really dress this up, a little bit.
We'll go to Insert, where we have the Shapes here.
I'm going to choose a interesting shape.
I'll go with this, maybe the banner shape, nice and long, the whole nice long sentence.
We'll draw a banner there and let's choose a better color than that one.
I'll go with Red. All right, now the formula, where the information for the banner is going to come from, is in cell B4.
So I'm going to click, in the Formula Bar, type equal B4 and press Enter and now you see that the value that's calculated in B4 shows up on our banner.
All right, now a couple things we can do here.
We can make that quite larger and also vertical Align: Center, Horizontal Align: Center.
Maybe we can choose a different Fonts like a Headline Font, I'll go with that one.
All right, so there we have it, at this point now, we can kind of move that to be the center of attention and then every day when we come in and update the new number...
the banner will automatically update, all right.
So a very nice way to add a headline and that headline has text that's dynamically calculated by Excel.
Now, the one thing you have to be really careful about here, and I've tried this before is, I click on the text box and I try and build a formula, you know, where I actually do the concatenation and the calculation in that and that will not work.
Now this has to be pointing to a single cell, that cell has to be the one to be calculated actually...
Hey, I want to thank you for stopping by. I will see you next time for another netcast from MrExcel.
