Customize Excel Default Settings to Work as Your Way

People are different. They have different requirements. So, the out-of-the box environment of Microsoft Excel might not provide the most efficient setup for you. When you create a new workbook, if you find yourself changing the same attributes every time, you can consider changing them at the program or template level. Customize Excel default settings, Excel will generate each new workbook just the way you like it. Sometimes, changing some default settings to meet your needs can save you tons of time and make you happy on working. There are some samples that many users customize to meet their needs. Maybe you can find some good for you. 

1. File Location

The Excel default file location is “My Documents”. You can quickly customize this default setting as follows:

1. Go to the File tab and choose Options (under Help). In Excel 2007, go to the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. In the left pane, choose Save. In Excel 2003, click the General tab.
3. In the Save Documents section, change the path in the Default File Location field, as shown in the image below. You can type the path or click Browse to locate it.
4. Click OK.

2. Number of sheets

In the Excel default setting, each new workbook includes three sheets. You can easily add or delete sheets, but you can also change the number of default sheets, as follows:

1. Go to the File tab and then click Options (under Help). In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Click General in the left pane. In Excel 2003, click the General tab.
3. In the section of When creating new workbooks, enter the number of sheets in the Include this many sheets field, as shown in image below. In Excel 2003, use the Sheets in new workbook drop-down.
4. Click OK.

3. Open a Specific Workbook

If you often work with the same workbook, it can save some time by opening a specific workbook when launching Excel. This shortcut is easily accomplished by saving that workbook in the XLStart folder. Depending on your version of Windows, you’ll find that folder in one of the following locations:

• Windows XP: C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart
• Windows Vista: C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart
• Windows 7: C:\Program Files\Microsoft Office\Office\XLStart

Just save the workbook as you normally would, but save it to the appropriate XLStart folder.

4. Cursor Movement

In Excel worksheet, when you press “Enter”, Excel moves the cursor down one cell. But if you want to enter data by moving to the right, rather than down, you may want to change the cursor movement, as follows:

1. Go to the File tab and then click Options (under Help). In Excel 2007, go to the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. In the left pane, select Advanced. In Excel 2003, click the Edit tab.
3. In the Editing Options section, choose Right from the Direction drop-down under the After Pressing Enter, Move Selection option, as shown in the image below. You can choose Right, Left, Up, and Down. In Excel 2003, the option is Move selection after Enter. Uncheck the option can stop cursor movement.
4. Click OK.

5. Font and Size

The default setting of Excel font and size can also be customized. By doing so, you eliminate the task of adjusting each new workbook. To change the default font, do the following:

1. Go to the File tab and then click Options (under Help). In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Choose General (if necessary) from the left pane. In Excel 2003, click the General tab.
3. In the When Creating New Workbooks section, change the Use This Font (Standard Font in Excel 2003) and Font Size options, as shown in the image below.
4. Click OK.

6. Template

Like Word, Excel bases each new workbook on a template file (Book.xltx) which can be permanently changed by adjusting many default settings. You can make this change once to Book.xltx, instead of adjusting each new workbook manually.

When making changes at the template level, you can start in one of two ways:

• If you’ve customized Book.xltx, open it so you don’t lose prior changes.
• If you haven’t customized Book.xltx, you can begin with any blank workbook.

Make the changes you want to implement at the template level and then save the workbook as a template:

1. Go to the File tab and choose Save As. In Excel 2007, click the Office button and choose Save As. In Excel 2003, choose Save As from the File menu.
2. Choose Excel Template (.xltx) from the Save As Type drop-down, as shown in the image below. Excel 2003’s template extension is .xlt.
3. Locate the XLStart folder using the Save In control. You must save Book.xltx to the XLStart folder.
4. Click Save.

If you can’t find XLStart, you can use Excel’s Visual Basic Editor to find the path, as follows:

1. Launch Excel’s VBE by pressing “Alt + F11”.
2. If the Immediate window isn’t visible, press Ctrl + G.
3. In the Immediate window, type ? application.StartupPath and press Enter, as shown in the image below.

If you want to alter things at the sheet level, make your changes and save the file as Sheet.xltx. You’ll use this file to change things such as inhibiting gridlines.

7. Startup folder

Tips #3 and #6 both rely on the XLStart folder to open or use specific files when you launch Excel. However, you can add a second location as follows:

1. Go to the File tab and then click Options (under Help). In Excel 2007, go to the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Choose Advanced in the left pane. In Excel 2003, click the General tab.
3. In the General section, enter the path to the new alternate startup folder, as shown in the image below.
4. Click OK.

Excel will continue to open any workbooks found in XLStart, in addition to those workbooks found in the alternate startup location.

8. File format

Many organizations must deal with version compatibility issues because some users upgraded to the Ribbon version before others. When sharing Excel 2007 and 2010 files (.xlsx format) with Excel 2003 users, you must downgrade the workbook to the older format (.xls). Doing so is no big deal, but if you have to do it for every workbook, you might as well change the default file format to .xls, as follows:

1. Go to the File tab and choose Options (under Help). In Excel 2007, go to the Office button and then click Excel Options.
2. Choose Save in the left pane.
3. In the Save Workbooks section, choose Excel 97-2003 Workbook (*.xls) from the Save Files in This Format drop-down, as shown in the image below.
4. Click OK.

However, by using this method, we can’t use new features. Download and install the compatibility pack might be much easier.  

9. Enable Text Wrap

When we enter a long string into a cell, Excel allows it to extend past the column’s right border. If there’s no data in the adjacent cells, the entire string is visible. But once you enter data in the adjacent cell, the new data takes precedent and the original string disappears behind the new text. This happens at the format level and we can reset this alignment attribute by adjusting the Normal style. Unfortunately, that doesn’t help all the other workbooks you create. If this is a format you want to set for all new workbooks, change the Normal style in book.xltx (#6):

1. Go to the Home tab. Then, in the Styles group, click the Cell Styles drop-down. In Excel 2003, choose Style from the Format menu.
2. In the resulting gallery, right-click Normal and choose Modify, as shown in the image below. In Excel 2003, select Normal in the Style Name control, click Modify, and then skip to step 4.

3. In the Style dialog, click Format.

4. Click the Alignment tab and select the Wrap Text option in the Text Control section, shown in the image below.

5. Click OK twice.

Changing the Normal style only works in the current workbook. For all new workbooks, you need to save at the template level to adjust the style.

10. Comment Font Size

To customize Excel comment font size would take a detour from the traditional routes. We must change a Windows setting.

For Windows XP, do the following:

1. Right-click the desktop and choose Properties.
2. Click the Appearance tab and then click Advanced.
3. From the Item drop-down, choose ToolTip.
4. Select a font size and click OK.
5. Click Apply and then click OK.

Windows 7 users should do the following:

1. Right-click the desktop and choose Personalize.
2. Click Window Color.
3. Click Advanced Appearance Settings.
4. From the Item drop-down, choose ToolTip.
5. Choose a font size.
6. Click OK and then click Save Changes.

This change will update all tip-type windows, not just Excel comments. So be sure you can live with the total package, especially if you share your system with other users.

Note: Although the dialog will let you specify a different font, Excel will ignore the new setting.