Stop Sending Ugly Pivot Tables to Your Boss

16:19

Stop Sending Ugly Pivot Tables to Your Boss

Leila Gharani 16.04.2026 484 410 просмотров 14 449 лайков

Machine-readable: Markdown · JSON API · Site index

Смотреть на YouTube

Поделиться Telegram VK Бот

Транскрипт Скачать .md

Анализ с AI

Описание видео

🔥 500,000+ enrolled in our courses. Join them 👉 https://link.xelplus.com/yt-d-all-courses Tired of copying and pasting Pivot Table data just to make it look presentable for management? In this tutorial, discover how to transform clunky, default Excel Pivot Tables into sleek, minimalist, and fully dynamic reports without ever leaving the Pivot Table environment. ⬇️ DOWNLOAD the workbook here: https://www.xelplus.com/format-pivot-table-excel/#download Learn the professional workflow for data visualization, including how to remove clutter, create custom styles that stick even after refreshing, and use advanced formatting tricks that most Excel users overlook. We dive deep into creating "Variance" indicators using custom number formatting and emojis to instantly highlight over-budget and under-budget items. What you will learn: - How to clean up the Pivot Table layout by removing +/- buttons and field headers. - The secret to adding "Variance" using Calculated Fields. - Step-by-step guide to building a Custom Pivot Table Style from scratch. - How to use Custom Number Formatting to add visual symbols (red/green indicators) to your data. - Pro tips for disabling "Auto-Fit" so your column widths stay perfect every time you refresh. Timestamps 00:00 The problem with standard Pivot Tables 00:50 Setting up the source data and initial Pivot Table 02:26 Adding a Calculated Field for Variance (Budget vs. Actual) 03:09 Improving the layout: Blank rows and compact forms 03:47 Removing clutter: Disabling buttons and field headers 04:06 Renaming Grand Totals and fixing header errors 05:37 Creating a Custom Pivot Table Style from scratch 07:12 Modifying borders and vertical separators for a clean look 10:28 Visualizing Variance with Emojis and Custom Number Formatting 13:16 Essential Settings: Disabling Auto-Fit and handling blanks/errors 14:15 Testing dynamicity: Adding new data to the report 15:03 How to copy your custom style to other workbooks 📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: https://link.xelplus.com/yt-d-newsletter 🔓 Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join 🎬 LINKS to related videos: You Won't Believe These Crazy PIVOT TABLE Hacks! https://youtu.be/ZZRl7fALdjw You Can Put Images Inside Pivot Tables Now. Here's How. https://youtu.be/WNrB1Q9Rry0 👕 Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ 🧭 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/ 🧰 Resources I recommend: https://www.xelplus.com/resources/ 🛒 More resources on my Amazon page: https://www.amazon.com/shop/leilagharani 📲 Let’s connect on social: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/company/xelplus This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! #Excel

Методичка по этому видео

Структурированный конспект

Создавайте профессиональные отчеты в Excel, которые впечатлят руководство и сэкономят часы на форматировании

Профессиональное оформление сводных таблиц для офисной аналитики. Мини-курс для тех, кто хочет превратить «уродливые» таблицы в чистые управленческие дашборды за 16 минут.

Оглавление (12 сегментов)

The problem with standard Pivot Tables

For years in my corporate life, my pivot tables looked like, — well, this. Not exactly something you want to put in front of management. So, whenever I needed to present this data, I would copy it out and format it on the grid separately. And honestly, a lot of people on the team did the same thing. I had no idea that a pivot table could actually look like this. There was no need for me to copy and paste and format the stuff separately. This is a fully functioning pivot table. The design is minimalistic, it's professional. We have visual indicators that instantly show us what's over budget, and the whole thing is completely dynamic. Had I known this, my life would have been a whole lot easier. So, if you use pivot tables at work, you're going to want to see this. So, here's our data, and we want to create a budget report

Setting up the source data and initial Pivot Table

for our fast food chain. So, we have expenses for actual and budget. We have a couple of departments, a couple of categories, and our months currently go from Jan to June. Now, this is way too much information and data to read through, and this is exactly what pivot tables are for. So, the current data is formatted as a table. We can see it over here in table design. I'm going to go ahead and insert a pivot table on a new worksheet. Let's bring over the pivot table fields. Now, I want to add department and category to the rows. Let's go ahead and add budget and actual as values. Actually, I want it the other way around, so I'm going to click and drag actual and bring it before budget. And I want to add month as a filter, so I'm going to click and drag it to the filters area. Okay, so here is what we get. With month being in the filter, we're able to look at 1 month at a time. So, this is the classic pivot table layout. Let's be honest. I mean, it's not that terrible, but it's not that great either. We're going to fix that. One improvement though Microsoft 365 that you might have noticed is that the source formatting pulls through. So, notice these numbers already have the thousand separator because that's how my data was formatted at source. Now, this formatting pulls through, which means I don't have to adjust these if I want to keep this formatting. I'm fine with this, so let's keep it. But, there is one information that's missing. It would

Adding a Calculated Field for Variance (Budget vs. Actual)

be good to see the variance so that we can see how much over or under budget we are. Let's add it to our pivot table. I'm going to go to pivot table analyze, fields, items, and sets, and add a calculated field. I'll call this variance, and I'll go with budget minus actual. And okay. So, anytime we see a minus, it means that we're over budget, and this are expenses, so it's not good here to be over budget. Wherever we have a plus, we're under budget. Now, this is a good addition because it makes the report easier to read, easier to digest. But, now it's time to work on the layout, and

Improving the layout: Blank rows and compact forms

let's start with the changes that make the biggest impact. I'm going to go to design, blank rows. Let's insert a blank line after each item. This gives your categories a bit more breathing space. Right, this is a lot easier to read like this. Now, you can also adjust the report layout and show it in tabular form, for example. This splits things a bit more. So, notice we have department and category in their own columns instead of nesting them. But, I think the nested view works pretty well in our report, so I'm going to go back to compact form.

Removing clutter: Disabling buttons and field headers

Now, let's just remove some of the clutter. Let's go to the analyze tab and turn off this plus and minus buttons. So, I don't need anyone to be able to expand or collapse these. I just want to take them off. And I'm going to remove the field headers here. Notice the

Renaming Grand Totals and fixing header errors

subtotals are on top. That's fine. I want to keep that. On the bottom, I have grand total, but I'm not so fond of grand total. I'm going to call it all departments. And yes, you can actually type whatever you want here. And when you refresh it, it's going to stay. Now, for the headers, no one wants to see sum of actual, sum of budget. So, let's replace this. The thing is that if I completely remove this and press enter, we're going to get an error message that the field name already exists. Excel doesn't like it if we are using an identical name here that we have in our field list. So, the trick is to add a space either before or after the names. I'm going to add one before. Let's press tab to go to the next one and adjust this, and tab again, and adjust this. Okay, so this is already a lot cleaner. We can switch and take a look at Jan values only, Feb. But, it still has that pivot table look. Let's get rid of that, and this is the part that makes the biggest difference. We're going to be creating our own custom pivot table style. Now, I'm going to walk you through each step, but if you don't want to do it all from scratch, you can download my file from the link in the description. The style is already built for you. You just need to copy it and paste it into your own

Creating a Custom Pivot Table Style from scratch

workbooks, and I'm going to show you how to do that at the end of this video. But, if you want to understand how this works, and trust me, it's worth knowing, let's build it. Let's go to the design tab, and here you can select between different pivot table styles. So, you could go with something more minimalistic like this one or this one. Actually, I'm going to pick this one as my basis, so this is white pivot style light one. Right, this already looks pretty neat and clean, but we're going to customize it further. And to do that, you just need to right mouse click and duplicate. Up here, you can give your style a name. I'm going to call it Excel Plus style. And here, you can see all the elements that are used in a pivot table. That's the key for keeping everything dynamic because you're not formatting cells, you're formatting pivot table elements. And the ones in bold here mean that they already have some special formatting in place. So, the main one is the whole table. This defines how everything is formatted, and then you can override an element by selecting it from here. For example, notice the header row. That one already has some formatting applied to it. I'm going to click on format. We can see in border, these are the borders currently applied. In font, this is the color that's currently used. You can change these to fit your needs. I'm going to keep the font color as is, but

Modifying borders and vertical separators for a clean look

for border, I actually want to go with this black color. And I only want a border on the bottom. Right, I only want the border here. I don't want this border on top. So, I'm actually going to get rid of everything, and then only click here to add a border on the bottom. Now, just to see how this looks, let's click on okay. And okay. Now, notice nothing happened in the pivot table. That's because the old style that I selected is still applied. To apply this new Excel Plus style, I'm going to go here, and you're going to see your custom style here. Notice when I hover over it, we see the name. Select that, and now we can see that style applied. I'm actually going to go ahead and remove the grid lines from the sheet just so that we can see our pivot table pop more. Let's remove that. I do want to make some other changes because notice here we have this empty row, but then we have the border on the bottom and on top here. I find this to be a bit too much of a clutter. I want to remove that. So, let's go back to design. Just make sure you select your custom style here, right click, and go and modify this. Now, we're going to look for the blank row element. That must be somewhere right here. Currently, it doesn't have anything applied to it, so whatever formatting it has, it's coming from the whole table formatting. We're going to go and format it. Here under borders, I'm just going to get rid of everything. I don't want that. Let's click on okay. And actually, another thing I want to do before we commit this is I want to adjust the whole table formatting. Let's go over to font. Let's not go with this super dark color, but this one. And for border, I am going to get rid of everything first. I don't want those side borders. I do want the middle border here. And this color right now, that's pretty good. It's not too strong, so let's add that in. But, for the vertical line, I actually want a thick white border. So, the thick one is here. I'm going to switch to white. Now, I can't see where it is. It's a good thing I saw it before. It's right here. Now, I'm going to add that as my vertical border. And this adds a very subtle white in between, and it breaks things up a little bit and makes things look really neat. Now, we remove that one here, and you can notice here we have that subtle white break in between the borders. So, this already looks a lot nicer. Let me just insert a row here, on top so we can the side borders are also gone. Now comes my favorite part. But by the way, we cover lots of cool tricks that many people who use pivot tables every day don't know about in my pivot table mastery course. There we will take you from beginner to saving a ton of time with pivot tables. You're also going to learn how to create pivot tables from multiple data sets and other real-world scenarios that you're likely going to come across at work. If you're interested, check it out. Link is in the description. Okay, so here's the really

Visualizing Variance with Emojis and Custom Number Formatting

cool way of bringing attention to what matters most in your reports and this is one of my favorite tricks and it doesn't use conditional formatting. We're going to visualize the variance data so it's easier to spot how things are going. I'm going to bring variance a second time to the reports. I'm going to click and drag it all the way down to the end. Now for this version, we're going to right-click, go to number format, go to custom. Now if you aren't familiar with custom number formatting, the trick is that you can decide how positive numbers should be formatted, how negative and then how zeros and text can be formatted and you separate these with a semicolon. Now let's say for positive numbers, I want to get a green symbol. So I'm going to bring up the Windows emojis, but I'm actually going to go all the way here to symbols and I want to go with this box here. So I'm going to add that in. Now I want this to be green. So in square brackets, I'll add green. Okay, [clears throat] so this is how positive numbers are going to be formatted. Now we can decide how negative numbers can be formatted after that semicolon. Now I'm just going to copy this and paste it in here, but instead of green, I want red. And last is how zeros can be formatted. I'm just going to leave it blank, so zeros are hidden. Now by the way, if you're using Excel in another language, you want to replace green and red with whatever that is in your language. So I'm going to okay this and instantly we get really nice, neat-looking icons added to our pivot table report. Now I definitely don't want to show this header, so I'm going to remove it and add a space because we do need something over here. And instantly we can see where we have overspent and where we have underspent. One thing though I prefer to improve here is that I'm not so fond of this green color here. It's just far too bright for me. So let's right-click, go to number format. You can go with a different color and my favorite is color 43. And for red, I actually prefer another red, a darker one, and that's color 53. Now let's just add this and that's the result that we get. Now if you're wondering what color codes are available, I've also included that here in this workbook. Now we're currently looking at February numbers. Let's switch our view to Jan and everything updates automatically.

Essential Settings: Disabling Auto-Fit and handling blanks/errors

But there are just a few final things that we want to do to make this report ready. Let's right-click, go to pivot table options and uncheck auto fit column widths on update. This makes sure that your column widths stay the same every time you refresh the pivot table. Also make sure you have a check to preserve cell formatting on update. Now if your report has empty cells and you don't want to show blanks, but instead you want to show zeros, you can adjust that over here. And if you end up with error values because let's say you're calculating percentage change and sometimes you're dividing by zero, you can just show blank for error values. Okay, so there are some settings here that you can update. So let's go with that and then adjust the column widths to make sure all the numbers fit properly. Okay, so now let's check May and this is beautiful.

Testing dynamicity: Adding new data to the report

beautiful. So we ended up going from this to this. Same data, same pivot table underneath, but completely different impression. And let's actually test it. What happens if we add a department that didn't exist before? Let's go over to budget data. I'm just going to scroll down here. I have some extra values. I'm going to cut this and let's go and add it below the table. Okay, so we can see our table expanded. Let's go over to our beautiful report here. I added a new category for research and development. I'm going to right-click and refresh and it pops up here and my calculation and icon are added as well. Now one last thing, this

How to copy your custom style to other workbooks

specific custom style that we just created here in design lives inside your workbook. So if you want to use it in another file, here's the trick. Copy the sheet to your other file. So I'm going to right mouse click, move or copy, create a copy. Now select your other file or in this case I'm just going to copy to a new book and okay. Now in this other file, because we've copied over the pivot table that has that design, you're going to find the custom design available. Now you're going to go ahead and delete that pivot table, right? You don't want to move that pivot table or copy it to your other sheet. You're going to delete it. When you delete it, the custom pivot table style is going to stay. So when you create a new pivot table in this file, you're going to see that custom style. Okay, so remember you can also just download my practice file from the description and then copy it from there. Now if you found this video helpful, you know what to do. I'm going to see you in the next video.

Другие видео автора — Leila Gharani

Ctrl+V

Экстракт Знаний в Telegram

Экстракты и дистилляты из лучших YouTube-каналов — сразу после публикации.

Подписаться

Лучшие методички за неделю — каждый понедельник