RETURN_LAST_CELL
Return the last non-empty cell in a range, ignoring trailing blanks.
=RETURN_LAST_CELL(range)Retourne : any
Aperçu
RETURN_LAST_CELL scans through a range and returns the value of the last non-empty cell it finds. This is particularly useful for columns or rows that grow over time, such as log entries, transaction records, daily metrics, or any dataset where new entries are appended at the bottom.
Paramètres
| Paramètre | Type | Requis | Description |
|---|---|---|---|
range | Range | Oui | The range to search for the last non-empty cell (e.g., A1:A100 or A:A). |
Exemples
Get latest daily revenue
In a daily revenue tracker, get the most recent day's revenue without knowing which row it is in.
=RETURN_LAST_CELL(B2:B100)Entrée
| $1,200 |
| $1,450 |
| $1,380 |
Sortie
$1,380Find most recent log entry
Get the latest timestamped entry from a log column that grows over time.
=RETURN_LAST_CELL(A:A)Entrée
| 2024-01-01 Login |
| 2024-01-02 Update |
| 2024-01-03 Export |
Sortie
2024-01-03 ExportLatest stock price in a column with gaps
Find the most recent stock price even when the column has blank rows for weekends and holidays.
=RETURN_LAST_CELL(C1:C50)Entrée
| 142.50 |
| 143.20 |
| 145.80 |
Sortie
145.80Current inventory status
Get the most recently recorded inventory count from a daily stock check column.
=RETURN_LAST_CELL(D2:D200)Entrée
| 500 |
| 480 |
| 465 |
| 450 |
Sortie
450Cas d'Usage
Dashboard KPI display
Show the most recent KPI value on a dashboard without manually updating cell references as new data rows are added.
Transaction monitoring
Display the latest transaction amount or balance from an ever-growing ledger column.
Sensor data monitoring
Retrieve the most recent temperature, pressure, or quality reading from a continuously updated sensor log.
Project status tracking
Show the latest status update from a project log where new updates are appended as rows.
Weight or health tracking
Display the most recent weight measurement or health metric from a personal tracking spreadsheet.
Conseils Pro
Perfect for dashboard cells that should always show the latest data point from a growing column.
Combine with other functions: =TEXT(RETURN_LAST_CELL(B:B), "$#,##0") to format the latest value.
Use RETURN_CELL_INVERSED if you need the second-to-last or any specific position from the end.
Works great for monitoring sheets where new data is appended via form submissions or API integrations.
The function flattens the entire range and identifies all non-empty cells, then returns the very last one. Empty cells anywhere in the range are automatically skipped, so gaps in your data do not affect the result. This means "last non-empty" is truly the last cell with a value, regardless of any blank rows between entries.
A common use case is building dashboards that always show the most recent data point. Instead of manually updating cell references as new data arrives, RETURN_LAST_CELL dynamically finds the latest entry. For example, in a daily sales tracker where new rows are added each day, =RETURN_LAST_CELL(B:B) always returns today's (or the most recent) sales figure.
The function works with any data type including numbers, text, dates, and booleans. For accessing cells by position from the end (e.g., second-to-last), use RETURN_CELL_INVERSED. For selecting a random value from a range, see SELECT_RANDOM_CELL.
Erreurs Courantes
Returns a cell that is not visually the last oneCause : Cells below the visible data contain spaces or invisible characters that are not truly empty.
Solution : Select the cells below your data and press Delete to clear them completely. Spaces and invisible characters count as non-empty.
Returns empty string despite data in the rangeCause : The range reference is incorrect or points to an empty area of the sheet.
Solution : Verify the range reference. If using a named range, check that it points to the correct cells.
Questions Fréquentes
Yes, the function scans all cells and returns the very last non-empty one, regardless of gaps in the data. Blank rows in the middle do not affect the result.
Yes, you can pass an entire column reference. The function will find the last non-empty cell in that column. However, for performance, it is better to limit the range to your actual data area when possible.
It returns an empty string ("") if no non-empty cells are found in the range.
Yes, it works with any data type. The returned value preserves its original type, so dates remain dates and numbers remain numbers.
Traditional approaches like INDEX(range, MATCH(2, 1/(range<>""), 1)) are hard to read and error-prone. RETURN_LAST_CELL provides the same result with a simple, self-explanatory formula.
Yes, you can pass a row range like A1:Z1 and it will return the last non-empty cell in that row.
Fonctions Associées
Commencez à utiliser RETURN_LAST_CELL aujourd'hui
Installez Unlimited Sheets pour obtenir RETURN_LAST_CELL et 41 autres fonctions puissantes dans Google Sheets.
