Excel - Pivot tables with text values or numbers without calculations
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.
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
- Import the desired data to a blank Excel sheet.
Source data - 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 tool data selection. - Confirm the selection and if it has headers.
- Power Query application will open. Convert the fields to the appropriate type, in this case, time.
Adjusting the data types - Select the column that has the names of the new columns. In this case "Action".
- Click on the Transform Menu - Pivot Column.
Pivot Column creation - 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.
- 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 - Check if the results are OK and then click on the Home Menu - Close & Load.
Final results, if everything is OK, click on "Close & Load" - 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
Another example
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.
