The Excel Cheat Sheet for College Students


By Staff Writer Published 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

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:

  1. 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.
  2. Select the Data tab in Microsoft ribbon, and click on the Filter command.
  3. Click the gray drop-down arrow in the right-hand corner of the header cell of the column you wish to filter.
  4. 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

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)

 

Press Enter and you’ll have your number! Check out this YouTube video for a more thorough, step-by-step guide on how to properly use the VLOOKUP function.  

SUM Function

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

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

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:

  1. 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.
  2. Select the tab you want – i.e. formatting, charts, totals, tables, etc.
  3. Use your mouse to hover over the various preview options of the selected visualization.
  4. Select your desired preview

After making your selection, Excel will then create the Quick Analysis in a new worksheet to help you quickly analyze data, spot trends, and much more.

Recommended Charts

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

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

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.

  1. Select and copy your entire data range.
  2. Click on a new location within your worksheet, and got to Edit | Paste Special
  3. Select the Transpose checkbox and click OK.

Dollar Signs

As ironic as it may seem for “broke college students,” using the dollar sign function in Excel will help to ensure specific columns and rows remain unchanged, regardless of if that formula was plugged into different cells. Simply put, using dollar signs allows you to change a specific formula from relative to absolute.

For example, say you’re working on a project for business class where you need to convert Euros into US dollars. In your data chart you’d have column of Sales values in Euros listed in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into a formula, but referring to a cell where the exchange rate is absolute would be an easier, more efficient solution, and for this, you’d use the dollar sign to fix the rate in the formula. Check out this quick guide.

Conditional Formatting

Conditional formatting is a college student’s Excel liferaft. By enabling you to easily identify trends, outliers, and more, with conditional formatting you can gain a general understanding of your data at a glance. Furthermore, conditional formatting helps you stay neat and organized by allowing you to change a cell’s color based on the cell’s specific information.

Say, for example, that you are interning for a chiropractic company and your boss wants to track patient’s birthdays to see who is approaching and mark whether they have received a birthday greeting from your office. Instead of sifting through dozens of patient profiles and compiling a scattered, confusing document, you can use the conditional formatting formula in Excel to efficiently create an accurate document that can be continuously updated and revised.  This step-by-step tutorial will help you master conditional formatting.

With the simple Excel functions, you’ll be able to quickly and easily analyze project data, create presentations, stay organized, and much more.

Becoming an Excel master will help you in virtually any career path you choose. Learn more about degree programs at Stevens-Henager College, or give us a call at (800) 622-2640.

Stevens-Henager College admits students of any race, color, and national or ethnic origin.