Automatically making individual tables in Excel

Question

Automatically making individual tables in Excel

Sam 40

Hi there,

Below (Image 1) is a draft excel spreadsheet with random figures that I generate from the payroll system at work. Is there a way to automatically create a table for each employee that is formatting like 'Image 2'?

Image 1:

User's image

Image 2:

User's image

Thanks for your help :)

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Dear @Sam,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your description, you wonder there is a way to automatically transform each employee’s row into a formatted vertical table like Image 2 in Excel.

Yes, this can be achieved in Excel, and the most effective method is to use Power Query with the Unpivot feature.

Here is following steps:

Select your data range and go to Data > From Table/Range.

User's image

In Power Query:

Select the columns salary, membership, superannuation, bonus.
Go to Transform > Unpivot Columns.

User's image

The result will display data in this structure:

Name | Attribute | Value

Home tab > Click Close & Load to return the result to Excel.

User's image

Each employee’s data will be converted into rows that can easily be filtered or formatted into a table like your Image 2.

I hope my information meet your expectations, if not, please feel free to reach out so I can support you more effectively.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.