Usually reference the cell ranges are entered directly in formulas, e.g. =SUM(A1:a10). Another approach is to use as a reference a range name. The article will consider what are the advantages of using a name.

Usually formulas directly entered into cells, but you can pre-assigning the formula to use in cell its name. What are the benefits of a named formula read in this article.

If you need to add the values in a column, for proper operation of Your formulas, You will probably need dynamic ranges that automatically increase or decrease depending on the amount of your data.

When filling the cell with data, it is often necessary to restrict the option of entering a specific list of values. For example, there is a cell where the user must enter the name of the Department, pointing out where he works. Logical, first create a list of departments in an organization and allow the user only to select values from this list. This approach will help to speed up the entry process and reduce the number of misprints.

If pull-down (drop-down) list based on data Validation enter the new value in the cell then using the drop-down list based on the of a form control combo Box can only select the desired value.

When filling cells with data, it is necessary to restrict the option of entering a specific list of values – this can be done using Dropdown. If at the same time must ensure the launch of only distinct values, you need a Dynamic dropdown list: using it the user will not be able to choose twice the same value.

Create a dropdown list whose contents depend on the values of another cell.

In the article an improved version of Linked list.

Create list whose contents depend on the values of another cell. Create it on the basis of form control List.

For modeling complex hierarchical data to create a Multilevel linked list.

If Your Dropdown has become very long and it was not easy to work, the creation of a nested linked list may be a solution.

the Essence of the query is to select from the original table the rows that meet certain criteria (like the use of filter).&In contrast to filter selected rows will be placed in a separate table.

For the analysis of large and complex tables are usually used Pivot tables. Using formulas you can also implement a grouping and analysis of the available data. Create simple reports by using formulas.

Sorting the list can be done via the menu Data/ group Sort and filter/ Sorting. In case, if the original list is constantly introducing new values, to maintain the list in sorted condition, the user needs each time to manually sort the list via the menu, which is not always convenient. It is much easier to have dynamically controlsize list. The list consists of numeric values.

To build array formulas sometimes use a numerical sequence, for example {1:2:3:4:5:6:7}, entered directly into the formula. This sequence can be formed by manually entering the literal array, or using functions such as ROW(). Also using formulas you can dynamically change the length of the numeric sequence.

an Array of values (or array constant or array of constants) is a set of numbers or text values that can be used in array formulas. Array constants must be entered in a specific format, for example, {1:2:3:4:5} or for text values {"North":"SOUTH","East":"West"}.

Create a numerical sequence of different types: 1, 2, 3, ... 1, 3, 5, 7, ... I, II, III, IV, .... 100, 200, 300,...00-01, 00-02, ... 1, 1, 1, 2, 2, 2,... etc.

will Create a sequence of text values of different types: first, second, third, ... товар1, товар2, товар3, ... A, B, C, ...1st period, 2nd period, ...1), 2), ... will Also create a sequence of an arbitrary type.