Locked lesson.
About this lesson
Connecting to external data sources (such as databases), and pulling the data into Excel.
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.
Using Database Data.accdb46.6 MB Using Database Data - Completed.xlsx
26.2 MB
Quick reference
Topic
Pulling database data into Excel and Pivot Tables.
Where/when to use the technique
When part or all of the data you need to build your reports is stored in a database.
Instructions
Sourcing data via “Get External Data”
- Go to the Data tab > From Access
- Browse to your database, select it and click Open
- Choose the table or query you wish to import and click OK
- Choose to land it in a table or a PivotTable
- If you landed it to a table first, go to the Insert menu and add a new PivotTable
- Layout your PivotTable as desired
- Refresh your PivotTable from the database
Key points to remember
- Landing data in a table first:
- You can “see” the data in the worksheet so you can review it.
- You can easily write formulas to manipulate, change or add to the data.
- Means that your data set must be less than 1,048,575 rows.
- Landing data in a PivotTable first:
- You can’t “see” the data in the worksheet so you can review it.
- You can’t easily write formulas to manipulate, change or add to the data.
- Means that your data set must be more than 1,048,575 rows as the PivotTable summarizes it before it lands in the worksheet.
- 00:04 In this video we're going to look at how to get data from a database.
- 00:07 And there's actually quite a few different ways to do this depending on the database
- 00:10 that you're working with.
- 00:11 We're gonna use an Access database that's provided with your downloads.
- 00:15 And the way we're gonna do it is we're gonna start from a completely blank
- 00:18 workbook.
- 00:19 We're gonna go to Data, and we're gonna choose Firm Access.
- 00:24 Now, I'm gonna browse to my desktop, and I have a shortcut here that I
- 00:29 will just click on that opens up this POS or Point of Sale ACCDB.
- 00:33 And that is our database that we're looking at, and
- 00:36 once we open it up we get this interface here that says, here's all of the tables,
- 00:40 and if there were queries, they would show here too, that are in this view.
- 00:44 What would we like to do with it?
- 00:45 Well, the bulk of the data is in the TblChits table.
- 00:49 So I'm gonna click on that and say, OK.
- 00:51 And what you'll see is we now get the option to
- 00:55 pull this into a few different ways.
- 00:59 The two that I wanna focus on are table and pivot table report.
- 01:03 And there's a difference between these two, for sure.
- 01:06 So the first one is if we grabbed this and pulled it into a table,
- 01:09 it would land all of the data right in the worksheet for us.
- 01:13 And you could see it's getting it, it's running it's background and
- 01:15 boom,there we go.
- 01:16 We have all the data from the table.
- 01:18 Now there's a certain set of benefits to landing your data inside an Excel table,
- 01:23 because one of the things we can do is we can actually see the data.
- 01:27 And that's important, it makes us a little bit more comfortable when we're working
- 01:29 with it if we can see what records are there.
- 01:32 The second thing is, is that if we need to make any changes or add any data to this
- 01:36 table, it's really easy to do when we've actually got it right In front of us.
- 01:39 Like let's say for example I wanted to add a column that said Month and
- 01:44 that was equals month of the POS chit date.
- 01:50 Well I can make this modification, write this formula or
- 01:53 any formula that I want against this set of data and now I've
- 01:56 got something that will be pulled right into the pivot source when I go to use it.
- 02:00 So if I were to go in now, and say insert pivot table, and
- 02:04 we can see we've got a range of data, we'll put in on a new worksheet.
- 02:07 At this point in time, I can go and grab my Units and
- 02:11 I could grab the new field that I've written for Month and
- 02:15 I could throw that down there and maybe throw the POS category code down the side.
- 02:19 So, I could see what the breakdown was that was going there.
- 02:22 So, that's really a nice benefit of having this thing in the grid is that I can
- 02:25 modify that table.
- 02:27 Now the other options is that I don't have to do this.
- 02:31 And one of the problems that comes with this is that if this data set were more
- 02:36 than a million rows.
- 02:37 This one's not, this one's only 348,000.
- 02:41 That was pretty quick for 348,000 records, wasn't it?
- 02:44 But if this was more than a million rows, it wouldn't fit into the worksheet, and
- 02:47 that's a problem.
- 02:48 So how would we go about dealing with that?
- 02:51 This is where we can come back and say, let's go and pull the data instead of,
- 02:56 we need to be in a regular blank cell here.
- 02:58 Instead of pulling this into a table, let's go and grab it from Access this
- 03:02 time, and again, we'll go back to Desktop, Sample, pos, there we go.
- 03:07 We'll grab the Chits table again.
- 03:10 Actually we'll grab the Categories table this time, why not, and say OK.
- 03:13 And this time it allows me to pull this directly into a pivot table.
- 03:17 So if this was more than a million rows of data,
- 03:20 I don't have to worry about overflowing the worksheet grid,
- 03:23 because I'm only gonna report the aggregated information.
- 03:27 So now we can say OK, and it says that it can't overlap
- 03:31 another pivot table report because I'm trying to put it on the same worksheet.
- 03:34 So that's not so good.
- 03:35 Let's go back and say let's put this on a new worksheet.
- 03:39 It's important I tried to on an existing worksheet, it can overlap,
- 03:42 that's why that one threw an error there.
- 03:43 So we'll say OK.
- 03:46 There we go, a new worksheet, it creates me a new pivot table frame and
- 03:50 we can now see that I've got all the fields.
- 03:52 Now this is a very small table.
- 03:54 So it's probably not gonna help me a whole lot here, but if I throw a POS
- 03:59 group on here, it'll count the number of POS groups, and if I threw description
- 04:03 over here it will tell me there is one of each this particular tables.
- 04:06 So again, I can even count text fields.
- 04:08 But the big key in this thing to remember is that there is two different approaches
- 04:12 to work with.
- 04:12 If you need to pull in more than a million rows of data
- 04:15 pull it directly into a pivot table.
- 04:17 If you don't you may wanna land it in the worksheet grid so
- 04:20 that you can manipulate it and see it first.
Lesson notes are only available for subscribers.