Okay, I'm done. Here's the column chart
you requested. It has the sales managers with the items sold for this week. Oh cool,
thanks. But, wait, where's the target line? Oh yeah, I forgot about that. And, the target
quantity for this week was 280, right? Okay, so one second, we're
going to get it for you. Let me just add it in, okay? Yeah, okay, yeah! That's it, there you go. You didn't just do
that? Is this how you've been creating a report for the past weeks? Yeah, it's a bit of a pain,
but I've gotten good at estimating the location, so don't worry. The ruler helps. Okay, so you
picked the wrong thing to be good at. You can make that line dynamic and add it to your chart.
Step aside, let me show you. Okay, so there are different ways of doing this, but I'm just going
to show you the easiest way. First thing you're going to want to do is to transform this data into
a table, so the moment we have more sales managers and more data, the chart is going to update
automatically. So, let's just go somewhere inside, press Ctrl + T. Table has headers. I don't like this
table design. Let's go and remove that design, okay? So, so, so far so good. Now, we are going to
add a new column for Target. This is going to equal this. Press F4 to fix the referencing, and
now we have our series added. Okay, so your line wasn't really straight, but your estimate
was pretty good. Now, we obviously don't want these to be columns, so we're going to right-mouse
click, change series chart type for Target in the combo section here. Let's switch it to line.
There we go, we have our line. Click on OK, and we have our line dynamic. Okay, I'm going to
remove your other line. The great thing about this is that it's automatic. If this changes to 150, it
updates, if it's 300, it updates. I'll just put it back to 280. Now, actually, we can even improve
this further. Let's press Ctrl + 1 to bring up the settings, go to line options, and let's
add an arrow here. I want to make it thicker, and the ending part should have that too. Now this
looks a lot better. Okay, we could also add the target directly to this. I'll add it to the first
point. So, I'm going to select to add data labels, but I don't want the data labels to show up
everywhere, so I'm going to click again, just to select the first one. Right-mouse click, add data
label, now click, click to only select this one. Press Ctrl + 1 to bring up the settings, or if you
double-click, it's going to come up. Scroll down, show it on the left-hand side. That looks good. I
actually want to make it bold, and let's make it bigger. Okay, we don't need this either. Delete,
and take a look at this beauty. And yeah, if you don't want anyone to see this target series, you
can just hide it or better, let's group it. I'll use the shortcut key, Shift + Alt and the right
arrow key. Now we have this group together, but notice the line disappears. Don't worry,
right-mouse click, select data, hidden and empty cells. We want to show data in hidden rows
and columns, and our line is back. What? So, next time all I have to do is add the new numbers
and everything updates automatically. Yeah, so I know it's hard to tell, but next time you find
yourself doing weird stuff in Excel, just ask. I'm so happy with the automation. You have no
idea. Look at this. I just paste the latest values, update the target, and I'm done. Yeah, so
higher-ups want a bar chart instead. This column chart is getting too wide for them with all our
new sales managers, so please switch to a bar and add that Target line. The meeting is starting in
like five minutes, so be quick. No problem, let me just do it. I'm just quickly, I'm going to insert
a bar chart. So, let's just highlight...where's my insert here? Create a bar, that's it. Right
here, it was this one, right? That's my Target line. Right-mouse click, change series chart
type, Target. I want a line. Oh, that doesn't look right. It's the wrong way around. Your technique
isn't working. Let me just draw it. What, no, no, okay, okay. Um, bar charts are tricky with the
second series. We just have like two minutes left, time for a hack. Just move it. Okay, here's
what we're going to do. Delete this chart. Let's just move this to the side. Now, I'm going
to highlight the cells the chart is sitting on, press Ctrl + C, go to the side here, home,
paste, and paste this as a linked picture, so not picture but a linked picture. This way, it's
a live picture. So, let's just turn this around, and now we are getting a bar chart, but it's
not the way it should look. But no worries, we're going to make the adjustments here.
So, first of all, it should be a lot thinner, right? So, that looks better. We should bring the
bars closer, select them in our original chart, press Ctrl + 1. For Gap Width, let's go with
40%. Now, this is starting to look great, but we want to turn these around. So, let's select
the labels, under alignment for text direction, change it to rotate text to 270. Now, this
looks good. We're also going to rotate this one. I tend to click on this, but we have
to do it here. Select it. Let's go ahead and rotate it to 70. That looks good. The same
for the data labels, rotate to 270. We have our Dynamic bar chart created, and check this out,
if Target changes to 200, this updates, 320, it updates, everything is dynamic, but we have a
bar chart with a Target line. Okay, I'm late, I've got to run. Just save this on the drive, please.
What just happened? I wish I could rewind that.
