Have you ever been two hours into a tedious Excel task and thinking “There has to be a simpler way to do this!” Well, you can rest your weary, bloodshot eyes, because there is a simpler way! If you are just starting in Excel we recommend checking out our Top 10 Easy Excel Shortcuts to Boost Your Productivity. But for those of you who have the basics down, enjoy these five intermediate Excel tips that will take your skills to the next level. In this article we will be discussing:
1. Vlookup/Hlookup
2. Index+Match
3. Conditional Formatting
4. Pivot Tables with Charts
5. Recording Macros
Vlookup and Hlookup When do vlookup and hlookup come in handy? Let’s imagine you have two sheets. One is a list of 200 names. The other is a list of thousands of names (including the 200 on your other list) with their respective addresses, phone numbers, emails, and other information. All you need is the 200 email addresses that correspond with your first list of names. You could spend hours hitting “control+f” digging through your huge list of names and copying over email addresses, or you could save yourself the cheese-grater to the forehead and use a vlookup. (Note: Vlookups help find information in a vertical list. Hlookups help find information in a horizontal list. Both formulas work the same way, so we will just focus on vlookups with this example.) You can use the following formulas: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])   And this is what those formulas mean in English: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
 lookup_value This is the value that you have and want to match from the other list (in our example this would be the name from your first list of 200 names). table_array This is the table that you want to find your value from (you would just select the list of thousands of names with all of their contact info). col_index_num This is the number of the column in your “table_array” that you want a value returned from (if the emails are 3 columns over from the name, this would then be 3). [range_lookup] This is either TRUE or FALSE. TRUE would return the closest value and FALSE only returns a value if there is an exact match (in our example, we would want FALSE).
However, vlookup has some drawbacks:
1. Your Lookup_value has to be in left most column of your table_array
2. Inserting or deleting columns will ruin your lookup
3. Your Lookup_value can’t exceed 225 characters
4. It is slower than Index+Match
Index+Match Index+Match is a combination of two formulas that, when put together, do the same things as our vlookup function. So why use two formulas? Because Index+Match removes the issues that vlookups has. Using the same example from above, here are the two formulas and what they do:   =INDEX(array, row_num, [column_num])
 array This is the range of cells you want to return a value from (similar to our table_array above – e.g. the list of thousands of name with their contact info). row_num This is the row number in in your array that you want to return your value from. If omitted, then column_num is required. [column_num] This is the column number in in your array that you want to return your value from (similar to the column_index_num above – e.g. column 3 with the email addresses). If omitted, then row_num is required.
=MATCH(lookup_value, lookup_array, [match_type])
 lookup_value This is the value you have and are looking for (same as the lookup_value above – e.g. the name we are trying to find an email address for). lookup_array This is the range of cells you want to return a value from (similar to the table_array above – e.g. the list of thousands of names and contact info). [match_type] This is either 1, 0, or -1. If 0, then it will return an exact match only, whereas 1 or -1 returns the next largest or next smallest value to the lookup_value. Most of the time you will want to use 0. (This is similar to the [range-lookup] above.)
When you put these two functions together, they look like this:                 =INDEX(array, MATCH(lookup_value, lookup_array, [match_type])) But it may be easier to think of the function like this:                 =INDEX(column to return a value from, MATCH(lookup value, column to look up against, 0)) So overall, Index+Match is the best way to lookup information from lists and tables. Vlookups should be used when doing quick, simple lookups.   Conditional Formatting If you are constantly changing formatting based on the contents of your cells, then you should consider using conditional formatting. For example, if you want cells above \$5,000 highlighted red, then use conditional formatting. Click, “Conditional Formatting” on the home tab to view the following options:
• Highlight Cells Rules
• Top/Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
1. Macro name- Make sure that it is something that will remind you what this macro does, for example “Make Chart”.
2. Shortcut key- Create a shortcut key for future use. Be careful not to use a shortcut that is already taken! You don’t want to record a macro and use the shortcut Ctrl+s (shortcut for Save) and accidentally run your macro instead of saving your work.
3. Store macro in:
1. “This Workbook” – this will run your macro only in the current workbook
2. “New Workbook” – this will run your macro only in a new workbook that you create
3. “Personal Macro Workbook” – this will run your macro in ANY workbook that you open