One of the best ways to master Google Sheets — the powerful spreadsheet app that you use in a web browser — is to learn how to build and use pivot tables.
To describe it very generally, a pivot table takes data from a group of cells in your spreadsheet and presents it in more comprehensible and interesting ways.
Pivot tables are mainly used to compile number data in cells, but they can also manipulate text data. And they’re most helpful when they’re used to extract cell data from a spreadsheet that has lots of columns or rows.
Why use pivot tables?
The best way to explain what a pivot table does, and why you would want to create one, is to show examples. Below is a spreadsheet of “raw” cell data that hasn’t been organized. Note the two columns that list the product names and the amounts of each product that were sold. Sales for the same product names were entered multiple times, so it’s impossible to tell at a glance the total sales for each product type.
Howard Wen / IDG
The raw data in this spreadsheet is hard to decipher. (Click image to enlarge it.)
Here is a pivot table that was created from this spreadsheet. It’s been designed to summarize the total amounts of each type of product sold:
Howard Wen / IDG
This pivot table adds up the amounts of each product sold, showing overall totals.
The table compiles all the data from the PRODUCT NAME column and all the data from the AMOUNT SOLD column, placing them neatly together for a focused view of how many of each product sold — data that’s very hard to see in the original spreadsheet.
In our second pivot table example, below, we see the average of total sales of products that have been credited to each salesperson. Again, the pivot table pulls out information that’s impossible to quickly glean from the original spreadsheet.
Howard Wen / IDG
This pivot table computes the average sale value for each salesperson.
Preparing your spreadsheet for pivot tables
In…
2023-02-21 01:00:03
Original from www.computerworld.com
Google Sheets is an incredibly powerful spreadsheet-building tool that allows users to create complex visualisations and crunch enormous amounts of data in an efficient and intuitive way. However, many users don’t know about one of its most useful features that can help them cut down their workload significantly; pivot tables. Pivot tables in Google Sheets allow you to automate formulas and categorise data from multiple columns into a single table, meaning you can present your data in a much more organised and efficient manner.
Google Sheets pivot tables contain four main components; value, field, row, and column. The value component is kept in the centre of the table and is used to perform mathematical calculations on the data. The field component allows you to categorise the data according to fields such as week, month or year. The row component lets you present the data in a tabular fashion, while the column component enables you to group the data into separate categories. With these components in play, pivot tables allow you to effectively summarise complex trends into a single view.
Google sheets also offers users an array of features for further customising the data in their pivot tables. For instance, users can manually adjust the height and width of the column and row headers respectively. You can also use filters to narrow down the data you want to focus on, making it easier to locate the information you need. Furthermore, it’s possible to sort the relative values in ascending or descending order, allowing you to analyse the relative importance of certain figures.
In conclusion, pivot tables are a powerful Google Sheets feature that can drastically improve your workflow when dealing with complex data or analytics. You can quickly and easily categorise, sort and summarise complex information, creating an organised and well-presented result. With plenty of customisable features and a user-friendly layout, you’ll understand why pivot tables are an essential part of the Google Sheets toolkit.