Microsoft Office 2013 Projects
Unit I: Integration Projects III
Unit Overview
In the final unit of Integrated projects, you combine source materials from all four applications: Word, Excel, Access, and PowerPoint. You first create a report in Word that includes components from Excel, Access, and PowerPoint, and then you create two presentations in PowerPoint that include components from Word, Excel, and Access. The goal of this unit is to increase you’ understanding of how you can perform specific tasks with all four applications in the Office 2013 Suite.
Features that you should review with this Project:
Project 1: Status Report for Unity Fitness
PR-I Unity Fitness Classes.rtf
PR-I Unity Fitness Classes.xlsx
PR I-Unity Fitness Members.rtf
PR I-Unity Fitness Report.docx
PR I-Unity Fitness.accdb
In Project 1, you create a report in Word that includes objects from Access, Excel, and PowerPoint. Here is a list of objects you create or insert into the Word report:
- PowerPoint slide created on the report title page
- Access table exported into an .rtf file and then copied to the Word report and formatted
- Access query exported to Excel, then a pie chart is created in Excel and copied to the Word report
- PowerPoint slides copied to the Word report
- Access report copied from Access, pasted into the Word report, and then formatted
You may find Project 1 quite challenging because it involves using all four applications. Before you begin Project 1, you may want to review the following materials.
Inserting a PowerPoint Slide
In Steps 2 and 3, on page 222 of the text, you are instructed to insert a PowerPoint slide as an object on page 1 of the Word report. Ask you to check the list of object types in the Insert Object dialog box to determine what other types of objects can be created in a Word document. For example, you can insert a media clip, an equation, an Excel chart, and even an Adobe Acrobat document. I encourageyou to experiment with some of the different objects to see how you are inserted into a Word document. You are inserting the slide as an object, rather than cutting and pasting, because you do not have to open PowerPoint in order to do so.
Modifying a Gradient Fill
After you insert the PowerPoint slide on page 1 of the Word report, you are instructed to modify the slide background with a gradient fill. I adviseyou to experiment with the many ways in which you can modify and customize a slide background.
Applying Heading Styles
In Step 6 on page 222 of the text, you apply the Heading 1 style to all the document headings so that in step 7you can generate a table of contents. If you are not yet familiar with the table of contents feature, review how a table of contents is created from text formatted with the Heading 1, Heading 2, and Heading 3 styles.I adviseyou that you will be updating the table of contents after you have inserted all of the objects required for the report. When the table of contents is updated, the page numbers will change.
Reviewing Relationships
In Step 10 on page 222 of the text, you open the Relationships window in Access and review the relationships between four related tables. I encourageyou to study the relationships and the captions in Figure I-3 on page 223 of the text to increase their understanding of how the data has been divided among the four tables to reduce data redundancy.
Using the Total Function in Access
In Step 7 on page 224 of the text, you are instructed to use the Totals button to group the Fitness Class Registrations query by the Registration ID column and then use the Count function to count how many members took each of the fifteen fitness classes. Review the options available from the Totals button and discuss how these options increase the data analysis capabilities of Access queries.
Analyzing Access Data in Excel
In Steps 8 and 10 on page 224 the text, you are instructed to export the Fitness Class Registrations query to Excel and then to complete the worksheet as shown in Figure I-6 on page 225. I adviseyou to carefully check the captions in Figure I-6 on page 225 of the text to ensure that the correct formulas are entered.
Creating a PivotTable in Excel
In Steps 2 through 5 on page 226 of the text, you use the PivotTable function to quickly calculate the total revenue from each fitness category. You then create a PivotChart. Discuss PivotTables with you and remind you how you can be used to easily summarize and calculate data.
Copying Slides from PowerPoint
In Steps 7 through 9, on page 226 of the text, you are instructed to copy two slides from a presentation and then paste you into a table form in the Word report. Remind you that you must work in Slide Sorter view when you want to copy a PowerPoint slide from PowerPoint to another application. Also remind you how useful the table function is for displaying graphics such as the PowerPoint slides side by side.
Adding a Report from Access
In Steps 4 through 7, on page 228 of the text, you are instructed to convert the Access report into a table so that you can easily apply formatting. You first need to delete the last row of the table that contains the page number. The row will be very tall. Remind you that Word enters a number of columns that is equal to the maximum number of tab characters in any one line of text. In the report exported from Access, a tab character appeared at the beginning of each line; as a result, the first column in the converted table is blank. You remove this blank column, adjust the position of the total amount, then apply a table design.
Project 2: Welcome Presentation for Goose Point Estates
PR-I Goose Point Estates Data.xlsx
PR I-Goose Point Estates Low Sale Prices.docx
PR I-Goose Point Estate Orietnation.pptx
PR I-Goose Point Estates.accdb
In Project 2, you create a presentation in PowerPoint from a Word outline and then insert data entered in Access and analyzed in Excel. After modifying the PowerPoint presentation, you update the Access database, which in turn updates the Excel worksheet and the slides in the presentation. By creating links between Access, Excel, and PowerPoint, you learn how to use the Office Suite to save time when creating presentations that require frequent updates. New values are entered only once in the Access database. The relevant data is then automatically updated in Excel and PowerPoint.
You first create a table in an Access database and then copy the table and paste it into Excel as a link. The sequence of tasks in Project 2 is intended to mirror the process required in the real world to create a presentation from existing materials. Creation of the database comes firstas it would in a business situation. Sales information is first recorded in a database and then presented as part of additional materials created in the other Office applications.
Before you begin Project 2, you might want to review the following materials.
Copying Data to Excel
In Step 8 on page 230 of the text, you are instructed to copy the Design Breakdown query and then paste it as a link into a new Excel workbook. Discuss with you why you are not using the Export feature to copy the table from Access table to Excel. The Export feature places Access data in Excel; however, the data is no longer linked to the Access table. The Paste Link command is used to paste an Access table into Excel as a link so that changes made to the Access table are also made to the data copied to Excel. Note also that the Currency format is removed when a table is copied from Access and pasted into Excel as a link.
Creating a PivotTable and PivotChart
In Steps 4 through 9, on page 232 of the text, you are instructed to create a PivotTable to analyze data in the data copied from Access and pasted into Excel as a link. You then use the PivotChart function to create a column. Stress the importance of entering formulas to calculate the values required for the PivotTable and PivotChart. If formulas are not entered as directed in Steps 1 and 2 on page 232 of the text, the updating tasks required in the final activity will not work.
Importing the Word Outline
In Step 1 on page 234 of the text, you insert the outline for the presentation from a Word file. Ensure that you understand that the text in the Word document must be entered in Outline view in Word so that each heading level is assigned a heading style. If the presentation text is just typed into a normal Word document, the text will not be transferred as slide titles and bulleted items in PowerPoint.
Think about the situations where you might need to send a Word outline to a PowerPoint presentation. For example, you might have a report already created in Word that you then want to summarize in a PowerPoint presentation. As long as the report headings are formatted with heading styles, you can easily use the headings as the basis for a PowerPoint presentation. Using Word source materials for a PowerPoint presentation saves typing time and allows you to present the same material in different ways.
Updating the Presentation
In Step 5 on page 236 of the text, you display Access and enter new values in the Home Designs table. Remind you to press [Enter] after you make each change (including the last change). If [Enter] is not pressed after making the final change, all the updated data will not appear in the presentation. Note that in Excel, the PivotTable must be refreshed to show the new values entered in Access.
You might need to wait up to five minutes before the changes made in Access are reflected in the PowerPoint slides.
Breaking Links
In Steps 9 and 10 on page 236 of the text, you are instructed to break all links between the applications. With all the links broken, the presentation can be distributed on its own without the accompanying files.
Exiting and Then Opening All Programs
The Clues on page 236 of the text describes how to maintain links in a PowerPoint presentation that is linked to an Access database and an Excel workbook. Note that you need to keep the files all together in the same location to avoid receiving error messages. You should also open the Access database first because the original source material to which the data in Excel and PowerPoint are linked appears in Access.
Project 3: Event Presentation
PR-I Book Launch Budget.xlsx
PR I Book Launch Details.docx
PR I Book Launch Picture.jpg
PR I Book Launch Presentation.pptx
In Project 3, you work in Excel, Word, and PowerPoint (not Access) to develop a presentation. The goal of Project 3 is to show you how you can adapt materials already entered in other programs for use in a PowerPoint presentation. You should be encouraged to recycle materials—that is, adapt and modify materials already entered in other files and applications to save time and reduce errors of inconsistency.
Working with the Excel Template
In Steps 1 through 5 on page 238 of the text, you work with one of Excel’s templates. The template consists of three worksheets. I adviseyou to student Figures I-23 and I-24 on page 239 of the text carefully to ensure you match all values.
Modifying and Saving a Picture File
In Step 9 on page 238 of the text, you insert a JPG file and then apply the Washout color effect to modify the appearance of the picture. You then save the picture as a file, delete the picture, and then select the picture as the background for the entire presentation
Rememberyou can open and modify a picture file in PowerPoint, and then save the picture file with a new name. Discuss with you situations where this capability may be useful. For example, you may have a picture that you would like to change to black and white and then use in a presentation or for another purpose. You can insert the picture into a PowerPoint slide, modify it, and then save it with a new name.
Adding a Custom Background
I encourageyou to experiment with the various options available in the Background dialog box. If you are able to give an online presentation, I encourageyou to think carefully about the effect on an audience of the background or slide color schemes chosen for a presentation.
Setting the Transparent Color in a Picture
In Step 9 on page 240 of the text, you are instructed to use the Set Transparent Color feature to remove the background from a photograph. Explain to you that this feature can be used to remove any color from a picture, leaving the slide background to show through.
You should read and work through Pages Integration 222-Integration 241 to review all features being covered in Integration Projects 1. There are several Hints, Figures and Tables that provide additional information on applying these features.