Streamline Your Bill for Excel for Research and Development with airSlate SignNow

Bill for excel for Research and Development

in a world where time is of essence and data is King wouldn't it be amazing to have an AI power assistant that can help you build an Excel report from scratch well look no further today we are going to look at chart GPT and how we can build this beautiful and interactive Excel report using it let's go so here I have got some chocolate sales data and I want to create a dashboard from it so let's ask chart GPT what are the first steps that we should be doing I'm going to select the headers and copy them and go here and then say so here is the suggestion from chat GPT it says uh you can do total sales display the overall sales figures for a given period compare the sales perform ments of different salespersons analyze the sales based on different geographical reasons Etc and these are the seven things that it has given so now I don't really know how to do any of these so let's ask chat GPT how to do all of these things when it comes to chat GPT the key to successfully getting what you want is to treat it like a person so think of chat GPT as your assistant or a colleague or a friend Network talk to it in the normal way just ask questions in plain English and be as descriptive and as simple as possible this is really impressive so now it has given me detailed instructions on prepare the data insert pivot table set up the pivot table and then you know create the pivot charts so let's go one at a time and ask the questions for how do I make the pivot table for total sales so here is the first set of instruction total sales display the overall sales figures for given period drag the amount field to values area in the pivot table field list ensure some aggregation is applied to the amount field optionally you can also add the date field to the Rose area to break down the sales by date so let's do that we have got this here I can insert a pivot table and here we're going to put amount into values it is already summing we just need to apply the number format into currency and that is the total amount $18 million and optionally we can also break this down by date so now we can see this at yearly level 2022 full year we have got $1 million and half first half of 2023 $6 million and if I expand these out I can see this by quarter so we have got q1 Q2 Q3 Q4 like this this looks like a good one so let's make this one into a pure chart again if I go back to the instructions here it has given me the instruction for creating pivot charts select the pivot table go to insert tab click on the P chart and choose the desired T chart type customize the chart by adding or removing chart elements formatting the access and applying suitable chart title so let's do that insert and I'm think for this kind of a thing a column chart would be perfect let's add that and uh I'm going to customize this by taking out the legend and here is our first chart after doing a little bit of formatting let's repeat the same process for some of these other pivot tables meanwhile here are some jokes about pivot tables that Chad GPT made for [Music] us I like the third one what about you so using those instructions I made these four graphs see sales by quarter top countries top sales persons and top products behind each of them is one PIV table for example here is my top salesperson pivot where I have got salesperson in the RO level area amount in the values area and then I sorted these amounts by right clicking sort largest to smallest and once this pivot table is there then I created a p chart by going to insert and selecting a bar chart option and formatting it so here is how our dashboard so far looks it is pretty static so I would like to make it interative by clicking on something and changing it all so let's ask chat GPT how to make that happen so here it has given me six ways to do the interactions we can use the slicers Dynamic chart title although technically not an interaction it's still pretty good drill down options which is already there so it's pretty good as well data validation and uh and then filters and conditional formatting so let's go with slicers to add a slicer select the P table / chart go to options or analyze Tab and insert the slicer so let's do this I want to add a slicer on this so I'm going to go here insert slicer let's pick category for this and put that slicer there and now if I pick a category I can see that slicer updating and when I do this only this chart is updating not the other chart so I want to ask chart GPT how to reflect that across the board again quite impressive so it has figured out what to do which is to use the slicer connection feature in Excel so select the slicer you want to link to multiple charts go to options or slicer tools Tab and in the Slicer group click on report connections or connect slicer and use the P table connections to do this so let's do that let's select this slicer go here and click on report connections and let's just link this to all the pivot tables now and now everything updates anytime you click on the slicer all right that looks good the only other problem that I'm finding with this dashboard is if you look at my top salesperson report it's quite busy and I would like to just show the top 10 people not all everybody so let's ask that okay so what it has done is it has told me to filter the raw data to show the top 10 records that's not going to work for us we need to filter the pivot table so let's tell it again and now it has gotten the correct answer which is select the pivot table that generates the salesperson graph right click on the salesperson name within the pivot table in the context menu go to filter and then select value filter so we'll go here right click to filter top 10 and just click okay so now this is limiting just the top 10 and in the dashboard I only see the top 10 people for any particular category which is just awesome like I can see everything that I want without all the Clutter let's do a little bit of quick formatting on this I'm going to turn off the grid lines first so that everything looks nice and clean select this particular slicer and apply a darker formatting so it's kind of strong like that so here is our final dashboard it looks pretty cool we can pick a different category and instantly see the updated results it tells me what's happening at a quarter level which are our top countries top salese and top products for that particular category I can clear the slicers to see everything or I can pick one item at a time so now that we have made this dashboard you might think what next well I suggest checking out this video where I show you how to construct this kind of a dynamic business dashboard from this exact same data so we can still interact but this goes a little bit more you can see individual sales performance of the people in the team how things are happening very Trends top five products and visualize not just sales but profits and boxes and various other things so check out that video for more or go and ask chat GPT questions about your own data and get it to build something awesome for you whatever you do you're awesome and thank you so much for watching this video I'll catch you somewhere else bye