This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Share Styles Between Workbooks
Problem: I created the CommaGood style as discussed in the last topic. But, it is only in the workbook that I used. Do I really have to do this for every single workbook?
Strategy: Styles that you create are available only in the current workbook. It would be better if you could globally make the style available to all workbooks.
There are two approaches that you can use.
In the first approach, you create a sample workbook that contains all your favorite custom styles. You can copy those styles to any workbook by following these steps:
1. Open the new workbook and the sample styles workbook.
2. Make the new workbook the active workbook.
3. Open the Cell Styles menu and choose Merge.
4. In the Merge Styles dialog, choose to merge from the sample styles workbook. Excel will copy the styles to the new workbook.
This approach is admittedly a hassle because you would have to apply the styles to every workbook you ever create.
The second approach is more difficult at first, but then will be easier as you create new workbooks. This approach uses a new book template. Follow these steps to define a book template:
1. Open a blank workbook with a single worksheet.
2. Create new styles as described earlier.
3. Clear the cells that you used to create the styles.
4. Optionally, if you have favorite Page Setup settings, apply any custom margins, headers, footers, or scaling settings.
5. In Windows Explorer, create a new folder. You might call this folder c:\XLTemplates.
6. In Excel, go to File, Options. In the left navigation, choose Advanced. Scroll to the bottom and look for a section called General. There is a setting for At Startup, Open All Files In. Enter the name of the folder from step 5 in that box. Click OK.
7. Use File, Save As. In the Save as Type drop-down, choose Excel Template. Annoyingly, Excel navigates to a different path. Browse back to C:\XLTemplates.
8. Save the file with a name of Book.
9. Perform a second Save As to save as a template with the name of Sheet.
Whenever you want to create a new workbook, use the Ctrl+N shortcut. This will load the Book.xltx file as the new blank workbook. All your favorite cell styles will be available. When you click the new worksheet icon, Excel will insert Sheet.xltx as the new sheet. This will ensure that your page setup settings apply to the new worksheet.