Microsoft Excel Tutorial: Can you mix formatting in an Excel cell that contains a formula?
How to Work Around Excel's Formatting Limitations
Ever wondered if you could apply different formatting to parts of a cell populated by a formula in Excel? In today’s video, we dive into this intriguing question posed by Paul, a viewer who couldn’t find a solution to this common Excel conundrum. While Excel doesn’t allow partial formatting of a formula’s result, I’ve discovered a clever workaround using the **Paste Linked Picture** feature. Watch as I demonstrate this creative hack step-by-step!
We start with a fundamental Excel tip: editing and formatting individual characters in a cell. From using **F2**, the **Home** key, and **Shift**, to applying bold or changing font colors, these techniques are handy for working with non-formula cells. But what happens when the data comes from a formula? This is where Paul’s question takes center stage—he’s looking to create a report with bold labels and non-bold results, but can’t format the text in a formula-driven cell.
My initial response was to confirm that Excel doesn’t support this feature. But then it struck me: there’s almost always a workaround! By converting the formula’s result to a linked picture, we can achieve the desired look while maintaining the dynamic nature of the data. I’ll show you how to align the linked picture, remove grid lines, and match fill colors for a seamless appearance.
This video isn’t just about solving Paul’s problem—it’s a reminder of the creativity Excel inspires. We explore how tools like linked pictures can go beyond their intended use to overcome Excel’s quirks. Whether you’re handling audit reports or building complex dashboards, this trick can add a polished, professional touch to your work.
Thanks to Paul for sparking this idea and to all of you for watching! If you’ve got a tough Excel question or a formatting challenge, drop it in the comments. Don’t forget to like, subscribe, and hit the bell so you won’t miss the next creative Excel tip. See you next time on MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Welcome
(0:11) Formatting Part of a Cell
(0:47) Question: Format Part of a Cell Coming from an Excel Formula
(1:46) Absolutely no way to do this
(2:20) Solving with a Linked Picture
(3:00) Tweaking linked picture formatting
(4:25) Wrap-up & Members invite
This video answers these search terms:
Excel formatting tricks
How to format part of a cell in Excel
Excel partial formatting workaround
Using linked pictures in Excel
Excel formula cell formatting hack
Creative Excel tips for formatting
Solve Excel formatting limitations
Dynamic formatting in Excel reports
Excel Paste Linked Picture tutorial
Formatting formula results in Excel
How to Work Around Excel's Formatting Limitations
Ever wondered if you could apply different formatting to parts of a cell populated by a formula in Excel? In today’s video, we dive into this intriguing question posed by Paul, a viewer who couldn’t find a solution to this common Excel conundrum. While Excel doesn’t allow partial formatting of a formula’s result, I’ve discovered a clever workaround using the **Paste Linked Picture** feature. Watch as I demonstrate this creative hack step-by-step!
We start with a fundamental Excel tip: editing and formatting individual characters in a cell. From using **F2**, the **Home** key, and **Shift**, to applying bold or changing font colors, these techniques are handy for working with non-formula cells. But what happens when the data comes from a formula? This is where Paul’s question takes center stage—he’s looking to create a report with bold labels and non-bold results, but can’t format the text in a formula-driven cell.
My initial response was to confirm that Excel doesn’t support this feature. But then it struck me: there’s almost always a workaround! By converting the formula’s result to a linked picture, we can achieve the desired look while maintaining the dynamic nature of the data. I’ll show you how to align the linked picture, remove grid lines, and match fill colors for a seamless appearance.
This video isn’t just about solving Paul’s problem—it’s a reminder of the creativity Excel inspires. We explore how tools like linked pictures can go beyond their intended use to overcome Excel’s quirks. Whether you’re handling audit reports or building complex dashboards, this trick can add a polished, professional touch to your work.
Thanks to Paul for sparking this idea and to all of you for watching! If you’ve got a tough Excel question or a formatting challenge, drop it in the comments. Don’t forget to like, subscribe, and hit the bell so you won’t miss the next creative Excel tip. See you next time on MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Welcome
(0:11) Formatting Part of a Cell
(0:47) Question: Format Part of a Cell Coming from an Excel Formula
(1:46) Absolutely no way to do this
(2:20) Solving with a Linked Picture
(3:00) Tweaking linked picture formatting
(4:25) Wrap-up & Members invite
This video answers these search terms:
Excel formatting tricks
How to format part of a cell in Excel
Excel partial formatting workaround
Using linked pictures in Excel
Excel formula cell formatting hack
Creative Excel tips for formatting
Solve Excel formatting limitations
Dynamic formatting in Excel reports
Excel Paste Linked Picture tutorial
Formatting formula results in Excel
Transcript of the video:
Here's another one that could have two names.
How to conquer Excel's formatting quirks with creative hacks.
Or, when “absolutely no way” means “Ah, yeah, there's a way.” Alright, first off, let's cover one quick thing.
You know, if you have a cell that has some words in it, it's possible to edit that cell.
I'm going to press the F2 to edit.
Home key to move the cursor to the beginning.
And then hold on the shift key while I select these characters.
And Control B for bold.
And then the right arrow key again.
And hold on the shift key and select those three characters.
And maybe I can come up here and change the font color of that to red.
So it's possible to mix formatting within a cell.
[ Angela ] See, I wouldn't know how to do that.
[ Bill ] Alright, so here's today's question.
Paul was in one of my webinars recently and he writes into me afterwards.
He says, “Look, I got this one little nitpicky thing that I can't seem to figure out.
“It bothers me.
“I Googled the heck out of it.
“And I think it's just one of those things that's not available.
“Can you confirm or refute?” And Paul goes on to explain that at the top left hand corner, he has this stamp with the information for this audit report.
And there's one cell: “Audit budgeted hours” that changes constantly.
So he wants that to be a formula to pull the answer from B13 into the cell.
But the thing that drives him crazy.
He says “It annoys my OCD personality” is that I can't format the font.
Everything else has a bold label and a non bold answer.
And I need that 72 to be not bold.
Very clever - he tried the TEXT function.
But he can't figure out any Number Format that controls bold or italics.
Now Paul's asking me to confirm or refute that there's no way to do this.
And so I write back and say.
“I'll confirm you are correct.
“There is absolutely no way to format part of a cell if that cell is the result of a formula “It would be nice to do, but they haven't given us a way to do that.” I type that out in the email.
And then a few minutes go by.
And it starts to bug me.
And I realize that every time that I tell someone that “there's absolutely no way to do that in Excel.” Well hey, there might be a way to do that in Excel.
We just have to be a little bit creative.
So instead of a formula here, let's just freeze this.
I'll convert it to values.
So we have “Audit Budgeted Hours:” and I'll get rid of the 72.
And then I come down here to where the answer is.
That's where we want to get the answer from; B13.
And I'm going to copy that cell.
So I copy B13 to the clipboard.
And then I'm going to come back up kind of here-ish.
And at the bottom of the paste dropdown, the very last item is called Paste Linked Picture.
And what I'm getting is I'm getting this little thing that I can drag around.
Alright?
And this thing is a live picture of this cell.
So if something on here were changed.
If that 72 changes to a 74.
You see that it changes there.
Now we just have to be clever here.
We can left align and just kind of perfectly line it up.
Now if we look really close.
I'm going to zoom in here.
You'll see that when we click away.
We're seeing the outline of that cell.
Which is bad.
And we don't want to have the outline of the cell.
It's going to look worse than whatever.
So I need to get rid of grid lines in this section.
One way to get rid of grid lines is to use a fill color.
And I can just use the same fill color that we're using up here.
So that same light orange.
So that gets rid of the grid lines.
And we should have something now that looks like that formula.
I guess the only problem would be if it got too large.
We're still okay.
You just have to worry that this is wide enough that you're never going to get the hash signs because it's not large enough.
And you can kind of nudge this into place.
And although it's not a formula.
It will look like it's a formula.
And the results will have bold back here and not bold there.
So can you format part of a cell if that cell is coming from a formula?
Well, no, you can't.
But using this Linked Picture trick on the Paste dropdown.
You can get something that looks about the same.
And you can control the formatting of the label and the result.
I want to thank Paul for sending that question in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, what's this?
A new JOIN button?
Yeah, there's a membership section.
This'll be special loyalty badges, emojis, members only videos.
These are things that I just, one off videos that I did for a specific people.
Or when I screw something up, there's an outtake and shows you how to recover from the outtake.
Member shout outs.
And a live stream every quarter at least.
Check it out.
Thanks.
How to conquer Excel's formatting quirks with creative hacks.
Or, when “absolutely no way” means “Ah, yeah, there's a way.” Alright, first off, let's cover one quick thing.
You know, if you have a cell that has some words in it, it's possible to edit that cell.
I'm going to press the F2 to edit.
Home key to move the cursor to the beginning.
And then hold on the shift key while I select these characters.
And Control B for bold.
And then the right arrow key again.
And hold on the shift key and select those three characters.
And maybe I can come up here and change the font color of that to red.
So it's possible to mix formatting within a cell.
[ Angela ] See, I wouldn't know how to do that.
[ Bill ] Alright, so here's today's question.
Paul was in one of my webinars recently and he writes into me afterwards.
He says, “Look, I got this one little nitpicky thing that I can't seem to figure out.
“It bothers me.
“I Googled the heck out of it.
“And I think it's just one of those things that's not available.
“Can you confirm or refute?” And Paul goes on to explain that at the top left hand corner, he has this stamp with the information for this audit report.
And there's one cell: “Audit budgeted hours” that changes constantly.
So he wants that to be a formula to pull the answer from B13 into the cell.
But the thing that drives him crazy.
He says “It annoys my OCD personality” is that I can't format the font.
Everything else has a bold label and a non bold answer.
And I need that 72 to be not bold.
Very clever - he tried the TEXT function.
But he can't figure out any Number Format that controls bold or italics.
Now Paul's asking me to confirm or refute that there's no way to do this.
And so I write back and say.
“I'll confirm you are correct.
“There is absolutely no way to format part of a cell if that cell is the result of a formula “It would be nice to do, but they haven't given us a way to do that.” I type that out in the email.
And then a few minutes go by.
And it starts to bug me.
And I realize that every time that I tell someone that “there's absolutely no way to do that in Excel.” Well hey, there might be a way to do that in Excel.
We just have to be a little bit creative.
So instead of a formula here, let's just freeze this.
I'll convert it to values.
So we have “Audit Budgeted Hours:” and I'll get rid of the 72.
And then I come down here to where the answer is.
That's where we want to get the answer from; B13.
And I'm going to copy that cell.
So I copy B13 to the clipboard.
And then I'm going to come back up kind of here-ish.
And at the bottom of the paste dropdown, the very last item is called Paste Linked Picture.
And what I'm getting is I'm getting this little thing that I can drag around.
Alright?
And this thing is a live picture of this cell.
So if something on here were changed.
If that 72 changes to a 74.
You see that it changes there.
Now we just have to be clever here.
We can left align and just kind of perfectly line it up.
Now if we look really close.
I'm going to zoom in here.
You'll see that when we click away.
We're seeing the outline of that cell.
Which is bad.
And we don't want to have the outline of the cell.
It's going to look worse than whatever.
So I need to get rid of grid lines in this section.
One way to get rid of grid lines is to use a fill color.
And I can just use the same fill color that we're using up here.
So that same light orange.
So that gets rid of the grid lines.
And we should have something now that looks like that formula.
I guess the only problem would be if it got too large.
We're still okay.
You just have to worry that this is wide enough that you're never going to get the hash signs because it's not large enough.
And you can kind of nudge this into place.
And although it's not a formula.
It will look like it's a formula.
And the results will have bold back here and not bold there.
So can you format part of a cell if that cell is coming from a formula?
Well, no, you can't.
But using this Linked Picture trick on the Paste dropdown.
You can get something that looks about the same.
And you can control the formatting of the label and the result.
I want to thank Paul for sending that question in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, what's this?
A new JOIN button?
Yeah, there's a membership section.
This'll be special loyalty badges, emojis, members only videos.
These are things that I just, one off videos that I did for a specific people.
Or when I screw something up, there's an outtake and shows you how to recover from the outtake.
Member shout outs.
And a live stream every quarter at least.
Check it out.
Thanks.
