Skip to content
Tips, Tricks, and Excel Hacks: What You Should Know

Tips, Tricks, and Excel Hacks: What You Should Know

Microsoft Excel is a program that most people know about. It is on most computers, and lots of students just have it there. However, they do not know the great attributes that are going to waste.

I have used Excel for multiple uses in my daily life. I have made personal grade calculators, master schedules for my friend group, and even an easy way to schedule potential classes for a future semester.

info-png

Simple Formulas

First things first, you should know simple formulas and how they work: SUM, AVERAGE, MAX, MIN, and QUOTIENT. Remember to always start a formula with an equal sign (=). This will get you through simple calculations. You can always just use excel as a calculator, too, but typing in something like =10*63 into a cell. Or, you can type the formula like =C2*100 to directly reference a cell.

Some more intricate functions that are necessary for jobs are INDEX and VLOOKUP. I have been at multiple meetings with recruiters for large companies through the Whitman School of Management, and they have asked if I know how to do these specific functions.

Both are useful when handling large data sets.

INDEX

The INDEX function shows a value within a given location of an array, or table, and matches a cell value with the column number and row number. Basically, it matches a column number and row number to get you the value that is in that exact location. This helps the user to not have to search for everything by hand. When there is a very large set of data, this can be helpful to not get lost in all of the data.

The function is =INDEX(array, row number, column number).

For example, say there is an array of a prior presidential election with every state in a column,  the top row has the candidates, and every corresponding cell within the array holds the percentage of the candidate’s winnings in that state. The function would be =INDEX([table of all results],[row number of state wanted],[column number of candidate wanted]). This would get the answer to the related location of the wanted percentage.

Take the next step.

Harness the power of information with a Bachelor's in Information Management & Technology.

Get Started

VLOOKUP

The VLOOKUP function is one that is like the INDEX function, but it matches a number to a number within a table, and then it matches the row to either a number or category. This is helpful when trying to put a term to a certain range of numbers. If you are trying to match a number with a certain term, like ‘100’ and ‘A’, this is the function to use. The function is =VLOOKUP([lookup value to match the array], [table array to match], [column number to match the value to, number or term], [0 for exact match, 1 for a range lookup]).

Table Tool

Another great tool is the table tool. This makes any set of data into an interactive table. Within this table, you can make the table sort in different orders, or only see certain categories within the table.

To use this, you simply go to the ‘Insert’ tab, and then click the ‘Table’ button. This action will automatically find all the cells with data in them, and create a table with drop-down lists for the headers. This drop-down list will allow you to choose how you want the entire table to be sorted. Some options include: “Sort A-Z”, “Sort Z-A”, “Sort Largest to Smallest”, “Sort Smallest to Largest”, and if you only want certain attributes to be shown, the user could choose which ones he/she wants.

PivotTables

PivotTables are another great tool that Excel has. This simple way to make charts that compare numbers within the table. Having this tool can use the table to get information from specific categories. For example, there is a table with different types of houses, prices, location, and the number of square feet. With this table, you can compare only one or multiple categories, have the numbers as shown as percentages of category total.

Just clicking around Excel and practicing can really make a difference in the skill level you have with the program. Knowing this information helps with future jobs because some employers want these skills, and create a great impression. These tricks can also make your life easier by making personal grade calculators, schedules, and budgets. Plus anything else you could possibly need or want!

Sydney Sokol

Sydney Sokol

I’m Sydney Sokol '20, an accounting major in Whitman. I like the gym/nutrition/working out, shopping, hanging out with friends, and doing community service. Technology is very involved in my life because I need Excel to do simple functions for my major and personal life.

More Posts