Pre-Lab Excel Activity #2
Using Multiple Worksheets
Excel Lab Manual Chapter 2 pages 51-98
Excel Lab Manual Chapter 3 pages 115-154
Must be completed by Tuesday October 20 at 11:00pm by students enrolled in Wednesday lab sessions
Must be completed by Thursday October 22 at 11:00pm by students enrolled in Friday lab sessions
Must be completed by Monday October 25 at the start of class for students enrolled in Monday lab sessions
FOLDER/FILE SUBMISSION TO THE APPROPRIATE KSU ONLINE DROPBOX MUST BE
COMPLETED BY THE DATE AND TIME LISTED ABOVE.
If you meet the
folder/file submission deadline listed above, you can hand in your packet of printouts at your regularly scheduled
lab session the next day.
IMPORTANT:
In order to complete this pre-lab activity, you must have the custom Excel lab
manual (the one with the Tiger on the cover).
If you don’t yet have the Excel lab manual, you should be able to
purchase it at Varney’s.
Before you start, PLEASE
print out this entire activity description.
Having a printed copy of this in front of you results in the activity
taking less time than completing the activity while constantly switching back
and forth between the instructions and the application you are working
with. You will get finished with this
activity quite a bit faster if you have printed activity description in front
of you.
This printout is
your assignment description.
Please follow this description,
even if it conflicts with the tutorial found in the packet. When working with the tutorial, always read and refer to this printout
first, prior to working with the tutorial.
At times, I require printouts that the tutorial does not have you
make. There are also printouts the
tutorial has you make that I don’t want.
Follow what is listed below. This is your assignment description.
Prior to starting this activity, you need to create a folder on your storage media containing your own first and last name, followed by Excel Prelab 2. For instance, my folder would be called Brian Kovar Excel Prelab 2.
After creating that folder, you will next need to visit the MANGT 366 course on KSU Online. After logging in, visit Files & Content, then Lab Assignments and the folder containing Excel files to download for the second Excel prelab activity. Download all of the files that you see into the folder that you just created. If you create any files from scratch as part of this activity, you will also need to save them into your named folder.
IMPORTANT: PLEASE READ: If a tutorial talks about getting files from
some sort of folder or storage location, you will need to visit the folder that
you just created/added to your storage media in the prior paragraph. All files that you don’t create yourself are
already in that folder. When one of the
tutorials tells you to save a file under a certain name, use the name required in the assignment description that you are
currently reading (rather than the name found in the tutorial text).
SPECIAL NOTE: PLEASE READ
All currency/dollar values found in this project must contain the $ symbol and be formatted using “Currency Style”. Please use the “true” currency style, rather than the $ button. Although the button is labeled as “$”, it is really Accounting Style. You want to use the true currency style, which results in a $ sign right next to the number, such as $45
The first part of
this Pre-Lab activity uses chapter 2 from the Excel lab manual.
Chapter 2: Data
Consolidation (Using Multiple Worksheets)
1) Please read pages 52-61 from the chapter.
2) On page
62, please do steps #1A and #1B. Save
the file as your own last name, followed by Summary, using the following
example as a guide (Kovar Summary).
3) Continuing on with step #1C on page 62 and
complete the tutorial steps as written, all the way to the end of page 67. DON’T DO ANYTHING ON PAGE 68 unless you
want to waste your colored ink.
**Note:
things to watch for**
a) After completing step #4E, your
labels in row #2 should match row #2 in Figure 9 on page 65. If you don’t see your labels as Qtr 1, Qtr 2,
Qtr 3, Qtr 4, then please adjust your result to match the figure.
b) Make sure that nothing in column A is getting cut off.
4) Instead of following the
instructions on page 68, please apply the following formats to all of your
grouped worksheets (so that group editing is still in effect).
5) Click the New York tab to ungroup the worksheets. Save the workbook. Close down all of the windows EXCEPT THE ONE FOR THE SUMMARY WORKSHEET. Once again, save your work. After doing this, you should see one window. Make sure that it is maximized to cover your entire screen. You should be able to see 4 worksheet tabs at the bottom of your screen.
6) Click the first of the sheet tabs. Then, press down the Shift key, and while holding down the shift key¸ click the last of the sheet tabs. All of your worksheets should now be in a worksheet group.
7) After creating a worksheet group, click on the Insert tab found in the Excel ribbon,
and then select Header/Footer.
8) The contextual ribbon should change and you should now see various header
and footer options.
9) Assuming that you are working with the Header, click the Sheet Name option. This will result in the worksheet name
appearing as your header (on all sheets in the group).
10) Click the Go To
Footer option. Type
in your first and last name into the footer box. Since your sheets are grouped together, your
name should appear as the footer on all sheets.
11) After creating both the header and the footer, click the somewhere into the
body of the worksheet. After doing that,
click the View tab and select Normal.
You should be back to the regular spreadsheet. Use the Print Preview option to make sure
that each of your worksheets displays your name as the footer and the name of
the sheet as the header. After doing
that, ungroup your worksheets.
12) At this time, you will need to print
the following sheets: Summary, New York, Philadelphia, and Chicago.
13) Next, display your formulas on the Summary worksheet. Print the formulas for the Summary worksheet.
14) Save your work and close Excel.
15) Please read pages 70-75 from the chapter.
16) On page 76, please do step #1A. Save the file as your own last name, followed by LinkingSolution, using the following example as a guide (Kovar LinkingSolution).
17) Continue on with step #1C on page 76 and complete the tutorial steps as written, all the way to page 80, stopping after completing step #4C.
18) Name the sheet you have been working with as Consolidation Worksheet.
19) Create a footer that displays the name of the worksheet. Create a header that displays your first and last name.
20) Format just the Consolidation worksheet so that its formats match the printouts that you made earlier (see step #4 above). Save your work.
21) Make a printout of the Consolidation worksheet.
22) Display the formulas on the Consolidation Worksheet. Make a printout that shows those formulas.
23) Close
Excel.
We are now going to shift from working with formulas that go between different
worksheets and files to creating and working with Excel data tables. In order to complete the rest of this pre-lab
activity and the third pre-lab activity, you will use the chapter 3 tutorial
and the chapter 4 tutorial (chapter 4 is also used in pre-lab #3).
24) Please read pages 115-124 from chapter 3.
25) You can skip the activity on pages 125-130.
26) Please read pages 131-146.
27) On page 147, please do steps #1A-1F (open the file called YourNameGoesHere Band Solution when completing step #1F). Use Save the file as your own last name, followed by Band Solution, using the following example as a guide (Kovar Band Solution).
28) Continue on with step #1G on page 147 and complete the tutorial steps as written, all the way to the bottom of page 151.
29) After reaching the bottom of page 151, make a printout of your work showing Juniors who play the Euphonium. Label your printout as Juniors who play the Euphonium.
30) On page #152, please do step #5C and then continue doing the tutorial steps as written, all the way to step #7G on page 154. Do not make the printout in step #7H on page 154.
31) When finished, make sure that you save your work.
HANDING IN THE ACTIVITY
1) Submit your named folder (containing all of the Excel files) to the proper location in the KSU Online dropboxes.
2) Please assemble your packet of printouts. The printouts should be arranged in the following order:
· The Consolidation sheet
· The formulas for the Consolidation sheet.
IN ORDER TO BE
CONSIDERED FOR FULL CREDIT, YOUR PRINTOUTS MUST BE IN THE REQUIRED SUBMISSION
ORDER. Deductions will be taken for
printouts that are not in the proper order.
FOLDER/FILE SUBMISSION MUST BE COMPLETED BY THE DUE
DATE/TIME LISTED ABOVE. If you meet the folder/file submission deadline listed
above, you can hand in your packet of printouts at my office or in your
regularly scheduled lab session the next day.