Exporting Data from Primavera P6 and Creating Histograms & S-Curves in Microsoft Excel

Managing complex projects often involves analyzing data visually to make informed decisions. One effective way to do this is by generating reports that include histograms and S-curves to showcase cumulative manpower, man-hours, and costs over time. In this blog post, we’ll explore the importance of these visualizations and provide step-by-step instructions for exporting data from Primavera P6 and creating these charts in Microsoft Excel.

Why Use Excel in Conjunction with Primavera P6?

While Primavera P6 is a powerful project management tool, Microsoft Excel offers enhanced flexibility and functionality for creating custom reports. Here are a few reasons why you might choose Excel for generating histograms and S-curves:

Familiarity and Ease of Use: Most users are more familiar with Excel, making it easier to manipulate data and customize reports without extensive training. Excel’s user-friendly interface also enables quick adjustments to charts and graphs.

Customization and Flexibility: Excel offers a wide range of chart types and formatting options, allowing users to tailor visualizations to meet specific needs or preferences, something that may be more limited in Primavera P6.

Data Integration: Excel enables users to integrate data from multiple sources, simplifying the creation of comprehensive reports that incorporate information from other tools or departments. Achieving this level of integration can be more challenging in Primavera P6.

Here’s How to Do It!

Step 1: Open Primavera P6 Professional then follow these steps:

1. Open your project schedule
2. Ensure the schedule is resource loaded and/or cost loaded.
3. Navigate to the Activities Tab
4. Open the Activity Usage Spreadsheet
5. Copy budgeted data (costs or units) from Activity Usage Spreadsheet details form at the bottom.

Step 2: Open Microsoft Excel then:

1. Paste the copied data into an Excel sheet
2. The topmost data (costs or units) is the summary of the data in the period (in months or weeks) of your report. Use Excel SUM formula to calculate the sum, as shown in the video, the formula typed into the cell H3 is “=SUM(I3:AF3)”.
3. Calculate the percentage for each period (month or week) by dividing the sum against each periodic data. For 1-Jan-25, the formula to calculate percentage in cell I4 is I3/$H$3. Make sure to change the cell I4 to percentage format. Select cell I4 and drag across to the last cell AF4.
4. Open another sheet/tab, in this case ‘Weekly Data Sheet’ and create a table of weekly or monthly plan data (%), cumulative plan data (%), actual data (%), and cumulative actual data (%).
5. Copy or use formula to copy the percentages in step 8 to the new sheet. E.g. type =Sheet1!I4 in cell N15. Select cell N15 and drag across to the last cell. These data become monthly or weekly plan values (%).
6. In the Weekly Data Sheet, use formula to calculate the cumulative plan values (%). For Jan 25, in the cumulative plan row, type =N15 into cell N16. For Feb 25, type =N16+O15 into cell O16, then select cell O16 and drag across to the last cell.
7. Also create the row for actual values (%). This will be the earn value/data during execution of the project.
8. Repeat the steps for calculating cumulative Plan values in step 12 to calculate the cumulative actual values (%)
9. Insert Combo chart of Line and Column chart using data in the table in step 10.
10. Move the chart (S-curve/Histogram) to a new sheet (Weekly Overall Curve).
11. The line chart represents the S-curve, and the histograms represent the periodic plan and actual percentages.
12. Format the chart, legends and labels with favorite fonts and colors.