Microsoft Excel 2003, 2007, 2010, 2013 Tips
The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
June 17, 2015
If the images in this message are a little misaligned in your email program,
you can go here
to read the online version of this newsletter
Greetings from The Excel Addict
NOTICE OF TEMPORARY CHANGES TO MY NEWSLETTER - PLEASE READ
As you may already know, I publish this newsletter every week, maintain my website and answer a lot of questions from Excel Addict members, all in my 'spare' time. Yes, I do have a full-time day job as well, so every week it's a challenge for me to find the time to write and publish my newsletter.

Over the next two months I will be updating my website, something I have been wanting to do for years. This is going to take up a significant amount of my time but I definitely don't want you to miss out on these valuable tips completely. So, what I have decided to do for the next while is to continue publishing my newsletter weekly, but in order to free up some time, I will be scaling back to just one tip each week. I hope that will be enough to keep you learning and loving Excel until I get my new and improved website up and running.

Thanks in advance for your patience and understanding.
Francis

Hi fellow Excel Addict,


Francis Hayes (The Excel Addict) - I want to help you EXCEL !!Thanks for joining me this week.

Sorry. My website project is taking longer than I expected. I ask for a little more patience. + I have an exciting announcement coming up in the next few weeks. Stay tuned.

This week's tip is a repeat of a one I posted a couple of years ago. I hope you'll find it useful and that it helps make your work a little easier. If you do, please be kind and share it with others who could use a little help as well.

I hope you have a fantastic week.

Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com


To view this week's newsletter online click here.

If you missed last week's newsletter, you can click here to view it online.

You can access even more tips on my website by going to my members' page



If you have a favourite quote, send it to me and I may post it in my newsletter.
"If you don't see yourself as a winner then you cannot perform as a winner." -- Zig Ziglar
Microsoft Excel Tip #1
Create Custom Default Worksheet And Workbook Templates

Are you constantly having to change the settings for your worksheets, such as margins, font size, page headers, etc... every time you create a new workbook or insert an new worksheet? Most Excel users assume that's just the way it is. Well, I'm here to tell you those days are over.

I'm going to show you how to create a template containing your own personalized settings that will be applied to all new workbooks and worksheets.

When you open a new workbook, Excel uses a workbook template with default settings such as three worksheets, column widths of 8.43, no headers and footers, default print margins, etc... When you insert a sheet into a workbook, Excel uses a worksheet template with similar default settings.

Here's how to create your own personalized templates so that every new workbook and worksheet will already have the settings you want.

1) Open a blank workbook;

3) Delete all sheet tabs except for one;

4) If you want to change the default formatting of your worksheet cells, such as the font face, font size, number formats, etc..., on the Home tab, click the Cell Styles command in the Styles group. Then right click the Normal style and choose Modify. Click the Format button and make any changes you like to the default style and click OK;

5) Next, to select your preferred print settings, click the Page Layout tab. Make all the changes you want your default worksheets to have (i.e. margins, headers/footers, etc...);

Personalizing Your Default Worksheets?

• Want a Custom Header or Footer to appear on every new worksheet? On the Page Layout tab, click the Dialog Launcher (small arrow on bottom right corner of 'Page Setup' group) and click the Header/Footer tab.
• Want all new sheets to fit your data one page wide? From the Page Layout tab, select 1 Page in the Width field of the 'Scale to Fit' group.
• Don't like the current print margins? On the Page Layout tab, click the Margins command in the 'Page Setup' group, then click Custom Margins...
• Would you prefer the default orientation of all new sheets to be Landscape?
On the Page Layout tab, click the Orientation command in the Page Setup group.
• Don't like gridlines displayed in your worksheets? Click the View tab and uncheck Gridlines in the Show group.



6) When you have selected all of the custom settings for your default worksheets, click cell A1 to make it the active cell for all new worksheets;

7) Next, you need to save this custom worksheet as a template in your XLSTART folder. Click the File tab and Save As. In the Save as Type dropdown on the bottom of the dialog box, select Excel Template (*.xltx) and browse to the XLSTART folder.

Locating Your XLSTART folder

In Windows 7 and 8 you should be able to find your XLSTART folder at...
C:\Users\username\AppData\Roaming\Microsoft\Excel\

If you do not see the AppData folder, try typing the full path (above) into the Address Bar in Windows Explorer.

Easily locate your XLSTART directory / folder
 
Another option for quickly locating your XLSTART folder is to type or paste the following command into the Windows Explorer Address Bar or even into the 'File Name' field of Excel's Save As or Open dialog boxes and press Enter.

%APPDATA%\Microsoft\Excel\XLSTART

Easily locate your XLSTART directory / folder


Change the suggested name to SHEET.XLTX. This will now be the default sheet template used when you Insert a worksheet into a workbook;

Create a custom default workbook

9) Now you need to save this same personalized worksheet as your default 'workbook' template by saving it with a different file name. To do this, simply repeat Step 7 but this time save the workbook with the filename BOOK.XLTX;

Personally, I like my default workbook to have only one sheet rather than the default three sheets Excel offers, as I find the extra two sheets redundant. If you choose to go with a one sheet default workbook you can quickly insert a new sheet by clicking the Insert Worksheet command (immediately to the right of the sheet tabs). However, in workbooks with a lot of sheet tabs, the Insert Worksheet command may not be visible, so I recommend adding the Insert Worksheet command to your Quick Access Toolbar (QAT). Alternatively you can use Shift+F11 to insert a new sheet.

Add the Insert Worksheet and New workbook commands to your QAT

1) Right-click the QAT and choose Customize Quick Access Toolbar;

2) In the Choose commands from dropdown, select All Commands;

3) Scroll down and select the Insert Worksheet command and click the Add>> button;

4) Scroll down again and select the New Workbook command and click Add>> button. Note that the name in the list for this command is New, not New Workbook.

Add the Insert Worksheet command on the QAT

Now, whenever you open a new workbook or insert a new worksheet it will always contain your preferred settings.

If you chose to have only one sheet in each new workbook, you can easily insert additional sheets whenever needed by clicking on the Insert Worksheet button.

Remember that if you ever get a new computer or work on multiple computers, you can copy these two files (SHEET.XLTX AND BOOK.XLTX) so you won't have to create them again.


Thanks for supporting this newsletter and website







Subscription Information: Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter. If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com

 If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com
3)  Post a link to TheExcelAddict.com in a company newsletter or website.

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630