Clever Clogs tip # 740
1 April 2024


How (not) to send a PivotTable report to a third party (Microsoft Excel) *

Many, many moons ago (to be precise on 19 July 2013 and Edge's Copilot tells me there have been approximately 130 full moons between now and then) I wrote a tip about how you can see the data behind a specific figure in a PivotTable report ... all you have to do is double-click any of the values in the PivotTable report to see the details. Or if you are a "right-clicker" ... Show Details.

But as Gary Knott recently pointed out on LinkedIn and in his book "Avoid Excel Horror Stories" it should come with a warning!

When you create a PivotTable you simply hide the data. It even comes along when you copy and paste the report in a separate workbook. Worse . when you send it to someone else, the recipient can also use the double-click trick to reveal the underlying data. Even if you did not send the file!

And yeah, I know it's 1st April but this is definitely not a joke ... The Information Commissioner's Office (ICO), UK's independent authority set up to uphold information rights in the public interest, first highlighted the issue back in 2013 after it fined two public bodies for breaches resulting from the use of PivotTables.

So, spread the word! I feel it's so important that I decided to write a separate tip about it, rather than simply updating tip_399.php as I normally would have done. (I'll add a warning to it though.)

Herewith two workarounds:

If you want to send it as an Excel file :

  1. Click anywhere in your PivotTable report, press CTRL + A followed by CTRL + C.
  2. Press CTRL + SHIFT + V (a relatively new keyboard shortcut in Excel to paste values only).

Alternatively, save it as a PDF :

  1. If necessary, format the report such as its column width, because "What You See Is What You Get" (WYSIWIG).
  2. Click the File tab and select Save As. (Or press F12.)
  3. If necessary, type a File name.
  4. Press TAB and type P (or select PDF *.pdf from the drop-down list).
  5. Press ENTER or click Save.

With thanks to Magalie for the suggestion to save it as a PDF. I hadn't thought of that!

Related tips

  1. See the data behind a specific figure in a PivotTable report - tip_399.php
  2. Save the current document, presentation or worksheet as a PDF - tip_480.php

Last month's CleverClogsTipTime on LinkedIn (newest posts first)

  1. Toggle between classic Outlook and the new Outlook
  2. You don’t know what you don’t know
  3. Organize your inbox by Conversation, then by Categories"
  4. Cannot bear Copilot's constant suggestions on Edge's Search page?
  5. See my working day and upcoming tasks on the right-hand side at the same time as responding to my emails

K.

PS Perhaps I should sit down and finally read Gary's book that I bought in 2021?!


* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 11 users, but might also be useful in Office 2010, 2013, 2016 and 2019.