6 Time-Saving Productivity Tasks You Didn't Know Excel Could Do
Excel’s latest version updated the functionality of the program, so it’s time to update your performance with the software as well. Who wouldn’t want to save time?
Excel 2013 has been enhanced as a data analyzer tool that doesn’t require a tech degree to use, however, following these helpful tips will make you appear like a Silicon Valley employee.
Knowing your way around the spreadsheet service is an extremely sought after skill, and if you can be knowledgeable and productive you’ll be more than sought after - you’ll be needed.
Some, like Forbes and Business Insider, have argued that Excel is the most important computer software program used in the world today. It seems that almost every field now uses the program in some form.
My friend took a Public Relations job right out of college, and the first time I asked her about the job she replied that her business card should read “glorified spreadsheet maker.” Now, it’s almost impossible to get her to stop sending out Excel documents, especially with the new social media share option in the latest version. In a very organized and rare moment for a group of 20-somethings, my friends and I logged into a spreadsheet to share what we were contributing to a ski trip (beer and microwavable pizza, some things never change).
I’ve had my own professional experience with Excel too. I’ll have to thank my poor math skills and a STAT professor’s extra credit for turning me into an Excel-lover and Excel-qualified. My professor offered homework points for using Excel formulas and tricks he would casually mention in class. I was desperate for those points, and while I will never be a statistical genius (survey says no), by the end of the semester I was confidently skilled in the spreadsheet program. That turned out to be of more use to me than I could have imagined.
Many people will write on their resume "proficient in Excel" but I was able to stand out as a candidate numerous times because I knew more Excel shortcuts, more features, than others.
Let's be honest, time is money, which is why knowing these 6 productivity-enhancing tips is gold.
6 Best Excel Features:
Once you know these 6 tips, you can expand your knowledge of Excel even further and really master the system.
Let's start with VLOOKUP (the function that allows you to look up a value in a spreadsheet, in case you forgot).
1. Going Only Left to Right is DONE
When you're selecting an entire array, a VLOOKUP on a massive spreadsheet can take forever, since it can only search in one direction.
Here's a better formula: = INDEX(column, MATCH(search value, search column,0)
MATCH returns the index of the value within the selected array, while INDEX returns a value based on a column array and a row number.
2. PowerPivot/Built-In-Data
I'll say it again, VLOOKUP can take forever. If you are a Professional Plus user then you can use PowerPivot to quickly relate columns across tables and analyze them instantly.
Or you can use the new built-in data model in Excel. With this service, you can link multiple tables with each other and generate Pivot reports with only a few clicks.
Be warned, knowing this trick will cause envy when you're finished your work and everyone else is still a slave to copy and paste in VLOOKUP.
2. IFNA()
If you’ve ever used Excel then you know the frustration of the #N/A value. Where did you go wrong? What happened? How bad is your karma that the universe would do that to you?
Now, there is the IFNA() function ready to save you. Its specificity is a time-saver, since it only considers the #N/A value.
IFNA(value, NAvalue)
where value references the formula you're checking for errors and NAvalue is the value you want returned if value returns #N/A.
If an argument references an empty cell, IFNA() treats it as an empty string (""). If value is an array formula, IFNA() returns an array.
Three cheers for that!
3. Timelines
How big is your spreadsheet? It seems like everyone is trying to one up the other for biggest data accumulation, but does anyone ever really want to look at that much data? More practical to show that you know how to access what you need for when you need it. This is where the timelines feature comes in, which lets you filter dates and through a much faster method than the auto-select way of the past. You can add timelines for any date column in a pivot table / pivot chart.
4. Distinct Count in Pivot Reports
Pivot reports now feature distinct counts of values! And with no extra formulas, making it a great time saver. Making those pivot reports (which are a quick way to see the big picture in your mountain of data) is also much easier with the new Recommended PivotTables function.
- select data and heading
- choose insert and Recommended PivotTables
- select the table you want and it will be drawn for you
Seriously, it's that easy. Excel knows you have better things to do.
5. Flash Fill
Your new favorite shortcut is now CTRL+E
This feature uses predictive data entry to detect patterns and extract and enter data that follows a recognizable pattern. Yes, it's a mind-reader.
Easily join names together, extract months, add initials, there are endless possibilities. MS OfficeVideo shows a short clip of the magic:
https://www.youtube.com/watch?v=UccfqwwOCoY
6. Goal Seek
Sometimes when doing what-if analysis, you have a particular outcome in mind, such as a target sales amount or growth percentage. The goal seek function can help find the input values needed to achieve the goal.
For example, if you ran a dog grooming company then you could look at a certain month to see how many dogs needed to be washed to reach 60,000 dollars or whatever goal you have in mind.
To use the Goal Seek feature located on the What-If Analysis button’s drop-down menu, you need to select the cell containing the formula that will return the result you’re seeking, then indicate the target value you want the formula to return as well as the location of the input value that Excel can change to reach this target.
Use this to figure out interest rates and other tricky formulas quickly.
Conclusion
Knowing and understanding the shortcuts and features of Excel can help productivity. Time can be saved by lessening formulas used , by using programming that can guess patterns, and by using tools to quickly access just the data immediately needed.
These are a few tips to get started with the system but there is always room for improvement when the program is so expansive!
Which feature is best for you when using Excel 2013? Any tip that leads to better productivity than the ones featured above?
