Share via

  • Article

Configure Group Policy settings for Excel 2007

Updated: May 17, 2007

Applies To: Office Resource Kit

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

Group Policy is an infrastructure that enables administrators to implement specific computing configurations for groups of users and computers. Policy settings can also be applied to member servers and domain controllers within the scope of an Active Directory forest. Group Policy settings are contained in Group Policy objects (GPOs), which are linked to selected Active Directory containers: sites, domains, or organizational units (OUs). The settings within GPOs are evaluated by the affected targets using the hierarchical nature of Active Directory.

Administrators can use Group Policy to specify configurations for Microsoft Office Excel 2007 for users. Excel 2007 includes policy settings for managing the following areas:

  • Excel Options

  • Customizable Error Messages

  • Data Recovery

  • Data Access Security

  • New Spreadsheet Links

  • Disable items in user interface

  • Block file formats

  • Miscellaneous

The Excel 2007 policy settings are located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007 node in the Group Policy Object Editor Microsoft Management Console (MMC) snap-in. To manage these policy settings, you must first load the Excel 2007 Administrative Template file, Excel12.adm, into Group Policy Object Editor.

For information about Administrative Template files, see Administrative Templates in Group Policy overview (2007 Office system).

For information about Group Policy Object Editor, see Group Policy Management Tools in Group Policy overview (2007 Office system), and Using Group Policy Management Console and Group Policy Object Editor in Enforce settings by using Group Policy in the 2007 Office system.

You can download the Administrative Template files for the 2007 Office release from 2007 Office System Administrative Templates (ADM) (https://go.microsoft.com/fwlink/?LinkId=78161) in the Microsoft Download Center.

Configuring settings with the Office Customization Tool and Group Policy

Administrators can configure settings for managing Excel 2007 options by using the Office Customization Tool (OCT) or Group Policy. Although both Group Policy and the OCT are used to configure user settings, there are important distinctions.

  • The Office Customization Tool is used to create a Setup customization file (MSP file). Administrators can use the OCT to customize features and configure user settings. However, users can modify most of the settings after the installation. This is because the OCT configures settings in publicly accessible portions of the registry, such as HKEY_CURRENT_USER/Software/Microsoft/Office/12.0. For more information, see Office Customization Tool in the 2007 Office system.

  • Group Policy is used to configure the 2007 Office release policy settings contained in Administrative Templates, and the operating system enforces those policy settings. True policy settings are written to the approved registry keys for policy, and these settings have access control list (ACL) restrictions that prevent non-administrator users from changing them. If you want to enforce specific Excel settings, use Group Policy to configure policy settings. For more information about Group Policy, see Group Policy overview (2007 Office system).

Excel Policy Settings

The following sections provide a brief description of the types of Group Policy settings available for Excel 2007 and instructions for configuring policy settings.

Excel Options folder

The Excel Options folder contains several sub-folders which include policy settings for managing workbook features, formulas, proofing, security options, and advanced features.

This folder contains policy settings for managing Live Preview and for specifying whether to show all open workbooks on the taskbar, the initial number of worksheets in a workbook, standard fonts, and hiding the Mini Toolbar. The Popular folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Formulas folder

This folder contains policy settings for checking or clearing the R1C1 reference style option. R1C1 reference style is used for computing row and column positions in macros. The Formulas folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Proofing folder

This folder contains policy settings for making an adjacent row or column part of the table that you are currently working in, and for enabling the Replace as You Type option for hyperlinks. The Proofing folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Save folder

This folder contains policy settings for controlling the following options:

  • Default working folder location.

  • Format for Save Excel files, such as: Excel Workbook (.xlsx), Excel Macro-Enabled Workbook (.xlsm), Excel Binary Workbook (.xlsb), Web Page (*.htm, *.html), Excel 97- Excel 2003 Workbook (.xls), and Excel 5.0/95 Workbook (.xls). For information about setting the default file save options for Microsoft Office Excel 2007, see Using Group Policy to set default file save options.

  • Prompting for workbook properties.

  • Saving AutoRecover information, AutoRecover time, AutoRecover save location, and AutoRecover delay.

  • Disabling the AutoRepublish feature and whether to display the AutoRepublish warning alert.

The Save folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Security folder

This folder contains policy settings for Trust Center and Trusted Locations, and other security areas. Administrators can configure the following policy settings:

  • Disable all application add-ins. Add-ins are enabled by default. When this policy setting is enabled, add-ins are disabled. Users are not notified that add-ins are disabled.

  • Disable Trust Bar Notification for unsigned application add-ins. When this policy setting is enabled, Office applications silently disable any DLL that contains an application add-in which does not have a digital signature.

  • Require that application add-ins are signed by Trusted Publisher. When this policy setting is enabled, Office applications check the digital signature on the .DLL that contains an application add-in. The applications display a security notification if the DLL is unsigned or the DLL is signed by a publisher's certificate that is not in the Trusted Publishers list.

  • Store macro in Personal Macro Workbook by default. When macro recording is started, the user is asked whether the macro should be stored in this workbook, in a new workbook, or in a personal macro workbook. This policy changes the default setting.

  • Suppress High Security macro alert for unsigned Macros. When the security level is set to high, unsigned macros do not run. This setting controls whether or not an alert displays when a macro is blocked from running because it is unsigned.

  • Trust access to Visual Basic project. When this setting is enabled, users can use Visual Basic code to change the Visual Basic project that is associated with a workbook (for example, users can programmatically insert a code module).

  • VBA Macro Warning Settings. This policy setting allows administrators to configure what notifications display for VBA macros.

  • Trusted Locations. This folder contains several policy settings that administrators can use to control the behavior of trusted locations:

    • Allow Trusted Locations not on the computer. By default, trusted locations that are network shares are disabled. However, users can use the Trust Center graphical user interface to select the Allow Trusted Locations on my network check box. Enabling the Allow Trusted Locations not on the computer policy setting disables trusted locations that are network shares and prevents users from selecting the Allow Trusted Locations on my network check box in the Trust Center graphical user interface.

      If this policy setting is set to Disabled and a user attempts to designate a network share as a trusted location, a warning informs the user that the current security settings do not allow the creation of trusted locations with remote paths or network paths. If an administrator designates a network share as a trusted location through Group Policy or by using the OCT and this setting is Disabled, the trusted location is disabled and is not recognized by an application.

    • Disable all trusted locations. By default, trusted locations are enabled. When this setting is set to Enabled, it disables all trusted locations. This includes trusted locations that are created by default during Setup, those created by users through the graphical user interface, or those deployed by using Group Policy. Enabling this setting also prevents users from configuring trusted locations settings in the Trust Center.

    • Trusted Location #1 through Trusted Location #20. These policy settings are used to specify trusted locations for Excel.

    Note

    During installation, several trusted locations are specified by default. These default trusted locations do not appear in Group Policy Object Editor or in the OCT. For information about default trusted locations, see "Default settings for trusted locations" in Evaluate default security settings and privacy options for the 2007 Office system.

The Security folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

For detailed information about planning and managing Trusted Locations and Trust Center and other security areas for Office 2007, see the following resources:

Advanced folder

The Advanced folder contains policy settings used for managing Advanced options.

In Excel 2007, many of the less frequently used settings for features such as compatibility, editing, printing, Web options, and saving have been placed in the Advanced category in the Excel Options dialog box. You can see the Advanced settings in the Excel 2007 user interface by clicking the Microsoft Office Button, clicking Excel Options, and clicking Advanced. The settings are organized by feature category.

The Group Policy settings in the Advanced folder are used to control many of the Excel Advanced user interface settings.

For example, the policy settings under the Web Options folder in Group Policy Object Editor are used to set options that specify how Excel data looks and responds when the data is viewed in a Web browser. The policy settings manage the corresponding user interface options on the General tab of the Web Options dialog box.

The Advanced folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Customizable Error Messages folder

This folder includes a policy setting for specifying a list of error messages to customize.

The Customizable Error Messages folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Data Recovery folder

This folder contains the following policy settings:

  • Assume structured storage of workbook is intact when recovering data. If this setting is enabled and an Open and Repair operation is in progress, Excel assumes that the structure of the workbook is not corrupted and tries to recover the entire workbook, including formulas, formatting, and Microsoft Visual Basic for Applications (VBA) projects.

  • Corrupt formula correction. When Excel tries to recover formulas in an Open and Repair operation, Excel converts a formula to a value or to #REF or #NAME if the recovery does not succeed. This setting also affects the default option when the user is prompted to recover formulas during the Open and Repair operation.

  • Do not show data extraction options when opening corrupt workbooks. If this setting is enabled and the user chooses to Open and Repair a workbook, Excel does not offer the user any options and opens the file by using the Safe Load process.

The Data Recovery folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Data Access Security folder

This folder contains policy settings for managing query refresh options, location of connection files, and settings for displaying warnings and alerts when connecting to or refreshing data. The Data Access Security folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Specifying Automatic Query Refresh settings

Users can use a query in Excel to retrieve data from corporate databases and files that they want to analyze in Excel. They can also use PivotTables reports to summarize, analyze, and present summary data. Users can specify PivotTable options to control whether to refresh the data when Excel opens the Excel workbook that contains the PivotTable report.

Users can specify options to refresh an external data range automatically when a workbook is opened by using the Connection Properties dialog box in Excel.

Administrators can use the Automatic Query Refresh policy setting to specify how Excel behaves when it opens a file that contains a query or PivotTable that is configured to refresh automatically. Administrators can specify one of the following options:

  • Default: Prompt for all workbooks.

  • Do not prompt; do not allow auto refresh.

  • Do not prompt; allow auto refresh.

Specifying the location of connection files

Excel workbook data can be stored directly in the workbook or in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. A data connection—information that describes how to locate, log in, and access the external data source—is used to connect external data sources to the workbook. The connection information can be stored in the workbook or in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).

Administrators can store a set of connection files within a folder (UNC or URL) on the corporate network, and then publish the location of these connection files to users across the network by using the Connection File Locations policy setting. If this policy setting is set to Enabled, you can make the connection files available to users by specifying a Name and a Location (UNC or URL) value for each set of connections. If no connection file location is specified in this policy setting, the Existing Connections dialog box in Excel has a blank list in the Connection Files on the Network section.

Configuring policy settings for displaying warnings and alerts

The Data Access Security folder contains the following policy settings for configuring warnings and alerts for data connect and refresh operations:

  • OLAP PivotTable connect warning. This setting specifies to warn the user about an OLAP (Online Analytical Processing) data connect operation.

  • PivotTable external data connect warning. This setting specifies to warn the user about a connect operation.

  • Refresh alert settings. This setting specifies to alert the user about a Refresh Data operation.

This folder contains policy settings Custom link #1 through Custom link #10. If these settings are enabled, administrators can configure hyperlink options such as display name and path, section of the work pane in which to display the link (for example, open a spreadsheet), and actions such as opening an existing file or creating a new file.

The New Spreadsheet Links folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Disable items in user interface folder

This folder contains policy settings for disabling predefined user interface commands and shortcut keys for Excel.

Administrators can also specify policy settings to disable commands, menu items, and shortcut keys for Excel by specifying the toolbar control ID (TCID) for the Excel controls. These policy settings are available in the Custom folder.

The Disable items in user interface folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

For more information, see Disable user interface items and shortcut keys.

Block file formats folder

This folder contains policy settings for blocking file open settings and file save settings. Administrators can use the block file format policy settings to prevent users from opening or saving various file types and file formats. The Block file formats folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

For detailed information about block file format settings, see Block file format settings in Security policies and settings in the 2007 Office system.

For planning information for blocking file formats, see Plan block file format settings in the 2007 Office system.

Miscellaneous folder

This folder contains the following policy settings:

  • Chart gallery path. Specifies the path where user-defined chart templates are stored.

  • Custom Answer wizard database path. Specifies the path and filename for the custom Help and Answer Wizard (AW) file.

  • Enable four-digit year display. Specifies that Excel always displays four digits when you type a date that includes a four-digit year, which may override the Short date style setting under Regional Settings in Control Panel. If this setting is not enabled, Excel follows the Short date style.

  • Locally cache network file storages. Enabling this setting helps prevent data loss during network failures when editing spreadsheets that are stored on network shares.

  • Locally cache PivotTable reports. Enabling this setting helps prevent data loss during network failures when editing spreadsheets that are stored on network shares.

  • OLAP PivotTable User-Defined Function (UDF) security setting. PivotTable reports can contain OLAP queries with references to User-Defined Functions (UDFs) which may include compiled executable files. Executable files may pose a potential security threat. When you enable this setting, you can specify one of the following options:

    • Allow ALL UDFs. This option allows all UDFs in OLAP queries to execute with no IObjectSafety check.

    • Allow safe UDFs only. This option allows only UDFs where the developer has used IObjectSafety to mark the UDF as a safe executable file.

    • Allow No UDFs. This option disables all UDFs from executing in OLAP queries.

    When you enable this policy setting, Excel passes the selected value to the OLAP provider.

  • Recognize SmartTags. Enabling this policy setting blocks Excel from recognizing SmartTags.

The Miscellaneous folder is located in the User Configuration\Administrative Templates\Microsoft Office Excel 2007\Excel Options folder in Group Policy Object Editor.

Loading the Excel12.adm Administrative Template file and setting Excel policy settings

The following procedures use the Group Policy Object Editor MMC snap-in from the Group Policy Management Console (GPMC) to edit the GPO. The procedures assume you have already installed GPMC. You can download GPMC from the Microsoft Download Center site. See Download Group Policy Management Console (GPMC) for more information. If you are using Windows Vista, GPMC is integrated into the operating system.

For more information and procedures for using the Group Policy management tools, see Using Group Policy Management Console and Group Policy Object Editor sections in Enforce settings by using Group Policy in the 2007 Office system.

The procedures also assume that you have previously downloaded the Excel 2007 Administrative Template file, Excel12.adm. You can download the Administrative Template files for the 2007 Office release from 2007 Office System Administrative Templates (ADM) (https://go.microsoft.com/fwlink/?LinkId=78161) in the Microsoft Download Center.

Important

As with any Group Policy deployment, it is critical to test all new Group Policy configurations or deployments in a non-production environment before you move them into your production environment. For detailed information about staging Group Policy deployments, see Staging Group Policy Deployments (https://go.microsoft.com/fwlink/?LinkId=80755) in the Designing a Managed Environment book of the Microsoft Windows Server 2003 Deployment Kit.

Administrative Templates policy settings provide Explain text, which you can view by clicking the Extended tab in the details pane (right side) of the Group Policy Object Editor console. You can also see this text by double-clicking a policy setting and clicking the Explain tab in the Properties dialog box for the policy setting. Explain text provides information about the policy setting.

To load the Excel12.adm file in Group Policy Object Editor and configure policy settings

  1. Click Start, click Control Panel, click Administrative Tools, and click Group Policy Management to open GPMC.

  2. In the console tree, double-click Group Policy Objects in the forest and domain that contain the GPO that you want to edit. This is located in Forest name, Domains, Domain name, Group Policy Objects.

  3. Right-click the GPO you want to modify and click Edit. This opens Group Policy Object Editor.

  4. In Group Policy Object Editor, right-click Administrative Templates in the User Configuration node, select Add/Remove Templates, and click Add.

  5. In the Policy Templates dialog box, browse to the Excel12.adm template. Click Open and click Close in the Add/Remove Templates dialog box.

  6. Double-click User Configuration and expand the tree under Administrative Templates to find the Microsoft Office Excel 2007 policy settings.

  7. In the details pane (in the right pane), double-click the folders and double-click a policy setting to open the Properties dialog box. Configure the Excel policy settings you want to use and click OK.

  8. Save the GPO.