Insert empty row between last names

New Contributor

Dear all,


I have a spreadsheet with a list of training courses for a group of learners. Each learner may have at least 20 courses to take. I would like to place an empty row between each last name in a column across the page. What tool do I use to do this?


I would also like to alternate colors of the rows of duplicate names to keep track of the courses each learner is required to take as in the example I have attached.


I greatly appreciate your input as this is a report I run and create monthly.


Kind regards -

6 Replies

HI @LMesi1110 


Is this what you are looking ?




pls check the last records.


Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

Best Response confirmed by LMesi1110 (New Contributor)

Hello and thank you@Faraz Shaikh 


I do know how to obtain the design on the left. I want to create the design on the right. I have 86 people with different data  that I want to sort like this and right now I do it manually by inserting an empty row between last names and then highlight each person's data to make reading the report easier. I have multiple reports similar to this that I need to create monthly.


I am so appreciative of your response to my question!


Kind regards, Lisa Mesi



Table to the right


could be generated by Power Query

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColumnNames = Table.ColumnNames(Source),
    EmptyTable = Table.FromColumns({{null},{null},{null}}, ColumnNames),

    GroupByNames = Table.Group(
        {{"Count", each Table.Combine({_,EmptyTable}), type table}}),
    RemoveUnused = Table.SelectColumns(GroupByNames,{"Count"}),
    ExpandTable = Table.ExpandTableColumn(
        ColumnNames, ColumnNames)


After that let apply to the resulting table some background color, and two conditional formatting rules 


first one to remove colors from empty rows, and second one with formula


to apply another color to every other block.


Thank you so much!@Sergei Baklan 


This is quite a bit over my head. I am not familiar with Power Query and have done some searching on how to do this. Is there some level 1 training you can recommend to get to the point of following your suggestion?




That's a lot of different level materials, but what I know and could recommend that's mainly next levels, not basic one.


You may start from Excel, in File->New find and open this tutorial


within it will be link on videos related to the tutorial.


Many thanks!@Sergei Baklan 


You have provided a direction to learn and apply your recommendation and I really appreciate your quick follow-up!


Have a great day and stay well.