jump to navigation

#20-Generating MS-Excel Graphs and Charts from SPSS Output Files

November 26, 2002

LTA OverviewFor many years now, SPSS has proven to be a comprehensive statistical software, widely used in analytic applications as a data mining tool. SPSS is a modular, tightly integrated, full-featured product line for the analytical process — planning, data collection, data access and management, analysis, reporting and deployment. However, its graph-generating capabilities cannot compare with those of MS-Excel, simply because Excel offers a wide variety of charts and graphs to choose from, with parameters that can be highly customized.

Credits

Bhuvana Gopal
Research Associate, NCITE
University of Nebraska-Lincoln
bgopal@cse.unl.edu

Bhuvaneswari Gopal has been a Research Associate with NCITE and the Department of Computer Science and Engineering, University of Nebraska-Lincoln since October 2001. She has worked on a state of the practice study about Learning Management Systems on the UNL campus. She earned her Bachelor’s degree in Physics from Madras University, Chennai, India in 1996, and Master’s degree in Applied Physics from Anna University in Chennai, India, in 1998, both with distinction. Her interest in and aptitude for computer science led her to obtain an advanced post graduate diploma in e-commerce applications and eventually secured her a position as a lead developer in the Web applications development department of the Chennai branch of Transys Technologies, based in Princeton, NJ from 1999-2001. Her current research interests are Educational Technology and Software Engineering.

 

This LTA deals with the idea of combining the analytical capabilities of SPSS with that of the charting, graph-generating capabilities of MS-Excel.

LTA Outcomes

After reading through the information for this LTA, the user should be able to import raw text data from a text file into SPSS, perform required analysis on it, and with the results in tabular form, import them onto MS-Excel, and generate the desired charts and graphs.

Skills Required to Complete This LTA

Users should be comfortable with working with SPSS to import and analyze data using the various analytical tools that are available on the software package. A familiarity with MS- Excel would also be useful.

Software Required to Complete This LTA

Microsoft Excel, SPSS

Description of Procedure

Raw text files can be used as input to the SPSS software to perform a statistical analysis. Using the data import facility in the “File – Read text data” menu of the SPSS data file screen, one can import text data delimited by any character (space, tab etc) on to SPSS, and save the data file as a .sav file.

Screen shot – SPSS data file imported from text file:

screenshotThe next step is to find the appropriate command from one of the “Data”, “Transform”, “Analyze” or “Variables” menus, to suit the analytical need of the user, and run it on the data set. The results are shown as an output file on the screen, on the SPSS viewer. These results can be saved , if necessary, as a .spo file.

Screen shot – SPSS output file:

screenshot 2The output tables can be highlighted (they are then know as pivot tables) and double clicked to set them in the editable mode. Then one can select all the contents of the table(s) by using the “Ctrl-A” command, and copy them using the “Edit-Copy” or “Ctrl-C” commands.

Screen shot – MS Excel worksheet created from SPSS output file:

screenshot 3Screen shot – generation of graph from MS Excel worksheet:

screenshot 4These results can be copied and pasted on to an MS-Excel template in a tabular form. To create a graph/chart on Excel, first the required data is selected on the spreadsheet. Then the “Chart” option is selected from the “Insert” Menu, and the type of chart required is chosen (column, pie, etc). The data range is set to be the one selected on the spreadsheet (it appears automatically that way). A title for the chart can be given at this point, and so can the legends for the X and Y axes, representing what the X and Y axes stand for. The graph can now be saved as an object in the original data sheet or as a new graph.

By the above mentioned simple steps that can be used to combine the two applications, we can take advantage of the strengths of both software packages, and get highly sophisticated statistical analysis as well as superior chart/graph generating capabilities, than as stand alone applications.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: