Create form in excel with few simple clicks. Yes, you can create form in MS Word, MS Access or any other kind of form builder software. But I think excel is simpler than MS Access, at the same time it makes your life easier than Word.
I will suggest not to waste your time in access if you don’t have huge data (more than many thousands data set) in background to show output in the form. Create form in Excel to make your life easier allowing some formula in the form. Today I am not going to show any data link with the form, you will get it in my another post.
Today, I am going to show you how to create form in excel (like bellow one) in just 5 minutes. As an example, I am going to create a result card for a school.
You need to know simple SUM and nested IF (if function to work with multiple logic) formula of excel to create form in excel.
Step 1: Go to the VIEW tab in your excel and unmark the “Gridlines” from toolbar.
now you excel worksheet will look like a blank MS Word file.
Step 2: Count how many column and row you need in your form to accommodate necessary information of a student. In my case we need 19 row (2 to 20 row) and 7 column (B to H) for my form. I kept one column (A) and one row (Row#1) out of the form. This is just a good practice but not mandatory. Now select those column and row and create a outer-line border with a color and size of the border you like. All these you will get in HOME tab.
Now it should look like a black form.
Step 3: Decorate your form with relevant information like logo, a title, student (in your case it can be customer) information like his /her name, roll, class, section, and courses. You can see there is 3 table in the form: 1. student’s marks and grade, 2. Definition of different grade 3. final marks and grade score.
Step 4: I used some formula to find % marks and Grade. F column of first table is nothing the the score divided by course marks. In the G10 cell, I used following formula:
=IF(E10>=80,”A”,IF(E10>=60,”B”,IF(E10>=33,”C”,”F”)))
This formula is to calculate the grade automatically. I used similar kind of formula in G19 cell.
Formula in G15 Cell: =SUM(E10:E12)
Formula in G17 Cell: =G15/3 here 3 is the number of courses.
Formula in G19 cell: =IF(G17>=80,”A”,IF(G17>=60,”B”,IF(G17>=33,”C”,”F”)))
That’s all!
If you have any question or for customize solution for you: write me – ask@taposbd.com
