1-866-245-5224 sales@keystonelearning.com

Excel 2007: Modifying Excel Options

Excel 2007: Modifying Excel Options

 In earlier versions of Microsoft Excel and other Microsoft programs, you could set your program preferences such as the default file save location or view options in the Options dialog box found under the Tools menu.

But, remember, with Excel 2007, there are no more menus and toolbars. The new Options dialog box can be found on the Office Menu (near the bottom).

Modifying Popular Options

The Popular Options tab contains the most often modified settings for Excel 2007.

 

Figure 34. Excel Options Dialog Box - Popular

Top Options for Working with Excel

By checking Show Mini Toolbar on selection is what enables the QuickFormat mini toolbar to pop up when you are editing a cell. The default state is checked.

When the Enable Live Preview is checked, your worksheet will change “on the fly” as you hover over gallery options. The default state is checked.

By checking Show Developer tab in the Ribbon, you’re enabling an advanced tab on your Ribbon giving you access to commands that will help you with XML and macros. The default state is unchecked.

Always use ClearType enables ClearType, which simply makes text easier to read. The default state is checked.

The Color scheme color defines the color scheme of the open program. The default state is Blue.

The ScreenTip style option allows you to choose what type of screen tip you see when you point to a command in the Ribbon. The default state is Show feature descriptions in ScreenTips.

When using AutoFill, Excel automatically completes lists of known data such as months of the year or days of the week. You can create your own custom lists by clicking Edit Custom Lists.

When Creating New Workbooks

The Use this font option allows you to set the default font for all new worksheets. The default state is Calibri.

The Font size sets the size of the default font for all new worksheets. The default state is 11.

Default View for new sheets determines the view that you see when you start Excel. You can select Normal View, Page Break Preview, or Page Layout View. The default state is Normal View.

Include this many sheets determines the number of worksheets that you see when you create a new workbook. The default state is 3.

Personalize your copy of Office

In the User name box you should type the name you want to be attached to the XML or any editing you do using the Track Changes features. The default state of this box varies based upon your installation settings and, often, your network login information.

Click Language Settings… to set your editing language preferences.



Modifying Formula Calculation, Performance, and Error Handling Options

The Formulas Options tab contains settings you can modify for how Excel deals with formulas.

Figure 35. Excel Options Dialog Box - Formulas

Calculation Options

The settings under Workbook Calculation determine how and when Excel 2007 calculates your formulas after you make a change. You can choose Automatic, Automatic except for data tables, or Manual. The default state is Automatic.

Automatic calculation performs all calculations immediately after you make a change to a value, formula or name.

Automatic except for data tables performs all calculations immediately except data tables.

Manual calculation only calculates an open workbook when you specify by clicking Calc Now.

It’s typically a good idea to leave Enable iterative calculation unchecked. If checked, Excel can find itself in an unending loop of trying to resolve a circular reference. A circular reference can be a never ending cycle in which a formula references its own cell.

Working with formulas

By default, we’ve all become accustomed to using the A1, B3 cell reference style. However, you can use the R1C1 reference style which refers to the actual column and row numbers instead of using letters for columns and numbers for rows. The default state is unchecked.

Formula AutoComplete is one of the coolest new features in Excel 2007. This feature is what drops down a list of functions when you begin typing in a formula by entering an equal sign (=) followed by a function name. The default state is checked.

By checking Use table names in formulas, you make working with tables even easier because it allows you to reference a name instead of a range in your formulas. This is particularly helpful when working with lookup functions. The default state is checked.

Much like being able to use table names in formulas, the Use GetPivotTable functions for PivotTable references makes referencing PivotTable data even easier because it allows you to use names versus ranges in your formulas. The default state is checked.

Error Checking

Enable background error checking, when checked, allows Excel to check cells to for errors whenever the program is idle. If Excel finds an error, it will flag the cell is an indicator in the upper-left corner in the color you set in the option directly below. The default state is checked.

Indicate errors using this color sets the color that Excel uses to indicate errors. The default color is green.

Much like the Spell Check feature will remember if you’ve ignored a word, Excel can remember if you’ve ignored an error. But, you can click Reset Ignored Errors to set everything back to normal and have old errors flagged again.

Excel Checking Rules

The bottom set of checkboxes are individual error rules you can choose to apply or not, at random.

With Cells containing formulas that result in an error selected, Excel will treat cells that contain formulas that result in an error as an error display a warning. The default state is checked.

Inconsistent calculated column formula in tables allows Excel to treat cells that contain formulas or values inconsistent with the column formula or tables as an error display a warning. The default state is checked.

A throwback to the Y2K scare, when Cells containing years represented as 2 digits is checked, Excel will treat formulas that contain text-formatted cells with years represented as 2 digits as an error and will display a warning when checking for errors. The default state is checked.

By default, spreadsheet programs in general treats all numbers as numbers, unless preceded by an apostrophe (‘). When the Numbers formatted as text or preceded by an apostrophe is checked, Excel will throw an error when a cell contained in a formula has a number preceded by an apostrophe or a number value formatted as text. The default state is checked.

Formulas inconsistent with other formulas in the region allows Excel to treat a formula in a region of your worksheet that differs from the other formulas in the same region as an error and to display a warning. The default state is checked.

Formulas which omit cells in a region will have Excel treat formulas that omit certain cells in a region as an error and display a warning. The default state is checked.

When Unlocked cells containing formulas is selected, Excel will throw an error and display a warning when it’s checking for errors that an unlocked cell contains a formula. The default state is checked.

With Formulas referring to empty cells selected, Excel treats formulas that refer to empty cells as an error and displays a warning. The default state is unchecked.

Data entered in a table is invalid means that Excel treats cells that contain values inconsistent with the column data type for tables that are connected to data in a SharePoint list as an error and displays a warning. The default state is checked.

Advanced options

The Advanced Options tab contains a lot of settings options for editing and the Excel display.

 

Figure 36. Excel Options Dialog Box - Advanced

Editing options

After pressing Enter, move selection tells Excel which direction, if any, you’d like the cursor to move when you press Enter to complete an entry. The default state is checked and Down.

Check the Automatically insert a decimal point box only if you wish to have numbers displayed with decimal places, by default. The default state is unchecked.

With Enable fill handle and cell drag-and-drop, you will be able to move and to copy cells and data by dragging. This option also allows you to drag the fill handle to copy data and to fill adjacent cells with a series of data. The default state is checked.

The Allow editing directly in cells option is what enables a user to double-click inside a cell to jump into edit mode. In this way, you don’t always have to use the formula bar to make edits to existing data. The default state is checked.

This option is terrific. Check Extend data range formats and formulas to have Excel automatically format new items added to the end of a list to match the format of the rest of the list including formulas. The default state is checked.

Enable automatic percent entry will automatically multiply all of the numbers less than 1 by 100 that you enter in cells formatted in the Percentage format. The default state is checked.

Check Enable AutoComplete for cell values to save you tons of time. This box will allow Excel to complete text entries that you start to type in a column of data. If the first few letters that you type match an existing entry in that column, Excel fills in the remaining text for you. How cool is that? The default state is checked.

If you have the Microsoft IntelliMouse pointing device Zoom on roll with IntelliMouse, sets the wheel button to zoom instead of scroll on your worksheet or chart sheet. The default state is unchecked.

Alert the user when a potentially time consuming operation occurs, when checked, will notify you when an operation affects a large number of cells (>33554, by default) and may take a long time to process. The default state is checked.

The Use system separators checkbox means that Excel will use a comma (,) to separate thousands and a period (.) to denote decimals. If you choose to clear this box, you will be asked which symbols to use instead of the default. The default state is checked.

Cut, Copy, and Paste

When checked, the Show Paste Options buttons will tell Excel to automatically display a dialog box with special options when you paste, such as Formatting Only and Link Cells. The default state is checked.

Show Insert Options buttons tells Excel, when checked, to automatically display a dialog box with special options when you insert cells, rows or columns. The default state is checked.

By checking Cut, copy, and sort inserted objects with their parent cells, you can ensure that graphic objects, buttons, text boxes, drawn objects, and pictures will stay with their associated cells whenever you cut, copy, filter, or sort on a worksheet. The default state is checked.

Display

The number displayed in the Show this number of Recent Documents is the number of recently used documents you’ll see when you display the Office Menu. The default state is 17.

The Ruler units set the unit you want to display on the Layout view ruler. The default state is Default Units.

When checked, Show all windows in the Taskbar will give each workbook its own button on the taskbar to make navigating between multiple open workbooks easier. The default state is checked.

When Show formula bar is checked, the formula bar will be displayed at the top of the worksheet. The default state is checked.

Show function ScreenTips will display brief descriptions of the functions that you select in the list of functions when Formula AutoComplete is turned on. The default state is checked.

When Show chart element names on hover is checked, the name of a chart element will be displayed when you rest the mouse pointer on it. The default state is checked.

When Show data point values on hover is checked, the value of a data point will be displayed when you the mouse pointer on it. The default state is checked.

Under For cells with comments, show, you can choose how you would like to view comments or comment indicators, if at all. The default state is Indicators only, and comments on hover.

Display options for this workbook

In the Display options for this workbook section, you’ll want to make sure that the name of the workbook for which you are intending to change option is selected. The default state is the name of the active workbook.

When Show horizontal scroll bar is checked, the horizontal scroll bar at the bottom of the worksheet for the active workbook will be displayed. The default state is checked.

When Show vertical scroll bar is checked, the vertical scroll bar on the right side of the worksheet for the active workbook will be displayed. The default state is checked.

When Show sheet tabs is checked, the worksheet tabs for the individual worksheets for the active workbook will be displayed. The default state is checked.

Group dates in the AutoFilter menu, when checked, allows AutoFilter to date filter on a nonhierarchical list for the active workbook, which in turn, allows you to filter for two-digit years. The default state is checked.

This one is all or nothing. With the For objects, show option, you’ll need to select whether you want to display All graphic objects or none of them for the active workbook.

Display options for this worksheet

In the Display options for this worksheet section, you’ll want to make sure that the name of worksheet for which you are intending to change option is selected. The default state is the name of the active worksheet.

When Show row and column headers is checked, the row numbers and column letters will be displayed for the active worksheet. The default state is checked.

Rather than checking Show formulas in cells instead of their calculated results to see the formulas instead of the values on your worksheet, you can quickly switch between the normal view and the formula view by pressing [CTRL]+~ on your keyboard. The default state is unchecked.

Just like the above option, there is quicker way to show page breaks than checking Show page breaks. Instead, just use Page Break Preview. The default state is unchecked.

Show a zero in cells that have zero value is the option that displays a 0 (zero) in cells that contain zero values for the active worksheet. The default state is checked.

Show outline symbols if an outline is applied, when checked will displays outline symbols for the active worksheet if the worksheet contains an outline. The default state is checked.

When checked, Show gridlines displays cell gridlines. The default state is checked.

When gridlines are displayed, the default Gridline color is based on the text color defined in the Windows Control Panel, but you can certainly change it if you’d like.

Formulas            

Selected by default, the Enable multi-threaded calculation option enables fast calculation by using all of the processors on your computer. The default state is checked.

If you decide to enter the Number of calculation threads manually, you must enter a positive number between 1 and 1024.

When calculating this workbook

In the When calculating this workbook section, you’ll want to make sure that the name of workbook for which you are intending to change option is selected. The default state is the name of the active workbook.

Update links to other documents will calculate and update formulas that include references to other applications for the selected workbook. The default state is checked.

Set precision as displayed will permanently change stored values in cells from full precision (15 digits) to whatever format is displayed, including decimal places for the selected workbook. The default state is unchecked.

When Use 1904 date system is checked, Excel will change the starting date from which all dates are calculated from January 1, 1900, to January 2, 1904 for the selected workbook. The default state is unchecked.

Save external link values will save copies of the values contained in an external document linked to an Excel worksheet for the selected workbook. The default state is checked.

General

Provide feedback with sound, when checked will play available sounds associated with Microsoft Office program events, such as opening, saving, and printing files, and displaying error messages. The default state is unchecked.

When checked, Provide feedback with animation will display worksheet movement and changes when you insert or delete cells, rows, or columns. However, the animation, fun as it may be, may slow video performance on some computer systems. The default state is unchecked.

Ignore other applications that use Dynamic Data Exchange, when checked, prevents the exchange of data with other applications that use Dynamic Data Exchange. The default state is unchecked.

When Ask to update automatic links is checked, you will receive a confirmation message before any linked items are updated. The default state is checked.

Show add-in user interface errors, when checked will display errors in the user-interface of add-ins that you install and use. The default state is unchecked.

In some countries or regions, Scale content for A4 or 8.5 x 11” paper sizes is necessary because the standard paper size may not be Letter. This box should be checked IF you want Excel to automatically adjust documents formatted for the standard paper size of another country or region so that they print correctly on the standard paper size for your country or region. This option affects the printout only. The default state is checked.

If you always open the same files each time you load Excel, this little checkbox can actually be quite a timesaver. When, At startup, open all files in is used, whenever you open Excel, all files in the specified file folder open, as well. Type the full path to the folder in the text box to indicate the location of the files. The default state is empty.

In the event that you are using Excel and the Web together, the Web Options command button lets you set options for how Excel data looks and responds when the data is viewed in a Web browser.

The Service Options command button is where you specify customer feedback and document management options.

Lotus Compatibility

Often the / key, because it’s a throwback to the Lotus 1-2-3 days, the Microsoft Office Excel menu key allows you to specify a key to access the commands on the Ribbon.

Lotus Compatibility Settings for

In the Lotus Compatibility Settings for section, you’ll want to make sure that the name of worksheet for which you are intending to change option is selected. The default state is the name of the active worksheet.

Transition formula evaluation, when checked, opens and evaluates Lotus 1-2-3 files without losing or changing information. The default state is unchecked.

Transition formula entry, when checked, converts formulas that are entered in Lotus 1-2-3 release 2.2 syntax to Excel syntax, and makes names that are defined in Excel behave like names that are defined in Lotus 1-2-3. The default state is unchecked.