Today, in Episode #1322 of the Learn Excel from MrExcel Podcast, Bill shows us how to have dynamic text within a shape. Use a formula and the Shape tool in Excel 2010 to produce a dynamic visual presentation of your data!
From Chapter 11
From Chapter 11
Transcript of the video:
Hey Welcome back to MrExcel podcast, I'm Bill Jelen.
Excel Charts Chapter 11; Shape Formula.
Hey, all right welcome back to the MrExcel. I'm Bill Jelen.
Well, I have a really simple Spreadsheet today.
We have a goal of 1.5 million, we're currently at 1234567.
And I'm gonna build a little motivating formula here, so ="we are at " and then & synonym, 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% and then I close finished off a sentence. So another concatenation character and then in quotes space so we're at, some percent of our goal.
Close the quotes.
Capitalize this I'll go back and edit that.
So we're at 82.3% of our goal, how's that?
does that motivate you? yeah I mean either.
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 banner shape nice and long, the whole nice long sentence.
We draw a banner there.
Let's choose a better color than that one.
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 B for and press Enter and now you see that the value is calculated in B4 shows up on our banner.
All right, now a couple things we can do here.
We can make that lot larger and also vertical Align: Center, Horizontal Align: Center.
Maybe we can choose a different Font like a Headings Font, that one.
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 very nice way to add a headline and that headline has text that's dynamically calculated by Excel.
Now the one thing 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 wanted never stop and 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. I'm Bill Jelen.
Well, I have a really simple Spreadsheet today.
We have a goal of 1.5 million, we're currently at 1234567.
And I'm gonna build a little motivating formula here, so ="we are at " and then & synonym, 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% and then I close finished off a sentence. So another concatenation character and then in quotes space so we're at, some percent of our goal.
Close the quotes.
Capitalize this I'll go back and edit that.
So we're at 82.3% of our goal, how's that?
does that motivate you? yeah I mean either.
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 banner shape nice and long, the whole nice long sentence.
We draw a banner there.
Let's choose a better color than that one.
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 B for and press Enter and now you see that the value is calculated in B4 shows up on our banner.
All right, now a couple things we can do here.
We can make that lot larger and also vertical Align: Center, Horizontal Align: Center.
Maybe we can choose a different Font like a Headings Font, that one.
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 very nice way to add a headline and that headline has text that's dynamically calculated by Excel.
Now the one thing 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 wanted never stop and I will see you next time for another netcast from MrExcel.
