Get startedGet started for free

Referencing and summarization

1. Referencing and summarization

Using lookup functions, we can capture specific values and results from our data. In cell J8, let's add the string “Car Name”; next to it, we will write “Car Price”. Let’s make sure the data is sorted from lowest to highest price. We can use HLOOKUP now to find the third cheapest car in our data. Our lookup value will be the first cell of the column we want to look data up in - so B1. We now have to select our table array. The lookup value must always be in the first column in our table array. So we will select columns B to G as our table array. We set our row index number to 4 - as we have to include the header row into our lookup procedure. We want an exact match of the column name Car, so we will finish the formula with FALSE and press enter. So the third cheapest car is a Suzuki. Let’s repeat the formula, but we will use C1 for our lookup value to find the car’s price this time. The table array will be from columns C to G. What if we want to find the tenth cheapest car though? Let’s add a new header in I8 called Car Rank. We can add the number we used before, 4, here. We can update the two HLOOKUP formulas so that the row_index_num now references our new cell I9. Let’s fix it by adding dollar signs before the column letter and row number. Cool, we see the same values now. What if we change the 4 to 11? Awesome, the car name and car price have changed and now we see the tenth cheapest car in our list. Now would be a good time to summarize this data and create a dynamic table. Let’s select cell A1 and go to the Insert menu, and click on Pivot Table. We can select the data range to create the PivotTable in the new window. We can change our selection by clicking the cell button at the end. We will select the cells with values in. Next; we want to select where the PivotTable will go - a new worksheet or an area within our current worksheet. Be careful when adding a PivotTable to a worksheet with data - you don't want to replace or overlap with existing data. We will choose New worksheet and click OK. Our new sheet looks like this. A placeholder starts in cell A3 and informs us how to continue building the PivotTable. We also have a new pane on the right that shows the column names from our data range and has the four options to build the PivotTable. Let’s summarize our data by Car Make first. We will drag the field to the Rows section. We now see all the values in the Car Make column in a long list. Let’s add the Car Price to the table to see the average price per car make. Drag the Price to Values. We now have some values, but by default, the aggregation type is sum. To change this, click the down arrow next to the Sum of Price, then select Value Field Settings. Here we can choose which summarization method is applied. Let’s change it to Average for our table. In the tab Show Values As we can even choose a calculation to apply to our column, like % of Grand total. Let’s leave it for now with no calculation. When selecting the PivotTable, we see a new menu at the top called PivotTable Analyze. Here there are different features and options related to our PivotTable. We can change the name, refresh the data behind the PivotTable, change the data source, or even select specific aspects of the PivotTable. We can even sort and format the values. If we select a value in the part of the table we wish to format and click on Field Settings in the PivotTable Analyze menu, a new window appears. We can click on Number Format in the corner and change the values format type to currency. Time to get your hands dirty again.

2. Let's practice!