Excel Insert Ingenious: Insert 2 Rows After Each Change in Customer - Episode 2158
HTML-код
- Опубликовано: 5 авг 2024
- Microsoft Excel Tutorial: Insert two rows below each customer's records
Welcome to another episode of the MrExcel netcast, where we help you master Excel and solve your toughest spreadsheet problems. In today's episode, we will be discussing how to insert two rows at each change in a data set, a question that was sent in by Janet, one of our viewers who I had the pleasure of meeting at Excelapalooza.
Now, before we dive into Janet's question, I want to address a common confusion that many Excel users have - how to insert multiple rows at once. It's actually quite simple, but for some reason, it seems to trip people up. All you have to do is select the first cell where you want the rows to be inserted, and then use the shortcut ALT+I R, or go to the INSERT tab and click on "Insert Sheet Rows". However, if you want to insert more than one row, you need to select the number of rows you want to insert before using the shortcut or command. For example, if you want to insert two rows, select the first cell and then hold down the SHIFT key while pressing the DOWN ARROW twice. This will select two rows, and then you can use the shortcut or command to insert them.
Now, let's get back to Janet's question. She wants to insert two rows after each change in customer name in her data set. To do this, we will use a helper column and an IF OR formula. In the helper column, we will check if the customer name in the current row is different from the one above or two rows above. If it is, we will put in a number 1, and if not, we will put in a letter. Then, we can use the GO TO SPECIAL command to select only the rows with the number 1 in the helper column, and use the INSERT SHEET ROWS command to insert the two rows after each selected row. Once we have inserted the rows, we can delete the helper column and our data set will have two rows inserted after each change in customer name.
This is just one of the many useful tricks and techniques that you can learn from my book, "617 Excel Mysteries Solved, Power Excel With MrExcel, the 2017 Edition". Click on the "i" in the top right-hand corner for more information on how to purchase the book and take your Excel skills to the next level. And as always, thank you for tuning in to the MrExcel netcast. Don't forget to send in your questions and we'll see you next time for another episode.
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#excelchallenge
#walkthrough
#evergreen
This video answers these common search terms:
how to insert a blank rows when values change in excel
how to insert multiple blank rows in excel
what is the keyboard shortcut to insert rows in excel?
how to insert rows after every nth row in excel
Table of Contents:
(00:00) Inserting 2 rows at each change in data
(00:11) Explanation of how to insert multiple rows in Excel
(00:31) Demonstration of inserting 2 rows using ALT+I R shortcut
(01:05) Use of a HELPER column to identify where to insert rows
(02:16) Selecting specific cells using GO TO SPECIAL
(02:48) Information about the book "617 Excel Mysteries Solved"
(03:01) Clicking Like really helps the algorithm
How to insert multiple rows in one command in Excel
Use an IF OR formula to figure out if this is two rows below. Use "A" or 1
Go To Special to select formulas that result in numeric
Issue one Insert Row command
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
If you like this tip, please click Subscribe and click the bell icon for notifications.
InstaBlaster...
Already did. Appreciate your magic.
Life Saver. I had 14000 rows in a sheet. You are definitely a life saver. Thanks a lot.
Thanks a lot! This gives so many new ideas to us new learners
small time, simple formula, BIG HELP, THank you
Awesome trick! Thanks Bill !
Wonderful trick, thank you very much Bill, the formula is amazing!
Fantastic tip!
Educative tutorial.
Great trick : )
Cool trick! Thanks!
Very smart trick
Will this work if the 2 consecutive values in a column are different?
Another great video. My 2 cents. I personally like Ctrl+ and Ctrl- (numeric keypad) as the fastest way to insert or delete rows. It seems logical to me. After you had selected the numeric values, I would have Ctrl+spacebar to select the entire row, then Ctrl+ to insert rows. Personal preference I guess. If you don't select the entire row then you have to answer the question in the dialog box.
Triple Magic!)
Nice one without using VBA.
How can I insert a row after every 2 rows with one stroke? I have about 4k rows of data and 5 columns.
This is a great question. I can't think of a "one stroke" solution. But in this video, I manage to do it in about 90 seconds. ruclips.net/video/a2njq-UVf_s/видео.html
Hi Bill I have to insert rows after every 5th row... how can I insert.? And I want to insert numbers for every 5th row, like 5-10-15-20...... after 5 - 5 rows. Is it possible...?
Hello sir,
How can I do sum only not hidden value in Excel.
Can this be accomplished with VBA?
Does the formula work if I have only one ocurrence of a customer, let's say AT&T?
Only works for 2 or more same customers.
HI bill.... is there any plans to make videos on DAX and Adv DAX ??
Thanks for your kind reply.... But, don't you think that DAX is the next level for even adv excel users and a must skill to cope up with Analytical DAX users ...
Ya i have read Rob collie book and also Alberto / Marco book.... both are great.
Wish you could make videos on DAX.. But i always thoroughly enjoy ur excel videos aswell..
Thanks
Slick! No VBA
Sir, How to insert 2 blank rows after each row having name of companies from cell A1 to A30
how to insert rows without affecting data in other column?
If you use Home, Insert, Cells, Shift Cells Down, OK then it will insert just in the selected columns and not affect the other columns.
But the hard part will be selecting just the cells. The trick at 2:30 in this video is selecting cells in the temporary helper column. Do you want to also select cells in the columns to the left?
Hi I am Indian ,how to buy that guy can u send link is easy for me
Spinny Excel
this was useful, but I mean you can't insert two rows at once if a value doesn't repeating more than two times.