Pivot Table Security, Limits,
and Performance
I
f other people have access to your pivot table, you may want to disable some of the features,
address privacy concerns, monitor access to protected data sources, or prevent users from
making some changes. Some pivot table security settings require programming, and are dis-
cussed in Chapter 11. Other settings can be made manually, and are explained here.
When a pivot table is based on external data, Excel’s security settings can affect your ability
to update the pivot table. You can change the security settings, to control the security warnings
displayed when you connect to the file. The external data source may have a password you’d
like to store in your Excel file, to make it easier to connect.
Pivot tables can summarize large amounts of data, but a few limits exist to what can go
into different areas of the pivot table layout. When you’re working with very large databases,
performance can suffer, and creating or refreshing a pivot table can be extremely slow. This
chapter outlines some of the limits and discusses ways to optimize pivot table performance.
8.1. Security: Storing a Database Password
Problem
Your pivot table is based on a query in an Access database that is password protected, and you
want to use the pivot table without entering the password. Currently, when you open the Excel
file and refresh the pivot table, a Database Password dialog box appears, in which you have to
enter the password AjPze$nZ. This password is hard to remember, and there isn’t room at the
edge of your monitor for another sticky note. This problem is based on the PivotPwd.xlsx
sample file that is connected to a query in the HardwarePwd.accdb database, which is stored in
a C:\_Work folder. If a different folder is used, the connections will be broken. Depending on
your security settings, you may see a security warning when opening some sample files. To
work with the file, you can enable the data connections.
Solution
In the Excel file that contains the pivot table, you can change the connection properties, and
store the password in the connection string:
155
CHAPTER 8

1. Open the PivotPwd.xlsx file, and refresh the pivot table, entering the password when
prompted.
2. Select a cell in the pivot table, and on the Ribbon, click the Data tab.
3. In the Connections group, click Properties.
4. On the Definition tab, add a check mark to Save Password.
5. In the alert message that appears, you are warned that the password is saved without
encryption in the Excel file, making your data less secure. To save the password, click
Yes. The password is now visible in the connection string (see Figure 8-1).
Figure 8-1. Database password visible in connection string
6. Click OK, to close the Connection Properties dialog box.
■
Tip
With the database password saved in the connection string, you won’t be prompted for the password
when you open the file and refresh the pivot table. However, the database password is visible in the connec-
tion string, so you should store the Excel file in a secure folder on the network.
8.2. Security: Enabling Data Connections
Problem
You created a workbook with a pivot table connected to an Access database, and when you
open the workbook, you see a security warning in the message bar, above the formula bar (see
Figure 8-2). When you refresh the pivot table, a security notice is displayed, warning that
Microsoft Office has identified a potential security concern.
Figure 8-2. Security warning in the message bar
You trust the data source, and you would like to work with the pivot table file without see-
ing the warnings. This problem is based on the PivotShipment.xlsx sample file that is
CHAPTER 8
■
PIVOT TABLE SECURITY, LIMITS, AND PERFORMANCE156
connected to a query in the Shipment.accdb database, which is stored in a C:\_Work folder. If a
different folder is used, the connections will be broken.
Solution

You can change the data connection settings in Excel’s Trust Center, to hide the message bar,
and to allow data connections. These are application level settings, and they affect all work-
books you open.
1. Click the Microsoft Office button, and then click Excel Options.
2. Click the Trust Center category, and then click Trust Center Settings.
3. To hide the message bar, click the Message Bar category, and then click Never Show
Information About Blocked Content.
4. To change the connection settings, click the External Content category.
5. In the Security Settings for Data Connections section, click Enable All Data Connec-
tions.
6. Click OK, to close the Trust Center, and then click OK to close Excel Options.
■
Note
If you prefer not to enable all data connections, you could click Prompt User about Data Connec-
tions, in the Security Settings for Data Connections section. Then, refresh the pivot table by using a macro
that runs automatically when the workbook opens, and the warning will not appear. See Chapter 11 for
sample code to refresh a pivot table.
8.3. Protection: Preventing Changes to a Pivot Table
Problem
You want to prevent users from making any changes to the pivot table. They should be able
to view the pivot table, but not change the selected items, type over any of the field names,
or rearrange the layout. However, you want users to be able to make changes to data and
formulas in other areas of the worksheet. This problem is based on the PivotProtect.xlsx
sample file.
Solution
If you protect the worksheet without enabling pivot table use, users won’t be able to modify
the pivot table.
Preparing the Worksheet
When protecting a worksheet, prepare the sheet first by unlocking cells where changes can be
made. Then, turn on the worksheet protection.

CHAPTER 8
■
PIVOT TABLE SECURITY, LIMITS, AND PERFORMANCE 157
To prepare the sheet, follow these steps:
1. Select any cells in which users are allowed to make changes. In the sample file, users
can make changes to cell E2.
2. On the Ribbon, click the Home tab.
3. In the Cells group, click Format. The Lock Cell command is enabled if the active cell is
locked (see Figure 8-3). To unlock the cells, click Lock Cell.
Figure 8-3. The Lock Cell command is enabled.
Protecting the Worksheet
After you prepare the worksheet, follow these steps to protect the worksheet:
1. On the Ribbon, click the Review tab, and then in the Changes section, click Protect
Sheet.
2. If desired, enter a password. If you don’t enter a password, the worksheet will be pro-
tected, but it can be unprotected simply by clicking Unprotect Sheet on the Review tab
on the Ribbon.
3. Remove the check mark from Use PivotTable Reports, and then check the items you
want enabled on the protected worksheet (see Figure 8-4).
Figure 8-4. Select items to enable on a protected worksheet.
4. Click OK and confirm the password, if one was entered.
CHAPTER 8
■
PIVOT TABLE SECURITY, LIMITS, AND PERFORMANCE158
How It Works
When you protect the worksheet, you can allow or disallow Use PivotTable Reports. With
either choice, if the worksheet is protected, use of the pivot table is affected.
Not Allowing Use PivotTable Reports
If the worksheet is protected, and Use PivotTable Reports was not selected in the Protect Sheet
dialog box, users won’t be able to make any changes to the pivot tables on the worksheet. For

example, they won’t be able to open the drop-down lists on the pivot field buttons, move
fields, remove fields, or add fields. The PivotTable Field List will be hidden.
You can create a pivot chart from a pivot table on the protected sheet, but you won’t be
able to change the pivot chart layout or use the PivotChart filter. You can change the pivot chart
formatting, chart type, and chart options. If you allow users to Edit Objects on the protected
sheet, a pivot chart can be inserted on the protected sheet. If Edit Objects was not checked, you
can create an empty chart on an unprotected sheet, and change its source to the pivot table.
Other pivot tables, based on the same Excel Table as the pivot tables on a protected sheet,
will have some features disabled, such as Refresh.
■
Tip
If you use worksheet protection to disable the pivot table, many other features of the worksheet are
also disabled, such as AutoSum, Spelling, Subtotals, and Creating or refreshing a pivot table. If these fea-
tures are required on the worksheet, you may prefer to use programming to protect the pivot table, while
leaving the worksheet unprotected.
Allowing Use PivotTable Reports
If you enable pivot table use when protecting the worksheet, users can open the drop-down
lists on the pivot field buttons, move the fields, remove fields, and add fields. The PivotTable
Field List can be shown. Users can work with existing pivot tables, but they can’t create a new
pivot table on the protected sheet or refresh existing pivot tables.
■
Note
When enabling PivotTable report use, you can’t control which pivot table features are allowed. If you
want to enable some features, such as selecting items from the Pivot field drop-down lists, and disable other
features, such as changing the layout, you can use programming, as described in Chapter 11.
Even with the Use PivotTable Reports setting turned on, many commands are disabled if
the worksheet is protected, including the following:
• Report Layout
• Refresh
• Group and Ungroup

CHAPTER 8
■
PIVOT TABLE SECURITY, LIMITS, AND PERFORMANCE 159