Скачать или смотреть Excel Conditional Format Mixed References - Episode 2105

Microsoft Excel Tutorial: Conditional Formatting Formulas with Mixed References.

👋 Welcome back to the MrExcel netcast! In this episode, we tackle a complex scenario involving conditional formatting and mixed references. Anderson, a yard manager dealing with trailer unloading schedules, presents an interesting Excel challenge.

🚚 Trailers need to be unloaded within specific time frames, and Anderson uses conditional formatting to track their status. However, copying the data to new rows poses challenges with mixed references in conditional formatting formulas.

🎥 Watch the full episode to explore the solution and learn valuable Excel techniques:
Copying Conditional Format with Mixed References
Dealing with dynamic date-related formatting
Handling conditional formatting rules effectively

🔍 Topics Covered:
1️⃣ Setting up complex conditional formatting rules
2️⃣ Managing mixed references in conditional formatting
3️⃣ Strategies for copying formatted data without introducing excessive rules
4️⃣ Using helper columns for relative references in conditional formatting

🔗 Additional Tips:
Quickly navigate the Conditional Formatting dialog box without inserting unwanted cell references using the F2 key.
Explore the step-by-step process of building conditional formatting rules in slow motion.
🙌 Special thanks to Anderson for sharing this real-world Excel challenge! Your feedback and suggestions are always welcome in the YouTube comments.

👉 Subscribe to MrExcel for more insightful netcasts and Excel tutorials! Help us reach our goal of 1 million subscribers and join the Excel community.

📌 Outtake: Learn how to avoid accidental cell reference insertion in the Conditional Formatting dialog box using the F2 key.

👍 If you found this episode helpful, don't forget to like, share, and subscribe for more Excel insights. Stay tuned for the next netcast from MrExcel!

Table of Contents
(00:00) Conditional Format Copy with Mixed References
(00:41) Color-coding with a formula
(02:09) Using 4 Conditional Formatting Rules
(03:05) Conditional Format Formula is Hard-Coded with Absolute References
(04:24) Problem with New Rows
(06:31) Removing Dollar Signs in Formulas with Replace in Excel
(07:30) Too many conditional formatting rules after copying
(08:22) Use a Helper Column
(10:10) Test to see if it is working
(10:48) Recap
(11:02) Clicking Like really helps the algorithm
(11:32) Outtake: F2 to enable moving through conditional formatting formula
(12:56) Outtake: How to build the conditional formatting based on a formula.

#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:
Absolute references in Excel
Conditional formatting rules
Copying conditional format with mixed references
Excel conditional formatting
Excel date functions
Excel formulas
Excel helper columns
Excel macro
Excel paste special
Learn Excel from MrExcel Podcast
Relative references in Excel


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

Anderson is looking for a way to be able to copy blocks of data containing mixed conditional formatting
Is there a way to remove the dollar signs once conditional formatting is set up?
No - not without introducing dozens of new rules
My solution: helper cells that use relative references to replace the mixed reference in conditional formatting
Other techniques in this episode:
If you have four conditional formatting rules, set up the first 3 and then make the fourth rule be the default color
Outtake #1: Press F2 to stop Excel from inserting cell references in the conditional formatting dialog
Outtake #2: setting up conditional formatting
Bonus: Here is another solution from Mike ExcelisFun Girvin:    • Excel Magic Trick 1448: Conditional Format...