In the above pop-up window click on Add below Legend Entries (Series). For this, right click you mouse anywhere on top of the above bar chart in Excel and then choose Select Data…You will notice a pop up window similar to the one shown below The next step is to add the trend line for the last column i.e. The only thing you want to change is the color of the bars from default excel blue to something mellow like grey. You could leave the default excel format for most part. I have done some additional formatting for the bar plot to make it appropriate for my purpose. The default excel bar plot will look different. Use Column in Charts within Insert tab (as shown above) to create a vertical bar plot. The first thing you need to do is create a bar plot of the first two columns i.e. ‘Bad Rate’ is the percentage of ‘Bad Loans’ to ‘Number of Loans’ i.e. I recommend you copy past this data on Excel and evaluate for yourself the merits of a single chart in comparison to two.
Let me present the data we are going to use for our purpose. This is useful because you and your audience don’t need to jump from one graph to another during analysis. Using Secondary Axis to Create Two Y Axes in ExcelĪ good practice is to present these two graphs in a single chart. If you recall, you have observed the following age groups wise population histogram and bad rate trend. You did some exploratory data analysis (EDA) using tools of data visualization and found a relationship between age with bad rates (Part 1). Bad rate, by the way, is percentage of customer defaulted on their payments. The idea was to identify customer segments with distinct bad rates. Additionally, you had noticed around 2.5% of overall bad rate for these loans. The bank had disbursed 60816 auto loans in the quarter between April–June 2012. Recall the banking case: in that you were playing the role of the Chief Risk Officer (CRO) for CyndiCat bank. In the next article we will create the same plot on R. This time we will create a two Y axes plot by using the secondary axis function in Excel.
In my opinion Excel is the best tool to start learning advanced analytics! Today we will start the ‘Analytics Lab’ using concepts we have discussed in the banking case study. It’s a bird…It’s a plane…It’s Excel man! Did I ever tell you what a super hero Excel is. We will use R and Microsoft Excel (both easily available tools) for this.
#Use secondary axis in excel for mac series
Keeping this in mind let me start a new series on YOU CANalytics called ‘Analytics Lab’ where we will practice some of the case studies we have discussed in the past on YOU CANalytics. There is nothing in the world that could beat hands-on practice for learning.
Malcolm Gladwell in his book ‘ Outliers‘ says that it takes approximately 10,000 hours of training or practice for anybody to master a subject.
0 kommentar(er)
