Automate Data Entry in Excel with AI and VBA

Hello and welcome to this Microsoft Excel tutorial where I'm going to show you how to build a nice little form like this that allows you to input data on another worksheet as easy as this. Success! And you do not have to learn a single bit of VBA, code, macro or automation. What we are going to do is we're going to use AI and I'm going to tell you exactly how to do it. I'm going to give you the exact prompt that you need to use, tell you the few places that you need to change for your data set so that you can make a beautiful, wonderful, amazing input form like this. And you can even tab through it for faster data entry. This tutorial is going to save you a ton of time and you can download the AI prompt for this tutorial for free on teachexcel.com. I'll put a link to it below this video and talk more about it when we get there. In the first part of this tutorial I'm going to show you how to automate taking the values from here and placing them on the data worksheet. Then at the end I will show you how to make this form look a bit nicer and so that you can tab through the input cells. You'll notice already that I've turned back on the grid line so you can see that this is just a regular worksheet. This is not a special, unique, or crazy user form. You don't have to do anything difficult, just a few little tricks I'll show you at the end. The first thing to do is to get some cells that will be your input cells. I have labeled them item, type, quantity, unit price, and notes. And I've made it white with different background colors so it's easier to see. But you'll notice that when I click them, so this is the item input, I have named it item up here. That's going to make our life so much easier. Down here for type, same thing. And all the way down, quantity, unit price, and notes. And all you have to do to name a cell is simply click that cell. Let's click one over here and let's change the background color. How about we make it yellow. Go up here, new, name. And now you have a named cell. Click this arrow to view all of the named cells. Click a name and it will go to that cell. Once you've done that, you've got the bare bones version of your input spreadsheet. And everything else is just to make it work a little bit easier and look a little bit nicer. I'll cover that once we get the code in here. But now let's set up the data spreadsheet. All you need to do is to figure out where you want each of those input cells to go and give each one of those columns a title. A nice, neat little title. And lastly, name your worksheets. Data is where we are going to store the data. Input where we have the input. After that, get your AI prompt. This is our prompt and I'll tell you what you need to change right now. But make sure that you download this from teachexcel.com so you don't have to pause the video and type it all out. I'll put a link to this tutorial below the video. It's completely free. All you have to do is create an account and then you can freely download this. Once you have that, I have highlighted in bold what you need to change. This is the name of the input worksheet. This is the name of the worksheet where you'd like to place the data. Then we go down here and simply type the name of the cell as you input it just a moment ago in your spreadsheet. And then the column on the data worksheet where you want it to be placed. Do that for all of your inputs. Then we can CTRL A, CTRL C, select everything, copy it, go to our favorite ChatGPT 4.0, really great guy, paste it in, hit enter and wait for the magic to happen. I'm going to fast forward through this. It's opened up a canvas for us, a great feature of ChatGPT and provided the entire macro right here. It's amazing how easy this is. Once you get your prompt right, your life is going to be made so much easier. We're going to go here, we're going to copy this, go back to Excel, hit ALT F11 to go to the VBA window, go to insert, module. You'll get a module over here like this. Paste in your macro, go back to the spreadsheet and let's test it out before we connect the button to it. Test item, then we can go to type, let's go B1, now hit ALT F8 to get the macro window, select the macro that we just input, transfer of values, click run, success, cleared from here and over here. And you didn't have to learn any VBA. You didn't have to learn macros. All you had to do was get your prompt right and I've already got it for you. Now let's go ahead and make this more usable, okay? There are a few tricks here. So let's first input a button. We're going to go to insert, illustrations, shapes, rounded rectangle, my favorite type of button, draw it, add item, I'll hit escape, home, center, center, bold, then what we can do is right click that guy. It's off the window but assign macro, transfer values, the name of our macro, okay, click away. Now let's test it out again. Item 2, type A1 or 11, 1 and notes. Click away, add item, perfect. Next up, let's make it so we can tab through these. Click all of them, so click the first one, hit control, then the next, next, next, next, right click off the screen but go ahead and click format cells, protection, uncheck locked, then we hit okay, review, protect, protect sheet, uncheck select locked cells, okay. Now it automatically goes to the first input and when I hit tab, it goes only to our inputs. Beautiful. That actually will save you a ton of time for repetitive tasks. The next thing, if you want to space these guys out, notice that it goes from 5 to 6, very small row, then 7. Let me unlock this guy, unprotect, so we use small rows as spacers. The same with a small column up here and how about the rounded corners? That is a beautiful trick. Look, I'll click the corner, the edge and I'll drag it away. This is a shape with no background that has rounded corners. All you have to do to make one is go insert, illustrations, shapes, once again, the beautiful rounded rectangle. We'll draw one, we'll click the orange guy to make the corners a little bit less rounded, then we'll go to shape, format, shape fill, no fill, then we'll go to outline, weight and make it a lot bigger. Then all you do, drag it over your form. There you go. That's all for this tutorial, but now what I'm going to do is to go back to the prompt and go through it and talk a little bit about it. First things first, tell your AI what it is. You are not a human, no. You are an expert in Excel, VBA and macros. You will write me a VBA macro for Excel. That way it knows what's going on. Then we want to structure the rest of it in a very logical way. The easier it is for you to read it, the easier it is for the AI to read it. So really the most important things here are going to be that I have a numbered bulleted list right here that will paste nicely into the chat GPT prompt and down here. How we tell it where to get the values from and where to place them. Notice that I have brackets around the columns and I have a format that is repeated and surrounded with a little tag that says, hey, that's where it begins. Hey, that's where it ends. This is very important if you want to get consistent outputs. And you'll notice up here, most of these items are going to be pretty basic. Each value must be input into its own column on the same row. But really important, this guy right here. The list below includes the name of the cells with the values and also the column into which those values should be input. The list is between the, in quotation, start list and end list tags. The format of this list is name column. Notice the brackets where name is the name of the cell and column is the column into which the value should be placed. We are basically making pseudo code that says, hey, this is exactly how you program it. This is exactly where the values are, exactly where they should go. And we've made it dynamic enough so that you can add new value. Just like that, into column F. Name a cell new value into column F. You only have two cells. There you go. And of course, don't forget item number one. Take values from the input sheet that is named input and place them on the destination sheet that is named data. And if you are interested in getting more into AI prompts, it's worth it to go through this and see how I told it what to do. But I don't think there's that much value in me explaining every single little piece of it right now. But AI prompts are pretty amazing. The better you get at them, the easier it's going to make your life. So, so, so, so much easier. And if you know a little bit of VBA already or a lot of VBA, then this is going to be great because sometimes it does mess up. And if you know how to fix it, then it'll be just save you so much time because you didn't have to write all of that boring, basic, repetitive code. You can just go in and fix the few places where it might've messed up. This prompt works pretty well. It hasn't messed up for me yet, and I've used it a few times for this tutorial and before, but I do highly recommend that you learn VBA if you have the time for it and then use AI to create your macros, to augment your skills and speed up your workflow even more. We have a full VBA course on teachexcel.com. It's a really great course with over 200 tutorials that'll show you how to automate your entire spreadsheet, send emails, import, export, slice and dice and pivot table and chart and do so many amazing, beautiful things with your data. Highly, highly recommend you take that. I'll put a link to it below this video and it'll probably be on sale as well. So give that a look. And I think that's about it for this tutorial. I hope that I've saved you time. I hope I have made your life better. Please don't forget to subscribe, hit the bell icon, give us a thumbs up, share it with your friends. The YouTube algorithm these days is hitting us very, very hard since, well, AI came about. Cheers guys. Have a great day. Have a great week. I'll see you next time.