DEPARTMENT OF CHEMICAL ENGINEERING
CHEG 3101-P02: CHEMICAL ENGINEERING LABORATORY I
COURSE ASSIGNMENT GRADING AND OUTCOME
Produce diagrams and tables such as PIVOT Tables of experimental results or raw data.
45 Points
Correctly analyze or interpret the date 45 Points.
Class Participation 10 Points
Creating a Pivot Table Assignment
A pivot table is a great way to summarize data in Excel. This tool can automatically sort, count,
and sum data into summarized data tables. Pivot tables use a feature called crosstabs which
summarizes data into a concise format for easier analysis or reporting. A pivot table usually
consists of row, column, and data fields.
For this assignment, you will analyze the nutrition information for select food data.
Step #1: Obtain Pivot Table.
1. Rename the worksheet Raw Data.
2. On the Ribbon, click the Insert tab.
3. Select the Table icon. Your data is placed into categories and should having alternating color
rows.
4. Click on the Table Tools Design tab. Select a new Table Style for your data.
5. Next, Click the Summarize with PivotTable icon. A new window will appear. Make sure that
New Worksheet is select and click OK.
6. In the new Worksheet window, change the PivotTable Name to CA1PIVOT.
7. On the right-hand side, you will see options for PivotTable Fields. Check all boxes in the
search field.
8. A PivotTable will appear in a new sheet. Name your new sheet Original.
Step #2: Obtain Summary PivotTable 1.
1. Create a copy of your Original worksheet (ask for assistance). Rename the copy Summary 1.
2. For Summary 1, select Category, Food Item, and Fat (g). You will have a PIVOT Table for
with only this information present.
3. In the bottom right hand corner of the PivotTable Fields, you will find sections for Filters,
Columns, Rows, and Values.
4. Under Values, click on Sum of Fat (g) and select Change Value Field settings.
5. Change Sum to Average.
6. Click the Number Format button. A new window will open. Select number and change the
number of decimal places to 1. You will now have a table of categories and average fat(g).
Step #3: Adding Standard Deviation and Graphing Summary PivotTable
1. Each category has a + sign next to its name. Clicking on it will expand the information.
2. You will use this information to determine the standard deviation of each category.
3. Under the Analyze Tab, select PivotChart. Determine the best graphing method for this data.
Step #4: Creating Summary PivotTables
1. For the remaining Summary PivotTables, you will create graphs based on
a. Average calories and standard deviation of Fast Foods only
b. Average carbs and standard deviation of mixed dishes only
c. Average protein and standard deviation of soups only
2. Choose the best graphing method for each
