lab7

LAB #7

CSC 101
Introduction to Spreadsheets

R Mohammadi

Objectives:

Learn more about the tools offered by Microsoft Excel.

Learn about functions and addressing in spreadsheets.

about this lab ...

You will learn about creating summary reports in this lab and additional techniques for formatting your spreadsheets. You will need your 3.5" disk and you must have completed lab#6 and already have a lab6 file on your disk. As always, you will also need your MS Office Starts Here CD.

1. Learn to calculate values and format Excel workbooks with Microsoft Office Starts Here.

Place your CD for Microsoft Office Starts Here in your CD-ROM Drive. Click the Quit button if a dialog box comes up to auto-start the software.

Start Microsoft Office Starts Here by clicking the Icon on your desktop.

Choose the Basics by clicking on it

On the left hand side of the Window under Sections, pick

Do the two lessons, Calculate Values and Format a worksheet.

Take Quiz after each lesson. Be sure to let your lab instructor see the results before you continue.

Close the window by clicking on the X in its upper right hand corner.

2. Starting Microsoft Excel.

Start Microsoft Excel by clicking on Start, clicking on Programs, and then clicking Microsoft Excel. An Excel window should be on your screen.

3. Enter 3^rd and 4^th Quarter tables.

Open the Excel file from lab6. The following worksheet should appear on your screen:

	Smally Department Store
	First Quarter Sales

	January	February	March	Total
Clothing	$1,500.00	$1,600.00	$1,800.00	$ 4,900.00
Toys	$ 800.00	$ 700.00	$ 800.00	$ 2,300.00
Houseware	$1,200.00	$1,600.00	$1,700.00	$ 4,500.00
Jewelry	$ 900.00	$1,100.00	$ 700.00	$ 2,700.00

Total	$4,400.00	$5,000.00	$5,000.00	$14,400.00


	Smally Department Store
	Second Quarter Sales

	April	May	June	Total
Clothing	$1,500.00	$1,750.00	$2,000.00	$5,250.00
Toys	$775.00	$750.00	$750.00	$2,275.00
Houseware	$1,300.00	$1,750.00	$1,800.00	$4,850.00
Jewelry	$850.00	$1,000.00	$900.00	$2,750.00

Total	$4,425.00	$5,250.00	$5,450.00	$15,125.00

Copy and paste these tables in creating the 3^rd and the 4^th quarter sales tables for our store. The 3^rd and 4^th quarter sales tables must appear below the first two in the correct sequence. You should only have to change the headers and the individual sales values for the new tables as the formulas will be inherited. The new tables should be as follows:

	Smally Department Store
	Third Quarter Sales

	July	August	September	Total
Clothing	$ 750.00	$ 875.00	$1,000.00	$2,625.00
Toys	$ 387.50	$ 375.00	$ 375.00	$1,137.50
Houseware	$ 650.00	$ 875.00	$ 900.00	$2,425.00
Jewelry	$ 425.00	$ 500.00	$ 450.00	$1,375.00

Total	$2,212.50	$2,625.00	$2,725.00	$ 7,562.50


	Smally Department Store
	Forth Quarter Sales

	October	November	December	Total
Clothing	$2,250.00	$2,625.00	$3,000.00	$7,875.00
Toys	$1,162.50	$1,125.00	$1,125.00	$3,412.50
Houseware	$1,950.00	$2,625.00	$2,700.00	$7,275.00
Jewelry	$1,275.00	$1,500.00	$1,350.00	$4,125.00

Total	$6,637.50	$7,875.00	$8,175.00	$22,687.50

4. Change Table colors.

For each table,

highlight the complete table

in the Format menu, choose Cells… You should now see the Format Cells pop-up window.

Click the Pattern tab.

Choose a color (a different color for each table) and click the OK button.

5. Develop a summary table.

We want a table that only reports the totals for the different items in each quarter; however, we don't wish to reenter all those totals again. More importantly, when updating individual monthly sales, all appropriate total sales that are affected by that change must change automatically. Totals that are reported in the summary table must automatically stay the same as the row totals that they represent in the quarter tables.

You may think that you could copy/paste the SUM functions into the summary table from their corresponding total cells in each of the four quarter tables, but the cell series that SUM functions apply to will actually change when you copy /paste. For example, if your quarter 1 total for clothing is in cell E5, the SUM function in that cell will be SUM(B5:D5). If you copy E5's content and paste it into the cell representing Q1's total for clothing, for example, I8, you will find that the SUM function will be copied into that cell, but the series to be SUMed will change to F8:H8 which is clearly not the series we want summed. Here you will learn the correct technique for creating the summary table.

Type in the header/labels for the summary table as shown below, to the right of the original 4 tables. It doesn't matter where you place it exactly.

Make Q1/Clothing cell in the Summary table reference the Total/Clothing cell of the 1^st quarter table. In the intersection Q1 and Clothing, type =E5, assuming E is the column for Total of the 1^st quarter table and 5 is the row for clothing in the 1^st quarter table. Don't just type =E5, make sure you reference the correct cell.

Repeat this process for the Q2/Clothing using the Total/Clothing cell in the 2^nd quarter table.

Repeat this process for the Q3/Clothing using the Total/Clothing cell in the 3^rd quarter table.

Repeat this process for the Q4/Clothing using the Total/Clothing cell in the 4^th quarter table.

Highlight all data cells (not the header cells) for the summary tables and in the Edit menu, choose Fill, in the Fill submenu, choose down. You should have the following table:



	Smally Department Store
	Sales Summary
	Q1	Q2	Q3	Q4	Total

Clothing	$4,900.00	$5,250.00	$2,625.00	$7,875.00
Toys	$2,300.00	$2,275.00	$1,137.50	$3,412.50
Houseware	$4,500.00	$4,850.00	$2,425.00	$7,275.00
Jewelry	$2,700.00	$2,750.00	$1,375.00	$4,125.00

Total

Highlight the cells starting with the intersection of Q1 and Clothing until row and column Total cells. This is the same highlight as the previous step, except that it includes all total cells in the summary table.

Click the Sum button

, this will automatically fill in all totals cells in this table, both row and column totals. You will have to widen your columns for the totals to fit, notice that the totals will appear as a sequence of ####s. I have already given you instructions for one way of adjusting the size of your cells so the data becomes visible in the last lab. Here, I'll outline another technique. The following explains how the Q1 column is widened, repeat the steps for the other columns of the summary table:

Point to the separation between column labels on top of the worksheet (A,B,C …) between the column that contains the Q1 totals and the column that contains the Q2 totals. The pointer will change to a bi-directional arrow if you are pointing to the correct spot.
Press the left mouse button and drag the pointer to the right until the column is wider by the width of a couple of characters, the columns only need to be slightly wider for the total value to be visible.
Repeat the steps for other columns in the summary table.

6. Why all this fuss.

Again, the key reason for implementing summary tables in this way is that if the data changes in the detail tables, without any work from the user (you) the summary data is updated appropriately. Try changing Clothing sales in June or Jewelry sale in September, you will notice that their summary data changes accordingly. Be sure to let your lab instructor see your worksheet.

7. Save your spreadsheet.

Save this sheet as LAB7 on your 3 ½" Floppy (A:). You have now completed the lab. Logout of the workstation after your lab instructor examines your work.