Right? You can make it more compact. But most importantly, take a look at the numbers. They vary a lot. Some are in hundreds and some are just a few. By putting the depths on top, we kind of break up this chart so it looks like we're dealing with multiple charts, and it helps avoid confusion. So, let's see how we could apply this to corporate context. So here we have the same numbers, just the context is different. Let's assume we're a manufacturing.
Company that's sourcing parts from China and the United States. We want to create a similar chart. Let me just push this to the side. First thing I'm going to do is highlight the area, go to Insert, and insert a stacked bar chart, the 100% stacked bar. Okay, so first thing I noticed is that this is the other way round. I want "Battery Cells" to be on top. So, select the axis, right-mouse click, Format Axis, and select "Categories in reverse order". This is going to switch things up. Let's.
Remove what we don't need. I'm going to delete the numbers there, I'm going to delete the grid lines. Let's bring up the legend and place it right here. Okay, so let's adjust the colors. This one for China, this was a red color, so let's go to Fill Options. I'm going to go with this one. For US, this was a gray color, let's go with this one. Now, let's add data labels to these. So select one of these series, right-mouse click, Add Data Labels. Do the same for the other one. We.
Want these labels to be right aligned. So let's select the labels and go to Label Options. For label position, select "Inside End". We're going to put these in bold. I'm going to do Ctrl + B. For these, instead of "Inside End", we want them "Inside Base". Ctrl + B for bold. And let's go and make them white. So far so good. Now let's add in our titles there. First thing I'm going to do is to remove the current axis. I'm just going to press Delete. Now, let's go ahead and add in our.
That's because when you do that, the text box is a part of your chart, so you don't have to group it together, you can move this chart and the text box comes with it. Now we're going to replicate this. So just select a text box, hold down Ctrl, and drag. And let's just repeat, hold down Ctrl, drag, and let's do that two more times. Okay, so now that we have this, we just have to correct the cell referencing. So for this one, we're going to reference cell A6,.
This one is going to be A7, let's expand this, this one A8, right? It's fun working with Excel, this one is A9. Okay, that's it. All we need is the title. I'm just going to connect it to this right here and our chart is done. Now, because we've connected the titles to the cells, if they happen to change, they're automatically reflected in the chart as well. Next chart is this one. It's typically referred to as a dumbbell chart or a connected dot plot, and.
It's great for cases when you want to emphasize change or compare values between two different periods. Here we have men's life expectancy at the average age of retirement in the 1970s as compared to 2020. Now something we have to keep in mind if we're planting this in Excel is that change can also go the other way, right? So we could be worse off now than we were before like we have here. So something we have to keep in mind, whatever technique we use should work in this case as.
Well. So let's say you work in the HR department and you've collected employee engagement scores for these different departments. Optimally your score has improved this year as compared to the previous year, but unfortunately things could go the other way as well. We want to use a dumbbell chart to plot this. To make sure we can handle all types of situations, I'm going to use a scatter plot. A scatter plot needs numbers for both the x-axis as well as the y-axis. Now in the y-axis,.
I have my category labels. These are not numbers, so I am going to translate them into numbers, basically, I'm just going to get their position as numbers. Just holding down Ctrl, dragging this down, and I have my indexes here. Now, let's just go somewhere empty, don't be on this data set because Excel is going to come up with a crazy chart, just go somewhere empty, go to Insert, and insert a scatter plot. Now, we have an empty canvas, right-mouse click on it, select Data,.
Posts Related:
- 8600 Corel Draw Templates Free Download CDR Files Sheri Sk Office
- SQL Server Integration Services (SSIS) Part 3 - Basic Transformations
- Mega Backgrounds Bundle Download In JPG PNG Files English Photoshop Tutorial
Add, Series Name. First one I'm going to add is the 2023 engagement scores, select the title. X
Values, these are my percentages, Y values, well optimally they would be my departments, but because a scatter plot needs numbers, I'm going to go with their position. And okay, let's add our second series, that's the 2024 series, X values are my percentages and Y values are my positions, and OK, OK. Next let's add our connecting lines. Well, that is the difference.Between my before and after. So let's calculate that here. This is simply this minus this one, and let's send this down. Okay, so how do we add these in here? Well, I'm not going to add it as a separate series, instead, I'm going to select my after series here and activate the error bars for it. So go to the plus here and place a check mark for Error Bars. I'll delete the vertical ones, so select them, press Delete. Now, select the horizontal ones, right-mouse click and format.
Them. For the direction, I want them to go the other way, so we're going to go with minus. I don't want a cap, that's that little line here, we don't need that. For the Error Amount, we're going to go with Custom and specify the value ourselves. And you've probably guessed what that value is. For the negative error bars, we are going to use our difference, and OK, and we get our dots connected with one another. Now, let's take care of our labels here. So this is where we obviously.
Don't want to see these numbers, right? We want to see these. So what I'm going to do is add a series that plots a dot at every point along this axis. That series has a value of zero for the x-axis, right? So I'm just going to put zeros everywhere here. This is going to be for my labels. So we're going to add a new series, Select Data, Add, Series Name: labels. The X values, we're going to plot everything right here along this line, our Y values are our Y position. Okay, this is.
Exactly where we want to have these labels. Click on OK and OK, select them, right-mouse click and Add Data Labels. Select the data labels, go and format them, place them on the left-hand side. For Label Options, we're going to go with "Value From Cells." What we want to see here are these values, and OK, and uncheck the Y value here. Now it looks a bit crowded, so let me just select my vertical axis, the original one that we had, and press Delete to remove it. Okay, so that's how we.
Can get our labels in there. Now, we don't really need these, we could change the marker style if we want, but I just want to hide them. So right-mouse click and let's get rid of the fill, and in case they have an outline, let's get rid of the outline as well. Okay, so this is our connected dot plot. We can improve on it by making these lines a lot lighter, and we could make these thicker as well. To do that, select them, right-mouse click, Format Data Series, go to Fill Options..
Under Marker for Marker Options, Built-in, you can make them bigger if you need to. For Fill, we can adjust the color, so I'm going to go with solid fill and go with a light orange, and we can have the dark orange for the after. Okay, let's just quickly adjust the size of these as well. Format them, under Fill Options for Built-in, I'm just going to increase to the same size. And here we have our before and after. You also have the opportunity to change the formatting of the error.
Lines, so we don't have to go with the default, we can go and make it into a nice gray color and make it thicker, so it looks like a bar, right? All we need to do now is add a proper title to our chart. Let's bring down the plot area and type in the title. Now, to make it clear what is what, I'm going to format "2023" to be the same color as my 2023 dots here, so let's make it bold, and "2024" should be in this darker orange and make it bold. Okay, so that's our neat dumbbell chart in Excel..
Now, it's one thing to create your charts in Excel, but it's a whole different story when you need to create charts or present charts in PowerPoint for your business reports because it can be a real challenge to make them look just right. So if you're one of those people who spends over 3 hours per week in PowerPoint, then Think-Cell might be just what you need, because this add-in can help you reduce the time that you spend on your slides by up to 70%. And.
Think-Cell is also well known in the business world. In fact, 90% of Fortune 500 companies
Trust Think-Cell with their presentations. With Think-Cell, it's easy to build auto-updating charts in PowerPoint. You get to link your Excel spreadsheets straight to PowerPoint and then choose which slides you want to update manually or automatically. You can manage all slide elements from one convenient menu, which makes it super easy to review and, most importantly,.To deliver mistake-free presentations. Now another benefit you get is that you can directly improve on your charts in PowerPoint by just clicking or dragging and dropping changes. This way, you get to add in more information to your chart so you make it easy for your audience to understand the data that you're presenting. If you were doing this manually, it would take up a big chunk of your time. With Think-Cell, it's just a matter of clicking here and there. And.
Here's the best part: because Think-Cell is sponsoring this video, you get to try their add-in completely free for 30 days. Just click on the link in the description and start taking your presentations from good to great risk-free. Now let's continue with our video. The next visual is this one right here. It's not technically a chart. Now at first glance, I thought it's a heat map, but it's not a heat map either. It's just color-coded based on these.
Different categories. So what we have here is the estimated frequency of temperatures exceeding 50 centigrade. "Almost never" is really dark gray, then "Extremely rare," "Rare," and "Common," and we have these different countries. So I tried to apply this to corporate context, and here's an idea I came up with. We have the same data, just the context is different. We're looking at the estimated frequency of complaints directed at IT help desk between these different.
Departments and these different years. Now I'm not happy with this approach. One, it's not dynamic. If you take a look at what's inside the cells, if you take a look at the formula bar, you can see our values are right here. Then I've used conditional formatting to conditionally format each of these based on the cell's value. I've also activated white borders for this, but it's not as compact and neat as the version that we saw from The Economist. So I really wanted to.
Replicate that, and that's what we're going to do on this side here. And we're going to use a method that became recently available in Excel. Plus, take a look at this. Your data is probably going to come in in this format, right? Where you're going to have separate columns for department, year, and complaints. So I formatted this data as an official Excel table so that we can get our visual to be fully dynamic. If new data is added, that Economist type of visual is going.
To update automatically. These are my different categories. For each of these, I want to have a separate color and shape. Now it's up to you where you want to create these. I've created these shapes in PowerPoint just because I like PowerPoint. I'm going to go to Insert, Shape and draw this out. So a shape that looks similar to this one. And then you can adjust the shape fill. I'll use the eyedropper and select this. Shape outline is going to be white. Once.
You have the shape, you can right mouse click and save it as a picture. So I've already done that for these four different categories. Next, I'm going to go to Excel, select these cells, go to Insert, Illustrations, Pictures, Place in Cell, This Device, these are my different shapes. Hold down Ctrl, select all of them, and click on Insert. Okay, so now that I have them here, I'm going to bring them and include them as a part of my table. So that if a frequency is rare,.
I want to see the picture. Okay, so let's call this "Category." You can use the lookup function of your choice. Mine is XLOOKUP. I'm looking up this value from this range. Let's fix the referencing using F4. And what we want to return, so the return array is going to be this range. I'm going to fix it using F4. Close the bracket, press Enter. And because I'm using a table, my formula automatically is copied down. Right?.
So now I have the right shape associated with each complaint frequency. Okay, so next, let's go ahead and create our visual. I'll start right here. First, I want to have the departments. So I'm going to use the UNIQUE function and let's just reference the Department column, close bracket, Enter. Up here, I want to have the unique list of the years. So again, we're going to use the UNIQUE function and reference the Year column. And when I press Enter, the years spill into the rows,.
But I want them in the columns. So I'm going to put this inside the TRANSPOSE function. Next up, we're going to bring in the shapes. So again, go with the lookup function of your choice. I'll go with XLOOKUP. My lookup value is now the combination of Department and Year. So I'm going to select Department, but because I'm working with spilled ranges, I can select the entire range. Use an ampersand to connect it with the Year column, right? So notice I get the hash icon here,.
Which means I'm referencing a spilled range. My lookup array is the combination of Department and Year. Right? I have to follow the same order. So let's select Department, ampersand, Year. What do I want returned? The Category. Close the bracket. And the beauty of this is it's going to spill down and across, right? That's my visual right there. All that's missing is a title. So let's bring it over here. I can add in the legend by copying these, pasting them here, and then notice this.
Icon. I get to pluck these images out of the cells. So I'm going to select them, let's go to more size options, you can uncheck "Lock aspect ratio," and then I'll just go with 0.1. Okay, so then you can place them wherever you need to and add in the right category name. So I'll just fast forward to where I've done this, and this here is our beautiful visual. Now the beauty of this is also the fact that it's dynamic. So let's say we have next year's data that's added to the table,.
I'm just going to paste it in. Notice the formulas are automatically copied down here. Unfortunately, IT is also not having with the IT Help Desk, but what happens to my visual? It's automatically updated. How cool is that? That's it, three charts from The Economist replicated in Excel. If you want to get the template, link to it is below. I'm also working on a new Business Charts course where we're going to be creating charts that look like McKinsey charts.
