Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've been using Excel to process data from a Sharepoint list for a while now. However, I recently added a column to the Sharepoint table, and when I refresh the data in Excel, I don't get the new column.

I perform a lot of calculations based on this data, so creating a new worksheet with the "new" Sharepoint list, moving the calculations and the pivots to THAT sheet is more hassle than I'd like to face.

Is there a way to force Excel to display this new column that I've added? Maybe by modifying the connection string?

share|improve this question

The excel file that you exported is connected to a view, not to the list. Therefore, you need to add the new column to this view apart from adding it to the list. Afterward, refresh your excel data.

share|improve this answer

Since you mentioned that you are 'refreshing' the data in Excel, it sounds as if you are using Excel's 'MSQuery' feature. Those can be modified and saved; here's how I'd do it in one of my workbooks in Excel 2007. Note that these steps apply to modifying a SQL server-based MSQuery. I've not done a SharePoint-based MSQuery, but hope it's similar:

  1. Click anywhere in the refreshable dataset. Then at the top of your workbook, select 'Data', then 'Properties', then the 'Connection Properties' icon next to the connection name.

  2. That should bring up a "Connection Properties" form, which should have a 'Usage' and 'Definition' tab. Select the 'Definition' tab, then at the lower left, click the 'Edit Query' button.

  3. That should bring up a 'Query Wizard' form. At this point you have 2 nice options:

    a. You ought to be able to bring in your new column from here, when you scroll to your source table in that Query Wizard.

    b. If you want some more flexibility, you can hit the 'Cancel' button on that 'Query Wizard' form, and it will ask you "Do you want to continue editing this query in Microsoft Query?" Select 'Yes'.

    This should bring up a 'Microsoft Query' editor. You should be able to drag-and-drop your new column into the query here. You also have the 'SQL' button at the top, and you can make direct edits to your SQL statement.

Whichever one you choose, when you are done, be sure to then select 'File', and 'Save'.

I hope this works for you; again, I'm not familiar how Sharepoint plays with MSQuery. Best of luck.

share|improve this answer
Ugh, can't edit the query. I created the table using the "Export to Spreadsheet" option in the Sharepoint List "Actions" menu. If there were another way to get the data, then maybe I could edit the process itself. – Jono Jan 11 '11 at 7:42

You must add your column to the view but this cannot be done using the browser as the view will be hidden and does not allow modifications.

  1. Open the site with SharePoint Designer.
  2. Click on All files -> Lists -> [LIST NAME] -> [Excel view]
  3. Add the field references manually (using internal names)
  4. Save the view
  5. Refresh the data connection in Excel.
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .