Microsoft Excel is the most precise professional calculation tool on the market, with it you can not only analyze data, but also create different variants of graphs that are very useful for displaying said information.
One of the most used graphical representations on this platform are the butterfly or tornado diagrams, since with them it is easy to compare and evaluate two sets of data . If you still don’t know how to do it, continue reading.
Index( )
- What is the correct way to sort data in Excel tables for these types of charts?
- Procedure to create your butterfly chart in Excel
- How to edit colors and axis options in Excel chart?
- To customize the color palette
- Editing the axes and their data
- How to add data inside butterfly chart bars?
What is the correct way to sort data in Excel tables for these types of charts?
Within Excel there are multiple functionalities that we usually apply little , partly due to ignorance and partly because we do not require it, but when we understand and master them, they become enormously advantageous. For example, activating the camera in Excel to make graphs , which if you did not know, is possible and very easy.
The initial step after having a table in the Excel workbook, with the three corresponding columns, is to insert an auxiliary column to the right of each of the main ones . In the central column of the inserted ones, enter a round and approximate figure to the average value of the data in your table.
The first of the inserted columns will work as a controller for the data of the first result, in it, you must insert a formula with a number considerably higher than all the values of your table , to which, you subtract the first cell of that column and drag to the end of the table. In the last column, you will use the same structure (With the same number), only subtracting the cell on the left.
Procedure to create your butterfly chart in Excel
When you frequently need to create diagrams of this type, it is recommended that you make sure you download the latest version of Excel , so you can access all its new features and optimized visuals . Assuming you already have it, let’s proceed with creating the butterfly chart in this program.
- You are going to completely select the table including, logically, the auxiliary columns.
- Then, you click on the “Insert” menu.
- Immediately, we click on the “column or bar chart” and “stacked bar” buttons.
- You will now have the graph ready to edit, preferably select all the legends, the grid lines and even the title, press delete and the diagram will be much cleaner.
How to edit colors and axis options in Excel chart?
As you already know, within this software it is possible to create different representations and give them the location and appearance that suits you best, from placing pie charts inside a cell to defining specific colors for each result . If this is an area that you do not handle very skillfully, it is always convenient that you visit tutorials that encourage you to try it.
Such is the case today, where we will continue polishing our tornado or butterfly diagram , to achieve a simple, understandable and organized result. After generating the graph with the steps described above, you must continue fine-tuning the final details, using the “Area Format” tool.
To customize the color palette
Initially, we must modify the colors of the auxiliary columns , since being able to identify the graph itself depends on it. You will notice that there are as many color bars as there are columns of data in the original table, so the next step involves:
- On the first color bar (the left), you right click.
- In the “Fill” option choose “No fill”.
- Repeat the process with the middle and far right data series.
- In the section in the middle of the two colors click, the “Chart elements” button will be activated next to it and you will take it, then in “Data labels” click on “More options”. You will already be able to notice that your graph is much more similar to the well-known butterfly ones.
Editing the axes and their data
Now, you right click on the data in the middle of the graph, choose the “Data label format” option , from there we will modify the axes and the data, you just have to:
- Go to the “Label Options” tab, make sure to check “Category Name” and uncheck “Value”.
- Continue by right-clicking on the horizontal axis and selecting “Format Axis”.
- Now in the right panel, go to the “Axis Options” tab and assign custom values for the “Maximum” (a number greater than all the values in the table) and “Minimum” (a number less than all the values in the table). the board).
- You can also modify the thickness of the chart bars by right clicking on them “Data Series Format” > “Series Options” and adjusting the “Series Options” as you prefer.
How to add data inside butterfly chart bars?
If you prefer to convert Excel graphs into a TIFF file , you can do so and thus share your butterfly graph in maximum quality , although beforehand it is appropriate that you put descriptive labels on each bar:
Select the first group of data series, go to “Chart elements” and click on “Data labels”, then on “Interior base”, you will see the labels inside the bars. To complement it, repeat the process with the second series, and in “Data labels” choose “Inner end”, you will have your finished butterfly diagram.