Hi, this is Wayne again with a topic “The Excel Format Painter – Basics and Beyond”.
This video is all about the excel format: painter we’ll look at the basics and beyond so here i have a spreadsheet that has some formatting problems. As you can see, we have a mixture of text formats. We’Ve got different, colors different font types and problems like this. Often will come up when you’re copying and pasting from one document to another or from an online source and into your document, etc.
So how can i go about fixing this? Obviously, i could select one of the problem areas and manually change. The font size maybe change the font type if i need to, but that’s going to take forever. So this is where the format painter comes in.
What i can do is i can just decide what is a good example of the kind of text formatting that i want in my spreadsheet. So, for example, this first person’s name, that’s good formatting. I think for this document, so i just select that cell. That is a good example of the formatting.
I want then, on the home tab home ribbon here in the clipboard group. I can just click on this paint brush. That’S the format painter once i’ve done that whenever i click on next will be formatted just like that cell. So i’m going to choose this one here. I click and it has changed the formatting for that cell to match the formatting here for gina pullen. Now one of the nice things about the format painter is that you don’t have to do it one cell at a time. So this time, i’m just going to choose cell d3 and i’ll click, the format, painter and then i’ll click and drag to highlight the range that has all of the problems and then i’ll release the mouse button and notice that everything now has been formatted in a Similar way to the cell that i had selected there’s a couple of potential issues here right. You probably noticed that these dates have been converted into just numbers like they are in column d, but i can fix that.
I can click on one of these other higher dates, click, the format, painter and then click and drag down the spreadsheet, and now those are restored to being dates. The other problem i see is that this border, when i clicked and dragged all of this data down the spreadsheet, it removed the borders on the left and the right, and also at the bottom of my data. Again, that’s easy to fix. I can just click here, for example, on cell b4, click the format, painter button and then click and drag on the data that i want to restore that border to, and then i could do the same thing on the right side as well, and it’s that simple And, of course, i could just use the borders button to add that again. Also, let’s look now at a couple of the advanced features of the excel format. Painter, as you can see, this name heading is bolded, but the other headings are not, and let’s say i want to apply a background color to that cell, and maybe i want to change the font color to be white. How could i quickly make those changes on these other cells? Again, i would select the cell that has the formatting that i want to copy. Then i’d click on the format painter and i could click and drag here to highlight that entire header row, but another way to do the same thing would be instead of clicking once double clicking. So if i double click on that format, painter now, i can click. If i want to, i can release the mouse button, click again, click again and i could even just keep clicking and add that formatting to the rest of my spreadsheet.
That doesn’t make sense. In this case, i could even do the same thing on another spreadsheet. As long as i have clicked on the cell, that has the formatting i want and i double click on that format. Painter and it’s still selected. I can click on location, two notice that it’s still selected here and then i could click or click and drag and just continue, adding that same formatting, where i want it to be. When i’m done with the format painter, i can tap escape on the keyboard or i can double click on it again. Either way it will remove the format painter from my mouse pointer. So in this spreadsheet. Maybe i want to draw attention to the management at this particular location of a company. I could click and drag to select one of the managers and then i could change the formatting to how i want it to be. Maybe the background color changes. Maybe i want it bolded, i could change the font color, so i could select one of these cells.
Let’S say this one here and i could double click to get the format painter to stay on and then i could find another member of management and then i could just click. Click, click click. So that’s one way to copy paste that formatting, but again it messed up my dates. So let’s look at an even better method. What i could do is click and drag a whole range of cells and then double-click the format painter and now it will format whatever.
I click and drag to select exactly in the same way as the original range, so notice that my date stayed the same and so in a lot of cases clicking and dragging all the way across a row or a column to select all of the various formatting. In a range and then double clicking on the format, painter and then clicking and dragging to copy the original formatting for the range to a new area in a lot of cases, that will be the best way to duplicate the formatting. I’M going to undo that because i want you to see that this works not only with a row or a column, but you could select all of your data in a spreadsheet and copy it to another spreadsheet. So, as you can see down here, i have this spreadsheet. We’Ve been working in with location 1, but i also have location 2 and that spreadsheet has all sorts of problems, and then i also have location three. So i want to format all three of these locations in the exact same way.
To do that, i would just click and drag to highlight all of the data in my spreadsheet. A quick way to do that is just to click inside the data and then hold ctrl and tap a with all of that selected. I will double click on the format, painter and then i’ll, go down here to location, 2 and click and drag all the way down, release the mouse button and now location 2 is formatted in exactly the same way as location 1.. Now i can click on location 3 and do the same thing: release the mouse button notice that i still have the format painter selected. So if i click anywhere else, it’s going to try to copy the same formatting. So i really need to tap escape to release the format painter. So now, all three of my spreadsheets are formatted in exactly the same way. The format painter really is a wonderful, powerful tool to help you make your spreadsheet look just the way you want it to look and to have it be consistent within a spreadsheet and across an entire workbook thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe, and when you do 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 by clicking the thanks button below the video or you could support me on my patreon account or by buying channel merch and you’ll, see more information about those options. In the description below the video .