How to Create an Electronic Register with Microsoft Excel

Table of contents:

How to Create an Electronic Register with Microsoft Excel
How to Create an Electronic Register with Microsoft Excel
Anonim

The electronic register on Microsoft Excel is a great tool to use. It is a very useful spreadsheet containing data and formulas which, compared to traditional ways, minimizes both the time and effort to save the grades and calculate them. This guide contains a fairly detailed description of the procedures that will allow you to learn this new tool. You can also use it for future data analysis. To follow the steps described in this article you only need basic knowledge of using Windows 7, Windows 8 or XP. You don't need to be familiar with Microsoft Excel.

Steps

Create a Gradebook on Microsoft Excel Step 1
Create a Gradebook on Microsoft Excel Step 1

Step 1. Open Microsoft Excel

Create a Gradebook on Microsoft Excel Step 2
Create a Gradebook on Microsoft Excel Step 2

Step 2. Enter the class information in the Excel sheet

Create a Gradebook on Microsoft Excel Step 3
Create a Gradebook on Microsoft Excel Step 3

Step 3. Choose a layout for the log

Create a Gradebook on Microsoft Excel Step 4
Create a Gradebook on Microsoft Excel Step 4

Step 4. Create the formulas

Part 1 of 4: Open Microsoft Excel

Create a Gradebook on Microsoft Excel Step 5
Create a Gradebook on Microsoft Excel Step 5

Step 1. On the home screen, press the "Start" button, then go to "All Programs"

Create a Gradebook on Microsoft Excel Step 6
Create a Gradebook on Microsoft Excel Step 6

Step 2. Click on "All Programs" to select Microsoft Excel

Create a Gradebook on Microsoft Excel Step 7
Create a Gradebook on Microsoft Excel Step 7

Step 3. Find "Microsoft Office" from the list and select it

Create a Gradebook on Microsoft Excel Step 8
Create a Gradebook on Microsoft Excel Step 8

Step 4. Click on "Microsoft Excel"

For easier access to Microsoft Excel, select and drag the Excel icon to the desktop, as per step 3

Part 2 of 4: Enter the Class Information into the Excel Sheet

For organizational purposes, you should always give the created sheet a name and include general information regarding the class (i.e. the name of the teacher, section and / or lesson times). This step is essential when you need to print the document, make copies and share them. It serves to identify the register table correctly and efficiently.

Create a Gradebook on Microsoft Excel Step 9
Create a Gradebook on Microsoft Excel Step 9

Step 1. Name the log sheet

  • By double clicking on "Sheet1" at the bottom of the Excel window, the text will be highlighted.
  • Type a name for the sheet, for example 'Votes Situation'.

  • Press "Enter".

    Create a Gradebook on Microsoft Excel Step 10
    Create a Gradebook on Microsoft Excel Step 10

    Step 2. Enter the course information

    • Click on cell A1 to select it.
    • Type in the teacher's name.
    • Press the "Down Arrow" key to select cell A2.
    • Type in the name of the course, for example 'Social Science Course'.
    • Press the "Down Arrow" key to select cell A3.
    • Write down the lesson hours.
    • Press the "Down Arrow" key to select A4.
    • Enter the quarter or semester, for example 'Fall 2015'.
    • Press "Enter" twice to go to cell A6.
    • The "Name" box at the top of the sheet shows which cell is selected.

    Part 3 of 4: Choosing a Layout for the Registry

    Create a Gradebook on Microsoft Excel Step 11
    Create a Gradebook on Microsoft Excel Step 11

    Step 1. Enter the names of the students

    • It is important to choose the table structure that suits you best. Knowing the type of data you are going to enter will help you identify the different columns you will need. You will need one column for each grade, one for the students' names, one for the total, one for the average and one for the final grade.
    • As far as students are concerned, in reality three columns are needed: sequential student identification number, name, surname.

    • Create a column with a sequence of numbers.

      • Select cell A6 and write 1.
      • Press the "Down Arrow" key.
      • Enter number 2.
      • Place the cursor on cell A6.
      • Click and drag the cursor from cell A6 to A7 - both cells are now highlighted by a box around them.
      • Hover your mouse over the lower right corner of the box until the cursor becomes a plus sign + (it is called the fill box).
      • Click and drag to the final number.
    • Type the name of the columns: select cell B5 and write "Name", which is the label of the column of names, then press the TAB key to move to the adjacent cell, where you will enter "Surname", which identifies the content of the next column. Go down to the next line and fill in the names and surnames of all students.
    Create a Gradebook on Microsoft Excel Step 12
    Create a Gradebook on Microsoft Excel Step 12

    Step 2. Create the remaining columns (follow the steps as indicated above):

    for example, label them as Tasks 1, Tasks 2, Test 1, Test 2, Exam, Total, Average, and Final Grade. Use the Tab key to move from one cell to the next column.

    To view the names in alphabetical order, on the Home tab click on the "Sort and Filter" icon, choosing "Sort from smallest to largest"

    Part 4 of 4: Creating the Formulas

    Excel provides a list of many functions that can be used in calculating grades. The first function is "Sum". It is used to find students' total grades. You can also use an expression to calculate the average as a percentage.

    Create a Gradebook on Microsoft Excel Step 13
    Create a Gradebook on Microsoft Excel Step 13

    Step 1. Calculate the total of votes

    • Select cell I6 (directly under "Total").
    • Under the "Formulas" menu, select "AutoSum".
    • Select and drag cells starting from cell D6 to H6 horizontally.
    • Press ENTER.
    • To copy the formula to the entire "Total" column, click and drag the fill handle until you reach cell I15. This will copy the function for each row, calculating the total grades for each student.
    Create a Gradebook on Microsoft Excel Step 14
    Create a Gradebook on Microsoft Excel Step 14

    Step 2. The grade point average

    To find the average grade for each student, divide the value found in the "Total" column by the highest possible score. In this example, assume it's 500.

    • Select cell J6, which is located directly under "Average".
    • Click on the Formula bar to type.
    • Write: "= I6 / 500".
    • Press ENTER.
    • Click on cell J6 and drag the fill handle along the entire column of the average, until you reach J15.
    • To put the averages column as a percentage, select from cell J6 to J15.
    • Right click on the selected column range.

    • Choose "Format Cells": a dialog box will appear.

      From the "Number" tab, click on the "Percentage" category

    • Change the number of decimal places, setting them as you like.
    • Click on "Ok".
    Create a Gradebook on Microsoft Excel Step 15
    Create a Gradebook on Microsoft Excel Step 15

    Step 3. Convert the average percentage into final evaluation. Excel allows you to insert a function that automatically calculates a vote based on the averages present in column J

    To perform this function, you need a conversion table, which is simply a pattern with literal grades and corresponding numeric values. Now create this table, again in Excel.

    • Create the conversion table.

    • Select cell M7 to start from.

      • Write "Average" in the first column.
      • Press the Tab key.
      • Type "Judgment".
      • Under "Average", enter your rating based on the numerical scale.
      • Under the "Rating" column, type the corresponding literal rating for each numerical score.
      Create a Gradebook on Microsoft Excel Step 16
      Create a Gradebook on Microsoft Excel Step 16

      Step 4. Type the formula

      The function that should return a literal judgment on output is VLOOKUP. It follows the following syntax: VLOOKUPlookup_value, table_array, column_index_number, [range_lookup]).

      • Select cell K6.

      • Start typing the formula: = VLOOKUP (J6, $ M $ 18: $ N $ 22, 2, TRUE)

        Explanation: After the parenthesis, type the address of the cell containing the student's final numerical grade which, in this example, is J6. The second part of the formula is inserted automatically by selecting the conversion table. Press F4 from the keyboard to enter the dollar sign which will block the selected range (it is the so called "absolute reference"). The third part must contain the number that corresponds to the column of the table that contains the literal judgments, the second. "TRUE" indicates an approximate match to the column values, while "FALSE" results in exact matches

      • Press ENTER.
      • Copy the formula by dragging it down over the entire column, to cell K15, by clicking and dragging the fill handle from cell K6.
      • By repeating this process you will be able to calculate the grades of the other future courses.

      Advice

      • Always give your electronic register a name by clicking on the "File" tab. Choose "Save As", locate a location to save the file and type a name for the document. When you are ready to save, press "Save".
      • When in trouble, consult the in-depth "Help" menu.
      • To find out which operating system your PC has, click on "Start", right-click on "Computer", scroll down and click on "Properties": a system dialog box with basic information will appear on your computer.
      • For easier access to Microsoft Excel, select and drag the Excel icon to the desktop, as per step 3.
      • The "Name" box at the top of the sheet shows which cell is selected.

      Warnings

      • Make sure the functions created for your registry do the calculations correctly.
      • Be sure to save your progress as you work to avoid losing information.
      • Always save a backup copy of your registry and keep hard copies.

Recommended:

How to Create an Attention-grabbing Electronic Press Kit

How to Create an Attention-grabbing Electronic Press Kit

An electronic press kit is essential for any artist looking for exposure and publicity. You can send it to music production houses, clubs, the press or anyone who may be interested in your work. Think of it as your professional resume. Did you know that most electronic press kits are trashed?

How to Create Electronic Music: 15 Steps

How to Create Electronic Music: 15 Steps

Although its origins date back to the mid-19th century, the first electronic musical instruments used for composition were the heterophone and the rhythmicon, created by Leon Theremin. Thanks to technological progress, synthesizers, which were once reserved for music studios, are now available to all electronic music lovers, who want to compose on their own or be part of a group.

How to Create a Calendar with Microsoft Excel

How to Create a Calendar with Microsoft Excel

Although its fame is due to completely different uses, Microsoft Excel can also be used to create and manage a calendar. The ready-made and freely customizable calendar templates are numerous, a real advantage in terms of time for those who do not want to create and format their own calendar from scratch.

How to Create a Currency Converter with Microsoft Excel

How to Create a Currency Converter with Microsoft Excel

Have you ever needed to know how many pesos a dollar is? Or how many yen does one pound equate to? Here is an easy way to create a currency converter within Excel that automatically fetches exchange rates from the Internet! Steps Step 1.

How to Create a Graph Using an Electronic Spreadsheet

How to Create a Graph Using an Electronic Spreadsheet

This tutorial shows the steps required to quickly create a chart in Microsoft Excel. Steps Step 1. Insert the table containing the data to be represented in the worksheet Use the following format: Cell '1-a' will contain the header of the abscissa data (x axis).