.equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/BIS155_W4_iLab.html”>iLab> STEP 2: Create
You work with the Student Education Trust, which
allows you to demonstrate your expertise with Excel. The trust is sponsoring an
auction, and you have received a list of all donors who have contributed to
this auction. The list was pulled from their database as a comma-separated text
file. You have been asked to create a letter that will go out to each of the
contributors that will accept their donation. Tickets to the event will be
enclosed. The letter requires that you provide the following pieces of information.
Full name and address
Number of tickets
Submit your assignment to the Dropbox, located
at the top of this page. For instructions on how to use the Dropbox, read these .equella.ecollege.com/file/8ff9f27a-3772-48cf-9855-4bec4e6706bf/1/Dropbox.html”>step-by-step instructions.
(See the Syllabus
section “Due Dates for Assignments & Exams” for due dates.)
You will turn in one Excel spreadsheet and two
You will submit the following three items: one
Excel spreadsheet, one Word document showing 25 letters, and one Word document
showing 25 labels. When submitting the Excel workbook and the Word documents,
provide a comment in the Dropbox comments area explaining what you learned from
completing this iLab activity.
File naming convention: If your name is
Jane Doe, then your files should be named very similar to:
Click to download the .equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/BIS155_W4__Lab_4_Grading_Rubric.docx”>Grading Rubric for Lab
Microsoft Office: Excel
Options for accessing Microsoft Excel 2013:
Use a personal copy on
your PC. You can request a copy of Microsoft Office 2013 via the Student
Software Store icon on the Course Resources Page
If you are a MAC user,
click to read the .equella.ecollege.com/items/2d5f4ce6-cbbd-4727-9910-eb5621ef0ed5/0/ZZZ_W2_MacSupport.html”>MAC User Information.
virtual lab to access Microsoft Excel in the virtual lab.
For additional virtual lab information and tutorials on saving files, click the
iLab icon on the Course Resources page.
Be sure you have reviewed the Tutorials located
on the Lesson page before you begin the iLab.
Please do not rely solely on
the videos to complete this week’s lab. The videos provide detailed examples, using the supplied
files. Applying the examples to the lessons will give you both practice and
instruction on what to complete.
STEP 1: Import Text
File Into a Spreadsheet and Clean up Data
Download the text file: .equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/Week4_iLab_MailingData_Student.txt”>Week4_iLab_MailingData_Student.txt.
To complete the download, right click on the file name and select Save Link As.
The file will save to your local drive.
Open a new Excel file
and import only the data from the txt file you will need for your letter (i.e.,
excluding unnecessary columns).
Separate the first and
last names into two columns so that you may use the first name in a Mail Merge.
Make sure the fields are named LastName and FirstName.
Check the accuracy of
all data by using the Spelling tool in the Proofing group.
Add a documentation
sheet that provides a spreadsheet title, author, date, and purpose. Format the
documentation sheet by applying a professional color scheme to the text. Ensure
that the sheet tab is named Documentation and that the documentation sheet is
the first sheet in the workbook.
STEP 2: Create Merge
Open the .equella.ecollege.com/file/683241e5-ae61-4ab4-8047-beb4fe804bf6/24/Week4_iLab_Trust_Template_Student.docx”>Week4_iLab_Trust_Template_Student.docx document
in Word. Make sure that the document is in the same folder as the Excel
spreadsheet. This will make it easier to work with the two documents during the
Note: Be sure to name this file, using the
naming conventions listed above.
Merge information from
your Excel spreadsheet into the Word document. Your completed merge will look
like the following when complete.
Student Education Trust
123 Adams Street
Pittsburgh, PA 15697
123 North Street
Pittsburgh, PA 15697
Thank you for your generous donation to the Student Education Trust Fundraiser.
We are delighted to accept the following donation.
Item Value: $50.00
Enclosed you will find the 2 tickets you requested for this Fundraising event,
which will take place on Friday, October 24, 2015. We will be looking forward
to seeing you and giving your personal thanks.
Thank you again for you support of the fund. The proceeds will support
international annual rescue efforts.
With Special thanks,
Student Education Trust
Accept all spelling suggestions in the document,
and save document as described above.
STEP 3: Create Address
Create labels for the letters. The Student
Education Trust wishes to use mailing labels to address the envelopes. They own
8 1/2 x 11″ sheets of label paper with 1″ by 4″ labels, which
they purchased from the local Office supplies store. Note: Select a label type
that is similar to this (i.e., 3 labels across, and about 4″ deep).
Note: Be especially aware of the extra spacing that Microsoft
automatically provides (i.e., space after paragraph), and be sure to make sure
the labels are single spaced.
Create a new file for
Merge data from your
Excel spreadsheet into the new label document. You can assume that a standard
address block is required for the labels.
Submit your completed workbook and the two Word
documents (merged letters and merged labels) to the Dropbox. Make sure you post
a comment about what you learned when submitting the files.