title
Conditional Formatting in Excel | Excel Tutorials for Beginners

description
Take my Full Excel for Data Analytics Course! https://www.analystbuilder.com/courses/excel-for-data-analytics Excel is one of the most used skills in the data world. In this series we will be walking through all of the most important topics that Data Analysts need to know in order to be proficient in Excel. Excel Conditional Formatting Template: https://github.com/AlexTheAnalyst/Excel-Tutorial/blob/main/Conditional%20Formatting%20Excel%20Tutorial%20File.xlsx Udemy Excel Courses: Pivot Tables in Excel: https://bit.ly/3JXCq28 Visualizations and Charts in Excel: https://bit.ly/3HOyij9 All Excel Essentials: https://bit.ly/3tf6XCK ____________________________________________ SUBSCRIBE! Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content! ____________________________________________ RESOURCES: Coursera Courses: Google Data Analyst Certification: https://coursera.pxf.io/5bBd62 Data Analysis with Python - https://coursera.pxf.io/BXY3Wy IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR Tableau Data Visualization - https://coursera.pxf.io/MXYqaN Udemy Courses: Python for Data Analysis and Visualization- https://bit.ly/3hhX4LX Statistics for Data Science - https://bit.ly/37jqDbq SQL for Data Analysts (SSMS) - https://bit.ly/3fkqEij Tableau A-Z - http://bit.ly/385lYvN *Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!* ____________________________________________ SUPPORT MY CHANNEL - PATREON/MERCH Patreon Page - https://www.patreon.com/AlexTheAnalyst Alex The Analyst Shop - https://teespring.com/stores/alex-the-analyst-shop ____________________________________________ Websites: GitHub: https://github.com/AlexTheAnalyst Instagram: @Alex_The_Analyst ____________________________________________ *All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for*

detail
{'title': 'Conditional Formatting in Excel | Excel Tutorials for Beginners', 'heatmap': [{'end': 682.823, 'start': 665.799, 'weight': 1}, {'end': 723.359, 'start': 699.787, 'weight': 0.768}, {'end': 859.575, 'start': 839.459, 'weight': 0.893}, {'end': 1001.058, 'start': 964.279, 'weight': 0.745}], 'summary': 'Learn excel conditional formatting for data visualization, color scales, and salary analysis, with emphasis on using data analysis tools and techniques, and highlighting the efficiency and time-saving potential in data analysis.', 'chapters': [{'end': 74.361, 'segs': [{'end': 56.253, 'src': 'embed', 'start': 0.109, 'weight': 0, 'content': [{'end': 2.874, 'text': "What's going on everybody? Welcome back to another Excel tutorial.", 'start': 0.109, 'duration': 2.765}, {'end': 5.037, 'text': "Today, we'll be looking at conditional formatting.", 'start': 2.934, 'duration': 2.103}, {'end': 13.995, 'text': "Now, if you've never heard of conditional formatting before, that's okay.", 'start': 11.334, 'duration': 2.661}, {'end': 16.736, 'text': 'I had never heard of it before I became a data analyst.', 'start': 14.195, 'duration': 2.541}, {'end': 20.338, 'text': "And so now that I've been using Excel a lot, of course I use it quite a bit.", 'start': 16.756, 'duration': 3.582}, {'end': 22.079, 'text': 'And so I want to show you how to use it.', 'start': 20.378, 'duration': 1.701}, {'end': 26.081, 'text': 'Conditional formatting is basically just a way to see patterns and trends in data.', 'start': 22.499, 'duration': 3.582}, {'end': 28.442, 'text': "And that's a super simple way of putting it.", 'start': 26.161, 'duration': 2.281}, {'end': 31.023, 'text': "Um, but it's very easy to use.", 'start': 28.902, 'duration': 2.121}, {'end': 34.524, 'text': 'And so hopefully I can show you how to use it really easily.', 'start': 31.223, 'duration': 3.301}, {'end': 40.267, 'text': 'And a lot of the things that I use the most and some of the things that I use it for so that you can also know how to use conditional formatting.', 'start': 34.544, 'duration': 5.723}, {'end': 45.549, 'text': 'Now before we jump into the tutorial, I want to give a huge shout out to the sponsor of this Excel series and that is Udemy.', 'start': 40.467, 'duration': 5.082}, {'end': 48.19, 'text': 'You guys know by now that I absolutely love Udemy.', 'start': 46.049, 'duration': 2.141}, {'end': 54.032, 'text': "I've been using them for years and I've taken literally hundreds of courses on Udemy, and I've learned so so much,", 'start': 48.37, 'duration': 5.662}, {'end': 56.253, 'text': 'especially when I was first starting out as a data analyst.', 'start': 54.032, 'duration': 2.221}], 'summary': 'Excel tutorial on conditional formatting for data analysis with a shoutout to udemy as the sponsor.', 'duration': 56.144, 'max_score': 0.109, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM109.jpg'}], 'start': 0.109, 'title': 'Excel tutorial: conditional formatting', 'summary': 'Introduces using conditional formatting in excel, emphasizing its importance for data analysis and acknowledging udemy as sponsor, promoting their excel courses.', 'chapters': [{'end': 74.361, 'start': 0.109, 'title': 'Excel tutorial: conditional formatting', 'summary': 'Provides an introduction to using conditional formatting in excel, emphasizing its importance for data analysis and offering a shout-out to udemy as the sponsor, highlighting the usefulness of their excel courses.', 'duration': 74.252, 'highlights': ["The tutorial introduces conditional formatting in Excel, highlighting its significance for data analysis and the speaker's personal experience as a data analyst.", 'The speaker emphasizes the simplicity and ease of use of conditional formatting, aiming to demonstrate its practical application to the audience.', "A shout-out is given to Udemy as the sponsor of the Excel series, with the speaker expressing their appreciation for Udemy's Excel courses and recommending them to the audience."]}], 'duration': 74.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM109.jpg', 'highlights': ["The tutorial introduces conditional formatting in Excel, highlighting its significance for data analysis and the speaker's personal experience as a data analyst.", 'The speaker emphasizes the simplicity and ease of use of conditional formatting, aiming to demonstrate its practical application to the audience.', "A shout-out is given to Udemy as the sponsor of the Excel series, with the speaker expressing their appreciation for Udemy's Excel courses and recommending them to the audience."]}, {'end': 208.631, 'segs': [{'end': 107.138, 'src': 'embed', 'start': 74.381, 'weight': 0, 'content': [{'end': 77.483, 'text': "All right, so let's jump right into it on this home tab right here.", 'start': 74.381, 'duration': 3.102}, {'end': 83.285, 'text': 'If we go all the way over to the right, there is conditional formatting and the description that it gives us is easily.', 'start': 77.503, 'duration': 5.782}, {'end': 88.787, 'text': 'spot trends and patterns in your data, using bars, colors and icons to visually highlight important values.', 'start': 83.285, 'duration': 5.502}, {'end': 91.889, 'text': 'And that is exactly how I would have defined it.', 'start': 88.868, 'duration': 3.021}, {'end': 93.369, 'text': 'Really good job, Microsoft.', 'start': 92.169, 'duration': 1.2}, {'end': 94.35, 'text': 'Exactly how I would have done it.', 'start': 93.409, 'duration': 0.941}, {'end': 97.915, 'text': "What you'll see right away is there's nothing too complex.", 'start': 95.274, 'duration': 2.641}, {'end': 107.138, 'text': 'We have some highlights of rules, we have some top-bottom rules data bars, color scales, icon sets and then at the bottom we can create a rule,', 'start': 98.255, 'duration': 8.883}], 'summary': "Microsoft's conditional formatting helps spot trends and patterns visually in data using bars, colors, and icons.", 'duration': 32.757, 'max_score': 74.381, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM74381.jpg'}, {'end': 142.89, 'src': 'embed', 'start': 112.9, 'weight': 2, 'content': [{'end': 117.921, 'text': "We're going to start with these icon sets and I'm going to show you how to use those and we'll work our way to the top,", 'start': 112.9, 'duration': 5.021}, {'end': 122.023, 'text': "and then I'll show you how to create some rules yourself and how that all works.", 'start': 117.921, 'duration': 4.102}, {'end': 125.08, 'text': "Let's start off with the icon sets.", 'start': 123.539, 'duration': 1.541}, {'end': 126.521, 'text': "I'm gonna go over here to sales.", 'start': 125.12, 'duration': 1.401}, {'end': 136.727, 'text': 'And for this data, we kind of have this trend or pattern that you can kind of see over time, so over the months.', 'start': 128.021, 'duration': 8.706}, {'end': 142.89, 'text': "So if we go right here, and let's use that conditional forming, let's use that icon sets.", 'start': 137.627, 'duration': 5.263}], 'summary': 'Tutorial on using icon sets for conditional formatting in sales data visualization.', 'duration': 29.99, 'max_score': 112.9, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM112900.jpg'}], 'start': 74.381, 'title': 'Conditional formatting for data visualization', 'summary': 'Explains how to use conditional formatting in microsoft excel to visually highlight important data trends and patterns using bars, colors, and icons, allowing for quick visualization and analysis of the dataset.', 'chapters': [{'end': 208.631, 'start': 74.381, 'title': 'Conditional formatting for data visualization', 'summary': 'Explains how to use conditional formatting in microsoft excel to visually highlight important data trends and patterns using bars, colors, and icons, allowing for quick visualization and analysis of the dataset.', 'duration': 134.25, 'highlights': ['Conditional formatting in Microsoft Excel allows for the visual representation of data trends and patterns using bars, colors, and icons. The feature helps in easily spotting trends and patterns in the data, providing a visual representation through bars, colors, and icons.', 'Icon sets in conditional formatting help to quickly visualize and analyze data trends, showing if values are average, below average, above average, or increasing. Icon sets provide a quick visual indication of data patterns, allowing for easy identification of average, below average, above average, or increasing values.', 'Individual application of icon sets for each row provides a more representative visualization of the data, allowing for a clearer analysis of specific data subsets. When applied individually to each row, icon sets provide a clearer visualization of specific data subsets, allowing for a more accurate analysis.']}], 'duration': 134.25, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM74381.jpg', 'highlights': ['Conditional formatting in Microsoft Excel visually represents data trends and patterns using bars, colors, and icons.', 'Icon sets in conditional formatting quickly visualize and analyze data trends, showing if values are average, below average, above average, or increasing.', 'Individual application of icon sets for each row provides a more representative visualization of the data.']}, {'end': 341.322, 'segs': [{'end': 299.228, 'src': 'embed', 'start': 208.631, 'weight': 0, 'content': [{'end': 210.992, 'text': "the arrows are ones that you'll probably see the most often.", 'start': 208.631, 'duration': 2.361}, {'end': 221.56, 'text': "that's the one i've used if i ever do use them, but you can, you know, do ones like where they have kind of a trend upward or a trend downward.", 'start': 210.992, 'duration': 10.568}, {'end': 223.942, 'text': "And so there's just several more arrows.", 'start': 222.1, 'duration': 1.842}, {'end': 225.303, 'text': 'This one only gives you three.', 'start': 223.982, 'duration': 1.321}, {'end': 227.585, 'text': 'As you can see, this one gives you five.', 'start': 225.824, 'duration': 1.761}, {'end': 233.73, 'text': 'And you can do colors or shapes or different indicators and all these different things.', 'start': 227.605, 'duration': 6.125}, {'end': 238.012, 'text': "And honestly, it's kind of whatever you want to use, whatever makes sense for your data.", 'start': 234.551, 'duration': 3.461}, {'end': 241.613, 'text': "But, you know, I've really only ever seen like these colors being used.", 'start': 238.092, 'duration': 3.521}, {'end': 244.253, 'text': "I've never really seen these flags or anything like that.", 'start': 241.673, 'duration': 2.58}, {'end': 247.034, 'text': 'But again, it just depends on what industry you work in.', 'start': 244.673, 'duration': 2.361}, {'end': 248.094, 'text': 'You might you might see that.', 'start': 247.054, 'duration': 1.04}, {'end': 254.696, 'text': "Let's go right over here to the demographics and let's look at our color scales.", 'start': 248.314, 'duration': 6.382}, {'end': 260.557, 'text': 'Now, color scales are going to be the probably the most obvious thing that in data bars are going to be the most obvious things in here.', 'start': 254.896, 'duration': 5.661}, {'end': 268.719, 'text': "If you go right here and you look at this color scale, if it's high, if it's among the top ones, it's green.", 'start': 261.817, 'duration': 6.902}, {'end': 270.42, 'text': "The lowest, it's red.", 'start': 268.979, 'duration': 1.441}, {'end': 275.741, 'text': 'You can change that to really any colors you want, any colors that they offer you.', 'start': 271.2, 'duration': 4.541}, {'end': 279.433, 'text': 'It does exactly what it does.', 'start': 278.092, 'duration': 1.341}, {'end': 284.397, 'text': "It's a color scale, a gradient of the colors from high to low or low to high.", 'start': 279.473, 'duration': 4.924}, {'end': 290.442, 'text': "And so any color that you do, you'll be able to kind of see what's good and what's not good.", 'start': 284.937, 'duration': 5.505}, {'end': 295.145, 'text': 'That really is color scales in a nutshell.', 'start': 290.982, 'duration': 4.163}, {'end': 299.228, 'text': 'Data bars are, again, super, super straightforward.', 'start': 295.686, 'duration': 3.542}], 'summary': 'The transcript discusses using arrows, color scales, and data bars for visualizing data.', 'duration': 90.597, 'max_score': 208.631, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM208631.jpg'}], 'start': 208.631, 'title': 'Data visualization and color scales', 'summary': 'Explores the use of arrows, color scales, and data bars in data visualization, highlighting flexibility in choosing indicators and colors, and emphasizing the significance of color scales in representing high and low values. additionally, it briefly explains the rarity of certain color usage and gradient fills in data bars.', 'chapters': [{'end': 341.322, 'start': 208.631, 'title': 'Data visualization and color scales', 'summary': 'Discusses the use of arrows, color scales, and data bars in data visualization, emphasizing the flexibility in choosing indicators and colors, as well as the significance of color scales in representing high and low values. it also mentions the rarity of certain color usage and provides a brief explanation of gradient fills in data bars.', 'duration': 132.691, 'highlights': ['Color scales are the most obvious thing in data visualization, with green indicating high values and red indicating low values. Color scales are emphasized as the most prominent feature in data visualization, with green representing high values and red representing low values.', 'Flexibility in choosing indicators and colors for data visualization is highlighted, with the suggestion that it should make sense for the data being represented. The flexibility in choosing indicators and colors for data visualization is emphasized, with the recommendation that the chosen indicators and colors should make sense for the data being represented.', 'Different types of arrows can be used for data visualization, including those indicating trends upward or downward. The use of different types of arrows in data visualization, including those indicating trends upward or downward, is mentioned.', 'The rarity of certain color usage in data visualization is mentioned, indicating that some colors or indicators are not commonly seen. The rarity of certain color usage in data visualization is mentioned, with the indication that some colors or indicators are not commonly seen.', 'Explanation of gradient fills in data bars is provided, with the example of a blue gradient fill representing the highest value. An explanation of gradient fills in data bars is provided, with the example of a blue gradient fill representing the highest value.']}], 'duration': 132.691, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM208631.jpg', 'highlights': ['Color scales are emphasized as the most prominent feature in data visualization, with green representing high values and red representing low values.', 'Flexibility in choosing indicators and colors for data visualization is emphasized, with the recommendation that the chosen indicators and colors should make sense for the data being represented.', 'Different types of arrows can be used for data visualization, including those indicating trends upward or downward.', 'Explanation of gradient fills in data bars is provided, with the example of a blue gradient fill representing the highest value.', 'The rarity of certain color usage in data visualization is mentioned, with the indication that some colors or indicators are not commonly seen.']}, {'end': 550.607, 'segs': [{'end': 401.467, 'src': 'embed', 'start': 341.322, 'weight': 0, 'content': [{'end': 346.925, 'text': "but if you do see it, that's how you use it, that's how it can be done Again, pretty easy.", 'start': 341.322, 'duration': 5.603}, {'end': 351.547, 'text': 'As I just showed a second ago, if you want to clear the rules, you can clear from the selected cells.', 'start': 347.905, 'duration': 3.642}, {'end': 352.227, 'text': "That's what we're doing.", 'start': 351.567, 'duration': 0.66}, {'end': 355.309, 'text': "So I have column G selected, and I'm going to clear that.", 'start': 352.247, 'duration': 3.062}, {'end': 358.45, 'text': 'If you want to clear the rules of the entire sheet, you can do that as well.', 'start': 355.769, 'duration': 2.681}, {'end': 360.952, 'text': 'So it would affect every single column and row.', 'start': 358.47, 'duration': 2.482}, {'end': 363.273, 'text': "We'll just do this for now.", 'start': 361.192, 'duration': 2.081}, {'end': 366.516, 'text': "So now let's go look at the top bottom rules.", 'start': 364.193, 'duration': 2.323}, {'end': 373.843, 'text': 'So this is the top 10 items, top 10%, bottom 10 items, bottom 10%, above average and below average.', 'start': 366.876, 'duration': 6.967}, {'end': 376.866, 'text': "And they're going to do exactly what you think they are going to do.", 'start': 373.863, 'duration': 3.003}, {'end': 384.054, 'text': 'If you select above average is going to select or highlight the cells that are above the average in column G.', 'start': 377.186, 'duration': 6.868}, {'end': 386.256, 'text': "So let's look at the salaries that are above average.", 'start': 384.054, 'duration': 2.202}, {'end': 394.321, 'text': "All right, and so the ones that are at the very top are Michael Scott's, Toby Flenderson's and Dwight Schrute.", 'start': 387.335, 'duration': 6.986}, {'end': 396.102, 'text': 'No shock there.', 'start': 395.021, 'duration': 1.081}, {'end': 401.467, 'text': 'I believe the average is somewhere around like 48,500 or something.', 'start': 396.643, 'duration': 4.824}], 'summary': 'Demonstration of clearing rules and using top bottom rules to analyze salaries, including specific examples and average salary of around 48,500.', 'duration': 60.145, 'max_score': 341.322, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM341322.jpg'}, {'end': 455.315, 'src': 'embed', 'start': 422.699, 'weight': 1, 'content': [{'end': 429.042, 'text': 'And you can do this in a lot of different ways in Excel, but this is just a really simple, fast way to do that.', 'start': 422.699, 'duration': 6.343}, {'end': 431.143, 'text': "Let's get rid of that real quick.", 'start': 429.982, 'duration': 1.161}, {'end': 436.071, 'text': "And let's go back up here and now we can, oops, let's go to top bottom rules.", 'start': 432.21, 'duration': 3.861}, {'end': 439.972, 'text': "And now we can see the below average, and it's going to highlight all the other ones.", 'start': 436.151, 'duration': 3.821}, {'end': 443.032, 'text': 'And so it works exactly how you think it is going to work.', 'start': 440.032, 'duration': 3}, {'end': 446.353, 'text': 'And this is the default way that it highlights these cells.', 'start': 443.352, 'duration': 3.001}, {'end': 455.315, 'text': 'So it highlights them this kind of, um, see-through red, and then it highlights the actual text or the, or the, um, characters in their red as well.', 'start': 446.713, 'duration': 8.602}], 'summary': 'Excel offers various ways to highlight data, with default see-through red highlighting below average cells.', 'duration': 32.616, 'max_score': 422.699, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM422699.jpg'}, {'end': 507.647, 'src': 'embed', 'start': 478.56, 'weight': 2, 'content': [{'end': 484.342, 'text': 'Um, and the one in here that I use more than any other conditional formatting rule is this duplicate values.', 'start': 478.56, 'duration': 5.782}, {'end': 485.843, 'text': "So we'll start with that really quick.", 'start': 484.662, 'duration': 1.181}, {'end': 487.865, 'text': "And I'll kind of show you a few of these other ones.", 'start': 485.863, 'duration': 2.002}, {'end': 493.111, 'text': 'But this duplicate values to me is one of the most useful ones.', 'start': 488.286, 'duration': 4.825}, {'end': 496.134, 'text': "And so let's kind of show you how that works.", 'start': 493.972, 'duration': 2.162}, {'end': 501.239, 'text': 'If you go to the start date, you can see that we have a duplicate value right here.', 'start': 497.215, 'duration': 4.024}, {'end': 507.647, 'text': 'And if we go over here to conditional formatting, highlight cells, rules and duplicate values,', 'start': 502.202, 'duration': 5.445}], 'summary': "The most useful conditional formatting rule is 'duplicate values', which can be applied to start date cells.", 'duration': 29.087, 'max_score': 478.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM478560.jpg'}], 'start': 341.322, 'title': 'Excel data rules and highlighting salaries', 'summary': 'Covers using and clearing data rules in excel, including top and bottom rules, and highlighting cells above and below the average salary of $48,500, demonstrating its application with specific employee salaries like michael scott, toby flenderson, and dwight schrute.', 'chapters': [{'end': 376.866, 'start': 341.322, 'title': 'Excel data rules and clearing', 'summary': 'Explains how to use and clear data rules in excel, including top and bottom rules, as well as clearing rules for selected cells and the entire sheet.', 'duration': 35.544, 'highlights': ['You can clear rules from selected cells or the entire sheet, affecting every single column and row.', 'Excel provides options for top 10 items, top 10%, bottom 10 items, bottom 10%, above average, and below average rules for data analysis.']}, {'end': 550.607, 'start': 377.186, 'title': 'Excel: highlighting above and below average salaries', 'summary': 'Explains how to use excel to highlight cells above and below average, with the average salary being around $48,500, showcasing michael scott, toby flenderson, and dwight schrute having salaries above average and using the duplicate values highlight rule as the most useful one.', 'duration': 173.421, 'highlights': ['The average salary is around $48,500, with Michael Scott, Toby Flenderson, and Dwight Schrute having salaries above average, bringing up the average significantly.', "Using Excel's conditional formatting, it's possible to easily highlight cells above and below average, providing a quick way to identify outliers.", "The most frequently used conditional formatting rule is the 'duplicate values' highlight, which efficiently identifies duplicate entries in the dataset."]}], 'duration': 209.285, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM341322.jpg', 'highlights': ['Excel provides options for top 10 items, top 10%, bottom 10 items, bottom 10%, above average, and below average rules for data analysis.', "Using Excel's conditional formatting, it's possible to easily highlight cells above and below average, providing a quick way to identify outliers.", "The most frequently used conditional formatting rule is the 'duplicate values' highlight, which efficiently identifies duplicate entries in the dataset.", 'The average salary is around $48,500, with Michael Scott, Toby Flenderson, and Dwight Schrute having salaries above average, bringing up the average significantly.', 'You can clear rules from selected cells or the entire sheet, affecting every single column and row.']}, {'end': 752.505, 'segs': [{'end': 617.565, 'src': 'embed', 'start': 578.536, 'weight': 0, 'content': [{'end': 585.08, 'text': 'Now I work a lot with pharmaceutical data, a lot with pharmacy data, as well as healthcare data.', 'start': 578.536, 'duration': 6.544}, {'end': 591.623, 'text': 'So like names, social security numbers, addresses, phone numbers, all of those things, all that customer or client information.', 'start': 585.2, 'duration': 6.423}, {'end': 596.948, 'text': 'And oftentimes, when I get a new data set and I have it in Excel or I convert it to Excel,', 'start': 592.003, 'duration': 4.945}, {'end': 601.032, 'text': 'I will start using these duplicates to try to find issues with the data.', 'start': 596.948, 'duration': 4.084}, {'end': 602.534, 'text': 'And I find them all the time.', 'start': 601.172, 'duration': 1.362}, {'end': 608.9, 'text': "Either there's an employee ID or some type of customer ID or client ID that has a duplicate in there that should not be in there.", 'start': 602.854, 'duration': 6.046}, {'end': 610.602, 'text': "Or there's multiple socials.", 'start': 609.521, 'duration': 1.081}, {'end': 613.203, 'text': "security numbers, or there's an issue in some other way.", 'start': 610.742, 'duration': 2.461}, {'end': 617.565, 'text': "And I'm able to find those things and spot those patterns using this duplicates.", 'start': 613.243, 'duration': 4.322}], 'summary': 'Working with pharmaceutical and healthcare data, identifying and resolving duplicates to find data issues', 'duration': 39.029, 'max_score': 578.536, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM578536.jpg'}, {'end': 682.823, 'src': 'heatmap', 'start': 653.366, 'weight': 3, 'content': [{'end': 657.829, 'text': 'so that is how duplicates work again super great.', 'start': 653.366, 'duration': 4.463}, {'end': 665.799, 'text': "it's obviously not super useful when you're only using 10 rows, but when you have 50,000, 100,000, and there should be zero duplicates in there,", 'start': 657.829, 'duration': 7.97}, {'end': 669.466, 'text': 'and you highlight it and then you come right here,', 'start': 665.799, 'duration': 3.667}, {'end': 679.383, 'text': "use the filter and we're gonna filter and we're gonna sort by the color and it allows you to sort by the color and you have duplicates in there,", 'start': 669.466, 'duration': 9.917}, {'end': 680.083, 'text': "then that's a problem.", 'start': 679.383, 'duration': 0.7}, {'end': 682.823, 'text': 'And you identified a problem super quickly.', 'start': 680.643, 'duration': 2.18}], 'summary': 'Identify duplicates in large datasets efficiently.', 'duration': 26.017, 'max_score': 653.366, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM653366.jpg'}, {'end': 730.908, 'src': 'heatmap', 'start': 699.787, 'weight': 4, 'content': [{'end': 707.857, 'text': "So let's go ahead and we're going to clear that as well as get rid of our conditional formatting rules.", 'start': 699.787, 'duration': 8.07}, {'end': 716.387, 'text': 'Now, another one that I use a lot is this one right here, which is the text that contains.', 'start': 709.559, 'duration': 6.828}, {'end': 723.359, 'text': "Honestly, this one comes a lot in handy, especially when you're looking for like a specific keyword.", 'start': 717.352, 'duration': 6.007}, {'end': 729.807, 'text': 'In my case, a lot of times I was using this when I was going through drug names.', 'start': 723.82, 'duration': 5.987}, {'end': 730.908, 'text': 'I am not a doctor.', 'start': 730.047, 'duration': 0.861}], 'summary': "Clear conditional formatting rules, use 'text contains' for specific keywords like drug names.", 'duration': 31.121, 'max_score': 699.787, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM699787.jpg'}], 'start': 550.707, 'title': 'Data duplication and analysis', 'summary': 'Discusses identifying data duplicates in personal information and the use of duplicates to identify and address data issues in pharmaceutical and healthcare data sets. it also emphasizes the significance of using data analysis tools and techniques such as identifying and handling duplicates and using text contains function for efficient data processing and analysis.', 'chapters': [{'end': 617.565, 'start': 550.707, 'title': 'Identifying data duplicates for data sets', 'summary': 'Discusses the common occurrence of duplicates in data sets, particularly in personal information such as social security numbers, addresses, and phone numbers, and highlights the use of duplicates to identify and address data issues in pharmaceutical and healthcare data sets.', 'duration': 66.858, 'highlights': ['The speaker works with pharmaceutical and healthcare data, dealing with personal information like names, social security numbers, addresses, and phone numbers. The speaker mentions working extensively with pharmaceutical and healthcare data, dealing with personal information like names, social security numbers, addresses, and phone numbers.', 'Duplicates are commonly found in the data sets, including employee IDs, customer IDs, and multiple social security numbers. The speaker frequently encounters duplicates in the data sets, such as employee IDs, customer IDs, and multiple occurrences of social security numbers.', 'Identifying and addressing data issues by using duplicates to find patterns and inconsistencies in the data sets. The speaker uses duplicates to identify and address data issues by finding patterns and inconsistencies in the data sets, such as duplicate IDs and multiple occurrences of social security numbers.']}, {'end': 752.505, 'start': 617.685, 'title': 'Data analysis tools and techniques', 'summary': 'Discusses the significance of using data analysis tools such as identifying and handling duplicates and using text contains function, emphasizing their practicality and benefits in data analysis for efficient data processing and analysis.', 'duration': 134.82, 'highlights': ['Identifying and handling duplicates is crucial for efficient data analysis, particularly when dealing with large datasets of 50,000 or 100,000 rows, where zero duplicates are expected, allowing quick problem identification and prevention of errors. The importance of identifying and handling duplicates in large datasets, such as 50,000 or 100,000 rows, is emphasized for quick problem identification and prevention of errors.', "Using the 'text contains' function is highlighted as a practical tool for searching specific keywords, such as drug names, enabling efficient data retrieval and analysis, particularly useful for non-medical professionals. The 'text contains' function is emphasized as a practical tool for searching specific keywords, such as drug names, enabling efficient data retrieval and analysis, particularly useful for non-medical professionals."]}], 'duration': 201.798, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM550707.jpg', 'highlights': ['The speaker works with pharmaceutical and healthcare data, dealing with personal information like names, social security numbers, addresses, and phone numbers.', 'Duplicates are commonly found in the data sets, including employee IDs, customer IDs, and multiple social security numbers.', 'Identifying and addressing data issues by using duplicates to find patterns and inconsistencies in the data sets.', 'Identifying and handling duplicates is crucial for efficient data analysis, particularly when dealing with large datasets of 50,000 or 100,000 rows, where zero duplicates are expected, allowing quick problem identification and prevention of errors.', "Using the 'text contains' function is highlighted as a practical tool for searching specific keywords, such as drug names, enabling efficient data retrieval and analysis, particularly useful for non-medical professionals."]}, {'end': 1246.093, 'segs': [{'end': 829.892, 'src': 'embed', 'start': 804.879, 'weight': 1, 'content': [{'end': 812.021, 'text': 'and if you put in a dash and it has it in there, then you know that that is wrong.', 'start': 804.879, 'duration': 7.142}, {'end': 815.658, 'text': 'Now that is really all I wanted to show you in the highlight cells rules.', 'start': 812.455, 'duration': 3.203}, {'end': 819.862, 'text': 'The duplicate values and the text contains are by far the ones that I use the most.', 'start': 815.958, 'duration': 3.904}, {'end': 827.89, 'text': 'All the other ones I have used, these ones not so much, but in these highlight cells rules, I use these two all the time.', 'start': 820.343, 'duration': 7.547}, {'end': 829.892, 'text': 'Sometimes I use this between.', 'start': 828.531, 'duration': 1.361}], 'summary': 'The most used highlight cells rules are duplicate values and text contains.', 'duration': 25.013, 'max_score': 804.879, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM804879.jpg'}, {'end': 874.093, 'src': 'heatmap', 'start': 839.459, 'weight': 0, 'content': [{'end': 843.922, 'text': "And if you haven't used them before to maybe try them out and see how you can apply them to your own data sets.", 'start': 839.459, 'duration': 4.463}, {'end': 850.866, 'text': "Now we've looked at all of these preset ones in conditional formatting, but you can also do a new rule.", 'start': 844.082, 'duration': 6.784}, {'end': 859.575, 'text': 'And so if we click on new rule right here and we go down to use a formula to determine which cells to format, we can add our own formula in here.', 'start': 850.947, 'duration': 8.628}, {'end': 862.56, 'text': 'that will then highlight exactly what we want.', 'start': 859.575, 'duration': 2.985}, {'end': 868.549, 'text': "And so if there isn't a preset rule that you like, it doesn't have the option that you want.", 'start': 862.981, 'duration': 5.568}, {'end': 874.093, 'text': 'you can do almost any formula that you want in our formulas video that we did a few weeks ago and you can put it in here,', 'start': 868.549, 'duration': 5.544}], 'summary': 'Learn how to create custom formatting rules using formulas in excel.', 'duration': 34.634, 'max_score': 839.459, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM839459.jpg'}, {'end': 940.463, 'src': 'embed', 'start': 908.363, 'weight': 5, 'content': [{'end': 911.726, 'text': "That's how you start the how you actually create a formula.", 'start': 908.363, 'duration': 3.363}, {'end': 914.168, 'text': "And we're gonna give it this range right here.", 'start': 912.286, 'duration': 1.882}, {'end': 918.329, 'text': "and so it's going to take everything from g2 to g10.", 'start': 914.728, 'duration': 3.601}, {'end': 921.429, 'text': 'now these dollar signs are super important.', 'start': 918.329, 'duration': 3.1}, {'end': 927.331, 'text': "if you don't know how to use them or you don't know what they do, you're going to mess up this formula a lot.", 'start': 921.429, 'duration': 5.902}, {'end': 932.672, 'text': "and so what this dollar sign basically does is it's basically hard coding it in there.", 'start': 927.331, 'duration': 5.341}, {'end': 940.463, 'text': 'it is only going to look at g2 and is only going to look at g10 or through g10, because that colon And this can come into play,', 'start': 932.672, 'duration': 7.791}], 'summary': 'Transcript covers creating a formula for a range from g2 to g10 using dollar signs, crucial for avoiding formula errors.', 'duration': 32.1, 'max_score': 908.363, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM908363.jpg'}, {'end': 1001.058, 'src': 'heatmap', 'start': 964.279, 'weight': 0.745, 'content': [{'end': 970.684, 'text': "We're going to get rid of the dollar signs because we want it to be pretty fluid and be able to be applied basically anywhere we want.", 'start': 964.279, 'duration': 6.405}, {'end': 972.306, 'text': "Let's go into this formula.", 'start': 971.305, 'duration': 1.001}, {'end': 982.554, 'text': "If it meets our criteria, let's give it a border and we'll give it some color.", 'start': 973.907, 'duration': 8.647}, {'end': 986.532, 'text': "We're going to say if this is greater than 50,000.", 'start': 983.375, 'duration': 3.157}, {'end': 990.194, 'text': "So let's hit OK, and nothing happened.", 'start': 986.533, 'duration': 3.661}, {'end': 992.415, 'text': "So let's go back and see why.", 'start': 990.654, 'duration': 1.761}, {'end': 1001.058, 'text': 'So if we go to our Manage Rules, you can see that so the G2 to G10 is greater than 50,000, but it only is being applied to this H11 cell,', 'start': 992.435, 'duration': 8.623}], 'summary': 'Removing dollar signs for flexibility, applying formula to cells, criteria set at 50,000, troubleshooting discrepancies in application.', 'duration': 36.779, 'max_score': 964.279, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM964279.jpg'}, {'end': 1137.457, 'src': 'embed', 'start': 1110.781, 'weight': 4, 'content': [{'end': 1115.726, 'text': 'Um, that was all for demonstration purposes, but again, you need to really be aware of that.', 'start': 1110.781, 'duration': 4.945}, {'end': 1119.21, 'text': "That is something that I think almost everybody's gonna mess up at some point.", 'start': 1115.766, 'duration': 3.444}, {'end': 1123.654, 'text': "If you don't already know about it, then you definitely are going to make that mistake.", 'start': 1119.67, 'duration': 3.984}, {'end': 1130.831, 'text': 'Now, if we come over here in this area, we go to our manage rules and not just the current selection, but this whole worksheet.', 'start': 1124.025, 'duration': 6.806}, {'end': 1132.793, 'text': 'then you can see that we have these two formulas.', 'start': 1130.831, 'duration': 1.962}, {'end': 1137.457, 'text': 'Now you can go in and edit any of these by double clicking or clicking on it and then hitting edit rule.', 'start': 1132.833, 'duration': 4.624}], 'summary': 'Be aware of the potential mistakes in managing rules and formulas in spreadsheets.', 'duration': 26.676, 'max_score': 1110.781, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM1110781.jpg'}, {'end': 1241.972, 'src': 'embed', 'start': 1217.004, 'weight': 2, 'content': [{'end': 1222.766, 'text': 'It can take honestly like ten minutes for something simple that you could do with conditional formatting in like ten seconds.', 'start': 1217.004, 'duration': 5.762}, {'end': 1226.967, 'text': 'So definitely something to mess with and use when you are working with your own data sets.', 'start': 1222.946, 'duration': 4.021}, {'end': 1228.768, 'text': 'I hope this was helpful.', 'start': 1227.627, 'duration': 1.141}, {'end': 1230.508, 'text': 'I mean, honestly, I use this all the time.', 'start': 1228.828, 'duration': 1.68}, {'end': 1236.49, 'text': "So, you know, I hope that somebody out there can can use this for their own work that they're currently using.", 'start': 1230.568, 'duration': 5.922}, {'end': 1237.87, 'text': 'Thank you guys so much for watching.', 'start': 1236.79, 'duration': 1.08}, {'end': 1239.151, 'text': 'I really appreciate it.', 'start': 1237.89, 'duration': 1.261}, {'end': 1241.972, 'text': 'Again, huge shout out to you me for sponsoring this Excel series.', 'start': 1239.171, 'duration': 2.801}], 'summary': 'Utilize conditional formatting to save time and enhance efficiency with data sets.', 'duration': 24.968, 'max_score': 1217.004, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM1217004.jpg'}], 'start': 752.525, 'title': 'Excel highlighting and conditional formatting', 'summary': 'Covers using highlight cells rules in excel to filter emails and utilizing best practices for conditional formatting, emphasizing efficiency and time-saving potential in data analysis.', 'chapters': [{'end': 992.415, 'start': 752.525, 'title': 'Highlight cells rules in excel', 'summary': 'Demonstrates how to use highlight cells rules in excel to filter emails by specific criteria, such as containing certain text or values, and highlights the most frequently used rules, like duplicate values and text contains, emphasizing their usefulness in data analysis.', 'duration': 239.89, 'highlights': ["The chapter emphasizes the frequent use of highlight cells rules, particularly focusing on the usefulness of the duplicate values and text contains rules in data analysis. The speaker highlights the most frequently used rules in Excel's highlight cells feature, emphasizing the usefulness of the duplicate values and text contains rules in data analysis.", 'The demonstration includes creating a new rule using a formula to determine which cells to format, showcasing the flexibility of creating custom formatting rules based on specific criteria. The chapter demonstrates the flexibility of creating custom formatting rules in Excel by using a formula to determine which cells to format, allowing for the creation of unique formatting rules based on specific criteria.', 'The speaker explains the importance of using dollar signs in formulas, highlighting their role in hard coding specific cell references and providing a cautionary note to avoid potential formula errors. The speaker explains the significance of using dollar signs in Excel formulas, stressing their role in hard coding specific cell references and cautioning against potential formula errors if not used correctly.']}, {'end': 1246.093, 'start': 992.435, 'title': 'Conditional formatting best practices', 'summary': 'Demonstrates the best practices for conditional formatting in excel, emphasizing the importance of selecting the correct range, avoiding hardcoding, and utilizing conditional formatting to efficiently analyze large datasets, potentially saving significant time compared to traditional filtering methods.', 'duration': 253.658, 'highlights': ['Utilize conditional formatting to efficiently analyze large datasets Demonstrates how conditional formatting can quickly identify patterns and trends, especially in datasets with thousands or tens of thousands of rows, potentially saving significant time compared to traditional filtering methods.', 'Emphasizes the importance of selecting the correct range and avoiding hardcoding in conditional formatting rules Stresses the significance of selecting the appropriate range and avoiding hardcoding in conditional formatting rules to ensure accurate and efficient data analysis, preventing potential errors and the need for subsequent adjustments.', 'Provides practical tips for creating and managing conditional formatting rules in Excel Offers practical guidance on creating and managing conditional formatting rules, including editing, duplicating, and deleting rules, ensuring effective utilization of this feature for data analysis and visualization in Excel.']}], 'duration': 493.568, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/_eZRkmRfVTM/pics/_eZRkmRfVTM752525.jpg', 'highlights': ['Demonstrates flexibility of creating custom formatting rules in Excel using formulas.', 'Emphasizes usefulness of duplicate values and text contains rules in data analysis.', 'Provides practical tips for creating and managing conditional formatting rules in Excel.', 'Utilize conditional formatting to efficiently analyze large datasets.', 'Stresses the significance of selecting the appropriate range and avoiding hardcoding in conditional formatting rules.', 'Explains the significance of using dollar signs in Excel formulas.']}], 'highlights': ["The tutorial introduces conditional formatting in Excel, highlighting its significance for data analysis and the speaker's personal experience as a data analyst.", 'Conditional formatting in Microsoft Excel visually represents data trends and patterns using bars, colors, and icons.', 'Color scales are emphasized as the most prominent feature in data visualization, with green representing high values and red representing low values.', 'Excel provides options for top 10 items, top 10%, bottom 10 items, bottom 10%, above average, and below average rules for data analysis.', 'The speaker works with pharmaceutical and healthcare data, dealing with personal information like names, social security numbers, addresses, and phone numbers.', 'Demonstrates flexibility of creating custom formatting rules in Excel using formulas.']}