A lot of dreadful Primavera P6 advice continues to clog the internet. Today's subject is the spreadsheet export feature in Primavera P6. Most of you realize that the XER export file is text-based and can be opened with any text editor. When Primavera P6 data is exported to a Microsoft Excel spreadsheet this creates some confusion because Excel sees text in fields like durations and budgets and therefore displays it as such.
Excel will flag this text and provides an easy solution for converting the text to numbers. Now, when I am showing a client a cost-loaded spreadsheet it will not be possible to ascertain the totals unless the text is converted to numbers. But the ONLY reason I convert the numbers is for the purpose of adding up budgets. This "fix" is otherwise unnecessary.
Below is a simple example. After exporting Primavera P6 data to Excel, I changed the duration of one activity (outlined in red). Excel recognizes that I am typing a number and for this reason the "5" lines up on the right side of the column. All other durations are still being stored as text and therefore line up on the left side of the column. You can also see Excel's warning a couple of rows below the changed duration.
Here is where the bad advice comes into play. Some Primavera P6 users claim that any revisions to durations and budgets must be changed back to text before importing the Excel spreadsheet into Primavera P6. f you want to convert the numbers to text, fine, but you are just wasting your time. This bad advice refuses to die, as I verified just a few days ago on a popular project planning website.
I figure I have exported and imported several thousand Excel spreadsheets and have never had an issue with text versus numbers being in the same column. Spreadsheets are great for gathering or confirming data. There are a few nuances to working with spreadsheets but this is not one of them. Numbers or text in the same column are fine, assuming we are talking about durations or budgets.
You should watch out for an asterisk inside parentheses in a column title, as this signifies data that will not import into Primavera P6. In the spreadsheet excerpt, the Start, Finish, and Resources columns have these asterisks. In fact, most import errors have to do with mismatched data such as a number appearing in a date column, or vice versa.
