Skip to main content
AmigoFish

Stop Sending Ugly Pivot Tables to Your Boss

From Leila Gharani · · 16 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.

If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.

Watch on YouTube

Not rated yet

Description

Format Excel Pivot Tables so they look like a finished report. No copying data out, no formatting cells by hand. Everything stays dynamic when you refresh. ⬇️ Download the practice file (includes the custom style and color codes): https://www.xelplus.com/format-pivot-table-excel/#download

📘 Pivot Tables course mentioned in the video: https://www.xelplus.com/course/excel-pivot-tables/

You'll build a budget vs actual report for a fast food chain. Start with a standard Pivot Table and add a Variance calculated field (Budget minus Actual). Then clean up the layout: blank rows, compact form, no +/- buttons and no field headers. Rename Grand Total to anything you like, and replace the "Sum of" headers with a simple space trick.

Next, create a custom Pivot Table style. You format Pivot Table elements instead of cells, so the design holds when the data changes. Then add green and red variance icons with custom number formatting. No conditional formatting needed.

Finish with the Pivot Table options that keep column widths and formatting in place on refresh. You'll also see how to copy your custom style into any other workbook.

Source number formatting carrying over into the Pivot Table is a Microsoft 365 feature. If your Excel is in another language, replace Green and Red in the number format with the color names in your language.

★ 500,000+ professionals learn Excel with our courses: https://link.xelplus.com/yt-d-all-courses

✉️ Get one practical Excel tip every week in our free newsletter, Between the Sheets: https://link.xelplus.com/yt-d-newsletter

00:00 Ugly vs report-ready Pivot Tables 00:48 Build the budget vs actual Pivot Table 02:02 Microsoft 365: source number formatting carries over 02:24 Add a Variance calculated field 03:06 Blank rows and report layout 03:49 Remove +/- buttons and field headers 04:09 Rename Grand Total and Sum of headers 05:18 Create a custom Pivot Table style 07:35 Apply the custom style 08:50 Cleaner borders with a white vertical separator 10:28 Variance icons with custom number formatting 12:30 Softer icon colors: Color43 and Color53 13:15 Pivot Table options: keep column widths on refresh 14:09 Before vs after, then add new data 15:03 Copy your custom style to another workbook

🎬 You Won't Believe These Crazy PIVOT TABLE Hacks: https://youtu.be/ZZRl7fALdjw 🎬 You Can Put Images Inside Pivot Tables Now: https://youtu.be/WNrB1Q9Rry0

🔓 Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

🚩 Connect with us: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/in/leilagharani

#excel #pivottable #exceltips

Back to Leila Gharani · Report a problem