To format a pivot table in Excel, click inside the pivot, go to the Design tab, select Report Layout and choose Show in Tabular Form. Set number formats through Value Field Settings, not the Home ribbon, so they survive every refresh. Apply a PivotTable Style for instant professional polish.
- Three layouts exist: Compact, Outline and Tabular. Tabular is the most spreadsheet-friendly for reporting and export.
- Number formats must be set through Field Settings, not the Home ribbon, or they will reset on every refresh.
- PivotTable Styles let you apply a consistent look in two clicks, including alternating row colours.
- Repeat Item Labels fills blank cells in Outline and Tabular layouts, which is essential before pasting data elsewhere.
- If the field list disappears, right-click the pivot table and choose Show Field List to bring it back instantly.
Report Layouts Explained
When you first create a pivot table, Excel defaults to Compact layout. All row labels stack into a single column, which saves space but makes the data almost impossible to use outside the pivot itself. Understanding all three layouts is the foundation of knowing how to format pivot table in Excel properly.
The Three Layout Options
| Layout | How Row Fields Appear | Best Used When |
|---|---|---|
| Compact | All fields share one column | Quick internal review, small screens |
| Outline | Each field gets its own column, subtotals above groups | Hierarchical data with subtotal visibility |
| Tabular | Each field gets its own column, subtotals at the bottom of groups | Exporting, sharing, further analysis |
The practical difference matters a lot on the job. If you are presenting sales data from a retail chain across Maharashtra and Karnataka, Compact layout will merge state names and city names into the same column. That makes a VLOOKUP or a Power Query merge almost impossible. Tabular splits them cleanly into separate columns, which is exactly what downstream tools expect.
Subtotals and Grand Totals
Each layout has its own default subtotal position, and you can override it. Go to Design > Subtotals and choose whether to show them at the top or bottom of each group, or turn them off entirely. Grand totals can be toggled for rows, columns or both under Design > Grand Totals. Turning off grand totals is common when the pivot feeds into a dashboard where totals are calculated separately.
A real mistake beginners make: they try to delete a subtotal row by pressing the Delete key. Excel either ignores it or throws an error. You have to use the Subtotals menu, not keyboard editing.
How to Format Pivot Table in Excel: Switching to Tabular Form
Here are the exact steps to switch any pivot table to tabular form. This works in Excel 2016, 2019, 2021 and Microsoft 365.
Step-by-Step: Switch to Tabular Layout
- Click any cell inside your pivot table to activate the contextual tabs.
- Click the Design tab in the ribbon (it appears under “PivotTable Tools” or “PivotTable Design” depending on your Excel version).
- Click Report Layout in the Layout group.
- Select Show in Tabular Form.
- Go back to Report Layout and click Repeat All Item Labels to fill in the blank cells that appear between groups.
That last step, repeating item labels, is something most tutorials skip. Without it, every group break leaves blank cells in your row columns. If you copy that data into another sheet or send it to a colleague, they will see gaps where values should be. Always enable it when using Tabular or Outline layout.
Why Tabular Form Matters for Data Work
According to LinkedIn’s Jobs on the Rise 2024 India report, data analyst roles grew by over 33% year-on-year, with Excel and data visualisation listed among the most requested skills. When analysts export pivot data to tools like Power BI or Tableau, tabular layout is the only format that maps cleanly to a flat data table. Compact layout creates merged-cell logic that breaks most imports.
According to the NASSCOM Future of Work Report 2023, data presentation skills, including structured formatting and visual clarity, are consistently cited by Indian hiring managers as a gap in entry-level analyst candidates. A separate Statista survey on global Microsoft 365 usage (2024) found that Excel remains the most widely used data tool in enterprise environments, with over 1.1 billion users worldwide, underscoring why formatting pivot tables correctly is a baseline professional skill.
If you are preparing for a data analyst interview, expect a practical Excel round. Interviewers frequently ask candidates to reformat a pivot table and explain why they chose a particular layout. Knowing the difference between the three options, and being able to explain it, signals real working knowledge.
How to Format Pivot Table Numbers, Styles and Columns in Excel
This is where most of the visual work happens. Excel pivot table format options let you control colour, banding, fonts and number display. Done right, a formatted pivot table looks like a polished report.
Applying PivotTable Styles
Click inside your pivot table, go to the Design tab and look at the PivotTable Styles gallery. You will see Light, Medium and Dark categories. Hover over any style to preview it live. For professional reports, Medium styles with banded rows are the most readable.
Tick Banded Rows or Banded Columns in the PivotTable Style Options group to add alternating shading. This makes it much easier to read across wide tables, especially when you have many columns of values.
Setting Number Formats That Stick
Here is the single most common formatting mistake when learning how to format pivot table in Excel: selecting value cells and applying a number format from the Home ribbon. It works, but it resets the moment you refresh the pivot. The correct method is through Value Field Settings.
- Right-click any value cell in the pivot (for example, a Sales Amount cell).
- Select Value Field Settings.
- Click Number Format at the bottom of the dialog.
- Choose your format (Currency, Number, Percentage, etc.) and click OK twice.
This stores the format inside the field definition, so it survives every refresh. Use Indian number formatting (the rupee symbol with lakh and crore separators) by selecting Currency and customising the format code to ₹#,##,##0.00 if your regional settings do not apply it automatically. This is particularly useful for analysts working with BFSI, e-commerce or retail datasets in India, where lakh and crore notation is standard in client-facing reports.
Editing Pivot Table Columns
To rename a column header in a pivot table, click the cell containing the header and type a new name directly. Excel will not let you use the exact same name as the source field, so if your source column is “Sales”, name the pivot column “Total Sales” instead. This is one of the most useful Excel pivot table edit techniques for cleaning up reports before sharing them.
To change column width, drag the column border as you normally would. Excel resets column widths on refresh by default. To stop this, right-click the pivot table, choose PivotTable Options, go to the Layout & Format tab, and untick Autofit column widths on update.
Conditional Formatting on Pivot Tables
Conditional formatting works on pivot tables, but you need to apply it correctly. Select your value cells, apply the formatting rule, then in the “Applies to” section of the rule, choose All cells showing [field name] values. This ensures the rule follows the data even when the pivot is refreshed or filtered, rather than being pinned to a static cell range.
If you want to build on these skills with structured learning, check out practical data analytics projects that take you from raw data to polished output.
Display Options and Field Settings
Beyond layout and style, several display settings control what the pivot shows and how users interact with it. These settings live in two places: PivotTable Options (right-click the pivot) and Field Settings (right-click a specific field).
Showing and Hiding the Field List
The field list panel on the right side of the screen is where you drag fields into Rows, Columns, Values and Filters. If it disappears, click anywhere inside the pivot table, then go to PivotTable Analyze > Show > Field List. Alternatively, right-click the pivot and select Show Field List. It hides automatically when you click outside the pivot.
Field Settings for Row and Column Fields
Right-clicking a row or column field and choosing Field Settings gives you control over subtotals (automatic, custom or none) and layout for that specific field. You can also set a custom name for the field here, which is cleaner than renaming the header cell directly.
For value fields, Value Field Settings lets you switch the summary function (Sum, Count, Average, Max, Min), rename the column and set the number format, all in one dialog. If your pivot is counting instead of summing, it is usually because the source column contained text or blank cells. Changing the function to Sum in Value Field Settings fixes it.
Hiding Drill-Down and Expand/Collapse Buttons
The plus and minus expand buttons on the left of row fields look messy in a final report. To remove them, go to PivotTable Analyze > Show and untick +/- Buttons. You can also hide field headers (the grey “Row Labels” and “Column Labels” text) from the same Show group. Both changes make the pivot look significantly cleaner when printed or screenshotted for a presentation.
Blank Cells and Error Values
Empty cells in a pivot table default to showing nothing, which can look like missing data. In PivotTable Options > Layout & Format, you can set a custom value to display for empty cells (type a zero or a dash). You can also set what to show for error values, replacing #DIV/0! errors with a cleaner placeholder like “N/A”.
Excel and data skills like these are increasingly relevant beyond pure analytics. The comparison between a data analyst vs data scientist is worth reading before you decide which direction to specialise in. If you are just starting out and wondering how to build a career in data in India, the guide on data analyst kaise bane covers the full roadmap from beginner to job-ready.
For teams that outgrow Excel entirely, tools like Power BI and Tableau offer more advanced visualisation. The breakdown of Power BI vs Tableau will help you figure out which one fits your workflow. Excel pivot tables are a strong foundation, but knowing when to move to a dedicated BI tool is part of growing as an analyst.
Data and analytics skills are also among the highest paying tech skills in 2026, according to multiple industry salary surveys. Building Excel proficiency alongside Python, SQL or a BI tool significantly increases your market value as an analyst in India and globally.
Frequently Asked Questions
How do I show a pivot table in tabular form?
Click inside the pivot table, go to the Design tab, click Report Layout, then select Show in Tabular Form. Each row field will appear in its own separate column. Follow up by clicking Report Layout > Repeat All Item Labels to fill in the blank cells between groups, which makes the data much easier to copy or use in another tool.
How do I make a pivot table look good?
Apply a PivotTable Style from the Design tab and enable Banded Rows for readability. Switch to Tabular layout, remove the +/- expand buttons, hide the “Row Labels” and “Column Labels” headers, and set number formats through Value Field Settings so they do not reset. These five changes take under two minutes and produce a report that looks genuinely professional.
How do I edit pivot table columns in Excel?
To rename a column, click the header cell and type a new name (it must differ slightly from the source field name). To prevent columns from resizing on refresh, go to PivotTable Options > Layout & Format and untick “Autofit column widths on update”. To change what a value column calculates, right-click it and choose Value Field Settings to switch between Sum, Count, Average and other functions.
Why does pivot table formatting disappear on refresh?
Number formats set via the Home ribbon are applied to cells, not to the field itself, so they are overwritten when the pivot refreshes and rebuilds its layout. Always set number formats through Value Field Settings > Number Format to store the format inside the field definition. For column widths, untick “Autofit column widths on update” in PivotTable Options to stop them resetting.
How do I show the pivot table field list?
Click any cell inside the pivot table, then go to PivotTable Analyze > Show > Field List. You can also right-click inside the pivot and select Show Field List from the context menu. The field list hides automatically whenever you click outside the pivot table, so its disappearance is normal behaviour, not a bug.
How do I format pivot table numbers in Excel?
Right-click any value cell, choose Value Field Settings, then click Number Format. Select Currency, Number, Percentage or a custom format code and click OK twice. This method stores the format inside the pivot field so it survives every data refresh, unlike formatting applied from the Home ribbon.
Knowing how to format pivot table in Excel correctly, from layout to number formats to display options, turns a default pivot into a report you would actually send to a manager or a client. The three things that make the biggest difference are switching to Tabular layout, setting number formats through Field Settings, and cleaning up the visual noise with Styles and hidden buttons.
- Open an existing Excel file this week, build or open a pivot table, and practice switching between all three report layouts. Notice how the data structure changes each time.
- Set a currency or percentage format through Value Field Settings, refresh the pivot, and confirm the format survives. Compare that with setting it from the Home ribbon.
- Apply a Medium PivotTable Style with Banded Rows, hide the +/- buttons and field headers, and screenshot the result. That is a report-ready pivot table.
If you want to take these analytical skills further with structured certification, explore 3.0 University’s full catalogue of online certification courses in Data Analytics, Cybersecurity, AI and Blockchain. For analysts interested in applying data skills in a security operations context, the EC-Council Certified SOC Analyst course builds directly on the structured data thinking you develop in Excel.
Last updated: October 2026. Reviewed by the 3University editorial team.