Building a CXO Excel Dashboard in 15 Minutes using Claude AI [ Part 2]

In this session, learn how plot within Excel automates data analysis to generate business insights. The video demonstrates categorical, continuous, and multivariate analysis along with dashboard creation highlighting prompting strategies, efficiency gains and practical limitations in real workflows. — So, it is asking for uh allow. Yes, I would say allow. to go ahead allow. It is trying to tell us that I'm going to make some changes. I'm going to add here. So, it is adding a new sheet called categorical analysis. I haven't added it automatically. It is adding it. Categorical data analysis. Let's see. So you can see that we started absorbing Google style color coding blue, red, green etc. Maybe next time when we're doing another example we will try to ask for Apple style. Then we may get interesting charts as well. All the white, gray, black that is Apple style. Later on we'll try that. But as of now we have tried Google style which is looking little colorful. You can also ask clot to adjust it format it better. But I would say I think formatting we should be able to do the little bit of formatting. So driver one previous campaign outcome if previous campaign previously if we have contacted a customer if they have successfully opted for it there's a high chance that those kind of customers are opting. So subscription rate is 67% and then driver to contact communication people whom we have contacted through cell phone they those are the ones who are subscribing more and the contact month seasonality is there any month particularly where customers are more prone to buy our insurance let us see so see these insights see these icons etc all these are generated so you can see that March December September October so September October November December I think those four months November is baseline. So looks like here the subscription rate overall subscription overall in the population only 11% of the people are buying but in March 52% are buying. I think March it has some connection with financial ending year or something we have to find it out. December 46% are buying. September 46% of the buying and July and May these are the months May June July probably they have the least amount. So baseline above average peak below average. See these icons see this analysis. This is really great. I'm not saying that an analyst cannot create it. They can create it. But that requires a lot of experience, a lot of thinking and somebody has to really guide us through this uh business process. We got to have a lot of business knowledge as well to create such kind of insights. But Claude is doing all that thinking on its own. And based on the job type, students and retired people are the most likely to subscribe. Now that is kind of little surprising. Students are actually subscribing to our insurance. They are buying and retired people are also buying highly and these are the baseline these are the below average housemmaids entrepreneurs and then blue collar job guys they are the worst segment student and this is the insight that is created I told you in the beginning I have not touched any of the graphs formatting I haven't done anything but still you are getting the results as if somebody has spent hell lot of time in formatting analyzing now you can see that marital tell status etc etc. As of now it is not giving any charts, bar charts or anything. We can specifically ask for it create bar charts for me. Create the charts and you try to give me the insights beside the chart. It'll definitely do it. I think by now you might have got an idea. You already would have got the confidence that it is giving you the results that you are looking for. What we will do is we will do categorical data analysis. Then we will go to discrete variables which are like numerical but which are having lesser number of categories. And then we will go to continuous variable. We will analyze that. And finally we will ask it to create a dashboard. Those are the remaining steps. But by now I think you must have been convinced that already claude is giving pretty good insights. Basically we are talking about AI doing the job of a data analyst and you can see in front of eyes your own eyes that it is actually doing a pretty good job and this is just the starting. In future we may have huge number of tools where they themselves will be giving us so many options to pick up from so that we can spend out time in solving different problems. So driver and impact etc everything is given. There are some formatting issues that is not really the incapability of cloud. I would say Excel is also not that a simple tool to handle. So Claude may not be finding if it would have been a programming language then it would have handled the formatting also very well but here there

will be little bit of niggle in the formatting but we should be able to handle it sometimes there will be overlap between the graphs overlap between the tables so what I have learned is if you mention it here there is some overlap between the graphs and the tables try to adjust it try to format it then in the second iteration it'll do it I think it is doing it on its own here now the overlapping and all that it's almost like somebody some ghost is working behind the scenes and giving you all these results. Yes, it is taking a bit of time. It's almost like 10 minutes we are doing this categorical data analysis. But it's all worth it. These insights are quite valuable. You can really drive a business by looking at these insights. Probably these are all the charts that are loading. I have seen a couple of charts earlier but now they are not seen. Looks like the analysis is over. Let me ask. So looks like the charts are created but we are not able to see them. So let me tell that to claude saying that the charts and the tables are over overlapped overlap. There is an overlap between the charts and the tables. Try to format it. Then it will run all this checks whether there is any overlap and try to adjust it. Maybe we can ask it format it sequentially. give me the results one below the other we can ask it like that so it is checking the current chart position and I can see that charts are stacked at the same left position at this point so now it is running that check and it'll try to format it slightly I would have given it arrange them one below the other or put all the charts below all the tables we could give that information so once again I'm telling you just for the sake of showing it to you quickly I'm giving shorter prompts here in reality if you're really using claude for giving you the results You may have to give a very detailed prompt as if you are explaining it uh to a data analyst. This is exactly what I am looking for in this particular format. The chart should be here on the left side or the chart should be at the bottom of the tables. Now the chart formatting is happening. Let's give it a minute. Not only charts, you can create pivot tables. Literally whatever you can do in Excel, you can do that. Looks like the rate limit exceeded. Looks like it is giving me a warning that you have uh get extra usage upgrade your plan. Looks like I have used so many tokens because of those tokens. It is saying that you have exceeded the rate limit and it'll again reset. Probably I have used a large data set. That is the reason why this step has taken some time but finally it has given us the result. The final result looks like this. We have the graphs which are well arranged and most importantly we have pretty well formatted tables in this uh Google color scheme format. So categorical data analysis is done and we can also see the impact of uh each of the category. what is the variable let's say within uh let's say month March, December, September these have the high subscription rate. Now we will move on to the new tab. We will try to do the numerical variable analysis. Let's start with discrete variables or let's directly go to continuous variable. Let's analyze the continuous variables and their impact on the target. Create histograms and find the key drivers. try to create 3D charts or interactive charts wherever applicable is what I'm giving. So let's work with continuous variables. Now it is responding the continuous variables in the data set. Ideally it should create a new tab. Create continuous variable analysis. It is expecting we're expecting it to create a new tab. It has created it. After taking a couple of minutes of time, we have this final result for the continuous variables. Each continuous variable and some of the graphs, some of them are 3D charts. And most importantly, we have also got the insights. There is still a little bit of issue with the formatting. Let's say point number one is hiding behind this. But we can adjust few of the graphs by ourself then we will get the results. That should not be a very big issue. Once you get the results then you can adjust a little bit of these charts and so on. So we have the continuous variables analysis. We have the categorical variable analysis. Maybe we will try to do one more analysis before going to the final dashboard. We will ask for multivaried analysis. Perform a multi-variate analysis. Try to take two variables at a time.

Try to I'm going to provide all these uh prompts to you so that you can also try or you can go ahead and have an interaction with cloud and ask whatever you think is the right way to analyze. Perform multivariate analysis. Try to find the interaction effect. Try using two variables and find the combination. Find the combination that has the highest or that has the best target value. Our target value is getting the highest subscription rate for the term insurance project. So basically we want to sell this insurance and we want to find that segment which has the highest probability to buy. Once again I'm giving this prompt. It will take 4 to 5 minutes and let us wait until the output. After taking a couple of minutes, this is the final result that we got with a little bit of formatting. And the best part here is top 15 ranked high subscription interaction rate combos. The basically variable one, variable 2 put together. So there is a subscription rate of 65%. The average subscription rate is 11%. And top 1 to three combinations are given people who are very elderly and if their bank balance is high then most of them are subscribing. So if at all we are targeting a particular segment, these are fruitful segments. Maybe for them we don't really need to give any offers. We can do cross-selling upselling because these seems to be the customers that are high likely to buy. And these are the customers which are good but uh compared to others may be slightly lesser but overall it is 11% and we may we can also do the opposite of this. We can find the customer segments that are very less likely to buy and then we try to give them some offers. So this is the interaction effect age versus balance. How is the subscription rate when age is high? when the balance is high you see this green one where you have the highest subscription rate and context versus previous outcome etc. So this is the analysis from the multivaried point of view and then we will end this analysis with one final prompt that I want to create a dashboard. So again I'm not giving a very lengthy prompt here. What you may want to do is get into charg or cloud and get very detailed prompt or spend some time on this prompt and give the instruction clearly so that you can get the best results. So I would ask it to give me create a dashboard that has all the important KPI metrics along with the graphs include all the key insights make the dashboard for CXO or above level. So this is what we want to do. We want to keep all the metrics. important graphs and we also want to have the key insights presented in that. So let it work on this. What I'm doing is while it is working I'm not really recording because it is uh just taking some time 4 to 5 minutes. I think in the beginning of the video I told we can generate it within half an hour. From our side the effect uh the effort is half an hour but claude when it is thinking when it is generating all this sometimes we may get misformatted output. So the formatting needs to be taken care by us. We can also ask cloud but that will unnecessarily eat away your token. Sometimes you may get into an error saying you have used up too many tokens in the given stipulated time. You may have to wait for some more time before you use clot. So my suggestion to you is if there is something that you can handle this kind of basic formatting you go ahead handle it. You don't ask the claude to generate it. In fact what you will see is initially it is giving a certain type of output. Later on it will format it and we can ask it to format and it will try to give slightly different results. So right now this is just the basic benchmark uh output that it will give. Later on it will go to another level called formatting level and it will follow all these Google style. So in the beginning of the video I have shown you a dashboard. This is a different dashboard while I have created it previously. This is the dashboard format that I got that is also created by claude. Now when I'm reworking on this again I'm getting a slightly different uh differently formatted dashboard but nonetheless it'll contain all the analysis. I think by now you may have got an idea on how to create dashboards, how to work with cloud within your Excel. Now what I'll do is if you do not have the claude license, pro account, we will try to repeat the same in Google Sheets plus Gemini. So let's get into that video while it is working in the back end. I must say this particular step has taken considerable amount of time. It has now reached the final content verification stage. Now there are so many charts that are overlaid on each

other. Overall the quality of the analysis is quite good. The formatting is something that we may have to adjust a little bit because Excel is also not that an easy tool for a large language model like clot to handle it. Overall it does a decent job. What I will do is I will try to attach the best formatted output. I will try it a couple of times and then I will try to attach all the prompts and the final results so that you can also try it out. Maybe this is part one where Excel plus cloud. If you can try, you can try if you have the cloud access. Otherwise, part two can be tried by everyone. I'm not even sure whether Gemini is possible for everyone. You may have to have one paid plan for Google Drive or something. Otherwise, if Gemini is available, then you can try Google Sheets plus Gemini. That will be our part two. I'll see you on the other side. — This session showed how Claude automates analysis in Microsoft Excel from segmentation to dashboards. In the next session, you will learn how Gemini works in Google Sheets for automated data analysis.