Excel assignments reimagined with auto-marking

Microsoft Excel and its equivalents are a vital component of many post-secondary programs. However, grading spreadsheets can be tedious, as instructors need to manually mark each student’s assignment cell by cell.

But that can change with Dominarlo’s Excel Analyzer. Excel Analyzer is a digital tool included in The Workshop by Dominarlo. The tool allows you to completely automate the grading process for any spreadsheet based assignment.

This video will give you a brief demonstration of how Excel Analyzer works, including some tips and best practices. All right, let’s begin.

This four-section video will give a brief demonstration of how Excel Analyzer works, including some tips and best practices.

Section One will demonstrate how to create an assignment.

Section Two will show how to upload it to Dominarlo’s workshop and assign it to students.

Section Three will briefly show how to download and submit assignments as a student.

And Section Four will cover the grade book.

All right, let’s begin.

The first step is to create an ordinary Excel assignment, though, as you can see, I’m not actually using Microsoft Excel myself. I’m using Google Sheets. The exact software you use in your work isn’t important, just so long as you can export your assignment as an Excel file.

For this video I’ve created a very simple assignment to use as an example. We have our directions, some values, and we’ve clearly indicated where we want our students to input their answers. If we intended to mark this assignment manually we would already be done. But to have it marked automatically, we’ll need to do a little more prep work.

Save this Excel sheet as is or export it, in my case. This will be our questions template. This is the file the students will download, work on and then submit. Now we need to make an answer key. To do so, we’ll make a copy of our questions template, rename it, and then fill in all the correct answers, which I’ve done ahead of time.

Please note that the answer key needs to be handled with care. The two files don’t need to be exactly the same. As you can see, I’ve highlighted the answers in red for my own convenience, but adding or removing rows or columns in your answer key can result in faulty grading. For very simple Excel assignments, this may be all you need to do.

However, for more complex assignments, especially those requiring formulas or functions, we should consider alternate solutions. Excel only allows you to input one formula into a cell, and so our answer key can only have one answer per cell. But what if there are multiple ways to achieve the correct answer? In our example, for instance, the solution for cell E7 can be found by multiplying the value in cell C7 by the value in cell D7, which is what I have inputted here.

But wouldn’t D7 times C7 achieve the same result?

Fortunately, we can distinguish valid alternate formulas and functions using notes. All we need to do is add a note to the cell and list all the alternatives we wish to include. This can be done for as many cells as we need and can include any number of alternate solutions that we like.

Once your answer key is filled out, make sure to save it as well.

Now that we have our questions template and our answer key, we are ready to upload them to the workshop.

Once you’ve signed in to your workshop account, click Create Question on the Question Bank on the sidebar. From here, you’ll be asked to provide some metadata for your worksheet. Fill in this data as appropriate, and when selecting the question type, make sure to choose Excel Analyzer.

On the following screen, you’ll be asked to provide a question description, question difficulty, and whether it should be listed as public or private. You’ll also be asked to separately upload your answer key and your questions template. Do so and then click next.

Excel Analyzer will automatically detect the range of cells that contain formulas in your answer key. If everything looks okay, you can go ahead and click next.

On the following page, you can scroll down to see a simulation of your answer key. Notice that each cell that contains a formula has a checkbox. If you click on one of these checkboxes and scroll back up, you’ll be shown the address of the cell, the formula contained within it, the alternate formulas you’ve listed using notes as well as the discrete value answer.

And, directly to the right of this information, you’ll find a set of options that can be used to award different point values for different kinds of correct answers.

We could, for instance, only award marks if the student used the given formula or one of our specified alternative formulas. We could also award full marks for a correct answer of any kind, even if the student used a formula we didn’t specify. Or if they just manually entered the correct answer. It’s entirely up to you.

When you finish adjusting point values, don’t forget to click Save to save your settings for this particular cell. Note that the cell background has changed to indicate that its point values have indeed been saved.

And now we could go cell by cell checking and unchecking boxes and filling in this information. Or we can click check all in the top left corner and set point values for all cells at once. If there are certain cells that we want to have a different point value system than the rest, we can always go back to those cells individually and change things around.

Once everything’s to your liking, click finish.

Your assignment will now appear in your question bank search results and will mark itself automatically once you’ve assigned it to your students.

To do so, navigate back over to the sidebar, click Assignments, and then Create Assignments.

You’ll first be prompted to find your question in our question bank, which you can do using the metadata we specified earlier. Once you’ve found it, click the checkbox and then click the button on the bottom right on the next page. You can tweak grades and finalize your questions.

But we’ve already done this in our prep work, so there’s no need to adjust anything further.

Finally, you’ll be asked to give your assignment a title, a brief description, and a grade release date. The grade release date is simply the number of days students will need to wait to receive their marks after the assignment due date, which we’ll set in just a moment.

Now that your assignment is created, we can release it to students. You’ll automatically be navigated to the courses page under classes. Find the course you want.

Click Assign and select your assignment from the dropdown menu on the next page.

Now all that’s left is to specify which class sections are to receive this assignment and their start and due dates. When you’re done, click assign to classes and you’re finished.

Now, we will very briefly take a look at Excel Analyzer from the students’ perspective.

Once a student has logged into the workshop account, they can access their ongoing assignments by clicking Classes and then My Classes on the sidebar. From here, they can select a class to see a list of all of its assignments.

When we click on one, we will see an option on the next screen to download the questions template, which is this file we prepared earlier.

The student can then complete the assignments and upload their answers back to the workshop. Afterwards, they can click submit, agree to our declaration of academic integrity, and then they’re finished.

Lastly, we’ll give a brief overview of the grade book.

Navigate to the Courses page under Classes on the sidebar. Find the course you’re interested in. Click View Classes and then select the class whose grades you wish to view.

The following page will give an overview of the class’s performance and scrolling down, you can find a detailed record of each student’s grade on each assignment. Not only that, but the workshop will automatically flag assignments that contain suspicious activity.

Let’s click on this assignment for more details. There are a few things instructors can do from this screen. First, the assignment’s grade can be manually overridden if needed. Second, you can download and review the exact file the student submitted by clicking this button. And lastly you can scroll down to view a detailed report of the workshop’s grading. This section breaks down every answer the student provided and how the final grade was calculated.

Additionally, the workshop will pinpoint cells that may contain violations of academic integrity. This includes cells that were copied over from another document, as they were in this example. You can even hover your mouse over the icon on the left to see exactly which classmate this student copied from if that’s the case.

And there’s one final note. Instructors can export the contents of the grade book as an Excel file for their convenience.

And that will wrap up our overview of Excel Analyzer. Thank you so much for watching.

For more information about Excel Analyzer or our other educational tools, please visit our website at dominarlo.ca.

Streamline your education with Dominarlo.