We have, as an example, such a table with information on the number of employees and the cost of four well-known IT companies:
Task: You want to visually display both parameters for comparison across all companies.
You can try to build a typical bubble (scatter) chart in such cases. You can try playing with XNUMXD charts or tornadoes. Or you can play around a bit and make a flat histogram with variable width columns.
Stage 1. Data preparation
Half the success in building any non-standard types of charts in Excel lies in properly preparing the data table for the chart. In our case, the original plate will have to be significantly altered and turned into something similar to:
Let’s take a look at all the changes that have been made.
- Each company has gone from row to column.
- For each company, there should be a block of 5 cells, where the first and last cells with zeros, and the three central cells contain the same values - the cost of each company.
- Added auxiliary columns Dummy и signatures, in which the values and names of companies are placed opposite the central values of each block. We will need these columns a little later for the correct placement of column captions.
- The first (unsigned) column is a bit trickier. The cells that are not filled in blue in it are the number of employees for each company (92, 126, 54, 380), but taken sequentially with a cumulative total. Those. for Apple it’s 92, for Microsoft it’s already 92+126=218, for Google it’s 92+126+54=272, etc. The blue cells will subsequently be, in a way, “adapters” and contain the arithmetic mean of neighboring cells, i.e. for Apple it is (0+92)/2=46, for Microsoft (92+218)/2=155 etc.
When the table is prepared, you can exhale with relief – the most dreary part of the process is already behind. It remains to build the diagram itself.
Stage 2. We build a diagram
Let’s select all columns in our table except the last two and on the tab Insert (Insert) choose to build a diagram option With stacked areas (Stacked Area):
The output should look something like this:
The main problem is that Excel automatically interprets the X-axis labels not as numbers, but as text – the intervals between the labels do not correspond to the mathematical difference between the numbers, and the labels themselves are repeated. That’s why our chart doesn’t look like what we want yet, but it’s actually easy to fix. Right-click on the horizontal axis and select the command from the context menu Axis Format (Axis Format). In the window that opens, switch Axis type с Autoselect on Here are the dates (Date Axis) and everything will change at once!
In the same window, you can set the division step along the horizontal axis – for our example, I changed the parameter Units from months to days and set the value to 50.
Stage 3. Add signatures
Now it would be nice to add labels with company names to our diagram. Copy a column Dummy (highlight the range G11:G25 and press Ctrl + C), select the chart and paste the copied data directly into it (press Ctrl + V) – a new row should be added to our diagram, looking like the roofs of houses above our rectangles:
Now right click on any “roof” and select command Change the chart type for a series (Change series chart type). In the window that opens, change the chart type for the added series to a regular chart with markers:
Then right click on the chart and select Add data labels (Add Data Labels). Excel will sign the value of the company at each “peak”.
The next step depends on which version of Excel you have. If you have the latest Excel 2013 or 2016, then everything will be easier. Right click on signatures, select command Signature Format (Format Data Labels) and then enable the checkbox Values from cells (Values from cells). It remains to select the data from the last column in our table so that they get on the chart in the column labels instead of numbers:
If you have older versions of Excel, you will either have to change the signatures manually (several single clicks on the signature until the cursor starts blinking, and then enter the company name), or use a special free add-on XYChartLabeler (God bless Rob Bovey – its author, who saved a lot of my nerve cells and time).
PS
Excel 2016 was the first version in 20 years where Microsoft finally added new chart types (Waterfall, Pareto, Marimekko, etc.) to the standard set. I really want to believe that the “ice has broken” and in the next updates we will see even more beautiful and visual options. Perhaps someday the histogram described above with a variable width of the columns will be built without the “shamanic dances” from this article, but in two movements. Let’s hope 🙂
- How to build a bubble chart in Excel
- What is a Pareto Chart and How to Build One in Excel
- Comparing Two Datasets Using a Tornado Chart