Drawing Lines Between Sorted Groups In Microsoft Excel 2010

 

In this article, we will learn how to draw lines between sorted groups in Microsoft Excel 2010.

To draw lines between sorted groups of data, follow the below mentioned steps:

 
img1
 

  • Sort the List by Customer Number by selecting a cell in column C and click on the Sort icon.
  • Click on Data ribbon
  • In Sort & Filter group, click on A to Z (lowest to highest)

 
img2
 

  • Now, the data has been sorted to customer wise.

 
img3
 

  • Select cell A1 in the Current Region and press CTRL + SHIFT + * or CTRL + A
  • Click on Home ribbon
  • In Styles group, click on Conditional Formatting
  • Click on New Rule

 
img4
 

  • In New Formatting Rule dialog box, select “Use a formula to determine which cells to format” option.
  • In Format values enter formula as =$C1>$C2

 
img5
 

  • Click on Format
  • Select Border tab & select underline style & color

 
img6
 

  • Click on OK to see the preview

 
img7
 

  • Click on OK, and the conditional formatting rules manager dialog box will appear

 
img8
 

  • Click on OK button to see the desired result.

 
img9
 
 



14 thoughts on “Drawing Lines Between Sorted Groups In Microsoft Excel 2010

  1. Great tip! I had been looking for some time to to this – works perfectly. MS should include all these tips in their online help…

  2. “Hi Anand,
    I may be missing something here, but you can certainly sort data and then insert a new row in the middle.
    For example, if you have a list of names:
    Bob
    AnandCharlie

    You could first sort that data alphabetically, and then insert a blank row after Anand:

    Anand
    {Blank}
    Bob
    Charlie

    If the new name entered was, say, ‘Zac’, then you might need to re-sort again.

    Hope that helps,

    Alan.”

  3. “Hi Alan ,
    Thanks for reply,
    I needed to insert a blank row on same lines of inserting a coloured line using a formula & conditional formating.This happens automatically and if we have too many groups (for eg. I have about 1000 groups and will be changing very often), inserting blank lines manually will be a tedious work. SO using conditional formating or some other technic, Can we insert a balnk row which can act as seperator ?
    Please help ASAP
    Thanks”

  4. “Hi Anand,

    You could apply conditional formatting to change the background colour (for example) when a certain field switches from one value to another.
    Would that meet your requirements?
    If so, then the *easiest* way to do that might be to add a column away on the right of the data (say), and use a formula to simply check whether the row you are working on is the same as the one above (for the given field). If there has been a change, then change from, say, 0 to 1 as the result, or back from 1 to 0 at the next change. Then use conditional formatting to change the background colour based on that flag.
    Hope that helps!
    Alan”

  5. “Anand did you ever find an answer outside of this posting? Please share if you did.
    I too was hoping to use a blank row as a separator as my data is in a single column. Each record is separated by a single row.
    I was hoping there was a symbol for inserting a row so I simply highlight the column and do a find/replace: find blank cell and replace with 2 insert row symbols.”

  6. “Hi David,
    I think you just need to write a simple macro to do that. Essentially, just create a LOOP or a WITH statement to roll through the clumn of data, check the cell to see if it is blank, and if so, insert two rows (above or below as you require). Just be carful not to add so many rows that you end up hitting the bottom of the sheet (row 65536), but you’d need a fair sized data set for that to happen!
    Alan.”

  7. “Alan,
    Thanks, but I bearly know what a macro is much less program one. I’ve got about 6,000 lines of data so I won’t fill up the sheet if I only knew how. Do you have instructions on how to write this particular macro. Most of my data is separated by one blank row though there may be a few exceptions where there are 2 or 3 blank lines separating the data. I guess once the macro runs into say 4 or 5 blank rows in a row then it would stop the process otherwise insert a blank row if the current row is blank AND the row above it is not blank. Is that how it would work? It would be a big help if you could provide the steps in the writing this particular macro as I will have use for it many times in the future for the same purpose on large data sets.
    Regards,
    David”

  8. “Your first guess what correct. I do not want to remove rows. My data looks like:
    field data1
    field data2
    field data3
    field data4
    field data5
    field data6
    field data7
    field data8
    {blank}
    field data1
    field data2
    field data3
    field data4
    field data5
    field data6
    field data7
    field data8
    DESIRED RESULT AFTER MACRO RUN:

    field data1
    field data2
    field data3
    field data4
    field data5
    field data6
    field data7
    field data8
    {blank}
    {blank}
    field data1
    field data2
    field data3
    field data4
    field data5
    field data6
    field data7
    field data8

    Thanks,
    David

  9. “Hi David,
    I think I am beginning to understand now.
    So, is there a standard number of total fields that you want to have ‘space’ for?
    From your above example, it seems that the maximum total fields is 9, and some records have less than that, and for those records, you want to insert additional rows so that you can, later, add the missing fields up to 9.
    Is that correct?
    If that is the case, then it is a bit more complext thanI originally indicated, since we cannot just search or a single blank row, and insert a new blank row above or below that, else the fully populated records will have two blank rows after them, and those with, say, 2 missing fields will still not work.
    Thanks,
    Alan.”

  10. “It is hard to give a definite answer as I am provided data by my employer and sometimes it is a fixed number or rows and sometimes not. Sometimes it is orderly so that row5 is always the “”city”” for example and sometime the “”city”” is in row 4 and sometimes it is in another row all within the same column. I’ve gotten pretty creative in addressing these other issues. I always have at least one blank row between the records and there are never blank rows within the record. In my previous example I posted to you, there were 8 fields per record. My goal is to add a single row each time a blank row is reached. It would be an even better goal if it only inserted a blank row the first time a blank row appears below a non-blank row. In other words, if there were two blank rows between each record, I really only to add one more additional row and not two.
    Thanks again.”

  11. “Just FYI – I came up with additional steps that will get me 100% of my desired solution. In other words, if I have varying numbers of lines between the data, I can now add just one row between each record. Here’s how: Assume the data is in column A. In column B I write a simple IF/THEN statement that if the cell to the immediately left is NOT blank, then display column A. If the cell to the immediate lefts is blank and the cell above it is NOT blank, then print an identifying code like “”xx””. I haven’t tested it, but should look like assuming the cell pointer is in Cell B2:
    =IF(A2″”””,A2,IF(AND(A2=””””,A1″”””),””xx””,””””))
    I would copy this formula all the way down to the end of the data. I would then copy and paste the results of Column B in Column A using Paste Values and delete Column B.
    There should now be a “”xx”” on what you use to be the first blank row after each record and nothing added to the additional blank rows between records.
    Using you AutoFilter suggestion, I could filter for “”xx”” rather than filter for blank lines and then go through the rest of your steps.
    At the end of the process, I would also have to highlight Column A and replace all “”xx”” with “”””.
    Thanks again,
    David”

  12. “Is it possible to get Excel to view “”2.10″” as “”section 2 part 10″”? I’m trying to produce a log, and would like Excel to be able to sort 2.10 after 2.9…
    Can this be extended so that a list could sort itself:
    1
    1.1
    1.2
    1.2.1
    ….1.2.9
    1.2.10
    ….1.9
    1.10
    etc”

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>