Which cell is called active in excel. Cell function in excel and examples of its use

MOSCOW INSTITUTE OF INTERNATIONAL

ECONOMIC RELATIONS

Specialty: "JURISPRUDENCE"

COURSE WORK

BY DISCIPLINE:

« COMPUTER SCIENCE AND MATHEMATICS»

On the topic

"The Purpose of SpreadsheetsMSexceland the main functions performed by these tables "

COMPLETED:STUDENT

CORRESPONDENCE DEPARTMENT

Gudyushkin Evgeny Nikolaevich

CHECKED: __________

GRADE: ___________

Ufa - 2007

Introduction 3 pages

1 . concept Spreadsheets Microsoft Excel 4 pages

2. Data types and formats of their presentation 7 p.

3. Application of operators in formulas 8 p.

4. Active cell and its modes of operation 11 p.

5. Autofill cells 12 pages

6. Selection of ranges 14 p.

7. Moving around the worksheet 15 p.

8. Copying and moving cells and other objects page 16

Conclusion page 18

List of used literature 20 pages.

Introduction

Today, many companies store and/or process some of their business information using the publicly available MS Excel program.
To work effectively with spreadsheet data, macros and controls are used - lists, switches, counters, buttons, calendars, etc. Macros automate repetitive and time-consuming processing of data stored in a table. The controls placed on the worksheet form the user interface of the workbook, which controls all the work of its tables and charts. Spreadsheets that use a graphical interface and macros to perform one or more related tasks are often referred to as spreadsheet applications. As a rule, such "professional" tables are developed using the Visual Basic for Applications (VBA) programming language built into MS Excel.

Microsoft Excel Spreadsheets- an extremely powerful program that allows you to achieve the desired result in various ways, even in the most seemingly simple situations. Excel offers rich possibilities for building complex formulas. Armed with a few mathematical operators and rules for entering values into cells, you can turn your worksheet into a powerful programmable calculator. excel has several hundred built-in functions that perform a wide range of different calculations.

Functions- these are special, pre-created formulas that allow you to easily and quickly perform complex calculations, perform financial and statistical analysis. With Excel, you can create complex charts for worksheet data. Using macros in Excel worksheets allows you to interact with the user and perform cumbersome and routine operations with just a few mouse clicks.

1 . concept Spreadsheets Microsoft Excel

The main MS Excel document is a Workbook, which is a *.xls file. A workbook can be thought of as the electronic equivalent of a binder folder. The book consists of worksheets (Worksheet). The maximum number of worksheets is 255, they are designated as Sheet 1 (Sheet 1), etc. (Fig. 1). A worksheet can be associated with the concept of "document" or "spreadsheet". Sheets of the book can be moved, copied, renamed. Moving can be done by dragging the sheet tab with the mouse. To rename, you can double-click the left mouse button on the label of the sheet to be renamed, and then enter a new name. Copying is conveniently performed using the context menu. Also, the context menu allows you to add a new sheet to the book and, if necessary, select all sheets. In the user's view, an Excel 97 spreadsheet (worksheet) consists of 65536 rows (rows) and 256 columns or columns (columns) that are displayed on a computer screen. Rows are numbered with integers from 1 to 65536, and columns or columns are denoted by letters of the Latin alphabet A, B, ..., Z, AA, AB, ... IV. At the intersection of a row and a column, the main structural element of the table is located - a cell (cell). The contents of a cell can be accessed by its address (link), for example, A5. A table (in MS Excel, the term worksheet is adopted, which we will use further) is a set of elementary cells, each of which belongs to a certain column and simultaneously belongs to a certain row. Typically, in a graphical representation, cells in the same column are stacked vertically one below the other, and cells in the same row are stacked next to each other horizontally. Rows and columns are identified in some way, for example columns are named and rows are numbered. The result is a data structure called a worksheet. All rows contain the same number of cells and all columns contain the same number of cells, that is, the worksheet has a rectangular shape. By default, the worksheet has 256 columns named A to IV and 16384 rows numbered 1 to 16384 (which is sufficient in most cases). The column name and row number together uniquely identify the cell that (simultaneously) belongs to them. This identifier is called the cell address or cell reference. Immediately, we note that MS Excel also supports another addressing system (link style), when both rows and columns are numbered. This style of linking is sometimes handy and needed for compatibility with other spreadsheet systems. Usually, as a rule, addresses are used in the form "column name - row number". Cells can also be given their own names, and these names can be used to refer to cells along with addresses.

In addition to the concept of a cell, the concept of an interval of cells is used - a rectangular area of adjacent cells. The interval is specified by specifying the address of the upper left cell and the lower right cell, separated by the symbol: (colon). For example, the entry E5:G10 defines an interval of cells. Accordingly, an interval consisting of several integer columns is denoted by the names of the leftmost and rightmost columns, separated by a colon, for example, the entry B: G does not denote Boris Grebenshchikov, but all cells in columns B, C, D, E, F and G. Worksheet cells are designed to store different values. Thus, a cell can play the same role as a variable in mathematics: it has a designation (name or address) and can have and change a value. Any calculation consists in the fact that the values of some variables are calculated values of other variables. Usually, the calculation method is described using a formula containing mathematical operations and functions. But the formula itself is also a value that can be stored in a cell! This is the main idea of spreadsheets: some worksheet cells are used as independent variables, which must be given values from the outside, and other cells are used as dependent variables (they are called in MS Excel - dependent cells), which contain formulas that refer to on independent variables (In MS Excel they are called - influencing cells). The user enters the initial data into the influencing cells, calculations are automatically performed using the formulas in the dependent cells, and the user sees the finished result of the calculations in the dependent cells. The capabilities of Excel are sufficient to describe (and therefore automatically perform) any calculations (in any case, these capabilities are certainly sufficient for calculations that are practically necessary in business).

MS Excel is not just a spreadsheet with data and formulas that perform elementary calculations using four arithmetic operations and some built-in functions. It is a versatile data processing system that can be used to analyze and visualize data, and this powerful Microsoft Office tool can truly produce amazing results - in the right hands, of course. Forecasting in MS Excel is one of the most commonly used features. MS Excel can easily extrapolate a series of data - for example, to analyze the available actual data, assess the current trend in their change and obtain, on this basis, a short-term forecast for the future.

Excel is a multi-window program. This means that multiple documents (workbooks) can be opened and edited at the same time.

The appearance of the presentation of information contained in a spreadsheet can be changed by changing the data format, as well as setting the design of cells, changing their width, height and color (menu Cell Format, command View).

2. Data types and formats of their representation

Any cell can contain one of the following types of data:

- number (numbers can be presented in various formats: general, numerical with a different number of decimal places, in monetary or financial, as well as fractional or exponential formats);

– text (arbitrary combination of characters – in general or text formats);

– date (in a special date format);

– time (time format);

- a formula for performing the necessary calculations (a formula is an arbitrary mathematical expression starting with an = sign). A formula can contain function calls, various operators, and cell references. For example, the formula =PI() located in cell A5 returns the value of the number p (3.1415 ...) to this cell, or the formula =RAND() located in cell A6 returns a random number in the range from 0 to 1.

Cell format can be controlled using the menu Cell Format.

3. Using operators in formulas

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition
- (minus sign)	Subtraction
unary minus
* (asterisk)	Multiplication
/ (slash)
% (percent sign)
^ (cap)	erection to a degree	3^2 (similar to 3*3)

For example, the formula =COS(A5)/2+SIN(A5/6) located in cell A8 will return the value 0, provided that the formula =PI() is located in cell A5.

Comparison Operators are used to denote the operations of comparing two numbers. The result of the comparison operation is the logical value TRUE or FALSE.

Comparison operator	ZValue	Example
= (equal sign)
> (greater sign)

>= (greater than sign and equal sign)	More or equal
	Less than or equal to
(greater than sign and less than sign)

For example, let's say cell B5 contains the formula =PI( ) , and in cell C5 =RAND( ) . In cell D5, you need to insert the result of dividing B5 by C5. There is a possibility of an erroneous situation - division by zero. To prevent this, enter the following formula in cell D5 =IF(C50;B5/C5;"You can't divide by zero". In this example, the IF function returns either the result of dividing the contents of two cells, or, in the case where C5 = 0, a warning about the illegal division by zero.

Text operator «&» is used to denote the operation of concatenating multiple text strings or text constants into a single string.

For example, let's say cell A5 contains the formula =PI(), and in cell A6 =RAND(). The result of the product of the contents of these cells can be placed in cell A7 in the form of a formula ="Product of PI by a random number ="&A5*A6.

Address operators define cell ranges.

: (colon) is a range operator that refers to all cells between the range boundaries, including those cells themselves.

Example . B5:B15 - cells of column B, from the fifth to the fifteenth.

, (comma) is a union operator that refers to the union of range cells (in fact, it is the union of several links into one link).

Example . The SUM(B5:B15,D5:D15) function will sum the contents of the cells in these two ranges.

(space) is an intersection operator that refers to common range cells.

Example . The SUM(B5:B15 A7:D7) function will return the contents of cell B7, because it is she who is common to these ranges.

4. Active cell and its modes of operation

An active cell is a cell with which a user can currently work (or is working) by entering or editing data or formulas. The address of the active cell is displayed in the cell name field (see Figure 1).

An active cell can be in three main modes of operation, which are shown in Fig. 2.

In mode Selected cell all basic editing modes and cell parameter settings are available. Selection of several cells is carried out by dragging the cursor with the left mouse button pressed through the selected cells. The first cell in the selected range remains active.

IN Edit Mode the active cell switches automatically when you start typing any characters on the keyboard or when you go to the formula editing line.

In the third mode, the moving dotted frame of the cell means that its contents are on the clipboard and can be used for pasting.

5. Autofill Cells

Compilation of tables is a laborious process. The Excel developers have made this task easier with the auto-complete mechanism. It can be used where a certain number of adjacent cells must be filled with homogeneous information. The first element of the data series is entered into one of the cells, the second element is entered into the next. Then you should select both cells (by dragging while holding down the left mouse button), set the mouse pointer to the auto-fill marker (black square in the lower right corner of the cell or group of selected cells, while the mouse pointer becomes a black cross), press the left mouse button and drag it further down the row or column to be filled in automatically. Filling will occur from the available lists (see menu Service – Parameters… – Lists), or by the principle: next value = previous + step. The desired list can be independently generated and added to the list of standard ones.

Let's give examples showing the possibilities of auto-completion.

In the first two cells, enter the dates 02/27/00 and 02/28/00. After completing the sequence of actions described above, we get a series:

Let Xmin=2, Xmin+h=2,2. Let's enter these values in adjacent cells and apply autofill. We get a series:

You should pay attention to the fact that by default the separator between the integer and the fractional part of the number can be a comma. And if Excel does not perceive the data entered with dots as numbers, just replace the dots in the numbers with commas or change the default decimal point in the Windows settings through the menu Start(subparagraph Settings, Control Panel, Language and standards).

Comment. If the data series being filled out goes beyond the working screen, it is more convenient to use the command Edit – Fill –Progression…

6. Selection of ranges

A range of adjacent cells can be selected by selecting the first cell in the range, pressing the SHIFT key, and then selecting the last cell in the range.

A special case is when the range boundary is out of view (outside the current screen). Then, while holding down the Shift key, press the End keys and the desired arrow key in sequence. If the range consists of multiple rows or columns, hold down Shift, press END again and then an arrow key (in the direction you want to make the selection).

If you need to select nonadjacent cells (or their ranges), use the CTRL key. Select the cells in the first range. Then, holding CTRL, select the second range with the mouse, then the third, and so on. Curiously, the first cell of the last selected range will be active.

You can select one row (or column) by clicking the left mouse button on the row (column) name. To select several adjacent rows (columns), drag the mouse with the left button pressed over the names of the selected rows (columns). Nonadjacent rows (columns) are selected using the CTRL key.

All cells of the sheet can be selected using the button Select all, located at the intersection of the row of column names and the column of row names. You can cancel the selection by clicking the left mouse button anywhere on the sheet.

7. Moving around the worksheet

Only a small portion of the worksheet is displayed on the screen. Therefore, it is necessary to be able to move to the desired part of it. Scrollbars can be used for this.

In some cases, it is more convenient to use the keyboard. You can return to the first cell of the sheet using the CTRL+HOME keys. Using the key combination CTRL + END, you can move to the last (rightmost) cell of the sheet. Pressing the Home key will move you to the beginning of the current line. You can move one screen to the left or right (keys ALT + PgUp and ALT + PgDn respectively). Using the combinations CTRL + PgUp and CTRL + PgDn, you can quickly move from one sheet of the book to another. The PgUp and PgDn keys allow you to scroll up and down the screen.

You can move to the border of the current block of information on the sheet by pressing CTRL and the desired arrow key.

8. Copying and moving cells and other objects

To copy and move a cell or a group of cells, it is convenient to use the capabilities provided by the Clipboard of the Windows operating system. All functions for interacting with the clipboard in the Excel application are implemented through the menu Edit and consists of three main features:

1. Copying the selected cells (cells, arbitrary text, picture or any other object) to the intermediate clipboard (the object does not disappear from the screen and remains in its place). Copying is carried out through the sub-item Copy or through the hot key combination Ctrl+C.

2. Cutting the selected object to the clipboard is carried out through the sub-item Cut or through the hot key combination Ctrl+X. In this case, the selected object, as a rule, disappears from the screen and is placed in the intermediate clipboard (when moving part of the table to a new location for Excel after the operation Cut cells do not disappear from spreadsheet until operation is performed Insert).

3. Inserting an object from the clipboard to the place where the editing cursor is currently located or to the current cell of the spreadsheet. To do this, we use the subparagraph Insert or press the key combination Ctrl+V.

Comment. Hot combinations depending on the version of Excel or type of installation may differ from the above.

The second way. You can also perform intermediate exchange buffer interaction operations through context menu selected object (the context menu is called by pressing the right mouse button on the selected object). Intermediate buffer operations are usually grouped into a single block and are easily recognizable.

The third way to perform clipboard operations is to use the toolbar, with the main buttons for interacting with the clipboard located on it, which are shown below

Conclusion

Considering this topic, I came to the conclusion that one of the most productive ideas in the field of computer information technology was the idea of a spreadsheet. Many PC software developers have created their own versions of spreadsheet processors - application programs designed to work with spreadsheets. Of these, Lotus 1-2-3 by Lotus Development, Supercalc by Computer Associates, Multiplan, and Excel by Microsoft are the most well-known. Domestic school computers are also equipped with simplified (educational) versions of spreadsheet processors.

Spreadsheet processors (TP) are a convenient tool for economists, accountants, engineers, scientists - all those who have to work with large amounts of numerical information. These programs allow you to create tables that (unlike relational databases) are dynamic, that is, they contain so-called calculated fields, the values of which are automatically recalculated according to specified formulas when the values of the source data contained in other fields change. When working with spreadsheet processors, documents are created - spreadsheets (ET). A spreadsheet (document) is created in the computer's memory. In the future, it can be viewed, modified, recorded on a magnetic disk for storage, printed on a printer. The presentation of data in the form of tables greatly simplifies the analysis of information. They are focused primarily on solving economic problems, but they can be used to solve mathematical, physical and engineering problems, for example, perform calculations using formulas, build graphs and diagrams.

From the foregoing, we can conclude that MS Excel spreadsheets are a very convenient and almost indispensable tool for both experienced users and beginners. This program greatly simplifies and makes it easier and more enjoyable to work in many areas of our lives.

Each person whose work is related to the preparation of any reports, plotting graphs, performing complex calculations should be able to use MS Excel. This will greatly simplify and make all routine work faster and less time consuming.

References

Blatner P, Ulrich L, Cook K. Using Excel 2005. Special edition. - M., K., St. Petersburg. 2005.

Karpov B. Microsoft Office 2000 Handbook. - St. Petersburg: Peter, 2004.

Akhmetov K., Borzenko A. Modern personal computer. - M.: Computer-press, 1995.

Bott Ed. Using Microsoft Office 97. - K .: Dialectics, 1997.

Dodge M., Kinata K., Stinson K. Effective work with Excel 7.0 for Windows - St. Petersburg: Peter, 1996.

Novikov F.A., Yatsenko A. Microsoft Office as a whole - St. Petersburg: BHV-St. Petersburg, 2004.

Martik Althaus Michael Orlet, Excel 7.0. - M.: Bikom, 2002.

Nicole N., Albrecht R. Excel 7.0 Spreadsheets. -M.: ECOM., 2002.

Nicole N., Albrecht R. Excel 7.0 Spreadsheets for Power Users. -M.: ECOM., 2002.

Probityuk A. Excel 7.0 for Windows in the office. -K., BHV, 2000.

no right answer

10. Specify what the worksheet cell address consists ofin a programme Excel.

column designation, line number

column designation

column number

line number

11 . In a programme excel specifying the address of a cell in a formula is called...

function
operator
cell name

12. The active cell in an Excel spreadsheet is the cell...

for writing commands

highlighted cell

13. How to select nonadjacent cells in Excel?

< ctrl>

click on the first cell, press < Shift> and while holding it, click on other cells

click on the first cell, press < alt> and while holding it, click on other cells

perform actions: Edit– Go – Select.

14. How to select a range of cellsin the program tableexcel?

click on the first cell, press < Shift>

click on the first cell, press < ctrl> and while holding it, click on the last cell

click on the first cell < alt> and while holding it, click on the last cell

perform actions Edit– Go – Highlight

15 . Specify the correct actions when data entry is completed in a cellin a programmeExcel.

press a key < Enter>

click on the button < Cancel> formula bars

press a key < end>

press a key < Space>

16 . Which function key inMicrosoft Excel Can I edit and enter data in the active cell?

17. How to delete the contents of cellsin a programmeexcel?

select a cell and click < Del>

select a cell and click < ctrl> + < Del>

select the cell, press the left mouse button, in the dialog box that appears, select the command Clear content

select a cell and execute commands: View– Usual.

18. What commands can be used to add cells to the tablein a programmeexcel?

Format– Cells...

Insert – Add – Cells...

Insert – Sheet

Insert– Cells...

19 . Before enteringinformation into a cellin a programmeExcel need...

a) make the cell active

b) create a new cell

c) call the context menu by clicking the right mouse button

d) press a key Delete.

20. Specify how the names of the lines on the worksheet are indicated in the program Excel.

numbered by numbers

21. Specify how the names of the columns on the worksheet are indicated in the program Excel.

named arbitrarily by users

denoted by the letters of the Russian alphabet

denoted by letters of the Latin alphabet

numbered by numbers

22. If you click on the row headerin a programmeExcel...

line will be highlighted

line content will appear

expected to enter a formula expression

insert a new line

Option 2

1. The data of the program table are called the main ones. excel, ...

data that can be determined from the values of other cells

derived data

all spreadsheet data is master

2. Table data are called derivatives excel, …

data that cannot be determined from other cells

data that is determined by the values of other cells

Basic data

all spreadsheet data is derived

3. Specify the correct worksheet column labelsin a programmeExcel.

4. Specify the correct line designation of the worksheet in a programmeExcel.

5. Enter the correct cell addressin a programme Excel.

6 . The range of a table in Excel is...

a set of cells that form a rectangular area in a table

all cells of one row;

all cells in one column;

set of allowed values.

7. Specify the correct notation for the ranges of the table in the programexcel.

8. Ordering the values of a range of cells in the program excel

named in sequence...

formatting

filtration

grouping

sorting

9 . What data can be entered into a cell in a programmeexcel?

all of the above

10. What formatting is applicable to cells in excel?

framing and filling

text alignment and font format

data type, width and height

all options are correct

11. Command Dialog Box Format in a programme excel used...

filling in table entries

table formatting

spell check sheet

filter table records by condition

12. You can change the data format in a single cell using the panel ...

Formatting

Formula bar

Standard

Menu bar

13. You can format cells from a specific range using the commands ...

Format - Cells

Service - Cells

Table - Cells

Insert - Cells

14. You can change the width and height of cells using the commands ...

Format - String; Format - Column

Service - String;Service - Column

Insert - String;Insert - Column

Edit - String;Edit - Column

15 . If you double-click on a filled table cell in Excel, the mode is activated ...

editing cell content

Text

copy cell content

data entry, if it is formula expression

16. Scroll barsin the program windowExcel is needed for...

a) viewing the diagram

b) table browsing

c) browsing books

d) viewing the contents of the sheet

17. Is the program capableexcelautomatically continue a sequence of homogeneous data?

only a sequence of natural numbers

just a sequence of dates

18. With functionAutocomplete in the program tableexcelcan…

create series of numbers, days, dates, quarters, etc.

automatically perform simple calculations

automatically perform complex calculations

make changes to the contents of a cell

19. Aautomatically fill in the program tableexcelhomogeneous data, you can use the commands ...

Edit - Fill - Progression

View - Fill - Progression

Format - Fill - Progression

Service - Autocomplete

20. How to multiply the contents of the current cell into several cells in a column or row of a table in the programexcel?

by dragging the fill handle
moving the mouse cursor

copying the formula in the formula bar

filling in the status bar

21. What is a document in the programexcel?

set of tables - a workbook that consists of one or more worksheets*

a set of numerical data interconnected by arithmetic and logical relationships

a set of columns marked with Latin letters A,B,C ...

a set of strings numbered with integers

22. Vertical scroll bar in the program windowexcelallows...

a) look at different sections of the sheet

b) view the document as it will be printed

c) change the scale of the document

d) resize the window

Test #6

"Calculation and diagramming in the programexcel.

1 option

1. What character should the formula start with? excel?

does not matter

2. Is it possible to edit formulas in a programme excel?

no correct answer

3. How to type a formula for calculation in the program excel?

select cell, enter formula

select a cell, enter the answer immediately

select the cell, type the “=” sign, write the formula without skipping the operation signs *

select the cell, type the “=” sign, write the formula, skipping the operation signs

4. Which character is not a designation of an arithmetic operation in a programexcel?

5. The expression "Datacells A1 divided bycell dataB1" in the programexcellooks like...

6 . Is it possible when entering formulasin a programmeexceluse brackets?

Yes, but only when using absolute references

Yes, but only when using links to other sheets

7. Expression 5(A2+C3):3in a spreadsheet looks like this:

8. Specify the correct formula expression in the documentprogramsexcel …

=50 : 100

9. To insert a formula into a cellin a programme excel, you need to enter the sign:

10. Select the correct formula entry for the spreadsheet:

11. Which of the following formulas multiplies all the data of the top 10 rows of the current column tables in the programexcel ?

A1*A2*A3*A4*A5*A6*A7*A8*A9*A10

Option 2

1. How to fix a mistake in a formula in a program excel ?

rewrite it

click on the formula and correct the errors in the formula bar

copy the correct formula

execute commands in sequence: Format - Formula

2. Specify what the function defines AVERAGEin a programme excel?

arithmetic mean of a given range of cells

average value from cell division

search for the shortest text

3. What is the function used for SUM in a programme excel?

to get the sum of the squares of the given numbers

to get the sum of the given numbers

to get the difference of sums of numbers

to get the square of the given numbers

4. Specify what the function does MIN in Excel?

finding the smallest value

finding the mean

descending ordering of numbers

search for the shortest text

5. Specify what the function doesMAX in Excel?

search for the shortest text

descending ordering of numbers

finding the mean

search for the largest value

6 . CommandAutoSum in Excel need for…

non-graphic presentation of information

cell sum calculation

graphic representationinformation

graphical construction of columns or rows

7 . Data sortingin a programmeExcel is...

order data in ascending or descending order

order data in ascending order only

order data in descending order only

data filtering

8 . What types of sorting exist in Excel?

a) descending sorting; sort by removal

b) sorting in ascending order; descending sort

c) sort by multiplication; ascending sort

d) sorting down; descending sort

9 . Ddiagram- this…

a) graphical representation of data

b) external formatting of data, as well as format adjustment

their content data

c) a sequence of cells that form a logical

d) numbered labels, which are located on the left side

each line

1 0 . Diagrams in a programme Excel is made for...

graphical presentation of information

graphical construction of columns

graphical construction of columns or rows;

graphical construction of columns and rows

1 1 . What commands canadd chart to program bookexcel?

Insert - Chart -Chart Wizards

Chart - Add Chart

Service - Diagram sequential execution of steps Chart Wizards

View - Chart - Add Chart

Test #7

"Work inPowerPoint. Creation of colorful presentations»

1 option

1. What is Power Point?

Microsoft Office application for creating presentations

application program for processing code tables

a computer device that manages its resources in the process of processing data in tabular form

system program that manages computer resources

2. What is a presentationPowerPoint?

demonstration set of slides prepared on a computer

spreadsheet application

computer device that controls a slide show

a text document containing a set of drawings, photographs, diagrams

3. powerpointneeded to create...

tables to improve the efficiency of calculating formula expressions

text documents containing graphic objects

Internet pages in order to provide wide access to available information

presentations in order to improve the efficiency of perception and memorization of information

4. A component of a presentation containing various objects is called ...

5. A set of slides collected in one file form ...

presentation

6. Launchprogramspowerpoint

Start - Main Menu - Programs -Microsoft power point

Start - Main Menu - Find -Microsoft power point

Taskbars - Settings - Control Panel -Microsoft power point

Worker table– Start – Microsoft power point

7. In which section of the program window menupowerpointthere is a teamCreate new) slide ?

slide show

File

Insert

8 . Choosing a slide layout in the programpowerpointdone with commands...

Format - Slide layout

Format - Slide Color Scheme

1. What is a spreadsheet (ET)

Spreadsheet - a computer program that allows you to perform calculations with data presented in the form of two-dimensional arrays that mimic paper tables

2. Expand the concepts: row, column, cell, block of cells.

Title bar is used to display the name of the application.

Input and edit line displays data or formulas entered into the current table cell. In this row, you can view or edit the contents of this cell, as well as see the formula itself.

Status bar contains information for jumping to any required page and for zooming.

Prompt line is designed to provide information to the user about his possible actions at the current moment.

Cell The worksheet is formed by the intersection of a row and a column, and its address is determined by the letter of the column and the row number: for example, cell A1 is at the intersection of the first row and column A, which is in fourth place. Not only a cell can have an address, but also a group of adjacent cells block (range) of cells. A cell block can be a row or part of a row, a column or part of a column, as well as a rectangle consisting of several rows and columns or their parts. A block of cells is specified by specifying the address of its first and last cells, between which a separating sign is placed - a colon (B2: D5).

3. What is a cell address?

4. What cell is called current (active)?

Table hotspot called its part filled with data

5. How is absolute addressing different from relative addressing?

6. What can be the content of the ET cell?

Different tables may contain completely different information. Some cells contain text, some cells contain numeric data. From the point of view of Excel, a cell can contain three kinds of data.

Text the data is a string of text of arbitrary length. Excel reproduces such data exactly as entered. Such a cell cannot be used in calculations. If Excel cannot interpret the data as a number or as a formula, it considers it to be text data.

Numeric data is a single number entered into a cell. They cannot contain alphabetic or special characters, because mathematical operations are performed on them. The only exceptions are the decimal point (comma) and the number sign that precedes it. How numbers are considered data that define dates or amounts of money.

Formulas are instructions on which calculations are performed. In this case, the formula itself may not be visible on the screen, and the result of calculations based on it will be presented in the cell. The content of a cell is treated as a formula if it starts with an equal sign.

7. What are the rules for writing formulas?

1. Make sure that the cell in which you want to get the calculation result is active (highlighted in italics).

2. Entering a formula begins with the “=” sign. This character is entered from the keyboard.

3. After entering the “=” sign, Excel enters the formula input mode. In this mode, when a cell is selected, its address is automatically entered into the formula. This saves the user from having to know cell addresses and enter them into the formula from the keyboard.

4. Being in the formula input mode, you successively point with the left mouse button to the cells that store some numerical values, and enter the operation signs between the original values from the keyboard.

§ Operation signs must be entered between cell addresses.

§ It is more convenient to enter operation characters from the right numeric keypad. In order for this block to work in the desired mode, the indicator must be enabled.

5. In order for the calculation result to appear in the active cell, you must exit the formula input mode.

§ completes the formula entry, and moves the cursor to the next cell.

§ “Green check mark” on the formula input panel completes the formula entry, and leaves the cursor in the same cell.

9. Name the features of Excel

Spreadsheet MS Excel (spreadsheets) is one of the most commonly used applications of the integrated MS Office package, a powerful tool in capable hands that greatly simplifies routine daily work. The main purpose of MS Excel is to solve almost any calculation problems, the input data of which can be presented in the form of tables. The use of spreadsheets makes it easier to work with data and allows you to get results without programming calculations. In combination with the Visual Basic for Application (VBA) programming language, the MS Excel spreadsheet becomes universal and allows you to solve any problem at all, regardless of its nature.

10. What is the structure of the Excel window?

Basic Structural Elements of a Spreadsheet:

Line

Column

Cell

A spreadsheet can have multiple sheets. These sheets form book. The cell currently selected is called active cell.

11. What is the sequence of creating ET.

To create a new book With Microsoft Excel open, do one of the following:

Click on the button Create on the panel Standard ;

Press a keyboard shortcut<ctrl>+ <N>;

Execute menu command File -Create and then on the panel that pops up on the right

in section Creation command blank book (Fig. 8.3).

After that, a new Excel document will open in front of you, ready to work. The program will give the new document a type name book1 , but you can give the document a unique name when you save it.

12. How to clear the current ET cell?

Delete button

13. How to enter a formula in a ET cell?

Rules for writing formulas in Excel:

two operation symbols must not be placed together;

each opening parenthesis must have a matching closing parenthesis (Excel will not allow you to fix the entry of a formula into a cell if there is no matching parenthesis);

formula cannot contain more than 1024 characters, formulas can be divided into many simpler ones (principle of superposition).

Algorithm 2.1. Entering a simple formula

The following simple formula is used to multiply 135 by 1%.

To enter a simple formula, do the following.

Select (activate) a cell A3 , to enter a formula.

Enter character “= ” and formula symbols (zero integers can be omitted when entering). Please note that the word is displayed on the left side of the status bar Input. Entered characters simultaneously appear in the cell A3 and in formula bar.

Fix the manual entry of the formula - press the key . The result of running the formula 1.35 is displayed in the cell A3. The word is displayed on the left side of the status bar Ready.

The formula will appear in the formula bar when the corresponding cell is active.

Instead of a formula, you can enter a constant in a cell (directly the value is 1.35).

14. How is a formula copied? 5 How to fix the contents of a cell?

15. How to format numeric data?

The Cell command of the Format menu allows you to control the output (display) of numeric and text values in the activated cell.

16. How to delete rows (columns) of ET?

Blank cells can be inserted above or to the left of the active cell on the sheet. In this case, the remaining cells will be shifted down in the same column or to the right in the same row. In the same way, you can insert rows above the selected row or columns to the left of the selected column. Cells, rows and columns can also be deleted.

17. How to align data in ET cells

[x] in which commands are entered.

to record commands;

for charting

to create a table

MS Excel 2010. Which tab contains the commands: Ruler, Formula Bar, Grid, Headings?

[x] View

home

Insert

Formulas

data

MS Excel 2010. What group of commands do the following commands belong to: Freeze Panes, Arrange All, New Window?

[x] Window

Macros

Scale

Show

Paragraph

MS Excel. What command did the student use to automatically convert the grade from letter to number?

[x] Macro

Formula

Replacement

AutoSum

Number

MS Excel 2010. What will the student take as a basis when building a diagram?

[x] Table data

Excel workbook

Number of sheets

Cell Format

no answer

[x] COUNT

AVERAGE

SUM

EMPTY

MS Excel 2010. The student needs to determine the day of the week in the numerical format of the given date. Which function should be used?

[x] DAYWEEK

TIMEVALUE

TODAY

CHECK

AVERAGE

MS Excel 2010. Which function rounds a number to the specified number of decimal places?

[x] ROUND

ROUND

CHECK

SUM

MAX

MS Excel. What function is used to exponentiate?

[x] POWER

ROUND

CHECK

SUM

MAX

45. MS Excel 2010. Specify which functions have no arguments:

[x] TODAY, PI

IF AND

COUNT, AVERAGE

RANK, RANGE

SUM, MIN

MS Excel 2010. When moving a chart from one sheet to another, a student used the “Move Chart” command. What tab is this command in?

[x] Constructor

Layout

Format

home

Insert

MS Excel 2010. The student needs to indicate the name of the axes in the diagram. Which tab will he use to fulfill this requirement?

[x] Layout

Constructor

Format

home

Insert

MS Excel 2010. The student in the diagram needs to indicate the data label over the points. What additional option should he choose?

[x] Top

Left

On right

Bottom

Centered

MS Excel 2010. The student needs to choose the type of diagram for the best display of the morbidity criteria for different age groups. What type of diagram will he not be able to use?

[x] tree

bar graph

with areas

circular

schedule

MS Excel 2010. In which tab is the command to select data for plotting a chart?

[x] Constructor

Layout

Format

Diagram

Insert

MS Excel 2010. In the finished chart, you need to change the background. What tab is this feature in?

[x] Format

View

Constructor

Diagram

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 save you 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 "Paste Special" 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!

Microsoft Excel is a very powerful tool, thanks to which you can create large spreadsheets 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 figure it out. 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.

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

Select any number of rows and columns.

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

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.

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

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

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.

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.

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

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

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

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

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.

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

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

Right after that, you will have a new window.

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

Then click on the "OK" button.

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

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.

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.

First of all, we select the information we need.

After that, select the appropriate menu item.

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

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

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

Ready-made templates in Excel 2016

For those who are especially lazy, 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 a tutorial with detailed information about the capabilities of this software;
choose some ready-made default template;
continue searching on the Internet if you don’t like any of the proposed designs;
sign in with your Microsoft account.

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.

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

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

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.

First, let's prepare the field for experiments.

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

Enter the following command there.

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.

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.

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

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".

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

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.

Select the data area.

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

Online Services

For those who want to work in "real time" and share information with friends or work colleagues, there is a great tool called "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 biggest 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.

We open the document. He looks quite normal.

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.

To cancel, press the Esc hotkey. 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.

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.

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.

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 the technical characteristics of the 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 looked at various ways to create and present tables. Particular attention has been 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 of ready-made automatic table formatting styles. 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:

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.
In the "Create table" dialog box that appears, if necessary, check the "Table with headers" option and click OK.
Click on the table and on the auxiliary panel "Working 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 an 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:

Create a table (CTRL+T) as shown in the picture.
Go to any table cell and select the tool "Home" - "Format as Table" or "Working with Tables" - "Table Styles".
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.
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:

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).
Move the mouse cursor over the upper left corner of the table so that it moves to the diagonal black arrow.
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:

Move the keyboard cursor to the table area to make it active.
Press the hot key combination CTRL+A to select data. Pressing CTRL+A again will select the entire table.
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 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.

Solution

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):

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.
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:
In a hat tables automatically AutoFilter turns on(can be forced to disable on the tab Data).
When adding new lines to them automatically all formulas are copied.
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.
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):
By enabling the checkbox Show total row tab Constructor (Design) we get an automatic totals row at the end tables with the ability to select a function (sum, average, count, etc.) for each column:
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 the 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 row, for example, the formula =Table1;] - will refer to the VAT value from the current row of the table.
(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 most convenient program for creating tables, making calculations, subsequent formatting, writing reports with a large number of different 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:

How to Create a Table in Excel - 3+ Step by Step Methods
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 of necessary settings will appear on the side. 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 figure out 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

As a result, the 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:

Open the last tables you have worked with
Create a new book - this is the name of the working panel for creating tables in the program
Get acquainted with detailed information about all the features of the program - this is where you should start working
Select one of the already available default templates
Sign in with your account
Continue searching for templates on the Internet if none of the existing options are to your liking

Carefully study all the options of the program

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

Excel has become the most popular application for calculating and analyzing data precisely because of 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: it is advisable to study all the useful information about working with formulas before putting the program into practice, as well as 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