Making a table in excel. How rows and columns stand out

Excel is a multifunctional application, the functionality of which clearly exceeds the capabilities of any other. Economists, accountants, etc. cannot do without this application. specialists. Excel allows you to draw all kinds of graphs, tables, etc., which greatly simplifies life.

Nevertheless, despite the huge number of functions, tables are still the most basic function most used by users. It is because of the need to draw a beautiful table, say, for a presentation or for work, that many people download Excel and, of course, do it right. After all, this program is ideally sharpened for performing such actions, and tables can have all kinds of shapes and sizes and, moreover, it is very simple! But let's talk about everything in order. So, further we will talk about how to make tables in excel.

How to draw a table in Excel

How to work with a table

Now you can adjust the table itself, change / add data, delete / add rows and columns, thereby expanding the boundaries of the table, etc. So, for example, if you hover the cursor over the bottom corner of the table and drag it, it will begin to grow, just as you can achieve its reduction.

The table can be made slightly larger. To do this, select any line or column and right-click on it. A menu will drop out, in which you need to select the "Insert" item, and then - any of the listed lines, for example, "Table columns on the left". In this way, your plate can become overgrown with new elements. Also according to the table you can. I hope that the question of how to work with tables in excel has become clearer for you!

Of course, it will not be possible to consider all the nuances, there are too many of them. But the most important thing, so to speak, the basics, you now know, and then it's up to the little thing - to draw the table yourself!

Video to help

To simplify the management of logically related data, EXCEL 2007 introduces a new table format. Using tables in EXCEL 2007 format reduces the likelihood of incorrect data entry, simplifies insertion and deletion of rows and columns, and simplifies table formatting.

Source table

Suppose you have a regular table (range of cells) consisting of 6 columns.

In the column № (position number), starting from the second line of the table, there is a formula = A2 + 1, which allows automatic. To speed up entering values in a column Unit.(unit) with created.

In the column Price introduced a formula for calculating the cost of goods (price * quantity) = E3 * D3. The numeric values in the column are formatted to display the digit separators.

Actions with a regular table

To show the benefits of EXCEL 2007 tables, let's first do some basic work with a regular table.

First, let's add a new row to the table, i.e. fill in the data line 4 of the sheet:

fill in the values of the columns without formulas ( Name, Price, Quantity);
in columns Price and № use to copy the formulas into the cells below;
to make it work on a new line, in the column Unit... copy the format to the cell below. To do this, select the cell C3 , copy it to Clipboard and, by highlighting the cell below, through the menu Home / Clipboard / Paste / Paste Special / Value Conditions insert Drop-down list(or, as in the previous step, copy the value from the cell C3 v C4 thereby copying the rule. Then you must enter the Units value on a new line).

Of course, you can copy the formulas and cell formats down several lines in advance - this will speed up the filling of the table.

Now let's look at the same steps, but in a table in EXCEL 2007 format.

Creation of tables in EXCEL 2007 format

Select any cell of the above table and select the menu item Insert / Tables / Table.

EXCEL will automatically detect that our table has column headings. If you uncheck the box Table with headers, then headers will be created for each column Column1, Column2, …

ADVICE:
Avoid headings in number formats (such as "2009") and references to them. When the table is created, they will be converted to text format. Formulas that use numeric headers as arguments may stop working.

After clicking OK:

the interlaced style will be automatically applied to the table;
in the header will be enabled (to disable it, select any cell in the table and press CTRL + SHIFT + L, press it again to turn on the filter);
a special tab in the menu for working with tables will become available ( Working with tables / Constructor), the tab is active only when any cell of the table is selected;
the table will be assigned, which can be viewed through the table constructor or through Name manager (Formulas / Defined Names / Name Manager) .

ADVICE :
Before converting a table to EXCEL 2007 format, make sure that the source table is properly structured. The article sets out the basic requirements for the "correct" structure of the table.

Dropping tables in EXCEL 2007 format

To delete a table along with data, you need to select any header in the table, click CTRL+ A, then the key DELETE(either select any cell with data, double-click CTRL+ A, then the key DELETE). Another way to delete a table is to delete from the sheet all rows or columns containing table cells (you can delete rows, for example, by highlighting the required rows by the headers, by calling the context menu with the right mouse button and selecting Delete).

To preserve the data in the table, you can convert it to a regular range. To do this, select any cell in the table (The Working with Tables tab will be displayed, containing the Design tab) and through the menu Working with tables / Constructor / Service / Convert to Range convert it to normal range. The table formatting will remain. If you also want to remove formatting, clear the table style ( Working with tables / Design / Table styles / Clear).

Adding new lines

Now let's do the same with the table in EXCEL 2007 format, which we did earlier with the usual range.

Let's start by filling from a column Name(first column without formulas). After entering the value, a new row will be automatically added to the table.

As you can see from the picture above, the table formatting will automatically propagate to the new line. Also, formulas in columns will be copied to the row. Price and №. In the column Unit... will become available with a list of units.

To add new rows in the middle of the table, select any cell in the table above which you want to insert a new row. Call the context menu with the right mouse button, select the Insert menu item (with an arrow), then the Table Rows item above.

Deleting Rows

Select one or more cells in the table rows that you want to delete.
Right-click, select Delete from the context menu, and then select Table Rows. Only the table row will be deleted, not the entire sheet row.
Columns can be deleted in the same way.

Totals data in table

Click anywhere in the table. On the Design tab, in the Table Style Options group, select the Totals Row check box.

The totals row appears in the last row of the table, and the word appears in the leftmost cell Outcome.

In the total row, click the cell in the column for which you want to calculate the total value, and then click the drop-down arrow that appears. From the drop-down list, select the function that will be used to calculate the total.
The formulas that you can use in the totals row are not limited to formulas in the list. You can enter any formula you want in any cell in the totals row.
After creating a row of totals, it is difficult to add new rows to the table. rows are no longer added automatically when new values are added (see section). But there is nothing to worry about: the totals can be turned off / on through the menu.

Naming Tables

When creating tables in EXCEL 2007 format, EXCEL assigns tables automatically: Table 1, Table 2 and so on, but these names can be changed (via the table constructor:) to make them more expressive.

The table name cannot be deleted (for example, through Name manager). As long as the table exists, its name will be determined.

Structured references (references to fields and table values in formulas)

Now we will create a formula in which one of the table columns in EXCEL 2007 format is specified as arguments (we will create the formula outside the row results).

Introduce into the cell H1 part of the formula: = SUM (
Select the range with the mouse F 2: F 4 (whole column Price No title)

But, instead of the formula = SUM (F2: F4, we will see = SUM (Table1 [Cost]

This is a structured link. In this case, it is a reference to an entire column. If new rows are added to the table, then the formula = SUM (Table1 [Cost]) will return the correct result given the newline value. Table 1 Is the name of the table ( Working with tables / Constructor / Properties / Table name).

Let's consider another example of summing a column of a table through it. In a cell H2 enter = SUM (T (the letter T is the first letter of the table name). EXCEL will offer you to choose, starting with "T", a function or a name defined in this book (including table names).

Double-clicking on the table name, the formula will take the form = SUM (Table1. Now we enter the symbol [(open square bracket). EXCEL after entering = SUM (Table1 [will offer to select a specific field of the table. Select the field Price by double clicking on it.

Into the formula = SUM (Table1 [Cost enter the character] (closing square bracket) and press the key ENTER... As a result, we get the sum for the column Price.

Below are other types of structured links:
Column header reference: = Table1 [[#Headers]; [Cost]]
Referencing a value on the same line = Table1 [[# This line]; [Cost]]

Copy formulas with structured links

Let there be a table with columns Price and Price with VAT... Suppose you want to calculate the total cost and the total cost with VAT to the right of the table.

First, let's calculate the total cost using the formula = SUM (Table1 [Cost])... Let's compose the formula as shown in the previous section.

Now let's select the cell J2 and press the key combination CTRL + R(copy the formula from the cell to the left). Unlike we get the formula = SUM (Table1 [Cost]), but not = SUM (Table1 [Cost with VAT])... In this case, the structured link is like

Now let's look at a similar table and make a small one based on its data to calculate the total cost for each fruit name.

In the first line of the report (a range of cells I1: K2 ) contains the names of fruits (without repetitions), and in the second line, in the cell I2 formula = SUMIF (Table1 [Name]; I1; Table1 [Cost]) to find the total value of the fruit Apples... When copying a formula using to a cell J2 (to find the total value of the fruit Oranges) the formula will become incorrect = SUMIF (Table1 [Unit]; J1; Table1 [Cost with VAT])(see above), copying using a combination of keys CTRL + R solves this problem. But, if there are more names, say 20, then how to quickly copy the formula to other cells? To do this, select the required cells (including the cell with the formula) and put the cursor in (see the figure below), then press the key combination CTRL + ENTER... The formula will be copied correctly.

Table Styles

For tables created in EXCEL 2007 format ( Insert / Tables / Table) it is possible to use different styles to give tables a certain look, including with. Select any cell in the table, then press Constructor / Table Styles and choose the style that suits you.

Microsoft program Excel is a very powerful tool thanks to which you can create large tables with beautiful design and an abundance of different formulas. Working with information is made easier precisely because of the dynamics that are missing in Word.

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. This may seem daunting to beginners at first. But in fact, with ongoing work in the Excel program, you will become a professional and will be able to help others.

The training plan will be simple:

first consider different methods creating tables;
then we do the design so that the information is as clear and understandable as possible.

This method is the simplest. This is done as follows.

Opening 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 a regular elementary tablet.

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 the "Draw Grid" option.

Immediately after that, you will change appearance cursor.

Left-click and drag the pointer to a different position. This will draw a new mesh. The upper left corner is the starting 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, you need to do the following.

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

Pay attention to what we are prompted about the hotkeys. In the future, for automatic creation, you can use the Ctrl + T key combination.

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.

This will create a nice table with alternating lines.

To change the name of a column, just click on it. After that, you can start editing right 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 take the following steps.

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

Now go to the main menu "Insert". Next, we select 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, you will have a new sidebar where you need to customize the future table.

At this stage, you need to transfer the fields to the required categories. The columns will be months, the rows will be the assignment of 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 its 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 interesting for data analysis yourself.

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

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 example case, we got the amount total costs, excluding months.

Out-of-the-box templates in Excel 2016

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

When you start Excel, you are offered the following options:

open the last files you worked with before;
create a new blank workbook;
watch the tutorial with detailed information about the capabilities of this software;
choose some ready-made default template;
continue searching on the Internet if you do not like any of the proposed designs;
enter 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 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, you get ready-made version very large and complex table.

Registration

Appearance is one of critical parameters... It is very important to focus on some elements. For example, heading, heading, and so on. It all depends on the specific case.

Let's take a quick look at the basic cell manipulation.

Creating a header

Let's use a simple table as an example.

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

Select the line that appears and click on the "Combine cells" menu item.

Next, write any title.

Changing the height of elements

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

As a result, the line height will be larger.

Text alignment

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

Click on the "In the middle" button and get the desired result.

Now the title looks much better.

Style change

Or use ready-made 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;
strings;
columns;
whole sheet.

Removing items

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

Fill cells

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

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

Item format

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

As a result, you will be able to:

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

Content format

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

Thanks to this tool, you can:

change the format of the displayed data;
specify alignment;

choose any font;

change the borders of the table;

"Play" with filling;

establish protection.

Using formulas in tables

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

In addition, we recommend that you familiarize yourself with the description of all functions.

Let's consider the simplest operation - cell multiplication.

First, let's prepare a field for experiments.

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

Enter the following command there.

= C3 * D3

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 auto-substitution, the formula will enter all cells.

The values in the "Total cost" column will depend on the "Quantity" and "Cost of 1 kg" fields. This is 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, we highlight the values. Then click on the "AutoSum" button, which is located on the "Home" tab.

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

Using graphics

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

Selecting an empty item. Go to the "Insert" tab. We select the "Illustrations" section. We click on the item "Pictures".

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

The result will not disappoint you. It looks very nice (depending on the selected picture).

Export to Word

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

Select a data area.

Press the hotkeys Ctrl + C.
Open the document
Now we use the buttons Ctrl + V.
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.

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

Printing methods

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

Such printouts are inconvenient to read. It is much better when all the information is on one sheet and does not go anywhere beyond the borders. In this regard, the developers from Microsoft have added a document viewer function. Let's take a look at how to use this.

We open the document. He looks quite ordinary.

Then press the hotkeys Ctrl + P. In the window that appears, we see that the information does not fit on one sheet. The "Total cost" column has disappeared from us. In addition, below they tell us that 2 pages will be used for printing.

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

To cancel, click hotkey Esc. As a result, a vertical dashed line appears, which shows the boundaries of the print.

You can increase the print space as follows.

The first step is to reduce the margins. To do this, go to the "Page Layout" tab. Click on the "Fields" button and select the "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.

It is necessary 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 fits on one sheet.

Difference between Microsoft product versions

It should be understood that the 2003 Excel is outdated for a long time. There is a huge amount missing modern functions and opportunities. In addition, the appearance 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 in 2016 versions, everything is much cooler.

Many menu items are located in different sections. Some of them have changed their name altogether. For example, the "Formulas" we are used to, back in 2003 were called "Functions". And they didn't take up that much space.

Now there is a whole tab for them.

Limitations and capabilities of different versions

On the official site Microsoft you can find an online help that lists all specifications created books.

An example of the most basic parameters.

The list is quite long. Therefore, it is worth following the link and familiarizing yourself with the rest.

Note that the 2003 version is not even being considered as it is no longer supported.

But in some budgetary organizations this office suite is still in use today.

Conclusion

This article covered different ways creating and presenting tables. Special attention has been given to giving a beautiful appearance. Do not overdo it in this regard, since the bright colors and variety of fonts will scare away the user who is trying to familiarize himself with the contents of the table.

Video instruction

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

If you've never used a spreadsheet before to create documents, we recommend reading our Excel guide for dummies.

After that, you can create your first spreadsheet with tables, graphs, math formulas and formatting.

Detailed information about the basic functions and capabilities of the MS Excel spreadsheet processor. Description of the main elements of the document and instructions for working with them in our material.

Working with cells. Padding and formatting

Before you start any specific steps, you need to understand the basic element of any document in Excel. An Excel file consists of one or more sheets divided into small cells.

A cell is the basic component of any Excel report, table or graph. Each cell contains one block of information. It can be a number, date, currency, unit of measure, or other data format.

To fill in a cell, you just need to click on it with the pointer and enter the required information. To edit a previously filled cell, double-click on it.

Rice. 1 - an example of filling cells

Each cell on the sheet has its own unique address. Thus, you can carry out calculations or other operations with it. When you click on a cell, a field with its address, name and formula will appear at the top of the window (if the cell is involved in any calculations).

Let's select the cell "Share of shares". Its location address is A3. This information is indicated in the properties panel that opens. We can also see the content. This cell does not have formulas, so they are not shown.

More cell properties and functions that can be used in relation to it are available in the context menu. Click on the cell with the right key of the manipulator. A menu will open, with which you can format the cell, analyze the content, assign a different value, and other actions.

Rice. 2 - context menu of a cell and its main properties

Sorting data

Often users are faced with the task of sorting data on a sheet in Excel. This feature helps you quickly select and view only the data you want from the entire table.

Before you is an already completed table (we will figure out how to create it later in the article). Imagine that you want to sort the data for January in ascending order. How would you do it? A banal retyping of a table is an extra work, and besides, if it is voluminous, no one will do it.

Excel has a dedicated function for sorting. The user is only required to:

Select a table or block of information;
Open the "Data" tab;
Click on the "Sort" icon;

Rice. 3 - "Data" tab

In the window that opens, select the column of the table, over which we will carry out actions (January).
Next, the sorting type (we are grouping by value) and, finally, the order is ascending.
Confirm the action by clicking on "OK".

Rice. 4 - setting sorting parameters

The data will be automatically sorted:

Rice. 5 - the result of sorting the numbers in the column "January"

Similarly, you can sort by color, font and other parameters.

Mathematical calculations

The main advantage of Excel is the ability to automatically carry out calculations in the process of filling out the table. For example, we have two cells with values 2 and 17. How to enter their result into the third cell without doing the calculations yourself?

To do this, you need to click on the third cell, in which the final result of the calculations will be entered. Then click on the function icon f (x) as shown in the figure below. In the window that opens, select the action you want to apply. SUM is the sum, AVERAGE is the average, and so on. Full list functions and their names in the Excel editor can be found on the official website of Microsoft.

We need to find the sum of two cells, so click on "SUM".

Rice. 6 - selection of the "SUM" function

The function arguments window has two fields: "Number 1" and "Number 2". Select the first field and click on the cell with the number "2". Its address will be written to the argument string. Click on "Number 2" and click on the cell with the number "17". Then confirm the action and close the window. If you need to perform mathematical operations with three or big amount cells, just continue to enter the values of the arguments in the fields "Number 3", "Number 4" and so on.

If in the future the value of the summed cells changes, their sum will be updated automatically.

Rice. 7 - the result of the calculations

Creating tables

Any data can be stored in Excel tables. With the help of the quick setup and formatting function, in the editor it is very easy to organize a personal budget control system, a list of expenses, digital data for reporting, and so on.

Tables in Excel take precedence over similar options in Word and other office programs. Here you have the opportunity to create a table of any dimension. The data is filled in easily. There is a function panel for editing content. In addition, the ready-made table can be integrated into docx file using the usual copy-paste function.

To create a table, follow the instructions:

Click the Insert tab. On the left side of the options panel, select Table. If you need to summarize any data, select the "Pivot Table" item;
Using the mouse, select a place on the sheet that will be allocated for the table. And also you can enter the location of the data in the element creation window;
Click OK to confirm the action.

Rice. 8 - creating a standard table

To format the appearance of the resulting plate, open the contents of the constructor and in the "Style" field, click on the template you like. If desired, you can create your own view with another colors and selection of cells.

Rice. 9 - formatting the table

The result of filling the table with data:

Rice. 10 - filled table

For each cell in the table, you can also customize the data type, formatting, and information display mode. The constructor window contains all the necessary options for further configuration of the plate, based on your requirements.

Adding graphs / charts

To build a chart or graph, a ready-made plate is required, because the graphic data will be based precisely on information taken from individual lines or cells.

To create a chart / graph, you need:

Select the table completely. If you need to create a graphic element only to display the data of certain cells, select only them;
Open the insert tab;
In the recommended charts box, select the icon that you think is the best way will visually describe the tabular information. In our case, this is a 3-D pie chart. Move the pointer to the icon and select the appearance of the element;

Likewise, you can create scatter plots, line charts, and table element dependencies. All the resulting graphic elements can also be added to text documents Word.

There are many other functions in the Excel spreadsheet editor, however, for initial work the techniques described in this article will be enough. In the process of creating a document, many users independently master the more advanced options. This is due to a user-friendly and intuitive interface. latest versions programs.

Thematic videos:

Processing tables is the main task of Microsoft Excel. Knowing how to create tables is fundamental to working with this application. Therefore, without mastering this skill, further progress in learning to work in the program is impossible. Let's find out how to create a table in Microsoft Excel.

First of all, we can fill the cells of the worksheet with data that will later be in the table. We do it.

Then, we can draw the boundaries of a range of cells, which we then turn into a full-fledged table. Select the data range. In the "Home" tab, click on the "Borders" button, which is located in the "Font" settings block. From the list that opens, select the item "All borders".

We were able to draw a table, but it is perceived as a table only visually. The Microsoft Excel program perceives it only as a data range, and accordingly, it will not process it as a table, but as a data range.

Converting a data range to a table

Now, we need to convert the data range into a full table. To do this, go to the "Insert" tab. Select the range of cells with data, and click on the "Table" button.

After that, a window appears, in which the coordinates of the previously selected range are indicated. If the selection was correct, then nothing needs to be edited. In addition, as we can see, in the same window opposite the inscription "Table with headers" there is a check mark. Since we really have a table with headers, we leave this checkbox, but in cases where there are no headers, the checkbox must be unchecked. Click on the "OK" button.

As you can see, although it is not at all difficult to create a table, the creation procedure is not limited only to the selection of borders. In order for the program to perceive a range of data as a table, it must be formatted accordingly, as described above.