Conditional Formatting to Highlight Missing Sequential Incident Numbers. Excel Magic Trick 1766

Learn how to.
1. (00:00) Introduction
2. (00:38) Why we build formulas in cells before pasting formula into Conditional Formatting Dialog Box
3. (01:19) Logic for formulas to find missing numbers in the incident number
4. (02:09) Create formula to find next sequential number from a text string. Learn about “Text Numbers” and Actual Numbers
5. (03:34) Create formula to check if NEXT number is in sequence
6. (05:14) Create formula to check if PREVIOUS number is in sequence
7. (06:46) What is an OR Logical Test?
8. (07:17) Clipboard to collect formula elements using keyboard Ctrl + C, C
9. (08:04) OR Function for Logical Text
10. (08:41) Mixed Cell References for formula to conditionally format whole row
11. (09:34) Paste formula into Conditional Formatting Dialog Box
12. (10:18) Test Conditional Formatting
13. (10:29) Summary
14. (10:50) Closing and Video Links

[krn]

Love it Mike. Thanks for the keyboard shortcut CTRL D. Always forget about that one. Merry Christmas!

[obviousness]

I followed along on the video and I was able to accomplish what I needed to. Thanks, great video!

[bagnon]

As good as Mike is at Excel, I think his video editing skill are even better. I can't imagine the amount of time it takes to put in every highlight, note and accent, and all for free just because he is a good person.

[SyedMuzammilMahasanShahi]

This is awesome. Thanks Mike for this EXCELlent video.

[roywilson]

Great video as always! In all the years I have been working with Excel I never knew that conditional formatting would see an error as a false value! Always something new to learn :D I always try to avoid conditional formatting as I find that it can bring Excel to a halt if you are looking at more than a few thousand records

[ivanmamchych]

Thanks, Mike. It's a great lesson and I especially appreciate the trick with shortcut "control c c". You're probably aware about another great Windows 10 functionality called Clipboard History. Shortcut "windows v" opens the list of copied fragments that can be chosen and pasted everywhere. User can even pin some important ones for multiple usage

[vida]

Very useful scenario and great solution!

[excel_along_the_way]

Hi Mike, definitely liked this one. Good tutorial. Thank you

[darrylmorgan]

Boom!Super Cool Helpful Lesson...Thank You Mike :)

[mattschoular]

That was great Mike. I love how you slip in the keyboard shortcuts as Bonus tips when explaining the primary goals. I always struggle with CF for entire rows based on a criteria. This was very helpful. I particularly like the subject matter and I can use this when working on our General Ledger transaction table. Thanks for sharing.

Happy Holidays to you and your family.

[Reduce_Scan]

Thanks Mike, this formula works even with the first and last two Numbers

[co-odinatemakoni]

Great video! Thank you for the clear explanation.

[chrism]

Mike, super useful as usual! Go team!

[kebincui]

Thanks Mike. Your videos are always great 🌹🌹👍👍

[shiffamohammed]

Thank you very much for the superb excel tutorial video, Mike!!!

[vincent-fpwf]

Hey, great video! Always has surprised me Excel has no standard functionality for this. Just wanted to add that the IFERROR function can be of use to get rid of the #Value error for those wondering

[asifpervaiz]

another great video learn a lot from your videos and still inspiring me i have tried this formula to trace out missing no. and it was successful =IF(A2+1=A3, "", A2+1),

[wayneedmondson]

Awesome Mike! Some great tricks there to get the correct bookend formatting for sequential numbering issues. Thanks for sharing :)) Thumbs up!!

[nadermounir]

Brilliant Mike. Thank u for this great explanation 👏

[khanabdussabur]

Awesome video mike, I have watched all your conditioning formatting videos and learned a lot of my unknowns from them which is impossible for individuals to express in language. But this video will definitely be of special importance to me. Long live and Happy Christmas Day.