Originally published by Custom Writing. Republished with permission.
Excel Cheat Sheets
Many research papers, especially on technical subjects, require calculations and the handling of significant data volumes. Microsoft Excel is a perfect solution for these goals. Students can use this software in multiple ways, and our cheat sheets from the following sections can show you how. You can also check how to write a research paper for additional tips.
There are two terms that you should learn before you start:
Key Excel Functions & Tricks for Students
A function in Excel is a formula that helps with mathematical, statistical, and logical operations. It is a quick way to find the sum, average, count, maximum value, and minimum value. Students should master just some of them as it will significantly help them in their academic life. The rest of the Excel functions can be helpful in business or at work.
Excel Functions You Need to Know
As a student, you will find these functions useful:
SUM is one of the most frequently used functions in Excel. It sums up values from several columns or rows. For instance, =SUM(A1:C1) is a selection that sums the value of rows. You can also turn the function into a formula.
IF is used when you want to arrange your data in a particular manner. You can also include formulas and functions into it if it serves your purpose. For example, =IF(A2<D4, “TRUE,” “FALSE”).
SUMIF sums up the values in a range that meets specific criteria. For instance, a column contains numbers, and you want to calculate only the values equal to 1: =SUMIF (A2:A45, “=1”).
VLOOKUP stands for “vertical lookup.” This function does a search in the sheet for a certain number or value in a column. It brings the matter from different columns into the same row. For instance, =VLOOKUP(“London,” A1:C90,2, TRUE).
INDEX/MATCH are two functions that are usually combined for advanced lookups. To use this formula, type =INDEX($B$2:$B$8, MATCH(H12,$C$2:$C$8,0)).
MAX/MIN are other vital functions in Excel. MAX helps you find the highest, while MIN – the smallest number in a range. While searching, the functions will miss empty cells. Here is how you can use them: =MIN (or MAX) (number 1, {number2}, …)
COUNT in Excel counts numbers in a given range. It can look like this: =COUNT(B1:B20).
COUNTA will count cells that contain information. To use this function, you need to determine the cells that you want to include. For instance: =COUNT(A1:A7)
COUNTIF is a statistical function. It counts cells in a range that meets a specified condition. For instance, it can count the number of times some information appears on the sheet. Here is an example: =COUNTIF(A1:A5, “1990”). This function can help you understand how many times “1990” is mentioned in the spreadsheet.
AVERAGE in Excel will find the average of the numbers or values. It adds all the figures and then decides the result by the overall amount of values. Here’s an example: =AVERAGE(B1:B10).
Tricks for Using Functions Like a Pro
Mastering functions are an essential part of understanding how to use MS Excel like a pro. Here, you’ll discover how you can use the functions that we’ve discussed:
Use ALT+= to enter a sum automatically. Select the column and keep pressing ALT. Then, type + and =. It will quickly give the sum of the column’s contents.
Click CTRL-` to demonstrate formulas in a worksheet. Press the control key and at the same time click “`”. This way, you’ll see all the formulas. To hide them back, click the same combination again.
Try the Automatic Calculation. Be careful using this trick. Every time you change something on the spreadsheet, Excel will recalculate the formulas. It is very convenient, as it means your data is up-to-date. If you want to switch off the Automatic Calculation, go to options and select formulas to view the Calculation options.
Consider using the Quick Aggregation Tool. It can demonstrate aggregated statistics, such as Average, Numerical Count, Max, Min, Sum, and Count. For that, just select the rage without entering any formula. Excel will show these stats automatically.
Convert numbers to a range. The LOOKUP function can help you to convert numbers to ranges for simplifies summary. To convert numbers, you should use [range_lookup]. For instance, you have some values in a Column, and you want to convert them into specific ranges. For that, follow simple steps: create a table and its fields, and enter the function.
Count numbers in a range. If you want to count how many numbers fall into a specific range, you can try several methods: fixed and dynamic. Imagine you have a range of numbers in B1 to B100, and you want to know how many values are between 10 and 30. For that, enter the function into the formula bar, =COUNTIFS(B1:B100, “>=10”, B1:B100, “<=30”). However, a dynamic method is better. You can change values, and Excel will update the count. The formula for the dynamic count is COUNTIFS(B1:B100,”>=“&C2, B1:B100,”<=“&B3).
6. Helpful Excel Cell References
If you want to nail MS Excel, you have to learn more features than just functions. In this section, we’ve explained cell references. They will help you navigate Excel better and use it more efficiently.
To use autofill in Excel, you need to know about cell references. Below, you can see combinations of letters and numbers. A letter represents a column, and a number represents the row.
For instance, you want to change a simple reference in your cell into a relative consideration. For that, press F4. It will change the reference from A1 to $A$1.
7. Excel Keyboard Shortcuts That You Need
In any Microsoft program, knowing key combinations can make your life easier and your work process – faster. There are many of these combinations in Excel. You probably know most of them already simply by using Word and other programs.
In the following table, we’ve gathered some Excel specific shortcuts:
Shortcut | What it does |
= | Starts a formula. |
Ctrl+ | Goes back and forth between formulas and cell values. |
Ctrl+' | Copies and pastes the formula from the cell into the current one. |
Shift+F3 | Inserts a function. |
Ctrl+Shift+U | Collapses or expands the formula bar |
F9 | Calculates the worksheets in all opened workbooks. |
Shift +F9 | Calculates the worksheet. |
Alt+= | Inserts an Autosum function |
Shift+PgDn / Shift+PgUp | Extends the selection down one screen/up one screen. |
Ctrl+Shift+Home | Extends the selection to the beginning of the worksheet. |
Shift+Arrow key | Extends the selection by a single cell. |
Shift+Home | Extends the selection to the beginning of the row. |
Ctrl+A or Ctrl+Shift+Spacebar | Selects an entire worksheet. |
Shift+Spacebar | Selects a row. |
Ctrl+Spacebar | Selects a column. |
Excel Formulas: Top Tips
The correct application of formulas is the key thing you have to learn. In this section, we will give you several tips on creating them in Excel successfully.
Not to make typos and mistakes in the formulas, you should remember this:
Use (brackets). You can avoid many mistakes if you follow this simple tip. Many errors happen with mathematical operations. Using brackets allows you to see each part of your formula separately and thus, exclude mistakes. Here is an example: =(B1+C2)/A5
Include line breaks for complex formulas. Getting lost and making unnecessary mistakes is a part of learning MS Excel. Thankfully, you can avoid numerous errors by just adding line breaks to your formulas. This way, it will be more manageable for you to detect all the mistakes and typos.
Utilize the Fill Handle to copy the formula to different cells on the spreadsheet. You can select the Fill Handle and drag it down to the rows. It is the small square on the bottom right. If you use it, Excel will apply a function from one cell to all the other cells you dragged it.
Delete duplicate rows. You can go into the Data menu and click on Remove Duplicates. To select a range that you want to remove the duplicates from, click Ctrl+A. Hit okay and enjoy the lack of copies.
Add leading zero. Sometimes you need to add zeros to a number. To simply do it, use the following formula: =TEXT(A1, REPT(“0”,9)). The formatted number will have a text length of 9 characters.
Create named ranges. If you have a large number of rows, it is helpful to give names to the ranges. It will allow you to refer to them using their titles instead of clicking and selecting them. To provide names, do as follows:
Click on the Formulas menu.
Click on create from selection button.
Select the ranges and name them
7. Add double quotes. One of the best ways to include double quotes is the CHAR function. It will keep your formula readable and concatenated. ASCII Table can help you see what number you want to join.
8. Count unique values from a range. Sometimes you need to see a count of unique values in a range. You can use a method of deleting duplicates. However, there is a quicker way to do it. For instance, you have your classmates’ names in A1:A20. In an empty cell you type a formula, =SUM(1/COUNTIF(A1:A20, A1:A20)). Instead of hitting Enter Key, you press Ctrl+Shift+Enter on Windows. It will create an arrow formula. You will see the desired result of how many unique names are in your class.
Coming up next - mastering OneNote, PowerPoint and Teams.
Comments