March 30th, 2018 by admin
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:
- Vlookup/Hlookup
- Index+Match
- Conditional Formatting
- Pivot Tables with Charts
- 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:
- Your Lookup_value has to be in left most column of your table_array
- Inserting or deleting columns will ruin your lookup
- Your Lookup_value can’t exceed 225 characters
- 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
For most of the things you want to do, these preset options should be sufficient. You can play around with what they have given you, but if you are not satisfied with those options or they don’t do what you want them to, then you can choose to create a “New Rule” by selecting that option. Make sure you have selected the cells that you would like to apply this rule to first. You will most likely use the “Use a formula to determine which cells to format” option and can enter whatever formula you would like. You can also use the format button to choose your desired formatting style for your formula. This will then dynamically change the format of your cells based on the values you change within those cells.
Pivot Tables
Pivot tables are helpful when you have a lot data and want to be able to manipulate it into a more useful format. For example, you can rearrange how the data is summarized, add subtotals, add calculated fields, and use charts to visualize the data.
To create a pivot table, select all your data (Crtl+a), go to the Insert tab and select the “Pivot Table” button. It will then pull up the following popup:
The data selected will automatically populate in the Table/Range section and then you just need to choose where you want your pivot table to be located. Click “OK,” and the PivotTable Fields sidebar should appear on the right side of your screen. You can drag which fields you would like as the columns, rows, values, and filters. When you right click in your pivot table, then you can choose to summarize values on multiple options, such as sum, average, count, min, max, and others. Another helpful aspect of pivot tables is their charting capabilities. You can easily visualize your data by adding a chart. When you filter your pivot table, the chart will automatically update. A slicer, which is a filtering tool found on the Insert tab, can also help you filter both your chart and pivot table.
Recording Macros
If you find yourself doing a repetitive task (whether that be making charts, formatting, or anything else) then you might want to consider using macros. Excel has a “Record Macro” button that allows you to automate almost any task.
You might be thinking, “Why have I never seen this button before?” That’s because Excel has this tab defaulted to ‘hidden’. To access the “Record Macros” button, go to File -> Options -> Customize Ribbon -> and then under the tabs displayed, make sure that the Developer tab is checked. This tab will then appear and the “Record Marco” button should be visible.
When you click on “Record Macro,” it will prompt you to enter the following four things
- Macro name- Make sure that it is something that will remind you what this macro does, for example “Make Chart”.
- 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.
- Store macro in:
- “This Workbook” – this will run your macro only in the current workbook
- “New Workbook” – this will run your macro only in a new workbook that you create
- “Personal Macro Workbook” – this will run your macro in ANY workbook that you open
- Description – any additional information about your macro
Once you click OK, the macro starts recording everything that you click and do with your mouse. You can create your chart, or do whatever task that you have been doing repetitively, and it will record those actions.
After you are done, be sure to set your mouse to your next starting position, and then click “Stop Recording”. Then, whenever you either use the shortcut you created or click on “Macros” and run your macro, it will re-preform those same steps (e.g. make a chart).
You can look at the code for the macro by clicking on “Visual Basic” button and selecting the module. If you decide to research more about macros and VBA (the code language that macros use), then can learn to edit your recorded macros or even write your own from scratch!
Hopefully these tips are able to help improve your Excel knowledge. Depending on how much time you spend in Excel, experimenting with these tips can save you exponential amounts of time!
One extra tip: If you want to look like a real excel pro, use hotkeys! That allows you to do anything you want in Excel without having to touch your mouse. All you have to do is click the "Alt" button and then select the appropriate keys that popup for the action you want to do.Posted in: Tech Tips, Productivity, Technology
