Google Sheets may not provide as many features as Excel does, but you can still filter cells as if you are using a table in Google Sheets. Here’s how to make a table in Google Sheets in the simplest way.
A table in spreadsheets is basically a range of cells that are made to be easier to filter, edit, and view. You can sort and filter, add more records, and update the pivot table. You can also reference the table from another sheet easily. But, this complete functionality is only on Excel.
In Google Sheets, you can’t directly create a table. Google Sheets doesn’t provide the same features that work exactly like Excel’s table at this time of writing, which is a bummer.
But don’t worry, you can filter the range of your data the same way, customize the coloring to make it look like one, or creating a pivot table in Google Sheets. A pivot table here can still make it easy for you to edit and filter your data, and update records easily and automatically from the source cells.
How to make a table in Google Sheets (pivot table)
A pivot table can be easily made in Google Sheets by accessing the menu. Simply open the Google Sheets, load your spreadsheets, and prepare the range of data you want to create as a pivot table.
Don’t worry, while a pivot table is a little different than a regular spreadsheet table, you can still sort the pivot table you make in Google Sheets. This can be a solution for the absence of the table feature.
Now, simply select the range of the data by clicking and dragging your cursor on the spreadsheet.
The pivot table menu can be accessed by clicking the Data menu. Click Data > Pivot table.
Here, Google Sheets will automatically put the selected range of the data on Data range option. You can choose to insert the pivot table in a new sheet or on the existing sheet. Click Create.
That’s it. Google Sheets now shows you a pivot table editor sidebar on the right side of your screen. It contains some options you can use to add more data, like a custom formula and stuff.
But, you might need to add rows first from the source cells you’ve selected previously. Click Add, then choose which column you want to add rows from.
You can sort the content of your pivot table by selecting the order either ascending or descending and sort by the column. Here, you can also add the total which shows the total number at the end of the pivot table.
Do this for another column as well. Now let’s take a look at the filtering feature. You can filter by the column and select whether you want to show all items or not. Click Add, then click the column name.
If you want to change whether you want to show all items, click Showing all items.
You can checkmark any row data to filter from. Click OK to filter and show data differently.
ALSO READ: How to Count Checkboxes in Google Sheets
A Google Sheets pivot table can also add values to show more additional data. Click Add and select the column name.
You can add values with any function, like SUM. Then show as a percentage of the row, column, or grand total.
Here’s what the pivot table looks like. It makes the data a lot easier to manage.
If you want to change the range of data you use as the source of the pivot table, you can scroll up and change the range by clicking Select data range button.
Click OK after selecting a new range of data.
How to create a table in Google Sheets by adding alternating colors and filters
Filtering is what makes creating a table makes sense. You may not be able to reference the table just like in Excel, you can still filter and sort the rows by the column header.
Besides, you can also customize the style of the data range to make it look like a table. To do that, you can select the column header and choose Bold to make it bold and change the alignment to be center to make it look better.
Now, click Format then choose Alternating colors.
Here, there would be different options to choose to make your data range look better and easier to read. You can also choose your own custom color.
Choose a color for the header and rows, and click Done.
Now, this is the final part. You can make the data range sortable by adding a filter. Select the data range, choose Data, and choose Create a filter.
Great. Now you can sort the data by clicking the column header.
You can sort ascending or descending, sort by color, filter by color, condition, or even values.
In case, if you want to add more records or rows, you can click the last row then choose Insert > Row above, then manually drag and drop the last row moving it to the empty one, so the row you just inserted would be included in the filter.
Or, select the second row from the last row then choose Insert > Row below, and move the row to the last.
Either way, both methods is the only way because you can’t add more records or rows as easy as Excel’s by simply adding a new row from the last one or dragging the bottom right corner of the data range.
Google Sheets provide a pivot table feature and filter feature so you can sort the data simply by clicking the header row, which is the same functionality you find in Excel’s table feature. You can read and manage your spreadsheet a lot easier, including to use alternating color.
ALSO READ: How to Sort by Date in Excel