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.
- Merge and center cells C2 through E2 and label the merged cell “Q1.”
- Drag the formatting across by selecting and dragging the bottom right corner of the cell titled Q1 across cells F2–N2. (See Figure 37.2)
Next, insert the chart.
- Select the entire range (B2-N6)
- Select Insert.
- Select Charts.
- Choose the basic 2D line chart. (See Figure 37.3)
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).
Format Data Series
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:
- Right-click a specific object or series in the chart.
- Choose Format Data Series. (See Figure 37.4)
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:
- Format Data Series
- Format Chart Title
- Format Data Series
- Format Axis
- Format Chart Area
- Format Plot Area
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.
- Go to high forecast.
- Go to Format Data series.
- Select the fill-in line option. (See Figure 37.5)
There are many options to customize the line. For this chart, format the line according to the following:
- Make it a solid line.
- Change the color of the line to green since it represents the “best case.”
- Adjust the width to 1 pt.
- Make the line a Dash Type to show that it is only a potential scenario.
- Repeat these steps for the low forecast, but instead of green, make the color orange.
The expected forecast is in the middle of the two possible scenarios. Let’s add markers to indicate certain milestones:
- Select Marker Options. Select Built-in Marker.
- Make it a circle.
- Change the size to 7.
- Change the fill color to white to make it look empty. (See Figure 37.6)
- Make the border a solid line.
- Change the color to dark blue and set it at 1 pt. (See Figure 37.7)
We’ll adjust the expected forecast’s line.
- Deselect the middle line and then select the middle line again.
- This ensures you will deal with the line itself, rather than the markers on the line.
- In the Format Data Series box to the right of the screen, select the triangle next to Border to collapse the menu.
- Two options will appear under the series options icons: Line and Marker.
- Select Line.
- Adjust the width to 1.5 pt.
- Change it to the dark blue color. (See Figure 37.8)
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.
- Select the legend (currently at the bottom).
- In the Format Legend to the right, select the three bars (Legend Options).
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:
- Select Chart Design. See Figure 37.11)
- Select Add Chart Element.
- Choose Axis Title.
- Select Primary Vertical.
- In the textbox, type Quantity.
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.
Adding More Data
To add data that represents our production capacity, complete the following:
- Select Select Data.
- In the Select Data Source popup box, select Add
- Select the cell name you would like to add. In this case, select Production Capacity.
- Select the Series Value.
- The cells (C8 through N8) are behind the chart so use your arrow and shift keys to select all of the information. (See Figure 37.14)
- Select Okay. A fourth yellow line will appear.
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.
Series Chart Type
We can adjust the series chart type to simplify this cluttered chart.
- Right-click on a specific series and select Change the Series Chart Type. (See Figure 37.16)
- Change the Production Capacity Series to Area Chart. (See Figure 37.17)
- The chart should reflect Figure 37.18. The new information has been pushed to the background so it doesn’t get in the way of important information.
- Change the color to light gray to ensure this information adds to the current data without overwhelming the chart. (See Figure 37.19)
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.
- Select the Data tab.
- In Data Tools, select Data Validation.
- In the Data Validation popup box, there is a dropdown selection below Allow. Select the dropdown option List.
- In the Source textbox, include the following list items (separated by commas):
- Division A
- Division B
- Division C
- Select Okay.
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:
- Create a field below Production Capacity.
- Call it =”forecast chart for “
- Use the ampersand to combine the name of the division. (See Figure 37.21)
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:
- Select and right-click the line for the Expected Forecast.
- Select Add Data Labels.
- Right-click and select Add Data Labels. Your chart should reflect Figure 37.24.
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)
- Make the font dark gray and 7 pt.
- On the right-hand side in Format Data Labels, choose the far right option.
- We can choose label options and choose where they should be positioned.
- Mark them as Above the line.
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:
- In the Format Data Labels table on the right, select the first icon of the paint bucket spilling paint, and open the Fill menu.
- Select Solid Fill.
- Choose a white color. (See Figure 37.26)
For comparison, create a few charts with different formatting:
- Make a copy of this chart by selecting and pressing ctrl+C and then ctrl+v.
- Select both charts by pressing and holding ctrl.
- Choose Shape Format.
- Choose Align and align them to the bottom of each other. (See Figure 37.27)
- Turn off the gridlines. (See Figure 37.28)
- Select the data range and press ctrl+1 to see the border tab.
- Add a light gray border by selecting Outline and Inside just in case we need to go back and reference the data quickly. (See Figure 37.29)
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.
- Copy the second chart by selecting it and pressing ctrl+C, and then press ctrl+V to paste the copy below the second chart.
- Right-click the high forecast on the third chart.
- Select Change Series Chart Type. (See Figure 37.30)
- In the pop-up box, select the third icon, Column.
- Use the Stacked Columns.
- This is an accumulative chart format, so it adds series of data on top of each other. (See Figure 37.31)
- Select Cancel.
- Right-click the high forecast.
- Select Change Series Chart Type again.
- Open the last icon, Combo.
- Select the drop-down arrow next to High Forecast and change it to Stacked Column.
- Select the drop-down arrow next to Low Forecast and change it to Stacked Column.
- It will automatically format the low forecast to the top and the high forecast to the bottom. (See Figure 37.32)
Reorder the stacks to put the low forecast on the bottom.
- Right-click the chart and choose Select Data.
- In the Select Data Source popup box, under the Legend Entries (Series) section, there are two arrows (one point up and one pointing down). Select Low Forecast and select the Up arrow indicated in Figure 37.33. This will adjust the order of the high and low forecasts.
- The list should now read, from top to bottom: Expected Forecast, Low Forecast, High Forecast, Production Capacity.
Change the formatting on the low forecast. (See Figure 37.34)
- Select Format.
- Select Shape Outline.
- Select No Shape Outline.
- Select Shape Fill.
- Select No Fill.
On the high forecast, it tells us it goes from the low forecast to the top of the high forecast.
- In the forecast table, add a cell called “Forecast Range.”
- This will be the difference between our high forecast and low forecast.
- We’ll format it the same, and then copy it over. (See Figure 37.35)
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:
- “179” pt, and at the top we have the old number.
- “259” which is referenced inside of the range.
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.
- We want three gradient stops, one at 0%, one at 50%, and one at 100%.
- To add or remove a gradient stop, select the icon to the right of the Gradient Stop bar; the icon with the green plus sign will add a gradient stop, and the icon with a red x will remove a gradient stop. (See Figure 37.36)
- In the top stop, change the color to white. (See Figure 37.37)
- In the middle stop, change it to a light orange color. (See Figure 37.38)
- In the bottom stop, change the color to white at the bottom. (See Figure 37.39)
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.
- If that’s the case, change the white to a light orange at the very tips.
- Change the transparency to 50% transparent.
- This lets the background fade in and shows us where we’re at. (See Figure 37.40)
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)
- Select the base data (B1 through N6).
- Select Insert.
- Select Chart.
- Select Line Chart.
- Move the line chart to the bottom left corner
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.