Excel style table in Google Sheets

Question

In Excel, I can create a table (n.b., not a Pivot Table) through Insert > Table with headers where I can sort and filter the table among other functions.

Is there a similar functionality in Google Sheets? I can't find it in the Insert dropdown menu.

Answer 1

There's no single feature in Google Sheets which exactly replicates Excel's Tables feature, but you can replicate some of it:

• You can set a filter on the table range using the filter icon, and this will add filtering/sorting functionality to the header row.
• The Format/Alternating colours option adds formatting to the table range which looks very similar to an Excel Table.
• Google Sheets doesn't support Structured References (the best feature of Excel Tables and one I'd really like to see in Sheets one day), but you can partially replicate some of this functionality with standard Named Ranges.

Answer 2

To insert a simple sortable table view of for a range of data, select the range and choose Insert > Chart > Table chart. To sort the table by a column, double-click the column name in the table chart. Note that the chart is just a view of the data, which means that you cannot edit the values in the chart.

Also see filter views. They let you and other users decide which rows to show at a time independently of each other, all without disturbing each other. The data remains editable. Filter views also let you sort data in the view, without changing the storage order of rows. Filter views are different from regular filters.

You can save filter views and give them descriptive names. For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

Answer 3

To implement something like Excel's Structured References, try this: The following Apps Script will add a function to created Named Ranges in the format SheetName_ColumnHeader for the contiguous range (basically, a table) surrounding the cell you have selected. If the Named_Range already exists, it will overwrite it. You'd have to run it again every time the table length increases, but if your data is relatively static it does the job nicely.

function createStructuredReferences() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSheet();
  const re = new RegExp( '[^A-Za-z0-9]+', 'g' );
  const sheet_name = sheet.getName().replace(re,'');
  console.log( sheet_name );
  
  const rng = sheet.getActiveRange().getDataRegion();
  const dr_height = rng.getNumRows();
  const dr_width = rng.getNumColumns();
  const dr_row1 = rng.getRow();
  const dr_col1 = rng.getColumn(); 

  const headers = sheet.getRange( dr_row1, dr_col1, 1, dr_width )
  const header_names = rng.getValues()[0].map( (h) => h.replace(re,'') );
  console.log( header_names );

  const namedRanges = sheet.getNamedRanges();

  for ( let i = 0; i < dr_width; i++ ) {
    let name = sheet_name + "_" + header_names[i];
    let range = sheet.getRange( dr_row1 + 1, dr_col1 + i, dr_height - 1, 1 )
    
    for ( var j = 0; j < namedRanges.length; j++ ) {
      let nr = namedRanges[j];
      if ( nr.getName() == name ) {
        ss.removeNamedRange( name );
      }
    }
    ss.setNamedRange( name, range );
  }
};

You'll also want to add a Named Function called AT which simulates Excel's table[@Column] behaviour.

=INDEX( range, ROW() - ROW( range ) + 1 )

Then you just called it like so: =AT([named_range]).

Here's a file you can use as a template with both of these features built in: Structured References Template