1.048 Million Rows within 16,384 Columns! That's 17 Billion Cells... on JUST ONE WORKSHEET!
This is our 8th Podcast featuring the New Book "Don't Fear The Spreadsheet" by Tyler Nash, Kevin Jones, Tom Urtis and Bill Jelen. Today, Tyler needs to be able to insert some Data into a Spreadsheet that already has existing Data in it. Follow along with Episode #1586 to learn your options and best practices in performing this merger.
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
This is our 8th Podcast featuring the New Book "Don't Fear The Spreadsheet" by Tyler Nash, Kevin Jones, Tom Urtis and Bill Jelen. Today, Tyler needs to be able to insert some Data into a Spreadsheet that already has existing Data in it. Follow along with Episode #1586 to learn your options and best practices in performing this merger.
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
This MrExcel podcast is sponsored by Easy-XL.
“Don't Fear the Spreadsheet” podcast episode number eight: Insert row into existing data.
Hey, welcome back to the “Don't Fear the Spreadsheet” podcast I'm Bill Jelen from MrExcel.com with Tyler Nash with today's question.
Tyler: So I already have like a ton of data but I need to be able to insert a whole new row.
Is there a way that I can insert either a row or a column into existing data?” Bill: All right Tyler, hey yeah, okay, now you said you have a ton of data, I just made up this little data set here so I don't have a ton of data but the concept is the same.
Let's say that we want to add a new South region and it has to go between East and Central so here's what we want to do.
If we want to insert a new row after East we actually have to go to the row, how do we want to insert the row before, all right so right there, I choose, that was weird, what was up with that, okay, I choose Row 3 and I'm going to right click and say insert and that's going to insert a row above like that.
If for some reason I would need to insert a new quarter, between quarter one and quarter two, it works kind of the same you go, yeah that's always going to insert the row to the left or the column to the left so I go to column C, insert and I get a new column to the left and everything else is shifted over.
All right now this works great provided you don't have anything else out here to the right side of your data I'm going to move on to the next worksheet, now here we have two data sets on the same worksheet, well not two data sets, but we have two very important things.
We have the sales report and then over here I jotted the list of groceries I have to get on the way home and I don't want to insert a row over here, I want to keep this nice and compact and contiguous, contiguous there's a word for you.
All right, so in this case we are going to select all of these cells here, all right, so before I just had the whole row now I want to select specifically the cells that I want to push down and I use ALT IE, ALT IE, let me try to make the screen smaller here so you can see the right-hand side.
Right here on the Home tab, under insert there's something called insert cells.
ALT ie is the old shortcut for that you could also use CTRL SHIFT = and we're going to shift cells down, now watch what happens when we do this.
That takes the old cells and it moves everything below it down but it doesn't affect the data outside here, outside of the selection.
Alright so that's a cool way to go, we could also do the same thing here although that's going to cause problems but let me just show you, ALT IE, shift cells right click okay.
All right now that's screwed up my grocery list over here so I'm going to CTRL Z to undo.
I realized that was going to happen before I did it and another way and I'm going to thank an old co-worker of mine named Pat.
Pat showed me this cool trick, if we have the new data that has to go in there so here's the data for south and I need to insert it in the middle I can actually come down here and I'm going to CTRL X to cut, you could also use the cut icon up there, and then select where I want it to go, I want to go between East and Central and under insert, now because I have cut the menu actually changes I can insert cut cells and it just put all of the South and shifted everything else down so there's a yet another way to go.
Oh yeah I want to thank you for stopping by check out “Don't Fear the Spreadsheet”, this book makes Excel for dummies look like it was written for rocket scientist.
“Don't Fear the Spreadsheet” podcast episode number eight: Insert row into existing data.
Hey, welcome back to the “Don't Fear the Spreadsheet” podcast I'm Bill Jelen from MrExcel.com with Tyler Nash with today's question.
Tyler: So I already have like a ton of data but I need to be able to insert a whole new row.
Is there a way that I can insert either a row or a column into existing data?” Bill: All right Tyler, hey yeah, okay, now you said you have a ton of data, I just made up this little data set here so I don't have a ton of data but the concept is the same.
Let's say that we want to add a new South region and it has to go between East and Central so here's what we want to do.
If we want to insert a new row after East we actually have to go to the row, how do we want to insert the row before, all right so right there, I choose, that was weird, what was up with that, okay, I choose Row 3 and I'm going to right click and say insert and that's going to insert a row above like that.
If for some reason I would need to insert a new quarter, between quarter one and quarter two, it works kind of the same you go, yeah that's always going to insert the row to the left or the column to the left so I go to column C, insert and I get a new column to the left and everything else is shifted over.
All right now this works great provided you don't have anything else out here to the right side of your data I'm going to move on to the next worksheet, now here we have two data sets on the same worksheet, well not two data sets, but we have two very important things.
We have the sales report and then over here I jotted the list of groceries I have to get on the way home and I don't want to insert a row over here, I want to keep this nice and compact and contiguous, contiguous there's a word for you.
All right, so in this case we are going to select all of these cells here, all right, so before I just had the whole row now I want to select specifically the cells that I want to push down and I use ALT IE, ALT IE, let me try to make the screen smaller here so you can see the right-hand side.
Right here on the Home tab, under insert there's something called insert cells.
ALT ie is the old shortcut for that you could also use CTRL SHIFT = and we're going to shift cells down, now watch what happens when we do this.
That takes the old cells and it moves everything below it down but it doesn't affect the data outside here, outside of the selection.
Alright so that's a cool way to go, we could also do the same thing here although that's going to cause problems but let me just show you, ALT IE, shift cells right click okay.
All right now that's screwed up my grocery list over here so I'm going to CTRL Z to undo.
I realized that was going to happen before I did it and another way and I'm going to thank an old co-worker of mine named Pat.
Pat showed me this cool trick, if we have the new data that has to go in there so here's the data for south and I need to insert it in the middle I can actually come down here and I'm going to CTRL X to cut, you could also use the cut icon up there, and then select where I want it to go, I want to go between East and Central and under insert, now because I have cut the menu actually changes I can insert cut cells and it just put all of the South and shifted everything else down so there's a yet another way to go.
Oh yeah I want to thank you for stopping by check out “Don't Fear the Spreadsheet”, this book makes Excel for dummies look like it was written for rocket scientist.
