Pre-release Considerations

Quick reference

• 00:04 In this video, we're going to look at some of the things that you should consider
• 00:08 before you actually send your pivot table out to the wilds.
• 00:11 Now in this case here,
• 00:12 I have a pivot table in a file called Pre-release Considerations, and
• 00:17 it's linked to an Access database called prc.accdb.
• 00:21 And if I right-click on this guy here,
• 00:23 you'll notice that it'll refresh, no problem.
• 00:25 And I can slice and dice the data and
• 00:27 do kinda different things with it and leave myself in a situation where things,
• 00:31 columns, are moving all over the place and different stuff.
• 00:34 So there's some things that I wanna look at of how to structure things first
• 00:37 to make sure that they're not gonna do that.
• 00:39 Number one, the thing I want to consider is,
• 00:41 on my PivotTable Options tab, do I want to auto-fit column widths on update?
• 00:46 If I want to preserve my formatting, I certainly don't.
• 00:49 So, we'll uncheck that guy.
• 00:51 I want to look at my slicers.
• 00:54 I'm going to hold down my Ctrl key and click all these guys,
• 00:57 I'm also going to click my chart.
• 00:58 I"m going to right-click on this and say Size and Properties.
• 01:02 And go to Properties.
• 01:04 And I don't want to move or size with cells.
• 01:07 That's one of the big things that I'd like to make a change on, here.
• 01:10 Because now, when I go through, and
• 01:12 I start drilling away into things, even though I'm getting into different areas,
• 01:16 it doesn't actually push things all over the place.
• 01:20 The other thing that's really important to me and I showed you in the show details
• 01:24 video that we could actually rip off all the fields on the pivot table.
• 01:29 And we could actually go and extract the entire data set.
• 01:32 So I'm going to, I'm not going to do that with this particular one right now, but
• 01:37 I'd like to prevent that ability.
• 01:39 So in the PivotTable Options,
• 01:41 the first thing I'm going to do is go to the Data tab here and
• 01:44 I don't want to let people accidentally click the show details pieces.
• 01:48 But that's not enough to stop them because even
• 01:51 if they open up the database they can still double-click on something or
• 01:55 they could turn this back on, double-click on it and expand all the records.
• 02:00 What I do want to do is I want to uncheck this save source data with a file.
• 02:05 Now we'll say OK to this, and I'm gonna show you the differences here.
• 02:09 I'm gonna hit Save As.
• 02:11 And I'm gonna save this a Pre-Considreations-Complete.
• 02:17 And now what you'll notice is that I can refresh the data, no problem, and
• 02:21 I can slice it and dice it, no problem.
• 02:25 Let's close this file.
• 02:29 And don't need to save it again.
• 02:32 What I'm going to do is I'm gonna go and
• 02:34 change the name on the Access database to make it unavailable.
• 02:38 There we go. Now we'll come back into
• 02:40 Pre-Release Considerations.
• 02:42 Can I right-click, can I refresh the data?
• 02:45 Nope can't find it, okay.
• 02:47 Well, that's not so good.
• 02:48 It also throws up, and unfortunately on my other monitor.
• 02:51 This little window here, I'm just gonna cancel that, get rid of it.
• 02:56 But even though I can't refresh the data, watch what happens now.
• 03:01 When I go and rip all these information off,
• 03:03 this is the file that I never turned off to show details feature.
• 03:10 There's 2,500 records that came from the database,
• 03:12 even though I can't access the database cuz it's not there.
• 03:17 So that's why we wanna make sure that we control this data because even though I've
• 03:21 connected to an Access database that a user doesn't have access to get to,
• 03:24 they can still extract those records if I don't protect it properly.
• 03:28 So now I'm gonna go to the one set all those settings on.
• 03:31 This is Complete and it says, the External Data Connections have been disabled.
• 03:35 That's fine, we'll enable those.
• 03:37 The question I've got right now is, okay, can I refresh the data?
• 03:43 Nope, can't find it.
• 03:44 Fair enough.
• 03:45 And again, I've got my nice little window over here.
• 03:47 Cancel that.
• 03:48 Can I slice my data?
• 03:50 No, and this is the kind of good thing and bad thing.
• 03:53 If your data is sliceable, it resides inside the pivot cache, which is internal
• 03:58 in the workbook, which means the data is there and can be accessed in its raw form.
• 04:04 If it's not sliceable, we now have a very static report.
• 04:09 A user can look at this,
• 04:10 but I know that they can't make any changes to it whatsoever.
• 04:13 They can't filter by date, they can't go into alcohol,
• 04:16 they can't double-click on the records, nothing will change.
• 04:20 So this is one of the reasons why,
• 04:23 when we're working with our pivot table, now, look at that.
• 04:26 It won't even let me in to modify the formats on this thing at all.
• 04:29 So this is one of the reasons why when we're working with our file,
• 04:34 we wanna make sure that we actually set those settings.
• 04:37 Now, here's the next piece.
• 04:38 Let's go File > Close.
• 04:41 What if the user did have access to that database?
• 04:43 Have I completely hosed them all forever?
• 04:46 Let's rename this guy back.
• 04:48 Yup.
• 04:49 We'll go back to Complete, that's the one that didn't work in the past.
• 04:52 Let's go slice it.
• 04:54 Nope, we need to refresh the data, okay.
• 04:57 Refresh.
• 04:59 Let's go slice it, so there you go.
• 05:01 If you have access, you can refresh the data.
• 05:04 But as soon as that workbook's saved, it's cleared out so
• 05:07 it can't fall into the wrong hands.

Lesson notes are only available for subscribers.