Display text entries in the data area of a pivot table

{ 60 Comments }

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names in column A.

The task is to reorient this database to show all part numbers in column A and parts supplied by those vendors should appear in different columns of that row.  If one attempts to create a pivot table, the problem would be with the data area of the pivot table.  When one drags the part codes (alphanumeric string) column to the data area, count of part number would appear instead of the actual part number.  This is the very nature of a pivot table.  Since it is a summarisation tool, one can never see text entries in the data area of a pivot table.

You may download the workbook showing both solutions (using MS Query and Power Query) from here.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (minimal formulas) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  You may refer to Solution2 – Power Query worksheet.

You may watch a short video of my solution here

Solution for all versions of MS Excel

You may refer to Solution1 – MS Query worksheet.  To reorient the data i.e. to view text entries in the data area of a pivot table, try the following approach:

1. In cell C1, type Count.  So the value in cell A1 is Vendor, in cell B1 is Part_code and in cell C1 is Count
2. In cell C2, enter =$B$1&” “&COUNTIF(A$2:A2,A2) and copy down.  Since the number of rows here are quite many, i would suggest copying in batches i.e. first copy down 5,000 rows, then another 5,000 rows and so on.  This is solely to prevent Excel from crashing and/or taking unduly long to process.
3. Select range A1:C20237 and press Ctrl+F3 > New.  In the name box, enter Dummy
4. Select range A1:C20237 and press Ctrl+T to convert to a Table.  This is to take care of data being added beyond row 20237.
5. Save the file and open a new worksheet (in the same workbook)
6. While on any cell in this new worksheet, go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
7. Navigate to the folder where you saved the file in step 5 above, select the file and click on OK
8. With Dummy selected by default, press the > symbol.  This will show you three column headings in the right hand side box
9. Click on Next three times
10. Select the second option button and click on Finish
11. Click on the SQL button and delete all contents that you see there.  Type the following in the blank white space

TRANSFORM first(Part_code)
SELECT Vendor
FROM dummy
GROUP BY Vendor
PIVOT count

12. Click on OK
13. Go to Data > Return Data to MS Excel
14. In the Import Data box, select Table and in Existing sheet, select cell A1
15. Click on Finish

Data should now appear in the desired format.

For any changes to data in range A2:B20237, right-click on any cell in the output range and select Refresh.  Furthermore, since the range has been converted to a Table, you may even add further rows of data and simply right-click to Refresh the output data range.

To improve performance of your workbook, you may want to copy the formulas in column C and paste them as Values (Paste Special > Values).  Once again, do not paste special all values at once – do so in groups of 5,000 rows to prevent Excel from crashing and/or taking unduly long to process.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Dear Ashish, trying your example but the three colum titles do not appear in step 8. Only Dummy – Vendor. It seams that =$B$1&” “&COUNTIF(A$2:A2,A2) is not working as it shows #NAME? in C2 and further.

    What is going wrong ?

    Thanks, –Igor

  • hi, im wondering if this could work on the assignment im working on. as my lecturer do not teach this. so do u mind take a look and give me some idea how should i do it?

    • Hi,

      If your question is related to “Displaying text entries in the data area of Pivot Table”, then post your question here with a detailed description. Also, upload your file to SkyDrive and share the link of the uploaded file for better understanding.

      • Don’t know if I can post another question in this thread, but I think it is still related and will help other people too…

        Was wondering if you can help me out with an extra bit to the initial problem.

        If I wanted to add, let’s say a column for Branch where the specific part, mentioned above is found so that we can also show this Branch column in our summarised and linked table in the end. So instead of having only Vendor and Part_Code, you will also have Branch in your initial data set. In the output the idea is to have the Vendor listed with all its parts in the same row now, but then an additional column for Branch next to the last part.

        Thanks in advance.

  • Hi Ashish,

    I’m trying to recreate your power query solution so I better understand it, but replicating the steps listed on the right hand side doesn’t work. For instance, how did you get the program to list the expanded columns as ‘Grade1’, ‘Grade2’, etc? Why was the custom column called ‘Grade’ and not ‘Custom’ (manually renaming it leaves a ‘renamed column’ step, but I don’t know how else it could be renamed)?

    As well, for my purposes, I want the outcome to be a little different. Instead of the columns being headed ‘Grade1’, ‘Grade2’, etc, is there anyway to have them listed ‘A’, ‘B’, ‘C’, etc, and then in each cell is listed those items of a third column that match the grade (A, B, etc) and the S_No? So, if there was a third column giving a unique name to each item, is there any way to produce a table with S_No running vertically, grade (a column labelled ‘A’, etc) running horizontally, and in each cell those names that match the corresponding S_No and grade (ideally listed within the same cell)?

    I’m just getting started with power query so these are probably quite simple questions 🙂

    Many thanks in advance!

  • Hello Ashish,
    First of all i would like to congratulate you on your service to the others users of Excel. It’s a great help to have this kind of tutorials on the net.

    I am a bit stuck on some analysis a need to make on some data. What i am trying to do is some sort of data matrix with text. I have arrived to your website, searching on how to build a pivot table with text on the data value, but the way you explain is no posible for me to work on the information i want. I need to creat a table that can allow me to see what the vendor A asks to the Vendor C in theis commercial plannings. Once each vendor can ask for several things there isn’t a static rule, as well as the vendors.

    Please contact me if you can help me on improving this knowledge.

    Greetings
    Nunio.

  • Is there a limit on how many rows a file should have for using PowerQuery? I am trying to create a table with text in cells for a file containing 108274 rows and 3 columns. What should I do?

      • I realized that there is no limit. I am trying to create a pivot table with actual values in cells for about 108274 candidates. Another problem I am facing is that some individuals in my data set have more than one score which is resulting in ‘error’ in the PowerQuery table. Is there anyway I can keep all their scores (which may result in repeating some of the candidate IDs) in my PowerQuety table? I hope I am making sense.

          • Here is what I’m trying to do. If you could help me, that would be wonderful.

            Below is a link to my doc in dropbox. The columns are Department, Status, and Project. I want to produce a table with the first column being the department, the other column headings being the values in the Status column (Analysis, Active, Soon, Planned), and the table values being the values in the Project column that match the department and status

            The main challenge that I’m encountering is that there is more than one project that matches each department and status.

            https://www.dropbox.com/s/i8x54xa5njgstol/Projects.xlsx?dl=0
            The first tab has the base data.
            The second tab “by Dept” has the desired result.

            Is this even possible?

          • Hi,

            Yes. It is possible. Infact in your “By dept” worksheet, you have missed the row for the Marketing Department. You may download my solution from this workbook. This is my Power Query solution:

            1. In the Data worksheet, type Dummy in cell D1
            2. In cell D2, enter this formula and copy down

            =COUNTIFS(A$2:A2,A2,B$2:B2,B2)

            3. Select A1:D51 and press Ctrl+T
            4. Click on any cell in the data range and go to Power Query > From Table
            5. In the Power Query window, right click on the Dummy heading and change the Type to Text
            6. Go to Add column > Add Custom Column and create this formula =[Department]&”^”&[Dummy]. Leave the Custom default Name
            7. Remove columns other than Status, Project and Custom
            8. Click on any cell in the Status column and go to Transform > Pivot column. In the Value column, choose Project and under Advanced Options, choose Don’t Aggregate
            9. Right click on the Custom column and Split the column by Custom > ^
            10. Right click on the Custom.2 column and Remove it
            11. Rename the columns as desired
            12. Sort the first column in ascending order
            13. Reorder the columns (by dragging) to Analysis, Active, Soon, Planned
            14. Go to Home > Load and Close

            Hope this helps.

  • Hi Ashish
    Your solutions are just amazing, I am hoping you can help with mine. I have a workbook with 20 tabs that are bill of material lists for various manufactured goods. Column A is the part number, Column B is the description and column C is the qty required for the unit. I need to create a summary sheet that rolls up the totals of the materials on all of the tabs and shows the part number, the description and the total quantity. Some tabs may have the same material on them more than once too. It would be nice to be able to expand each item to show the quantities coming from each tab but not really necessary. Note that I need to do it with MS Query as I can’t install Power Query on my version.
    Thank you Marty

    • Hi,

      Thank you for your kind words. Once we get all the data at one location, then your question can easily get answered by creating a Pivot Table. I have shared the technique of creating a a Pivot Table from data split across multiple worksheet using MS Query at this link (refer to “Solution for all versions of MS Excel” section)

      Hope this helps.

  • Hi Ashish
    That video will work great to gather all the data to a pivot table, but the issue I have is that I need it to show the part number in the first column and the description in the second column which is technically in the data area then column 3 is simply the sum of the quantity for each item from the multiple tabs. It would be great to have a way to expand each item and see the quantities coming from each sheet to give you the total, but I can live without it.
    Thanks in advance, your Excel skills are just amazing!

    • You are welcome. The Part Number and the description can both be dragged to the Row labels section of the Pivot Table. To know the quantity coming from each worksheet, create another column in all worksheets with the worksheet name.

      Thank you for your kind words.

  • Dear Ashish,

    I have a data with three headers Item no. , Manufacturer & Ordering code
    Where item no. is common while Manufacturer & Ordering code are unique.
    I need to form a table with Item no. in rows, Manufacturer in columns & Ordering code below to each manufacturer.
    Can u pls suggest the same by using MS Query.

  • Hi…

    Sorry the data is confidential, so i can not upload it you can take Example as below

    Item Manufacturer ordering code
    1 IDT QS3244QG
    1 IODES INC. (PERICOM) PI5C3244QEX
    2 FAIRCHILD 74LCX74M
    2 ON SEMICONDUCTOR MC74LCX74DG
    2 IDT 49FCT3805AQGI
    3 FAIRCHILD 74LCX125M
    3 ON SEMICONDUCTOR 74LCX125DG
    4 MAXIM MAX3224EEAP+
    5 MAXIM MAX3491ESD+

    And i am looking for resultant some how

    Items Manufacturer 1 Manufacturer 2 Manufacturer 3
    1 Ordering code Ordering code
    2 Ordering code
    3 Ordering code Ordering code

    • Hi,

      This is very easy to accomplish (practically a single click solution) with the help of Power Query. Power Query is a free add-in for Excel 2010 and higher versions. Do you have that add-in. If not, please download and install it. Once you have done so, let me know so that I can share the steps with you.

  • I am stuck on some analysis and found your blog. What i am trying to do is some sort of data matrix with text. I am searching on how to build a pivot table with text on the data value, but the way you have explain above is not possible for what I need. I need to create a table that would allow me to look at medical specialties by cities and list the organization(s) that are within either one of these when pulled into a pivot. I have all the data for each but each time I try to run a pivot I am stuck with counts and not the organization name instead. Happy to share with you the file to review on how best to create this.

    Please contact me if you can help me. Thanks!

  • Hi Ashish,

    At step 12 I get a message saying the query can’t be shown graphically and then it returns a replica of the table I’ve used. Can you advise?

    Many thanks
    Nate

    • Hi,

      I’ll need to see the workbook. But before you share the workbook, I’d suggest you try out the much better Power Query a.k.a. Get and Transform method. I have shown a video of the same in the original Blog article.

  • I went through this whole process and essentially what I figured out is this process just generates a new table under SQL rules. It is not a pivot table. It doesn’t have the analyze functions or properties of a pivot table.

  • Hi Ashish,

    can u help me out of this situation.
    suppose I’ve a data of employees attendance for 30 days of a month.there are four status in this data i.e. Present, half, absent and missing.
    Now I just want pivot table to do that for me is calculate the data like employee name in row, date in columns and the status of attendance in data field juss like that it is as, present, half, missing, and absent (in alphabetic form).

    plz help me, m sure u can help me with this.