For the regular knowledge worker, Microsoft Excel, 30 years on, remains the go-to product for people looking to make sense of data. Cut through the more than 500 Excel functions and you’re left with 100 or so truly useful functions and features. We’ve selected 10 that are especially easy to learn.
PASTE SPECIAL: Paste Special enables you to pick which elements of the copied cell you bring over. Alt+E+S+V is the shortcut to just paste values — probably the most common use of Paste Special.
ADD MULTIPLE ROWS: Often, just highlighting the number of rows you want to add and using right click, insert, is quicker when adding in bulk as it will add the number of rows you’ve highlighted.
FLASH FILL: Flash Fill automatically fills your data when it senses a pattern. If Flash Fill is turned on (File Options, Advanced) just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you.
INDEX-MATCH: INDEX and MATCH used in combination help you extract the data you need from a large dataset efficiently and precisely by allowing you to look up a value anywhere in the lookup table regardless of its position.
SUM: This is one of the first functions you’re likely to learn in Excel. But did you know you can select the cell at the end of a row or column and press Alt + to do this function in seconds?
CTRL Z/CTRL Y: If you aren’t using Ctrl Z to undo mistakes in Excel, then you should be. What many people don’t know is that Ctrl Y performs the opposite task.
REMOVE DUPLICATES: Remove Duplicates does exactly what you’d expect. It’s found on the Data tab in the Data Tools section of the Ribbon.
FREEZE PANES: Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section.
F4: There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about: F4 repeats your last action, where available.
CTRL + ARROWS: If you’ve found yourself scrolling through a data set to reach the bottom of it, stop right now and start using Ctrl + the arrow keys!
Whether you want to help justify data-driven business decisions at a high level or simply get home to your family earlier, mastering the right Excel functions is a quick and easy way to maximize your productivity.
(Adam Lacey is managing director of Excel with Business. Deborah Ashby is the information technology training manager at Excel with Business and Filtered.com.)
