Whatever - needs an unconventional title don't it? Here's the challenge :
Your data looks like this :
| Name |
N |
S |
E |
W |
| Alpha |
700 |
800 |
900 |
500 |
| Beta |
2 |
3 |
5 |
6 |
| Gamma |
111 |
445 |
333 |
212 |
| Delta |
78 |
11 |
99 |
2 |
And, you want to be able to (easily) plot the bars for any ONE of the players AND, also make the largest bar STAND OUT.
(Excel without VBA, overlay plots show bars from data with max bar highlighted)
What? If you're plotting the bars for Alpha, you don't want to see just this :
But, you want to see this! :
And, you want to be able to (easily) change between the players. There's a few things I didn't know you could do. The easy one is - if you have a chart and click on the title, you get to put in a formula! You just have to point it to the Data field that the user is going to use to "Choose the Player"!
First, the on-demand chart - that, is the plain vanilla (no max highlighting) that gets you a player's performance on demand (yes, I know it's salesman, but player sounds cooler, right?)
Easy -
In cell, put your cursor, then, go to the Data menu and then click on Data Validation (if you don't even see the Data menu, which I highly doubt. I can understand if you don't see the Developer menu)
In the resulting "Data Validation" dialog, set "Allow:" to List and for "Source:", click in the field and then go to the worksheet and click and drag through the cells containing the names. You can do this much with a pic right? :)
Now comes the fun - how do you get the numbers corresponding to the player you've chosen? (Using the drop-down)
Pure gold :
Enter (in one cell, and then you can drag ) :
=VLOOKUP( (and now notice the tooltip)
Now, when you're being prompted for "lookup_value"
you just click on the drop-down cell (where the user chooses the Player's name) you recently created with Data Validation, et al.
In my case, I get B19 when I do that. Is that what I want? Sort of. I want $B$19. The recipe? After clicking the reference, you hit F4. Did you know that? Ain't that cool?
Type the comma, and now the fun - it's asking you for the "table_array". Here, you select the entire table, but not the headers. So, you click and drag starting on Alpha and finishing on the "2" at the bottom right. Yes, if you're wondering, that's not the only start and finish you can take :) But don't be perverse now :)
And then? Magic! Type the comma and then (without the quotes, duh) : "{2,3,4,5}"
The reasoning? If you noticed the tool-tip this time, it's asking you for column numbers. Well, the names are in Column 1, obviously :). But, why the curly braces and the array?
That comes in handy when you do the drag to fill. For now, just finish up with the final parenthesis, the ")" and see if Excel is happy (i.e., it has dutifully displayed the appropriate value from the "N" column for this Player. Yes? Good.
Your cell that you just finished will look something like this if you care for the formula :
=VLOOKUP($B$19,Table1,{2,3,4,5})
If you didn't format the data to be a table, then it's probably like this :
=VLOOKUP( $B$19, B26:F29, {2,3,4,5})
Now, you put your mouse pointer on the bottom right corner of this cell and drag over the next three cells to the right. You'll see four cells highlighted, and looking something like this
That's cause you need to give Excel more info - i.e., define this array. So, if these four aren't already selected, select them, then go to the formula field, and put your cursor in it and hit
CTRL+SHIFT+ENTER
This defines the array. You should now see them magically show the four values corresponding to this player. If you choose a different player with the drop-down, you should see the values update.
From here, on, the first chart is easy. You select the four cells, and the Insert a chart :
Just use this one for now :)
And you get your chart. You'll notice the X-axis don't exactly have the N,S,E,W :) What do you do?
You'll see the Chart Tools menu being active (look at your title bar :) Click on Design, and now you'll see button for "Select Data". In the resulting "Select Data Source" dialog, you click on the "Edit" button (how do you know it's a button? Don't ask me) and then, you get a new dialog called Axis Labels. You can now select the N, S, E, W cells and you're through. Phew :)
Okay, now to make this chart magical - that is, get the max bar to stand out!
Easy, all you do is make another four cells, (you get the idea, you're making another chart that you're going to paste over this one!) where only one has a number other than zero :) You dig?
Easy - you just enter
=C19*(C19=MAX($C$19:$F$19))
And drag this. See the logic? Unfortunately, hackers would prefer the "=" were actually "==", but M$ disagrees :(
"Cell-Value times 1 only-if-cell-value-is-max-of-these-four-cells else 0 "
Simple? The Boolean takes care of the 1 else 0 part.
Now, Just select these four cells and do a CTRL-C and then, click on your existing chart, and do a CTRL-V. Look at im! You have to give someone credit for at least making this one somewhat intuitive :)
Yup, we're not quite there. (If your eyes are open, you know you already have enough info to fix the "Chart Title" :) Go to it! ) That's where this guy helps out :
How do you get the red to dominate? Easy (if you know how :) - like with all things M$) :
Click on one of the bars, then right click and choose "Change Series Chart Type" :
Then, all you have to do is check the "Secondary Axis" checkbox for Series 2. And you're done!
