5

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?

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.