Look Who it Was Random video

Excel - Turn Numbers into Yes/No in Excel Pivot Table | Step-by-Step Tutorial - Episode 723

3,255 views · Published 13 February 2009 · 3:58 · Indexed 14 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Turn Numbers into Yes/No in Excel Pivot Table | Step-by-Step Tutorial

Welcome back to the MrExcel netcast! In today's video, we're going to tackle a question that was asked during one of my Excel seminars. The question was about summarizing data in a pivot table by customer and quarter, but without showing any numbers. Instead, we wanted to show a simple "yes" or "no" if the condition was met. This one really made my head spin, but I came up with a solution that I think is a bit of a cheat. Let's dive in and see how we did it.

First, we start with our data and create a pivot table. We put the date along the left-hand side and group it by quarters. Then, we add the customer along the rows and the gross profit percent in the heart of the pivot table. But wait, the gross profit percent isn't in our field list! No problem, we can create a calculated field using the profit and revenue fields. We'll call it GP and divide profit by revenue. Now we have our gross profit percent throughout the pivot table.

Next, we want to get rid of any error values and show blanks instead. We also turn off the grand totals for columns and rows. Now, we have a series of GP percents, but we want to show a "yes" or "no" based on a condition. We could add a field to the original data set, but that would only work at the detail level. So, we double click on the Sum of GP field and change the name to GP percent. Then, we go to the number tab and use a custom number format to create a condition. In this case, we want to show "yes" if the GP percent is greater than 0.54 and "no" for any other condition.

And just like that, we have a pivot table that shows a simple "yes" or "no" for each customer and quarter. This is a great solution for reports that need to be presented to higher-ups in the company who may not want to see all the numbers. I hope this tip helps you in your own pivot table adventures. Thanks for watching and don't forget to subscribe to the MrExcel netcast for more Excel tips and tricks. See you next time!

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-on-youtube/


After summarizing data in a pivot table, you want to replace the numbers with a simple Yes/No text. Yes if they met the condition, No if they did not.

Episode 723 will show the rather convoluted process. In the process of creating the pivot table, you will see data grouping, calculated fields, table options, and then a bizarre conditional number format.

Table of Contents
(00:00) Pivot Table to show if a condition was met or not
(00:55) Build the pivot table and roll up to quarters
(01:26) Calculated field for Gross Profit
(01:48) Option to replace pivot table error values with blank
(02:11) Replace numbers over 54% with Yes
(02:39) Custom Number Format with Logical Tests in Square Brackets
(03:12) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Applying if statement in Excel pivot table
Changing number format in Excel pivot table
Creating a calculated field in Excel pivot table
Creating yes/no values in pivot table
Custom number formats in Excel
Displaying text instead of numbers in Excel
Excel netcast from MrExcel
Excel pivot table tutorial
How to show conditional values in pivot table
Removing error values in pivot table
Summarizing data in pivot table by customer and quarter
Using conditional formatting in Excel pivot table


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152174/

More from this channel