Excel - Master Pivot Table Conditional Formatting with This Excel Tutorial! - Episode 875
Microsoft Excel Tutorial: Master Pivot Table Conditional Formatting with This Excel Tutorial!
Welcome back to another episode of the MrExcel netcast! In today's video, we're going to be discussing a great tip sent in by Nevel from Germany. In a previous episode, I talked about the frustration of formatting issues in Pivot Tables when making changes. Well, Nevel has come up with a simple solution using basic Excel techniques. Let's dive in and see how it works.
First, let's create a Pivot Table with Region and Product on the left and Revenue and Profit in the main body. In older versions of Excel, this would be built going down the report, but we're going to change it to go across. Now, here's where Nevel's suggestion comes in. We're going to select a larger area than the Pivot Table could ever possibly encompass. Then, we'll go to Format, Conditional Formatting, and change it from "Cell Value Is" to "Formula Is".
Next, we need to write a formula that will work as if Cell A1 is the cell that the formula refers to. Nevel's formula starts with =IF and uses the OR function to check for certain conditions. For example, (OR(RIGHT($A1,5)="total", will check if the last 5 characters in cell A1 are "total". We can add more conditions for other columns, such as RIGHT($B1,5)="total", and so on. This formula will work for both the English and German versions of Excel.
Once we have our formula set up, we can apply some formatting. Nevel suggests using bold font and a background color, such as blue. Now, when we add new fields to the Pivot Table, the formatting will still apply to the total rows. This is a great trick to keep your Pivot Table looking clean and organized.
I want to thank Nevel for sending in this tip and for being a part of our MrExcel community. As a token of our appreciation, we'll be sending Nevel one of our Excel Master Pins. And to all of our viewers, thank you for stopping by. Be sure to subscribe to our channel for more helpful Excel tips and tricks. We'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Neville from Germany sends in a good suggestion for overcoming pivot formatting problems from Episode 865. By using conditional formatting on the underlying worksheet, Neville comes up with a way to make sure that the total rows are always highlighted in a pivot table. Episode 875 shows you how.
Table of Contents:
(00:00) Introduction
(00:15) Nevel's suggestion
(00:31) Nevel's solution using Excel blocking and tackling
(00:46) Changing the layout of the Pivot Table
(00:56) Selecting a larger area for Conditional Formatting
(01:06) Changing the formatting to "Formula Is"
(01:16) Writing the formula for the Conditional Formatting
(01:38) Handling additional conditions
(02:00) Applying formatting to the totals
(02:35) Adding new items to the Pivot Table
(03:07) Highlighting total rows
(03:17) Adding new fields to the Pivot Table
(03:31) Conclusion and thank you
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorials
This video answers these common search terms:
Adding new fields to a Pivot Table
Applying Conditional Formatting to Pivot Table rows with totals
Changing Pivot Table layout in Excel 2003
Excel Conditional Formatting for Pivot Tables
Excel tips and tricks from MrExcel
Formatting Pivot Table cells based on conditions
Formatting total rows in different languages
Handling multiple conditions in Conditional Formatting
Highlighting total rows in a Pivot Table
Pivot Table formatting troubleshooting
Using bold font and background color in Conditional Formatting
Using formulas in Conditional Formatting
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - Master Pivot Table Conditional Formatting with This Excel Tutorial! - Episode 875 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 05 January 2009, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,944 once and liked it 13 people.