Supercharge Your Microsoft Excel Skills in 2023 with ChatGPT

1. ChatGPT for Excel Intro: This course will introduce you to check GPD and demonstrate how it can be used to significantly enhance your Microsoft Excel skills. You'll learn how to use chat GPD, automate tasks, perform complex calculations, generate reports, and much more. Let's briefly see what is Chad GBD and what is Excel automation. Before we dive deep into the course, chat GBD is a computer program created using AI technology, our artificial intelligence technology that can understand and generate human like text. It's like having a virtual assistant that can answer your questions, write articles, summarized texts, and even hold conversations with you to start using it. Just go to chat, open ai.com, create an account for yourself, saved your username and password, and start asking what you want from your assistant. That's it. Let me tell you this fact. From the day I started using chat GBD, I feel like I have a superhuman assistant to help and support me in my daily tasks. If you are new to this, you also feel the same way pretty soon. Now think about this. What do you think this powerful artificial intelligence will do? Do job market. We have our very witnessed a rapid shift towards automation in various industries. With advancements in artificial intelligence, companies are increasingly turning towards automation to increase efficiency and reduce costs and improve productivity. While this has its benefits, it also has its downside. Automation has lead to job losses in various industries and it is predicted that this trend will continue in the coming years. As an employee, it is important to stay relevant and have skills that are in demand in the job market. One such skill is the ability to automate tasks using tools like chat, GBD, and Microsoft Excel. These tools can be used to automate repetitive and time-consuming tasks and freeing up a valuable time that can be used for more strategic work. By taking this course that teaches how to use chat GBD with Excel to automate tasks. Employees can learn how to work smarter, not harder. They can streamline their work processes, increase their efficiency, making them valuable assets to their companies. Additionally, employees who have these skills are less likely to be replaced by automation, ensuring job security. It is important for employees to take charge of their career development and continuously learn new skills that will make them valuable in the job market. Learning how to use chat GBD with Excel to automate tasks is one such skill that'll be in high demand in the coming years. By taking the initiative to learn the skill, employers can future proof their careers and secure decision in the workforce. And who is this course for? Anyone who wants to learn how to use chat GPD to master Excel in general, in Excel, automation. In particular, especially people who are working in corporate and use Excel regularly to process their data. You just need basic knowledge in Excel to start with this course. That introduction. Let's get started. 2. MS Office Installation: To learn macros, functions, and formulas, you need Microsoft Excel software on your computer. If you already have it installed. You may skip this video. If you don't have the latest version of Office and won the software free, please follow the instructions in this video. Please note that this office we are downloading is directly from Microsoft website. They allowed these tools to download for free for IT administrators. We are going to download those files. This is not a paid licensed version. Paid version is subscription-based and you'll need to pay for it every year. What we are downloading is the free version given by Microsoft for IT administrators. Let's go to Google and type in Office deployment to open this link and click on download. The file is downloaded. I go to show it in the folder. Then I copy it and come to desktop. I create a new folder here and name it as MS. Office. I go to the folder and paste in the copied file. Now I create another folder within this MS Office folder. I'll name it as Office apps. You can give any name you want. Now, you need to extract the configuration files in this new folder. Let's right-click on this and run as administrator. I accept the license terms from Microsoft and continue. I select the path of this file. It's in the desktop. Ms Office and Office apps. Files extracted successfully. Now I go to this folder. I have four different files here. I just need Office 2021, enterprise dot XML. So I selected the other three files and delete them. Now at this same window, press Shift key, and right-click on the mouse. Click on open PowerShell window here. Here just go to the text section of this video. You will find this link there. Just copy and paste this link here in this window and press enter. Please don't enter the text manually. You may do some typos. So just copy and paste from the description section of this video. After you based it. Press Enter. Press yes. To permit the windows. The Office 2021 download has started late for some time. For the download to finish. Once it has done Search for Excel, you can start using Excel now, for the purpose of this course 3. MS Office Activation: After installation of Microsoft Office 2021, when you open Excel and go to File Account, you see the product requires activation here. My product is already activated, and that's why you see that here. But in your case, you'll see that the product requires activation. Just follow a few simple steps to activate it. I've given the activation key in the description section of this video. It looked like this. The first line of code is for 32-bit PCs, and the second one is for 64 bit pcs. First of all, check if your PC is 32-bit or 64-bit by right-clicking on this PC. And go to properties. Here, you'll get to know whether your BC is 32-bit or 64-bit. Now, Open command prompt window and copy and paste this code here. And hit Enter. Then copy paste the third line of code and enter. This will install a volume license. The next step is to activate a muscle office with Canvas over. Here. I've given two sets, of course. Try the first code first. And if it doesn't work, try the second one. Let me copy paste the first chord. As you can see, the MS Office is successfully activated. After this process. If you open Excel and go to Account, you'll see this product is activated 4. Prompts in chatGPT: Chat GBD is a chat bot that converse fluently with its users. Prompts are like ideas or suggestions that you give to a computer program to help it start a conversation with you. Just like how you might ask a friend a question. To start a conversation. You can give chat GBD a prompt to help it understand what you want to talk about. E.g. you could give it a prompt, like tell me about Microsoft Excel are dark about chat GPD, but these are generic prompts. But the more detailed your prompts are, the better it is for chat GBD to give an appropriate response. Chat GBD will use its artificial intelligence to come up with a response that makes sense based on the prompt you give it. Let me give you an example to make it clear. If I want check GPT to explain an Excel formula, I would ask like this, forget about all my previous instructions. Explain the Excel function x lookup in simple words as if you're teaching in a ten year old kid, generate a data table and explain with an example. There you go. If you're not happy with the response, you may ask what exactly you expect. Chat GBD will try to answer as per your requirement. Tweak your question in different ways to get what you want. If the answer is do simplistic, you can ask for more complex examples. Yes, sometimes the response might be wrong are silly. You'll need to continue to converse with a bot by rephrasing your questions. If you are completely new to this, Get started by throwing in some random questions to chat GBD and see how it response 5. Project 1 Write macros to send automated invoice to debtors: The purpose of this course is to teach you how to use chat GBD and finish. Some of the complex tasks are automated, some of the repetitive and mundane tasks in Excel. So I'm not going to cover any of the basics of Excel to like what our rows and columns and what are the various tabs and exon. You can learn all of them in any basic Excel course or for free on YouTube. So let's straightaway, gets chartered with Excel macros and how Chad GBD can help to generate macros to automate your tasks. Our first project is automating an e-mail and wise to debtors. Let's say you have a list of people who owe money to your company. We call them guitars. In column j, we have their e-mail addresses, and in column B we have the amount they owe to us. Now we want to write a macro that send an e-mail to all these debtors telling them they owe so much money to us. If you know, macros, you know, you can do that easily. Now, let's see how Chad GBD can help here. Now to start with macros, I'll follow three simple steps. Step one, I'll save this Excel file as Excel macros file. For that, I need to save the file as x as m file. Normally Excel file extension is XLS. Now I need this file as XLS file, which is Excel macro file. It simple. Go to File, Save As and select Excel S M as the extension. Now step two, let's enable macros. I go to the ribbon up on top, right-click and click, customize the ribbon. On the right side. Let's check the developer checkbox. Click on Okay. Now let's go to Developer tab. On top-left, you'll find Visual Basic. Let's click on that. From your working Excel file. You can come to this page by simply pressing Alt F11. It's a toggle key. You can go back and forth between developer mode and worksheet mode using alt F 11. Step three, I'll go to insert and insert a new module. That's it. We've enabled macros in Excel. Now, I go to chat GPD to write a prompt. I copy paste the prompt from here. Just pause the video and read the prompt and understand it. Look at that. Chat GBD is generating Excel macro code for us. Here. It also tells us what we need to do so that this works well in Excel. Now I add another prompt that's good. In addition, include suitable text in the e-mail so that it should look as an official email center of a company that includes a suitable email text as well. Now, let's copy this code. Go to Excel and paste in within the module. I go to Tools and references. Here, I ensure that MS outlook object library is enabled. Now I've pasted in the code from Chad GPD. I'll just ensure that the columns are captured properly from Excel because that's where the base data is stored. I'll save this file again. Now I go to view the macros. We can see the macro we just created. Let me just run it. And boom, the e-mails are sent. We need to have Microsoft Outlook setup send such e-mails. As you can see, the males are in my sent items 6. Project 2 Write a macro to create Index sheet: Let's see more examples on how to write macros with Chad GBD. Now, let's say I have an Excel file with several sheets. In each sheet, I have a data store. Now, navigating between these sheets is a real challenge. If I want to have an index sheet with table of contents, in that it would make the job of navigating between these sheets much easier. Now, I want to create an index sheet with Table of Contents hyperlinked to the respective sheet. I need to write a macro program for this. Let's see how we can do this. With Chad GBD. I typed in this prompt in the chat GPT. Forget about all the previous instructions. Imagine that you're an expert in creating Excel Macros. Write a macro to create an index sheet in Excel. The index sheets should grab the names of other sheets in the file and create an hyperlinked text within the index sheet gives us suitable heading to the index sheet and list out all the names below with a hyperlink to the respective sheets. There you go. The macro code is ready. Now let's start implementing the macro using our regular steps. First of all, I'll save this file as Excel. Awesome. I'll call it as index dot XLSX. Now I copy the code from chat GPD and go to Developer tab. Visual Basic are simply I can use Alt F11 to toggle between the Excel worksheet and the visual basic coding sheet. I opened the module and paste in the code. And I save this file. Now let's go to run the macro view. Macros. There you go. The index sheet with hyperlinks is created 7. Project 3 – Macros to Creating random text: Many of us need Excel files with some random texts to work with. We can learn to work with many of the Excel functions and formulas. Only when we have Excel sheets that data. Manually creating data on an Excel sheet will take a lot of time and effort. How about automating it with macros? In this project, we'll create some random text in excel sheet using macros. As usual, I open a new Excel sheet and save it as XLS m-file. I give all f 11 to go to visual basic coding area within Developer tab. Now I open a module. Okay, Let's go to chat GBD now to give it a prompt. Forget about all the previous instructions. Imagine that you're an expert in creating Excel macros. Can you write an Excel macro, degenerate dummy text on a sheet from a to F B12. The first column should contain unique names, other people. The names should be people's names like ROM, reader, rank, get, angel, etc. Try not to repeat the same names. The next five columns should contain their marks scored in five subjects all and do digits. All of them can be random, but include column headers. Suitably. I must be able to generate this text in more than one sheet of an Excel file. Okay, We have the code ready. Let's copy this. Now, I go to VB module and paste in the code. I save it on effluent to go back to the Excel working sheet. Fa, to open the macros. Now I run the Macro that ego, the random text is ready. Let me open another sheet within this file. And I run macro on this sheet as well. Okay? When I run the code in the second sheet, macro is not responding to that. So let me go back to the chat GPT. I am not able to generate random text in the second sheet. Change the code so that I can generate random texts on more than one sheet within a file. Okay, Chat GBD has revised the code. Now, let me copy paste it into the Excel module. Let's see if this works. Yes, Now it works. Let me try on another sheet. Yes, it works fine. Now, this is how you should do when Chad GPD, make some mistakes. Clearly point out the mistake descriptively and ask what you want from Chad GBD 8. Project 4 – Macro for creating a Mortgage Calculator: At some point in time, we would have taken loans from the banks. It could be a whole loan or a car loan. For the loan, we pay monthly mortgage or equal monthly installments or EMI. How about creating a mortgage calculator? Our EMI calculator in Excel with chat GBD. Let's do that now. I create a new Excel sheet and name it as EMI calculator. I save the file with XLSX extension. Now alt F, the goto VB coding space. And open a new module. I go to Chegg GBD and give a prompt like this. Forget about all the previous instructions. Imagine that you're an expert and creating Excel Macros. Write a macro that create a mortgage calculator based on the input data given all amounts in USD. Max number in decimal is two digits. There you go. We've got the code from Chad GBD. Now let's copy and paste this code to Excel VBA module. I save it Alt F11 to go to Working Sheet View Macros. I double-click to run it. Now it is asking us inputs. So let me enter them. There you go. We got the output details 9. Project 5 – Using Chat GPT to generate functions and formulas: In the previous example, we generated macros to do mortgage calculations. How about creating a function to calculate mortgage amount by yourself? Let's say you have an Excel file with details like loan amount, interest rate, and loan tenure. You want chat GBD regenerate the formula to calculate mortgage a monitor. Can we do it? Yes, we can. Forget about all the previous instructions. Imagine that you are an Excel expert and creating Excel functions and formulas. Given a function to calculate mortgage payment. In cell B2, I enter the loan amount. In cell B3. I enter the interest rate in percentage in cell before I enter the loan tenor. In years. The final monthly mortgage amount should be in two decimals. That you go. Chat GPD has given me a function. I copy this and paste it in as text. The answer is already printed. Like this. The options are endless. I suggest you to kick-start your creativity and try out many such tasks. All of them may not work in the first try, keep giving your feedback to chat GBD and revise your prompts suitably 10. Project 6 Generate a formula with ChatGPT: In this project, I'll show you how we can generate a complex Excel formula with chat GBD. Let's say I have salesmen wise sales data in Excel. I want to calculate sales commission for the salesman. It is kind of complex because the commission has four slabs. Higher the sales they do hire the commission, they earn. Let's go to the chat GBD to generate salesman wise data because I don't have one to show as an example. So I'll generate the data table using chat GBD. Here you go with a prompt to generate the data table. First. Generate a data table to show the following. A two to 830 shows names of Salesman. Names are random. B2 to B3 shows sales numbers in INR. The numbers are between INR 10,000 to INR 100,000. Suitably provide column headings. Format the table in such a way I can copy paste it into Excel. So we have the data table ready. I'll copy pasted into Excel. Let me add a column. It shows sales commission. Now, I want to calculate sales commission for the sales numbers. Let me give a commission structure, recharge diabetes so that it can generate a formula, generate an Excel formula to calculate sales commission. If B2 is less than 100000 commission, if B2 is 10,001 to 20,005% commission if B2 is 20,001 to 40,010 per cent commission. If B2 is 40,001, 60,020 per cent commission. If B2 is more than 60,030% commission. So the formula is ready. I will copy paste it into Excel. You go, the commission is generated. I'll double-click here to copy the formula for all the rows. Now, let's make it even more complex. Randomly add a region for each salesman. I want chat GBD to check the region also before it calculates the commission. Here's the prompt. Generate an Excel formula for the following. If C1 is not our salt and b1 is less than 10,000, Commission is zero. B1 is 10,000, 20,000 commissioners, 5%. B1 is 20,000 to 40,000 commissioners, ten per cent, b1 is 40,001, 60,000, Commission is 20 per cent. If b1 is more than 60,000, Commission is 30%. If C1 is East are vest, and b1 is less than 10,000, Commission is zero. B1 is 10,001 to 20,000. Commission is ten per cent. B1 is 20,000, 140,000 Commission is 20 per cent, b1 is 40,001, 60,000 Commission is 30%. If b1 is more than 60,000, Commission is 40 per cent. As you can see, more commission is paid for salesmen from east and west compared to salesmen from not insult. Now, let's see how chat GBD generates the formula. So we got the formula. I'll copy paste it. It should be B2 instead of one. But anyway, let's copy this and the next row. There we go. We got the result. See it's a very complex formula. But Chad GBD made it easy for us. 11. Learn Functions and Formulas with ChatGPT: As you know, there are many functions and formulas in Excel. If you just know the name of those functions and want to know how to use those functions or formulas. You can use chat GBD learned the syntax of those functions and their explanation are already there in Excel, but Chad GBD can simplify the explanation to a great deal. Use this prompt to make any function easily understandable. Please explain this Excel function. Dash in simple words with examples, like you're teaching it to a ten year old kid. If required. Printer detailed data table first. Then show the example. If you're not happy with the examples asked for more simple or complex examples, chat GPD will oblige. In this template, you can replace the dash with any function you have in mind. Let's say we look up our index bar, match function, our average are some IF functions. If you don't know which function to use for which problem, then be descriptive on chat GBD so that it can tell you which one to use. How can I do that? First of all, I want to give the source data table format to check GBD. I would say I have my source data table in the format given below. And give a format like this. Now, if I want the Science and Math mark of a student to be printed in another sheet. Which formula or function to use. For this chat GPT gives the result. We don't want to read this full explanation. We just learned index bar match function can do this. Now, we'll go back to our template and use that to learn about Index Match function. In Excel, you can combine functions and formulas to solve complex problems. How can you do that? Just ask Chad GPT, how to combine two or more functions in Excel. Explain in simple words with examples. Like you're teaching it a ten year old kid. If required, print a detailed data table first, then show the example. As usual. If you're not happy with the examples, you may ask, give me more simple or more complex examples. Likewise, you can learn how to create complex Excel models using chat GBD. Just ask how to create a complex Excel model that includes functions, formulas, and macros. Explain in simple words with examples like you're teaching it to a ten year old kid. If required, print a detailed data table first, then show the example. There you go. A fantastic example that uses functions, formulas and macros are given by chat GPD 12. Learning Data Analysis using Pivot Tables and Pivot Charts: Pivot tables are a powerful tool to analyze complex data on an Excel sheet. Let's see how we can use chat GBD to learn to create a professional looking pivot table with all the bells and whistles, pivot charts and other advanced features to analyze the data. The first and foremost requirement to create a pivot table is data. Let's create that are using chat GBD. I give a prompt the chat GBD, to create a data table so that I can create a pivot table, create a data table, so that I can use it to create a pivot table. A2, A3, to contain ten different random salesman names like John, Philip, etc. B2 to B3 to contain four regions like north, south, et cetera. Again, random for all the salesman, C2 to C3, to contain different computer products like laptops, keyboards, mouse, Windows, software, etc. All of them random. D2, D3 to contain months like Jan, fab, etc. Again, they're all random due to eat 30 to have sales numbers 1000-100 thousand, depending upon the price of the products sold. You are suitable heading to the table columns. So we have a data table ready for use. If you want more data, you can ask for more in the same format. I mean, you can ask, give me 30 more rows of the same data. For the purpose of this video, let me proceed with the present era. Now, I copy paste this data into Excel. Now I asked Chad GPD to explain step-by-step to create a pivot table based on the above data. I want it to look professional and include all the advanced features of a pivot table. Chad GBD gives us various options to implement. First of all, let me insert a pivot table. I select the entire data. I go to Insert pivot table. Now I dragged the salesman in the rows region, also in the rows. I keep the products in the filter and months in the column. Now, the table looks like this. I can drag and drop them in different ways. The change the table format. I can see salesman wise table keeping the products in the filter are I can also see product wise table keeping the salesman in the filters. Or I can see a region wise table keeping the products and the column. Or I can choose the C month wise data. So I can move the fields as per my requirement. To make it easy for us to read and analyze the table, I want to add slicers. I go to each field and right-click and add that field as a slicer. Now with slicers, I can easily filter out what I don't want to see. If I want to see the performance of an individual salesman, I can do that. Or if I want to see the performance of a particular product, I can do that as well. Now, I choose the table and go to conditional formatting. And I can set a condition or a rule to see that on the chart. And I go to format table, but change the design. And when I click on the pivot table, I can see these two options. I go to Design and see options like subtotals, removed are added, granddaughters removed or added, or changed. The report layout. I go to PivotTable, Analyze, and add a pivot chart. Now, when I filter the table, the chart automatically changes. If you have any specific questions about pivot tables, ask Chad GBD and get the answers. That's data analysis using pivot table with Chad, GBD is help 13. ChatGPT for Data validation in Excel: Now I have salesmen wise sales data. Let's say I want to learn what is data validation in exon. So I asked Chad GBD like this, I copy paste the data set first and ask. Explain what is that I've added nation with the below data set. Show me an example and teach me as if you're teaching it to a ten year old kid. So they got the answer. Let's see what it says. Okay? Now, if I want to validate the user inputs in this column, I can do it. So if a user enters any other value apart from what I want, Excel will show an error. That's our validation. Now, let's implement it. Let's say I want to validate the region and month columns. Because regions cannot be other than these four regions. And months cannot be other than 12 months of the year. If a user makes any typo, Excel will show an error message. So if a user enters anything else apart from these four regions in this column, are anything else apart from these 12 months in this column, excel will show an error message. First of all, I choose the four regions and give them a range name. I call them as valid regions. Now, this is the source data for the Excel. Excel will refer to this to validate the user input. Now, I choose the entire column, because in this entire column, I don't want to see any other names except these four region names. Now I go to data and data validation. In the allow field. I choose list. Now, Excel asked for source data to refer to and validate the user input. I give the source name as equal to valid regions. Because that's the name we gave for these four regions. Now, you can see the drop-down with four region names. I can simply choose the region names from the drop-down. If a user enters any other name here, they'll see an error message. Now, let's do the same for the months. I choose all 12 months and give them a name to that rage, Let's say valid months. Then I choose the month column. Go to data. And data validation. Choose the allow field as list and give the source data name as equal to valid months. That's it. We have a drop-down menu for the month column as well. 14. Asking more from chatGPT for Excel help: Handling a large data, especially when it runs into thousands of rows and columns, can be really challenging. Can we take help from Chad GBD on how to handle it? Yes, we can just ask Chad GBD like this. I have a large data set that runs in the thousands of rows and columns. How can I simplify and handle it with ease with some advanced Excel tools? How do you go with some solutions? Now I can explore the solutions one-by-one, asking Chad GBD how each of them can help. Excel has in store a big list of add-ins that we can use. The power of the analysis. I can ask Chad GBD like this to learn some of the top adage. Show me ten most popular Excel Add-ins. Now I have the list. I can ask Chad GBD to explain how each of them can be used. With an example. If you are a regular Excel user, excellent shortcut keys can be very useful. To save some time. I asked chat GBD to give a list of excellent shortcut keys. Show me the list of shortcut keys in Excel. So Chad GBD has given a list. I can take a brand and stick it on the wall above my laptop so that I can refer to it whenever I need