Microsoft Excel Tips & Tricks for College Students
College students routinely manage an abundance of responsibilities. From group assignments that are crucial to maintaining a strong GPA to time-consuming leadership positions within university organizations, many of these responsibilities require them to gather and organize large quantities of data and information, such as lists of people to recruit for a new club, pertinent data or research points for a class project, or an organized budget of how to best allocate their limited funds for that semester. There are many digital tools that college students can use to organize that data and information, but one of the most useful is Microsoft Excel. Originally released in 1985, Excel has held its popularity throughout the decades and remains a widely used program for organizing information in digital spreadsheets. However, despite its popularity and commonplace usage in professional, personal, and academic settings, many users don’t know about some of the powerful and helpful tools and processes that can better build charts, identify trends, and organize and interpret data. The following Microsoft Excel tips and tricks for college students can help users better organize and understand their data, and in turn, make a stronger impact in their classes, campus activities, and future professional and academic careers. This article will also show college students which Excel tips and tricks can be used to help them stand out in internships and entry-level jobs, as well as common Excel mistakes and mishaps to avoid.
Chapter 1: Excel Tips and Tricks to Use at School
These Excel tips and tricks for college students may be more beneficial to some students than to others, depending on the individual’s degree and career ambitions. Students majoring in data- and analytics-driven fields might need to learn dozens of Excel functions before they graduate. Students pursuing liberal arts degrees may find themselves using Excel less often and with less rigor. But for all college students, regardless of major or career path, these particular tips should help throughout their academic career. Below are specific Excel tips that students can use, as well as hypothetical examples of how those tips can be applied in a university setting.
A. Use Recommended Charts to visualize data
Bernadette is creating a presentation for a big classroom assignment and wants to make sure her insights are fully understood by the rest of the class. She knows she wants to use some sort of graph or chart but is unsure which might be the best to incorporate. Recommended Charts is a tool that can give college students using Excel an idea of what their data may look like visually. This tool will show Bernadette immediately which data representation model will be most effective. She can simply select the data, click “Insert,” then click “Recommended Charts.” Then she can scroll through the selections and click on one to see a representation of that data. <H3>B. Apply VLOOKUP to organize data across different sheets<H3> Bill wants to compare attendance numbers for student government meetings over the past few years. He’s collected data for this year in one Microsoft Excel sheet, and he has meticulously organized the data for the past 10 years in separate sheets, all formatted in the same style. When looking at the current year’s data, Bill starts to notice that the membership numbers this year might be the lowest since the start of the decade. Bill can use VLOOKUP to quickly find that info from years past. He simply needs to go to the function area field above the sheet, the area after fx, and type =VLOOKUP, followed by the specific information being looked for in parentheses. For example, if Bill wanted to quickly find the total attendance for all 20 student government meetings for 2015, he can use a VLOOKUP formula .
C. Utilize Conditional Formatting to track data with colors
Shana has surveyed nearly 500 students on campus about how much they would donate to a particular advocacy cause. She’s spent a great deal of time organizing all of the data into a spreadsheet, but now she has to dig through that information and extrapolate which of those students would donate a large sum of money. In this instance, Conditional Formatting would be a helpful Excel trick to use. Conditional Formatting assigns a particular data value a color, allowing Shana to quickly see which of her respondents indicated they would donate a certain amount of money. To use this function, Shana would simply go to the “Conditional Formatting” option on the “Home” tab and select from one of the drop-down choices on how to organize that data. She would choose “Highlight Cell Rules” and then select one of the options to assign a color for data that corresponds to that particular option. For example, cells with values greater than $500 could be highlighted as red, while cells with values less than $10 could be highlighted as yellow.
D. Use Autofill to automatically fill out certain data points
This semester, John has a goal of saving more money. If he saves $20 one week, he wants to save more than that the next, with the ultimate goal of calculating his total over 15 weeks. He wants to track his progress in Excel week-by-week, but he doesn’t want to have to type out seven days, then 14, then 21, and so on. An Excel tip John could use is Autofill, which would easily have that data put in place for him. To use this function, John can simply insert a value into one cell and another value into the cell beneath it, and Excel will recognize a pattern (if one exists). So if that first cell had a value of five and the second had a value of 10, he would just need to select those two cells, hover the cursor over the bottom right corner, and then drag that black plus sign down over the blank cells as far as he wants to go. After letting go of the cursor, it will populate with the remaining numbers in the pattern: 15, 20, 25, and so on.
Chapter 2: Excel Tips and Tricks to Use in Internships and Entry-Level Jobs
Many college students work hard to get competitive entry-level jobs or internships. But once they actually find themselves in those roles, they may not know how to produce exemplary results or stand out among their peers. The following Excel tips and tricks are particularly useful for college students entering the workforce and pursuing positions such as Product Analyst or Data Analyst. These tips and tricks will allow students to produce reports and results more quickly, extrapolating meaningful data that can help them and their office better achieve business goals.
A. Use Flash Fill and templates to save time when generating reports
After entering the names of all 200 employees at his company, Dylan realizes he made a mistake. Company protocol requires that the names be entered in the format last name/first name/middle initial, not first name/middle initial/last name. In this instance, Flash Fill is a useful Excel trick that can help Dylan save time and quickly reorganize that info without having to type it all in again by hand. To use Flash Fill, Dylan can simply type the correctly formatted value in a cell that’s to the right of the incorrectly formatted value that needs to be corrected. Then he can select the next blank cell underneath, select “Fill” on the “Home” tab, then “Flash Fill,” and the incorrect data will be replaced by the corrected version.
B. Incorporate graphics in charts to enhance reports and presentations
One of the final assignments for Sarah’s internship at a major sporting goods company is to create a comprehensive report that details the effectiveness of her social media marketing campaign. Even though Sarah has strong enough insights to prove the campaign was a success, she’s worried that the standard graphs and visualizations she uses won’t resonate with the leaders in her company.
In this instance, Sarah can incorporate graphics and pictures to help her charts stand out and be more widely appreciated across her company. She could simply select the area of a chart where she wants the picture added, click the “Insert” tab, select “Pictures,” and then choose which image or graphic she wants to include. Sarah could use logos for social media brands that correspond to each area of her chart.
C. Apply IF formulas to concisely organize and locate data
William is working as an administrator in his first job out of college where one of his recurring tasks is to plan Friday afternoon social gatherings for his office. William is allocated a different budget each week for the gatherings, but his supervisors want to know on a month-by-month basis if they’re going over budget. A useful Excel trick William can use are IF formulas to effectively organize that data and find results. Above the sheet area there is an “fx” icon, signifying function, where Excel users can select different analysis or interpretation tools they want to use. William can click on the “fx” icon and an equal sign (=) will appear. If William has his values listed in the A column, he can use corresponding cells in the B column to input IF formulas that would determine if the value of a specific cell in column A is over budget or under budget.
This is a simple way to use the IF tool, but its ability to effectively sum up data and find conclusions is immensely helpful in various aspects of day-to-day office life.
Chapter 3: Common Excel Mistakes and Mishaps to Avoid
Excel is a powerful tool that, when used correctly, can efficiently organize and interpret data while increasing productivity. However, many users often encounter errors or unexpected problems when using Excel, causing them to become frustrated by the program. How college students or entry-level professionals use Excel throughout their academic and professional careers will determine the specific types of Excel problems they face. But there are some frequently occurring Excel problems that can be avoided if users are knowledgeable and cautious.
A. Be careful when using Cell Formatting to round up numeric data so as to not get incorrect figures
Megan’s internship supervisor wants to track the amount of money the company spends per month on digital advertising. In the past, Megan’s supervisor has only requested the dollar amount spent and not any additional cents that may have been left over. So Megan has been using one of the Excel tips and tricks called Cell Formatting to round specific dollar numbers. This tool is available on the homepage under the “Number Format” drop-down window. For example, if Megan entered $19.25, it would appear as $19; if the value entered was $28.89, it would be listed in the sheet as $29. The problem, though, is that over the 500-plus digital advertising transactions the company makes each month, since those smaller cents don’t appear on the sheet, Megan has an inaccurate portrait of how much money the company is spending. While the formatted cells may give her a value of $1,000 in total, the cells in an unformatted version may provide values much higher or lower than $1,000 because they incorporate those smaller digits. Cell Formatting is a helpful tool to keep data looking clean and organized, but one should always approach it with caution to make sure it’s being used to accurately present data.
B. Be wary when using numbers as column headings
For a science class, Tim wants to find an enticing way to organize and present visual data regarding how many dinosaurs existed during each prehistoric period. He already has the information on hand from textbooks, and he starts putting it into Excel into two separate columns: left for specific prehistoric era and right for how many dinosaurs perished during that time. But now that Tim has entered his first set of numbers into the first row of Excel, when he tries to find a bar graph or pie chart to visualize that data, it doesn’t display with the proper titles for each group of information. It’s a common mistake to make, and an easy one to avoid. Always make sure that all column headings are text and not numbers. That way, Excel can accurately organize and interpret that data, allowing users to find the best visualizations.
C. Don’t overwrite or delete data, even if it doesn’t seem useful anymore
Jill started working as an athletic assistant for her university’s football team her first semester of college, where one of her duties was organizing information pertaining to how much the team had spent on equipment. By her senior year, Jill had risen in the department and was now consulting on the decision on which jerseys to buy. Jill knew that she had organized the data on how much they had spent on jerseys over the past three years on Excel, but when she went to open her file, she discovered that she only had the most recent year’s expenditures, as she had deleted the data from previous years, thinking it would not be important or relevant anymore. It’s true that one of the most useful Excel tips and tricks is to simply never write over any data. This applies to students who are graduating and think their spreadsheets compiled during college may not be useful anymore and to those who are starting their careers and don’t believe they’ll need the info organized into Excel sheets from earlier positions. Simply using more sheets within the same Excel file or using multiple Excel files that aren’t adjusted or tampered with can help students avoid unnecessary headaches down the road.
Sources: Microsoft, “Microsoft Excel” Microsoft Office, “Create a chart from start to finish” Microsoft Office, “Create a chart with recommended charts” Microsoft Office, “Enable Flash Fill in Excel” Microsoft Office, “Fill data automatically in worksheet cells” Microsoft Office, “IF Function” Microsoft Office, “Recover an earlier version of an Office file” Microsoft Office, “Use formulas with conditional formatting” Microsoft Office, “VLOOKUP function” Microsoft Support, “Columns and rows are labeled numerically in Excel” Microsoft Support, “How to control and understand settings in the Format Cells dialog box in Excel” Microsoft UK Small and Medium Business Blog, “5 Benefits of Microsoft Excel” Payscale, “Entry-Level Product Analyst with Microsoft Excel Skills Salary” Payscale, “Entry-Level Data Analyst with Microsoft Excel Skills Salary”