Replacing Cell Formats In An Entire Excel Workbook

Wed 24th August 2011

It may be that you have to change formatting to certain cells in an entire Excel workbook; a time-consuming chore if each worksheet is altered individually. This article shows how to change the formatting of an entire workbook in minutes.

In the current economic climate, with the utilities companies announcing huge price rises and petrol prices at an all time high, many people are embarking on economy drives to save money in the home and the workplace.

So it is at the firm of Waste-Knott & Wantnott, where a strict regime has been initiated to cut costs across the board. The manager called a meeting at which he asked the entire workforce to consider their actions a little more carefully. "Ask yourselves if what you are doing is really necessary," he said, "For example, do you really need to make that phone call? Would an email not do the job just as well? Does the light in the stationery cupboard need to be on when nobody is in there?"

He went on to say that making small savings each day would amount to a considerable saving over the course of a year. To remind everyone that an economy drive was under way, he had posters made that asked the question, IS THIS NECESSARY?

With this new cost-cutting attitude at the fore, one employee went to print off an Excel workbook. Just as he was about to click Print, he glimpsed one of the posters and this prompted a thought. The workbook he was about to print off was for reference purposes only and yet all of the column headers and totals were in bold type. Printing these as they were would be a waste of ink, so he set about changing all of the bold entries into regular type. The only trouble was, there were twenty seven worksheets in this workbook, so it would take some time to complete.

It would if he altered each worksheet individually, but Excel has the means to change the formatting of an entire workbook with just a few mouse clicks. The process is the same for all versions of Excel from 2003 onwards. Here is how the employee would have removed bold font from the entire workbook.

Open the workbook and press Ctrl + H to bring up the Find and Replace dialog box. Instead of typing in the Find What box, click on the Format button to the right of it. This will open up the Find Format dialog box. Select the Font tab and click on Bold in the Font Style list. Click OK.

Back in the Find and Replace box, click on the Format button to the right of the Replace With box, and this time select Regular from the list and click OK. You will be sent back to the Find and Replace dialog box. The last thing to do is click on the options for the Within box and select Workbook and then click Replace All. You have now instructed Excel to find all of the bold type within the workbook and replace it with regular type.

It is worth taking some time to explore the Find Format dialog box. Look under all of the tabs and you will see the vast array of formatting options at your disposal. Experiment with different fonts, or apply patterns or borders to an entire workbook.

And that is one of the great features of this application; when you learn how to perform one task, such as the one above, there are often many similar tasks that can be carried out using the same process. This makes the learning curve a lot less steep and it enables the student to build up a working knowledge of Excel fairly quickly. Those skilled in the use of Excel are always in demand, so why not embark on a journey to see just how versatile this application is.

Author is a freelance copywriter. For more information on excel course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1897-replacing-cell-formats-in-entire-excel-workbook.html