How To Replace Cell Formats In An Entire Excel Workbook?

An entire Excel workbook may need to be reformatted if worksheets are changed independently.

Isn’t it frustrating when it takes so long to format your Excel documents? The good news is that you do not need to keep applying the same formatting styles every time. Instead, it is easy to format multiple sheets at once using a few simple tricks.

This post demonstrates how to format a whole workbook within minutes to work effectively and be more productive rather than working hard. Let's just study this with an example.

In the current economic climate, people are embarking on economic drives to save money in the workplace and at home, with utility companies announcing drastic price increases and petrol prices at an all-time high.

Similarly, the firm of Waste-Knott & Wantnott has instituted strict measures to cut costs across the board. After calling an all-staff meeting, the manager asked everyone to think about their actions a little more carefully. 

Do you really need to do what you are doing? He said. "Do you have to make that phone call? Can't you send an email instead? Is it necessary to keep the light on in the stationery cupboard when no one is there?".

Over a year, making small daily savings would add to substantial savings.

Saving money by not wasting printer’s ink

One employee decided to print an Excel worksheet with the new cost-saving attitude in mind. He spotted one of the posters just as he was about to click Print, prompting him to think. It is interesting to note that all of the column headers and totals in the Workbook were bold even though he was printing it out for reference purposes only. Ink would be wasted if he printed these as they were, so he changed all the bold entries to regular type. 

But, have you ever wondered what if this Workbook had twenty-seven worksheets? Is it possible to change or format the entire workbook?

Yes! Now, you can easily change the entire workbook!

Changing the formatting of an entire workbook would take more time than changing the formatting of each worksheet individually in Excel. All versions of Excel from 2003 onwards follow the same process.

For example-

1: The employee could have removed the bold font from the Workbook by taking the following steps.

Step 1: Click Ctrl + H to open the Find and Replace dialogue box on the Workbook.

Step 2: Click the Format button instead of typing into the Find What box. That will bring up the Find Format window.

Step 3: Choose Bold on the Font tab.

Step 4: Click OK.

2: Replace the common words

Step 1: Select Regular from the list in the Find and Replace dialog box and click OK.

Step 2: A dialog box will appear where you can search for and replace common words.

Step 3: Select Workbook from the Within options before clicking Replace All

Excel has now been instructed to replace all bold types with regular ones throughout the Workbook.

The dialog box is well worth exploring. A vast array of formatting options are available under all tabs. Make your Workbook stand out by applying patterns or borders.

This application has the advantage that once you learn how to perform one task, such as the one above, you can often perform several similar tasks using the same method. Professionals can gain a working knowledge of Excel fairly quickly, as the learning curve is less steep. You will always be in demand if you can use Excel effectively, so why not find out just how versatile it can be? For more tricks about Excel Tool, read our article!