Although the schedules you build with Microsoft Office Project 2007 contain much of the information you need to manage your projects, some crucial details are easier to deal with in other formats, such as databases, text documents, or Microsoft Office Excel spreadsheets. For example, information about product defects, their causes, and their resolutions is best suited to a spreadsheet or database file. Even so, you might want some of this type of information available in your project schedule.
Sometimes the information you use to build a schedule to begin with might start in Office Excel 2007. For example, team leaders might build task lists for their portion of a project in Excel. Or, detailed information about available resources might be listed in an Excel workbook. Regardless of whether you want to move a little or a lot of data from Excel, you can use the Excel-to-Project templates to import information into Office Project 2007.
Going the other direction, you’ll find plenty of reasons to transfer some of your Project data into Excel. For example, you can export project cost and earned value data to Excel, where you can create graphs—such as S-curves—to analyze project performance. You can even link Excel information in Microsoft Project to automatically update when that information changes, for example, to update status for change requests that you’ve added to your schedule. If you are spoiled by the power of PivotTables for other kinds of management reporting you do, you can export Project information to Excel and pivot to your heart’s content. In Project 2007, the new visual reports are built-in templates designed to help you choose which Project data to display in Excel and how that data should be presented.
Note
For information about exchanging information with other applications besides Excel, see Chapter 16.
Whether you want to integrate Excel information into your project plan or feed information from Microsoft Project to an Excel workbook, there are several methods for exchanging information. Transferring data between these two programs can simplify aspects of project planning, progress tracking, status reporting, and stakeholder communication.
The methods for transferring information between Microsoft Project and Excel, which are described in deatil throughout this chapter, are as follows:
Copy and paste sheet or graphic information.
You could paste the payment milestones from a Project schedule into the project’s capital budgeting Excel workbook or paste the Excel cells that summarize software defect status in the Project Gantt Chart near the testing and debugging tasks.
Insert, or embed, a Microsoft Project file into an Excel file.
For example, you could embed a Project file into the Excel file you use to select or prioritize projects.
Insert, or embed, an Excel workbook or chart into a Microsoft Project file.
For example, this is helpful if you want to review pending change requests in an Excel file while you scan your Project schedule for resources with available time.
Link information dynamically between the Microsoft Project and Excel files, so that when that information changes in one file, the same information in the other file automatically updates to reflect that change.
Linking information is ideal when information changes frequently. For example, if you store risk analysis data in Excel, linking current cost projections in Excel to Project cost fields keeps your estimated project costs up to date automatically.
Open, or import, an Excel file as a Microsoft Project file, or import a Microsoft Project file as an Excel file.
This method comes in handy when you have schedule-related data in Excel that you want to use in Project, such as an initial task list or a resource list. Similarly, importing a Project file to Excel means you can use Excel’s data analysis and formatting to analyze project information, which is particularly helpful if you don’t have access to Microsoft Project Server and its enterprise project management features.
Save, or export, a Microsoft Project file as an Excel file. The file is converted to the Excel file format so it can simply be opened in Excel. You can also export an Excel file as a Microsoft Project file.
Saving a Project file as an Excel file, or vice versa, is equivalent to importing. Your choice to import or export hinges on which program you are more familiar with or perhaps, which one is open at the time.
By using the Copy and Paste commands, you can easily exchange sheet data and static graphics between Microsoft Project and Excel. For example, if you have an Excel file with a proposed list of tasks (with estimated durations) for a project, you can copy the cells for the tasks into a Microsoft Project Task Sheet to get your project schedule started. Similarly, the Copy and Paste commands work well if you want to display a graphic from one program in the other, for example, to show an Excel graph of sensors installed each week within the Gantt Chart of an equipment deployment project.
You can also use the Copy command to insert an embedded object. The benefit of embedding an Excel file within a Microsoft Project schedule, or vice versa, is that you can open the embedded object without switching programs and without having to access the original file.
Note
See the section titled Embedding Between Microsoft Project and Excel later in this chapter.
You might want to copy two types of information from Excel worksheets:
Data from Excel worksheet cells into cells in a sheet view in Microsoft Project.
Copying data from Excel cells to Microsoft Project cells is a wonderfully simple way to import data from Excel to Microsoft Project—as long as the columns of information in Excel and Microsoft Project line up.
Images of Excel graphs in graphically oriented areas in Microsoft Project.
Excel is the program of choice when you want to graph numeric data. If you want to display a graph within your Microsoft Project file, for example, a pie chart or a histogram, you can copy the graph from Excel to the chart area of a Gantt Chart view or certain other nontable areas in Microsoft Project.
Anything in Excel worksheet cells can easily be copied and pasted into a Microsoft Project table. However, the order and data type of the columns of information must match, so some up-front preparation is needed in either the Excel workbook or the Microsoft Project table.
Match the Columns
Copying and pasting information is one of the easiest ways to get information from Excel to Microsoft Project. However, copy and paste success depends on the correct match-up of the columns of Excel information you’re copying and the Microsoft Project columns into which you’re pasting.
For example, suppose that your Excel worksheet contains four columns you want to copy to your project plan: Task Name, Resource, Duration, and Fixed Cost. To copy these columns from Excel to Microsoft Project, you must first add, remove, and rearrange columns in a Microsoft Project task table so that the corresponding four Microsoft Project columns appear next to each other (see Figure 17-1).
Figure 17-1. Insert columns in Microsoft Project so that the table columns match the order and type of the Excel data being copied and pasted.
The following are techniques you can use to arrange your Microsoft Project view to match the incoming Excel data:
Apply a different table to a Microsoft Project sheet view. Click View, Table and then click the table you want.
Add a column to a Microsoft Project table. Click in the column to the right of where you want the new column to appear. Click Insert, Column. In the Field Name box, click the field you want to add and then click OK.
Remove a column from a table. Click the column heading and then click the Delete key.
Move a column to a different location. Click the heading to select the entire column. When the mouse pointer changes to a four-headed arrow, drag to the location you want.
A vertical gray marker appears to show where the column will be moved.
To copy and paste Excel worksheet cells into Microsoft Project, do the following:
Arrange the source Excel columns to match the target Microsoft Project field order and data type. Or, arrange the target columns in Microsoft Project to match the incoming Excel data.
In Excel, select the set of cells to be copied.
On the Home tab of the Ribbon, in the Clipboard group, click Copy.
In Microsoft Project, select the anchor cell in the table where you want the incoming information to begin to be pasted.
This anchor cell will become the location of the upper-left cell of data selected in Excel.
On the Standard toolbar in Microsoft Project, click Paste.
Copying an Excel graph and pasting it into specific areas of Microsoft Project as a picture is the most obvious application of copying Excel graphics into Microsoft Project. However, you can also paste Excel worksheet cells as a picture in Microsoft Project, instead of pasting those cells into table cells.
To paste Excel information as a static picture, do the following:
In Excel, select the chart or other data you want to insert as a picture in Microsoft Project.
On the Home tab of the Ribbon, in the Clipboard group, click Copy.
In your Microsoft Project file, display the location at which you want to place the picture. The following locations in Microsoft Project can accept pictures:
Chart area of a Gantt chart
Notes tab in the Task Information, Resource Information, or Assignment Information dialog box
Objects box in the Task Form or Resource Form
Notes box in the Task Form or Resource Form
Header, footer, or legend of a printable view or report
Click Edit, Paste Special.
The Paste Special dialog box appears.
Select the Paste option.
In the As box, click Picture or Picture (Bitmap), as illustrated in Figure 17-2.
Figure 17-2. Use the As box in the Paste Special dialog box to specify that you want to insert the Excel information as a picture.
This option ensures that the data, whether it’s a chart or a set of worksheet cells, is pasted as a graphic rather than as an embedded graphic or straight text.
Click OK.
The new picture is pasted in Microsoft Project at your selected location (see Figure 17-3).
Although static pictures look like embedded objects, you cannot open or edit them. However, you can drag the entire object or a side to move or resize them.
Note
For more information about embedded objects, see the section titled Embedding an Excel Object in Microsoft Project later in this chapter.
Copying information from Microsoft Project and pasting it into Excel worksheets is one solution when you need to process project data in a way that Microsoft Project doesn’t handle easily. Likewise, if you’re more comfortable with Excel’s sorting, grouping, and formatting features, you can copy data from Microsoft Project to Excel to quickly obtain the results you need, for example, when you’re rushing to prepare for a hastily convened meeting.
Copying and pasting data from a Microsoft Project table to Excel worksheet cells is actually easier than the reverse process. Unlike copying from Excel to Microsoft Project, the columns or data types don’t have to match, because all Excel fields can accept any data type.
To copy Microsoft Project table cells and paste them into Excel worksheet cells, follow these steps:
In Microsoft Project, display the view that contains the information you want to copy to Excel.
If necessary, apply a different table or add columns that contain the information you need.
Select the cells or columns you want to copy.
To select a column, click its heading. Select multiple adjacent columns by dragging across the column headings. Select multiple nonadjacent columns by holding down Ctrl while you click each column heading.
On the Standard toolbar, click Copy Cell or press Ctrl+C.
In Excel, select the anchor cell in the worksheet where you want the incoming information to begin to be pasted.
This anchor cell will become the location of the upper-left cell of data selected in Microsoft Project.
On the Home tab of the Ribbon, in the Clipboard group, click Paste.
The selected project data appears in the Excel worksheet starting at the anchor cell. Adjust column widths in the worksheet as necessary to see the data (see Figure 17-4).
You can copy a picture of any Microsoft Project view and then paste it into Excel. To do this, follow these steps:
In Microsoft Project, display the view you want to capture as a picture for Excel.
Manipulate the view to show the information the way you want it to appear in Excel.
On the Standard toolbar, click Copy Picture.
In the Copy Picture dialog box, select the options you want.
In the Render Image section, select the option for the most likely method for viewing the image: on a computer screen (the default), printed, or a GIF image file (ideal for pictures published to the Web).
In the Copy section, select the Rows On Screen option (the default) if you want the rows currently showing on the screen to be copied. The Selected Rows option copies the currently selected rows whether or not they appear on the screen.
In the Timescale section, select the As Shown On Screen option (the default) if you want the timescale to be represented as set in the current view. Select the From and To boxes if you want to specify a timescale and date range different than what appears on the screen.
When you’ve finished setting the Copy Picture options, click OK.
In Excel, select the location in the worksheet where you want to paste the picture of the view.
On the Home tab of the Ribbon, in the Clipboard group, click Paste.
A static picture of the Microsoft Project view appears on the Excel worksheet (see Figure 17-5). You can move the image by dragging it. You can also resize the image by dragging any of the edges.
