Although Google Sheets is structured in tabular format, sometimes you may require inserting tables to analyze and view large sets of data more efficiently. Adding tables to your spreadsheet can help you sort and filter your data and you can keep the non-table data intact so your spreadsheet is more presentable. 

In this post, we’ll help you understand all the ways you can insert a table in a spreadsheet on Google Sheets. 

How to insert a Table chart on Google Sheets

The easiest way to add tables inside your spreadsheet on Google Sheets is by using Table charts. 

Step 1: Insert a table chart

  1. To add a Table chart, open a spreadsheet you’re working on inside Google Sheets.
  2. When the spreadsheet opens, select the range of cells whose data you want to use to create a table. We recommend selecting all the cells you want to work on but even if you don’t, you can add more cells to your table at a later time. 
  3. Once you’ve selected a required cell range, click on the Insert tab from the toolbar at the top and then select Chart from the overflow menu. 
  4. Google Sheets will now add its default chart type to your spreadsheet. You’ll also see a Chart editor sidebar on the right-hand side. 
  5. Inside the Chart editor sidebar, you can select the table you want to add to the spreadsheet. To do that, scroll down the Setup tab inside this sidebar, select the dropdown box below Chart type, and click on Table Chart. 
  6. The default chart that was added to your spreadsheet will now be replaced with a Table chart. 

You can then continue your modifications to this Table chart using the Chart Editor sidebar on your screen. 

Related: How to use Percentage Change Formula in Google Sheets

Step 2: Set up the table with desired values

  1. If you accidentally closed the Chart Editor, you can recall it by selecting anything on the Table chart and then clicking on the 3-dots icon at the top right corner of the table. In the menu that opens, select Edit chart. 
  2. When the Chart editor appears, you can modify your table from the Setup tab.
  3. Inside Setup, you can edit the following elements of your table according to your preference. 
    Data Range: If you hadn’t selected the desired cell range when creating a table chart, this section lets you customize your selection more precisely. Here, you can type the cell range you want to choose in “A1:D10” format or you can click on the
    Data range button (marked by a grid icon) to select the cell range from your spreadsheet manually. 

    Series    : Selecting the series makes sure all data values from a specific row or column are plotted inside your table chart. You can select different series, aggregate data inside them, or add more series to the table from this section. 

    Switch rows/columns    : If you check this box, the rows and columns in the spreadsheet will interchange positions. So, you’ll have all the rows listed as columns and vice versa. The data inside the table will be relocated accordingly. 

    Use column A as headers    : You can check this box to assign items in column A as headers of the table chart. 

    Use row 1 as labels    : When you check this box, all cells inside row 1 will be listed as labels in the table. 

Related: How to Alternate Colors in Google Sheets

Step 3: Customize the appearance of the table

After you’re done setting up your table chart, you can make the table more presentable by customizing its appearance inside your spreadsheet.

  1. Click on the Customize box inside the Chart editor sidebar on the right-hand side. Inside Customize, select Table to expand its options. 

    Alternate row colors    : To make your table more presentable, you can make its rows appear in alternating colors by checking this box.

    Show row numbers    : You can make it easier to locate and edit values in the table by checking the Show row numbers box inside Chart Editor. Your spreadsheet will now show row numbers on the left-hand side of the table you created. 

    Sort column    : You can assign a column of your table to be sorted by default. To do that, click on the None under the Sort column to open a dropdown menu.
  2. Now, select the column column you want to sort.
  3. From there, you can check the Ascending box to sort items in low-to-high order or uncheck it to sort cells in the opposite order. 

    Pagination    : With Pagination, you can make tables with large sets of data easier to view. To use this, check the Pagination box and then enter your desired page size (anywhere between 5 and 100).

    After you’ve enabled this option, you’ll see page numbers at the bottom of the table click on the page you want to view by clicking on the corresponding page numbers. You can also click on the left/right arrows here to move between adjacent pages in the table.  
  4. When you’re done making changes to the table, close the Chart editor sidebar by clicking on the x icon at the top right corner.
  5. Your table should now be ready and it should look something like this.
  6. You can move the table to any location within your spreadsheet or resize it however you want.

Related: How to Make a Histogram in Google Sheets

How to format a spreadsheet in tabular form [7 methods]

If you don’t fancy using table charts or pivot tables but wish to convert elements in your spreadsheet into a tabular format, then you can format the sheet and customize its appearance. This way, we will be tweaking your spreadsheet into a great-looking table without adding or inserting one from the toolbar. 

Method 1: Apply borders

If there’s one thing that differentiates a table from a spreadsheet, that’s borders. You can choose from various types of borders, some outlining the entire table while others may highlight the edges or the insides of the table. 

  1. To apply borders, select the cell range where you wish to have a table.
  2. When selected, click on the Borders icon from the Sheets toolbar at the top. 
  3. Here, you can try out different options to see what looks best for the table.
  4. Once you’ve selected the border type, you can choose a border color by clicking on the pencil icon inside the Borders box. 
  5. You can also change how the border appears – thin, thick, or patterned lines. 
  6. With just borders applied, your table should transform into something like this. 

Method 2: Realign data 

When you create a spreadsheet and add data to it, Sheets will automatically align cells with values to the right and cells with text to the left. If you want to change the appearance of the table, you will have to realign the data inside the cells to make them pop. 

  1. The first thing to do when realigning a table is to align the headers centrally. To do that, select all the header cells in the sheet and then click on the Align button (marked by four horizontal lines) from the toolbar above.

    If you cannot see this button, click on the 3-dots icon and that should reveal the Align button. 
  2. When Align options appear, click on the Center align icon. This should relocate the text in the headers to the center of the cells. 
  3. You can align other cells in the table to your preferred position, i.e., move cells with text and numbers in your way. 
  4. When properly aligned, the table should look like this. 

Method 3: Use colored or bold headers

If you have a large sheet with multiple headers, then you can highlight the headers to make them pop inside the spreadsheet. This will not just make your table look neat but will also help others understand the contents of the table more easily. You can highlight headers by applying text bolding or by coloring their background.   

  1. To start highlighting headers, select the cells in the header row inside your spreadsheet.
  2. Once selected, click on the Bold icon from the toolbar above or use the CTRL (or CMD) + B keyboard shortcut to apply bold to the selected cells.
  3. Optionally, you can apply different colors to the header background by clicking on the Fill color button (marked by a paint bucket icon). 
  4. When an overflow menu opens, click on the color of your choice from the preset options or create a color from inside the Custom section. 
  5. Sheets will now apply the selected color to the background of the selected header cells. 
    Note: When you choose a dark header background, make sure you change the text color inside the headers to white to make them visible. 
  6. Here’s what your table should look like after you use bold and colored headers. 

Method 4: Apply colors on alternate rows

Another feature that can improve your table’s appearance inside a spreadsheet is alternating rows.

  1. To apply alternating colors to your table’s rows, select the cell range for the table inside your spreadsheet. 
  2. When the cells are selected, click on the Format tab from the top toolbar and select Alternating colors. 
  3. Google Sheets will now apply alternating colors to your table with the default color set at grey. The header’s colors will be slightly darker than other rows. If you had previously applied a color to the header’s background, that will be replaced with the colors you pick here. 
  4. You will also see the Alternating colors sidebar on the right-hand side of the screen. In this sidebar, you can customize the following options.

    Apply to range    : Here, you can select the range you want to apply alternating colors in. 

    Head and Footer    : Checking these boxes will add a darker shade to the header or footer of the table, depending on which of the two you enable.  

    Default styles    : You get 12 color options to apply to your table. 

    Custom styles    : If you aren’t satisfied with the default styles that Sheets offers, you can create your style by clicking the + icon inside this section.  

    From there, you can apply different colors for the Header, Color 1, and Color 2 boxes. 
  5. Once you’re done making all the changes, click on Done at the bottom. 
  6. The table will now look something like this. 

Method 5: Sort columns based on different data sets

In addition to beautifying the table, you can also make it easier to analyze and understand the data inside it by sorting the columns neatly. You can sort a single column or multiple columns at once to view the table with a better understanding. 

  1. To sort your table, select the cell range for a particular table. Once selected, click on the Data tab from the toolbar at the top and then go to Sort range > Advanced range sorting options.
  2. You’ll now see a Sort range prompt on your screen. Inside this box, ensure that the Data has header row box is checked.
  3. In the “Sort by” section, select a column you want to sort primarily by selecting it from the drop-down menu. 
  4. When you’ve selected a column to sort, choose A to Z or Z to A depending on the order in which you want to sort the column. This order isn’t just alphabetical but is also applicable for columns with values as choosing A to Z for a column with values will reposition the rows according to ascending values. 
  5. To sort more columns in addition to the first one, you can click on Add another sort column below the “Sort by” section. 
  6. You’ll now see the “then by” section where you can customize more columns to sort by. Here, select the column you want to sort by after the first round of sorting and pick an order in which you want to be sorted.
  7. You can add as many columns as you want to sort from here and when you’re done customizing these options, click on Sort. 
  8. The result after sorting the table will look like this. 

Method 6: Highlight cells with higher/lower values

If you have a bunch of values you’re dealing with in a table, you can make it easier for you and others to analyze the content by highlighting the low and high values in it. To highlight these cells, you will have to use Conditional Formatting on Google Sheets. 

  1. To use Conditional Formatting, select cells in a single column where you want to apply highlights inside the spreadsheet. 
  2. When you’ve selected the necessary cells, click on the Format tab from the top toolbar and select Conditional formatting. 
  3. You should see the Conditional format rules sidebar on the right-hand side. Before you apply the highlights, you need to choose a condition when the values in the table get highlighted.
  4. Click on the dropdown under Format cells if inside this sidebar and choose your condition from the options available depending on the type of value you’re dealing with. If you have dates listed in the selected column, you can set a condition that highlights the exact date, before, or after the set date. For numbers, you can choose to highlight cells that have the same, lower, or higher value than the preset value you entered. 
  5. When you choose a condition, enter the value you want to assign for the condition to pass. You can also assign a formula for applying more complex conditions to highlight the cells.  
  6. After entering your format condition, customize how the highlight appears inside the table from the Formatting style section. From you, you can choose to apply different text formatting to the cells that get highlighted and choose a fill color that gets applied. For best results, we suggest only changing the fill color as modifying the other elements may not bode well for other elements of the table. 
  7. If you wish to add more conditions, click on Add another rule at the bottom. If not, you can click on Done to save changes to the sheet. 
  8. The result with the highlighted cells will look like this. 

Method 7: Use filters to show important content

If you think that your table has a lot of data to analyze, then sorting it in ascending or descending order won’t make it look neat. In such instances, you can use Filters to hide less significant data from the spreadsheet so that only things you search for are visible on the screen. You can create filters for all columns in your sheet or selected columns by going to Data > Create a filter from the toolbar at the top. 

That’s all you need to know about inserting tables on Google Sheets. 

RELATED