Click on INSERT tab from the ribbon.So let’s jump straight into creating a beautiful looking butterfly chart. Select the data you want to represent in graph. To get the desired chart you have to follow the following steps.
![]() Create A Chart With A Benchmark Line In Excel Series Which SimplyWhat we do is to take a large value (say 100) and then if the actual value of a particular category is, say 45 then its corresponding padding becomes 55 (which is 100-45). The padding A and padding B are two special series which simply help us align the actual data series better. As you might have guessed, the butterfly chart is a stacked bar chart where the various bar series are arranged in such a manner that they meet/align at the center. The remaining columns merely help us organize them in the chart.By default Excel will plot the series in the order in which they appear in a range. Making the basic ChartLet’s create a basic chart with five series. (Biologically speaking – that would be the Thorax of the butterfly !) The last two columns are for creating the ‘special’ axis – where the value of 0 lies at the center and the twin scales proceed outward. The “gap” is another dummy series that helps us separate the bar and provides a placeholder for the category name/labels.All that needs to do now is to format the chart. You may want to give this part a bit of focus as the placement of various XY points are determined by the values that you provide here.Once we’ve inserted the XY-Series the chart looks like this:Although it may need look much like a butterfly chart, the above pretty much has all the components and is just a few steps away from being one. The series marked as label acts as the Y-Axis and the the other one as the X-Axis. Let’s begin by plotting an XY chart using the last two columns. So we crate one on our own. We begin with Padding A, followed by values for Store A, then the gap, followed by value for Store B and finally the padding for B.Adding the XY series for the dummy scalesExcel does not provide the functionality to create an axis which begins at 0 and has two scales extending outwards – something that we do require for creating a butterfly chart.![]() In order to remove the extra values from the legend, you can select individual named by placing two slow single clicks on it. We will inset our own custom gridlines by adding the the Y error bars to each of the points.Let’s turn on the chart legend and place it at the bottom. In order to force them to align with the X-axis, you can change the vertical scale towards the right and make the minimum value 0 and provide an arbitrarily large value to the maximum.Let’s delete to default chart grid lines. Firefox 39 for mac downloadActually the trick lies in getting a couple of steps right:1. Turn on the labels for the the values for the second and fourth (Store A and Store B) (Use values as labels)And here is our beautiful butterfly chart.You can download a sample worksheet with a example of Butterfly Chart here or click on the button below:I get what you are saying. Remove the fill from the first, middle and the last series of bars or fill them with white color.6. Turn on the labels for the XY Points (use Y-axis as label)5. Turn on the labels for the center bar (use Category name as label)2. One by one, apply this step to all the labels that are not required.The last few steps and our Butterfly Chart is ready to fly1. ![]() One by one edit the X-axis values so you force each point to lie on the intersection of the grid lines and bottom x-axis. The “label” values have to be entered as dual – say starting from -1.5 and then moving to + 1.5 with an interval of. Change the Label and X-axis values (column I and J). But if it helps, here’s another version of the butterfly chart with smaller scale. If required, fix the maximum and minimum values for both the upper and lower x-axis scales so that they have the same max and min.The remaining steps should be the same.
0 Comments
Leave a Reply. |
AuthorHannah ArchivesCategories |