Lookups and PivotTables
1. Lookups and PivotTables
In the final part of this course, we will examine lookup functions and ways to summarize your prepared data.2. Lookup and reference functions
Excel has many lookup and reference functions. But what are they exactly? Think of them like Excel's address book. In an address book, you have names, numbers, and addresses all sorted alphabetically. You can find the information you want by using a letter of the alphabet. Lookup and reference functions give you the same power in Excel.3. Two LOOKUP functions
We will focus on two lookup functions - VLOOKUP and HLOOKUP. VLOOKUP stands for vertical lookup, while HLOOKUP stands for horizontal lookup. They are used to look up specific values in a dataset and provide results related to that value. You can also use them to add information to your dataset from other Excel sheets or files.4. VLOOKUP
VLOOKUP searches the data vertically, so across columns. It requires three parameters, with a fourth optional one. The function requires a lookup_value, which is what you want to look up in your data. The table_array is where you will for the value, and the col_index_num is the column number containing the value to return when counting from left to right. The optional range_lookup is set to FALSE if the lookup_value is an exact match to the data in the lookup column, or TRUE if you are searching for an approximate match.5. VLOOKUP
What does this look like in action? Say we want to find Rossy's test score from our student exam data.6. VLOOKUP
VLOOKUP can help us do this. Our lookup value can be a cell reference, as shown. Our table array will start for the column with the lookup value, and our column index will be the number of the result column in the order of our table array. We can now see that the test score for Rossy is 66.7. HLOOKUP
HLOOKUP is similar to VLOOKUP as a function, but instead of searching vertically, it will search the data horizontally. This means it searches the rows first and provides outputs in the same column.8. HLOOKUP
Let's use some sales data to see how the function operates. We wanted to identify the total sales amount of bananas in 2017.9. HLOOKUP
Using HLOOKUP, we input the look_up value contained in its own cell. We select the table array and then put the number the bananas row is in the table from the top down. We set the range_lookup to false as we want an exact match of the lookup value.10. A new perspective
In our data preparation journey, we have dealt with raw, tabular data, each row representing a unique record. This has ensured we can efficiently clean, sort, and organize data. However, now we can start thinking about viewing our data from a different perspective, where we summarize the data differently.11. PivotTables
PivotTables are a feature in Excel that allows us to summarize and analyze large amounts of data. We can aggregate our data using PivotTables and organize it into new dynamic table structures. We can easily transform rows into columns and vice versa. We can group the data how we wish, filter, and even choose Excel's many aggregation methods. This dynamic feature helps us find trends in our data much more easily while providing a last step in ensuring our data is prepared and ready for analysis.12. PivotTables
For example, you work for an insurance company. You have to process a large dataset of hundreds of thousands of rows showing the insurance type of each customer.13. PivotTables
You can use a PivotTable to quickly identify the individual insurance types and the number of customers in each group. With just a few clicks in Excel, a long list of raw data was transformed into an easily adapted summary table.14. Let's practice!
Now you have seen the power of lookup functions and PivotTables; it's time to try them out!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.