A field list is shown on the right with all the columns from the “Sales” table.Ĭlick and Drag the “Category” field into the Rows area of the Pivot Table, and the “Total” field into the Values area. The new worksheet is inserted and the PivotTable placed on it. And the default option is to insert the Pivot Table on a new worksheet. The “Sales” table is picked up as the data source to be used. With the category column now in the “Sales” table, we can create the Pivot Table to show the total sales for each product category.Ĭlick in the “Sales” table, then click Insert > PivotTable. It is very powerful and makes generating reports quick and simple. What is a Pivot Table?Ī Pivot Table is a reporting tool in Excel that summarises data and performs an aggregation on values.įor example, to show total sales by month or number of orders for each product. In addition to VLOOKUP, the INDEX and MATCH formula is also very useful to look up data from other Excel tables. VLOOKUP is an extremely useful function in Excel that can be used in many other clever ways such as to compare lists or test values. The following formula is added to the “Sales” table in column ID]],Products,2,FALSE) Are you looking in ranges? In our example we aren’t, because we are looking for a specific category ID.

Range Lookup is the type of lookup you are performing. This will be the column containing the category, which is the second column. This will be the “Products” table.Ĭol Index Num is the column number of the table containing the information to return. Table Array is the table we need to look up this information.

Lookup Value is the value you are looking for. They are the lookup value, table array, col index num and range lookup. The VLOOKUP function has four arguments (information it needs). We can use the VLOOKUP function to bring the category information into the “sales” table. That information is stored in the “Products” table. However the “Sales” table does not have the details about the product categories.

We would like to create a PivotTable showing the total sales by the different product categories. It is commonly used in a worksheet to look up and pull data from another Excel table or worksheet.įor example, we have an Excel table named “Sales” which contains details of product sales for all months of the year.Īnd another table named “Products” with product details. VLOOKUP is a lookup and reference function in Excel.

Pivot tables for dummies pdf