Rather than static text in the AutoShape, use a formula to add dynamic text to an AutoShape. Episode 370 shows you how to create a shape that shows off the total of all sales made today.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
OK, now yesterday I showed you how to take an AutoShape, and we added basic text to the AutoShape which is cool, but not as interesting as if we could put a formula in the AutoShape.
We can't necessarily calculate in the AutoShape, but I'll show you a good workaround.
I have a worksheet here, where we show how much has been collected or sold throughout the day, and then I created a hidden formula up here at the top.
That way I actually format the way that I want the text to be.
Couple of things about this formula, first of all, I'm using the concatenation character, the &, to join text with a value.
And also you'll see here that I used the CHAR(10) function, to put a carriage return in, that's how I put text on one line and then another line.
I'm finally using the TEXT function to format the total of my sales with a $, and I basically get something that looks OK there in cell D2.
My trick though, is I'm going to click inside the AutoShape, and then click in the formula bar and say =D2, hit Enter, and Excel then puts the result of the formula into my text box.
Now we have to use all the tricks that we used before, change the font size to something larger, maybe making it a different color in order to make it more interesting.
And then finally, drag the AutoShape and hide that temporary formula, so that way it's basically covered up.
Now, as we get more sales in, let's say that we get sales, the banner automatically updates to show the new value.
Very cool way, again, to add visual interest to your spreadsheet, and this time in a dynamic matter, that will show how we're progressing towards a goal.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
OK, now yesterday I showed you how to take an AutoShape, and we added basic text to the AutoShape which is cool, but not as interesting as if we could put a formula in the AutoShape.
We can't necessarily calculate in the AutoShape, but I'll show you a good workaround.
I have a worksheet here, where we show how much has been collected or sold throughout the day, and then I created a hidden formula up here at the top.
That way I actually format the way that I want the text to be.
Couple of things about this formula, first of all, I'm using the concatenation character, the &, to join text with a value.
And also you'll see here that I used the CHAR(10) function, to put a carriage return in, that's how I put text on one line and then another line.
I'm finally using the TEXT function to format the total of my sales with a $, and I basically get something that looks OK there in cell D2.
My trick though, is I'm going to click inside the AutoShape, and then click in the formula bar and say =D2, hit Enter, and Excel then puts the result of the formula into my text box.
Now we have to use all the tricks that we used before, change the font size to something larger, maybe making it a different color in order to make it more interesting.
And then finally, drag the AutoShape and hide that temporary formula, so that way it's basically covered up.
Now, as we get more sales in, let's say that we get sales, the banner automatically updates to show the new value.
Very cool way, again, to add visual interest to your spreadsheet, and this time in a dynamic matter, that will show how we're progressing towards a goal.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
