EASILY Make an Automated Data Entry Form in Excel

Make an automated data entry form in Excel.
🔥 Take our VBA & Macros Course: www.careerprinciples.com/cour...
🆓 DOWNLOAD Free Excel file for this video: careerprinciples.myflodesk.co...
In this tutorial you'll learn how to make an automated data entry form in Excel where you can add values, checkboxes, dropdowns, and even popups depending on your answer. Once a user clicks submit, the data automatically gets collected in a separate worksheet with a database. First we'll go over how to format the Excel file to look like a form, then we'll go over how to create the results sheet, followed by how to automate the whole process, and finally how to protect the Excel sheet so other users don't break it.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00​ -​ Format Data Entry Form
5:42​ - Store Submitted Results
8:18​ - Automate Data Entry Form
12:44​ - Protect the Form

[@KenjiExplains]

🔥 Take our VBA & Macros Course: www.careerprinciples.com/courses/vba-macros-for-business-automation

[@AZ4ZEL666]

THANK YOU Its easy to understand with your good explanation

[@Dr-Luvaz25]

Thank you Kenji I enjoyed this video as I was working with you , You're the best keep up the good work. all the way from South Africa

[@erikguzik8204]

nice video. Couple things you should change, in the protection tab, don't allow selection of protected cells, this way the cursor stays only in the cells the are unprotected, so it works more like a user form where you can't select the titles. you also need to unprotect the refers to cell for the Option buttons. The option buttons are trying to change a value in a protected cells. this is why you get the error message.

[@amcytechresource9607]

This is my first time watching your video and i found you do amazing while watching. Clear, direct, detailed and concised.

[@wrickeynelson9689]

Thanks Kenji, you make my work life easier with this. Well explained and have change a lot of my data entry .

[@droidfan]

The automation section was great! I never would have thought about that! Thank you!

[@usamashakeel2833]

This is good when you have 15 or less questions What about if you have 350 questions data entery of 500 respondents 😅. Sadly, i wish my professor use Google form.

[@brandon.T1531]

8:05 - You could omit the need to transpose (and by proxy, the "Raw" sheet) by moving the refence cells (C21:D30) to the top of the Data tab as a single row instead of as a column on the input sheet. Then, you could paste directly to a table on the same page using your process.

[@lalchhanhima_DarkHeart]

Yup, ikr?

[@Chrisdb851]

Thank you for simplifying this process

[@norizaamin9028]

Thank you sir. Your instructions are very easy to follow. I was able to automate my library book stocks. I currently in planning of using excel to automate a library lending system.

[@isdfkjhgd11]

This is so helpful!!! thank you so much for uploading this!!! subscribed! 🙏

[@donaldmacdonald4901]

Thanks Kenji much appreciated as always

[@swapnilshingwekar7359]

Your post are very simple and concise to understand

[@John-sl8cs]

Kenji - you are a genius. Thank you!!!!

[@carlospulidofalcon5656]

'm literally watching you while having lunch. Thanks for the entertainment/learning video.

[@KenjiExplains]

Nice! Hope lunch is a good one haha

[@kasm.pasaulan9672]

Thanks Kenji for this work. I would like to know if I can do the same macro for "update" as "submit" to update the data.

[@jeffreyguevarra3000]

Very helpful and easy to follow. Kudos!

[@iamLorenzoChatmon]

This was a very useful video. Thank you for creating it.

[@abdullahadam6187]

Easy to understand, thank you ❤

[@royalsolutions7684]

Thanks a lot for this amazing tutorial. Never knew Excel was so powerful .

[@KenjiExplains]

Glad it was helpful!

[@christiangarcia3795]

Great video. I wonder if the submitted data could feed a dinamic table as well.

[@mghargrave2863]

When hiding information that is off the end of the intended user work area it's generally more secure and less prone to data corruption to hide the rows and columns rather than match the colour.

[@rawconcept]

yeah right, but as long as the sheet is protected with the cells locked, making only your intended work area active, then your data remains intact...

[@Luciano_mp]

Very good, useful! Thank you.

[@SergMarAlamo]

excelent explanation, thanks Kenji

[@power-reconcile]

Well explained and thanks for sharing!

[@motivateme3244]

guy you did so well, i love the video it's understandable

[@abdulrahman-qg3sh]

Sir please make such kind of videos on Power BI But you are making excellent videos on excek ❤❤

[@user-ol2yh6ob6j]

Great video, you make learning looks easy.

[@geovannyolivero6241]

Kenji, just to let you know that You are the Best. Outstanding video

[@KenjiExplains]

Thanks so much 😁

[@ajayshivpuje3797]

Sir your videos are very useful. Please make videos on Power bi

[@ianrobertson5128]

Great Video. Ensure the cell link for the two radio buttons is unlocked before you lock the sheet. It should work after that.

[@sabeelahmed1157]

Even I had the same thought

[@amcytechresource9607]

very well noted

[@ancc1969]

Hi Kenji, thank you for all your excel tutorials. Can I trouble you to consider showing your viewers how to do a dashboard that tracks monthly dividends?

[@heshamabuzaid4293]

Thanks you so much, it was really clear and valuable.

[@andrea_weekenduae6058]

Nice video! Is there a way to have a message if some fields have already been inputted previously to not create a duplicate?

[@kevf2720]

Very informative. I have a few ideas for work that I will try this with.

[@PsycheKane]

Hi Kenji thanks for the video. I wanted to add that a solution to correcting the form buttons is by right clicking and going to format control, you will see an option tab for protection. Once you uncheck the "locked" option, it will allow the user to select the button even when the sheet is in a protected state.

[@Linda-jo8ys]

Mine doesn't work

[@jayvinbonajos3855]

Hi Kenji, I'm always watching your videos! Thank you for this tutorial. By the way, could I request an inventory management system in Excel? It will be used for a hotel and casino business. Thanks!

[@dany-hermannkouassi8987]

Great! We always learn something new with you. But here is an other way to generate a form more easily by creating the data array first then select a cell in the array and in the search console, Search for the function "form ". Excel will show a you a form based on the informations in the first row. However this video allows us to learn more functions. My methode is for lazy persons 😂.

[@drumswithmatt]

Thank you for this excellent video

[@putrakamangpu5033]

Thank you, I have a lot to learn here..

[@yichamroeun3948]

Very nice and useful.

[@worldwiderich93007]

Thank you Kenji... It is very good Video. Its save lots of Time.

[@franciscoeiras3711]

You only need to unprotect the cell where you link the option buttons, in this case E27. After you do that it will work

[@nishanth4323]

👏🏻👏🏻👏🏻 wow awesome explanation and good very easy to understand for new Learner's

[@mestosabinov08301]

perfect video I have a question in the "form" you have the item "Staff ID" how is it possible to make the "Staff ID" value so that I don't have to enter it manually, but it would always be new with a new record example: first staff value 1 second staff value 2 etc... something like n+1 always on a new record

[@marwanattya8067]

Simple and great 👍

[@maher_alzoubi]

thank you so much for everything 🙂

[@einoconsult5563]

Great content, can we do the same with Office Script

[@desilvakts2071]

Hi Kenji, in the macro where the data gets copied while the previous data move down, instead of this how can i do it so that new data gets added to the immediate row and continue to add to the rows below.

[@Angelicaaaaaaaa1_]

Hi, great step by step explanation. However, as I tried to run the macro program in my excel (MacBook) it says "The macro may not be available in this workbook or all macros may be disabled". I tried to save in xls format.

[@jaywindross6693]

Thanks Kenji, great video to follow! How would we stop the 2 other tabs (Data and Raw) from being viewable as there could be sensible information on there?

[@Linda-jo8ys]

Hide the sheets. Right lick on each sheet(Data and Raw) , you will see the option to Hide. To unhide them, right click on the Form or any unhidden sheet: it will show you all hidden sheets and you will be able to unhide them from there

[@rafayhussain4171]

I am doing acca now on what skills I need to build my career previously I had Excel knowledge

[@SandeepSharma-md2ex]

Amazing way of teaching

[@KenjiExplains]

Thank you for your comment :)

[@camlex6310]

Wow really great!!

[@Esgala2024]

what if i want to change the look of the responses page of the excel sheet? i want it to look like a decently looking document that i can print that has headers and footers and a company logo for example. Can i specify what cell is it going to insert the data inputted from the form?

[@PreachKreachh]

Brill Vid, learnt a lot

[@FRANELC-ss8sz]

SUPER BROO NOW I DONE THIS AUTOMATION WORKS REALLY GOOD THAN U 😍😍

[@i-jamesazubuike9208]

Very great and inspiring! Thànk a lot❤

[@davidchow923]

Hi Kenji, why the second time that I type a serise of data, it can follow the previous one and will not be replaced? Please let me know. Thanks.

[@miksosnowy1038]

interesting video. just why to format 1mln rows for such small form?

[@savezabbasi9733]

Awesome 🤩

[@optimistjourney2024]

Thanks

[@drumswithmatt]

Sir could please make a video for school payments automation per student?

[@paulabrink1532]

Hey Kenji, Great video! Got lots of answers here. Do you have any ideas on this: We have trainers that need to tell us where in the world they will be training. From date - To date, Country, and Time Zone. I will want to plot that on Excel. I think this form idea of yours is going to be great - so my question: How can I use the 2 dates - from and to - to populate themselves in a grid so that we can see all the dates in between as well??

[@kushangshah-be7hn]

can i do all this on website excel? I don't have inbuilt application.

[@erick.terrestrial]

You have to go to format settings on the checkboxes and unlocks them, and enable auto filters when you protect the sheet

[@pizmen24]

how will we send out the form? without the data and other sheets. I think it is not possible. but nice learning vid

[@investorritel5298]

Sir, please make about stock market❤❤

[@shweta-bisht]

Loved it 💕💕💕💕

[@KenjiExplains]

Thank you!

[@henrychow8849]

The reason the Option radial buttons don't work, is because the cells that are linked to the radial buttons are also locked. By default, VBA and Form Controls can't change values (and formulas) of protected cells, although this can be overwritten if you protect a workbook or worksheet using VBA. In this case, a simple solution without manual VBA coding is to make sure that the the referenced cell-link is unprotected. You can do that by either directly editing the properties of the cell, or better still, use cell styles.

[@dany-hermannkouassi8987]

I think you're right.

[@jeffgeneo]

Can you run this in excel web?

[@shafiqulsymon5482]

Hello , If I want to edit my previous entry after clicking the submit button, how can I do it?

[@user-ly3ox7rc1d]

Hi kenij sir, i'm rimon from bangladesh and learn excel video to your channel,You are very interisting now i want to your help. i want an best data analyst please suggest your opinion

[@aboapid]

Is there a way to choose cells shift up ؟

[@user-uf1yn4wg9d]

thanks for that but how can i refound it after that

[@milanmmng]

Bravo

[@muhammad__nouri]

Great

[@williamwedding5317]

Thanks!

[@KenjiExplains]

Thank you for your support!

[@danielsomera9396]

Can this work in iPads?

[@entrustedhub]

What causes my micro to shake, sometimes freeze until I switch off my computer? Thank you

[@theborgv]

How can i make the RAW data sorted automatically?

[@JustinHorrocks-vh3em]

I added a date field to track date of entry and it's not reflecting correctly in the Data Tab. Any guidance?

[@DendisTomas]

Good video, but I think in this situation is better to use MS Form or PowerApps. It is not so complicated created this there and users do not have access to stored data. And it is more "mistake-proof". You can protect everything in excel, but in MS form or PowerApps you no need to do it, because there is nothing around. But this is only my opinion, I think that everybody prefer some way. 🙂

[@jaimemoran5428]

Espectacular @KenjiExplains !!

[@techcorner2024]

Hello, I have a problem. After submiting the form for the 4th time, I got an error report and I couldn't deburg it. Please advice on what to do.

[@garmrdmr]

Not sure if anyone answered - but to protect the check boxes: i grouped them then selected the group and dbl clicked Format shape window comes up on the right of the page. select the third set of settings - select the "locked" option finally protect the sheet as Kenji did. now the options can be selected /changed but they cannot be select for editing

[@sunnindawg]

Cool

[@fssacko]

Great thank you lot Mr Kenji

[@AliAlnaser-su5hl]

Great work and well explained I have one question though..how can we intigrate a condition on the submit button so that they cannot click on it unless they complete the whole entries or at least the essential ones which we specify in advance?

[@user-er3du2co2c]

You could but you would have to edit the macro, and essentially write the VBA code to it. I think in this tutorial he was steering away from showing any VBA code that might scare a lot of people off. However, if you wanted to edit the VBA, you could add a simple IF statement that would exit the sub immediately if not all fields were filled in. It would look something like this: If ThisWorkbook.Worksheets(Form).Cells(4, "C") = "" Then Exit Sub If ThisWorkbook.Worksheets(Form).Cells(6, "C") = "" Then Exit Sub These would be put at the very top of the subroutine and be repeated for each cell. Basically this just checks the cell and if it equals "" which is two double quotes which means empty, then it exits the subroutine without doing any of the other steps. Notice I used the name "Form" for the sheet because that is what the video creator named his worksheet, and You can see that you list the row before the column, and normally Column is a number and "C" would be 3, but you can also place the letter in quotes and I did that so you could easily see that the row comes first separated by a comma and a space then the column. it could also look like this: If ThisWorkbook.Worksheets(Form).Cells(4, 3) = "" Then Exit Sub If ThisWorkbook.Worksheets(Form).Cells(6, 3) = "" Then Exit Sub Simply add this for each cell you want to make sure has info and viola! done If you wanted to go a tiny bit further and let the user know they have to fill out all fields, then you could change those lines to this: If ThisWorkbook.Worksheets(Form).Cells(4, "C") = "" Then GoTo MessageUser: If ThisWorkbook.Worksheets(Form).Cells(6, "C") = "" Then GoTo MessageUser: And then at the very bottom of the subroutine put this: Exit Sub MessageUser: MsgBox("All fields must be completed.") Exit Sub

[@sunnysun6567]

If we use power pivot , why we need to use VBA ? Is any difference ?

[@henrychow8849]

Power Pivot does data transformation, but not add, delete and modify data.

[@sunnysun6567]

It does …

[@c.pvandenberg8120]

Question, how do i construct the formule on 7:11 if i have 3 checkboxes.

[@c.pvandenberg8120]

i actually found it in one of your other tutorialsallready,. tnx

[@pradeeppadeliya]

when click on evening check box it is showing true and false but in your videos it is showing as 2,1 numbers ... also tell me how to check evening and morning at a time when i am checking another box it is not auto uncheck..... i am unchecking it manually... Please tell me how should i correct it...

[@thefrankperspective4247]

There has got to be a way to do this without macros. It’s like no one realizes like every corporate server blocks these now…

[@henrychow8849]

I'm not sure if you can.... because new data is added. using formulas and arrays, you can do a huge amount of data transformation without programming, but I don't believe you can do adding (and deleting data) without macro in this way.

[@thefrankperspective4247]

@@henrychow8849 - ugh. Can’t use my personal computer for this, can’t use my work laptop… so lame when one actually knows how to do all of this, and is blocked.

[@rafayhussain4171]

Plz make a video on index match formula from scratch to advance

[@KenjiExplains]

Hey I already have: kzbin.info/www/bejne/rYWmqYqmqLesd8U

[@rafayhussain4171]

@@KenjiExplains thanks sir

[@user-uz1ho2xh4n]

You are my love Kenji

[@rodeld.rengel4905]

How about the data and Raw sheets? Why dont you protect it as well. Tried protecting all sheets but a pop up error message appears. Meaning, you cant protect Data and Raw sheets, which is vulnerable to being erased or deleted.

[@patrixer1]

can we add a command where it does not submit when a 0 value is added

[@QD09876]

Wow….

[@TGP_JC]

How can you send these to people via email, and still have all the data collected?

[@JannesGajewski]

Everytime i try it i get the Failcode 9 and i try to put it into a Form to submit every Data into a Power Bi Form, can anyone help me i always do the same stepps as him