Nonprofit Accounting Basics

Information Security and Privacy (Part 3)

Topics: 

Summary: The Excel Pivot Table is one of the most widely used data analysis reporting tools. The security concern with using Excel Pivot Tables is keeping unauthorized people from accessing the underlying data source used by the Pivot Table. Here are several key methods to keep in mind when creating Excel Pivot Tables.

Note: This is the third part of a three-part series. Part One | Part Two

One of the most popular features in Microsoft Excel is the data analysis reporting tool called the Pivot Table. All types of organizations, both nonprofit and for-profit, are using this reporting tool to summarize data and acquire meaningful answers from small and large amounts of data.

Let’s say that you are gathering data in Excel to show hours worked on various projects over time. Placing this data in an Excel sheet allows for quick analysis with a Pivot Table. In seconds, you can create subtotals, averages, counts and many other calculations, including showing hours worked by project name and by date (week, month, quarter, or year).

Here is an example of a list with such data.

Click to enlarge

With the cell pointer in the list, you could select the Insert tab on the Excel Ribbon, click the Pivot Table tool, confirm the cell range of the list, and select OK. On the sheet created for the Pivot Table, you would place a check mark next to the fields Project_Name, Hours Worked, and Cost.

The picture below shows the Pivot Table report that would be created.

Click to enlarge

Then you would place a check mark next to the Pay_Period field and right-click on one of the dates in the Pivot Table report.

Click to enlarge

By selecting the command “group” you will launch the Grouping dialog box and then can group the dates into weeks, months, quarters, or years or a combination, such as year and month.

Click to enlarge

In the group dialog box, by selecting year and month, the resulting report would look like this.

Several different Pivot Tables can be created from the same data set.

Security Concerns

The data security challenges with the Pivot Table report become apparent when someone points the mouse pointer at a calculation within a Pivot Table and double-clicks. When you double-click on a calculation, you drill down into the underlying detail rows. Excel will automatically create a separate sheet tab and will list the rows used by that calculation. Excel copies the rows from the original data source. If you double-click on the grand total in the Pivot Table, all the rows in the data source will be listed on a new sheet tab.

Drill-down can be a very useful tool to discover what detail underlies a calculation, but, if used by an unauthorized person, drill-down could lead to unauthorized access and display of data that may not be intended for viewing.

The Problem With Pivot Table Drill-Down

The Pivot Table technology is decades old. When creating an Excel Pivot Table, the data is loaded into a hidden part of the Excel file called the Pivot Cache. When you double-click and drill-down on a calculation into its underlying detail, the underlying detail is copied from the Pivot Cache to a sheet. Therefore, even if you delete the sheet in the Excel file holding all the source data, the Pivot Cache is still full of the data, and the double-click drill-down still works and results in copying out the rows of data onto a new sheet showing what composes the calculation.

Solutions to Maintain Control of the Source Data

If you are using a Pivot Table to report results and others need to see the results, do not send a sheet tab with a live Pivot Table unless it is being sent to someone who has authority to perform drill-down and see the underlying data.

If you need to send the Pivot Table information to someone and you do not want that person to access the underlying data, then you can use one of the following options:

  1. Create a PDF from the Excel sheet by selecting:
    • File, Export, Create PDF (Excel 2013)
    • File, Save and Send, Create PDF (Excel 2010)
    • Office Button, Prepare, Create PDF (Excel 2007).
    • In addition, set the PDF page settings first in Print Preview. 
  2. Grab a screenshot picture with the Snipping Tool. Use the Windows Snipping Tool to grab a screen shot of the Pivot Table report to send via email. To do this, while viewing your Pivot Table on the screen, launch the Snipping Tool. In Windows 7 or 10, it can be found by clicking the Start button, All Programs, Microsoft Office Accessories, Snipping Tool. In Windows 8 you can find the Snipping Tool in the list of applications. Once opened:
    • Click New and then click and drag a rectangle shape containing the information for your screen shot.
    • Next, within the Snipping Tool user interface, select the email tool and email recipient. An Outlook email will be generated with the snipping tool picture in the body of the email message.
    • The Snipping tool also has annotating tools, including a highlighter and marker to draw on your picture.
  3. Copy and paste to values. Click and drag the range of cells of the Pivot Table, select Copy, start a new Excel file or sheet, right-click on cell A1 and select Paste Special, Values. The result will be a sheet with the Pivot Table content typed into the cells and no interactivity or ability to drill-down.
  4. Clear the Pivot Cache. Keep the Pivot Table and clear the Pivot Table’s Cache of all the data. To do this:
    • First delete the sheet in the file with the source data.
    • Then right-click on the Pivot Table and select Pivot Table Options. In the Options dialog box, on the Data tab, uncheck “Save source data with file.” Select OK.
    • Then right-click the Pivot Table and select Refresh.
    • Save, close, and reopen the file. The Pivot Table is now disconnected from the data.
    • You may want to save the file under a different name before disconnecting the Pivot Table from the data so that you keep your original work.

Most Excel users enjoy the speed, the calculations and the simplicity of the Pivot Table features but are not aware of the Pivot Cache. Inform your colleagues to not send live Pivot Tables to people not authorized to view all the underlying detail data.