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 is used by Excel, it may be necessary to reformat the data. We will also show how to accomplish these conversions using Excel.

Topics:

- Sorting and Removing Duplicates
- Reformatting Data Analysis Tools
- Sorting and Removing Duplicates by Rows
- Extracting Columns
- Frequency Table Conversion
- Coding of Categorical Variables

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

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

OK. Thanks Charles. Derek

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

Derek,

Can you give me a simple example of what you are looking for?

Charles

I notice that all the topic hyperlinks, except the last one, are not working.

Leo,

Thank you very much for catching this error. I have now added the hyperlinks.

Charles