Look Who it Was Random video

Excel - Highlight All Excel Records If Total Sales Exceed $1000 For Day - Episode 347

434 views · Published 18 November 2009 · 2:57 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Highlight All Excel Records If Total Sales Exceed $1000 For Day.

Welcome back to the MrExcel podcast, I'm Bill Jelen. In today's episode, we're tackling a tough problem that was brought to my attention by my friend Andy Walker. While writing my book "Learn Excel from MrExcel", I would often seek help from Andy in the green room during my trips to Toronto. And one day, he presented me with what seemed like a simple question, but as I tried to explain it, I realized it was much more complex than I initially thought.

The problem at hand was to highlight all the records in an Excel sheet where the total sales for a particular day exceeded $1000. This may seem like a straightforward task, but it becomes challenging when we have to add up multiple records to determine the total sales for a day. To solve this, we will be using a relatively new function called SUMIF, which allows us to add up values based on a specific criteria.

First, we will use the SUMIF function to add up all the sales for each day. Then, we will use a simple formula to check if the total sales for a day are greater than $1000. This will give us a column with either TRUE or FALSE values. Next, we will use Conditional Formatting to highlight all the rows where the sales were greater than $1000. This is an incredibly useful feature that many people are not aware of. However, it's essential to note that it's best to build the formula in a temporary column and then apply it to the dataset using Conditional Formatting.

I hope you found this tip helpful. Don't forget to tune in tomorrow for another exciting Excel tip. And if you have any suggestions or questions, please leave them in the comments below. Thanks for watching, and I'll see you in the next episode!

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/


Table of Contents:
(00:00) Introduction and problem explanation
(00:21) Background information on problem
(00:31) Difficulty of problem and use of SUMIF function
(00:51) Explanation and demonstration of SUMIF function
(01:18) Adding a test for values greater than 1000
(01:36) Using Conditional Formatting to highlight values
(02:09) Final result and tip for building formula
(02:27) 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:
Adding up multiple records in Excel
Complex conditional formatting formula in Excel
Conditional Formatting in Excel
Creating temporary columns in Excel
Excel sales tracking
Greater than comparison in Excel
Highlighting cells based on condition in Excel
How to use SUMIF function in Excel
Learning Excel tips and tricks
Using absolute references in Excel formulas
Using formulas in Conditional Formatting


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


Andy "Cyber" Walker once asked me what seemed like a simple question - he wanted to use conditional formatting to change records to green if the total of all records for that day exceeded $1000. While this sounds simple, it actually is incredibly complex to set up. Episode 347 walks you through the amazing conditional formatting option to allow this to happen.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel