Analyze data in Excel with a case study of Dry Port to Dry Port (Part 3)

Published by Ngoc Tran on 21 October 202121 October 2021

Let’s continue with the final step in the series of data analysis in Excel.

#4: Data visualization

There are usually two common ways to visualize your data, either via PivotChart, or simply via Excel Charts. Since we have not discussed PivotTable in the previous steps, it would be better to go with the second option. You can see above a stacked bar chart about a comparison between lead time and cost, as a result of our calculation in step 3.

As seen from the graph, there are five transport routes as indicated in our case study, with the comparison between the estimated and actual costs. Each part of the stacked bars represents each step of the whole supply chain. Take a few minutes to read the graph before moving forward!

So how to create a graph like this? Follow the 5-step process below:

• Step 1: Strictly organize your data set as shown in the figure below. Row N1 to W1 represents for the steps of the supply chain. For example, 11 means “empty container pickup” in row D1, 22 means “loaded & transport back” in row E1, etc. You can continue to adapt the numbers with the steps. This is because it was (at least for me) impossible to put data of lead time and cost together. Furthermore, this serves for the next step where we make a secondary axis for the dataset. This therefore leads to the separation of the two datasets (lead time: row D1-M1, and cost: row N1-W1). Note that if you don’t like to indicate each step as “11”, “22”, it is also fine to call it your way as long as you understand how to do it. Coloring the data rows is a tip to recognize the fixed data (in yellow) and the variable data (in red) so that you know what to update at the later stage. Again, adjust the color or take note of this in the way that you are comfortable with.

• Step 2: Select the whole data => Go to “Insert” and choose 2-D bar, stacked bar. There might be a problem that “steps” (row C1) is also included in the data, but you don’t want that. Therefore, go to “Chart Design” and choose “Select data”. Then, choose “Edit” and choose the range B2:C25.
• Step 3: Go to “Chart Design” and select “Change chart type” => Go to “Combo” and change all chart types to “stacked bar” => Select “Secondary Axis” for all series name in number (so “11”, “22”, etc.) or in text (“empty container pickup”, etc.). You should see your graph will be slightly similar to the first figure.
• Step 4: Adjust the colors for each step. Here you need to use the same color for each step in text and numbers, because Excel will give them different colors while a specific number would be equivalent to its text. For example, “empty container pickup” and “11” mean the same thing, so you can decide “blue” as a unique color for these parts of the stacked bar. Similarly applied for the other steps. After completing this, delete all the steps in numbers in the legend part.

• Step 5: Decorate your graph (for example by changing the style) and congratulations, you got your own graph!

Please do practice this 5-steps process a few times in order to understand how to create a chart showing a comparison between lead time and costs simultaneously!

This guidance is simply based on my experience and trials, so if you know another better way to perform, please share in comments so that everyone can learn together.

Thank you for reading and I hope you have enjoyed this section!