Google-sheets – How to get the number of cells used in a Google Sheets spreadsheet

google sheets

I would like to know how close I am to the limit of 2 million cells with a spreadsheet.

How can I find the information about how man cells I am using in a Google Sheets spreadsheet?

Best Answer

Formula

=ROWS(A:A)*COLUMNS(1:1)

If you have more that one sheet, repeat the above pattern for each one as arguments of SUM or use the + operator

=SUM(ROWS(Sheet1!A:A)*COLUMNS(Sheet1!1:1),
     ROWS(Sheet2!A:A)*COLUMNS(Sheet2!1:1)
)

Explanation

When used with open-ended references like A:A and 1:1, ROWS and COLUMNS returns the number of actual rows and columns.

An alternative is to use Google Apps Script. Below is a custom function: 

/**
 * Count the number of cells of the active spreadsheet
 *
 * @param {A1} input Used to force recalculation.
 * @customfuncion
 */
function cellsCount(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var cells = 0;
  sheets.forEach(function(sheet){
    cells = cells + sheet.getMaxRows() * sheet.getMaxColumns();
  });
  return cells;
}

The argument is optional. It's recommended to include a cell that changes frequently but it should not contain a non-deterministic function like NOW(), RAND(), etc.

Related Topic