Excel Charts 11 - Shape Formula

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 16, 2010.
Add a formula to an Excel Shape.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top