Locked lesson.
About this lesson
Items to consider before releasing your file to users.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
prc.accdb964 KB Pre-release Considerations.xlsx
61.3 KB Pre-release Considerations - Completed.xlsx
40.3 KB
Quick reference
Pre-release Considerations
Items to consider before releasing your file to users.
Where/when to use the technique
When you want to review your file to make sure that you haven’t given too much access to your data.
The biggest consideration
Is the audience authorized to see your raw data?
- If not, then don’t send a full PivotTable!
- If your file can be sliced, the data is there and can be extracted IN FULL
Best practices
Clear the data cache from the file
- Go to PivotTable Options > Data > Uncheck “Save data with file”
- This will clear the PivotCache upon saving
- The pivot will still show on the worksheet, but it won’t be sliceable unless the user can connect to the data source
Force the data to be up to date
- Stale data can be dangerous in many situations
- Go to PivotTable Options > Data > Check “Refresh at open”
- Does the user have proper authorization credentials to connect?
- Yes: The data will silently be updated
- No: The data refresh will fail with an error message
Turn off the Show Details feature
- It’s very easy to litter your workbook with extra worksheets that clutter the story
- Turning off this feature will prevent accidental drilling
- Go to PivotTable Options > Data > Uncheck “Enable Show Details”
Preserve your formatting work
- Consider preserving PivotTable column widths and slicer positions
- PivotTable Options > Layout & Format > Uncheck “Autofit column widths on update”
- Right-click the Slicer > Size & Properties > Properties > Don’t move or size with cells
- 00:04 So we built a perfect pivot table solution, we got everything formatted
- 00:07 exactly that way we want, we want to send it out the door to our end users.
- 00:11 But the question you've gotta ask is, is it ready?
- 00:14 And there's a couple of things that we want to check along the way.
- 00:17 This particular guy here is built against an access database.
- 00:21 And I'm going to prove that it does refresh.
- 00:22 Now, I'm going to go hit the Refresh All button, and you're going to be able to see
- 00:26 that it refreshes because you're going to watch my pivot table has some problems.
- 00:30 I didn't set this thing up to say, let's force our column widths.
- 00:34 Even though the data hasn't changed, it's still done a refresh, this isn't cool.
- 00:38 And this is one of the first things that you're going to want to look at is have I
- 00:42 set all of my stuff up correctly so
- 00:44 that when it refreshes it still maintains the view that I want?
- 00:47 For example, if I right-click Pivot Table Options,
- 00:51 have I set my autofit column what's on update properly?
- 00:54 Have I masked the errors that I might not want people to see?
- 00:57 Maybe I prefer to see a 0 or for empty cells show 0, so
- 01:01 that when I actually end up releasing this these holes are also filled in.
- 01:06 Have I selected all of my slicers, my timelines, and even my charts?
- 01:11 And gone right-click, Size and Properties.
- 01:14 Let's check the properties and see, did I set it to say don't move or
- 01:17 size with cells?
- 01:18 And this is interesting because this isn't just a property for slicers and timelines,
- 01:22 it also affects charts, they can move around too.
- 01:25 So now this is all set up nicely.
- 01:27 Hopefully at this point, if I do a refresh,
- 01:29 everything should stay exactly where it's supposed to be, and that's good.
- 01:34 Now, there's other options you may also want to toggle.
- 01:37 Do you want to protect your users from accidentally clicking on
- 01:41 the Enable Show Details?
- 01:43 Now, I'm going to leave this one here as is for right now.
- 01:46 And I'm just going to say OK, and what I'm going to do is I'm going to say File >
- 01:51 Save As, and I'm going to save this as Pre-release Considerations- Complete.
- 01:57 And here's the thing,
- 01:58 what I'm going to do at this point is I'm going to say File > Close.
- 02:03 And I'm going to go back to the actual folder that has the database.
- 02:06 And I'm going to rename it to .old, so that it can't be found.
- 02:13 And I'm going to reopen my Pre-release Considerations.
- 02:15 So this is the completed version, I'll enable the content.
- 02:20 And at this point in time, the question is, does it still work?
- 02:23 Can I still slice it?
- 02:24 Yes, I absolutely can.
- 02:26 Even though the data source cannot be found, I can still slice it.
- 02:30 Can I double-click and look at my show details?
- 02:32 Yes, I can.
- 02:34 All of this stuff still works.
- 02:36 So the question is can I change this?
- 02:39 Can I do better?
- 02:40 Because this is okay if I send it to somebody inside the organization.
- 02:45 But what we know about this is that if I rip all the fields off the pivot table
- 02:49 except for one value, if I sent this to one of my suppliers,
- 02:53 they can get all of the underlying data and that's not cool.
- 02:56 So here's what I'd like to do.
- 02:58 I'd like to go back and say, let's change one of the options on the pivot table, and
- 03:03 I'm going to just change one.
- 03:04 And that is this save data with a file.
- 03:06 We're going to say, OK, we're going to say Save, File,
- 03:12 close, and now File, and we're going to open it right back up.
- 03:18 And what you'll see at this point is that everything still looks fine.
- 03:22 But when I try to slice it, it says, hey, you saved this without the underlying data,
- 03:26 you can't do that you need to refresh.
- 03:29 I can't use any of these things and drill down.
- 03:31 It also gives me the same thing.
- 03:33 So what I'm going to do right now is I'm going to say all right,
- 03:36 how can I change the properties of this particular guy?
- 03:39 Pivot table options, it won't even let me change that.
- 03:42 So I'm going to need to very quickly come back here and say,
- 03:46 let's go grab this guy, oops, cancel that, need to rename him, there we are.
- 03:54 So that the database is now available again, we'll come back to Excel, data,
- 03:59 Refresh All, make sure that the data is actually reconnecting.
- 04:02 Here it goes, it's connected to the data source.
- 04:05 It'll stream the data in.
- 04:06 And now I'm going to do one better.
- 04:09 I'm going to go back to data.
- 04:10 We're not going to save the source data with a file, but
- 04:13 we are going to choose to refresh when opening the file.
- 04:16 And at this point, what you're going to
- 04:20 see is that I can now hit Save > File > Close.
- 04:25 I'm going to go back once again.
- 04:27 I'm going to right-click rename this to .old.
- 04:34 Now, what we're doing is we're actually replicating the process at this point
- 04:39 in time of saying,
- 04:40 let's send the database to somebody that's outside of our organization.
- 04:45 And they say, hey, we couldn't find the file, it's tried to refresh it, that's it.
- 04:48 Can you use it?
- 04:49 Nope, you can't use it at all.
- 04:52 But if we send it to somebody inside the organization, I say Close, Don't Save.
- 04:59 We're going to go back and we're going to make the file available again.
- 05:03 So this is someone inside my organization now because they have access to
- 05:07 the database source.
- 05:09 When I now go back into this file, it will now open and
- 05:13 you can see here it is running it's refresh right away.
- 05:17 And, boom there we go, we can now slice, we can now filter,
- 05:21 We can drill down, we can see what we need to see.
- 05:24 This is the proper way to actually go and set your files up, especially if you're
- 05:28 sending them outside your organization is to hit your Pivot Table Options,
- 05:33 go to the Data tab, don't save the source data within the file.
- 05:36 Make sure you refresh on opening and the enabling show details,
- 05:40 that is entirely up to you.
- 05:42 But the key thing is with this, nothing goes.
- 05:45 Basically what you're sending is a picture of a PDF.
- 05:47 And unless the end user can actually have the credentials to refresh it, they will
- 05:51 not be able to get your underlying source data, so that protects it.
- 05:54 And to be honest with you, in my opinion,
- 05:56 that's the way the default should actually be set.
Lesson notes are only available for subscribers.