analyzeexcel

toc

=Analyzing Data Using Microsoft Excel 2007= Today we’ll look at ways that Excel can quickly help us make decisions regarding the data we give it. First we will look at sorting and filtering information. Then we’ll let Excel use its color scales to visually discern values. Excel also allows us to flag information based on certain criteria.

Open this file to get started:

Sorting Information
The most basic way to analyze data is to sort it by column. This is often done to alphabetize a list or sort numbers is ascending or descending order.
 * 1) Switch to the data ribbon.
 * 2) Highlight the range of cells you would like to sort; include the column labels. Typically, all cells should be selected, especially all columns. Failure to select across all columns could break the association between the column data.
 * 3) Click the sort button. The Sort window appears.
 * 4) From the Sort by pop-up menu, select Last Name.
 * 5) Be sure that Values is shown in the Sort On column.
 * 6) Verify that A to Z is in the Order column.
 * 7) Click OK to complete the sort.


 * Note:** You can add additional sort criteria by clicking the Add Level button on the Sort window. This will allow you to add a sort for first name, for example. That way, those with the same last name can then be sorted by first name.









Filtering Data
A filter is similar to a sort, but the column headers serve as the sort command. You can also filter out unwanted data, such as certain names or values.
 * 1) Select the range of data, including the column header labels.
 * 2) From the Data ribbon, click the Filter button.
 * 3) Note that the column labels now have pop-up menu buttons.
 * 4) To sort or filter the data, click the pop-up button next to that column’s label.
 * 5) Choose the type of sort or filter you would like to use.
 * 6) Repeat the process for additional columns.


 * Note:** Once the filter has been added, you may need to resize the column widths to show the entire text label.





Color Scales
One of Excel’s coolest features is its ability to format cells using color scales. Excel looks at the numerical values of all the cells in the column, highlighting cells using a scale based on the range of values.
 * 1) Highlight a column of numerical values. DO NOT include the column label.
 * 2) From the Home ribbon, click the Conditional Formatting button.
 * 3) From the resulting menu, choose Color Scales > Green – Yellow – Red Color Scale.
 * 4) The cells will be shaded based on value.
 * 5) If you sort the cells, you can better see the effect.





Custom Conditional Formatting
You can also tell Excel to automatically enter data in cells based on the criteria you desire. In this example, we’ll use the funds raised column to flag those students who have earned enough to go on the field trip.
 * 1) Select the Funds Earned column.
 * 2) From the Home ribbon, click the Conditional Formatting button.
 * 3) At the bottom of the menu, choose New Rule…
 * 4) From the list at the top, select Format all cells based on their values.
 * 5) From the Format Style pop-up, choose Icon Sets.
 * 6) Change the Icon Style to 3 Arrows (Colored).
 * 7) Change both Type pop-ups to Number.
 * 8) Students needs $60 to qualify for the trip, enter 60 in the first Value box. You also want to flag students who have raised at least half of the money; enter 30 in the second Value box.
 * 9) Click OK.
 * 10) The icons indicate student progress.





=Additional Resources= [|Official Microsoft Excel Courses Online] [|Florida Gulf Coast University Excel Tutorial]