Data Conversion and Reformatting

It is often useful to reformat data in an Excel worksheet, to sort, remove empty cells, extract some columns, etc. We will show how to accomplish these types of operations in Excel.

Also when data from Excel is to be imported for use in standard statistical packages such as SAS and SPSS, it is often necessary to change the format of the data. Similarly, when data from one of these standard packages are used by Excel, it may be necessary to reformat the data. We will also show how to accomplish these conversions using Excel.

Topics

10 thoughts on “Data Conversion and Reformatting”

  1. Thanks Charles
    Group Score
    A 25
    A 40
    B 20
    A 46
    B 20
    C 22
    C 15
    etc into this form
    A B C
    25 20 22
    40 20 15
    46
    and back.
    Cheers, Derek

    Reply

    • Derek,
      This can be accomplished by the Real Statistics =StdAnova1(R1) function. The reverse is done using =Anova1Std(R1).
      The first of these is also done automatically when you use the One Factor ANOVA data analysis tool.
      Charles

      Reply

      • I must be misunderstanding something. I think I have the same situation as Derek. I have two columns that look like this (for example), and I want to make it into (let’s say for the sake of example) three columns. I am not able to get that formula you gave above to do anything.
        1 a
        1 b
        1 f
        1 g
        1 z
        2 y
        2 x
        2 w
        3 p
        3 m
        3 n
        3 k

        1 2 3
        a y p
        b x m
        f w n
        g k
        z

        Reply

        • Donna,
          If say your data is stored in range A1:B12. If you highlight the range D1:F5 (or larger range) and then enter the (array) formula =StdAnova1(A1:B12) and then press the key sequence Ctrl-Shft-Enter, you will get the three columns that listed in your comment.
          Charles

          Reply

          • Oh wow! Charles, this is another really big game changer for me. You have no idea how much you just helped me! Thank you!

            For what it’s worth, I had found another work around, which was easier than doing it manually (as I had been). However, it was very time consuming and potentially error prone.

            Thank you so much. [And it is long past time for me to donate some more money. I am going to do that right now.]

  2. Hi Charles.
    One reformatting I often need to do is converting from a two column database format with single category and data columns, into column format with one column for each category. Almost as often I need the reverse.
    Any ideas on that?
    Cheers
    Derek

    Reply

Leave a Comment