Microsoft Excel is a powerful spreadsheet and data analysis tool with wide-ranging capabilities. In this article, we'll review the top 10 things you absolutely need to know in Excel, from Pivot Tables to formulas and functions.
Multiple Files
When working with several Excel files at once, opening files one-by-one becomes tedious, fast. A handy shortcut for opening multiple files is to select all desired files, then press the Enter key to open them all simultaneously. Ta da!
Customized Shortcut Menu
Excel usually has three shortcuts in the top menu by default: Save, Undo, and Redo. However, if you want to have more shortcuts readily available, such as Copy or Paste, they can be easily added. Go to File > Options > Quick Access Toolbar and then select what you want to be added to Shortcuts.
Paste Special
Most of us know the basics of copy and paste (CTRL + C to copy; CTRL + X to cut; CTRL + V to paste; works across Microsoft and many other applications), but did you know about Paste Special?
When using standard copy and paste, Excel copies everything from the cell—formatting, cell values, comments, etc. If you only want to copy a part of a cell, using Paste Special (first copy with CTRL + C, then use CTRL + Alt + V, or Paste Special in the Home tab).
When the Paste Special dialogue box appears, you can choose which parts of the cell you want pasted. You can even add, subtract, divide or multiply, or transpose the cells.
Remove Duplicates
Sometimes values are duplicated when they shouldn’t be. Excel makes it easy to find and remove these duplicates. First, click on any single cell inside the data set. Go to Data > Data Tools > Remove Duplicates. Follow the prompts on the dialog box that appears, and that’s it! All done.
VLOOKUP
The VLOOKUP function is perhaps the most crucial function in Excel allowing users to retrieve data from a table.
In the example below, we could “look up” Revenue - Sales and have it return February 2004 at a value of $8,221.52.
The VLOOKUP function has four inputs: the lookup value, the table, column number, and lookup type (exact or approximate). Learn more about it in our VLOOKUP tutorial.
INDEX-MATCH
This powerful function is an industry favorite. When looking for specific information within a huge table of data—such as a retailer looking up particular items in stock—INDEX-MATCH lets users look up the value of the items according to a unique identifier, such as item number, and return the desired information. So, instead of wading through thousands of values, INDEX-MATCH helps you find exactly what you’re looking for in a snap.
PivotTables
PivotTables allow users to create impressive, interactive reports for large data sets. How is it interactive? Well, PivotTables can take thousands of rows of data and summarize selections of that data in a meaningful way. A viewer’s attention can be “pivoted” to certain statistics, such as specific date ranges, summaries, items, etc.
Conditional Formatting
Conditional formatting easily lets you emphasize certain cells or ranges of cells in order to better visualize and analyze data. What conditional formatting does is alter the appearance of cells according to conditions that you specify. For example, you can add color to cells, or icons. If conditions are true, the cell range is formatted; if the conditions are false, the cell range remains the same.
Absolute References
Absolute Reference is a way to assign a specific formula to a specific cell. It’s a constant that must contain a dollar sign ($) in the row or column coordinate, or both. This is useful for when you copy/paste the formula into other areas of your spreadsheet because the dollar sign tells Excel to keep the original reference.
Input Restriction with Data Validation Function
Data changes all the time, and input restrictions allow you to maintain order within your spreadsheet. For example, if you’re only inputting data that follows specific conditions—amounts, colors, age, price—you want to make sure all incoming data follows your restrictions in order to maintain validity. Using Data Validation, you can do just that. Go to Data > Data Validation > Setting, and input your conditions and shift to Input Message to create prompts, such as “Only input amounts between 50-100” or “Only include items that are categorized blue.” Anyone inputting data will see your message and be able to follow your prompt!
Master Excel
There's so much more to Excel than just these tips and tricks. Learn how to execute complicated tasks to create dynamic and interactive data sets. While you're at it, learn how to integrate Excel elements into other software available through Microsoft Office—such as PowerPoint—in order to create impressive presentations and reports for clients.
We offer the best Microsoft Office training in NYC. Our expert instructors guide students of all levels through step-by-step projects with real-world applications. Sign up individually, or contact us about corporate training: