P6 Export to Excel: Number Formatting Secret

excel wont add

We all struggle with the same issue when exporting data from Primavera P6 to Microsoft Excel for analysis, progress updating and graphing. Excel does not seem to recognize numbers as numbers!

That is because P6 formats all data exports as text (appends an apostrophe in front of all data). 

This makes getting clean data from P6 (even filtered data) extremely difficult. Often we would like to manipulate the data in Excel (such as performing calculations in Excel using P6 numeric, date or cost data).

While you might think this has an easy fix in Excel, using the “Format Cells” function will not change the text data to the data format you require. You would actually have to go through each cell individually to change it from text to number! 

This following “secret” tip is an even quicker and easier way to solve this issue using multiplication. Watch the video below to learn how: 

When exporting data from Primavera P6 to Excel, we want to make sure that Excel recognizes numbers as numbers. 

I exported a large set of data from P6 into Excel and as you can see, the numbers are positioned to the left in their fields – meaning, they are formatted as text not numbers. If we want to make that correction, we have to go through each cell individually and hit “Enter” to transform it into a number format.

p6 export to excel

The “Number Formatting” options in Excel also do not resolve this. 

But I have got a quick tip for you! 

We are going to multiply all of these numbers by one to transform them into numbers.

I start by adding “1” to any empty cell, hit copy, and select all of the cells with the number values we want to convert. 

 

p6 export to excel

We then right-click and go to “Paste Special” and go down in the menu to “Paste Special” again to get the pop-up box. 

p6 export to excel

From here, we select the “Multiply” option in the “Operation” section and hit “Ok”. 

select multiply

And this is it! Simple as that. 

p6 export to excel

Another tip I can share with you is using Excel’s “Text to Column” option on the Data tab. 

 

To change the format of exported duration, date, or cost fields from text to the proper data format, start by selecting the column (only select one column at a time).

Here I selected Column E (Original Duration). After selecting your desired column, on the “Data” tab. 

excel data tab

In the Data tab, look within the “Data Tools” group and click on “Text to Columns”

text to column

Next in the “Convert Text to Columns Wizard – Step 2 of 3” dialog, select the “Tab” checkbox under the Delimiter group. In the “Text qualifier” drop-down box, select the apostrophe “’” and click on “Next”.

p6 export to excel

Then, in the “Convert Text to Columns Wizard – Step 3 of 3” dialog do not change any of the default settings but check to confirm the following and click “Finish”. 

  • Under “Column data format” grouping, ensure you selected “General”
  • In the “Destination” textbox, ensure that the column shown is the same as the one selected.

p6 export to excel

We successfully converted the data in Column E (Original Duration) to numbers with the apostrophes removed.

 

I hope you found this tip useful. It has certainly helped me!

Author:
Michael is an avid project controls blogger and is the Chief Learning Officer here at Plan Academy. Michael has taught 1000s professionals how to use project controls software like Primavera P6 over the past 10 years through his online courses and tutorials. Michael is a member of AACE, the Guild of Project Controls and holds his PMP certification from PMI.