Excel - Create a Gradebook in Excel: Step-by-Step Tutorial with Formulas and VLOOKUP - Episode 506

Microsoft Excel Tutorial: Create a Gradebook in Excel: Step-by-Step Tutorial with Formulas and VLOOKUP.

Welcome to another MrExcel netcast! In this episode, we will be addressing a question sent in by Christina, who is a teacher. She asked how to set up a student gradebook in Excel, and I have done some of the work in advance to show you the process.

First, we will set up the rows at the bottom for the students' names. At the top, we will have a row to show the number of points possible for each assignment. In the second row, we will have a list of the assignments, such as quiz 1, homework 1, homework 2, etc. Make sure to leave enough room for all the assignments you may give in one grading period.

As you complete the assignments, fill in the total points possible for each one. For example, if homework 4 is worth 10 points, fill in 10 in the corresponding cell. Then, fill in the scores for each student.

On the left-hand side, we have three formula columns. The first one is the total of all the points, which we can calculate using the =SUM formula. Copy this formula down to all the students to see their total points.

Next, we will calculate the percentage score for each student. This formula will use a relative reference to the cell containing the total points possible, and an absolute reference to the cell containing the student's score. Format this as a percentage and copy it down to all the students.

Now, we need to set up the grading scale. We will use the VLOOKUP function, but in this case, we will have ranges instead of exact matches. The grading scale should be entered in reverse order, starting with the lowest grade at the top. Then, use the VLOOKUP function to assign a grade to each student based on their percentage score.

To make it easier to see the student names while scrolling, we can freeze the top row. This can be done by selecting the cell containing the student names and going to Window ＞ Freeze Panes.

These are the basic steps for creating a simple gradebook in Excel. For more detailed instructions and tips, check out the book "Excel for Teachers" available on MrExcel.com, Amazon, or your local bookseller. Thank you, Christina, for your question and I hope this helps you and other teachers out there. Thanks for watching and see you next time for another MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents:
(00:00) Introduction
(00:21) Setting up a student grade book
(00:31) Creating rows for students and assignments
(01:00) Using formulas to calculate total points and percentage scores
(02:29) Setting up a grading scale using VLOOKUP
(03:46) Tips for creating a simple gradebook
(04:04) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Excel for Teachers book
Formatting percentage score
Formula for calculating percentage score
Formula for calculating total points
Freezing panes to view student names
Points possible for each assignment
Rows and columns in grade book
Setting up a grade book in Excel
Setting up grading scale
Tips for creating a grade book in Excel
Using VLOOKUP function
YouTube video, student grade book in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...



Christina asks how to create a gradebook in Excel. Episode 506 shows off how to create absolute references and also the obscure TRUE version of the VLOOKUP function. Everyone teachers and non-teachers should check out the VLOOKUP in this episode.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!