Do you need to format your Excel Data Set -which has previously been prepared as a printed report- so that it is now capable of being imported into Microsoft Access or some other Database arrangement? Today, in Episode #1436, Bill shows us how to accomplish this task effectively.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1436: Access format.
Well hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I have a great spreadsheet today.
I've redacted all the numbers, so all the numbers are gone, but check this out.
We have data that, you know, this is a nice looking report if a human is going to read it, but not if we want to go to a database.
There's a couple of things.
There's a plant location up here in cell A1 and also a type in A3, which kind of-- so, there's actual and then budget.
So, I'm going to insert two new columns.
The goal here today is to get this into something that we can load into Access or, you know, any kind of a database or something like that.
So, I'm going to copy that plant location down to all of the cells.
Of course, that's what we should do and then copy those headings from each section down to all of the cells.
I'm just doing a straight out copy down there, but then anytime that it changes-- so, here the budgets and I bet we can calculate that with the database, but I'm just going to go ahead and do all that.
All right, so we have those fields over there in A and B. I'll copy and paste values and then, what do we have we have?
We have some extra rows now that we don't really need any more.
Like, for example, these blanks here and also the heading of budget and some blanks here and the heading of variance to budget.
All right, so, that helps us out in columns A, B, and C. Now, going across the top, going across the top we have two rows that I really want to put into one row.
So, I’m going to insert a row here.
So, we'll say equal that date, ampersand-- I’m going to separate it in quotes with the vertical bar.
You could use anything you want.
I just want to-- I like to use the vertical bar because I know it won't ever show up again and copy that across, paste Ctrl C, program key V to convert to-- guys, this is going to be called location.
This is going to be called type.
This is going to be called line item.
So, a lot of copying and pasting there, just, you know, basic stuff, but here is where the real trick starts.
This is a trick that I learned from Mike Alexander during one of our data analysis boot camps.
I'm going to create a brand new field here called key because Mike's trick can only work with one row label.
It can't work with these three.
So, I'm going to join whatever is in A2, ampersand, quote, vertical bar, quote, ampersand, whatever is in B2 and again in quotes, another vertical bar and then whatever is in C2 and we will double-click to copy that down.
All right, now we need to use a multiple consolidation range pivot table.
That's not available on the insert tab anymore, so we have to do Alt D for data, P for pivot table.
Multiple consolidation range, still there in the old style.
We click Next.
I'm going to create the page fields-- actually I'm not going to create any, but I don't want them to create them, click Next and then we simply specify the range from the key column that I created on over.
I just click add and click finish.
All right and this gets me a pivot table not useful at all.
It looks exactly like the original data, although we're missing columns A, B, and C, but it has everything we need.
Now, check this out.
If you go to any cell in a pivot table and double-click, what do you get?
You get all of the records that make up that cell, but in a multiple consolidation range pivot table, if you double-click the grand total, grand total, you get the data spun unwrapped; whatever you want to call it.
Now, instead of having six rows times 20, I have 120 rows.
All right.
This is absolutely beautiful.
How do we now undo?
First of all, let's make it not be a table anymore.
So, convert to range.
This is new in Excel 2007.
Yes, so I come back to normal range and then we'll insert a couple of columns here; two columns because I know I have three fields here.
We do data text to columns, it is delimited.
What is it delimited by?
It’s delimited by that vertical bar character.
We click finish.
Do we want to replace the contents?
Yes, that's replacing the formatting, that's okay.
So, we can ignore this.
This is location.
This is type.
This is line item.
Then, we also have something there that we need to break apart.
So, I'm going to insert one new column, data text to columns, delimited still by type, click finish.
Yes, this is the month and this is the type and we now have data that is ready to roll for Access.
Well, thanks to Mike Alexander from DataPig Technologies for that amazing trick.
Check out Mike's book Pivot Table Data Crunching available in three different editions.
Excel 2010 is the latest edition.
Of course, he has a great, smart co-author with that book.
All right, there we go.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1436: Access format.
Well hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I have a great spreadsheet today.
I've redacted all the numbers, so all the numbers are gone, but check this out.
We have data that, you know, this is a nice looking report if a human is going to read it, but not if we want to go to a database.
There's a couple of things.
There's a plant location up here in cell A1 and also a type in A3, which kind of-- so, there's actual and then budget.
So, I'm going to insert two new columns.
The goal here today is to get this into something that we can load into Access or, you know, any kind of a database or something like that.
So, I'm going to copy that plant location down to all of the cells.
Of course, that's what we should do and then copy those headings from each section down to all of the cells.
I'm just doing a straight out copy down there, but then anytime that it changes-- so, here the budgets and I bet we can calculate that with the database, but I'm just going to go ahead and do all that.
All right, so we have those fields over there in A and B. I'll copy and paste values and then, what do we have we have?
We have some extra rows now that we don't really need any more.
Like, for example, these blanks here and also the heading of budget and some blanks here and the heading of variance to budget.
All right, so, that helps us out in columns A, B, and C. Now, going across the top, going across the top we have two rows that I really want to put into one row.
So, I’m going to insert a row here.
So, we'll say equal that date, ampersand-- I’m going to separate it in quotes with the vertical bar.
You could use anything you want.
I just want to-- I like to use the vertical bar because I know it won't ever show up again and copy that across, paste Ctrl C, program key V to convert to-- guys, this is going to be called location.
This is going to be called type.
This is going to be called line item.
So, a lot of copying and pasting there, just, you know, basic stuff, but here is where the real trick starts.
This is a trick that I learned from Mike Alexander during one of our data analysis boot camps.
I'm going to create a brand new field here called key because Mike's trick can only work with one row label.
It can't work with these three.
So, I'm going to join whatever is in A2, ampersand, quote, vertical bar, quote, ampersand, whatever is in B2 and again in quotes, another vertical bar and then whatever is in C2 and we will double-click to copy that down.
All right, now we need to use a multiple consolidation range pivot table.
That's not available on the insert tab anymore, so we have to do Alt D for data, P for pivot table.
Multiple consolidation range, still there in the old style.
We click Next.
I'm going to create the page fields-- actually I'm not going to create any, but I don't want them to create them, click Next and then we simply specify the range from the key column that I created on over.
I just click add and click finish.
All right and this gets me a pivot table not useful at all.
It looks exactly like the original data, although we're missing columns A, B, and C, but it has everything we need.
Now, check this out.
If you go to any cell in a pivot table and double-click, what do you get?
You get all of the records that make up that cell, but in a multiple consolidation range pivot table, if you double-click the grand total, grand total, you get the data spun unwrapped; whatever you want to call it.
Now, instead of having six rows times 20, I have 120 rows.
All right.
This is absolutely beautiful.
How do we now undo?
First of all, let's make it not be a table anymore.
So, convert to range.
This is new in Excel 2007.
Yes, so I come back to normal range and then we'll insert a couple of columns here; two columns because I know I have three fields here.
We do data text to columns, it is delimited.
What is it delimited by?
It’s delimited by that vertical bar character.
We click finish.
Do we want to replace the contents?
Yes, that's replacing the formatting, that's okay.
So, we can ignore this.
This is location.
This is type.
This is line item.
Then, we also have something there that we need to break apart.
So, I'm going to insert one new column, data text to columns, delimited still by type, click finish.
Yes, this is the month and this is the type and we now have data that is ready to roll for Access.
Well, thanks to Mike Alexander from DataPig Technologies for that amazing trick.
Check out Mike's book Pivot Table Data Crunching available in three different editions.
Excel 2010 is the latest edition.
Of course, he has a great, smart co-author with that book.
All right, there we go.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
