How To Insert A Blank Row At Each Change In A Column In Excel

Подписаться 8 тыс.

Просмотров 66 тыс.

50% 1

In a previous post I talked about how to insert a line at each change in a column of a table or data range in Excel. In this tutorial we'll look at how to insert a blank row at those changes.
To download the file and follow along, go to the link on my website at:
app.box.com/file/244442894973...
Do you need help with a formula or an Excel project?
You can send me an e-mail to mrempel@excel-bytes.com
Including a sample file is always helpful!
You can donate to my channel through any of the following:
PayPal:
paypal.me/ExcelBytes?locale.x...
Zelle or Venmo:
Send to mrempel@excel-bytes.com

Опубликовано:

3 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 95

@mrtimtews Год назад

Thanks for the video, its still helped this Excel newb in 2023!

@jamesmurfin9724 4 года назад

Saved me at least 2 hours of work today, and countless more in the future, thank you!

@ExcelBytes 4 года назад

Great! Glad you found it useful!

@MSOfficeQuickTips 3 года назад

Thank you!!!!!!! I have been going nuts trying to figure this out! Thank you, thank you, THANK YOU!!!

@ExcelBytes 3 года назад

Glad you liked it!

@60secondrhymes 4 года назад

Thank you Michael! Very useful Technique! Huge Time Saver

@ExcelBytes 4 года назад

Thanks! Glad your liked it.

@TeddyMonacelli 4 года назад

Thanks for this tip. Very simply and effective.

@ExcelBytes 4 года назад

Glad you liked it!

@charbelmesa 4 года назад

Este video debería tener 1 millon de Likes

@ExcelBytes 4 года назад

muchas gracias

@alecambo 5 лет назад

Thanks, it worked perfectly!

@ExcelBytes 5 лет назад

Thanks, glad you liked it!

@samanthastidham1780 3 года назад

This was fantastic!! Thank you for sharing!!

@michaelrempel2905 3 года назад

I'm glad you liked it!

@altafhussain4425 4 года назад

Thanks man! You're Genius.

@ExcelBytes 4 года назад

Thanks. Glad you found it useful.

@Up4Excel 2 года назад

Very useful technique. I hadn't thought of using the find all feature to do stuff like this before 👍

@michaelrempel2905 2 года назад

Thanks, glad you liked it

@JohnyComeLately 4 года назад

Thank you! A google search linked me to you video and it was exactly what I was wanting to do.

@ExcelBytes 4 года назад

Great, glad you found it useful!

@anwarhussain4997 4 года назад

Thank you. It was great help

@ExcelBytes 4 года назад

Glad it was useful for you.

@hungriecat6540 4 года назад

for long i've been trying to figure this out, thanks !

@ExcelBytes 4 года назад

Glad you found it useful!

@Masterhack025 2 года назад

Thank you so much Micheal. Actually i was searching for same thing from last few days. all the best wishes for you channel.👍👍👍

@ExcelBytes 2 года назад

I'm glad you found this useful!

@francisf.massaquoijr2406 3 года назад

You really helped me, you are a legend... Thanks

@ExcelBytes 3 года назад

Thanks, I'm glad you found this useful!

@252anand 4 года назад

Thank you ..you saved me an hour.....

@ExcelBytes 4 года назад

Great! Glad you found it useful.

@GenerationGenius 3 года назад

Excellent explaining

@ExcelBytes 3 года назад

Thanks, glad your liked it.

@budhori1201 5 лет назад

Love You sir, You solved my problem

@michaelrempel2905 5 лет назад

Thanks! Glad your liked it and it was useful for you.

@samawad4564 4 года назад

Thank you!

@AldensAntlers 3 года назад

THANKS SO MUCH

@ExcelBytes 3 года назад

Glad you liked it.

@MrSafwan786 4 года назад

Thank you This is help me alot

@ExcelBytes 4 года назад

Great to hear!

@doreen9051 3 года назад

Thanks so much!

@MichaelRempel 3 года назад

I'm glad you found this useful!

@Oracle_Zeus 4 года назад

Life saver

@davidoregonoils7023 3 года назад

Genius!

@ExcelBytes 3 года назад

Thanks!

@rose_shimmerling 2 года назад

It worked for me! CTRL + wouldn't work for me though. So what I did was click the insert tab and select 'Add Sheet Rows' and it worked. :) Thanks.

@mandypdx 3 месяца назад

‘+’ is shift +, so you need ctrl shift +

@rose_shimmerling 3 месяца назад

thanks :) @@mandypdx

@RawStrengthBharat 2 года назад

You saved my 45 minutes

@michaelrempel2905 2 года назад

Glad I could help!

@joelgordy2635 5 лет назад

This seems to work as long as you don't have FALSE in two or more adjacent rows (data changed again on next row). Two FALSE in two rows will yield two blank rows above the first FALSE. Three FALSE in three rows will yield three blank rows above the first FALSE. Maybe I missed something?

@ExcelBytes 5 лет назад

No, you are correct. This process didn't account for that scenario. I will need to work on a different process altogether to deal with that issue, Will probably result in a new video.

@ExcelBytes 5 лет назад

I did find this VBA code that works in this situation. It assumes you have headers in row 1, your data starts in row 2, and column B is where the changes occur that you want to check: Sub InsertRowsAtValueChangeColumnB() Dim X As Long, LastRow As Long Const DataCol As String = "B" Const StartRow = 2 LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row Application.ScreenUpdating = False For X = LastRow To StartRow + 1 Step -1 If Cells(X, DataCol).Value Cells(X - 1, DataCol) Then Rows(X).Insert Next Application.ScreenUpdating = True End Sub

@evgueniteplits5687 4 года назад

@@ExcelBytes Hi! Is there any new solution for the case Joel described? Thanks!

@ExcelBytes 4 года назад

@@evgueniteplits5687 See this tutorial: www.excel-bytes.com/inserting-a-blank-row-at-a-column-change-update-in-excel/

@jeanettecarey9031 6 месяцев назад

I am so glad it wasn't just me. I thought I was for sure doing it wrong (repeatedly)

@milliehanson750 9 месяцев назад

Hello thanks for the video. - we have been trying to do this. But when we try to highlight our column, the insert box (e.g to select entire rows etc) does not show up (and the ctrl + formula doesn't work), so we can't select this option. The only thing that happens is an automatic insert of another column next to it. Any ideas? Thanks

@ExcelBytes 9 месяцев назад

Millie, I'm not sure what the issue may be, could be a couple of things. If you want, I'd be happy to do a phone or video call to see what the issue is. If you want to pursue this, please send me an e-mail at mrempel@excel-bytes.com and we can continue there.

@megangrehan3213 3 года назад

Great video very easy. I had a problem when I had cells change one after the other. So there were three falses in a row which is correct but when I went to insert it inserted three rows after the three falses. So they didnt separate. Any ideas?

@ExcelBytes 3 года назад

This video addresses that issue: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qWbaGnZlLn8.html

@megangrehan3213 3 года назад

@@ExcelBytes Thank you!!

@martinsawe6341 Год назад

very useful but how will i make an empty row automaitcally show a value at the end of the row like an interest

@ExcelBytes Год назад

Can you be more specific? If you want you can send me an e-mail with more details and/or a sample of what you want to do. Send to mrempel@excel-bytes.com

@fp715 2 месяца назад

For us that use this maybe once or twice a month. How do we do it without shortcuts? I know it takes longer just can’t remember the combos

@ExcelBytes 2 месяца назад

I used 4 keyboard shortcuts in this tutorial: Ctrl F: Home>Find&Select>Find Ctrl C: Home>Copy Ctrl +: Home>Insert>Insert Sheet Rows Ctrl -: Home>Delete>Delete Sheet Columns

@SuryaAchanta Год назад

Hello how you added heading of the cell after =..............like =[@salespeople]

@muskanjalan9574 2 года назад

how we can put sum in all the added rows for above number of columns

@ExcelBytes 2 года назад

Your probably better off using the SUBTOTAL function than this process.

@aslowikowski 4 года назад

What's the shortcut command to copy down the formula you use at 3:40?

@ExcelBytes 4 года назад

Whenever you double click on the lower right corner of a cell (cursor turns into a solid plus sign) that has anything in it, Excel will automatically copy it down as far as any entries in the column immediately to the left or right. If there is nothing in a column to the left or right, nothing will happen.

@tantukumardas684 4 года назад

sir, I am not getting solution for those cells having only one data and no other same value. For example, only one cell contains ed then it may not give the same result.

@ExcelBytes 4 года назад

I did a follow up that addressed this issue: www.excel-bytes.com/inserting-a-blank-row-at-a-column-change-update-in-excel/

@nitintalekar2322 4 года назад

Hello, sir how can I add blank lines between data by different quantity number wise

@ExcelBytes 4 года назад

The theory would be the same, but I need to be more clear on how your quantity number changes. Can you provide an example file? Send to mrempel@excel-bytes.com

@ganesh55507 Год назад

if we have only one row for one unique number , its now working

@ExcelBytes Год назад

Try this one: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qWbaGnZlLn8.html

@budhori1201 5 лет назад

Sir problem solved but i want serial number also changed after blank rows (always start 1 after blank rows) plz solved my problem

@ExcelBytes 5 лет назад

Can you please explain further what you want? Possibly send me a sample file with what you want to do with an explanation. Send to mrempel@excel-bytes.com

@Masterhack025 2 года назад

but what will happened when "salespeople" column has single row data not multiple row data??? please reply.

@ExcelBytes 2 года назад

Check this one out: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qWbaGnZlLn8.html

@hemachandra4315 4 года назад

How to add the total price in the space of ROW "F"

@ExcelBytes 4 года назад

Can you please explain your question a bit better? "F" is not a row but a column. Do you want to add up the entire column "F" or insert subtotals in column "F" at each blank row? Or is it something else?

@bernardblumberg803 6 лет назад

Excellent, but still not scrolling in to formulas! Especially for us old folk.

@ExcelBytes 6 лет назад

What do you mean "scrolling in to formulas"?

@bernardblumberg803 6 лет назад

Closeup. Panning in.

@VijayKumar-zd7ou 5 лет назад

Hello sir, my one query is how to many name a column and repeated some 4,5,3,7,2 time how to coloured at once in excel then sort by colour show grouping but every group show different different colour. Please help😂

@ExcelBytes 5 лет назад

Can you send me a file showing more detail of exactly what you want and I'll see if I can help. Send to mrempel@excel-bytes.com

@VijayKumar-zd7ou 5 лет назад

Excel Bytes sir, I m send the sample file ur email address. please help 😂 file subject name of grouping color