I have 10x different large Excel tables, tracking technician competencies in this format:

I know that if I select the region, it won't be useful for Pivot data. Any suggestions on how to keep track of this data differently, or reorganize it so that it is Pivot-table ready?

enter image description here

1 Answer 1


You should reorganize your data with these columns:

  • Technician (Technician 1, Technician 2, ... Technician n)
  • Capability (General Basics, Full Service, etc)
  • Product (Product 1, Product 2, ... Product n)

You will have as many rows in this table as there are green cells in your image. There should be no blanks in any of the columns.

To reorganize this quickly, you should:

  1. Fill in the blanks in the Technician column
  2. Put headers on the Technician and Capability columns
  3. Use Data>Get & Transform Data>From Table/Range. When the Power Query Editor opens, select the Technician and Capability columns, then use Transform>Unpivot Other Columns. This will take each of the Product columns and transpose it to a row against each grouping of Technician and Capability.
  4. Apply a filter to remove any rows you don't want (i.e. those rows with a blank in the Value column)
  5. Remove the value column
  6. Rename the Attribute column to Product
  7. Use Home>Close & Load to put the query back into the workbook

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.