Today, we want to learn how to create a chart on which we have unique targets for each month, group, shop, etc.
Target Chart with Unique targets
The most important task for us is to create proper data. When we do it in the right way, the rest will be much simpler. Let’s start with creating a Base series, which will be the foundation of our chart. We have to check if the income is larger than the target. It means that we have to make a logical test with the IF function. If it’s correct, we want the smaller value, which is the target value. In the other case, we want the income (Fig. 1)
=IF(B2>C2,C2,B2)
Fig. 1 IF function for the Base series
And just like that, we have our foundation. Now, we can move on to the next step. If the income is larger than the target, we want to know how much larger it is. We will create it in the Upper column. We start with the IF function again, and we are checking if the income is larger than the target. If so, we want to deduct the target from the income. In the opposite case, we want nothing, i.e. we want our chart to show nothing. It means that we cannot write just an empty text string in the formula in two double quotes. We have to return an error. The simplest way I know is using the NA function (Fig. 2)
Fig. 2 IF function for the Upper series
And just like that, we have our Upper series. The last thing to do is the Lower series. It is very similar to the previous formula, All we need to change is the ‘larger than’ sign into ‘smaller than’ sign, and deduct the income from the target (Fig. 3)
Fig. 3 IF function for the Lower series
Just like that, we have the second series.
Now, when we have a value in one of the Upper cells, we will have an error in the same row in the Lower cell, and vice versa. When our data looks OK, we can move on to inserting a chart. We have to select the Months column, as well and the Base, Upper and Lower series. Then, we go to the Insert tab (1), then to the bar chart, where we select the Stacked Column option (2) (Fig. 4)
Fig. 4 Creating a stacked column
Now, we have all we need. We have our Base. When our income is larger than the target, we have a blue part, and when we didn’t reach the target, we have green. Let’s change the colors, so that the differences are more visible. I also don’t like the horizontal lines, so let’s delete them by selecting them and clicking the Delete key. I will also widen the columns by selecting them and pressing Ctrl + 1. In the Format Data Series window on the right we can change the Gap Width to 70% (Fig. 5)
Fig. 5 Gap Width option
Now, I want to add our target. Since we have only column charts here, we can copy our target column, select the chart and press Ctrl + V. Now, we have our target, however, we don’t want to have another column. We can change it into a line chart with marks. We can do it by right-clicking on the target series in the chart. In the pop-up menu, we have a Change Series Chart Type option (Fig. 6)
Fig. 6 Change Series Chart Type option
In the Change Chart Type window, we can change our target by clicking on proper options (Fig. 7)
Fig. 7 Line with Markers option
However, there is one more option. We can go to the Insert tab, and when the series is selected, we choose a proper chart. The result is the same (Fig. 8)
Fig. 8 Line with Markers option
Now, we have to select our line with markers, then press Ctrl + 1 to open the Format Data Series window. When our line chart is selected, we have to go to the bucket part, and select the No line option in the Line part (Fig. 9)
Fig. 9 No line option
In the Marker part, we can use some built-in markers, like a line. However, when I’m using the line, it’s too thick for me, especially in a bigger size (Fig. 10)
Fig. 10 Bigger size
In such a case, we can do another trick. Let’s go to the Insert tab (1), then the Shape part (2), and select a simple Line (3) (Fig. 11)
Fig. 11 Line option
Now, while holding the Shift key, we can draw a line with our mouse (1), and modify it (2) (Fig. 12)
Fig. 12 Line modification
Now, we can copy the line, then select markers and paste it. I can see that my line isn’t long enough, so i’ll just make it longer, then paste it once again (Fig. 13)
Fig. 13 Longer line
Now, let’s insert data labels. In the Chart Elements window we have to select the Data Labels option. Excel,then will give me data labels for each series (Fig. 14)
Fig. 14 Chart elements window
However, I don’t want data labels for the target series, so I have to select it and just delete it. Now, It’s important that we have data labels for our upper part only when there is an upper part. In places with errors, there won’t be data labels. The same is with lower parts. Errors won’t show anything on the chart. Let’s change lower data label color by going to the Home tab and selecting white (Fig. 15)
Fig. 15 Color change
Let’s add the chart title. Let it be the contend of cell B1. We write = in the formula bar, then click the cell and press Enter (Fig. 16)
=‘E061’!$B$1
Fig. 16 Chart title
There is one more thing. I sometimes don’t want to show the target series in the legend. To remove it, we have to click on the legend, then once more on the target series in the legend, then press Delete (Fig. 17)
Fig. 17 Final chart
Now, I’m happy with my chart with unique target for months, groups, shops, etc.
Today, we want to learn how to insert a bar or a column chart in Excel.
Creating a Column Chart
For a start, we can select a cell in our dataset. We can also select the whole set. Then, we go to the Insert tab and choose a proper chart. I personally like 2 D columns, so I’ll select it (Fig. 1)
Fig. 1 Selecting a 2 D column chart
And just like that, we have our chart. We can modify its size by dragging the borders. When you drag the chart holding the Alt key at the same time, Excel will match the size of the chart to cell borders, which is a nice trick.
Now, that we have our chart, let’s change the way it looks. Let’s go to the Chart Design tab and choose one from the Chart Styles (Fig. 2)
Fig. 2 Selecting a proper chart style
We can also decide what individual chart elements we want to change. If we want to delete horizontal lines, we can just click on them once, and press the Delete button. If we want to have wider columns, we have to click on them, then press Ctrl + 1, which will open the Format Data Series window, where we can change the Gap Width. Let’s change it to 55 %. Now it looks good (Fig. 3)
Fig. 3 Gap Width to 55%
I will also add data labels. Let’s go to the Chart Elements (1) , and choose Inside End Data Labels (2) (Fig. 4)
Fig. 4 Inside End Data Labels
Now, let’s change the font color into white. You can find this option in the Home tab (1) , in the Font area (2) (Fig. 5)
Fig. 5 Font color
And just like that we’ve created a column chart. When we change some values in our dataset, our values in the chart will also change (Fig. 6)
Do you want to put a Pareto chart in Excel? I will show you how.
How to Create A Pareto Chart
If you want to put a chart on which you have columns that represent income and a line that represents the cumulative percent income, follow me.
From Excel 2016, you can simply go to the Insert tab and choose the Pareto chart from the Histogram command (Fig. 1)
Fig. 1 Inserting a Pareto chart
And you have the Pareto chart ready. However, the charts from Excel 2016 have some drawbacks. The line isn’t an actual series of the chart. It means that we cannot add data labels there. That’s why I don’t prefer using this type of chart. What I prefer is the earlier version that gives me more freedom concerning value changing, although it requires more calculations. We have to calculate the cumulative percent income on our own (Fig. 2)
=SUM(B$2:$B2)/SUM($B$2:$B$9)
Fig. 2 Cumulative percent calculations
Now, we can insert a simple column chart. Since the income is enormously bigger that the percentage, we aren’t able to see the income columns at all, but we want to select them (Fig. 3). How can we do it?
Fig. 3 No % Income columns
We can select our chart and go to the Format tab. On the left we can see all elements of the chart. We are interested in the Series “% Income” option (Fig. 4)
Fig. 4 Series “% Income”
We can see now that the series is selected on the chart. Let’s press Ctrl + 1 and go to Format Data Series, Series Options, Plot Series On, and select the Secondary Axis option. Now we have % income on a different axis (Fig. 5)
Fig. 5 Secondary Axis options
Now, we can change the chart type into a chart that will represent income better. We have to click on the chart element once, then go to the Insert tab and the Line chart with Markers option (Fig. 6)
Fig. 6 Line Chart with Markers
Since we are creating a Pareto chart ourselves, the values aren’t sorted and we have to do it manually. We just select one cell in our data, then go to the Data tab and choose the from Z to A option (Fig. 7)
Fig. 7 Data sorting
Now the data on the chart is nicely sorted and it looks more like a Pareto chart. We still have to add modifications to make it a real Pareto chart. First of all, let’s select a column and press Ctrl + 1. On the right, we have the Gap Width option. Let’s slide it to 0%. Now, let’s go to the secondary axis. It goes up to 120%, however our maximum is 100% (Fig. 8)
Fig. 8 Gap Width modification
We have to select the axis, press Ctrl + 1, go to Axis Options and write 1 in the Maximum bar, which equals 100%. We also change our Major Units to 0.2 which means that there will be less percent numbers showed on the axis (Fig. 9)
Fig. 9 Less percent numbers
We still don’t need the 0 after dot in our percentages, so let’s go to Numbers and change the Format Code from 0.0% to 0% and click Add (Fig. 10)
Fig. 10 Format Code change
Now, let’s go to the Income Format Axis. Press the axis and Ctrl + 1. Let’s change the Major from 100000 to 200000. This way the chart will show less numbers (Fig. 11)
Fig. 11 Less numbers
What I care about the most right now are data labels for the line. Let’s click once on the line, click on the plus sign and we have Data Labels option. Let’s place them above our line (Fig. 12)
Fig. 12 Data Labels
We still need to set a proper title. Let’s just write Pareto. After implementing the most important changes, the Pareto chart looks like that (Fig. 13)
