Hi everybody. I want to show you one of the simplest and easiest ways to represent your data in Microsoft Excel. Now there is a practice file for this. It's called bar charts. Remember you can go into the description, click on the link to find the practice files that go with this course. How do you create a bar chart and what is it? Well, first of all, bar charts are also sometimes called column charts. They're useful for showing data changes over a period of time or for illustrating comparisons among items. In column charts, categories are typically organized along the horizontal axes and values are going to be along the vertical axes..
So how do I create one of these bar or column charts? First of all, you need data, which we have. We need to decide what data we're charting. It's important to remember charts. You generally want to select the simplest information. This means titles like we see up in cell A1, which is a merged cell. You don't want that because it's extra. And then down in row eight, I have totals. And if I were to chart these with my data, it could skew my chart results. So instead I'm going to go to the simplest pieces of data, which will be cells A3 down to G7 and highlight them..
This tells Excel what I want charted. Then to select a bar chart, I'm going to go to the insert ribbon tab and come to the middle of the ribbon to the charts group. Here you'll see on the top row that there is a column or bar charts button that you can click on and it will show you the different subtypes that you can select. There are 2D, 3D, and even 3D bar charts. The column charts will be at the top, the bar charts will be along the side. Now to pick a specific chart, you hover over it and it will live preview what your data will look like if it is applied to this particular sub chart type. Once you pick the type that you want, you're going to click.
On it and you have a chart. This type of chart where it resides on top of the cells is called an embedded chart. Now charts are like graphics, so this means that if you come to the sides of them while you see their sizing corners turned on, you can actually drag them in and out to size them. I recommend doing it by the corners. Additionally, if you want to move your chart so it's not taking up too much space or over the top of cells, you can come to any side of the chart. You'll notice that your mouse pointer will look like an arrow pointing up, down, left, and right. You can just drag the chart to where you want it to go, and that way it.
Now, area charts are similar to line charts, but the difference is that the area below the line is filled in with color in an area chart. And both area charts and line charts are great for showing trends, but if you're needing to print, it's better usually to do a line chart. Now I have a practice file for this available. Feel free to use it. It's called area charts. First step is to select my data. Always remember you want to select again what you're charting. And in this case, I don't need the title or row eight to be part of that. So I'm going to highlight cells A3 through G7. Then I'm going to go up to my insert ribbon tab and come to the charts group..
Now it's interesting about area charts. is there really isn't a button representing them here. So if you come to the bottom right hand corner of the charts group, you're going to see this very small rectangle with an arrow pointing down and to the right. It doesn't look like a button, but it is. When you click on it, it's going to take you into the insert chart box. You'll want to go to the all charts button so that you can see again, all the different types of charts you can create in Microsoft Excel. The left hand side are the main categories. I'm going to select area. And along the top, I'll see the different subcategories..
I can click on any of these subcategories to see my data represented in the box below. And if I hover, it'll zoom out on it, or I should say zoom in and make it easier for me to see. Once I find an area chart that I like, I can click on it. and select it and then click on OK at the bottom of the box. And you'll see that the chart actually gets created for me. Again, I can size it. Now briefly, if you want to update this chart and change the way it looks, remember that in your chart, if you go to the top right or left corners, you'll see three buttons. The green plus sign is called the chart elements button. It allows you to quickly do things, for example, like add data.
Labels to your chart or even add a data table underneath of it. The green paintbrush that you see when you right click is going to be the chart styles button. It's all about updating either the style of the chart, as you can see here. Or, if you go to the second tab at the top, you can update the color scheme of the chart. Just make sure that the formatting you pick for your chart does not, again, decrease its visibility. You want to make sure that this chart is very easy for people to see and understand. Same thing goes for colors..
Now the third button that you'll see in the top right corner is the filter. I love this one because it allows you to filter what's showing in your chart. When you filter, you're not deleting. What you do is come in and select an item you want to turn off and uncheck it. Then come to the bottom right hand corner and click on apply. Let me show you one more time what I'm talking about here. When you click on apply, it just removes that particular element from the chart. To return it, you're going to go back up and re click and say apply again, and it will come back. But remember, area charts are a great way to show trems..
Posts Related:
- How To Design Responsive Navbar Using HTML, CSS, & JavaScript - Part 1 BlogSpot
- Create an animated GIF in Powerpoint. (New feature Office 365) #Stayhome learn #withme
- Azure Basics Tutorial Office
Hello everyone, let's talk pie and donut charts. There is a practice file for this called pie and donut charts.
Feel free to use it to follow along. Now pie charts are really important. They are a great way to visually represent data as a fraction part of a whole. Hence a pie with pie slices. They're also probably the simplest type of chart you can create in Excel. Now in my practice file to get started, I'm going to highlight what I want to chart. It's going to be my headings. So A2 down to D3 and my top customer rep. So the cells I have selected are A2 to D3..Then I'm going to go up to my insert ribbon tab. I'll click on the charts button, you'll see that there are 2D charts, 3D charts, and donut charts. You can hover over any of them to see the chart get created. Once you've selected the type of chart you want, I'm doing a 3D pie chart, you click and your chart's created. Now one interesting thing about pie charts is because they're so simple, you can actually adjust their range and adjust what information's being charted. To do this, I'm going to make sure that my chart is clicked on, and I'm going to come into Excel and go to the right hand corner of one of the.
Ranges of cells that's being charted. And I'm going to left drag. You'll see that my mouse pointer turns into a double headed arrow. When I do this, it actually adjusts the range of cells that are being charted. And now, for example, I can chart Jose instead. Now, for a donut chart, I'm going to click in a normal cell outside, again, my chart. I'm going to highlight all my information, A2 down to D6, go back up to the insert ribbon tab, come to the charts group, and find the donut chart subtype. Click on it, and now you'll see that I have a donut chart. Donut charts do a little bit better job, again, charting multiple.
Rows of data, unlike a pie chart. So they are able to represent more complicated data. If you ever want to take a pie chart or a donut chart and edit them, remember, you can use the buttons in the top right hand corner of your chart, or on a PC, you can go up to the contextual ribbons that come with charts called chart design and format. These two ribbons have great tools like different chart styles and also the ability to update the layouts of your charts using the different tools that you'll see up in the ribbon. It's a great way to quickly, again, update the layout of the chart.
Without changing the chart type. So try a pie chart and a donut chart. They're really simple. Hello, let's talk combo charts. There is a practice file for this called combo charts, so please use it to follow along. Now a combo chart is a combination of two charts, so it could be a line graph and a column chart, or an array chart and a line chart, but you can make a combo chart with a single data set or two data sets that share a common string field. We're going to use this again, Excel spreadsheet to compare sales totals to commission fees..
The first step is the same as all the other charts we've made. We need to highlight the appropriate cells that are being charted. Again, we're not going to highlight titles or totals. I'm going to come in and start by clicking in cell A3 and highlight A3 over to C8. Then I'm going to go up to my insert ribbon tab. Come to the charts group and look for the combo button. It has a column bar chart with, again, a line chart on top. When I click, you'll see that there are three classic combo charts that you can pick from. We're going to do the middle one, which is going to be the clustered.
Column line on a secondary axis. I'll show you why. When I click on it, you'll see that again, I have the column chart behind with the line chart on top. I'm going to make it a little bit bigger. Now what can you do to edit this chart? Well, if you right click and come into the right click menu, you have a change chart type button. I don't so much want to change the chart type as let you see the options that you have here. When I click on it, it's going to show me my current chart. Again, you'll see down below that it's showing me what charts.
Are being charted on which one. So right now my sales total is on my clustered column and my commission fee is on the line. If I'd like to switch that, notice I can come in and I can change that myself by picking a different chart type and again, selecting a line for the top and you'll see it switches the information. Also here, I can decide if I have a secondary axes or if I want them both charted on the same axes. Now that I've made that change, I can click on okay. Don't forget, you can further update the way the chart looks by going up to the contextual ribbon called Chart Design. Come over to the Quick Layout button to try out different, again,.
Quick layouts with your chart. that may help you better represent the data that you're trying to chart. Once you've selected your chart type, click away from it and you now have a combo chart. Welcome back. We want to explore another type of chart called a waterfall chart. Now these charts are really great at showing a running total as values are added or subtracted. So they can be really helpful for understanding how an initial value like say net income is affected by a series of positive and negative values. The columns are color coded so you can tell positives from negatives very quickly. In our spreadsheet called Create Waterfall Charts, which by the way is.
One of the practice files you can use to follow along and make a waterfall chart of your own, we're going to be comparing income and expenses for the
Second quarter in an organization. I'm going to start by highlighting my information. It sells starting in A3 down to B13. Then I'll go up to the Insert Ribbon tab, come down to the Charts group, look for the Waterfall subtype. Now it's actually combined with the Funnel, Stock, Surface, and Radar charts. So you might have to look for it a little bit. But the Waterfall chart will be the very top option when you click on the main button. Once you select it, you'll see the beginnings of a waterfall chart..I like to make these charts bigger because there's a lot going on inside of them. So I'm going to make it bigger. Now you'll see that one set of bars will be blue that represents positive values. Then my negative values are a different color. Now to be more accurate, I would like to have these actually be charted as negative because right now they're just compared to the values. To do this, I'm going to go to my first negative point in my chart and I'm going to click on it. When I do this, it selects all the data points in that series, but I'm going to click again to get that data point highlighted by itself. You'll notice the other data points in the chart or the data.
Fields don't highlight anymore. I'm then going to right click in the right click menu, second from the bottom, you'll see set as total. When I do this, it gives a more accurate portrayal of where this negative value is in comparison to the positive values. I'm also going to do the same thing with the additional negative value. I'm going to again, click on it. So it's separately selected, right? Click again and say, set as total. And now I'm getting a more accurate portrayal of where the positives and negatives in my waterfall are happening..
And again, once I deselect the chart, I'll see the colors become highlighted again. These are very powerful again, for helping you see how an initial value compares to positives and negatives, and they are used all over the place. So try making one of your own. Hey, everybody. We're ready to create what's called a football field chart. Now, this is not a chart for keeping track of football scores. Football field charts are floating bar charts or stock charts from Excel. They put several valuation analyses side by side to provide you with a full context of your company's value. They use a variety of methodologies and assumptions..
So a typical football field matrix will include a company's value based on, for example, DCF valuation or LBO analysis. Now we're going to use a stock chart for our example today, and there is a practice file for this that I suggest you use to try this out. It's called create football field charts. So the first step is to select our data and unlike the other charts we've created where we've selected column and row labels, we'll do that after the fact. So I'm going to come into my spreadsheet and highlight cells B3 over to E7. Then I'll go up to my insert ribbon tab. I'll come to the bottom right hand corner of the charts group to get into.
That insert chart box and go to the second tab at the top called all charts. This is where I'll find the different categories of charts. And of course, we want a stock chart. We're going to pick the second one in that's called open, high, low, closed. And then I'll click on okay. I'm going to size this chart out just a little bit because this is the beginning of our football field chart, and when you see it sized, it does start looking more like a football field, hence the name. And we want to continue to make some more changes. We want to get the correct labels down here on the bottom of the chart..
So we're going to go ahead and just right click on any of the data bars. This will include The entire series, because when you click on one, you get all will come down to select data. Now, from here, I'm going to go to edit because what I want to do is get the correct category axes labels. These are located in cells a three through a seven. And these are of course, the different various valuation methods that we're going to use. I'm going to click on. Okay. Now, once I click on OK again, we'll notice that they're added to the category axes. I don't need this legend anymore because it's not providing me with anything,.
So I'll click on it and hit delete. And then the grid lines, I don't need either. You could keep them, but again, they're just kind of making a extra space that we don't need. So I'll select them and hit delete as well. So now our chart's getting really clean and ready for us to add more customizations to it. All right. The next thing I want to do is add data labels to my chart to help show those highs and lows that we're trying to keep track of. So I'm going to click on any of the data bars to select the data series. And then if I go up to the top middle of the border of any of the data bars and click, I'll see that I get a series..
Small middle point. This is of course one of the data values that I can track in the chart or a data label. Now to format this specific data label, I'm going to come over to the top right hand corner of my chart. If it's too big, this button will appear in the top left corner and I'm going to click and come down to data labels. Now you'll see as I hover, it puts them on the top. Top right side, and I want to again, control and put them in the top middle. So I'm gonna click on the arrow at the side and actually come in and say more options. My format data labels task pane opens up over on the right..
This will allow me to come down and place those data labels above the current data bars. I also want to format these as a currency. So I'm going to go to number. And of course, this will allow me to come in and pick currency for their format with two decimal places, so that they're formatted correctly to make it easier for people to understand what they are. I want to do the same thing with the low point. So I'm going to close that Format Data Labels task pane, and come over and click on the button. bottom middle border of one of those again, data bars, and you get those same.
Little markers representing the low point. Then if I come over to the green plus sign for the chart elements button, again, I can go down to data labels and again, you'll see that it's placing the data labels kind of off on the bottom, right? I'm going to go ahead and come to the arrow on the side. Go to more options, just like I did previously, and this will give me the ability to open up the format data labels task pane again, and I'm going to select under label position below, then again, I'm going to come under number and format them as a currency. Then I can close the format data labels box..
Okay, as we continue with our journey, let's format the data series. I'm going to come into my chart and click on any of the data bars. This will of course select them. If I right click again, I can go down to format data bars. This will open up the format data bars box. Let's add a fill color to the data bars. Remember that's the inside, a solid fill and a color that's easy on the eyes. Something that won't be too dark. Additionally, I don't want to border around the data bars. So I'm also going to come to a border and say, no line. This again, simplifies things. I want to go to the chart title and I'm going to go ahead and add a chart title evaluation summary..
Now I'm going to type it in my formula bar. And as soon as I hit enter, it gets added to the chart. Additionally, I want to make sure that in my chart area, there's no fill for the chart area. And also I want to make sure that. If I do want to fill, I could do a solid fill that is white, right? So that's another thing that I can do is come in and say just a white fill. Additionally, if I don't want a border around the chart, I can come in, go down to border and say no line. When I deselect the chart, we'll see how those two things have come into play as well..
Okay, next we want to format our axes. So I'm going to come over to my vertical axes and click. You'll see that after I click it will select the axes and then if I right click, I can go down and save. Format axes. And I'd like to set the minimum to 10 to just kind of help spread things out. And you'll see that as soon as I do this, it updates in my chart. Additionally, if I go back and click on the axes again, I'm going to add a border just to separate the axes from the actual valuation. To do that, I'm going to come in again, click on my axes.
To make sure it's selected. So I have the format axes box selected, then select the paint count at the top. Come down to line and say solid line to increase its width. I'll come right here and make it maybe two points wide. And then also for the color, I'm going to click on the color here and I'm going to pick a gray color. Just let it set apart. When I click away from it, we can see that line is now in the chart. Okay. I want to do the same thing with my category or X axis as I did to my Y axis..
So I'm going to come in, click on any of the items in my X or category axis. Select it. If my format axes task pane doesn't open up, I can right click in the axes and select format axes. Then I'm going to come up and make sure I click on the paint can at the top. This will allow me to go in and again, I want to make sure that I have a line. I'm going to pick the same color gray that I did previously. Make it two points. And it's width. And then I'll go ahead and click away from it so that now I have, again, two borders that are similar. It looks like one of them is a little bit different, but you want.
To make them similar if possible. So they kind of look like a line that's just continuous as far as those two axes go. Okay. We're just about done with our chart. I want to add to the title up here. Beyond valuation summary, I'm going to click up in the title and also add to my title equity value per share. Okay. In dollars. And I'll just add this to the end of the current existing title that's there. Now to help really represent what the target reason for this chart is, for example, are you trying to represent a current share in this particular football field chart or a target price or a share price?.
So we're going to create a line to show that. So I'm going to come over to the side of my chart after I deselect it, go up to my insert ribbon tab Come into shapes and I'm going to pick a line. I'm just going to draw this line out with my, again, mouse. Then what I'm going to do is come in and format this line. I can use the format shapes task pane. If it opens up, if this task pane doesn't open, right click on your line and go down to format shape. I'd like to make this line dashed. So coming into again, the paint can side, let's go ahead and change the color..
First. We want a solid line. I'm going to make it an orange color so it sticks out. I'm also going to increase its width. This will make it a little bit easier for people to see. We'll make it two points wide. And then of course, to change the style, you'll notice here that you can click on different ones. I want to do a dashed line. And to make sure it's straight, we can just use the shift key and kind of pull it out. Then I'll bring it into my chart and put it where I want it to go right here at the 40 point mark or the 40 share price mark. And then I can drag it out across the chart..
All right. And to make it straight, what I suggest is to hold down your shift key as you again drag the line out. If you ever want to change the way the line looks, you can come back to the format shape menu. Here's the thing to remember about football charts, right? Their goal is to be a great way to summarize and visualize all of your valuation analysis. That's what these charts are for, and they do take a little bit of work, but once you understand how they work and what they can represent, they're very powerful. Welcome back. I want to show you this. Smallest type of chart you can create in Microsoft Excel called a spark line..
These small charts fit directly inside cells in a sheet. Now, because they're condensed size, sparklines help you to see patterns in large sets of data in a very concise and visual way, they're great for showing trends in a series of values like. Seasonal increases and decreases or economic cycles and they help to highlight maximum and minimum values. A sparkline has its greatest effect when it's positioned right near the data that it represents and they're very easy to make. So I have a practice file for this called create sparklines. Feel free to open it up to use it and follow along..
I'm going to start by clicking in the cell where I want my sparkline to go. Cell F4. Then we're going to go back up to the insert ribbon tab and come to the middle right of the ribbon to the sparklines group. Now there are three types of sparklines, line, column, and wind loss. Now let me clue you in with wind loss. It works best with highs and lows, so positive and negative values. We're going to start with a line type of sparkline and select it. Now it's going to ask me what the range of cells is that has my data..
I'm now going to highlight cells A4 over to E4 and then I'll click on OK. And voila, you have your sparkline. Now, when you click on the sparkline, you will see that there is a contextual ribbon that you can use to edit your sparkline. The right hand side has sparkline styles. These basically change the color, but on the far left, you can actually edit the range of data that you're editing or creating your sparkline from, and you can change your line sparkline to a column sparkline or a win loss sparkline. In addition, you can also highlight different parts points in your sparkline using the show group, you can edit to show a high point in a different color..
And for example, a low point in a different color and the sparkline editing contextual ribbon will allow you to do this. Let's also try out a high low sparkline. For this, I'm going to go click in cell F8. This is where I want my sparkline to go. I'll return up to the insert ribbon tab again. Go over to the right hand side to the Sparklines group and select Win Loss. Remember, this one is for highs and lows or positives and negatives. I'm going to highlight my data, A8 over to F8, and then click on OK. And here you're seeing an example of a win loss. Again, even with the win loss, I can highlight specific.
Points within the Sparkline. So, for example, if I want to make one of the bars turn a different color, I can do that by coming to the marker color and picking the specific point I want to edit. Now the only downside to sparklines is when it comes to deleting them. You cannot click on the cell where the sparkline is located and hit delete. It will not go away. So to clear a sparkline, if you click on the sparkline and go up to the sparkline contextual ribbon, The very last button in the right hand corner of the sparkline ribbon is a clear button. It gives you options to clear selected sparklines, which is what I want to do..
And this is how, if you click on a cell and you go in and select clear selected sparkline, it will delete the sparkline out of the cell. These again, are a very small, simple way to reveal patterns in data. And I love to try them on rows. Hi, I want to talk about one of my favorites. Favorite features in Microsoft Excel. It's called a pivot table. Now the value of a pivot table is that you can use summary functions in value fields to combine values from underlying source data, and it allows you to take a large data set and analyze specific columns out of that data set without having to worry.
About having to reformat, hide columns and rows and messing up your source data. There is a practice file for this called create pivot tables. Please feel free to use it to follow along. So here I have my spreadsheet. called create pivot tables open. How can I make sure that when I go to make my pivot table, this will work? First of all, let's look at row one. Super important. I have column headers. These are especially critical when you're creating a pivot table. Second of all, I don't have any entire blank rows. Now blank cells are fine, but entire blank rows will stop your analyses at.
The point where you hit the blank row. We're ready to create a pivot table. Now, remember the goal of the Pivot table is to allow me to analyze parts of my data. Not all of it. So say for example, your supervisor comes to you and says, Hey, with this data, I would like to know the total sales by commission for each destination. We'll think if you had to do that on your own, it would require tons of functions and also sorting and filtering. So a pivot table can do it for me in just a few clicks. The first step is to click anywhere inside the source data, and then go up to the Insert ribbon tab..
Now if you've never made a pivot table before, I highly recommend checking out Recommended Pivot Tables. This box will actually take your data. On the left hand side, you'll see examples of pivot tables you could build from your data. And in the middle, you can actually preview them. Remember, my idea was to see, again, the sum of tickets sold by destination. You can see that right here, I already have that separated by commission. And when I click on OK, this recommended pivot table gets put on its own sheet right here. My source data is back on my original sheet, totally unimpacted by the.
