Quick Links
If you don’t have a Microsoft 365 subscription, chances are you’ve been relying on Google Sheets as an alternative to Excel. While both apps offer similar features, Google Sheets missed one key function: the option to create tables. Fortunately, that isn’t the case anymore!
How to Create a Table on Google Sheets
Similar to how you can organize andformat data as tables in Excel spreadsheets, you can use Google Sheet’s built-in table feature to do the same.
Creating a table on Google Sheets is extremely simple. Once you launch Google Sheets and open a new spreadsheet, here’s what you need to do:
After creating your table, you may need to add rows and columns. To add a new column, place your cursor at the end of the table and click theplus (+)icon to insert a column on the right.
To add a new row, position your cursor at the end of the table and click theplus (+)icon to insert a row at the bottom.
To insert a column or row elsewhere, right-click at the desired location and selectInsert 1 table row aboveorInsert 1 table column leftfrom the dropdown menu.
Customizing Tables
Your table is named Table1 by default. You can change this by clicking thedropdown arrowicon next to theTable1text and selectingRename tablefrom the dropdown menu. Then, type in the name of your choice.
Google Sheets is somewhat limited when it comes to customizing table colors. By default, you may only change the color of the table name and column headers.
To do so, click thedropdown arrowicon next to the table’s name, selectCustomize table colors, and choose from the preset colors underTable Colors.
If you want more control, click theplus (+)icon to create a custom color by entering aHEX code, using the color slider, or adjusting the Red, Green, and Blue (RGB) values. Once you’re satisfied with how your table looks, click theOKbutton.
However, there’s a simple workaround to add more customization to your table—alternating row colors. Every alternate row is automatically formatted with alternating colors, but you may adjust these as needed. To do so, highlight any cell in your table, go to theFormattab, and selectAlternating colorsfrom the dropdown menu.
A panel will appear where you can customize the header color and choose two alternating colors for your rows. If you prefer a uniform look, all you need to do is set both Color 1 and Color 2 to be the same.
While this does make it easier to glance at a larger data set, it may not be your cup of tea. Thankfully, you can easily turn it off by clicking thedropdown arrowicon next to your table’s name and selectingTurn off alternating colors.
Setting Data Types
Typically, your data will include various types of information, such as full names, email addresses, contact details, age, gender, and date of birth.
For example, when managing a database for university applications, you might have columns for university name, location, international ranking, and minimum SAT score. You might also include a checkbox to indicate whether you have already submitted your application.
Since these data types differ, Google Sheets allows you to set the appropriate type for each column. Doing so would allow you to perform calculations properly and ensure data consistency. To do so, simply click thedropdown arrowicon next to the column header. Then, hover overEdit column typeand choose from options such asNumber,Text,Date,Dropdown,Checkbox, andSmar tchips.
Filtering and Sorting Data in a Table
No one has the time to go through a never-ending data spreadsheet and manually find specific information. Thankfully,Google Sheets lets youquickly filter and sort informationto find what you need.
Using the same example as above, let’s say I want to sort the table by the SAT scores each university requires from lowest to highest. All I need to do is click thedropdown arrowicon next to theSAT Requirementheader and hover overSort column. Since I want to sort the scores in ascending order, I’d clickSort A to Z.
On the other hand, you can alsofilter your data by colors, conditions and values. For instance, let’s say I’d like to only see the universities where I haven’t submitted my application yet. In this case, I’d filter my data by values. To do so, I’d click thedropdown arrowicon on theApplication Statusheader, and selectFilter column.
Then, I’d click the toggle beforeFilter by valueto expand the section and uncheckTRUE. Finally, I’d clickOK. This will hide rows for universities where the application has been submitted.
I could also apply a filter if I’d like to see the universities that require a SAT score of 1300 or higher. To do so, I’d click thedropdown arrowon theSAT Scoreheader, and selectFilter column. This time I’d click on the toggle next toFilter by condition. I’d click on theNoneplaceholder text, and scroll down and selectGreater thanfrom the available conditions. Finally, I’d enter the value 1300 in the text box below and clickOK.
Referring to Columns in Formulas
When working with formulas in tables, you may need to reference columns for calculations. To do this, simply use the column letter. For example, if I have a table listing all the articles I’ve written in the past week and want to calculate the total word count, I’d specify the starting and ending rows like this:
Alternatively, I could also do the same by specifying the table name and the column header like this:
I’ve always preferred Google Sheets over Excel, and the addition of tables is simply another feature that has helped bridge the gap between the two. This may be one of the manyreasons why you should dump Excel and start using Google Sheetsfor good!
