In this chapter, we will look at some advanced features for charts and graphs in excel. Specifically, we will look into forecasting and how you can use advanced features in charts to quickly compare data.
Use this workbook for the chapter.
Let’s take a look at a spreadsheet a company might use to forecast data. This spreadsheet has 12 months of data. There is an expected forecast, high forecast, low forecast, and production capacity. The production capacity represents what our manufacturing capability is. What’s the maximum number of units we can produce in a given period? (See Figure 37.1)
One thing businesses have to understand about forecasting is that anticipating data isn’t always simple or easy. To combat that, we estimate the best and worst case scenarios. We’ll organize it from low (worst case) to high (best case).
First, let’s organize this sheet in quarters like businesses do.
Next, insert the chart.
Unlike previous charts, you’ll notice this chart automatically added two rows of headers as a way to group them together into quarters. This is a feature you can take advantage of as long as you select both of the header ranges and they don’t have information in the cells to the left (in this case, cells B2 and B3).
Designing the chart is easily managed because almost every element in a chart is formattable. You can access the format options by completing the following:
This opens the formatting options in a box to the right of your screen. By selecting different elements of the chart, you’ll see different types of format menus including the following:
Each menu includes options like fill-in line, effects, and size and properties. Before you begin formatting, remember: the goal of a chart is to present data as clearly as possible so you can persuade, inform, or clarify information. Let’s practice these traits in formatting.
There are many options to customize the line. For this chart, format the line according to the following:
The expected forecast is in the middle of the two possible scenarios. Let’s add markers to indicate certain milestones:
We’ll adjust the expected forecast’s line.
The chart should reflect Figure 37.9, which shows the range of forecasts.
Typically, people read from left to right, top to bottom, but for charts, people will look at whatever stands out the most. We can direct their attention by adding a legend at the top of the chart. A legend clarifies what each color or data set means.
In the drop-down Legend Options, under Legend Position, select Top. (See Figure 37.10)
Always label the axis. Label the vertical axis by using the following steps:
Just like we learned in the previous chapter, we can also use the select data option (see Figure 37.12) to be able to add additional series to our data set as shown in Figure 37.13.
To add data that represents our production capacity, complete the following:
The chart should reflect Figure 37.15; as you can see, sometimes charts get cluttered and it becomes difficult to see what’s going on.
We can adjust the series chart type to simplify this cluttered chart.
Now we can quickly see that while the low cast is always possible and the high cast is sometimes possible, there are times in January and near the end of the year that we won’t be able to produce everything we need to hit our forecast. This chart shows that while you could do the math using the data provided, it’s much faster to view the data visually. See Figure 37.20 to see what your chart should look like now.
Chart Titles help clarify the sets of data viewers look at. For our example, let’s suppose that this is the dashboard we’re using to pull in data for different divisions of the company.
If we change the input cell to Division B, the data pulled in the data set would look at a new set and change. We would want the chart and chart title to change too! To do this, do the following:
Now when we select the chart title we can go to the formula bar, select Equals, and reference the cell. (See Figure 37.22)
If you change the drop-down, it will change the title of the chart. If our data was connected to a different data source, the chart would then rearrange the new set of data.
In addition to formatting the series on the chart, you may want to format the plot area itself. Sometimes people like to have a solid fill in the background. Sometimes, no fill is needed. It depends on what you’re looking for. The same can be done for the whole chart as well. You can select the inside of the chart and change the fill and set off the chart in the middle by doing it that way. See Figure 37.23 for an example of how a chart can be formatted.
We recommend that you keep the solid fill on the plot and chart area white in most cases because it stands out cleanly and doesn’t distract the eye with a bunch of new colors, but it depends on how you’ve designed your specific worksheet.
Data Labels indicate specific data points. Let’s go over an example:
Now you should see the number corresponding to the data on the chart itself. Be careful when using these data labels; they can make the chart look very cluttered. In this example, the vertical axis already shows the number, and unless there is a specific reason to know an exact number, we do not need the data labels. However, let’s practice formatting data labels for the future:
First, adjust the size and color of the data labels. (See Figure 37.25)
You’ll see on the chart there is a gray area chart behind some of the numbers, which makes it difficult to read. To help it stand out against the light gray background:
For comparison, create a few charts with different formatting:
Use the second chart to reference the ranges between the high and low forecasts. We’ll use the next chart to show the high and low forecast as a range and the expected forecast in the middle.
Reorder the stacks to put the low forecast on the bottom.
Change the formatting on the low forecast. (See Figure 37.34)
On the high forecast, it tells us it goes from the low forecast to the top of the high forecast.
This represents the range from the top of the low forecast to the top of the high forecast, and now, we will change this series to forecast the range number instead of that high forecast.
At the bottom of the chart the first bar reads:
We can format these to blend into the background a little more in order to show a range between which our expected forecast lies. What you’ll see over time is that the ability of that range increases; in other words, we’re pretty accurate in January, but as the year goes on and we get further in the future, we show we are a little less confident in where our ability to forecast actually is.
To format these ranges, we’re going to use a gradient fill.
That shows us that the center of the range may be more confident, but the edges are less confident. These colors here may disappear too early when we select off it.
You can also remove the line so that each dot represents the expected forecast within the broader range that we’re displaying. (See Figure 37.41)
We can also display, in the Series options, how wide the series are by changing cap width from 150 to 250, or 300. (See Figure 37.42)
It’s almost the same width as the circle. We can change it back to white on the ends. This is a clearer way to show where the range is in terms of the shapes.
If we wanted to, in addition to a fill, we could add a border line at a light gray to show us the actual range. (See Figure 37.43)
Your final worksheet should look like Figure 37.45.
This chapter reviewed the different ways to share the same data using some of Excel's more advanced charge functions. Compared to the base line chart we started with, you’ll see there’s quite a bit you can do to improve the visual presentation of data to help businesses make better decisions.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/advanced_charts.