Data Bars

Quick reference

• 00:05 The next tool we're gonna look at is another conditional formatting tool called
• 00:09 data bars.
• 00:10 And what data bars do is they allow you to get a really quick look at
• 00:15 your data point as it compares to every other data point in the selected range.
• 00:20 Now, this is kind of neat, what does that mean?
• 00:23 Well let me show you.
• 00:24 We're gonna take a look at our prices again.
• 00:26 If you recall in our last example,
• 00:27 we highlighted the top 10% of prices, but what about the bottom items?
• 00:32 How do I identify what those are?
• 00:34 I've got this table of data, which is kind of long, and gross.
• 00:38 So, this isn't really giving me any information.
• 00:41 So watch this now.
• 00:43 We'll go to conditional formatting, data bars and we'll pick up a data bar.
• 00:48 I haven't even clicked yet, and it's already put a preview on it.
• 00:50 So, I can actually check the different colors that I might want.
• 00:53 I'm just going to go with a plan old blue here.
• 00:56 But what you can see now, is I can immediately
• 00:59 identify that the four season one person tent is very expensive.
• 01:03 So, one person, 644 bucks to be able to sleep in this one, but
• 01:08 it's gonna last them all year round.
• 01:10 Where I scroll down the list and I find I've got the inverse over here,
• 01:13 I got a one per season, four person tent.
• 01:16 So that sleeps four, it's really cheap.
• 01:19 But it's obviously only gonna be good, because it must be made of really light
• 01:22 weight material and what not, in the summer.
• 01:24 It's probably a car camping tent, or something like that.
• 01:27 The nice thing about data bars again, is that they dynamically react to the data.
• 01:31 So if I go and change this again to 780,
• 01:34 you'll notice that everything updates really quickly, which is quite nice.
• 01:38 So now I can see that that 644 is not as big as it used to be.
• 01:41 If I roll this back, there we go it's backup were was.
• 01:45 This is one of my all time favorite visualizations because it gives a lot
• 01:49 of information really really easily on our brains are really quick at
• 01:52 picking that kind of stuff up so it's really nice.
• 01:54 Let's go take a look at some other ones you're gross let's go and
• 01:58 throw the gross revenues and see what we get from these.
• 02:01 Let's see, let's go grab a conditional format, maybe we want
• 02:04 something that's slightly different, how about we go with green this time?
• 02:09 We can see again the same kind of proportional changes, 25116,
• 02:13 that's the biggest number.
• 02:15 But what's interesting is the second largest value from our gross sales,
• 02:19 it is not from the largest price.
• 02:21 So we obviously sell a lot of three person, three season tents,
• 02:24 which is kind of an interesting piece around this particular data set.
• 02:27 Now what about discounts?
• 02:30 Discounts are negative numbers, so
• 02:32 let's go take a look at how these format with the data bar.
• 02:36 We'll go and we'll grab these guys here and
• 02:38 we'll go conditional formatting data bars, and I'm going to choose the blue one.
• 02:44 I notice that everything goes red.
• 02:46 It also pops out from the right-hand side not from the left-hand side.
• 02:50 So, positive numbers build out from the left and
• 02:53 negative numbers build out from the right.
• 02:55 What's interesting here, though, is I choose a blue rule and I get red numbers,
• 02:59 and that's a little bit weird.
• 03:00 So why don't we go and take a look at what's happening here.
• 03:03 We'll go to Conditional Formatting.
• 03:04 We'll go to Manage Rules.
• 03:07 We'll look at our Data Bar and say Edit.
• 03:10 And inside here, we don't see anything at all that shows us any red.
• 03:15 I see that we have a data bar.
• 03:16 I can choose to show the bar only if I don't want to see the numbers,
• 03:19 that's kind of interesting.
• 03:20 I can set my minimums and maximums and
• 03:22 by the virtue of this guy here I can set it to a cell which is kinda nice.
• 03:26 Here's my bar appearance, there's this little box right here,
• 03:30 negative value in axis.
• 03:32 When I click on that we can see the negative fill color.
• 03:35 So I wanna make this consistent with the blue, yellow kind of concept,
• 03:38 I could come back and choose something that is yellow.
• 03:40 I might go orange cuz it pops a little bit more here, let's choose this one.
• 03:45 The border color?
• 03:47 Well, sure, why not leave it red, why not?
• 03:48 And we'll say okay.
• 03:50 When I say okay, and okay again, you'll notice that it changes those bars out to
• 03:54 make them look a little bit different.
• 03:56 So I can change the appearance of these if I want.
• 03:59 The next one I wanna show you is what happens when we actually select a column
• 04:03 that has both negative and positive numbers in it.
• 04:06 So let's go and do this one here.
• 04:08 Will go to conditional formatting, data bars will choose the blue rule again.
• 04:13 And notice that this time we actually get an axis in the middle of the cell.
• 04:18 The positive variances go blue and come out the negative ones go red.
• 04:22 But what if we want to play with the formats on this because we can totally do
• 04:26 that too.
• 04:28 If we're going to our conditional formatting, we go to manage rows and
• 04:33 select our data bar and say edit.
• 04:35 Then here we have the option again to going to negative value in axis.
• 04:40 Now depending on the version of Excel, these are the Excel 2016 values,
• 04:43 not all of them may be there for your Excel 2010 or 2013.
• 04:47 But I could change my fill color, we'll go orange.
• 04:49 I think I'm gonna set the line to orange this time as well.
• 04:54 I can change the access to show up in the cell midpoint.
• 04:59 If I do this, say OK, OK, and apply,
• 05:03 notice that the values shift so that the line is dead center, all right?
• 05:09 The other option I have though, if I go back to edit rule, negative value and
• 05:13 axis, is I can say none.
• 05:16 And what this does is it actually flips my bars so
• 05:20 that they both build from the same side in different colors.
• 05:24 So there's some different options for
• 05:25 how you can use data bars to dynamically react and format your data.

Lesson notes are only available for subscribers.