To find the last nonblank value in Apple Numbers on Mac, use a helper column to test each cell, then use XLOOKUP to search those results from bottom to top. This method handles gaps and keeps zero as a valid value.

The task here is to return the bottommost occupied cell in a table column. “Last” means row position, so arrange your table in the order you want to use. In a project schedule, the bottommost entry represents the latest stage only when the rows follow that sequence.

Set up a small example with gaps

Apple’s current online Mac guide identifies itself as covering Numbers 15.4. To check your installed version, choose Numbers → About Numbers. The formulas below follow Apple’s current Functions Help. Numbers User Guide for Mac

Use one table with five columns and at least eight rows. Put the headings below in row 1. Enter the sample values in columns A and B, leaving the indicated B cells genuinely empty. The words “leave empty” explain the setup; do not type them into your spreadsheet.

Row A: Stage B: Object count C: Empty? D: Result label E: Result
1 Stage Object count Empty? Result label Result
2 Layout 12 Last count
3 Assembly leave empty
4 Review 18
5 Adjustment 0
6 Alignment leave empty
7 Finish 7
8 Extension leave empty

This is an illustrative object-count sequence. The expected answer is 7, from B7. The empty B8 should be skipped, as should the gaps within the column. The 0 in B5 is an entry and must remain eligible.

Keep the result in E2, outside both the source column and the helper column. This separates the answer from the cells used to calculate it.

Add a blank test for every source row

Click C2 and enter this complete formula:

=ISBLANK(B2)

Typing = opens the Formula Editor; press Return to commit the formula. Apple documents this entry procedure in its guide to calculating values in table cells. Calculate values using data in table cells in Numbers on Mac

ISBLANK returns TRUE for a completely empty cell and FALSE otherwise. For this helper column, FALSE therefore means the source cell contains something. A space or nonprinting character counts as content even when the cell looks blank. ISBLANK

Select C2, move the pointer over its selection border until the yellow autofill handle appears, then drag down through C8. Autofill copies formulas into adjacent cells. Use the empty helper column because autofill overwrites existing content in its destination. Autofill cells in Numbers on Mac

Inspect C3 and C8 after filling: they should reference B3 and B8 respectively. For the supplied sample, C2:C8 should contain the following results:

Helper cell Expected result Reason
C2 FALSE B2 contains 12
C3 TRUE B3 is empty
C4 FALSE B4 contains 18
C5 FALSE B5 contains 0
C6 TRUE B6 is empty
C7 FALSE B7 contains 7
C8 TRUE B8 is empty

Return the bottommost occupied value

Click E2 and enter:

=XLOOKUP(FALSE,C2:C8,B2:B8,"No entries",0,-1)

This searches C2:C8 for FALSE and returns the corresponding value from B2:B8. The fifth argument, 0, requests an exact match. The final argument, -1, searches from last to first. "No entries" supplies the message when no match exists. These argument meanings come from Apple’s XLOOKUP documentation.

For the sample, the bottommost FALSE is C7, so E2 returns B7: 7. Notice that this answer is smaller than 18. The task concerns position, so the largest number would answer a different question.

Use these three temporary edits to check your setup, then restore the sample if desired:

  1. Change B7 to 9: E2 should become 9.
  2. Clear B7: E2 should become 0, because B5 is now the final occupied source cell.
  3. Clear every cell in B2:B8: E2 should display No entries.

These checks distinguish three common requirements: responding to a changed entry, preserving zero, and handling an entirely empty range. They also make mistakes in the helper formulas easier to spot.

Decide whether “blank” means empty or empty-looking

The main method uses a strict definition: the source cell must be completely empty to be skipped. A cell containing a formula that returns "" still contains a formula, so it does not meet that definition. Consequently, a visually empty result can be the selected entry. Apple defines ISBLANK in terms of a completely empty cell. ISBLANK

For a column whose formulas return text, you may instead want to skip zero-length text results. In that case, replace the helper formula in C2 with the following and fill it down again:

=LEN(B2)=0

Keep the E2 lookup formula unchanged. This revised helper asks whether the source value has zero text length. Apple documents LEN as counting graphemes and accepting any value; its count includes spaces, numbers and special characters. An empty string has length zero, whereas a space has length one. LEN

Choose the rule before using the result elsewhere. The original helper answers “Which cell is last occupied?” The text-length helper answers “Which value is last to have nonzero text length?” Neither rule treats a space as empty.

Keep the ranges aligned as the table grows

The example deliberately searches rows 2–8. When you extend the source through row 20, fill the helper through C20 and update both lookup ranges together:

=XLOOKUP(FALSE,C2:C20,B2:B20,"No entries",0,-1)

Each helper row must correspond to the same source row. Also inspect the references after structural changes: Apple specifically notes that adding a row within a referenced range in a categorized table requires changing the reference for the new row to be included. Calculate values using data in table cells in Numbers on Mac

If you move the result into another table, qualify references with the source table name. Numbers uses :: between a table name and a cell reference and inserts the name automatically when you select cells in another table while building a formula. Selecting the actual ranges avoids guessing their addresses. Types of arguments and values

Avoid array, chart and highlighting shortcuts that change the task

A whole-column blank test is not the helper column

Do not replace the filled helper with ISBLANK(B2:B8) and assume it produces seven individual tests. Apple explicitly states that when ISBLANK references more than one cell, it returns FALSE even if all the cells are blank. That documented function boundary is why this method evaluates each source cell separately. ISBLANK

Numbers does have array functions; Apple defines an array as a sequence of values and explains that array functions can supply values to other functions. However, that general capability does not override the argument behavior documented for an individual function. Types of arguments and values

A chart is a display of selected data

Use the result cell to inspect the answer directly. If you also chart the object counts, select the chart and click Edit Data References to inspect or change its source cells. Hidden or filtered data is excluded from charts by default, and large charts may show a representative sample of points. A visible endpoint therefore needs checking against the underlying table. Modify chart data in Numbers on Mac

Highlighting a matching value can select several cells

For a visual cue, Apple supports conditional highlighting rules that compare a cell’s value with another cell reference. Select the relevant cells, open Format → Cell → Conditional Highlighting, and click Add a Rule. Use cell conditions in Numbers on Mac

A rule comparing counts with the result would identify matching values. If 7 appeared in several source rows, those matches would not uniquely identify B7. Keep that distinction clear when your task is to locate a particular row.

Once the helper reflects your definition of blank, verify the sample result and extend both ranges together. You then have an inspectable way to retrieve the final occupied value while preserving meaningful entries such as zero.

Sources