Data Analysis in Excel

Carillon Technologies Limited

Use of Data Analysis Package in Excel®

Using the Data Analysis Package in Excel®.

The Excel Data Analysis package found under the "Tools", pull down menus provides an alternative method for determining decriptive statistics and creating a simple histogram.

This section will describe the steps for using the Data Analysis package.

Step 1. Installing the Data Analysis Package

If you have not already activated the data analysis package in excel, then you must open Excel and go to the Tools pull down menu. Click on "Add in...."

And then click on the Analysis Toolpak - VBA. This will activate the Visual Basic macros necessary for the analysis package.

Step 2. Performing the Descriptive Statistics Subroutine.

The second step requires going back to the Tools pull down menu and selecting the Data Analysis Package.

Scroll down the menu and select the Descriptive Statistics Analysis Tool.

After selecting the Descriptive Statistics Analysis Tool, you must first select the data input range. This can be performed by clicking the cursor in the Input range box and then draging the cursor over the data set found in the Excel Worksheet.

Click The Summary Statistics Box

Finally, Input an address in the worksheet for the summary statistics to be posted

or

Click the New worksheet ply and add a name

or

Click the New Workbook button

Step 3. Output of the Descriptive Statistics Subroutine

The output of the summary statistics includes all the standard descriptive statistics.

If you want to change the number of decimal places displayed then highlight the worksheet column and go to the Format pull down menu. You can change from general to a specific number of decimal places.

You can also select the Title (Column1) and replace the name with an appropriate title.

Step 4. Creating a Histogram

In many cases we are interested in creating a histogram of the data.

Go to the Tools pull down menu and select Data Analysis

Then scroll down until you see Histogram, then select.

As in the Descriptive Statistics analysis tool you must designate the input range, just like in the descriptive statistics tool.

Then select the Chart Output button, and the output range or new worksheet or workbook.

The analysis tool creates the cells and sorts. If you have preset cell ranges these can be loaded thru the use of the bin range input.

Read the help section for this option

The outputs of this analysis tool include the histogram and summary data. Notice that the Bins signify the minimum value in the bin and the corresponding frequency.

Return to Instructional Materials Page