For many of us that live in Excel every day, we get a little giddy to learn about time savers. We search out anything that can help us be more productive. I’ve created macros and templates, but Excel offers many other great opportunities for increasing your efficiency by making it easier to work using formula tips.
One way is through using formulas and functions. The three formula or formula-related tips that I think will help dramatically improve your productivity are using the VLOOKUP function, exploring text functions, and copying the results of formulas.
VLOOKUP formula tips
One of the biggest time wasting activities in Excel is having to look up items manually in a list. The function VLOOKUP in Excel will make it possible to look up words or text in a separate list. For example, you might have a vendor number that can be looked up in a vendor list. The function would return the vendor’s contact information to you.
The function needs some basic items in the argument but will save you hours of time scanning a list to look up a number. Check out this Excel Pro Tip post to get the details to work with the VLOOKUP function effectively.
Sometimes less experienced Excel users avoid using formulas, especially related to text. This may be because they don’t know the functions exist or they think it will take too long to understand. So, these time-savers are overlooked.
It really is worth the time investment to explore the large number of text formulas. For example, you might want to adjust the formatting or even change a number to a label to use in another formula.
If you are interested in spending a few minutes learning about a few of these functions, check out the Save the Day with Excel Text Functions post.
Copying Formula Results
Once you have set up your formulas and functions, you might need to copy the formula results to a different range on your current worksheet or even to another file. Copying and pasting the formula seems logical, but it often results in an error. See Figure 1. If you want to copy and paste correctly, you have to use a Paste Special or Paste Options.
Instead, you can copy the cell and then use a Paste Special. Click where you want to Paste and use the shortcut Ctrl+Alt+V to bring up the Paste Special window. In the Paste section, choose to paste Values. Click the OK button. See Figure 2.
Now you will have the results of the formula pasted instead of the #REF error. The other option is to do a regular paste but then click the Paste Options button to select the Paste Values choice. The Paste Options button will appear in the bottom right corner of the cell or cell range that you paste. See Figure 3. The Paste Values choice is the first choice under the Paste Values section heading.
Try out these quick Excel productivity tips and let us know if they help you be more efficient. Make sure to add your own formula tips in the comments, too.
Image credit: https://www.flickr.com/photos/awskylershepard/