Create and work with cross tables in Google Spreadsheets

Create and work with cross tables in Google Spreadsheets

Prepare a spreadsheet

Currently, creating a spreadsheet is only possible in the desktop version of Google Spreadsheets using the corresponding function, while the mobile application on the different platforms does not contain the necessary tools. Throughout the process, we will go through each of the available configuration items in detail, but we will not focus on the table and data, which must be prepared beforehand.

Also Read: Open a Table in Google Sheets

  1. Once the previous preparation of the sheet with the data that you want to combine to summarize and separate the totals by certain attributes is finished, in the upper panel of the service, open the section "Insert". and use the function "Create summary table".. By default, there are only two options available here, which take care of selecting the values ​​and the location of the crosstab itself.
  2. Within a category. "Data Range". you must specify the range of cells with the desired data in the format familiar to Google Sheets, as shown in the example here. In this case, if you initially marked the cells and only later used the considered function, the text field will be filled in automatically.

    In addition to specifying the coordinates automatically and by yourself, you can also use the button "Select cell range" in the right part of the block and select the necessary elements in this mode. Also, in some cases, the service offers sampling recommendations based on whether the table is populated with certain values.

  3. Click the button "OKAY". at the bottom of the pop-up window to complete the cell range selection and return to the home page. Then in the category "Insert into". select one of the two options, either "New Leaf". o "existing sheet" at your discretion.

    standard option «New Leaf». will automatically create a new sheet specifically to accommodate the summary table, which can be useful if you didn't plan to post information on the same page. You cannot modify the parameters of the new sheet while it is being created, but you can immediately after it has been moved.

    When selecting "existing sheet" inside the window «New summary table» An additional text box will appear, within which you can specify the desired location manually or via selection mode. This applies not only to the current sheet, but also to any other sheet, using the familiar panel at the bottom of the site to switch between them.

  4. When you are done with the preparations, in the main window press the button "Create".to go to additional parameters. Don't worry about the initial range selection, this can be easily changed in the future via the summary editor.

Work with the cross table

The most difficult part is adjusting the summary sheet so that the data appears correctly in the cells of the selected initial sheet. Note that many problems with the appearance of elements can be avoided in the future if the layout of the document is not changed, apart from the width and height of the cells.

See recommendations

  1. Places the pointer on the starting cell of the summary table regardless of its location and immediately notes the "Cross Table Editor" in the right part of the work window. Here it is important to make sure that the correct range is specified in the coordinate text box, which may change later, for example due to an increase in raw data.
  2. A not always available, but also the fastest method to build a table is to open a dropdown list "Recommendations" and select one of the options shown here. To avoid problems, you can use the icon that appears next to the items "+" to see the result.

Add rows and columns

  1. If the recommendations are missing or inadequate, the table will need to be configured manually. To do this, first press the button "Add". within the category «Strings». o "Columns" based on the result requirements and select one of the recurring values ​​in the popup window.
  2. The main difference between the above options is in the design, that is, when selecting «Strok». the data will be presented as a vertical list, while "Columns". horizontal. This is shown more clearly in our screenshots.
  3. Once the first column or row has been created, the corresponding entry will be displayed in the editor area, the removal of which will allow you to undo the changes made without consequences. Also here you can select the sort order using dropdown lists and display an additional field "Totals"., designed to output the sum of all the data in the table.

add values

  1. To assign a value to each parameter from a different column in the Google Spreadsheet, highlight the adjacent empty cell within the document, pull down the menu "Add" in the category "Values". and select the appropriate option. If everything has been done correctly, the information will be displayed exactly as shown above.
  2. You can change how the totals for each cell are calculated using the dropdown list "Add and go"where there are many features available to choose from. We will not describe each of the options due to their large number, especially since it is not difficult to find out about them in the official help of the service.
  3. Using column parameters "Show as" you can select the style of the displayed data, either normal numbers or percentages of certain values. It all depends on your needs regarding the result.

data filtering

  1. One remaining, but quite important subsection Filters. allows you to hide certain summary table data that may have gotten there due to the formatting of the original document. Here you have to press the button "Add" button. and select the same parameter that was originally specified through the pop-up window, while there may be difficulties with the values.
  2. Click the left mouse button on the list "State".in standard mode, uncheck the data that should not be in the table and press «OK». This will update all rows and columns in the document instantly.
  3. As an extension you can use the category list "Filter by condition"if the amount of unwanted data does not allow you to quickly find and disable the necessary parameters. A normal text search is also available.
  4. The resulting table will update automatically if previously copied information changes, as well as access to settings. In addition, it will also be possible to consult the data through the mobile app, although without the possibility of modification.

We are glad that we were able to help you with your problem.

Describe what has not worked for you.
Our specialists will try to respond as quickly as possible.

Has this article helped you?

Share this article on social Media:


Creative Stop All About Technology
A How To.