Both Google Sheets and Excel offer customization in their interface. It helps to streamline workflows by easily accessing the most frequently used features. Google Sheets’ App Script can create custom menus. In Excel, you can rearrange the features and commands, and you can add custom menus to add those features that are not available. This post will show how you can implement custom menus in Google Sheets using Apps Script while comparing Excel’s Ribbon customization.

Implementing Custom Menus in Google Sheets Using Apps Script

In Google Sheets, you can create custom menus by using Apps Script. App Script is a JavaScript-based scripting language. It helps to automate tasks, enhance Sheets with custom functions, and build add-ons to use as custom menus.

Google Sheets doesn’t offer a Table option like Excel. Let’s create a custom menu for an Excel-like table using App Script.

Steps:

• Open Google Sheets, from Extensions >> select Apps Script.
• In the Apps Script editor, you can define a custom menu by writing a script. 

function onOpen() {

  var ui = SpreadsheetApp.getUi(); // Get the user interface of the spreadsheet
  ui.createMenu('Tables') // Create a custom menu named 'Tables'
      .addItem('Insert Table', 'formatAsTable') // Add 'Insert Table' option
      .addToUi(); // Add the menu to the user interface
}

function formatAsTable() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange(); // Get the currently selected range
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();

  // Apply bold formatting and background color to the first row (header row)
  var headerRange = range.offset(0, 0, 1, numCols);
  headerRange.setFontWeight('bold');
  headerRange.setBackground('#ADD8E6'); // Set header background color (e.g., green)
  headerRange.setFontColor('black'); // Set header font color to white

  // Apply alternating row colors (striped rows)

  for (var i = 1; i < numRows; i++) {
    var row = range.offset(i, 0, 1, numCols);
    if (i % 2 === 0) {
      row.setBackground('#f2f2f2'); // Light gray for even rows
    } else {
      row.setBackground('#ffffff'); // White for odd rows
    }
  }

  // Apply a border to the entire selected range
  range.setBorder(true, true, true, true, true, true);

  // Apply filter to the range
  range.createFilter(); // Creates filter for the range
}

Explanation:

• To give Excel-like table effect;
  • The first row will be treated as a header, with bold text, a light blue background (#ADD8E6), and a white font color.
  • A light gray background (#f2f2f2) to even-numbered rows and white (#ffffff) to odd-numbered rows, starting from the second row (below the header).
  • Filter option will appear in the header for the selected range, allowing users to filter and sort data like in Excel.

• Save the script and return to your Google Sheet, you will see the custom menu Tables in the menu bar. 
• Select the data range >> from Tables >> select Insert Table.

Now, you will get an Excel-like table in Google Sheets.

Ribbon Customization in Excel

It’s easy to customize the ribbon in Excel. You can add, delete, or customize the toolbar at the top of Excel, without needing to write code. Excel’s customization options are accessible directly from the Excel ribbon.

Steps:

• Right-click on the ribbon >> select Customize the Ribbon from the dropdown menu.

• In the Excel Options window;
  • You can add a new tab or customize an existing one.  You also can add new groups of commands to these tabs.
  • From the left side, you will get a list of all available commands, including built-in Excel functions or macros if you have one. Simply add them to your new or existing ribbon group.

• Now select  New Tab >> from Commands list >> choose Email >> click on Add.
• Click on OK.

A customized New Tab will appear in the ribbon.

Using Macros for Custom Buttons in Excel: If you need advanced customizations, you can use VBA macros by recording or writing VBA code, you can automate tasks in Excel just like you can with Apps Script in Google Sheets.

Conclusion

Google Sheets and Excel offer powerful ways to customize the user interface. Adding a custom menu in Google Sheets requires prior knowledge of JavaScript and Apps Script. It provides robust automation and integration with Google services. Excel’s ribbon customization is more straightforward to use, though it also supports advanced customization through VBA. The choice between the two will depend on the platform you’re using and your specific requirements. For users comfortable with coding can choose App Script that provides the flexibility of JavaScript for automation and integration. Otherwise, users can use Excel’s ribbon customization which is easier to manage without coding.