Subscribe to DSC Newsletter

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:

  • VlookupYou 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. 
  • IndexReturns a value or the reference to a value from within a table or range.
  • MatchThe MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. 
  • MMultReturns 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 TablesA 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

Additional Reading

Follow us on Twitter: @DataScienceCtrl | @AnalyticBridge

Views: 29993

Comment

You need to be a member of Data Science Central to add comments!

Join Data Science Central

Comment by Barry DeCicco on June 15, 2017 at 4:50am

In my experience, sluggishness from a VLOOKUP function is not a major problem; INDEX/MATCH will cause sluggishness far sooner.

Comment by ®γσ, Lian Hu ENG on May 22, 2016 at 4:10am

Waohh, like a city!!! Excel can do it now... amazing !!!

Comment by Bob Vanderheyden on April 16, 2015 at 7:48am

I'm surprised to see that SUMPRODUCT function didn't make this list (calculates the inner product of two vectors/arrays).  It's great for calculating weighted mean, scoring OLS models, etc.

Comment by Paul Katsen on April 13, 2015 at 2:04pm

I always use index-match. It's faster and less brittle. Specifically, if you change column order, index-match does not break like Vlookup does.

Comment by John Stanham on April 13, 2015 at 8:35am

Thanks for the nice tips.  I followed on your MMult option which I was unaware of.  As it turns out, Excel also provides:

1. MDeterm - computes the determinant

2. Transpose - computes the trasnsposed matrix (also available in the Copy->Paste Special->Transpose option

3. MInverse - computes the inverse function

All very handy in modeling too.

Comment by Vincent Granville on April 11, 2015 at 5:57am

A reader wrote: 

In terms of speed, INDEX (MATCH) beats VLOOKUP for >1 column

Follow Us

Videos

  • Add Videos
  • View All

Resources

© 2017   Data Science Central   Powered by

Badges  |  Report an Issue  |  Privacy Policy  |  Terms of Service