Create a pivot table
1. Create a pivot table
Suppose Thomas Omar wants to know how the sales of On The Rise's products are doing in the South American market and what the forecast was for the last half of the year. He could use several sum formulas and filter the data, or he could set up a pivot table to provide him with the information quickly. To create a new pivot table, select a cell in a table of data. Open the Data menu and click "Pivot table." Choose the data range for the pivot table to reference by either typing the address or by clicking and dragging. Google Sheets will automatically populate the data range using AI. Choose the destination for the pivot table either in a new sheet or in an existing sheet. In our case, let's choose the range A1:H253 to capture the actual sales and forecast sales for the cities in South America. If "Existing sheet" is chosen, enter the sheet name and cell range address for it to appear in. Again, clicking and dragging a range is supported. Select M2 to add the pivot table on the existing sheet. The pivot table editor panel will appear on the right side of the sheet. Google Sheets AI will list suggested ways to summarize the data. Click the "preview pivot table" button to see what each suggestion will look like, and then click on the description to apply it to create the pivot table. In our case, Thomas Omar wants to see actual product sales by city. So we will need to choose the rows and columns necessary to complete the pivot table. Select "Add" for rows and choose "Product." The three products are added to the M column and information about those products will be added to the rows. You can sort this information in either ascending or descending order. Select "Add" for columns and choose "City." All of the cities in the South American region fill in across the columns in row three. We want to know the actual sales for each of these products in each of the cities. Select "Add" for values and choose "Sales - Actual," then summarize by sum. The sum of the products and grand total appears. You can also do this for the forecasted product sales by following the same steps, choosing "Sales - Forecast" instead of "Sales - Actual." Thomas Omar wants to know if a particular product is driving the sales in each city. To do that, he would like to see the percent of sales each product accounts for in the city. Let's create another pivot table. Select "Pivot table" from the Data menu. Choose the destination for the pivot table in the existing sheet in cell M18. Select "Add" for rows and choose "City." The cities are added to the M column, and the sales for each location will be added to the rows. The cities are sorted in alphabetical order by default. Select "Add" for columns and choose "Product." The three products fill in across the columns in row 19. We want to know the actual sales for each of these products in each of the cities. Select "Add" for values and choose "Sales - Actual," then summarize by sum. The sum of the products and grand total appears. However, we want to know the percent of the product sales by city. Click the down arrow under Show as and choose percentage of row.2. Let's practice!
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.