The Excel Cheat Sheet for College Students
Posted By Staff Writer on March 17, 2017
Microsoft Excel is used by millions of people across the globe. Contrary to what you might think, however, Excel's broad applications, like simple data entry and complex problem solving, can benefit people other than just consultants, accountants, marketers, or analysts. In fact, Excel can also be incredibly beneficial to busy college students looking for tools to help them study and stay organized. If you're a student struggling to stay organized or searching for a handy resource to help you study, consider harnessing the power of Excel. By learning these 10 simple Excel functions, you can master the software and use it to study in an effective manner.
Filters[su_spacer size="10"] Filters are one of the greatest functions available to help you access and analyze specific data within a specific data set. Use this function to search for unique data that fits certain criteria by adding filters to every column and then selecting the cells you wish for the filter to be applied. To apply filters to an Excel worksheet, follow these quick and easy steps:
- Make sure that your sheet includes the header row or a row of titles that name each column. Your header cells will typically be located in row 1. So, if you're sifting through something like a bunch of inventory data, for example, they might include titles such as: Item name, Price, Quantity, etc.
- Select the Data tab in Microsoft ribbon, and click on the Filter command.
- Click the gray drop-down arrow in the right-hand corner of the header cell of the column you wish to filter.
- A filter menu will appear. If you wish to deselect all data, simply uncheck the box next to Select All. You can then go through and re-check the boxes from which you wish to filter data. Click OK to apply your filter.
VLOOKUP[su_spacer size="10"] Another function that helps you access specific data throughout one or multiple Excel worksheets is VLOOKUP. With VLOOKUP you can also combine data to quickly and easily create a report or summary for class projects, combine data sets to analyze content and results, and more. You should begin each VLOOKUP function with an equals sign, followed by “VLOOKUP” and an open parenthesis, like this: =VLOOKUP(. Generally speaking, your VLOOKUP formula will then include four different arguments, or various parameters needed to initiate an Excel function. These four key arguments include:
- The title, name, ID number, or other specified data that VLOOKUP is supposed to be searching for, enclosed in double quotation marks. At this point your formula should look like: =VLOOKUP(“Item name”
- The cell range of data on your worksheet, separated by a colon: =VLOOKUP(“Item name”, A2:B26
- The column index number, depending on what type of data you're searching for, and which column that data header falls under: =VLOOKUP(“Item name”, A2:B26, 2
- A TRUE or FALSE statement to indicate to the formula if it's searching for approximate or exact data matches: =VLOOKUP(“Item name”, A2:B26, 2, FALSE)
SUM Function[su_spacer size="10"] As one of Excel's most widely used functions, SUM simply adds a group of numbers. This is your go-to function for calculating grades, finalizing lab or report results, analyzing survey results, and much, much more. To apply the SUM function, simply decide which column you want to target and select the cell where you want Excel to present the total. Again, begin with typing the equals sign and then the function, sum: =SUM Type in the cell range of which you wish to reference: =SUM(A2:A14). Press Enter, and Excel will add up all of the numbers in cells A2 through A14 to present you with your total.
Pivot Table[su_spacer size="10"] As one of the most powerful tools in Excel, Pivot Tables are the perfect function if you're working on a school project with big data sets. Not only can you quickly summarize your data for project reports, but with Pivot Tables you can also analyze and explore specific trends, no matter how in-depth your data might be. Say you're starting a study group that meets three days a week. Chances are, at the end of the sign-up period, you'll have a long list of various names and which days they can meet. Instead of dealing with the headache of filtering and sorting, you can use a pivot table to see which students will be attending on which days, and you can even list them in alphabetical order. Pivot tables are also key in helping with things like environmental science experiments where you need to routinely check greenhouse measurements, business finance projects that require you to filter and sort a wide range of numbers, time logs, and much more. Check out this tutorial for beginners to get a step-by-step guide on how to make and use pivot tables.
Quick Analysis[su_spacer size="10"] As one of Excel's newest features, Quick Analysis is ideal for college students. Not only can you quickly and easily create different types of charts like line and column charts, or sparklines, but you can also pair Quick Analysis with other functions, to make analyzing data extra easy. After selecting the data you wish to visualize, you can use the Quick Analysis function in four easy steps:
- To open the Quick Analysis Gallery, simply press Ctrl+Q to or hover your mouse over the bottom-right corner of the selected cells and click on the pop-up icon.
- Select the tab you want - i.e. formatting, charts, totals, tables, etc.
- Use your mouse to hover over the various preview options of the selected visualization.
- Select your desired preview
Recommended Charts[su_spacer size="10"] Like Quick Analysis, Recommended Charts is also a newer Excel feature that can help students quickly and easily create appropriate charts for project reports or data analysis. As a college student strapped for time, this function can also save you valuable time by eliminating the need to test out every single chart before finding one that works best for you. To use the Recommended Charts feature, start by selecting the data you wish to plot on a chart. From the Insert tab of the Ribbon, choose Recommended Charts. You'll then be presented with a gallery of recommended charts for the selected data. Choose your desired design and click OK.
Autofill[su_spacer size="10"] Another staple Excel function for college students to know is Autofill. This time-saving function identifies particular patterns within your dataset and automatically completes the rest. Furthermore, the Excel autofill function can be customized to analyze and complete numbers or patterns that are specific to your project requirements or studying needs. To quickly fill various types of data series, select cells and drag the fill handle across or down the cells you wish to automatically fill. To show the fill handle, click the File tab in the upper-left corner of the worksheet and select Options. Click Advanced and, under Editing Options, select or clear the checkbox next to Enable Fill Handle and Cell Drag-and-Drop. To avoid replacing existing data while using this function, make sure that the Alert Before Overwriting Cells check-box is selected. From here you'll can select the all of the cells that contain the data you wish you autofill into adjacent cells by dragging the fill handle down and across all desired cells, selecting Auto Fill Options and clicking the option you feel is best for your needs. Check out this video for complete, step-by-step instructions on how to use the Excel autofill function.
Transpose[su_spacer size="10"] With the Transpose function, you no longer have to manually swap rows and columns, or vice versa, every time you want your data to be displayed differently. This quick three-click function can save you valuable time so you can study more productively.
- Select and copy your entire data range.
- Click on a new location within your worksheet, and got to Edit | Paste Special
- Select the Transpose checkbox and click OK.