Jump to content


Adding referencing to Pivot Tables

pivot table paste values data model

  • Please log in to reply
1 reply to this topic

#1 mdavids2



  • Members
  • Pip
  • 8 posts

Posted 14 February 2014 - 08:34 PM



I'm a CPA and I work a lot with Pivot Tables.  When I create pivot tables I would like to be able to add references to (on right) and from (on left).


I've attached an Excel file showing (in table format) what I would like to be able to do.


My question is: Is there a way to insert a column between pivot table columns where I can add manual text.


Thank you,




Attached File  Excel question.xlsx   10.41KB   302 downloads

#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 15 February 2014 - 12:49 AM

Hi Mike,


There are two answers to your question depending upon whether you are using Excel 2007/2010 or Excel 2013.


Excel 2007/2010


Unfortunately you cannot insert new columns into an Excel 2007/2010 pivot table.


You can, however, copy the pivot table and paste it "as values", which will extract the data from the pivot table and allow you to edit it in any way you wish (but, of course, it will then no longer be a pivot table)


For full instructions on how to paste "as values", see Essential Skills Lesson 3-5: Use Paste Values and increase decrease decimal places displayed.


For instructions on how to create a pivot table, see Expert Skills Lesson 5-1: Create a pivot table.


Excel 2013


This version of Excel introduces a brand new Data Model feature covered in Expert Skills Session Six: The Data Model, OLAP, MDX and BI


Pivot tables based upon a Data Model look very similar to those based on a single range but actually work in a different way and have different features.  Their primary feature is the ability to reference more than one source data table a the same time.


Without going too far into the details, a Data Model based pivot table allows you to convert the pivot table into a set of formulas that directly reference the data behind the pivot table.  In this case the (converted)  pivot table is still a pivot table but you can insert columns, cut, copy and paste values (that still reference the pivot data) and also format the pivot table in any imaginable way. 


There are several new concepts associated with the Data Model that you need to learn so we have devoted an entire session to the Data Model in the Excel 2013 Expert Skills course.


If you need any more assistance with this, please feel free to reply to this post.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users