How to Use Excel for Data Analytics: Full Guide

TL;DR

Conditional formatting is the fastest way to spot patterns in a spreadsheet, and the duplicate values rule under Highlight Cells Rules is the single most useful option for data cleaning. Apply icon sets row by row rather than across an entire table, because comparing small numbers against large ones turns every indicator red.

Transcript

what's going on everybody welcome back to another video today we're going to be learning Excel for data analytics in under three hours now Excel is just one of those skills that everybody expects you to know and so you just don't want to know the very Basics you kind of want to go in depth and really understand it well and s... Read More

Key Insights

• Conditional formatting is located on the far right of Excel's Home tab and, in Microsoft's description, lets you easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values.
• Icon sets applied across an entire table produce misleading results because Excel compares every number together. Values like 25, 50, and 65 get judged against 450 and 750, so the smaller ones all turn red regardless of their own trend.
• The fix for misleading icon sets is applying the rule one row at a time. Selecting a single row and then applying icon sets makes the indicators represent that individual printer's pattern rather than the numeric spread of the whole data set.
• Arrow icon sets are the most commonly used type, and Excel offers versions with three levels or five levels. Other options include colors, shapes, and flags, though the presenter has rarely seen flags used outside of specific industries.
• Color scales apply a gradient across the selected range, coloring the highest values green and the lowest values red, with the colors fully customizable from the options Excel offers. This makes good and bad values visible at a glance.
• Data bars fill each cell proportionally using either a gradient or a solid fill. The highest value fills its cell completely, and a value of about 36,000 that is close to half the maximum fills roughly half the cell width.
• Top bottom rules include top 10 items, top 10 percent, bottom 10 items, bottom 10 percent, above average, and below average. Choosing above average highlights every cell exceeding the column average, using a see-through red fill with red text by default.
• The duplicate values rule under Highlight Cells Rules is the presenter's most-used conditional format, exceeding all other rules combined. It flags repeated entries and has a unique option that inverts the logic to highlight only non-duplicated values instead.

Questions & Answers

Q: What topics does this Excel for data analytics lesson cover?

The lesson covers Excel for data analytics in under three hours, starting with simpler topics and working toward more advanced ones. The named topics are conditional formatting, pivot tables, lookups, data visualization, data cleaning, and more. The framing is that Excel is a skill everybody expects you to know, so the goal is not just the very basics but going in depth and really understanding it well. The instruction is screen-based, walking through the actual Excel interface using a sample data set with sales figures, demographics, salaries, and start dates.

Q: Where is conditional formatting in Excel and what does it do?

Conditional formatting lives on the Home tab, all the way over to the right. Microsoft's own description of it is that it lets you easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values, a definition the presenter endorses as exactly how he would have put it. The menu is not complex: it contains highlight cell rules, top bottom rules, data bars, color scales, and icon sets, and at the bottom there are options to create a rule, clear a rule, and manage rules. Once you create a rule, you can go back and manage it.

Q: Why do all my icon set arrows turn red in Excel?

Icon sets turn everything red when you apply the rule across an entire table at once, because Excel compares all the numbers together as a single group. In the example, values like 25, 50, and 65 are being compared against 450 and 750, so the smaller figures are all judged as low and get red indicators no matter what their own trend looks like. The solution is to apply the icon set row by row instead. Selecting one row and applying icon sets to just that row makes the indicators representative of that individual printer's actual pattern rather than of all the numbers as a whole.

Q: How do color scales and data bars differ in Excel?

Both are visual magnitude indicators but they render differently. Color scales apply a gradient across the range so the highest values appear green and the lowest appear red, and you can swap in any of the color combinations Excel offers, giving you a quick read on what is good and what is not. Data bars instead fill the cell itself, using either a gradient fill or a solid fill. The highest value in the range gets a completely filled bar, and a value around 36,000 that sits close to half of the maximum fills roughly half of its cell. The presenter notes that data bars are not seen very often in practice.

Q: How do you use above average conditional formatting in Excel?

Above average is one of the top bottom rules, alongside top 10 items, top 10 percent, bottom 10 items, bottom 10 percent, and below average. Selecting a column such as salaries and choosing above average highlights every cell whose value exceeds the column average. In the demo the highlighted names are Michael Scott, Toby Flenderson, and Dwight Schrute, which the presenter says is no shock, since those high earners pull the average up so everyone else falls beneath it. Below average does the inverse and highlights all the remaining cells. The default styling is a see-through red fill with the text in the cell also colored red.

Q: Why is the duplicate values rule so useful for data cleaning?

The duplicate values rule, found under Highlight Cells Rules, is the presenter's single most used conditional formatting rule, used more than all the other conditional formatting rules combined. Almost every data set contains some kind of identifier: an employee ID, a customer or client ID, a social security number, an address, or a phone number. Running duplicate values across those columns surfaces repeated entries that should not exist, which is how the presenter finds data quality issues. Working with pharmaceutical, pharmacy, and healthcare data, he says he finds problems this way all the time and uses the rule almost every single time he opens a new data set or starts with a new client's data.

Q: What is the difference between the duplicate and unique options in Excel?

They are inverse settings within the same Highlight Cells Rules duplicate values dialog. The duplicate option highlights the cells whose values appear more than once in the selected range, which is how the presenter spots a repeated start date in the example data. Switching the dropdown to unique flips the logic so Excel highlights all the cells that are not duplicates instead. Both options let you change the highlight color from the presets, and there is a custom format choice as well, though the presenter says he never spends time on custom formats and typically sticks with the default styling.

Q: How do you remove conditional formatting rules in Excel?

The clear rules option sits at the bottom of the conditional formatting menu alongside create rule and manage rules. You can clear rules from selected cells, which only removes the formatting from whatever range you currently have highlighted, as demonstrated when the presenter selects column G and clears just that column. Alternatively you can clear rules for the entire sheet, which strips the conditional formatting from every single column and row at once. Clearing before applying a new rule is a good habit, since the presenter clears the existing formatting on the demographics column before switching to data bars.

Summary & Key Takeaways

• The lesson covers Excel for data analytics in under three hours, moving from simpler topics to more advanced ones. The stated agenda includes conditional formatting, pivot tables, lookups, data visualization, and data cleaning. Excel is framed as a skill everybody expects you to know, so the goal is depth rather than just the very basics.

• Conditional formatting sits on the far right of the Home tab and, per Microsoft's own description, lets you easily spot trends and patterns using bars, colors, and icons. Its menu contains highlight cell rules, top bottom rules, data bars, color scales, and icon sets, plus options at the bottom to create, clear, and manage rules.

• Icon sets, color scales, and data bars each visualize magnitude differently: arrows show direction of change, color scales run a green-to-red gradient from high to low, and data bars fill each cell proportionally so a value near 36,000 fills roughly half the bar of the highest value. Data bars come in gradient or solid fill.