Google Sheets & Gemini AI: The Alternative AI Data Analyst[Part 3]

— This session explains how to use Gemini in Google Sheets for automated data analysis and dashboard creation. You will learn data set setup, prompt-driven workflows, — visualization techniques, and how to generate insights efficiently for business decision-making tasks. — We will repeat the same, but this time not on Excel. We will try to do the same analysis on Google Sheets. Now, in Google Sheets, you must have Gemini right at the top. You can use this option, Ask Gemini. I'm not sure whether this option is freely available for everyone. If at all in your Google Sheets, if you see this, that means you can try to do all these exercises. But I'm pretty sure if you take the Google Drive extension, I think if you take 100 GB Google Drive for a year or for a month, then this Gemini option will be available for sure. Now, the files are there on my GitHub. You can download from my GitHub, Venkat Reddy AI Classes. You can download the data set, and data set information is also given to you. You can go through that data set information. You can download the data. And the previous exercise that we have done, data analysis with AI by using Cloud, whatever analysis and the final file, along with all the dashboard details are available there. And the prompts that are used, after modifying a little bit, you can use these ready-to-use prompts. So, what we will do right now here, instead of writing the prompt slowly, we will try to use these same prompts. In fact, when you're practicing in your analysis, you can directly use these prompts. So, let me take the first prompt, overall summary and basic descriptive statistics related prompt. In Gemini, what you must do is you must select this data, and then click on Gemini. It will get to know what is that data it has to work on, and then we will paste the prompt. Gemini tend to give the results, but it gives in fragments, and sometimes it becomes very difficult to include those results in our particular sheet. But let us go ahead, try it. Let us see the options. I have selected the data and uh we are not creating any image, we are directly analyzing the data. So, let us see what are the results that are given by Gemini. Compared to Claude, Gemini seems to be a little faster, but in terms of the analysis and the results that we get, uh Gemini is not as accurate as Claude. And Claude tends to give a lot of in-depth analysis, Gemini very minimal analysis. So, it is doing all these analysis and it is giving us the results here. And Gemini is asking us to insert by ourself. That is where I found it a little painful, whereas uh Claude is uh directly giving us the result. So, in the sheet itself. So, let me add a new sheet and then I'll try to insert one by one. So, this is inserted. And then let us see without adding a new sheet if I try to insert. So, first I have a inserted data set overview until basic descriptive statistics and then this also I you can override the data or add to new sheet. I'm asking And then this also I will try to insert. So, this is a sheet one, sheet two, sheet three. The current window is too small to display all this, this is what it's saying. Can't sync. So, these are small niggles that we are facing with uh Gemini, but that should be fine. It is inserting everything uh when we click on it. Sometimes it is going to give us a results in the new sheet itself. Maybe in the next prompt we will try to particularly mention, give them in a new sheet. And surprisingly, even though there is internet connection, sometimes it says that uh Gemini says that you are offline. Probably we are offline from the point of view of Gemini, but in reality, since I'm recording this, we know that we are actually online, but it is saying you are offline. Maybe we will try to reload this. It is saying you cannot sync the changes. Fine, we can change the sheet names depending on what data they contain. So, that was the first prompt. I have given all these uh seven prompts in the most concise manner. You can expand on these prompts. Apply consistent Google style chart design. So, this is just an information that we are giving. I want to have all the charts in Google style. That is what I have told. Fine. From here on everything will be in Google style. And then I'm getting the third prompt. But this time I will add one more extension saying, "Add all the analysis in a new sheet. " Let us see. So, I have given the prompt to the categorical data analysis and I have told add all the analysis in a new sheet. But still it is saying, "You have to insert by yourself. " And provided the data table seems to contain only statistics. I think it is looking at this data. Probably we have to select this data. First the data and then you have to give your prompt. That is one thing with Google that we have to be careful. And then I would say, "Add the analysis to a new sheet. " So, basically what happened in the previous case was we were on sheet three and it was looking at these details for analysis. We have to select this data and then give the

prompt. Then it will work on this data. And let us see whether it will add it in a new sheet or not. As you can see, while Gemini is working, it is creating all these charts in Google style. Somehow I like these charts better than Cloud charts. But the problem is we have to insert all of them one by one and then we have to insert everything by ourself. It would have been beautiful if uh Gemini itself decides what to add and how to format everything. But there is one good option. We can export everything to Docs. So, right now you are on Google Sheets. You can export to Docs also. So, that means whatever analysis that we have done earlier, we can export it to Docs. But let me go to new sheets and try to insert all these analysis one by one by one. Now, that is the painful part. Let us see whether we can ask it directly to add all these analysis and charts analysis tables and charts in a new sheet one below the other sequentially. Let's see whether it understands this request and gives us a new sheet. Previous one I have exported to Docs. While it is adding to new sheet, maybe we can see that document. Maybe let's go there a little later, but first let us see whether it understands my request. My request was you have given good analysis. We are pretty satisfied with the graphs and the analysis, but we want you to add to a new sheet inside this. Let us see whether Google achieves that or not Gemini. All right, it has understood our request. It says I can certainly perform the detailed analysis and generate tables, but I'm still learning cannot directly create a new sheet or automatically populate the analysis or tables. That is what it is saying and if you see our data set looks like it has a kind of modified our data set. I will say discard. So, now we are back to our data set. So, let it be like that. Let us see what is the result in that drive the Google Doc that we have. So, it has exported everything into Google Doc. Not bad. It has given all the titles etc., but a good to have would have been if it is pushing it to Google Sheets itself. Let us try to do this manually one by one. Let us see whether if we spend some time on it, whether we can get it or not. So, it is pushing it here. No, this is not the place that I want to push it. So, I will select a new sheet and then I will insert select another cell here, insert one below the other. I myself I'm doing it. So, if we are getting the analysis, probably we can spend it is always not a bad idea to spend some time on this. Just push all these details here one by one by one. One below the other. You just select the cell and say copy chart or paste it or you insert it. Wherever you want to insert. So, basically all the analysis that you have got, you select a cell and then insert. Insert one by one by one. So, you have the insights etc. I think you got the point. You get all these fragments and then each of these fragments you try to push it here. Now, we will move on to our next analysis, the next prompt. I'll show one more example, then I will leave it to you. So, this is the categorical one done. Discrete 3D charts is what we will try to create. We will go back here, select the data and then put your prompt and let's say enter. Here we go. We have the analysis for the discrete variables. Let us add a new sheet. You may want to give a name to the sheet. The previous sheet is categorical data analysis. This sheet is discrete variable data analysis. You select the cell, push it. Push this chart here, then select another cell and push this here. The chatting style is quite better compared to the previous one that is generated by Claude. And the insights also not that bad, but one big advantage with the Claude is it is working. You are in Excel, Claude is also with you in Excel. I think very soon Gemini will also give you an option to add it. Probably right now itself there would have been an option by giving the prompt or by adjusting the prompt slightly, we should be able to insert it or somewhere we may have to give the permission for it to insert but overall analysis quality and the key insights quality is quite good. Now let's go ahead to our next prompt. I think you can do this. We have only three more prompts left. I will go ahead add those three more prompts and quickly we will reach to that executive level dashboard. So continuous variable analysis, let me just select the data once again. Go back, select the data.

Give your prompt. Let it analyze. Here you go. The analysis from the continuous variables point of view. Pretty good analysis. You have box plots. The secret here is Excel doesn't have inbuilt box plots but here box plots are created for continuous variables. Histogram charts are created. Again box plots and the analysis of each of the box plot, the key insights, everything looks pretty beautiful here. Then we will go back. There are two more prompts that you will be trying. As you know, I will be sharing this sheet as well. What I'll do is I'll not share this particular sheet. I'll work on this. I'll make sure that I am keeping all these charts. I am giving the commentary to this. Then I will share this sheet as well with you. And anyway, I have shared all the prompts which you can access from my GitHub. You can see the link in the description and you can get it from there. So let's go to the last but one, the sixth prompt, multivariate analysis. So as usual you should go to the data set and do this multivariate analysis. So let's do this multivariate analysis and followed by the final executive dashboard. And we have the multivariate analysis results. Such beautiful charts we are getting. Let us create a new sheet and all the add all this analysis. So telephone cellular versus the subscription rate. Highest subscription rate happened in March. That is what like interaction between the telephones or subscription rate by month and contact type. So by rate analysis along with the subscription ratio is given. Such beautiful very in-depth analysis. We can also include this one. Very nice conditional formatting has been done and the insights everything. So quite good analysis. Some people like to work with Claude. Gemini. Both of them are equally good. But I personally found that Claude somehow has a added advantage of fiddling with your own Excel file. Very soon Google will also introduce that if we give the permission. Probably Google doesn't want to override the data or do not do something that may cause the damage. Probably that is the reason why it is asking us to manually insert everything. I want you to do this exercise without fail. not only use the prompts that I have given. You try to create your own prompts. In fact, you can make use of ChatGPT. You can say that this is my data set. This is my objective and give me the prompts. How do you analyze before building the larger dashboard? What is the analysis that I have to do? Give me step-by-step prompt. Now the final prompt executive level dashboard is what I want to create. I have selected the data and then let's give create a professional executive level dashboard. And then it has started working on it. So the dashboard results are loading here. The executive level dashboard results key customer insights. Let us add one by one. We may have to do our own formatting here. So this is the first step and then maybe we will select it. Front cell here and add these details as well. One by one we will add all the required details in the executive level dashboard and finally Google is giving us the key insights in a very in-depth detailed manner. So here what I'm trying to prove is if you know what you want, if you have the business knowledge, you don't really need to spend a lot of time in creating the insights. formatting. Large language models have now the ability to understand your requirement and they can give you the results that you are looking for. In fact, you can have a discussion with the large language model and modify the charts or modify the results that are given to you. So, what it may take for creating all these charts, results, it may take one or two weeks time or at least one week time would have taken to get all these key insights for a regular data analyst. Now, we have taken hardly half an hour, maybe 1 hour, even if it is 1 day. It is almost like 10 times more time is saved. So, I want you to experiment with either Copilot plus Excel or Cloud plus Excel or Gemini plus Google Sheets and I want you to write in the comments how did you find it and what is the next step that you are going to take when you are analyzing data automatically by using a large language model. You now understand how Gemini enhances Google Sheets analysis and dashboard creation. Practice crafting better prompts, experiment with data sets, and explore multiple AI tools to improve analytical speed, insights, and reporting effectiveness.