Blog

Make Your Excel Pivot Tables Look Like Simple Tables (Tabular Form)

Illustration of a pivot table becoming a flat, easy-to-read table.

Pivot tables in Excel are super helpful for adding up and understanding your data. But sometimes, they can look a bit messy, especially when you want to copy the data or make reports. What if you want your pivot table to look like a simple, normal table? That’s where the “Tabular Form” comes in! It makes your data clear and easy to use. In this guide, we will learn how to change your pivot tables to this simple tabular form. You will see how much easier it makes your work!

Method 1: Using “Report Layout” to Get Tabular Form

This method is the quickest way to change your existing pivot table into a simple, flat table. It helps you see your data clearly, with each data type in its own column.

  1. Click Inside Your Pivot Table: First, just click on any cell inside your pivot table. This helps Excel know which pivot table you want to change.
  2. Go to the “Design” Tab: Look at the top of Excel. You will see new tabs appear called “PivotTable Tools”. Click on the “Design” tab there.
  3. Find “Report Layout”: In the “Design” tab, look for a group called “Layout”. Inside this group, you will find a button named “Report Layout”. Click on it.
  4. Choose “Show in Tabular Form”: A small list will appear. From this list, click on “Show in Tabular Form”.

Screenshot showing how to select

Why this works: When you do this, your pivot table instantly changes. Each item that was grouped together now gets its own column, just like a regular table. This makes it super easy to read and work with your data.

Method 2: Making Every Row Complete with “Repeat All Item Labels”

Sometimes, after changing to tabular form, you might see some empty cells. This happens because Excel only shows the item label once. To make your table even clearer and ready for copying, you can fill these empty cells.

  1. Make sure it’s Tabular: First, ensure your pivot table is already in the tabular form (you just learned how to do this in Method 1).
  2. Click Anywhere in the Pivot Table: Again, click on any cell within your pivot table.
  3. Go to the “Design” Tab: Click on the “Design” tab under “PivotTable Tools” at the top.
  4. Find “Report Layout” Again: In the “Layout” group, click on the “Report Layout” button.
  5. Choose “Repeat All Item Labels”: From the list that appears, click on “Repeat All Item Labels”.

Comparison of a pivot table in tabular form with and without repeating item labels.

Why this works: This simple step fills in all the blank cells in your pivot table’s first few columns. This makes every single row complete and standalone. Now, if you copy this data or filter it, it will be much easier to use, as every row has all its details.

Method 3: Removing Extra Totals with “Do Not Show Subtotals”

When you want a truly simple and flat table, you might not want those extra “Subtotal” rows. They can make your table look busy. Hiding them makes your tabular pivot table even cleaner.

  1. Click Inside Your Pivot Table: Just like before, click any cell in your pivot table.
  2. Go to the “Design” Tab: Click on the “Design” tab under “PivotTable Tools” at the top.
  3. Find “Subtotals”: In the “Layout” group, you will see a button named “Subtotals”. Click on it.
  4. Choose “Do Not Show Subtotals”: From the list, select “Do Not Show Subtotals”.

Screenshot showing how to hide subtotals in a pivot table using the Design tab.

Why this works: Removing subtotals gives you a very clean, flat table. This is perfect when you just want the raw data in a table format, without any extra summary rows breaking up the flow.

Important Things to Remember (Common Mistakes)

When you are making your pivot tables tabular, keep these simple points in mind:

  • Don’t Forget to Fill Blank Cells: After you change to tabular form, you might see empty cells under the main headings. Many people forget to use “Repeat All Item Labels” (Method 2). This step is important if you want to copy your data or use filters easily, so all rows are complete.
  • Subtotals Can Make it Messy: If you want a really clean and flat table, those “Subtotal” rows (Method 3) can make it look crowded. Think about hiding them if you want a simple table without extra summary rows.
  • Tabular is Different from Outline: The tabular form makes your data flat. If you need to see your data grouped and summarized quickly, you might need to switch back to “Compact Form” or “Outline Form” to get that view. Tabular is for a detailed, flat list.

Conclusion

There you have it! Now you know how to make your Excel Pivot Tables look just like simple tables using the tabular form. This makes your data much easier to read, copy, and use for other tasks. With these simple steps, you can take full control of how your pivot tables look and work. Keep practicing, and you’ll become an Excel expert in no time!