In this blog post, you will learn about one of the most useful and underused features of Microsoft Excel which is known as Format Painter.
See the image below:-
What is Excel Format Painter?
Excel Format Painter is used to copy a multi-formatted dataset and apply that formatting into a different dataset.
Suppose that a dataset has multiple formatting like – cell color, font type, font size, cell border, Number format, text alignment, etc.
If we apply these formatting one by one manually, this procedure takes much time.
But Excel Format Painter copy all multi-formatted datasets and apply the same in a single click only.
How to apply Excel Format Painter?
First of all, we must know that which dataset formatting we wish to apply in another dataset.
Select the whole data whether it is a single cell or a multi-cell dataset, see image below:-
In this image, we can see the dataset in column “A” has a multi-formatted dataset but column “C” has no formatting.
And we want to apply the same formatting for both of the datasets.
Here, we need to select the dataset which format we want to apply and here we know that column “A” multi-formatted dataset.
So, select the whole dataset in column “A” and go to the “Home” tab and click on the Format Painter icon.
Once we click on the Format Painter icon, our whole selected dataset has got copied, and see the mouse cursor is carrying a brush icon with it.
Which means this is carrying the copied format of the selected dataset.
Understand all this with the image and steps:-
Step 1:- Select the dataset which format we want to apply to our test dataset.
Step 2:– Click on the “Format Painter” icon under the “Home” tab and the ‘Clipboard’ group. Once click selected dataset formatting has copied now and seen that the mouse is carrying a brush icon with it.
Step 3:- Now go the desired place where you want to apply this formatting, in this case, column “C” is the target dataset.
Go to the desired place and drop the mouse here. You can see that your target dataset formatting has been changed as the previous dataset.
See image below:-
Use Single-Click to Only for Single Dataset.
We always use “Single-Click” on the “Format Painter” icon when we need to apply formatting into a single dataset.
If we want to apply format painting in a single dataset, then click a single time on this icon.
See, in the above example, we apply Format Painter only in a single dataset.
Use Double-Click for Multiple Dataset.
If we want to apply a dataset formatting with all other datasets, then follow the below procedure. First, see the situation:-
See in the above image, where we want to apply the formatting of column “A” into all other datasets such as “C”, “D” and “E”.
First step, select the whole dataset, which format we want to apply to another dataset.
Select the whole dataset in column “A” and then go to the “Home” tab or Clipboard group, then “Double-Click” on the “Format Painter” icon.
See your dataset has been copied, now drop the mouse cursor on each column one by one. As a result, see below image:-
Apply Conditional formatting by using Excel Format Painting
This is one of the best features of Excel Format Painting to apply conditional formatting from a single dataset to multiple datasets.
In the above examples, by the Format Painting feature, we can copy multiple formatted datasets and apply it into multiples of dataset in a few clicks.
Simply follow- the single procedure to apply condition formatting from a single dataset to multiple datasets.
First of all, select the dataset where conditional formatting is applied. Then go to the “Home” tab and click on “Format Painter’ icon “Single or Double times”, as per the requirement.
Once the format is copied then drop the mouse on the desired location single times or multiple times. Follow the same procedure, as follows in the above example.