In this post, I will show you how to create tables in Google Sheets to manage your data easily. In Microsoft Excel, the table feature allows you to manage and analyze related data easier. It has so many advantages over managing your data without a table. However, at the time of writing this post, Google Sheets does not have an inbuilt feature to create such tables. But still, you can achieve the same kind of results with Google Sheets by combining several functions, options, and formatting features.
You may also interest in; 10 Benefits of Converting Data into Excel Tables
For easy navigation, below is the list of features we are going to add to the table that we are going to create in Google Sheets.
- Assign names to tables in Google Sheets – Named range
- Add alternating colors
- Add functions to auto calculate data in new rows/ carry forward calculations
- Sorting and filtering
- Freeze the header rows of the table
- Add conditional formatting to the tables in Google sheet
- Remove all empty rows and columns
Assign names to tables in Google Sheets
Using the table name you can refer to the table when you need the table data somewhere else for your analysis.
When you create a table in Excel, it automatically assigns a name to that table. By default, your first table in the workbook named as Table1 and the second as Table2, and so on. In Google Sheet also you can give your data range a name using the “Named ranges” option.
TIP: Select a cell somewhere in the middle of the dataset and click Ctrl+A to select the entire data set adjacent to the selected cell.
To assign a name to the table (The easiest way),
- Select the entire data set.
- Go to the Name box in the top left corner of the Google Sheet cell range, or use the shortcut Ctrl+J
- Then type the table name in the Name Box and hit enter.
Add alternating colors
Tables are easy to read and attractive with alternate colors. In Microsoft Excel, the table is formatted automatically with alternating colors when you create it.
You need to apply alternating colors separately when you create tables in Google Sheets. To apply alternating colors
- Select the entire table. (If you have named the table as explained above, you can use it to select the table easily. To do that, click the small down arrow in the “Name box” and select the table name.)
- Go to Format and select Alternating colors.
- You can customize the colors using the “Alternating colors” dialog box that appeared on the right side of the Google Sheet.
You can also use “Explore” option (explain below) to add alternating colors.
Like in Excel the alternative colors are expanded to the adjacent rows or columns when you add data to them. You can change the range to which the alternating colors are applied by going to Format > Alternating colors.
However, in Excel when you hit the tab while the active cell is the bottom right cell of the table, the active cell changes to the first column of the next row. However, this does not happen in Google Sheet. It activates the next cell in the same row (outside the table) instead. You can use the Home key and Arrow keys to achieve this in Google Sheets.
Add functions to auto calculate data in new rows/ carry forward calculations
In Excel, when you add a formula to a cell in an empty column it automatically copied to all the other cells in the column. And the function copied to every new row added to the table automatically.
You can achieve this in Google Sheets using the
Let’s say you want to calculate 15% of the values in column “F” and put the calculated value in column “H”. Adding the following formula in the first row of column G will do the job. This formula copies down to every new row you added below the cell that contains the function.
!!! Here we have assumed that column A does not contain empty cells. If you have empty cells in column A, the function will not continue beyond that cell.
Sorting and Filtering
In Google Sheets, you can have more filter options than Microsoft Excel. Basically, there are two types of filters in Google Sheets namely Filter and Filter Views. You can also use
SORT() functions to filter and sort your data in Google Sheets.
This will filter data for everyone who has access to your spreadsheet. And those who have edit access can change the filters.
To create a filter,
- Select the range of cells related to your data table
- Click Data > Create a filter (or click the funnel icon in the menu bar)
- Then click the filter icon in the top row of your selected data range to see the filter options.
You can use Filter views to filter data without changing what collaborators see. So, when you use filter views, others will not see any changes to your data set.
You can create and save multiple filter views to present your data from different perspectives.
To create Filter views
- Select the range of data related to your data table
- Go to Data > Filter views > Create new filter view (you will see a dark color border appear around the Google sheet)
- Rename your filter view at the Name box in the top left corner of the working area of the Google Sheet.
- Sort and filter your data using the filter icon at the top row of each column of your data range.
- Your filter is automatically saved and click the cross mark in the top right corner of the working area of the Google Sheet to close the filter view.
- To access your saved filters, go to Data > Filter views and select the filter view you want.
The collaborators can open these filter views without interrupting others view.
Freeze the header row of the table
When you scroll down the table, the column headers cannot be seen. In such cases, you can freeze the header row to prevent it from disappearing when scrolling down.
You can also freeze the row headers.
To freeze header rows easily,
- Hover over the mouse bottom border of the column heading (the English letters)
- When the hand🤚 icon appeared, left-click and drag the mouse down. Then you will see a gray color horizontal line is moving down.
- Release the mouse in such a way that the gray color line is placed on the bottom border of the row to be used as the header row.
- Now you have frozen the rows above this line. Scroll down the mouse to see it.
With Explore option in Google Sheets, you can quickly gather insights into your data set. Even you can ask questions to solve your problems quickly. It analyzes your data using machine learning, and you can easily visualize them. It is also capable of generating pivot tables, charts, and other types of analysis.
To open Explore sidebar,
- Select data table. You can use the table name created above to easily select the entire table.
- Click the Explore button in the bottom right corner of the Google Sheet.
You can also select different data range insight the table and see the changes in Explore sidebar.
Ask Questions about data
On explore sidebar, you can ask questions about your data. The Google Sheets provides answers using charts or formulas.
To ask questions about your data,
- Select the range of data you are interested in. In this case, select the table using the mouse or table name you created here.
- Then, go to the Answers section of the Explore sidebar.
- In this section, you will see that Google has already created some questions about your data set. Click on a suggested question or type your question in the “Ask about this data” text box.
- It will provide answers to your questions using Formulas and charts.
It is also providing you predefined pivot tables based on the date range you have selected. You can drag and drop these tables to the current sheet or insert them into a new sheet.
In the analysis section, you will get a list of charts generated based on the data you have selected. You can see the charts are automatically updated when you change the data range you have selected.
Add conditional formatting to the tables in Google sheet
Conditional formatting is a data visualization technique that formats the cells (text color, text size, cell background color, etc…) if they meet certain conditions.
With this, you can format the cells that you want to highlight. So, you can add more visual effects to your data table.
There are several inbuilt conditions that you can use to apply conditional formatting. If your condition is not there, you can build custom formulas.
You can format your cells using a single color or color scale.
To add conditional formatting to table,
- Select the range of cells you want to add conditional formatting,
- Go to Format > Conditional formatting, the Conditional format rule sidebar will appear on the right side.
- At the top of the sidebar, you can select whether to use Single color or Color scale.
- Then you can customize it using, format rules, and formatting styles.
You can learn more about using the custom formulas from the following post. There, I have used checkboxes with Conditional formatting.
Remove all empty rows and columns
Unlike in Microsoft Excel, you can remove all other empty rows and columns from the sheet. This will make easy to visualize the data range.
To delete rows/ columns,
- Select the range of columns/ rows
- To select the entire column, click on the column header (the English letters)
- To select the entire row, click on the row number
- You can use Ctrl+Shift+Arrow Keys to easily select entire rows or columns. Use Ctrl+Shift+Right Arrow Key select all the column on the Right Side of the selected column. Use Ctrl+Shift+Down Arrow to select all the rows below the selected row.
- Then, right-click on the selected area and click Delete columns X-X or Delete rows X-X depending on your selection.
Many of you may have used the Table feature in Microsoft Excel. There are a lot of advantages to using it in Microsoft Excel. As of writing this post, there is no direct option to create tables in Google Sheets similar to Microsoft Excel. However, you can build similar kinds of functionalities to tables in Google Sheets using various other options, functions, features in Google Sheets.
In this post, we have discussed many options that can be used to create a similar kind of table in Google Sheets as Microsoft Excel. And also, we discussed some unique features in Google Sheets that can improve your productivity.