I know that out of all the Microsoft Office tools (Word, PowerPoint, and Excel), Microsoft Excel is probably the least used among college students, simply because we normally do not have a large need to create spreadsheets, but have a daily need of creating Word documents or PowerPoints. However, there are aspects of Microsoft Excel that we can use now that we may often overlook. For example, college students can use Excel to create a budget or keep track of their grades. As a future teacher, I realized that there are a plethora of uses for Excel for educators. From keeping track of grades to who has completed an assignment to which grade sold the most items for a school fundraiser, Excel provides an easy and clear way for teachers to stay organized.
Lynda.com is a website that offers tutorials on how to operate and work with different tools and technologies, such as Microsoft Excel. I explored Excel for Mac 2011 Essential Training, specifically focusing on Chapter 3 Managing Worksheets, Cells, and Cell Data, video 3, and Chapter 4 Summarizing Data Using Formulas and Functions, videos 1, 2, and 3. These videos taught me so much about the tools of Microsoft Excel that I did not know existed before. If you are a Windows user, don’t worry, because videos for the same topics can be explored through Excel 2010 Essential Training. Related information in this tutorial can be found under Chapter 2 Worksheet Basics, video 3, and Chapter 3 Excel Formula Basics.
The video in Chapter 3 entitled Entering Data Using AutoFill and Other Techniques was super interesting in the sense that it taught me lots of tips and tricks on how to make entering data into Excel faster and more efficient. For example, if you are creating a spreadsheet and need to enter in the months of the year, instead of typing out every single month, you can use AutoFill. If you type January into one cell, then click the bottom right corner of that cell and drag down the number of cells that you need months, AutoFill will automatically fill in the cells with the appropriate months. This process is shown in the pictures below.
AutoFill can help you insert more than just months though. In this screenshot, you can see that AutoFill can automatically fill in the number of cells you need with the full names of the months, abbreviated versions of the months, quarters, periods, weeks, and days of the week. All you have to do is type the word in the first row, then as you pull that cell down, Excel fills in the rest of the rows for you. Using AutoFill to create these categories saves time and makes using spreadsheets a lot less tedious.
In the first video in Chapter 4, entitled Introducing Excel Formulas and Functions, you learn all about the difference between a formula and a function. A formula is one that you enter into Excel by hand. A function however, is a tool that Excel offers that will automatically calculate whatever it is that you want calculated when you select a particular function.
In the example shown here, a teacher has a spreadsheet of test grades for her class. If she wanted to find the class average for each test, she could use the AVERAGE function, which would automatically determine the average of whatever numbers she wanted. In the second picture, you can see that she has found the class average for each test, which is the average for each column, and she has found each student’s individual test average, which is the average for each row.
Using the AVERAGE function is much faster than typing in a formula to find the average. If you used a formula to find an average, you would have to add all the numbers in a column or row, then divide by the total number in each particular column or row to get the average. That is a lot of tedious typing to create a formula when you could just use the AVERAGE function that does it for you. This is a great shortcut to use to save time and energy.
The second video in Chapter 4, Adding a Formula to a Cell, discusses a lot of the same things that video one covers. However, it highlights other functions besides the AVERAGE function. Excel has created a wide range of functions that can be found through the formula builder or reference tool at the top of the formula ribbon in Excel, shown below. These pre-made functions include SUM, MIN, MAX, or COUNT, just to name a few.
If we use the same spreadsheet example from above about the class averages, we can find the minimum and maximum grade for each test by using other functions in the same spreadsheet. To find these values, we must first start by clicking the cell and entering in the “equal” sign. It is important to note that any time you are entering in a function in a cell, you must signify that it is a function by first typing an equal sign =, followed by the name of the function. So after we type the equal sign, we can type in the word MAX, and the MAX function will appear. Once you enter the cells that you want to find the max of, hit enter and the maximum grade for that test will appear, as seen below.
The screenshot to the right shows the same class average spreadsheet as above, but instead of the averaged number or maximum or minimum, the function that was used to find that value is shown. This was done by typing “control ~”.
When using the same function across multiple rows or columns, like we did above when finding the class average grade, maximum, and minimum for each test, we can use what we learned in Chapter 3 about AutoFill to make finding the values even easier. Once you find the average, max, and min for the first test, you can use AutoFill by dragging across to the last column, and AutoFill will find all your values without having to type in the function each time. The same is true for finding each individual student’s test averages. All you have to do is type in the formula for the first student, then AutoFill down for all the rest. AutoFill addresses each cell individually, whether you are doing class average or student average, so that the formula is correct for each cell. This is very handy and makes finding values with functions even more efficient.
If the numbers in your spreadsheet are not all formatted the same, like in the class average spreadsheet where some averages are whole numbers, some have one decimal, and some have two decimals, you can learn how to format numbers to look neater in Maria H.’s blog post Formatting a Microsoft Excel Spreadsheet.
Once you have all your data, you might want to make a chart or graph to help analyze the results. For example, in the class average example, if you wanted to see which test overall your students did better on, you could make a chart or graph to determine the results. You can learn all about how to do this in Excel by reading Winna P.’s post Excel: Creating Basic Charts Quickly.
The third video in Chapter 4 is called Introducing Arithmetic Operators. This video focuses on how Excel solves operations without a function. If you remember way back to elementary school math when you learned about our good friend Aunt Sally- or PEMDAS (Please Excuse My Dear Aunt Sally)- when solving order of operations problems, then you know how Excel solves non-function problems. If you don’t remember, no worries, video 3 will teach you! Excel, just like you when solving an order of operations problem, does not solve the problem from left to right, but instead uses a specific order in solving the problem.
Using order of operations in an Excel spreadsheet can be seen in this example, which involves converting degrees Fahrenheit to degrees Celsius. In order to convert degrees Fahrenheit to degrees Celsius, you must enter in this formula: (F-32)*5/9. Excel solves this formula by first subtracting 32 from whatever degree Fahrenheit you are trying to convert because this is the action in the parentheses, which always comes first. Then, since multiplication and division are in the same step of order of operations, Excel solves the rest from left to right. The value in the parenthesis is multiplied by 5, then that value is divided by 9. Knowing the order of operations is useful because it allows you to know that how you enter in your formula matters, because a simple change of placement of parenthesis will result in an entirely different answer. For example, if the parentheses were not there in this conversion, the formula would look like this: F-32*5/9. The order of operations would do 32 times 5, divided by 9, and then that value would be subtracted from the degree Fahrenheit. So instead of subtracting 32 first, then multiplying and dividing, the subtraction would be the last step which would produce an entirely different, and wrong, answer.
When entering in this formula, you again can use AutoFill. Instead of typing in each degree Fahrenheit into the formula for each cell, you can type in the cell address. So for the first formula to convert 32 to degrees Celsius, you would enter in (A4-32)*5/9. Once you do this for the first cell, you can drag the cell down, just like before, and AutoFill will change the formula automatically for each row, determining the conversion to degrees Celsius.
I invite you to comment below your ideas on how having this knowledge of tips and tricks for formulas and functions in Microsoft Excel will increase your overall use of Microsoft Excel and how it will decease your possible frustration with entering and finding data in a spreadsheet. How do you think using Excel is beneficial to teachers? Are there any ways you think students can use Excel (both you as a college student and your future students)? How helpful is this information? How much did you know about Excel formulas and functions before?