Excel Data Analysis Fundamentals: How to Analyze Financial Data Like a Pro Part 6 | CFI Course

In this chapter, we will look at visualizing data with Excel charts. There is a wide selection of charts within Excel available to us, and visualizing data in Excel is quite flexible. Excel charts have three primary steps, selecting data, selecting the chart type, and formatting our chart. After seeing these steps in action, we will work through a small case study to see how we can create a dashboard of Excel charts, creating a few different charts to visualize some insights from financial data. By the end of this chapter, we will have used a few different types of Excel charts, be more familiar with how to create references to data that we want to visualize, some common formatting options, and how we can create our own charts with a bit of creativity. Let's head back to Excel to get started. Let's start by covering some of the foundations of building visuals in Excel. Creating charts consists of three primary steps, selecting the data to visualize, selecting the chart type, and then formatting the chart. Using this small Excel table to the right here, we can create a few different charts to demonstrate some of the options within these areas. So let's start by creating a line chart. And to do this, I'm going to select all the values of the amount column within our table, and then I'm going to navigate to the insert tab on the ribbon, and in the middle here we see this charts area. I'm going to select the line chart icon, and there's a little drop-down menu that we can select from a few different options of different line charts. I'm just going to select the first one here. Now I'm going to drag this into place under our line chart header and resize it so it's a little bit bigger. Now the first thing that jumps out to me is that we have numbers 1 through 16 on our horizontal x-axis, but what I want to display instead are the years. So I can go to the select data button up here on the ribbon, it's in the chart design tab, and when I select data, in this horizontal category axis labels, I can select edit, and then I can select all the values from the year column, and select ok, and ok again, and now we have the years appearing on our x-axis. If I go back to select data, we have some other options here as well. We see that in the legend entries series box on the left-hand side, it just says series 1. If I select edit, if we want to give this series a name, I can select the column header amount. So now we know when we look at this that we're looking at the amount series of our visual, and I can select ok. The last thing I might want to update is by default, this line is a light gray, and it's really difficult to see in our visual. So by selecting the line, just by clicking on the line here, I can right-click and go to format data series, and this brings up a format dialog pane on the right-hand side. I can navigate to the paint can for fill in line, and within this line option, I can select the color, and I can make this be a brighter orange. So now that really stands out. So that's how we can select the data to visualize, select the chart type, and then a few basic formatting options for our charts. Now we're ready to move on to build a few more visuals in Excel. Now that we've started our line chart, we're ready to move on to create two more visuals. The next visual will be a scatter chart. So let's select all the values from the amount and the cost columns in our Excel table. Then insert, and then the scatter chart icon here, and I'll select the first one again. I'm going to scroll down, and let's insert it under the second level header here. In the previous lesson, we looked how we could bring up all the formatting options in the dialog pane to the right-hand side here. We also have the option to select this plus icon to add some chart elements more quickly, like axis titles, for example. So on the x-axis, we can update the title to be amount, and on the y-axis, we can update the axis title to be cost. We can also click on the individual points in our scatter chart, right-click, and format data series. We can go back to fill, and instead of line for the markers this time, under the fill option, we can select a cyan color to make them stand out a little bit more. We selected the data series within the chart, right-clicked to bring up format data series. But now that we have this option open, and I'll expand it a little bit more, when we click on a different chart element, like the x-axis, we now get the format options for this particular aspect of the chart, for the format axis options now. So one thing I might want to do is under axis options, I can go to this last icon for axis options, and then under number at the bottom, instead of two decimal places, maybe I want zero decimal places. And now this gives us a little bit more room on the x-axis of our chart. Let's update the chart title to something more meaningful, like amount versus cost by year. And finally, we can select both the y-axis and bold the values, and the x-axis to bold those values as well. And let's do the same thing for the axis title, so they stand out a little bit more. As we're creating multiple visuals in this page, one option that can help make positioning all of these different charts more efficient can be found under the format tab on the ribbon. In the arrange section, in the align drop-down, I can select snap to grid. And now when I reposition the chart, it aligns to the height and the width of our columns and rows within our spreadsheet. Let's close this format pane down for now, and create our last visual, a clustered column chart. So let's select all of our amount and cost columns, including the column headers, insert, column chart, and clustered column chart. And we can position this next to our scatter chart, like that. Once again, we'll need to add years to the axis. So I can go to select data, horizontal series, and select all the values from our year column. We can also update the color of our columns. And one best practice is that in our line chart, amount was visualized with orange to show the trend in amount over time. So when we have that same amount column in a different visual, it's best to be consistent. So if we select the amount column, if we right click just under the fill option, we can select orange. And then if we select cost, we can do the same thing but select blue this time. We can update the chart title to be amount and cost comparison by year. And we do have a legend down here under the chart. We can see that amount is orange and cost is blue. But one thing that can help make this really stand out is if we select amount within our title and change the font color to match the color of the bars and do the same thing with cost to make that blue. And maybe even bold just the words amount and cost to really stand out. Now when someone just reads the title of our chart, it's really obvious that we're looking amount is the orange and cost is the blue. We've created our three visuals and now we can do just a few more steps to clean them up. I can select our clustered column chart visual and we can remove the border. We can say no line and do the same thing for our other two visuals as well. Just remove the borders from them. On our line chart, we can remove this amount chart title because we actually have a title of the chart in the header above. So we can delete that part of the chart. And we can also update the chart title to be And now we have three visuals that summarize and visualize some aspects of this small data table in Excel and really help bring some of these numbers to life. Throughout the rest of the chapter, we will be using some financial data to build a dashboard in Excel. As we build the visuals to populate the dashboard, we will take a look at some of the different Excel visuals available to us as well as exploring the flexibility that we have in formatting our visuals. We will use a standard visual, a combo visual, where we can use multiple visuals in the same chart, and a custom visual that we will create. Here's a preview of the dashboard that we're going to build together. Across the top here, we have the waterfall chart that visualizes the net earnings of a company. This provides a visual display of the income statement starting with revenue and ending with net income or net earnings. Beneath that, we've got a breakdown of the cash flow statement and the cash balance. We can see the three segments of the cash flow statement, cash from operations, cash from investing, and cash from financing, as well as the cash balance and how the cash balance is impacted by those three line items. In addition to that, we're introducing a new type of chart here called the bullet chart. The bullet chart has to be custom made as there's no automatic tool for it within Excel. It's a great way of showing an actual result relative to a target or multiple threshold levels. Let's get started with creating these three charts in Excel. The first chart that we're going to insert is the net earnings waterfall chart, which graphs the entire income statement. So let's select both the labels and the values that we want to graph. So I'll select our labels and our values. On the ribbon, we go to the insert tab and then under the chart section, we can select the waterfall icon and waterfall chart. We can also get to this by selecting this button in the bottom right hand corner, recommended chart, and going to all charts. And then we can see a list of all the charts that are available to us. I can select waterfall chart here and press okay. Let's move this chart and resize it into the area here so it's a little bit easier to see. For our first step, we need to select all of the totals from the income statement, such as revenue, and set them as totals within the waterfall chart visual. To do that, I can click on the revenue bar, right click, set as total. And we want to do this for gross profit as well and also for net earnings, set as total. These totals are not being added to or taken away from the waterfall effect. They're just their own totals here. So we start with revenue, we deduct the cost of goods sold, and that gets us to our gross profit. Then we start with our gross profit, we deduct all of our expenses, and we arrive at our net earnings. So now that we're done creating our visual, we can start formatting it. Let's select our y-axis here, right click, and format axis. In the axis options here, we can select our display units as thousands. And we can see that the values in our y-axis have updated after we've changed this option. Since we've changed the display units to thousands, we should update our chart header here to indicate that these values are within the thousands units. So we can do dollar sign, thousands there. We can clean up the chart a little bit we can remove these grid lines. I can select them and press delete. We don't need the legend and we also don't need this chart title because the header acts as a chart title for us. Finally, we can remove the border of the visual. I can select no line. And we could select the individual data labels and bold them so they're a little bit easier to read. And now we've got a really nice looking visual illustration of our income statement. To visually represent the cash flow statement and cash balance, we are going to insert a combination chart. The combination of visuals that we will use in our chart are a stacked column chart and a line chart. And we will use the two types of visuals across four different data series. Let's start by selecting all of the data here that we want to visualize. So cash from operations, investing, and financing for the five years as well as the cash balance for the five years. We don't need to show the net change in cash balance so we will select all of the other rows as our data. Now we can go to insert and under charts we can go to all charts, combo, and we can choose what we want each of our data series to be. So series one we're going to select as stacked column, series two stacked column, series three stacked column, and then line for series four. And I'll select okay. Now if I scroll down a bit we can put this visual in its place. So what we've done in our visual so far is that we're stacking the three components of the cash flow statement, operations, investing, and financing, and our overall cash balance is a line. So now that we've built our visual we can start formatting it. And the first thing that we want to do is to click on the axis here. We can see that this is appearing right in the middle of our stacked column chart. And we may want it to appear at the bottom out of the way. So I can right-click, format axis, and then under labels where it says next to axis let's put this low. And now it's underneath. We can go to our y-axis, right-click, format axis, and then under display units we can go to thousands once again. This brings up this thousands label on the y-axis but we don't need that. We can delete it because once again we're going to update the header to indicate that we're looking at thousands units on our y-axis. If we select our visual, go to chart design, and select data, we can also start naming the elements of our series as well. So for series one we can call this cash from operations. Series two we can call this cash from investing. And then the same for series three and four as well. Cash from financing. And finally our cash balance. We can also update the labels of our horizontal axis to be the five years along the top here. Okay. And now when I navigate back to our visual everything is labeled and a little bit more clear. Let's start updating some of the colors. So for the line in our combination chart, for the color, let's set it to be a lighter gold. And we can also change it to be a dashed line instead of a solid line as well to help it stand out a little bit. Cash from operations we can change the color of that to be blue. Cash from investing we can choose a bolder orange. And cash from financing we can leave as the cyan color. Let's get rid of the chart title because we have the title of the chart in the header up above. And we can get rid of the grid lines as well. So let's select the grid lines and delete. One thing we might want to do is make the bars for each of the years a little bit closer together. So if I select an individual series in our data, series options, gap width, let's move this down to 75%. And now the bars appear bigger and there's less space between them as well. Finally, let's delete the border. We can go with no line. For our last step, we can take our legend and we can position that at the top of the visual. And now we have a nice visual representation of the cash flow statement and cumulative cash balance. Before we start to build the bullet chart, let's take a look at the completed visual so we have an idea of what we are aiming to achieve. The first step for creating bullet charts are these tables over here to the right. There's a table for each one of the bullet charts. Business 1, business 2, and business 3. Within each business, we need to do a few things. First, we need to set the levels for the bullet chart. This will be the first level of the lighter color gold, here. Then the second level, which is a medium orange, and the third level, which is the darker orange. And these are the thresholds that we're going to measure our actual results against. Then we need some information about the actual result. In business 1's case, 74%. And this is what was actually achieved. This is the value that we want to compare to these threshold levels. Finally, we have some additional calculations within the tables, which we will explain in a little bit more detail a bit later. These calculations are used to fill in the rest of the chart, so we need to build this table for each bullet chart. The bullet charts themselves are made of stacked bar charts. And you can see how this background here is actually like a stacked bar chart, with levels 1, 2, and 3. Then in the middle, there's a stacked bar chart with blue at the bottom level, and then additional levels here. And these levels are the same as the stacked bar chart above and below it. So these threshold levels blend in and you don't really see them. So that's the concept of how we build a bullet chart visual by using a combination of stacked bar charts. Now that we've seen the completed product, let's take a look at how we can build it together. We will start creating our bullet charts with the tables already built, so we can focus on creating the visuals. After we see how to build the bullet chart, we will then review how to create the calculation tables. But to start, let's select all of the data that we need for our visuals. So I'll start with business 1 and just select all of this data here. Then I'll go to insert, and we'll go to a stacked bar chart. Now initially, it's not putting the columns and the rows in the right order. So under chart design, once we're selected onto our visual, I can select switch row and column. And it should look like this. So we want just three bars. Let's reposition our visual under the header. Now I've done that, we can start to see how our visual is going to work. And what we want is for these outside bars to be the colors that we want for levels 1, 2, and 3. So for level 1, let's fill this with our lightest orange color. For level 2, the orange. And for level 3, the darkest orange there. Then for the actual result in the middle of the bar here, we can change this to be blue. Let's delete the grid lines because we don't need them for our bullet charts. So delete. And then when I select on one of the series, right-click, format data series, let's completely remove the gap width between these bars. So instead of 150, I'll set that to zero. So once I do that, we still have three different stacked bar charts within our visual, but it looks like it's only one object. To clean up this visual, I can resize it so we can make it a bit smaller here. Let's delete the title. I can delete the legend. And I'm also going to delete these values on the axis here. On our x-axis, we can see that the values go from zero to 120%, but we actually only need to display values up to 100%. So I click on the x-axis, and if I go to axis options, we can set the bounds from zero instead of 1.2 to 1.0. For our last step, I might want to add a data label to show what our actual result is. So I can add a chart element, data label, and we can see there's one in the middle here. Let's change that font to be white, and then under label options, we can change the label position to inside-end, so it appears right at the end of that bar there. Finally, because we're going to add a few more bullet charts, I can remove the borderline. No line. And let's make this a little bit smaller, now that we've removed some of the other chart elements. There we go. So now we have our first bullet chart done. Now that our first bullet chart's done, we don't need to recreate everything from scratch. We can select our completed visual, and press Ctrl-D to duplicate it, and we can duplicate it twice. So let's move the second one just underneath our first one, and then our third one at the bottom here. So when I select our second visual, I can take our data reference to our table on the right, and I can just move this down to the second table. Now unfortunately, when we do change the reference, we lose the formatting, so we're going to have to update this again. But it is faster than creating our visual from scratch each time. So what we need to do is format our data series. So right-click, and we can fill with the white gold, the orange, and the darker orange. And then update our actual bar as blue. As well as the color formatting, the data labels also don't transfer when we duplicate it. So let's click on that actual bar, add an element, data label. We can change the font to white. If I click on the data series, right-click, format data series, and then click on our label, so we can format our data label. Under data label options, we can select inside-end once again. And now we just have to do the same thing for our third visual. Let's close that for now. Drag down the reference. We can update the colors. And then the dark one at the end. Change the bar to blue. Add a data label. Select white font. And then format our data series. Select the label. Under label options, inside-end. Like that. One last finishing touch we can add is to insert some text boxes that will help us differentiate which business these bullet charts belong to. So I can go to insert, text box. Let's add it here, and we'll say business one. We can add this right at the end of our bullet chart here. Under shape format, we don't want to snap it to grid. So we'll add it somewhere around the end here, and line it up to our chart. Under shape format, we can also get rid of our shape outline. So we can say no outline. And then we can duplicate this label. We can drag one down to the bottom for our business three. And drag one to our middle visual for business two. Now to make sure that these are all aligned properly, we can grab each of these three labels. And under shape format, we can align to the left. And we can also distribute vertically. So they're all aligned across the three visuals. And now we're done. We've created three separate bullet charts for our three different business lines here. In this lesson, let's take a look at the logic of how we make these tables that populate our bullet charts. So let's copy and paste this first one, and move it over here to the right hand side. Let's delete all of the formulas. And now we can talk about exactly how we've laid this out. So there's three bars that make up these charts over here. The first bar, this top one, is this first levels column. And these are the three thresholds that we want to appear in our bullet chart. So we can see the blue font, this means that these are hard-coded values. We've entered in 80, 10, and then 10. So for our other levels column, over here to the right hand side, the formula is straightforward. It's just equal to the corresponding levels in the first column. And we can fill this down. We need to make sure that all of the values within these three columns all add up to 100, because they're all percentages. So we can do a quick check. We can sum all of the values in the column here, and we can copy this formula across. Now, this actual result, we can see the blue font again, this can be anything that we want. So we can change this from 74 to something like 65. And what we need to do to populate this second row in the actual column that corresponds with level 1 here, is we want to calculate the difference between our actual result, in this case 65, and the first threshold level, which is 80. So we can do this initially by subtracting our actual value from our threshold level. So we can equal our threshold level less our actual value. And we get 15% here. And if we did this in this table here, which is connected to our visual, change it to 65, this 15% fills in this little square here. It's the difference between our actual value and this first threshold level. So let's change this back to 74. But what happens if instead of 65, our actual result is 92? Well, this returns a value of negative 12. And that won't work for our bullet chart. We can't have a negative value in any of our stacked columns. So to solve this issue, we only want to return the result of our calculation if it's a positive value. Otherwise, we want to return a zero. So what we can do is we can wrap this formula with the max function. And we want to take the maximum value of this formula and zero. So if this formula returns a negative value, we return zero. Otherwise, we return the results of the formula. So if we change this actual result back to 74, we see that we're getting a value of six. So we know that this is working properly. And what we need to do now is to build on our original formula. So in this previous cell, we can see that we're referencing two single cells in our calculation. The actual value compared to the first threshold level. And to fill in the formula for the other threshold levels, we need to refer to the cumulative amount for both the actual value as well as the threshold levels. So we can add two additional columns to our table to demonstrate what this looks like. We can have levels cumulative and actual cumulative. So what this cumulative value will look like is it's just going to be equal to 80 for the first level. For the second level, it's equal to the previous value plus the value of that row. And then we can copy that down as well. For the actual cumulative, we're going to do the same thing. For the first row, it's equal to this value of 74. For the second row, it's equal to our previous value plus our new value. And we'll copy that down to the bottom. So when we adjust our formula, instead of just referencing a single cell here to our actual result, we need to calculate the difference between 80 plus 10, so 90, and 74 plus 6, or 80. So our new formula is going to be equal to the sum of these two items minus the sum of these two items like that. And we return 10. But remember, we need to wrap this in the max function as well. So we're going to take the maximum of this formula and zero, like that. And now we're going to do the same thing for this third and final level. We're going to compare the threshold of all levels, so 80 plus 10 plus 10, or 100, minus the sum of all of our previous actual values. And once again, we can update this formula, wrap it with the max function, so it's the max of our formula, and zero. And now when we look at the cumulative values, we can see at the first threshold level, we compare 80 to 74. At the second threshold level, we compare 90 with 80. And at the third level, we compare 100 and 90. And we can check to see how this works. So when our actual value is less than our first threshold level, like 74 is less than 80, we get the difference between the two. And the next two levels, because our bar here doesn't go into these threshold levels, are just 10% and 10%. But if our actual value is between our first and second threshold level, if we say it's 88, for example, this first threshold level is zero, and the second one is the difference between our bar and the threshold level two. And if it's above our second threshold level, if it's 95, then we go 0, 0, and 5. And this is the same logic of how we build the formulas to populate all of our bullet chart visuals. We can see that the calculations in our table are now working as expected, and they cover all of the possible scenarios for our actual value. This course is focused on Microsoft Excel, where many of your reports and analysis will still be hosted. But modern business intelligence tools give us the power to create a wide variety of charts outside of Excel. Let's take a quick look at two of the most popular options. In Power BI, selecting a chart type is easy. We simply choose the chart type from the visuals pane, and we're then prompted for the data that we want to include. Check out BIDA's Power BI Fundamentals course for more information on how to use the world's most widely adopted visualization tool. Tableau is the global leader for creating professional-looking visuals, but it is slightly more complex to use. The interface is divided into a row and column shelf, amongst others. In this example, we place category on the row shelf and sales on the column shelf, which creates a bar chart by category. We can still create lines, tree maps, and pie charts, but the process is just a little different. BIDA's Tableau Fundamentals course will teach you all the basics to create stunning dashboards in this incredibly powerful tool. Once you've mastered Excel, Power BI, and Tableau, you'll have a good understanding of data visualization that can be applied to almost any business intelligence tool that you come across. In this chapter, we examined visualizing data by building Excel charts. Working through a case study, we saw some important aspects of creating charts in Excel. We saw how to select the data to visualize, as well as how to update this reference to our data, the wider range of built-in chart types available for us to choose from, and how we can be creative and have the flexibility to create our own charts. Great work. Congratulations. That brings us to the end of data analysis in Excel. We've covered a lot of different topics, from the beta world of business intelligence in Excel, including analyzing, transforming, and visualizing data. You should now feel confident working with data that comes in different forms and structures, and be familiar with some different methods and techniques available in Excel to analyze your data, produce insights, and support better decision-making. You can now understand the fundamental parts that make up data analysis in general, and are ready to move on to take BETA's more advanced courses in Excel, and in other tools too. You are ready to take the next step by using these skills to start your own data analysis projects. Well done continuing your learning journey. If you're interested in learning more about analyzing data in Excel, or other business intelligence tools, such as Power BI or Tableau, or any topic in the world of business intelligence and data analysis, be sure to check out the rest of our courses in our BETA program to further advance your skills. Good luck, and hope to see you again soon.