Cell in Excel - basic concepts. Control work Active cell in an excel spreadsheet

Argument is a value that is used to calculate the values ​​of functions.

A block is a range of cells bordered only by empty cells or column and row headers.

The clipboard is a part of memory designed to temporarily store a copied or cut information object until it is replaced by the next object.

The return value is the value that is the result of the function evaluation.

Selection is the operation of selecting an object on the monitor screen to perform any action with it.

Selection - the procedure for selecting a cell or cells for subsequent work with them. The selected range cells are displayed with a dark background, the active range cell retains a light background.

Alignment is an operation that allows you to place text at the discretion of the user: left or right, centered or justified.

Alignment - The placement of material within a cell, including line direction, character orientation, horizontal and vertical justification, and line break mode.

Typeface - character design style. For example: Times New Roman or Verdana.

Gradient is a command to gradually change the color of text from character to character.

Border - a rectangular frame that represents a selected cell or range

A range is a compact group of table cells that has a rectangular shape.

The key is the field by which the list is sorted.

Copying - an operation in which the contents of the source area are duplicated in the destination area

The autofill marker is a square in the lower right corner of the selected range border.

The parent row is the calculated row of the table, the formulas in which are compiled “manually” (not copied from anywhere).

The optimal solution is the set of input parameters of the model, at which its output parameter is maximum or minimum.

A filtered list is a list in which rows that do not meet the selection (filtering) condition are hidden (but not deleted!)

Transfer is an operation in which the contents of the active cell or selected cells (source area) are moved to another cell or range (destination area).

Editing is the operation of changing the values ​​of previously entered data.

List sorting - sorting the list entries in ascending or descending order by the value of one or more fields.

Status bar - a place to display information about current state workspace and program, and current prompts.

The formula bar is where formulas are displayed and edited.

The current row is the row in which the active cell is located.

The current column is the column in which the active cell is located.

Filtering is a way to search for data using filters. Filtering differs from sorting in that the filtering process does not reorder the records, but excludes from the set those records that do not meet the specified criteria.

Background - a combination of color and pattern that is used to fill a cell in order to visually enhance it.

A data form is a dialog box in which list fields correspond to input fields, and there are buttons for performing operations with lists.

Formatting is the operation of changing the presentation of data on the screen or in a printed document without changing their values.

A formula is an entry in the content area of ​​a cell that begins with an equal sign and describes the actions that result in the calculation of a value that is placed in the display area of ​​the same cell.

Formula - an expression that serves to perform a mathematical and (or) logical operation. The formula includes: Equal sign, cell addresses, numeric constants, arithmetic operations and functions.

A function is a part of a formula that indicates the need to perform standard calculations. A function consists of a function name followed by a list of arguments enclosed in parentheses.

Template - a file that serves as a template for quick creation and filling the same file type.

Cell - elementary structural element a table used to store data, having an address and two areas: content and display.

A cell is the intersection of a column and a row in a spreadsheet. Each cell has its own address (or name), consisting of a column name and a row number, for example: A1, D3, F5, etc.

Error messages in Excel:

#### – the cell width does not allow displaying the number in the given format.

#DIV/0! - perform division by zero.

#NAME? - Invalid cell name set. It is possible that the Cyrillic alphabet was mistakenly used for typing the names, and not the Latin alphabet.

#NUMBER! - incorrect application of mathematical formulas. For example, extracting the square root of a negative number.

#VALUE! - Invalid argument type used. For example: attracting a cell containing text to perform an arithmetic operation.

#EMPTY! - incorrect indication of the intersection of two areas that do not have common cells.

d) a system program that manages resources personal computer when working with tables.

2. The fundamental difference between a spreadsheet and a regular spreadsheet is:

a) the possibility of processing data structured in the form of a table;

b) the possibility of automatic recalculation of data given by formulas when the original data changes;

c) the possibility of visual representation of the links between the processed data;

d) the ability to process data presented in lines of various types.

3. Spreadsheet rows:

a) are arbitrarily named by the user;

b) are indicated by the letters of the Russian alphabet A ... Z;

d) are numbered.

4. Cell address in Excel consists of:

a) filename

b) a given set of characters;

c) the name of the column and the number of the row at the intersection of which the cell is located;

d) the row number and column name at the intersection of which the cell is located.

5. If you make a cell active in Excel and press the Delete key, then:

a) the contents of the cell will be deleted;

b) the cell format will be cleared;

c) the cell will be deleted;

d) the name of the cell will be deleted.

6. The information in the table is presented in the form:

a) files b) records; c) text, numbers, formulas.

7. Quick recalculation of data in a spreadsheet occurs due to...

a) distribution of information by cells;

b) the presence of formulas linking data;

c) fast processor operation.

8. Active cell in Excel is:

a) cell with address A1; b) a cell marked with a frame; c) the cell in which the data is entered.

9. A range of spreadsheet cells is called ...

a) the set of all filled cells of the table;

c) a set of cells forming a rectangular area;

d) a set of cells that form an area of ​​arbitrary shape.

10.Specify invalid formula for cell F1

a) =A1+B1*D1; b) =A1+B1/F1; c) =C1.

11. Specify an invalid formula to write to cell D1

a) \u003d 2A 1 + B 2; b) =A 1+B 2+C 3; c) =A 1-C 3; d) all formulas are admissible.

12. In spreadsheet cannot be deleted

a) a line b) column; c) cell name; d) the contents of the cell.

13. A group of cells is selected in the spreadsheetC 3: F 10. How many cells are in this group?

a) 21; b) 24; c) 28; d) 32.

14. Expression , written in accordance with the rules adopted in mathematics, in a spreadsheet has the form:

a) 3*(А1+В1)/(5*(2*В1–3*А2));

b) 3(А1+В1)/5(2В1–3А2);

c) 3* (А1+В1)/ 5* (2* В1–3* А2);

d)3(А1+В1)/(5(2В1–3А2)).

15. When moving or copying in a spreadsheet, absolute references:

a) do not change

b) are transformed regardless of the new position of the formula;

c) are transformed depending on the new position of the formula;

d) are transformed depending on the length of the formula.

16 . Spreadsheet cell H5 contains the formula =$B$5*V5. What formula will be obtained from it when copied to cell H7:

a)=$B$7*V7; b)=$B$5*V5; c)=$B$5*V7; d)=B$7*V7.

17. In the spreadsheet, cell A1 contains the number 10, cell B1 contains the formula =A1/2, and C1 contains the formula =SUM(A1:B1). What is the value of C1:

a) 10; b) 15; in 2; d) 150.

18. The chart is:

a) form graphic representation numerical values, which makes it easier to interpret numerical data;

b) schedule;

c) a completed table;

19. The bar chart is:

Answers:

Text word processor has several built-in programs that implement various objects in the created document. They are accessed through Insert/Object.... Let's take a look at the two most popular ones: the chart builder and the formula editor.

Diagramming

An application is used to build diagrams.

Select the cells in the table whose data is used to build the chart. The content of the top row and left column of the selected area is used for tick labels on the coordinate axes.

Through the menu Insert/Object.../Create in the "Object type" list, select "Microsoft Graph", after which a table with data and a chart will be displayed.

Set labels in the table: labels from the first row of the appeared table are used to mark the horizontal axis, and from the first column - for the legend. The legend is an explanatory picture to the right of the diagram.

Edit the chart. To do this, click right click mouse over the edited element of the diagram - a menu for editing it will appear.

Exit "Microsoft Graph" by clicking in the main window outside of the chart.

Move the chart to the desired location in the document and adjust its size.

Example

Let's build a diagram illustrating the dynamics of revenue (see table above). To do this, select the interval A2:F5 in the table and load "Microsoft Graph". Note that this range includes a month row and a city column. They are used for labels on the horizontal axis and in the legend.

If you want to edit the chart, right-click inside the chart and select the "Chart Options" menu item. The "Chart Options" window will open, through which you can make most of the corrections. For example, to set labels on the horizontal axis, you must open the Axes tab and select the X Axis (Categories) check box and the Automatic check box. If you want to change the chart type, select the "Chart Type" menu item.

To set the vertical orientation of the labels under the X axis, right-click on any of the labels, select the "Format Axis" menu and on the "Alignment" tab, set the vertical orientation.

If, with the "Microsoft Graph" object active, access the menu Data/Rows Form Columns, then the data from the columns of the table will be plotted along the X axis.

All actions for editing the diagram can be performed through the menu bar, which replaces the main menu during the work of "Microsoft Graph".

The final step in editing a diagram is resizing it and placing it in the right place on the page.

Working with the formula editor

The formula editor "Microsoft Equation" is a program that is installed during the installation of the Word editor at the request of the user. The editor has a large set of mathematical symbols and allows you to display fairly complex formulas. Unlike the TEX editor, in which the formula is first encoded and then reproduced special program, "Microsoft Equation" allows you to see the formula as you write it. After writing the formula, its dimensions can be changed like a regular drawing.

To write a formula, place the cursor in the right place and access the menu Insert/Object/Microsoft Equation 3.0. In this case, a frame for entering the formula and a formula editor panel containing two rows of buttons should appear. The top row of buttons defines character palette, lower - template palette. You can complete the formula input by clicking outside the input field.

The general procedure for typing formulas is to select the required element in the formula editor panel and refine it in the menu that appears. Entering ordinary characters is performed from the keyboard in the designated fields. When moving from one field to another and when entering new fields, it is necessary to monitor the position and size of the cursor. For example, the cursor for a common fraction is larger than for its numerator or denominator. In this way, a place is shown for entering the next character.

Inserting spaces in formulas cannot be done by simply pressing the appropriate key. There are several types of spaces that are available in the symbol palette. If you have to insert spaces often, then it is convenient to use the key combinations shown in the table.

Aligning a formula may be required, for example, after changing its dimensions, despite the fact that in most cases the formula editor itself aligns the formula relative to the line in which it is written. To align the entire formula or part of it, you need to call the formula for editing by double-clicking the mouse, select the part to be aligned and press the combination of the Ctrl key and one of the cursor keys as many times as necessary, depending on the alignment direction. Each click moves the selected part by 1 pixel.

test questions

1. How are built-in programs that implement various objects in the created document accessed?
2. What application is used to draw charts?
3. How to choose a charting app?
4. How to build a chart?
5. What is a legend in a chart?
6. How to set labels for horizontal axis marking and legend?
7. How to edit a chart?
8. What are the features of the formula editor?
9. How to write a formula in a document?
10. What general order a set of formulas?
11. How to insert a space in a formula?
12. How to perform formula alignment?

This is a chapter from a book: Michael Girvin. Ctrl+Shift+Enter. Mastering array formulas in Excel.

This note is for those who are really interested in complex array formulas. If you just need to extract a list of unique values ​​once, it's much easier to use Advanced Filter or PivotTable. The main advantages of using formulas are automatic updating when changing / adding source data or selection criteria. Before reading, it is advisable to refresh the memory of the ideas contained in the previous materials:

• (chapter 11);
• (chapter 13);
• (chapter 15);
• (chapter 17).

Rice. 19.1. Extracting Unique Records with an Option Advanced filter

Download note in format or, examples in format

Extracting unique list from one column with option Advanced filter

On fig. 19.1 shows the data set (range A1:C9). Your goal is to get a list of unique race tracks. Since you need to keep the original data, you cannot use the option Remove Duplicates(menu DATA –> Work with data –> Remove Duplicates). But you can use Advanced filter. To open a dialog box Advanced filter, go through the menu DATA –> Sort and filter –> Additionally, or press and hold the Alt key, and then press S, L (for Excel 2007 or later).

In the opened dialog box Advanced filter(Fig. 19.1) set the option copy the result to another location, check the checkbox Only unique records, specify the area from which to extract unique values($B$1:$B$9), and the first cell where the extracted data will be placed ($E$1). On fig. 19.2 shows the resulting unique list (range E1:E6). If you don't include the field name in original range dialog box Advanced filter(instead of $B$2:$B$9 in Figure 19.1), Excel treats the first row of the range as a field name, and you risk getting a duplicate. On fig. Figure 19.3 shows one of many possible uses for a unique list.

Retrieving unique list based on criteria with option Advanced filter

In the last example, you have retrieved a unique list from one column. An advanced filter can also extract a unique set of records (that is, the entire rows of the original table) by applying a criterion. On fig. Figures 19.4 and 19.5 show a situation in which you want to extract unique records from the range A1:D10 for which the company name is equal to ABC. Later in this chapter, you will see how to do this job using a formula. However, if you don't need the process to be automatic, you can use Advanced filter, which is certainly simpler than the formula.

Rice. 19.4. You need unique entries for the ABC company; To enlarge an image, right-click on it and select Open image in new tab

Rice. 19.5. Usage advanced filter to extract unique records based on criteria is much easier than the formula method. However, retrieved records will not be automatically updated if the criteria or source data changes.

Extract unique list from one column with pivot table

If you are already using PivotTables, then you know that every time you place a field in the Strings or columns(fig. 19.6), you will automatically get a unique list. On fig. Figure 19.6 shows how you can quickly create a unique list of race tracks and then count the number of visits to each one. While a PivotTable is useful for extracting a unique list from a single column, it is unlikely to be useful for extracting unique records based on criteria.

Rice. 19.6. You can use the pivot table when you need a unique list and subsequent calculation based on it

Extracting a Unique List from a Single Column Using Formulas and a Helper Column

Using a helper column makes it easier to retrieve unique data than using array formulas (Figure 19-7). This example uses the methods you learned in (using the COUNTIF function) and (using a helper column). If you now change the original data in the range B2:B9, the formulas will automatically reflect these changes in the area D15:D21.

Array formula: extracting unique list from one column using SMALL function

Since the array formulas used in this section are quite complex to understand, their creation is divided into stages: the first is a snippet that counts unique values ​​(Chapter 17); the second is criteria-based data extraction (Chapter 15). On fig. Figure 19.8 shows the formula for calculating unique values ​​(since this is an array formula, it is entered by pressing Ctrl+Shift+Enter). Pay attention to the following aspects of this formula:

1. The FREQUENCY function returns an array of numbers (Fig. 19.9): for the first occurrence of a race track, the number of its occurrences in the original data is returned; for each subsequent occurrence of the race track, zero is returned (see). For example, Sumner appears in the first and fifth positions of the array. In the first position, the FREQUENCY function returns 2 - the total number of Summer in the range B2:B9, in the fifth position - 0.
2. The FREQUENCY function is placed in the argument log_expression IF function, so the IF function returns TRUE for any non-null value, and FALSE for zero.
3. Argument value_if_true IF function contains 1, so the SUM function counts the number of such units.

Rice. 19.8. The FREQUENCY function is placed in the argument log_expression IF functions

Rice. 19.9. (1) the FREQUENCY function returns an array of numbers; (2) the IF function returns 1 for numbers other than zero, and FALSE for zeros

Now let's create a formula for extracting a unique list. On fig. 19.10 shows an array of relative positions placed in an argument array SMALL functions.

In the previous example (Fig. 19. 9) in the argument value_if_true the IF function was placed one, so the IF function returned ones and FALSE. Here (Fig. 19.10) the argument value_if_true contains: STRING($B$2:$B$9)-ROW($B$2)+1. Therefore, the IF function (within the SMALL function) returns the relative position number in the range with a unique race track, or FALSE for duplicates (Figure 19.11).

Rice. 19.11. The IF function returns the relative position number in a range with a unique race track, or FALSE for duplicates

On fig. 19.12 show the results of the formula. On fig. 19.13 it can be seen that as soon as the initial data changed, the formulas immediately reflected these changes. But what if you add new entries? Next, you'll see how to create dynamic range formulas.

Rice. 19.13. If the original data changes, the formula is updated immediately. Filter and Advanced filter can't update automatically without writing VBA code

Array formula: extract unique list from one column using dynamic range

Let's expand on the last example with what you learned about formulas that use specific names based on dynamic ranges (). On fig. 19.14 is the formula for determining the name track. This formula assumes that you will never enter an entry after line 51.

Rice. 19.14. Name Definition track formula based

Once you define a name, you can use it in any formula. On fig. Figure 19.15 shows how to use the name to count the number of unique values ​​(compare with Figure 19.8). And in fig. Figure 19.16 shows a formula that extracts the unique values ​​themselves from a list of race tracks. Note that instead of a snippet range»» (as it was in Figures 19.8 and 19.10), the ISTEXT function is used (any text will return TRUE). When using ISTEXT, if you enter a number (as in cell B11), or any other non-text, the formula will ignore that value. On fig. Figure 19.17 shows that the formula automatically extracts any new trace names, ignoring numbers.

Rice. 19.16. Extract unique trace name based on dynamic range

Creating a Unique Values ​​Formula for a Dropdown List

Based on the example just considered, we define the second name - TrackList also based on dynamic range, but now referring to a list of unique traces (range E5:E14, Fig. 19.18). Since the range E5:E14 contains only text and empty values ​​(test strings of zero length - ""), in the argument lookup_value MATCH functions can use wildcard characters *? (meaning at least one character). And in the argument match_type MATCH should use the value -1, which will find the last element of text in a column that contains at least one character. As shown in fig. 19.18, then you can use a specific name in the field Source window Validation of input values(For more information on creating a dropdown list, see). The dropdown list can expand and contract as new data is added or removed in column B.

If wildcards should be treated like regular characters

As you learned in, sometimes wildcards must be treated as characters. On fig. Figure 19-18 shows how you can modify the formulas for such cases. You are appending a tilde before the argument range lookup_value MATCH function and append an empty string behind the range in the argument lookup_array.

Using a Helper Column or Array Formula to Retrieve Unique Records Based on Criteria

At the beginning of the note, it was shown that for extracting unique records based on criteria, it would be great Advanced filter. However, if you need instant updates, you can use a helper column (Figure 19-20) or array formulas (Figure 19-21).

Dynamic formulas to extract customer names and sales volume

The formulas are shown in fig. 19.22. For example, if you add new record TTTrucks on line 17 , the SUMIF formula in cell F15 will automatically add the new value. If you add a new customer in column B, it will immediately be reflected in column E, and the SUMIF formula in column F will show the new total.

Rice. 19.22. Using a specific name and two array formulas to extract unique customers and sales volume

Note that the SUMIF function in the argument sum_range contains one cell - $C$10. Here is what the SUIF formula help says about this topic: argument sum_range may not be the same size as the argument range. When determining the actual cells to be summed, the upper left cell of the argument is used as the starting cell sum_range, and then the cells of the part of the range corresponding to the size of the argument are summed range. Formulas entered in cells E15 and F15 are copied along the columns.

Sorting Numeric Values

The formulas for sorting numbers are quite simple, but for sorting mixed data, they are insanely complex. Therefore, if you do not need an instant update, then it is better to do without formulas using the option Sorting. On fig. 19.23 shows two sorting formulas.

On fig. Figure 19-24 shows how a helper column can be used to sort numbers. Since the RANK function does not sort the same numbers (giving them the same rank), a COUNTIF function has been added to distinguish between them. Note that the COUNTIF function has an extended range that starts one line up. This is to ensure that the first occurrence of any number does not contribute. The second occurrence of the number will increase the rank by one. This sequential numbering establishes the order in which the INDEX and MATCH functions retrieve records in the range A8:B12.

If you can afford to create an auxiliary column in the data extraction area (range A10:A14 in Fig. 19.25), it is convenient to apply the sorting of numbers described above based on the SMALL function, and already on the basis of it, extract the names using the array function.

Rice. 19.25. If you can't use a helper column, apply a sort based on the SMALL function (in cell A11) and an array formula (in cell B11)

Often in business and sports, you want to extract the top N values ​​and the names associated with those values. Start your solution with a COUNTIF formula (cell A11 in Figure 19-26) that determines the number of records to display. Note that the argument criterion in the COUNTIF function in cell A11 - more or equal value in cell D8. This allows you to display all border values ​​(in our example, although we want to display Top 3, there are four suitable values).

Rice. 19.26. Extracting the top three total scores and their corresponding names. When N changes in cell D8, area A15:B21 will be updated

Sorting text values

If the use of an auxiliary column is acceptable, the task is not so difficult (Fig. 19.27). The comparison operators process text characters based on the ASCII numeric codes assigned to the characters. In cell C3, the first COUNTIF function returns zero, and the second adds one. In C4: 2+1, C5: 0+2, C6: 3+1.

Sort mixed data

The formula that allows you to extract unique values ​​from mixed data and then sort them is very large (Fig. 19.28). It was created using ideas that were encountered earlier in this book. Let's start learning the formula by looking at how it works standard function sorting in Excel.

Excel sorts the results in : numbers first, then text (including zero-length strings), FALSE, TRUE, error values ​​in order of occurrence, empty cells. All sorting occurs according to ASCII codes. There are 255 ASCII codes, each of which corresponds to a number from 1 to 255:

For example, the number 5 corresponds to ASCII code 53, and the character S corresponds to ASCII code 83. If you sort two values ​​- 5 and S - from smallest to largest, then 5 will be higher than S, because 53 is less than 83.

The data set in the range A2:A5 (Fig. 29) is converted into the range E2:E5 in accordance with the sorting rules. To better understand the principles of sorting, consider values ​​in the range C2:C5. For example, if you ask the question "How much higher than me in rank?" to the ID in cell A2 (54678), the answer will be zero, because in the sorted list, ID 54678 will be the top one. SD-987-56 will have three IDs above it. You need a formula to get values ​​in the range C2:C5.

First, select the range E1:H1 and in the formula bar type =TRANSP(A2:A5), enter the formula by pressing Ctrl+Shift+Enter (Fig. 19.30). Next, select the range E2:H5 in the formula bar, type =A2:A5>E1:H1 and enter the formula by pressing Ctrl+Shift+Enter (Fig. 19.31). On fig. Figure 19.32 shows the result, which is a rectangular array of TRUE and FALSE values ​​that correspond to each of the cells in the resulting array, as an answer to the question "Is the row heading larger than the column heading?"

Rice. 19.30. Select range E1:H1 and enter array formulas

Rice. 19.31. In the range E2:H5, enter the array formula =A2:A5>E1:H1

Rice. 19.32. Each cell in the E2:H5 range contains the answer to the question "Is the row heading larger than the column heading?"

For example, in cell E3 the question is: SD-987-56 > 54678. Since 54678 is less than SD-987-56, the answer is TRUE. Note that the E3:H3 range includes three TRUE values ​​and one FALSE value. Looking back at fig. 19.29, you can see that it is the number three that is in cell C3.

As shown in Figures 19.33 and 19.34, you can convert TRUE and FALSE values ​​to 1s and 0s by adding a double negative to the array formula. Since the original array (E2:H5) is 4×4 and you want the result to be a 4×1 array, use the MULTIP function (see Figure 19.35i). The MULTIP function is an array function, so enter it by pressing Ctrl+Shift+Enter (Fig. 19.36). Now, instead of using the range E2:H5, add the appropriate elements inside the formula (Fig. 19.37).

Rice. 19.36. By selecting the range C2:C5 and entering the array function MMULT you get a column of numbers that say how many IDs in the sorted list are higher than the selected one

Rice. 19.37. Instead of using the auxiliary range E2:H5, the corresponding elements are added inside the formula

On fig. Figure 19.38 shows how you can replace an array of constants with ROW($A$2:$A$5)^0.

Rice. 19.39. To deal with potential empty cells, all occurrences of A2:A5 should be supplemented with a check IF(A2:A5",A2:A5); the STRING function does not require such an addition, because the function works with the address of the cell, not with its contents

Since the final formula will be used elsewhere, we need to make all ranges absolute (Figure 19.40). On fig. 19.41 shows the resulting values.

Rice. 19.40. Ranges A2:A5 turned into absolute

Since this element will be used twice in the future, you can save it under a specific name. As shown in the dialog box (Figure 19.42), the formula is given the name MSB - How Many Values ​​Is Greater.

1. Argument array The INDEX function refers to the original range A2:A5.
2. The first MATCH function will tell the INDEX function the relative position of the element in the A2:A5 array.
3. While the argument lookup_value MATCH function is left blank.
4. Defined Name (SSB) in Argument lookup_array will allow you to first access an element that has a value of 0, then 2, and finally 3.
5. Zero in argumentation match_type specifies an exact match, which will eliminate the use of duplicates.

Rice. 19.43. You start a formula to extract and sort data in cell A11. Argument lookup_value leave the MATCH function blank for now

Before you create an argument lookup_value MATCH functions, remember what, in fact, you need. There are three unique IDs to sort, so you need three numbers in the argument lookup_value as the formula is copied down. These numbers will allow you to find the relative position in the A2:A5 array, which is what you want to provide to the INDEX function:

1. In cell A11, the MATCH function will return 0, which corresponds to a relative position of 1 within the specific MSB name.
2. When the formula is copied down into cell A12, the MATCH function should return the number 2 and the relative position = 4 within the MSB.
3. In cell A13, the MATCH function should return 3 and the relative position = 2 within the MSB.

The picture emerges when you think about what the argument lookup_value when copying the formula down, the query should match: "Give the minimum value within a specific SSB name that has not yet been used." As shown in fig. 19.44 formula element MIN(IF(END(MATCH($A$2:$A$5,A$10:A10,0)),MSB)) returns the minimum value when the formula is copied down, exactly answering the query. The reason this works is because the snippet UND(MATCH($A$2:$A$5,A$10:A10,0)) compares two lists (see). Notice the expanding range A$10:A10 in the argument lookup_array. In cell A11, the combination of UND and MATCH helps extract all the unique numbers from the SSB, and provide them to the MIN function. When copying the formula down to cell A12, the ID that was extracted in cell A11 is again in the expanded range and will again be found in the range $A$2:$A$5. However, YEND returns FALSE, and the value 0 is not extracted from the MSB. To see this, enter the array formula in Figure 19.44 by pressing Ctrl+Shift+Enter and copy it down.

Rice. 19.44. Formula element in argument lookup_value the MATCH function matches the query: "Give the minimum value within a specific MSB name that has not yet been used"

On fig. 19.45 shows that in the argument lookup_array of the second function MATCH, the range A$10:A10 has expanded to A$10:A11. To understand how this formula works, select its fragments one by one and click on F9 (Fig. 19.46–19.49).

Rice. 19.45. Expandable range A$10:A11 now (in cell A12) includes the first ID (54678)

Rice. 19.46. The combination of the UND functions and the second MATCH provides an array of Boolean values; two FALSE values ​​exclude null values ​​from the specific MSB name

Rice. 19.47. The zeros are eliminated and only the numbers 3 and 2 remain; the number 2 is the minimum, so it should be extracted next

Rice. 19.48. The MIN function selects the number 2; now the MATCH function can find the correct relative position for the INDEX function

Rice. 19.49. The INDEX function will retrieve the value 2, which corresponds to the relative fourth position of the ID in the range A2:A5

Now, returning to cell A11, you can add another condition so that empty cells do not affect the formula (Figure 19.50).

Rice. 19.50. Inside the MIN function are two conditions; the first: “are the cells empty?”, the second: “the value has not yet been used?”

On fig. 19.51 shows the final formula. It added a condition so that the rows in the range A11:A15 remain empty after the sorted unique values ​​are retrieved. On fig. 19.52 shows what happens if cell A3 is made empty. Our addition to check for empty cells worked.

It wasn't easy. But if you've read this far, I hope you enjoyed it.

Very often, when working in Excel, you need to use data about addressing cells in a spreadsheet. For this, the CELL function was provided. Let's consider its use on specific examples.

Function value and cell properties in Excel

It is worth noting that Excel uses several functions for addressing cells:

• - LINE;
• - COLUMN and others.

The CELL() function, the English version of CELL(), returns information about the formatting, address, or contents of a cell. The function can return detailed information about the cell's format, thereby eliminating the need for VBA in some cases. The function is especially useful if you need to display the full path of the file in the cells.

How does the CELL function work in Excel?

The CELL function in its work uses a syntax that consists of two arguments:

Examples of using the CELL function in Excel

Example 1. Given a table of accounting for the work of employees of an organization of the form:

It is necessary, using the CELL function, to calculate in which row and column the salary of 235,000 rubles is located.

To do this, we introduce a formula of the following form:

• – “row” and “column” – output parameter;
• – С8 – data address with salary.

As a result of calculations, we get: row No. 8 and column No. 3 (C).

How to find out the width of an excel table?

Example 2. You need to calculate the width of the table in characters. It should be noted right away that in Excel, by default, the width of columns and cells is measured in the number of characters that they fit in their value are available for display in a cell without a line break.

Note. The height of rows and cells in Excel, by default, is measured in base font units - in pt points. The larger the font, the higher the line to fully display the characters in height.

Enter in cell C14 a formula to calculate the sum of the widths of each column of the table:

• – “width” – function parameter;
• – A1 – the width of a certain column.

How to get the value of the first cell in a range

Example 3. In the condition of example 1, you need to display the content only from the first (upper left) cell from the range A5:C8.

Let's introduce a formula for calculation:

The description of the formula is similar to the previous two examples.

A cell in Excel is the main structural element of a sheet where you can enter data and other content. In this lesson, we will learn the basics of working with cells and their contents in order to perform calculations, analyze and organize data in Excel.

Understanding Cells in Excel

Each worksheet in Excel is made up of thousands of rectangles called cells. A cell is the intersection of a row and a column. Columns in Excel are denoted by letters (A, B, C), while rows are denoted by numbers (1, 2, 3).

Based on the row and column, each cell in Excel is given a name, also known as an address. For example, C5 is the cell that is at the intersection of column C and row 5. When you select a cell, its address is displayed in the Name field. Please note that when a cell is selected, the headings of the row and column at the intersection of which it is located become highlighted.

At Microsoft Office Excel It is possible to select multiple cells at once. A set of two or more cells is called a range. Any range, just like a cell, has its own address. In most cases, the address of a range consists of the address of the top left and bottom right cells, separated by a colon. Such a range is called contiguous or continuous. For example, a range that consists of cells B1, B2, B3, B4, and B5 would be written as B1:B5.

The figure below highlights two different ranges of cells:

Select cells in Excel

To enter data or edit the contents of a cell, you must first select it.

You can also select cells using the arrow keys on your keyboard (arrow keys).

Select a range of cells in Excel

When working with Excel, it is often necessary to select a large group of cells or a range.

Tables in Excel are a series of rows and columns of related data that you manage independently.

Working with tables in Excel, you can create reports, make calculations, build graphs and charts, sort and filter information.

If your work involves data processing, then Excel spreadsheet skills can help you save a lot of time and increase efficiency.

How to work with tables in Excel. Step-by-step instruction

Before working with tables in Excel, follow the recommendations for organizing data:

• Data should be organized in rows and columns, with each row containing information about one record, such as an order;
• The first row of the table should contain short, unique headings;
• Each column must contain one type of data, such as numbers, currency, or text;
• Each row should contain data for one record, such as an order. If applicable, provide a unique identifier for each line, such as an order number;
• The table must not contain empty rows and absolutely empty columns.

1. Select an area of ​​cells to create a table

Select the area of ​​cells where you want to create a table. Cells can be either empty or contain information.

2. Click the "Table" button on the Quick Access Toolbar

On the Insert tab, click the Table button.

3. Select a range of cells

In the pop-up, you can adjust the location of the data, as well as customize the display of headers. When everything is ready, click “OK”.

4. The table is ready. Fill in with data!

Congratulations, your spreadsheet is ready to be filled! You will learn about the main features in working with smart tables below.

Formatting a table in Excel

Pre-configured styles are available to customize the table format in Excel. All of them are located on the “Designer” tab in the “Table Styles” section:

If 7 styles are not enough for you to choose from, then by clicking on the button in the lower right corner of the table styles, all available styles will open. In addition to the styles predefined by the system, you can customize your format.

In addition to the color scheme, in the “Constructor” menu of tables, you can configure:

• Header Row Display – Enables and disables headers in the table;
• Total row - enables and disables the row with the sum of the values ​​in the columns;
• Alternating lines - highlights the alternating lines;
• First column - highlights the “bold” text in the first column with data;
• Last column - highlights the text in the last column with “bold”;
• Interleaved Columns - highlights alternating columns;
• Filter Button - Adds and removes filter buttons in column headers.

How to add a row or column in an Excel table

Even inside an already created table, you can add rows or columns. To do this, right-click on any cell to open a pop-up window:

• Select “Insert” and left-click on “Table Columns Left” if you want to add a column, or “Table Rows Above” if you want to insert a row.

• If you want to delete a row or column in a table, then scroll down the list in the pop-up window to the “Delete” item and select “Table Columns” if you want to delete a column or “Table Rows” if you want to delete a row.

How to sort a table in Excel

To sort information when working with a table, click the “arrow” to the right of the column heading, after which a pop-up window will appear:

In the window, select by what principle to sort the data: “ascending”, “descending”, “by color”, “numeric filters”.

How to filter data in an Excel table

To filter information in the table, click the “arrow” to the right of the column header, after which a pop-up window will appear:

• “Text filter” is displayed when there are text values ​​among the column data;
• “Filter by color”, as well as text filter, is available when the table has cells that are colored in a color that differs from the standard design;
• “Numeric filter” allows you to select data by the following parameters: “Equal to…”, “Not equal to…”, “Greater than…”, “Greater than or equal to…”, “Less than…”, “Less than or equal to…”, “Between…”, “Top 10…”, “Above average”, “Below average”, as well as set up your own filter.
• In a pop-up window, under “Search”, all data is displayed, by which you can filter, as well as select all values ​​or select only empty cells with one click.

If you want to cancel all created filtering settings, open the pop-up window above the desired column again and click “Remove filter from column”. After that, the table will return to its original form.

In the window list, select “Table” => “Total Row”:

A subtotal will appear at the bottom of the table. Click the left mouse button on the cell with the amount.

In the drop-down menu, select the principle of the subtotal: it can be the sum of the column values, “average”, “count”, “number of numbers”, “maximum”, “minimum”, etc.

How to fix a table header in Excel

The tables you work with are often large and contain dozens of rows. Scrolling the table “down” is difficult to navigate in the data if the column headings are not visible. In Excel, it is possible to fix the header in the table in such a way that when you scroll through the data, you will see the column headers.

To fix headers, do the following:

• Go to the "View" tab in the toolbar and select "Freeze Panes":

• Select "Freeze Top Row":

• Now, scrolling the table, you will not lose the headings and you can easily find out where the data is located:

How to flip a table in Excel

Imagine that we have a ready-made table with sales data by managers:

On the table above, the names of the sellers are indicated in the rows, and the months in the columns. In order to flip the table and place the months in the rows, and the names of the sellers, you need:

• Select the entire table (by holding down the left mouse button, select all the cells of the table) and copy the data (CTRL + C):

• Move the mouse cursor to a free cell and press the right mouse button. In the menu that opens, select " Special insert” and click on this item with the left mouse button:

• In the window that opens, in the “Insert” section, select “values” and check the box for “transpose”:

• Ready! Months are now placed in rows, and the names of sellers in columns. All that remains to be done is to convert the received data into a table.

In this article, you got acquainted with the principles of working with tables in Excel, as well as the basic approaches to creating them. Write your questions in the comments!

Program Microsoft Excel- very powerful tool, thanks to which you can create large tables with a beautiful design and an abundance of various formulas. Working with information is facilitated precisely because of the dynamics that are missing in the Word application.

This article will show you how to create a table in Excel. Thanks to step by step instructions even a “teapot” can deal with this. At first, novice users may find this difficult. But in fact, with constant work in the Excel program, you will become a professional and be able to help others.

The training plan will be simple:

• first, consider the various methods for creating tables;
• Then we are engaged in design so that the information is as clear and understandable as possible.

Manual mode

This method is the simplest. This is done in the following way.

1. When you open a blank sheet, you will see a large number of identical cells.

1. Select any number of rows and columns.

1. After that, go to the "Home" tab. Click on the "Borders" icon. Then select "All".

1. Immediately after that, you will have the usual elementary plate.

Now you can start filling in the data.

There is another way to manually draw a table.

1. Click on the "Borders" icon again. But this time, select Draw Grid.

1. Immediately after that, you will change the appearance of the cursor.

1. Make a left mouse click and drag the pointer to another position. As a result, a new grid will be drawn. The upper left corner is the initial position of the cursor. The lower right corner is the end.

The sizes can be any. The table will be created until you release your finger from the mouse button.

Auto mode

If you do not want to "work with your hands", you can always use ready-made functions. To do this, do the following.

1. Go to the "Insert" tab. Click on the "Tables" button and select the last item.

Pay attention to what we are prompted about hot keys. In the future, for automatic creation, you can use the combination of buttons Ctrl + T.

1. Immediately after that, you will see a window in which you need to specify the range of the future table.

1. To do this, simply select any area - the coordinates will be substituted automatically.

1. As soon as you release the cursor, the window will return to its original form. Click on the "OK" button.

1. As a result of this, a beautiful table with alternating lines will be created.

1. To change the name of a column, just click on it. After that, you can start editing directly in this cell or in the formula bar.

pivot table

This type of information presentation serves for its generalization and subsequent analysis. To create such an element, you need to do the following steps.

1. First, we create a table and fill it with some data. How to do this is described above.

1. Now go to the main menu "Insert". Next, we choose the option we need.

1. Right after that, you will have a new window.

1. Click on the first line (the input field must be made active). Only after that we select all the cells.

1. Then click on the "OK" button.

1. As a result of this, you will have a new sidebar where you need to configure the future table.

1. At this stage, you need to transfer the fields to the desired categories. The columns will be months, the rows will be the purpose of the costs, and the values ​​will be the amount of money.

To transfer, left-click on any field and, without releasing your finger, drag the cursor to the desired location.

Only after that (the cursor icon will change appearance) can the finger be released.

1. As a result of these actions, you will have a new beautiful table in which everything will be calculated automatically. Most importantly, new cells will appear - “Grand Total”.

You can specify the fields that are of interest for data analysis.

Sometimes it is not possible to correctly select fields for columns and rows. And in the end, nothing worthwhile comes out. For such cases, Microsoft developers have prepared their own data analysis options.

It works very simply.

1. First of all, we select the information we need.

1. After that, select the appropriate menu item.

1. As a result, the program itself will analyze the contents of the cells and offer several options.

1. By clicking on any of the proposed options and clicking on the "OK" button, everything will be created automatically.

1. In the case of the example, we got the sum of the total costs, excluding months.

Ready-made templates in Excel 2016

For the lazy ones this program allows you to create truly "cool" tables with just one click.

When you open Excel, you have the following options to choose from:

• open the latest files you have worked with before;
• create a new empty workbook;
• see tutorial from detailed information about the capabilities of this software;
• pick one ready template default;
• continue searching on the Internet if you don’t like any of the proposed designs;
• log in under your account Microsoft.

We are interested in ready-made options. If you scroll down a bit, you will see that there are a lot of them. But these are the default templates. Imagine how many you can download them on the Internet.

Click on any option you like.

Click on the "Create" button.

As a result of this, you get a ready-made version of a very large and complex table.

Registration

Appearance is one of the most important parameters. It is very important to focus on some elements. For example, a header, title, and so on. Everything depends on the specific case.

Consider briefly the basic manipulations with cells.

Create a header

Let's use a simple table as an example.

1. First, go to the "Home" tab and click on the menu item "Insert Rows to Sheet".

1. Select the line that appears and click on the "Merge Cells" menu item.

1. Next, write any title.

Changing the Height of Elements

Our header is the same size as the header. And it's not very pretty. In addition, it looks inconspicuous. In order to fix this, you need to move the cursor to the border of lines 1 and 2. After its appearance changes, left-click and drag it down.

As a result, the row height will be larger.

Text alignment

Our title is at the bottom of the cell and stuck to the header. In order to fix this, you need to use the alignment buttons. You can change the text position both vertically and horizontally.

We click on the button "In the middle" and we get the desired result.

Now the title looks much better.

Style change

Or use predefined styles. To do this, first select the line. Then, through the menu, select any of the proposed design options.

The effect will be very beautiful.

How to insert a new row or column

In order to change the number of elements in the table, you can use the "Insert" button.

You can add:

• cells;
• lines;
• columns;
• whole sheet.

Removing elements

You can destroy a cell or something else in the same way. There is a button for this.

Filling cells

If you want to highlight any column or line, you need to use the fill tool for this.

Thanks to it, you can change the color of any cells that were previously selected.

Element Format

You can do whatever you want with the table. To do this, just click on the "Format" button.

As a result of this, you will be able to:

• manually or automatically change the height of the rows;
• manually or automatically change the width of the columns;
• hide or show cells;
• rename sheet;
• change label color;
• protect the sheet;
• block the element;
• specify the cell format.

Content Format

If you click on the last of the above items, the following will appear:

With this tool, you can:

• change the format of the displayed data;
• specify alignment;

• choose any font;

• change table borders;

• "play" with the fill;

• set protection.

Using formulas in tables

It is thanks to the ability to use the auto-calculation functions (multiplication, addition, and so on) that Microsoft Excel has become a powerful tool.

For complete information about formulas in Excel, it is best to look at the official help page.

Consider the simplest operation - cell multiplication.

1. First, let's prepare the field for experiments.

1. Make active the first cell in which you want to display the result.

1. Enter the following command there.

1. Now press the Enter key. After that, move the cursor over the lower right corner of this cell until its appearance changes. Then hold down the left mouse click with your finger and drag down to the last line.

1. As a result of autosubstitution, the formula will fall into all cells.

The values ​​in the "Total cost" column will depend on the "Quantity" and "Cost per 1 kg" fields. That's the beauty of dynamics.

In addition, you can use ready-made functions for calculations. Let's try to calculate the sum of the last column.

1. First, select the values. Then click on the "Autosums" button, which is located on the "Home" tab.

1. As a result of this, the total sum of all numbers will appear below.

Use of graphics

Sometimes photos are used in cells instead of text. It is very easy to do this.

Select an empty element. Go to the "Insert" tab. Select the "Illustrations" section. Click on "Pictures".

1. Specify the file and click on the "Insert" button.

1. The result will not disappoint you. Looks very nice (depending on the selected pattern).

Export to Word

In order to copy data into a Word document, it is enough to do a couple of simple steps.

1. Select the data area.

1. Press the hot keys Ctrl+C.
2. Open Document
3. Now we use the Ctrl+V buttons.
4. The result will be as follows.

Online Services

For those who want to work in real mode” and share information with friends or work colleagues, there is a great tool “Google Sheets”.

Using this service, you can access your documents from any device: computer, laptop, phone or tablet.

Printing methods

Printing Word documents is usually a simple task. But with tables in Excel, everything is different. The most a big problem is that "by eye" it is difficult to determine the boundaries of the print. And very often almost empty sheets appear in the printer, on which there are only 1-2 lines of the table.

Such printouts are inconvenient for perception. It is much better when all the information is on one sheet and does not go anywhere beyond the borders. In this regard, developers from Microsoft have added the function of viewing documents. Let's see how to use it.

1. We open the document. He looks quite normal.

1. Next, press the hot keys Ctrl+P. In the window that appears, we see that the information does not fit on one sheet. We have lost the column "Total cost". In addition, at the bottom we are prompted that 2 pages will be used for printing.

In the 2007 version, for this you had to click on the "View" button.

1. To cancel press hot key Esc. As a result, a vertical dotted line will appear, which shows the borders of the print.

You can increase the space when printing as follows.

1. First of all, we reduce the margins. To do this, go to the "Page Layout" tab. Click on the "Fields" button and select the most "Narrow" option.

1. After that, reduce the width of the columns until the dotted line is outside the last column. How to do this was described above.

You need to reduce it within reasonable limits so that the readability of the text does not suffer.

1. Press Ctrl+P again. Now we see that the information is placed on one sheet.

Microsoft Product Version Differences

It should be understood that Excel 2003 has long been obsolete. It lacks a huge number of modern features and capabilities. In addition, the appearance of various objects (graphs, diagrams, and so on) is much inferior to modern requirements.

An example of an Excel 2003 workspace.

In modern 2007, 2010, 2013, and even more so 2016 versions, everything is much “cooler”.

Many menu items are in different sections. Some of them even changed their name. For example, the “Formulas” familiar to us were called “Functions” back in 2003. And they didn't take up much space.

Now they have a whole tab dedicated to them.

Limitations and features of different versions

On the official website of Microsoft, you can find online help, which contains all specifications created books.

An example of the most basic parameters.

This list is quite long. Therefore, it is worth clicking on the link and familiarize yourself with the rest.

Please note that the 2003 version is not even considered, since its support has been discontinued.

But in some budget organizations, this office suite is still used today.

Conclusion

This article has reviewed various ways creating and presenting tables. Particular attention was paid to giving a beautiful appearance. You should not overdo it in this regard, since bright colors and a variety of fonts will scare away a user who is trying to familiarize himself with the contents of the table.

Video instruction

For those who have any questions, a video is attached below, which includes additional comments on the instructions described above.

Excel provides a large gallery pre-made styles automatic table formatting. It also provides a preview when you hover over any style from the gallery. Styles can be used to quickly format regular ranges of cells.

Assigning Table Styles to a Normal Range

To implement this task, we need to create a table, assign a style to it, and then cancel "Format as Table".

Let's assign an auto-table formatting style to a regular range of cells:

1. On a blank sheet, select a range of empty cells and select the tool: "Home" - "Format as Table". Select the desired formatting style from the gallery.
2. In the "Create table" dialog box that appears, if necessary, check the "Table with headers" option and click OK.
3. Click on the table and on the auxiliary panel "Work with tables" - "Designer" select the tool "Convert to Range" in the tool section "Tools". Or right-click on the table and in the context menu select: "Table" - "Convert to Range". This will undo formatting as Excel spreadsheet range.

The table is converted to a regular range of cells that is formatted according to its style. It is also a very fast and convenient way to format cells. So formatting is much faster than doing everything from scratch. It is enough just to take the finished style from the gallery as a basis, and then make your own changes and additions to the details.

Automatic Formatting with Styles

Let's choose to change the style of automatic table formatting:

1. Create a table (CTRL+T) as shown in the picture.
2. Go to any table cell and select the tool "Home" - "Format as Table" or "Working with Tables" - "Table Styles".
3. Hover and hold your mouse over any of the suggested gallery presets. Pay attention to the table, which in the preview shows how this or that style assigned to it will look like.
4. Click the left mouse button and the table will be automatically formatted according to the new style you have chosen.

A very fast and convenient method of formatting is the assignment of a style, adding your own amendments or changes if necessary.

If you make changes to the style formatting, they will be saved (you can assign a different style if you wish). For example, if you change the background of one cell to yellow, then choosing a different style will make the same cell yellow.

Selecting table elements

Let's test methods for selecting tables as a whole and separately for its parts: rows and columns. To solve this problem, it is not necessary to activate the table. For an inactive table, just do the following:

1. For convenience, we will shift the table so that at its beginning there is an empty row and an empty column (for example, in cell B2).
2. Move the mouse cursor over the upper left corner of the table so that it moves to the diagonal black arrow.
3. Make one click with the left mouse button and only the data values ​​of the table will be highlighted. Click the same again and the entire table with headers and total row will be selected. If you click again, only the data will be selected again, since this function works in toggle mode.

Ways to select rows and columns with keyboard shortcuts:

1. Move the keyboard cursor to the table area to make it active.
2. Press the hot key combination CTRL+A to select data. Pressing CTRL+A again will select the entire table.
3. Click on any cell in the data range and move the mouse cursor over the edge of the table so that the cursor changes to an arrow cross. Click the left mouse button and the entire table will be selected.

Now select the rows and columns. In the active table, you can press the hot key combination CTRL + Space, then the data column will be highlighted. The one that contains the active cell. And if you press the combination SHIFT + Space, then the line corresponding to the active cell is selected. This function It is also implemented using the mouse cursor, if you bring it to the heading of one column or to the beginning of a line so that it changes its appearance to a vertical or horizontal arrow.

Video

Formulation of the problem

We have a table with which we constantly have to work (sort, filter, count something on it) and the contents of which periodically change (add, delete, edit). Well, at least, for an example - here it is like this:

The size - from several tens to several hundreds of thousands of lines - is not important. The task is to simplify and make your life easier in every possible way by turning these cells into a “smart” table.

Decision

Select any cell in the table and on the tab Home expand the list Format as table:

In the drop-down list of styles, select any fill option to our taste and color, and in the confirmation window for the selected range, click OK and we get the following output:

As a result, after such a transformation of the range into "smart" table(with a capital letter!) we have the following joys (except for a nice design):

1. Created Table gets a name Table1,2,3 etc. which can be changed to a more adequate one on the tab Constructor (Design). This name can be used in any formulas, drop-down lists, and functions, such as a data source for a pivot table or a lookup array for a VLOOKUP function.
2. Created once Table automatically adjusts to size when adding or deleting data to it. If you add to such table new lines - it will stretch lower, if you add new columns - it will expand in breadth. In the lower right corner tables you can see the automatically moving border marker and, if necessary, adjust its position with the mouse:
3. In a hat tables automatically AutoFilter turns on(can be forced to disable on the tab Data).
4. When adding new lines to them automatically all formulas are copied.
5. When creating a new column with a formula - it will be automatically copied to the entire column - no need to drag formula with black autocomplete cross.
6. When scrolling tables down column headings (A, B, C…) are changed to field names, i.e. you can no longer fix the range header as before (in Excel 2010 there is also an autofilter):
7. By enabling the checkbox Show total row tab Constructor (Design) we get an automatic totals string at the end tables with the ability to select a function (sum, average, count, etc.) for each column:
8. To the data in table can be addressed using the names of its individual elements. For example, to sum all the numbers in the VAT column, you can use the formula =SUM(Table1) instead of =SUM(F2:F200) and not to think about the size of the table, the number of rows and the correctness of the selection ranges. It is also possible to use following statements(assuming the table has the standard name Table 1):

• =Table1- link to the entire table, including column headings, data and total row
• =Table1- data-only link (no title bar)
• =Table1- link only to the first row of the table with column headings
• =Table1- link to total row (if enabled)
• =Table1- reference to the current line, for example, the formula =Table1;] - will refer to the VAT value from current line tables.

  (In the English version, these operators will sound, respectively, as #All, #Data, #Headers, #Totals and #This row).

P.S.

In Excel 2003 there was something remotely similar to such "smart" tables - it was called the List and was created through the menu Data - List - Create list (Data - List - Create list). But even half of the current functionality was not there at all. Older versions of Excel didn't have that either.

Microsoft Excel is the simplest and handy program for tabulation, calculations, subsequent formatting, writing reports with large quantity various formulas.

Understanding it is not at all difficult - you need to be patient, attentive, practice in practice and use it with pleasure.

Learn how to create a spreadsheet in excel step by step.

Content:

1. How to Create a Table in Excel - 3+ Step by Step Methods
2. How to create a spreadsheet with formulas

How to create a table in excel - 3+ step by step instructions

Microsoft Excel is very convenient and allows you to create tables in several ways at once - manually, automatically, make summary tables, choose from existing templates.

Let's consider each of them:

Tables in manual mode

Manual method number 1

The first thing you see when you open Excel is a lot of cells of the same size.

Do not immediately panic from the abundance of rectangles, numbers, letters and inscriptions.

In fact, an elementary table is created in 2 clicks.

Step 1: select with the mouse the required number of cells equal to the length and height of your future table.

Step 2: go to the tab called "Home", select the "Borders" icon, go to the "All borders" item. Profit. You can fill.

Step 2 Elementary table is ready

Manual method number 2

Step 1: click the "Borders" icon and select the "Draw Grid" menu item.

Step 2: after that you will change the appearance of the cursor. Make a click with the left mouse button and drag the pointer to the desired location.

The upper left corner is the starting position. The bottom right corner is final. To create a table, release your finger from the left mouse button.

How to create a regular table in excel automatically

Step 1: open the "Insert" tab. Select the "Tables" section from the menu and go to the last item.

Step 2: a window will appear on the monitor screen in which you will need to specify the boundaries. Select any area and the coordinates will appear automatically.

Step 4: after you release the mouse cursor, the window will return to its original form. Click the OK button.

Step 4: as a result, you will get a beautiful table with cells in two colors.

To change the name of a column, simply click on it with the mouse or enter the cursor in the formula field and write the text. Then enter the data and work with pleasure.

Tip: in the future, to create a table in this mode, you can use the hot key combination Ctrl + T.

How to create a pivot table in excel step by step with formulas

Such a table is necessary to summarize information and analyze it.

How to create it step by step:

Step 1: we make and fill out the basic form in one of the ways described above.

Step 2: Open the "Insert" menu and select the "Pivot Table" option we need.

Step 3: in the new window that appears, select the first row, then select the entire table with the mouse - its range will automatically be displayed in the active menu bar. We press the "ok" button.

Step 4: at this stage, a panel will appear on the side necessary settings. You need to move fields to categories.

The columns are the months, the rows are the purpose of the costs, the values ​​are the amount of money.

Step 5: to carry out the transfer, left-click on any field and, without releasing your finger, drag the cursor to the desired location.

When the cursor icon changes appearance, release your finger.

The result is a table of automatic calculations. For the convenience of analyzing information in it, you can select individual parts - the data is calculated automatically in the "Grand Total" cells.

If it’s difficult to deal with the values, the number of rows and columns of the table yourself, Excel developers have created several templates for data analysis. They work extremely simply.

We highlight the information we are interested in. Follow the menu command "Insert" - "Tables" - "Recommended Pivot Tables".

Create a PivotTable

Eventually software itself analyzes the content and offers several suitable options.

Manually select the value you need and click "ok", after which the solution will be created automatically.

In addition, please note that the moment you open the application on your computer, Excel immediately offers you several basic options and tables that you can create with a single click.

Here are the possibilities at your disposal:

1. Open the last tables you have worked with
2. Create a new book - this is the name of the working panel for creating tables in the program
3. Get acquainted with detailed information about all the features of the program - this is where you should start working
4. Select one of the already available default templates
5. Sign in with your account
6. Continue searching for templates on the Internet if none of the existing options didn't like it

Carefully study all the options of the program

How to create a table that counts itself in excel step by step

by the most popular application For the calculation and analysis of data, Excel became precisely due to the ability to carry out automatic calculations.

In fact, the entire program is built on the use of various mathematical formulas - you can see their huge range in the "Formulas" menu tab.

We will show with a simple example how to create a table in Excel with formulas for dummies.

Tip: all useful information about working with formulas, it is desirable to study before applying the program in practice, and also to familiarize yourself with the description of at least the main functions.

Example: Let's imagine that you need to multiply in cells.

Step 1: Let's take an elementary table as an example.

Step 2: set the cell in which you want to display the final result to the active state.

Enter the command =B2*C2 into it, where B and C are the column numbers, 2 are the rows, then press the Enter key.

Step 3: move the mouse cursor over the lower right corner of this cell until its appearance changes.

Hold the left mouse click with your finger and drag down to the last line. Using this auto-substitution method, the formula will appear in all cells.

Thus, the value of the total cost will depend on the cost of one kilo and the quantity of production.

You can also use ready-made autosums for calculations.

In the above example, we first select the values ​​​​we need and look for the menu item "Autosums" in the "Home" tab.

The result will be the total sum of all the required numbers.

Tip: if necessary, any table can be exported to Word. To do this, manually select all columns and cells, then use the keyboard shortcut Ctrl + C to copy and Ctrl + V to paste data into a new document.

Differences between different versions of Excel

Many people are interested in the question of how to create a table in Excel 2003.

Let's say right away that the software should be updated regularly, because the 2003 version in 2018 is at least obsolete and it lacks a huge number of important functions.

So, for example, "Formulas" in the menu used to be called "Functions" - an elementary discrepancy in names can confuse a beginner when working, and there were many times fewer formulas themselves.

Some enterprises still use outdated software, however, if you are going to install Excel 2003 for yourself, please note that its support has been discontinued, so do not forget to update the software regularly.

Don't Forget About Hotkeys

Top hotkeys for working in Excel

1. book closing-CTRL+W
2. book opening-CTRL+O
3. Switching to the "Home" tab- ALT+I
4. Saving a book-CTRL+S
5. copying-CTRL+C
6. Insert- CTRL+V
7. Cancel operation-CTRL+Z
8. Deleting Cell Content- DELETE
9. Fill color selection- ALT+I, R, 1
10. Cutting out-CTRL+X
11. Switching to the "Insert" tab- ALT+C
12. bold font-CTRL+B
13. Align cell contents to the center- ALT+I, E, 1
14. Switching to the "Page Layout" tab- ALT+Z
15. Switching to the "Data" tab- ALT+Y
16. Switching to the "View" tab-ALT+W
17. Opening context menu - SHIFT+F10 or shortcut menu key
18. Adding Borders- ALT+Z, G, G
19. Deleting a column- ALT+I, 0, 2, O
20. Switching to the "Formula" tab- ALT+L
21. Hiding selected rows-CTRL+9
22. Hiding selected columns-CTRL+0

To avoid the risk of data loss, use Google Sheets

As you can see, working in Excel is quite easy, moreover, it is convenient and greatly simplifies the workflow.

The program has a lot of possibilities for creating a beautiful appearance of tables.

And don't forget the Google Sheets app, which lets you work in real time, share data with colleagues, edit and store without fear of a system crash.

You will learn how to create a table for income, expenses and balance in Excel in the step-by-step video instructions below: