This course is designed for any academic new to Excel who wants to harness its power and save time tracking student progress. This course will be of interest to any academic who needs to take Excel from the fundamentals to the exciting. The curriculum is structured around a series of short, step-by-step videos with an actual Excel workbook I use in my college courses.
The materials included in the course are totally customizable for your needs without any restrictions except your creativity. The benefits and skills you will gain in this course will get you up and running quickly and with an impressive skill-set. The course has approximately 2.7 hours of video, but 90% are under 10-minutes. If you use Excel to track student progress and/or communicate with parents, you need this course.
Every academic at some point needs to create a grade book and what better way than using Microsoft Excel?
But most academics still rely on the hardcopy grade books, which are outdated and not very dynamic. This course will demonstrate visually and with hands-on examples, how to use Excel to create a dynamic grade book that can be use to track and analyze students in a course or courses. You can then take the developed grade book (or templates provided for this class) and adapt or modify what you have learned for your own needs. There are other lessons in the course including creating a grade-book rubric and mail-merge using MS Word and Excel (just to name a few) that will be a great benefit for any academic.
If you have never used Excel before, had some experience with Excel, or want to take it to the next level, then this course is for you. The Sample Gradebook we will use for this course is included, so just download it, save a copy (more on that later) and get ready to master Excel!
Using Columns and Rows in Excel is easy and basic. The thing to remember is that Columns are vertical like the Columns that hold up a building, and Rows are horizontal like seats in a theater.
The intersection of a Column and Row is knows as a cell address, so if a cell address is A10, then it is the intersection of Column A and Row 10.
Another important factor is that Columns and Rows extend beyond just the desktop you see because there are approximately 256 Columns and 1,048,575 Rows in Excel for a total of 268,435,456 cells for each Excel worksheet. And you can have multiple worksheets for every Workbook in Excel.
Navigating the Excel worksheet is basic to anyone who has used a computer, but with some differences. In Excel, selecting a Data Range is simply clicking on a cell and dragging it across the worksheet. Any cells selected will turn blue.
Formulas in Excel can be a simple a =A1+A2+A3 or as sophisticated as =SUM(A1:A10). The more streamlined the formula, the easier it is to work with and modify.
Spreadsheets increase exponentially in their content, so the best way to optimize a worksheet is to always start with a function such as =SUM(A1:A10) rather than =A1+A2+A3. It doesn't seem like a big deal, but will matter over time as your worksheets become more complex.
In this lesson we will start by looking at the basics of developing a grade-book. Like any good project, a bit of preplanning helps, so don't be afraid to draw out on paper what you want your grade-book to look like. Consider this step as blueprint for your final project.
In this lesson we will continue to develop the grade-book but with an eye of making it dynamic, so if a students grade on an assignment changes, Excel will automatically look up the correct letter-grade. While this can be done manually with a small class, imaging having four classes with 50 students in each class; that's 200 individual grades!
So we want to automate the grading process as much as possible, which is why we will look at the VLOOUP function.
While many people new to Excel will normally type the formulas directly into the worksheet, which is called Hard Data Entry. While this is easy in the beginning with a basic spreadsheet, its important to consider that spreadsheets have habit of growing exponentially as they become more complicated; and they always do!
By learning how to optimize your Excel skills and learning good Excel techniques, you take away a lot of the manual labor in maintaining a worksheet by letting Excel do the work for you.
In the following lessons (5a to 5g) we will look at the most commonly used functions in academics; at last the one I use the most often. But don't let functions scare you; sure Excel has 400+ functions but most people just learn the functions they need to get the job done.
Think of Excel functions, as Excel vocabulary and start with a few work and soon you will be speaking complete sentences.
In this lesson we will explore the AVERAGE and AVERAGEIF functions and apply them to our grade-book.
Why can we abbreviate the function =MAX() for Maximum, and =MIN() for Minimum, but have to spell out the word Average for the =AVERAGE() function?
In this lesson we will explore the COUNT, COUNTA and COUNTBLANK functions and apply them to our grade-book.
While some may never have a use for the COUNTBLANK function, it is a good tool to know since it allows you to see if a student didn't turn in an assignment without manually counting through all the scores in a grade-book.
In this lesson we will explore the IF, and IF ELSE THEN functions (aka logical IF statements) and apply them to our grade-book.
Did you know you use the IF ELSE THEN function everyday?
Have you ever told someone, "Go and get me a Coke and IF they don't have Coke, THEN get me a Dr. Pepper, and IF they don't have either (Else Then) come back with my money."
If you have used that phrase or something similar, you've used an IF ELSE THEN function
In this lesson we will explore the HLOOKUP, and VLOOKUP functions. While the VLOOKUP function looks vertically for information, the HLOOKUP looks horizontally.
In previous lessons we used the VLOOKUP to match a numerical grade (90%) with a letter grade (A), but with the HLOOKUP we will use this variation to look across the worksheet for the data we are seeking.
In this lesson we looked at using both a =VLOOKUP() function and a =HLOOKUP() function.
What does the V standoff in a =VLOOKUP() function and what does the H standoff in a =HLOOKUP() function?
In this lesson we will explore the Maximum (=MAX), Minimum (=MIN) and Median (=Median) functions.
Notice that in the first two functions (=MIN & =MIN) we can abbreviate the function name, but with Median (=MEDIAN) we have to type the entire word just like the =AVERAGE function.
While most people can look at a small class and find the maximum or minimum grade on a specific assignment by doing the math in their heads, how many are good at finding the median score for a class of 50-60 students?
The purpose of learning these functions is to help Excel optimize the information so it's at your fingertips anytime the data changes.
In this lesson we will explore the RAND, SUM and SUMIF functions.
We will use the RAND or random number generator function to get Excel to create random numbers for our data since doing this on your own is harder than you think. Quick, think of a random number; now think of 200 random numbers!
The SUM function will be used to automatically add up numbers in a range where as the SUMIF will only add up numbers in the same range if they meet a specific criteria that you define such as finding all of the scores on an exam that are greater than 80% but ignoring all the rest of the scores that don't meet the criteria.
We know the benefits of using the =SUM() and =SUMIF() functions, but what is the benefit of using the =RAND()* (random number generator) function?
In this lesson we will explore the UPPER, LOWER and CONCATENATE functions.
When I download a list of student names from a college or university directory, it's not uncommon to have names that appear as meGan SmitH rather than Megan Smith. Yea, sure I could make those correction myself, but if I have 200+ students this semester with a First, Last and Middle name on my grade-book spreadsheet, that's 600 names I have to fix!
In this lesson we will show you get Excel to make those changes for you without retyping 600 students names.
What is the benefit of using the CONTANTANATE function and why is it different from the =LOWER() and =UPPER() functions and how it is formatted in the Formula Bar?
As discussed previously, the =SUM() function appeared on the last worksheet as =SUM(A1:B11), but if that function range doesn't work correctly, to can make modifications so it does. Changing a function is just making a few brief changes to the formula.
For example, if the function =SUM(A1:B11) is looking at the wrong information, it easy to change it to another cell range simply by changing =SUM(A1:A10) to =SUM(B11:B24). It's just like editing text in MS Word.
Long, long ago in a technology far, far, away, there was a spreadsheet known as Lotus 1-2-3 and it was texted based meaning that it was boring to use and not very attractive. Then Microsoft bowing to market pressure from applications such as Quattro Pro and Wingz created presentation spreadsheets and the rest is history.
In this lesson we will show you how to dress up an Excel worksheet so that people will actually read the handouts you distribute at the next Faculty Governance meeting!
In this lesson we will discuss what a Relative Cell Reference is and how it works.
In short, there are certain conventions in Excel that are based on common usage and therefore relative to each other such as months of the year and days of the week.
When Excel sees one of these conventions, it will presume that if the word Monday is typed in and the Auto-fill control is used, the next logical or relative word to follow Monday would be Tuesday. It also does the same thing for the months of the year and some numbering sequence that will be demonstrated in this lesson.
What is the difference between a Relative and an Absolute cell reference, and when would you want to use one over the other?
In this lesson we will discuss what an Absolute Cell reference is and how it works.
Sometimes in Excel, you want it to reference a specific range of cells as demonstrated in this lesson with a popup list. To change a relative cell reference to an absolute, you just add dollar signs before the Columns and Rows so they are locked down in the formula.
An example would be the difference between the relative formula of =SUM(A1:A10) and the absolute formula of =SUM($A$1:$A$10). The former is relative to the cells around it and "floats" while the last one is "fixed" to look only at a specific range of cells.
If you do a lot of essay or research paper grading like I do, having a grading criteria or rubric is essential so that there is a level playing field when it comes to grading a written assignment over a mathematical formula where the result is either right or wrong.
What no "sort of the right answer" in math?
In this lesson we will create a grading rubric, which can be modified to fit your needs and eventually use the information in a printed handout, so students know what they scored on the assignment and why.
Student doesn't know why they scored a C- on their essay paper?
Well..."Here's your sign!"
This is one of my favorite lessons that demonstrate the power of Excel and the flexibility of MS Word.
In this lesson we will take the student data in Excel, then create a letter in MS Word and finally merged the two applications together to create what appear to be customize letters to their a parent or student.
In the downloadable materials you will find the Data worksheet and MS Word Merged Letter used in the class video, so you can follow along.
Have 200 students who need to have a printed a letter of their status for their financial aid or educational reimbursement from heir employer? Then you are going to love this lesson!
The benefit of creating letters using the features of both MS Excel and MS Word is that each application has its own strengths when combining information from Excel into a letter in Word.
How many fields in Excel can be included in a mail-merged Word document?
Spreadsheets without charts are boring which is why in this lesson we will show you how to create several charts linked to the data, and then when the data changes, the charts automatically update. How cool is that?
We also show how the data (numbers) and charts (pretty pictures) in Excel can be copied and pasted into a Word document since some readers are data drive and others not so much.
In this lesson we will be using the =RAND()* function so a slight review might be needed if you took a break between lessons.
After you have all your data in a spreadsheets with supporting charts and graphs, eventually you will want to show off your work with some snappy handouts which is why we cover printing in this lesson; but not just any printing, printing only the information you want others to see.
We will also show you how to secure your Excel spreadsheet with a password so unauthorized users (that would be students) can't magically change a D- grade to an A- grade.
Say it ain't so Joe!
Warning: if you forget or misplace the password to your work, calling Microsoft wont help, so user beware and always make a backup copy of your work before attempting this at home!
The benefit of using a password in Excel is that your data is protected from unwarranted changes.
What is the BIG DANGER when using a password in Excel especially if you forget the password?
Hey, congratulations on completing the course, now you can impress all of your colleague with your new found Excel expertise!
Just make they buy you lunch before showing them how to do all these cool skills themselves.
Thomas Hennefer, PhD, is a lecturer, educator, researcher, and media producer developing video courses for both formal and unconventional viewers and students. Thom is also a former Associate Professor of Business Management and Sciences at an HBCU in East Texas, and has taught at both the graduate and undergraduate courses to over 3,000 students both online and in the traditional classroom.