GoSkills
Help Sign up Share
Back to course

The PivotTable Life Cycle

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Understanding how the life cycle of a PivotTable solution is different than an ad-hoc reporting solution.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

There are no related exercise files for this lesson.

Quick reference

The PivotTable Life Cycle

Overview of how a PivotTable works.

Where/when to use the technique

Understanding how the life cycle of a PivotTable solution is different from an ad-hoc reporting solution.

The PivotTable Life Cycle

Construction

  • Source the data
  • Lay out the PivotTable design
  • Add calculations
  • Polish formatting
  • Release to audience

How Pivot Tables are different from ad-hoc solutions

  • PivotTable-based solutions
    • Built by the developer
    • End user refreshes data
    • End user slices, filters and generate alternate data views
  • Ad-hoc solutions
    • Built by the developer
    • Developer is needed to refresh/update data
    • Developer is needed to slice, filter and generate alternate data views

PivotTable benefits

  • Develop once, refresh many times
  • Allow user interactivity with the data
Login to download
  • 00:04 Before we jump in and start actually building and configuring pivot tables,
  • 00:08 I just like to cover off the main differences between building a PivotTable
  • 00:12 solution and building what I call an ad hoc reporting solution.
  • 00:16 Ad hoc reporting solutions are generally what we're used to where we take some data
  • 00:20 and we use a lot of formulas to manipulate it and update it in the worksheet grid.
  • 00:24 PivotTable solutions are very different.
  • 00:26 When we build a PivotTable solution,
  • 00:28 we actually have a process that we end up going through.
  • 00:31 The very first thing that we're going to do is we're going to go and we're going to
  • 00:35 source our data and then we're going to lay out the pivot table design.
  • 00:41 We'll add some calculations to our pivot table.
  • 00:44 We'll do a little bit of formatting, polishing and
  • 00:47 whatnot to make it look really, really good.
  • 00:49 And then at that point in time, what we'll do is we'll actually release our
  • 00:53 pivot table to our audience to use and that's a little bit different maybe than
  • 00:57 the way that we build some of our original stuff.
  • 00:59 I mean, the overall kind of goal is kind of the same.
  • 01:02 I mean, normally when we're building an ad hoc solution will still source our data
  • 01:05 and we'll build our report, and add our calculations and polish up and
  • 01:08 released to the audience.
  • 01:10 But the big difference is what the pivot table solution gives us next.
  • 01:15 So when we're actually working with a solution that's actually built using pivot
  • 01:20 tables.
  • 01:20 The way that we do that is we'll send our report off to our user.
  • 01:24 They'll open up the file.
  • 01:26 And, when they do that,
  • 01:27 they will go through the process of refreshing the data.
  • 01:30 And, this is the big difference rather than having a report that's static and,
  • 01:35 never changes the user can actually open it up and,
  • 01:38 refresh to pull the latest data from whatever the data sources are so
  • 01:42 that they can understand what's happening now or just happened?
  • 01:47 At that point, the user has the control to go and slice and
  • 01:50 filter the data in the way that they would like to use it.
  • 01:52 So they construct drilling in to the specific areas that they want to see.
  • 01:57 Once they have done that of course they going to make some observations and
  • 02:00 then hopefully if our business process supports it they'll go on act on those
  • 02:04 observations and then this is where the pivot table really changes the game.
  • 02:08 Once they've acted on those observations and
  • 02:10 they want to see what kind of effect they had, they will refresh the data and
  • 02:15 we end up getting into a cycle where they can re-use the same piece
  • 02:19 of infrastructure or the same reporting system.
  • 02:22 Over and over and over again, without always have to sending it back to
  • 02:26 the original report developer to do the updates.
  • 02:29 And this is the big beautiful thing about working with pivot tables,
  • 02:33 driving our business intelligence versus the static report.
  • 02:36 The user has to constantly go and update themselves.
  • 02:39 Now the big differences is that we see in these, in the classic reporting solution.
  • 02:43 Again, we've got a manual report design, which makes sense.
  • 02:47 We've got manual report updates, that's the killer.
  • 02:50 And most of the time when we actually get those report updates,
  • 02:53 we have to go back and we have to modify things to fit.
  • 02:56 Whether it's the source data has to be recleaned again or brought in and
  • 03:00 copied and pasted and
  • 03:01 cut down in order to drive those reports, something new comes out of it.
  • 03:05 It's not in a structure that can be reused.
  • 03:10 And for that reason, the classic report is very much what I call developer driven.
  • 03:13 And I'm not talking about your programmer in the organization.
  • 03:16 I'm talking about the business analyst person.
  • 03:19 He is the developer of that specific report.
  • 03:23 In the case of a PivotTable solution things are a little bit different,
  • 03:25 we design the reports once we refresh it often and often.
  • 03:29 Now, that design is done by our developer, but the refresh,
  • 03:33 the actual digging into the data to see what's going on, by the slicing and dicing
  • 03:37 of it is all user driven, and that is the big benefit of a pivot table right there.
  • 03:42 Is that we can actually use our developers to develop more reports.
  • 03:47 And again, I'm talking about the business analyst or your accountants or
  • 03:49 your engineers that are actually trying to drill in and figure out what's going on.
  • 03:53 They can build that report.
  • 03:55 And then they can send it off to their manager or to their subordinate to say you
  • 03:58 just refresh and this will give you the answers to the questions that you need.
  • 04:01 I'm going to go and
  • 04:02 spend my time developing another analysis that the business needs to understand.
  • 04:06 So that's where pivot tables can really start to add
  • 04:11 to our overall reporting environment and
  • 04:15 why they're so important to business intelligence.

Lesson notes are only available for subscribers.

What is a PivotTable - and Why Do You Care?
04m:17s
Creating Your First PivotTable
05m:30s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement