Brookepedia

Excel - Pivot tables with text values or numbers without calculations

Excel - Pivot tables with text values or numbers without calculations

By Daniel
- Updated 7 years ago
Posted in:
13 comments

This article demonstrates how to use the Excel Power Query tool to create pivot tables that organize and display values without calculations (count, sum, average, etc..). This can be used if the table contains text data or if you need the real values displayed in the cells.

Pivot Table with Text Values in Excel

Objectives

Create a Pivot table that displays text data or number values that are not summarized (count, sum, etc...). This solution does not require macros or long search/replace functions, just the Microsoft Power Query tool that comes with Excel 2016 or can be installed for free as an add-on in previous versions.

This solution is far better than Formulas or VBA Macros because the categories/columns can be auto-updated according to the data source changes. It is also much simpler than typing codes and don't require any 3rd party add-on.

Requirements

  • Windows + Excel 2016 or
  • Windows + Excel 2010-2013 with the Microsoft Power Query tool installed
  • Mac users: Power Query is not available for this platform yet. Good luck with VBA macros :)

Step-by-step

  1. Import the desired data to a blank Excel sheet.
    Source data
    Source data

     

  2. Select the data, click on Data Menu - From Table/Range (in Excel 2016 or higher) or in the Power Query Menu - From Table/Range (Excel < 2016).
    Power Query data selection
    Power Query tool data selection.

     

  3. Confirm the selection and if it has headers.Data range confirmation

     

  4. Power Query application will open. Convert the fields to the appropriate type, in this case, time.
    Adjust the data values
    Adjusting the data types

     

  5. Select the column that has the names of the new columns. In this case "Action".
  6. Click on the Transform Menu - Pivot Column.
    Pivot Table creation
    Pivot Column creation
  7. Inside the Pivot Column dialog, select the column with the values that will populate the new columns to be created. In this case "Time" but could be any field type, including text.
  8. In the Advanced Options part, select "Don´t Aggregate" so the values will displayed without any modification.
    Select the source for the new column values
    Select the source for the new column values

     

  9. Check if the results are OK and then click on the Home Menu - Close & Load.
    If everything is OK, just close.
    Final results, if everything is OK, click on "Close & Load"

     

  10. The data is now on Excel in the form of Dynamic Data (a little more challenging to work with). If you just want plain data, you can copy and paste the values to another spreadsheet.
    Final data in Excel
    Final data in Excel

     

Another example

Source data with textual values
Source data with textual values

 

Power Query configuration
Power Query configuration for the column "Brand"

 

Final results: Pivot Table with Text data
Final results: Pivot Table with Text data

Tip

If the Power Query is taking too long to load/calculate you probably selected all the thousand columns and lines (up to the XFD104856 cell). You must select only the exact the data range you have.

 

Tags

Comments

Submitted by Mayank on Thu, 08/10/2023 Permalink

Suppose we brought years in columns and we have Unit price of a product in values (In this case we have multiple unit prices for the same product in each year) In this case it is giving us error wherever there are duplicates or multiple values for one product. How to tackle this?

Submitted by Ashley on Wed, 06/01/2022 Permalink

Thank you so much for this info - which works for Excel in 365 also.  Extremely helpful, especially as I'd never even heard of Excel Power Query tool!

Submitted by Jonathan Price on Tue, 11/24/2020 Permalink

I've just started using the Power Query tool but didn't know it could do this! 

 

Thanks

Submitted by Annie Cote on Fri, 10/09/2020 Permalink

This is great but I want to make multiple spreadsheets with only a few selected columns in each (like in a Pivot where you can choose the data presented). I need a master sheet, and separate tabs I can PDF for various users that shouldn't see the rest of the data.

Any way this can be done?

Submitted by Ari on Wed, 06/27/2018 Permalink

I would ask, if it's possible to have two different types of values in the value area, side by side, perhaps in two columns. The first one pick up time and the second one amount of people. Thank you in advance!

Submitted by Leah on Wed, 12/27/2017 Permalink

Alas, I have a new problem.  If I have multiple rows for "Daniel" with "created" (in other words, he created multiple times), this creates a problem.  I want the value -- not a count -- but Excel won't concatenate the values in the same cell -- it spits out an error that says:

Expression.Error: There were too many elements in the enumeration to complete the operation.

Details:

    List

Suggestions for this one?  Ideally, what I want is one row for Daniel with all times he created in the same cell (with some separator between them).

Submitted by Leah on Wed, 12/27/2017 Permalink

The pivoted column worked beautifully (THANKS!), but in your example, all three rows for Daniel wound up on the same single row, with the values for time in the pivoted columns.  In mine, everything wound up in the right pivoted columns, but I still have 3 rows.  The values I used for the pivoted column were text (not time) -- does this make a difference?  How can I consolidate so that I only have one row for each "person" (using your example)?

Aha; I figured it out.  I had concatenated two other columns and not removed the originals until after the pivot, thus there was data that didn't fully match.  Once I moved up the removal of those columns, it worked like a charm!  Wonderful!

Copyright © 2024 - Daniel BP
Creative Commons License

exothermic