When I walk round companies to help those who have attended my courses, and those who have not, I do find that they sometimes do things the very long way round. For instance, I watched someone painstakingly format the same cells in each of their 10 sheets in exactly the same format. I almost cried that they took so long in doing this when there is such an easy and quick way to format across sheets. As long as the information that you wish to format in a particular way is in the exact same place of each sheet!
My example today will be collecting information from various sources that use different formatting styles for dates. Not only that but on occasion when you copy a date and wish to paste, it reverts to a number.
After all dates are simply numbers formatted to look like dates. In a past tip. I talked about inputting dates in three different ways. Another shows how to count only week days.
Why can we count using dates?
We can use dates to count because a date is just a number. For instance. Take the number 12345. Now format it in excel as a date. Your result is 18/10/1933 because there are 12345 days from the 1st January 1900 to 18th October 1933. Working on that premise when you paste a date and see the result is a number Do Not Panic. Simply format it to a date format of your choice.
Back to formatting the same cells in multiple sheets.
My tip – Creating several sheets in Excel with the same titles and calculations simultaneously explains just what I mean here.
Here is some data that has different dates with times collected from various sources.
The First Sheet named “Area1” has the same layout exactly as the other three. In this sheet, you can see that the dates are all formatted differently. Some are even in US format. We need them all to be the same and that goes for the other three sheets as well.
Selecting several sheets simultaneously
- Hold down the SHIFT key on the keyboard.
- Click on the last sheet.
- Let go of the SHIFT key.
The result should look like this. Your sheets are grouped.
Formatting across sheets
- Select the cell that holds the formatting you desire, in just the active sheet.
- Select the format painter tool from the Clipboard section on the Home Tab.
- Click on the name of the column where the dates reside.
Your result will be this on every sheet that is grouped. 
This can be translated to anything you need to look the same on each sheet as long as it is in the same place on each sheet.
