The lookup value must be on the left in the lookup table. It can only look up values from left to right. VLOOKUP is a great function, but it has its limitations. Mastering these functions will not only make you look like an Excel whiz to your colleagues and manager, but can make a tedious, mundane task quick and simple. INDEX and MATCH used in combination help you extract the data you need from a large dataset efficiently and precisely.
Used separately, these functions are invaluable, but it’s when you combine them that their true power is unleashed. INDEX-MATCH (45 minutes to learn)Īside from VLOOKUP (which looks up the value in one column and returns a corresponding value from another column), INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. It’s a fantastic time saver when you need to input or change a lot of data quickly and accurately. Or, get it going manually by clicking Data > Flash Fill, or Ctrl+E.įlash Fill is like magic, and can be used in many different scenarios. 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. Now, this is much faster and will impress people.Įstablish the pattern by typing ‘00001’ into the first blank cell. Pre-2013 this was possible, but relied on a combination of functions (FIND, LEFT, &, etc). You can easily discard the ‘ABC’ using Flash Fill. ‘ABC-00001’ to ‘ABC-00010’ and you only need the numbers after the ‘-’. Suppose you have a list of product numbers in the first ten cells of column A, e.g. Flash Fill automatically fills your data when it senses a pattern.
Flash Fill (30 minutes to learn)Įxcel developed a mind of its own in 2013 with this feature. Often, just highlighting the number of rows you want to add (say 5) and using right click, insert is quicker when adding in bulk as it will add the number of rows you’ve highlighted. The shortcut (Ctrl, shift, +) is pretty handy, especially as you can toggle the + to add multiple rows. We often need to add new rows between existing rows. Alt+E+S+V is the shortcut to just paste values - probably the most common use of Paste Special.
After you’ve copied your cell (Ctr+C) hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Paste Special enables you to pick which elements of the copied cell you bring over. These little frustrations can take time to fix, which is why Paste Special is so… special. But we often carry over a format we don’t want, or we copy a formula over, when instead we just want a value. Paste Special (10 minutes to learn)Ĭopy and paste is one of the simplest and most used functions in Excel. If, like many, you’re stuck on what to learn next in Excel, you might want to look at this 2×2 matrix, which factors in usefulness and time needed to learn a skill. These ten are listed in decreasing order of utility from our top 100 list.
We’ve selected ten from that list which are especially easy to learn (approximately 2 hours total) and which can make a material difference to productivity. Cut through the 500+ functions, and you’re left with 100 or so truly useful functions and features for the majority of modern knowledge workers. Though every Excel feature has a use case, no single person uses every Excel feature themselves. Last year we put together The Definitive 100 Most Useful Excel Tips, for which we consulted Excel experts and looked at tens of thousands of test results and course usage data.
0 kommentar(er)
