Share via

The difference between 'array' and 'range' in Excel?

Anonymous
2023-04-30T16:46:09+00:00

Doing self help with learning Excel. I've watched tutorials and such but have not seen an indication of the following. How can I find if the sheets or workbooks are a 'table' or 'a range'? All files saved as .xlsm or .xlsx workbooks. thanks and enjoy the week-end.

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.3K Reputation points MVP Volunteer Moderator
    2023-04-30T19:08:45+00:00

    A range is a set of cells on a worksheet, for example B2:D5 is the rectangular set of cells with B2 as upper left corner and D5 as bottom right corner.

    A table is a special type of range, created by selecting Table on the Insert tab of the ribbon. The first row of a table contains the headers (field names), and the rows below contain the data records.

    An array is a matrix of values. The values of a range are an example of an array, but Excel can also work with arrays that do not correspond to a range of cells. For example, {1,2,3} is an array of 3 numbers, but not a range.

    Some Excel functions can handle both ranges and arrays, for example SUM:

    =SUM(A1:A3) and =SUM({1,2,3}) are both valid formulas.

    Other functions can only handle ranges.

    For example =SUMIF(A1:A10, ">5") will sum the values of the cells in A1:A10 that are larger than 5.

    But on the other hand, =SUMIF({3,6,9}, ">5") will return an error because the array {3,6,9} is not a range.

    1 person found this answer helpful.
    0 comments