First, let's start with an article featuring many great Excel functions, entitled 11 Advanced Excel Tricks That Will Help You Get An Instant Raise At Work. It describes the following Excel functions:

• Vlookup: You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. 
• Index: Returns a value or the reference to a value from within a table or range.
• Match: The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. 
• MMult: Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.
• Pivot Tables: A PivotTable report is an interactive tool that combines and compares data. You can rotate its rows and columns to see different summaries of the source data and display the details for areas of interest. Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.

Excel map created with PowerMap

The second article that we selected discusses several Excel tricks:

• Jump from worksheet to worksheet with Ctrl + PgDn and Ctrl + PgUp
• Jump to the end of a data range or the next data range with Ctrl + Arrow
• Add the Shift key to select data
• Double click to copy down
• Use shortcuts to quickly format values
• Lock cells with F4
• Understanding Absolute and Relative Addresses in Excel
• Summarize data with CountIF and SumIF
• Pull out the exact data you want with VLOOKUP
• Use & to combine text strings
• Clean up text with LEFT, RIGHT and LEN
• Generate random values with RAND

Click here to read the article. Note that the Excel VLookup function can be extremely slow.

Finally, we found a great article about advanced Excel features. It discusses the following topics:

• One Click to Select All
• Open Excel Files in Bulk
• Shift Between Different Excel Files
• Create a New Shortcut Menu
• Add a Diagonal Line to a Cell
• Add More Than One New Row or Column
• Speedily Move and Copy Data in Cells
• Speedily Delete Blank Cells
• Vague Search with Wild Card
• Generate a Unique Value in a Column
• Input Restriction with Data Validation Function
• Fast Navigation with Ctrl + Arrow Button
• Transpose Data from a Row to a Column
• Hide Data Thoroughly
• Compose Text with &
• Transforming the Case of Text
• Input Values Starting with 0
• Speed up Inputting Complicated Terms with AutoCorrect
• One Click to Get More Status
• Rename a Sheet Using Double Click

Also, check out our section 6 of our data science cheat sheet. We plan to add many additional examples of Excel spreadsheets, some with advanced computations / modeling, and some for beginners.

DSC Resources

• Career: Training | Books | Cheat Sheet | Apprenticeship | Certification | Salary Surveys | Jobs
• Knowledge: Research | Competitions | Webinars | Our Book | Members Only | Search DSC
• Buzz: Business News | Announcements | Events | RSS Feeds
• Misc: Top Links | Code Snippets | External Resources | Best Blogs | Subscribe | For Bloggers

Additional Reading

• Data Scientist Reveals his Growth Hacking Techniques
• 10 Modern Statistical Concepts Discovered by Data Scientists
• Top data science keywords on DSC
• 4 easy steps to becoming a data scientist
• 13 New Trends in Big Data and Data Science
• 22 tips for better data science
• Data Science Compared to 16 Analytic Disciplines
• How to detect spurious correlations, and how to find the real ones
• 17 short tutorials all data scientists should read (and practice)
• 10 types of data scientists
• 66 job interview questions for data scientists
• High versus low-level data science

Follow us on Twitter: @DataScienceCtrl | @AnalyticBridge