Reference no: EM132238438

Lab Assignment -

Subtotals Assignment - Part 1

Subtotals are an effective but cumbersome way of demonstrating the ease of use of pivot tables to perform the same function more quickly, without the required sorting of data and re-sorting of new or additional data. As a prelude to demonstrating the use of pivot tables, subtotals divide the data into subsets specified by the programmer.

Objective: Format the list of data into subtotals for easier presentation. Use the Subtotals feature only (and not pivot tables).

You are given a list of orders for the first three weeks of the month. The information includes the customer, city, state, product description, price, and quantity stored in database table form. (This means that subtotaling the orders by order number will give you an itemized list of each order).

You will subtotal the data using the Subtotal option only. Pivot tables are not allowed. First, copy the Invoices worksheet to a new worksheet. This will be your subtotaled worksheet. Name the worksheet, then Freeze Panes at the top row so that the column headings always show when you scroll down the page.

The process of displaying information in subtotals is meaningless unless the data is sorted first. Sort the data by Sales Representative, then by Customer. Then use subtotals to find the total (sum) of orders for each Sales Representative.

Then Copy this information to a second worksheet. You will now add additional data (the data from the last week of the month) to this data at the end. Name the worksheet, Freeze Panes so that the column headings always show, then subtotal this worksheet the same way as the previous worksheet (by Sales Representative, then by Customer). Note that you may need to sort the combined data before the subtotal feature will be useful.

Your finished assignment should be one workbook that has two worksheets with original data, and two subtotaled worksheets.

Assignments and Quizzes - Complete and Submit the Textbook Exercises:

Note: Submit one workbook with multiple worksheets. There should be 3 worksheets.

Structured Tables -

Convert Chicago energy to and Excel table, and name it ChiEnergy filter to just blank building types Copy and paste values (visible values only) to a new worksheet, make that into a table, and name it ChiEnergyAgg Calculate KWH and BTU per capita and rank in descending order in two columns.

Pivot Tables -

Use the ChiEnergy table created above (not ChiEnergyAgg) and create a pivot table in a new worksheet showing top 20 neighborhoods in residential population that displays the sum of occupied residential units, total KWH consumed in 2010 by residential units, total BTU consumed in 2010 by residential units, and population of residential buildings.

Attachment:- Assignment Files.rar