Simple & Handy Excel Shortcuts and Functions for School and the Workplace
Written by Alexandra Embree
Thumbnail Photo by Christin Hume on Unsplash
Photo by Christin Hume on Unsplash
Microsoft’s Excel is a tool that is ubiquitous in the modern world. You’ll find Excel spreadsheets in workplaces of all domains, especially in smaller enterprises who don’t have access to more robust software packages. The sheets may be used to track complex projects, sales or orders, attendance, applicants, or journal entries. You name the intent, and there’s a spreadsheet format for that.
Proficiency with the MS Suite, including Excel, is a requirement for a large majority of office-based jobs. While most students are probably quite familiar with the basic functions of Excel by the time they reach university or college, in many cases this basic familiarity is just the tip of the iceberg. It can be jarring to recognize that when you see Excel wizards jumping across a spreadsheet without laying a finger on the mouse or writing complex nested formulas that seem indecipherable.
I’ve compiled some of the most fundamental shortcuts and formulas of Excel in the lists below. These tricks can help get the most out of your data for a research report, organize a shared spreadsheet, or simply impress your classmates and colleagues.
Shortcuts (Windows OS)
Shortcuts are how Excel wizards dart across the screen without taking their hands off the keyboard. For those who spend long stretches of time working in spreadsheets, shortcuts can significantly reduce frustration and increase efficiency. Shortcuts do take a lot of practice to become totally proficient in, but for those in professions like accounting and finance, they are lifesavers. The following five are pretty simple to try for yourself - note, however, that the listed key combinations may be slightly different for Apple users.
1. Alt + = : Pressing the ALT key and the = key at the same time auto-sums a column or row. The row or column is detected automatically – no manual selection needed!
2. Ctrl + Arrow keys, shift + Arrow keys: Holding CTRL while using the arrow keys to navigate allows you to jump across the spreadsheet with ease. Holding Shift while using the arrow keys will allow you to select a row, column, or block of data of any size without having to drag the mouse.
3. Ctrl + Shift + End: When this command is executed from the top left of your spreadsheet, all data contained will be selected at once, which is handy if you want to apply universal formatting or create a table.
4. Ctrl + Space, Shift + Space: These keyboard shortcuts allow you to select an entire row (Shift + Space) or column (Ctrl + Space).
5. Ctrl + Shift + L: Pressing the “L” key while holding CTRL and Shift will add filters to an entire row. This is a really easy way to quickly filter through a large dataset to find and view records that are of interest.
Functions
Functions are the user-input formulas that allow you to make computations within your spreadsheet. Some of the more elementary functions allow you to do things like get the sum of a row, calculate a standard deviation, or determine a median; they essentially allow you to use Excel as a handy and powerful calculator. Functions can do much more than simple computations, however. They are one of the best tools to master for the average user, as they can be applied to countless tasks. Some of my favorite functions are below:
1. SUMIF
Syntax: SUMIF(range, criteria, [sum_range])
The SUMIF formula is a handy conditional sum. It will sum all numbers in a row that contain values that correspond to your condition. For example, if you want to sum only values less than 100 in the B column rows 2 to 25, you would use the following formula: =SUMIF(B2:B25,"<100"). You can also use the values in another column as the condition for summing the column. For example, if in another column there were employee names and you wanted to sum the value of all orders created by “Jane”, you could do that with a SUMIF function.
2. VLOOKUP
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function initially looks pretty intimidating, but is relatively simple once you get your head wrapped around the syntax. This function is an abbreviation of “vertical lookup” and essentially allows you to get a return value that answers a question with this format: “What is the value that corresponds to this other value in the same row?”. For example, if you wanted to determine the price of a product with a given product code, a VLOOKUP would help you accomplish this by checking for the product code in a table and then scanning vertically to pick up and return the price. You can also reference data in a different sheet within an Excel file for VLOOKUPs.
3. IFERROR
Syntax: IFERROR (value, value_if_error)
The IFERROR function is a handy function to nest other functions into. This is because it can help catch and alter errors that can arise from using other functions, such as #N/A, #REF!, or #DIV/0 errors. You can set the fallback value of a cell to 0 or 1, or create an error message that can explain to others what went wrong with the formula and how the situation can be remedied.
4. INDEX-MATCH
Syntax: INDEX(range, (MATCH(lookup_value, lookup_array, [match_type])
Index and match are technically separate functions, but they are often combined into a nested function. INDEX(MATCH()) is the most advanced function on this list, but is also one of the most popular advanced combinations. It is frequently used as an alternative to VLOOKUP, as it can achieve a similar goal but skirts some of the limitations of VLOOKUPS. One such limitation of a VLOOKUP that can be avoided by using INDEX-MATCH is that the VLOOKUP formula requires the data you want to look-up to be to the right of the reference that you use, while the INDEX-MATCH formula allows you to have more flexibility with your lookup table design,
5. COUNTIF
Syntax: COUNTIF(range, criteria)
The COUNTIF function is similar to the SUMIF function in the sense that it is also based on an “IF” condition. In this case, the condition being true will add one to a count tally. This function is useful for getting a sense of how often a certain value appears within a given range.
Tip of the Iceberg
These formulas and shortcuts, while great, are just a small fraction of things you can learn to develop your Excel skills. Advanced Excel users can even create their very own function definitions using Microsoft’s Visual Basic! Not everybody has to be an advanced user or has a use for advanced Excel features, but keeping an open mind when facing novel problems in your spreadsheets will get you far. For future research, Microsoft’s support website has many great information pages about functions and features in Excel complete with examples to help you nail down new concepts.