Pivot Tables and Hyperlinks

Topic

New Reply

Hi to the Excel gurus…

Gotta begin by saying, WOW, what a difference. Gonna take a little getting used to…

On to my issue: I have a user who utilizes a synchronized list in Excel from Sharepoint. One of the fields in the list contains hyperlinks to various internal web sites.

However, when the user creates a pivot table from the list, the pivot table shows the text of the hyperlink, but there is no pivot table.

I’m pretty sure I read a while back that Excel pivot tables do not include live hyperlinks from the data source. I am writing here to see if that is correct, or if, in fact, there is a way to capture the live hyperlinks within the pivot table.

This question applies to both Excel 2003 and Excel 2007.

As always, thank you in advance…

George

Reply | Quote

Replies

I have a hard time visualizing what you’re talking about – could you attach a sample workbook (with sensitive information removed or altered)?

Reply | Quote

I have a hard time visualizing what you’re talking about – could you attach a sample workbook (with sensitive information removed or altered)?

My apologies, Hans. In my original message I stated that there was “no pivot table”. What I meant to say was there was “no hyperlink, just text”.

Does that make it any clearer?

Reply | Quote

Does that make it any clearer?

No, sorry.

Reply | Quote

OK, I have created a small sample for you to view.

Note that in column B of the data, each cell contains a hyperlink (at least it does here).

Now note that in the pivot table to the right, that field contains text instead of the hyperlink.

Is there any way to have the pivot table show the hyperlink, or is this something that pivot tables just will not do? Again, this question applies to both Excel 2003 and Excel 2007.

And again, thank you for all your efforts. Very much appreciated.

Reply | Quote

The items in a pivot table are just text, even if the source data contain hyperlinks. From Use a PivotTable report to make external table data available in Excel Services:

… active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document…

Reply | Quote

OK, I have created a small sample for you to view.

Note that in column B of the data, each cell contains a hyperlink (at least it does here).

Now note that in the pivot table to the right, that field contains text instead of the hyperlink.

Is there any way to have the pivot table show the hyperlink, or is this something that pivot tables just will not do? Again, this question applies to both Excel 2003 and Excel 2007.

And again, thank you for all your efforts. Very much appreciated.

I dont know about Excel 2007. However, in Excel 2003 and prior, I don’t beleive you can do this. A Pivot Table is desiged to work using data supplied by a User. In your case the Hyperlinks are data just like the other data and numbers. When you generate a Pivot Table it is extracting data, not formatted data.

Once a Pivot Table has extracted data the User must tell the new Pivot Table how to format the extracted data. The Pivot Table Tools do not allow for formatting Hyperlinks.

Perhaps VBA could get around this but I would think not.

Regards,

Tom Duthie

Reply | Quote

I dont know about Excel 2007. However, in Excel 2003 and prior, I don’t beleive you can do this. A Pivot Table is desiged to work using data supplied by a User. In your case the Hyperlinks are data just like the other data and numbers. When you generate a Pivot Table it is extracting data, not formatted data.

Once a Pivot Table has extracted data the User must tell the new Pivot Table how to format the extracted data. The Pivot Table Tools do not allow for formatting Hyperlinks.

Perhaps VBA could get around this but I would think not.

Regards,

Tom Duthie

Hi Tom,

Thanks for the update. The source that Hans sent applies to Excel 2007 and does state directly that pivot tables do not support active hyperlinks, so if there is a workaround, it would have to be in VBA.

Regards,

Reply | Quote

Thanks, Hans. I had thought so, but could not find an authoritative source.

Very much appreciated, as always…

Reply | Quote