Using Default Worksheets

I ended last week’s blog introducing the concept of a default worksheet that we could insert at any time in any workbook to help make copying page formats even faster and easier.  In today’s blog, we’ll actually save a default worksheet, and see how it works.

  • Click here to open an Excel 2007 workbook (zipped, no macros) that already has the Page Layout we want to use as a default worksheet. If you want to learn more about setting up customized Page Layouts, please see Chapter 3 “Printing the Worksheet” in the book “Simply Learning Excel 2007” available at Amazon.com.
  • Print Preview the worksheet (Office Button > Print > Print Preview). Note the customized page layout – gridlines, page numbers, file name, special sheet name, etc. You may be asking yourself why I didn’t leave the worksheet blank. The answer is that we will remove all content before we save it as a default worksheet, but Print Preview will only work if the worksheet has some content.
  • Right-click Row 10 and in the menu that appears, click Delete.
  • Click Cell A1.
  • Click the Office Button.
  • In the menu that appears, place the mouse pointer over the words Save As, and in the menu that appears on the right, click Other Formats.
  • In the box labeled Save As that appears, click the bar just to the right of the words “Save as type:” and in the list that appears, click “Excel Template (*.xltx)” (your computer may not show the file extension) as shown here:
    image
  • In the white box to the right of the words “File name:,” type the word Sheet.
  • The bottom of the box labeled Save As should now look as shown here: (Your computer may not show the file extension.)
    image
  • In the file location at the top of the box labeled Save As, specify the XLSTART folder. The location of that folder will vary depending on what operating system you’re using. To find out the exact location for your operating system, I recommend typing xlstart in a search engine such as Google, or in the Excel Help.
  • Click Save and close the file.

Let’s use the default worksheet we just saved to copy formats. For a more thorough discussion on copying page layouts, please see last week’s blog.

  • Click here to open an Excel 2007 file (zipped, no macros) we can use to copy formats.
  • If not already selected, click the sheet named Sheet2.
  • Print Preview Sheet2 and note that it has no custom page formatting.
  • Right-click the Sheet2 tab and in the menu that appears, click Insert.
  • In the box that appears labeled Insert, click the Worksheet icon, then click OK. Note that a sheet named Custom Sheet is now to the left of the sheet named Sheet2 and is the active sheet.
  • While holding down the Ctrl key, click the sheet tab labeled Sheet2.
  • Release the Ctrl key.
  • Click the Page Layout tab in the Ribbon.
  • In the Page Layout tab, in the Page Setup group, click the little Page Setup dropdown arrow, as shown here:
    Copy-Formats-Part-5-05b-Page-Setup-d[2]
  • Click OK. That one little step has now copied the page layout of the default worksheet we just inserted to Sheet2.
  • Right-click either of the two highlighted sheet tabs (Custom Sheet or Sheet2) and in the menu that appears, click Ungroup Sheets.
  • Print Preview Sheet2 again, and note that it now has the custom page layout we established for the default worksheet.
  • Close the Print Preview.
  • Right-click the sheet named Custom Sheet and in the menu that appears, click Delete.

Excel also allows us to establish default workbooks using a similar process. A default workbook will open up automatically every time we open Excel. To find out more, search for the words “Customize how Excel starts” in Excel help.

I hope this blog has been helpful to you. In two weeks, we’ll talk about how to avoid problems when sorting data. See you then!

This entry was posted in Default Worksheet, Excel Copy Formats. Bookmark the permalink.

One Response to Using Default Worksheets

  1. KimC says:

    Very cool, I never knew you could do this. Thanks for the tips.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>