There are hundreds, if not thousands of shortcuts, formulas, and features to learn in Excel. These six are merely the beginning.
By Jenna Cyprus
If you’ve used a computer in the past, it shouldn’t take you long to figure out the basics of how to edit and use a spreadsheet in Excel. It’s designed to be intuitive, so after a few minutes of playing around, you can probably grasp how to create a table or a chart of information for whatever application you need. This is good, considering the average professional spends about 10 percent of their working life working on spreadsheets.
However, if you plan on using Excel as an organizational and planning tool in your small business, it’s wise to learn the functions and formulas that enable more advanced features, and can help you save time as you use the software to accomplish your goals.
The Most Important Excel Functions
These are some of the most important functions and formulas to learn:
- Paste special. Copying and pasting is a standard computer function most of us are familiar with. A simple combination of CTRL + C and CTRL + V can help us carry information from one area to another. However, in Excel, you may find yourself wanting to copy a specific number rather than a formula or a section of text without its existing formatting. After copying, use CTRL + Alt + V or right click to bring up the Paste Special menu, where you’ll have far more options for how you want to paste the information.
- VLOOKUP is a lookup and reference function in Excel, designed to make it easier to get answers to your questions or find a specific piece of information in a given table. For example, you might need to look up the unit price for one of your products in a massive table of information; rather than manually searching for the name of the product, you can use VLOOKUP to enter the product name, and instantly get the answer. VLOOKUP takes some time to learn and even more time to master, but once you get the hang of it, you’ll be able to find practically any piece of information you want much faster.
- Flash Fill. Head to File Options and click Advanced to ensure that Flash Fill is turned on. When it’s activated, Excel will automatically detect when the data you’re entering has a noticeable pattern to it, and it will take action to preserve that pattern automatically. For example, if you’re entering product numbers as AA-1, AA-2, AA-3, and so on, Excel will automatically suggest AA-4, and so on, so just click enter to fill it automatically.
- Remove Duplicates. No matter how meticulous you are, there’s a chance you could end up with duplicate values in a spreadsheet. If you’re collaborating, it might mean that someone entered the same information you did, and if not, you might accidentally re-add an entry you forgot you added much earlier. In Data, under Data Tools, you can find the Remove Duplicates option. You can also simply highlight the duplicate entries (so you can choose what to do with them) by using Alt + H + L, or by looking under the Styles tab.
- Freeze Panes. This one is useful if you’re ever scrolling down a table of information and need to remember which of your columns are which. With it, you can freeze the top row, the top few rows, the left-most column, or any number of columns. All you have to do is select the columns or rows that you wish to “freeze” in place, then head to the View tab and click Freeze Panes. You can also use the keyboard shortcut Alt + W + F.
- Sum, Count, Average, Min, and Max. We’re grouping all these formulas because you’ll likely use them in similar contexts, and they’re all equally easy to learn. To use them, click any blank cell, type an =, then type the word of the formula you’re calling upon (i.e., SUM, COUNT, AVERAGE, etc.). Type an open parenthesis, then highlight or enter the range of cells you want to find the value for. When done, type a closed parenthesis or click enter, and you’ll automatically perform the calculation. Sum finds the sum, Count determines how many cells are in range, Average finds the average, and Min and Max find the lowest and highest value in the range, respectively.
Learning Even More Functions
There are hundreds, if not thousands of shortcuts, formulas, and features to learn in Excel, so these six are merely the beginning. If you want to become an Excel master and use it for more areas of your business, or simply use it more efficiently, keep looking for new information and experimenting to find new tricks. The extra effort is worth it.
Jenna Cyprus is a freelance writer from Renton, WA who is particularly interested in travel, nature, and parenting. Follow her on Twitter.