From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Visuals using conditional formatting

From the course: Learning Data Analytics Part 2: Extending and Applying Core Knowledge

Visuals using conditional formatting

“

- [Instructor] Conditional formatting applies various formats based on condition we specify like we can highlight values that are greater than 1000 or we can show above or below average. When you sort and then you mix in meaningful conditional formats, you can easily discover information faster than any other traditional sort. We've been focused on the totals and we filtered them to 55,000 and up. We've created a combo chart to capture the PO counts and the total amounts as the sample visual. But let's go back over to our PO counts again and let's sort those total amounts highest to lowest. So I'll travel over to my file here. Okay, one of the things I want to do because I'm going to supply conditional format. So I'm going to go ahead and change this table format. I'll go to table design and I'm just going to choose no format. I do want to go ahead and sort my total amount, highest to lowest, so I'll right-click and I'll sort it largest to smallest. Now that we've sorted the column, you notice that those PO counts fall in line with the sort and this data's not appropriate for multiple sorting, we just have the customer name that we can sort by, we have the total amount we can sort by. If we had fields like products included maybe we could do multiple sorts, but we'd still have the same issues with highlighting those PO counts because they're going to always fall in line with the sort. If we don't find a way to highlight them, we can easily miss that dip that we only see in that line chart in the combo. What if I said every PO, regardless of what's ordered goes through the same standard process and there's a cost associated to the company for each one of them, would it stand to reason that we would want to keep our customers ordering and being effective with their Pos, because it does have an impact, not only to us as a company, but also to them? I have a client right now going through this exact process, they're getting what they called nickel and dimed on the POs. They determined after some analysis that if they could get their clients to order effectively for the quarter, they could reduce the paperwork for all the parties involved and fulfill the same amount of products. Sound interesting? Let's take a look at how conditional formatting helps us to support the sort. Okay, I want to add conditional formatting to my PO counts. So I'm going to go ahead and highlight these and because I want to see what is the below average PO count for any of these orders over 55,000, I can use conditional formatting. I'll go to conditional formats, top and bottom rules, and I'll choose below average. And because this is positive, I'm going to make it green. Okay, so I'll click okay. Great, now I can see all of the POs sorted by total amount and highlight the ones that are actually below the average. But I really want to do just a little bit more. Okay, so I'm going to show you how to add icons. I'm going to highlight my range again, I'll go to conditional formatting, I'll go to icon sets, and I'll go to my indicators, I'll choose the three symbols. We know by default it gives us the default rules. So I'll go back to my conditional formatting and choose manage rules. I'll choose my icon set and I'll edit the rule. Again, by default, it believes that the highest numbers are the green checks, so I'm going to reverse my icon order and then I'm going to tell my icons to be no cell icon. So when any PO count falls below 33% of the total value, it's going to give me a green check box. I'll go ahead and click okay, I'll go ahead and choose apply, because I want to take a look at it and then immediately the 12 and the nine are checked. I'll go ahead and click okay. Now that I've supplied below average conditional formatting and highlighted any PO that falls below 33% I can sort this range any way I would like and I'm going to maintain that conditional formatting. For example, let me go ahead and sort by customer name. I can see that these customers are still in that below average range and I can see those counts being 33% or less. I can sort my values again, this time doing smallest to largest and the conditional formatting carries over on those POs. There is not a single answer to every single data visualization. There're just commands that we can apply when they may be applicable. It's so important to just expand your mind to think outside of just sorting and filtering. There's not a single answer to every single data visualization, these are just commands that we can apply when they may be applicable. You can learn a lot in the library of the various conditional formats that are available in Excel. And guess what, a lot of other programs do conditional formatting as well. It's a concept, not just a tool in Excel.

Contents