Excel 2016 Pivot Table: Format Lost & Text truncating

Question

Hello Expert,

I use Excel 2016 Pivot Table for my daily report. I found two questions, I did not find a resolution, could you kindly help me? Thanks:

Q1: How to let the format of a row same as the source data?

Q2: When a row have long text, how to avoid the text was truncated?

Source Data:

Category	ScopeVersion	Contents
Management	15.1R5	Support for YANG features, including configuration hierarchy must constraints published in YANG, and a module that defines Junos OS YANG extensions (EX Series)—Starting with Junos OS Release<br> 15.1, the Juniper Networks configuration YANG module includes configuration constraints published using either the YANG must statement or the Junos OS YANG extension junos:must. Constraints that cannot be mapped directly to the YANG must statement, which include<br> expressions containing special keywords or symbols such as all, any, unique, $, __, and wildcard characters, are published using junos:must.<br> <br><br>The junos-extension module contains definitions for Junos OS YANG extensions, including the must and must-message keywords. The junos-extension module is bound to the namespace URI http://yang.juniper.net/yang/1.1/je and uses the prefix junos. You can download<br> Juniper Networks YANG modules from the website, or you can generate the modules by using the show system schema operational mode command on your local device.
Management	15.1R5	Support for enforcing RFC-compliant behavior in NETCONF sessions (EX Series)—Starting with Junos OS Release 15.1, you can require that the NETCONF server enforce certain behaviors during the<br> NETCONF session by configuring the rfc-compliant statement at the [edit system services netconf] hierarchy level. If you configure the rfc-compliant statement, the NETCONF server explicitly declares the NETCONF namespace in its replies and qualifies all NETCONF<br> tags with the nc prefix. Also, <get> and <get-config> operations that return no configuration data do not include an empty <configuration> element in RPC replies.
MPLS	15.1R5	New command to display the MPLS label availability in RPD (EX Series)—Starting with Junos OS Release 15.1, a new show command, show mpls label usage, is introduced to display the available label<br> space resource in RPD and also the applications that use the label space in RPD. Using this command, the administrator can monitor the available labels in each label space and the applications that are using the labels.
Port Security	15.1R5	Media Access Control Security (MACsec) support (EX9200 switches)—Starting with Junos OS Release 15.1R1, MACsec is supported on all SFP interfaces on the EX9200-40F-M line card when it is installed<br> in an EX9200 switch.  MACsec is an industry-standard security technology that provides secure communication for all traffic on point-to-point Ethernet links. MACsec is capable of identifying and preventing most security threats, and can be used in combination<br> with other security protocols to provide end-to-end network security. MACsec can only be enabled on domestic versions of Junos OS software. MACsec is standardized in IEEE 802.1AE.
Port Security	15.1R5	MAC move limiting support (EX9200 switches)—Starting with Junos OS Release 15.1R1, MAC move limiting is supported on EX9200 switches. MAC move limiting provides port security by controlling the<br> number of MAC address moves that are allowed in a VLAN in one second. When MAC move limiting is configured, the switch tracks MAC address movements on access and trunk interfaces. A MAC address move occurs when an interface on the switch receives a packet<br> with a source MAC address that has already been learned by the switch, but on a different interface. If a MAC address moves more than the configured number of times within one second, you can configure an action to be taken on incoming packets with new source<br> MAC addresses. The incoming packets can be dropped, logged, or ignored. You can also specify an action to shut down or temporarily disable the interfaces associated with that MAC address. <br><br>[See Understanding MAC Limiting and MAC Move Limiting for Port Security on EX Series Switches.]

2. Pivot table:

Category	ScopeVersion	Contents
Management	15.1R5	Support for enforcing RFC-compliant behavior in NETCONF sessions (EX Series)—Starting with Junos OS Release 15.1, you can require that the NETCONF server enforce certain behaviors during the NETCONF session by configuring the rfc-compliant<br> statement at th
		Support for YANG features, including configuration hierarchy must constraints published in YANG, and a module that defines Junos OS YANG extensions (EX Series)—Starting with Junos OS Release 15.1, the Juniper Networks configuration<br> YANG module includes co
MPLS	15.1R5	New command to display the MPLS label availability in RPD (EX Series)—Starting with Junos OS Release 15.1, a new show command, show mpls label usage, is introduced to display the available label space resource in RPD and also the<br> applications that use the
Port Security	15.1R5	MAC move limiting support (EX9200 switches)—Starting with Junos OS Release 15.1R1, MAC move limiting is supported on EX9200 switches. MAC move limiting provides port security by controlling the number of MAC address moves that<br> are allowed in a VLAN in one
		Media Access Control Security (MACsec) support (EX9200 switches)—Starting with Junos OS Release 15.1R1, MACsec is supported on all SFP interfaces on the EX9200-40F-M line card when it is installed in an EX9200 switch.  MACsec is<br> an industry-standard secur

Answer 1

I have a resolution for those two issues:

for Q2, I use VBA.

for Q1, I use Google sheet to do a pivot to resolve the problem. Then copy the sheet to Microsoft Excel.

Thank all of your help.

If you want the VBA, please reach me by ******@126.com

Answer 2

I've tested this on two computers with Office 365, one on Insider Fast and the other one on Insider Slow and I have way more than 255 characters in the pivot tables. 

I'll try to find out more and report back.

Answer 3

Hi Teylyn,

Thanks for your information.

I see. That's why I post those two issues in Microsoft community, I think it is a complex issue.

For the first problem, it maybe resolves by Macro.

But for the second one, it seems a limitation of Excel 2016. Yes, the cell can hold more the complete text, but in the pivot table, the cell holds hold maybe 255. I don't know if this can be adjusted. 

Again, thanks for your information.

Answer 4

Hello,

I'm afraid you'll have to adjust your expectations. Pivot tables will only pull the values from the source data and it looks like your pivot row fields are truncated at 255 characters. I can see many more characters in Excel 2016, but I probably have a newer release than you do.

Pivot tables are great for crunching numbers, but not for arranging large amounts of text.

You may want to use another tool that is better with text.

Answer 5

My expectation is:

1. the contents field format: As the format in original Excel, the text before "-" with "bold" format
2. the text in contents field is complete. You can see the following picture, some words missing.

Picture 1: What I expected

Picture 2: the current pivot table

picture 3: format I expected

picture 4: source data