Format painter is a very handy tool that you can use to reduce the time spent on formatting cells in excel. Format painter allows you copy formats of one cell or range to another cell or range. In today’s tutorial, we would be taking you through how to use format painter in excel. Before we begin, you should note that there are 2 ways of activating the format painter in excel, these are:
- Format painter through the home tab
- Using Keyboard Shortcut to format
Format Painter through the home tab
The home tab has a format painter icon that resembles a painter’s brush. To format a cell to match another cell, all we need to do is to first select the source cell, then click on the icon and now select the cell you want to format. Let’s demonstrate below:
In our example, we need the number on cell D5 to have the exact format as the number on cell B5. How do we go about this without wasting much time? Easy, we just need to follow these steps:
Step 1: Select the source cell that contains the format. This is cell B5 in our case.
Step 2: On the home tab in the clipboard group, click on format painter
Step 3: You would notice a plus and a brush as you hover above cells in the worksheet. Now select the cell you want to format (cell D5 in our example) by clicking on it with with the plus and brush.
Result:
Once you click on the cell you want to format, the plus sign and brush disappears which means that format painter has been deactivated. You can activate the format painter for use on more than one cell by double clicking the format painter icon in step 2. Please see our visual guide below:
Format Painter on multiple cells at the same time
In the previous section we showed you how to activate the format painter brush and apply on the cells you need to format. The challenge however is that this is only useful when there is a single source cell to derive formats from. What if we need to format a range?
For formatting ranges, we would need to select the complete range before clicking the format painter icon, then you can choose to select the equivalent number of rows in the new range that needs formatting, or just click the first row in the new range (but careful, this method may get messy if you do not click on the first row).
Let’s show you an example with our format painter excel visual guide below. We have a template that shows our actual performance and we want the budget by the side in the same format:
