Beautifying our reports may take some time to do. But it’s worth the effort as it can significantly enhance our reports’ readability. A visually appealing report can surely attract anyone’s attention.
If you have perfected a particular report design and would want to replicate that on another report (or on another section of the report), you don’t have to painstakingly repeat the same steps just to apply the same format.
In this article, I’ll show you ways to copy the cell format to a different group of cells. I suggest you go over each method. I’m sure you’ll find something interesting to use next time.
What Exactly is Included When You Copy a Cell Format in Excel?
When we say “cell format” in Excel, we are referring to the following:
- The Number Format (General, Number, Currency, Text, etc.)
- Font Properties (Type, Style, Size, Color)
- Text Alignment, Direction, Indentation and Orientation
- Cell Borders and Background (Fill Color)
- Conditional Formatting
How to Copy Cell Format in Excel using Paste Special?
1. Select the cells containing the format you want to copy.
In my example above, I have set the format of the first 4 data rows, so I have highlighted these cells.
2. Right-click on one of these cells and select Copy. For the keyboard shortcut, press CTRL + C.
You’ll know the cells are copied in the clipboard once you see a moving broken line surrounding the selected cells.
3. Next, highlight the cells where you would like to paste the same format.
4. Right-click on one of the selected cells. From the menu that pops up, select Paste Special >> Paste Special.
If you want the keyboard shortcut, press CTRL + ALT + V. Doing so will open the Paste Special Menu.
5. In the list of Paste options, select Formats and click OK.
6. And that’s it! The selected cells will now have the same format.
Expert Tip:
The above method is great if you only need to copy the same format on adjacent cells because you will only need to do the steps once.
However, if you want to copy the cell format on different sections of the worksheet or workbook, you will need to repeat the steps, which can be cumbersome.
But there’s a workaround for that. You can still use this method to copy the format in lesser steps. You only need to utilize the keyboard shortcuts. You can try the following:
- Select the cells containing the format you want to copy and press CTRL + C to copy.
- Select the first group of cells where you want to paste the format and press CTRL + ALT + V.
- As the Paste Special menu appears, press T to select “Format” in the list of options.
- Then, press Enter.
- After that, highlight the next group of cells where you want to paste the same format.
- This time, press F4. This keyboard shortcut repeats the last command or action. So, in our case, it copies the cell format onto selected cells.
- Repeat steps 5 and 6 until you’ve copied the format on all the desired cells.
How to Copy Cell Format in Excel using the Format Painter?
1. Select all cells containing the format you want to copy.
2. Go to the Home tab. Click the “Format Painter” or the paintbrush button inside the Clipboard section (as shown below).
You can also just press ALT + H + FP if you want the keyboard shortcut.
You’ll know that the format painter is activated once you see a moving broken line surrounding your selected cells (same as when you copy a cell). You’ll also find the format painter logo appears next to your cursor.
3. Next, drag your mouse to highlight the cells where you want to paste the format.
Once you release your mouse, Excel automatically pastes the format onto these cells.
And that’s it! You have successfully copied the same format.
Expert Tip:
The above solution works great if you want to paste the format onto cells that are adjacent or next to each other.
If, however, you want to paste the format on multiple cells that are on different sections of the sheet or workbook, you can do the following:
- Select the cells you want to copy the format from.
- From the Home tab, click on the Format Painter button twice. Remember to double-click, not single-click. Doing so tells Excel to keep “copying” the selected cell even as you move to different sections of the sheet and have already pasted the format to a group of cells.
- Drag your mouse to highlight the cells where you would like to paste the format. Keep doing it until you have copied the format to all your desired cells.
- Once done, simply press ESC to deactivate the Format Painter. (You can also click the Format Painter button to deactivate it.)
How to Copy Cell Format in Excel using the Fill Handler?
Note that this method will only work on cells of the same column or rows.
This method is perfect if you want to apply the same format on cells that are next to each other.
1. Select all the cells containing the format you want to copy.
2. As you select the cells, notice a small green box at the bottom right of the selection. That is the Fill Handler.
3. Drag the Fill Handler to the direction of the cells you want to apply with the same format.
In my example above, I am dragging the Fill Handler to the right because the cells I want to format are on the right.
4. Once you’ve reached the end of the cells where you’d like to have the same format, release your mouse.
5. You may notice that Excel has changed the values in the selected cells. Don’t worry. The cells will revert to their original values. We only need to change the Fill Handler settings.
6. Click on the Fill Handler Option. You will see this at the bottom right of the selection.
7. From the list of options, select Fill Formatting Only.
8. And that’s it! You should now see the selected cells revert to their original values. They also now have the same format as the first group of cells.
Expert Tip:
If you have a large area of cells to apply the same format to, instead of dragging the Fill Handler up to the last row, you can try double-clicking on the Fill Handler to have it automatically paste the cells with the same format and values. (Note that you can change the Fill Handler option later to only copy the format.)
Please note, however, that this tip does not always work. If your dataset has an inconsistent data arrangement, like if there are merged or blank cells in between, nothing will happen when you double-click the Fill Handler.
If this is the case, it means Excel cannot determine the end of the dataset, which is why it can’t fill in the adjacent cells. When this happens, you will have to manually drag the Fill Handler till the end of the dataset.
Conclusion
As you can see, there are three nifty ways to copy cell format in Excel. Each of them has its pros and cons. So, use the method that best suits your needs.
If you ask me, using the Fill Handler is the fastest way to copy the format – but it only works if your cells are next to each other. If you want to copy the format on different sections of the workbook you can do so either by using the Format Painter or the Paste Special method.