A second solution to Don's problem from podcast 991: add a textbox inside the cell. With a bit of formatting, the words inside the textbox will appear to float next to the number, but will not cause any subsequent formulas to incorporate the text. Episode 992 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Yeah, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Like yesterday, I talked about my former co-worker Don, who wanted to put words, next to text and I came up with what I thought was the brilliant way to go of using the custom number format to get the word out there.
The important thing was that Don did not want adding that word, next to text, to cause the formulas to change.
He still wanted the number to appear in the formula and you know as I said yesterday, I could hear Don's voice.
He wasn't happy with this whole custom number format.
It was just a little bit tough to do.
So, came up with this other idea which Don liked a lot more.
And I'm going to increase the height of all these cells, and then we're going to use something on the insert tab.
We're going to use a text box.
Now, the text box used to be down on the drawing toolbar in Excel 2003.
Funny is now, here twice.
It's in the shapes as the very first one, they added that right before they went to market and that it's also over here and the huge screaming icon.
But for those of you who don't see it over here.
You can always use the other one.
So, I'm going to add a text box.
I get it.
Okay! This one over here.
This big screaming one.
That's the one that doesn't work.
Let's come back to shapes and use this one.
There we go.
Isn't that weird? I have no idea. What was up with that.
I'm going to draw a text box in here, and of course type a word like approved and now we're going to try and format this text box.
So, we have the text box, press [ ctrl + 1 ] and a couple of things, line color, we'll say no lines.
That way, there's no line around the text box and Text Box, vertical alignment, top text direction, horizontal. That's great.
I'll take the Margins down to as little as we can and looking for font.
Funny, don't see it or if we just changed up here in the home tab.
Now, you have a nice small Text box.
Now, that the words are small, I can resize the text box and let's see how it looks when we click away.
There we go, the word appears and you can't really tell that it is you know, not part of the cell.
Now, to copy that word if you would need to do so, we're going to [ ctrl ] drag and we can make copies of the words.
So, you still have to do a little bit of adjustment there, to try and get them in the same spot in the cell but in general it's good now.
The real test here is, are those text boxes going to sort with the data.
So, I'll add a heading here and sales here.
Make those bold and now try and sort this ascending or descending.
Sort and filter.
Small as the largest and the words did change, that's good.
That's the default behavior most objects move and size with cells.
For me this is a bit more tedious to go through and add the text box get rid of the line yada! Yada! Yada!
I would have preferred the custom number format that we used yesterday.
But whichever method works for you, have it.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Yeah, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Like yesterday, I talked about my former co-worker Don, who wanted to put words, next to text and I came up with what I thought was the brilliant way to go of using the custom number format to get the word out there.
The important thing was that Don did not want adding that word, next to text, to cause the formulas to change.
He still wanted the number to appear in the formula and you know as I said yesterday, I could hear Don's voice.
He wasn't happy with this whole custom number format.
It was just a little bit tough to do.
So, came up with this other idea which Don liked a lot more.
And I'm going to increase the height of all these cells, and then we're going to use something on the insert tab.
We're going to use a text box.
Now, the text box used to be down on the drawing toolbar in Excel 2003.
Funny is now, here twice.
It's in the shapes as the very first one, they added that right before they went to market and that it's also over here and the huge screaming icon.
But for those of you who don't see it over here.
You can always use the other one.
So, I'm going to add a text box.
I get it.
Okay! This one over here.
This big screaming one.
That's the one that doesn't work.
Let's come back to shapes and use this one.
There we go.
Isn't that weird? I have no idea. What was up with that.
I'm going to draw a text box in here, and of course type a word like approved and now we're going to try and format this text box.
So, we have the text box, press [ ctrl + 1 ] and a couple of things, line color, we'll say no lines.
That way, there's no line around the text box and Text Box, vertical alignment, top text direction, horizontal. That's great.
I'll take the Margins down to as little as we can and looking for font.
Funny, don't see it or if we just changed up here in the home tab.
Now, you have a nice small Text box.
Now, that the words are small, I can resize the text box and let's see how it looks when we click away.
There we go, the word appears and you can't really tell that it is you know, not part of the cell.
Now, to copy that word if you would need to do so, we're going to [ ctrl ] drag and we can make copies of the words.
So, you still have to do a little bit of adjustment there, to try and get them in the same spot in the cell but in general it's good now.
The real test here is, are those text boxes going to sort with the data.
So, I'll add a heading here and sales here.
Make those bold and now try and sort this ascending or descending.
Sort and filter.
Small as the largest and the words did change, that's good.
That's the default behavior most objects move and size with cells.
For me this is a bit more tedious to go through and add the text box get rid of the line yada! Yada! Yada!
I would have preferred the custom number format that we used yesterday.
But whichever method works for you, have it.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
