Hi, this is Wayne again with a topic “Copying Excel Formats: Format Painter & More”.
In this article, we’re going to focus on how to copy excel formats in excel, using the format, painter and also other methods. So let’s take a look at this spreadsheet here, a spreadsheet of student test results. Let’S say i decide to spruce this up a little bit. Make it look a little better by applying some formats? Maybe i want the background color of this cell to be a dark blue.
Maybe i want the text to be white. I think that looks pretty good. So what if i now want to copy that format? To other cells in my excel spreadsheet, well, one way to do that would be to click on the cell that i want to emulate and then hold ctrl and tap c to copy the cell, and then i can move over where i want to paste the same Excel format – let’s say here in j1 and then i would go here to the home, tab, clipboard ribbon and there’s an option here for paste. If i go to the bottom part of that paste button, there’s an arrow and there’s an option to paste special and now i can choose formats. I only want to paste the formats.
I don’t want to paste the values or the formulas or anything else. Just the formats, i click, ok and it still says top scores, it doesn’t say student name, but the same exact format from d1 has been applied to j1. Now, instead of using the tab and ribbon, i could have simply held the ctrl key held the alt key and tapped v control. Alt v gives you that same paste, special dialog box that pops up, and so i could have selected formats that way and gotten the same results.
Another option we have for copying excel formats is to use the format painter here it is, it looks like a paintbrush. You can find it on the home tab home ribbon in the clipboard group and the way this works is kind of confusing at first for people, but basically you click on the cell or range that has the formatting that you want. Then you click the format painter and it seems like nothing happened, but look at my mouse pointer. It now has a plus sign next to a paintbrush that tells me that i’m in format, painter mode and i can click on anything in excel to apply that same format.
I’M going to undo that now. Let me do the same thing, but this time by double clicking on the format painter now i can do the same thing, but not just once but multiple times anything that i click now will have the same exact format as is in d1. So that’s a nice quick way to apply a format to multiple cells in excel. I could even click and drag a whole range to apply that same formatting to the entire range i’ll undo, that now that format painter also works well with copying the formatting of an entire range.
So, for example, with this range here – let’s say i want to make it so that all the borders are visible and bolded. I want first and second and third place to be bolded as well, and maybe i want to add a different color here in column. I so that’s my formatting for this range of data here, but what, if i want to apply all of that formatting to this range below it seems like that, would take multiple steps. Even if i’m using the format painter, i would have to format paint each cell.
The way i want it to be right, no, actually, no, if i click and drag and select the entire range, i can then go to the format, painter click and then click and drag the range. I want to apply that same format to and i release the mouse button and that formatting is copied exactly to the second range. I could double click between columns, j and k so that you can see that a little better same thing between i and j. So this format painter is a very powerful way to copy excel formats, whether you’re just simply copying one cell or an entire range of formatting.
It works great. Let’S look at another way to copy excel formats. Let’S say i want to set up my own banded rows in this range now there are faster, easier ways to do this, but just as an example, let’s say i would like this row to be light blue this row to be white, this row to be light. Blue again, well, i’ve set up cell d3 to be light blue.
If i want to copy the formatting of this cell across to the right, what i can do is i could right click on the autofill handle. Many of you know that the autofill handle this little green square in the lower right corner of this cell, that i’ve selected that enables you to click and drag to either copy or extend the contents of a cell. In this case, it’s copying betty scruggs across to each of those cells that i dragged the autofill handle over i’m going to undo that. So, that’s if you left click what if you right, click on the autofill handle, you can click and drag across release, and it gives you options to choose how you want to handle that autofill.
I would like to fill formatting only, so it did not change the data itself, but instead it copied the formatting across, like i said before, there are easier ways to do that, but now that you know that you can right. Click on the autofill handle to extend formatting. I know that you’ll see opportunities when that would be a great way to copy excel formats, so those are a few different ways to copy excel formats from one cell or column or range to another cell column or range thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you do subscribe, click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account or by buying channel merch, and you can learn more about those options below this video .