5

I'm creating a pivot table of a list of items that need to be ordered. Each Item has a model number and a description that remain consistent. The data is copied from another sheet and is formatted like this:

|Model Number     |Description      |
=====================================
|                 |                 |
|                 |                 |
|                 |                 |
|1234-5678-90     |Model A          |
|                 |                 |
|1234-5555-90     |Model B          |
|1234-5555-90     |Model B          |
|1234-5678-90     |Model A          |
|1313-5678-90     |Model D          |
|                 |                 |
|                 |                 |
|1234-5678-90     |Model A          |

The final result that I'm trying to achieve is a pivot table resembling this

|Model Number     |Description      |Quantity         |
=======================================================
|1234-5678-90     |Model A          |3                |
|1234-5555-90     |Model B          |2                |
|1313-5678-90     |Model D          |1                |

But I can't figure out a way to do this without creating a multilevel table.

2 Answers 2

5

Assuming the attached image is suitable for your format, do the following:

  1. Convert your data into 2 columns (with column names at top).
  2. Create a pivot table containing your 2 columns of data.
  3. Drag the data field names into the row label section.
  4. Right-click on the pivot table and select Pivot Table Options, select the Display tab, select the checkbox Classic Pivot Table layout, click on OK
  5. Right-click on the first column and deselect Subtotal Model Number (or whatever the name of your first column is

I deselected the blank value in the first column list to clean up the table

pivot table image

1
  • On my sheet, I hid SubTotals with PivotTable Tools > Design > Subtotals > Do Not Show Subtotals (source)
    – Stevoisiak
    Sep 5, 2018 at 19:45
2

No doubt the Classic Pivot table layout solution does works,I too used it up until I found this solution that preserves the new pivot table styles, which is a very important aspect for data visualization.

  1. Create pivot table with Model Number followed by Description in the Row Area, The PT would like

enter image description here

  1. In the design tab, change Report Layout to Show in Tabular Form as below

enter image description here

  1. In the design tab again ,change subtotals to do not show subtotals enter image description here

**4. the final output as desired would be **

enter image description here

The major advantage of this over classic PT is that it preserves the design aesthetics of Excel 2016, more over you can add more columns for unique values like Item Unit Cost, Item Unit Price and whatnot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.