Excel pivot table: Show values instead of summaries

Solution 1:

Unfortunately, pivot tables can't return text. They aggregate.

However, there are workarounds. The simplest involves a "Helper" column and VLOOKUP. See my screenshot below.

First, insert a "Helper" column just to the left of your "License" column that concatenates the ID, Name, Brand and Model.

Next, construct the same pivot table as before, but select the "classic view" so that your layout is identical to your 2nd screenshot. Select the entire pivot table, then "copy, paste special, values". This will preserve the pivot table layout but remove the pivot table functionality.

Finally, use a VLOOKUP as indicated. Pay close attention to the $ sign placements in the formula so that the absolute references are preserved. The screenshot shows the formula in cell S13; copy it over and down as needed.

(In the pivot table layout, you'll also need to fill in the blanks in the "Brand" row. In the screen shot below, there's a formula in cell U11: = T11).

Admittedly, if your source data expands, you'll need to extend this technique.

There are other methods involving tables, INDEX+MATCH and array formulas.

enter image description here