Make Excel itself create a new table

There are several ways How create a table in Excel . Tables may be different - pivot table, simple Excel table with and without formulas, etc. Let's look at several types of tables and ways to create them.
First way.
How to make a table in Excel.
To create the simplest table, you need to select required amount rows and columns and draw all borders with the "All borders" icon on the “Home” -> “Font” tab.
Then we make the font of the headings and columns bold, and the outer borders of the heading row are made bold. Thus, we have selected a row with column headings.
Now at the bottom of the table in the cell under the “Name” list we write “Total:”, and in the cells on the right we set the formulas for calculating the amount.Here it is more convenient and faster to use the “AutoSum” function.How to set the autosum, see the article "Bookmark Excel sheet"Formula"" .
We got the sum for each column. Let's highlight these numbers in bold.
Now let's find out the difference between the sum of the columns called "2" and "3" and the column called "1". To do this, enter the formula.For more information about formulas, see the article “ How to write a formula in Excel."
The result was a difference of “-614”.
Now we want to find out what percentage of the sum of column “2” is the sum of column “3”. Enter the formula (see the picture in the formula bar).
It turned out “74.55%”
This is how you can create simple tables. Numbers and text, headings, summary data can be highlighted with font color or cell color, italics, etc. etc. See the article "Excel format".
At daily work convenient in Excel spreadsheet install current time and number to the table. See "T" current date in Excel".
Second way.
Insert Excel table.
You can make a table using Excel functions"Insert". See article "Excel worksheet tab "Insert"" .
Third way.
You can select cells with data and press the key combination Ctrl+T (English letter T on any layout).
Fourth way.
Pivot table in Excel.
How to make a pivot table, see the article "Excel Pivot Tables".
In an Excel table you can do any forms , programs. See" How to make a form in Excel".
Using the table data, you can build a graph, diagram, etc. "How to make a graph in Excel."
You can configure the table so that, under certain conditions, the cells will be colored in different colors. For example, if the amount exceeds 10,000 rubles, the cell will be colored red. Read the article "Conditional Formatting in Excel".
You can insert rows and columns into a prepared Excel table. "How to add a row, column in Excel".
About these and other features Excel tables look in the sections of the site.
How to practically use an Excel table and Excel graph, see the article " Practical use graphics, Excel tables" .
How to practically create a table, see the example of a family budget table, read the article "Table "Home", family budget in Excel"".

How to create a table in Excel, instructions.

Do you want to know how to consistently earn money online from 500 rubles a day?
Download my free book
=>>

A program like Excel is designed to create tables and use it to conduct various functions. At first sight, this program may seem complicated and incomprehensible.

But in fact, if you give it a little time, you will see how easy it is to use. But first, it’s worth taking a closer look at the basic tools that you will need to create the simplest table in Excel.

In general, this program is a book in which, by default, three sheets are installed at once, and they, it would seem, already have cells, as required by the tabular version. But it's not that simple. We'll come back to this a little later.

So, at the top of the sheet are the working tools that, according to the developers, are used most often. In general, this is true.

But, if you need something different, then you can browse the categories for what you need. Here by default in the very first top line there is “Home” with all the necessary tools.

After it come the less frequently used categories “Insert”, “Page Layout” and others. Each of them contains additional functions, which may be useful to the user when creating tables or performing calculations.

Let's now take a closer look at how to create a table using Excel tools.

Options for creating a table

To create a table, you can use two options:

The first is to record all the original data and then mark the boundaries;
The second is that the boundaries are first marked and the table is given in the right type, and only then fill it out.

Honestly, I think that the second option is the most appropriate when working with this program. But whoever is comfortable. Now let's go in order.

How boundaries are marked

When faced with the question of how to create a table in Excel, you should first decide what will be indicated in it. That is, think over its heading and content.

These two criteria will allow you to decide on the number of columns needed to fill in all the required parameters. After that, we start working with boundaries.

In this case, the tools from the “Font” section, which are located on the “Home” tab, will come to our aid, or you can go to the “Insert” tab and select the table. A window will appear in which you should specify the size of the table, that is, from which cell to which cell.

Also, as an option, you can use the context menu, which opens when you click the right mouse button. In this case, it is convenient for anyone.

Marking boundaries

Now let's move on to marking the boundaries. If you pay attention, when you hover the mouse over the sheet, in the place where there are cells, it takes on an appearance that is not quite familiar to us, that is, it becomes a large white plus. It is thanks to this that the main range of the table can be highlighted.

To select the range required for the table, count the number of cells in the top and side parts. Then, click on the upper right corner of your future table, for example, on cell A1, it should light up with a black outline and stretch it to the desired letter on the top line and the desired number on the side.

Thus, letters and numbers will light up in a different color, and the cells along the borders will be highlighted in black. After this, click on right key mouse and from the context menu select the line “Format Cells” and go to the “Border” tab.

Here you click on the pictures where there is a caption “Internal” and “External”. Click "OK".

And while the “Lists” window is open in front of you, go to the “Alignment” tab and check the box next to “Wrap by words”, and then to “OK” so that you don’t have any difficulties in the future. So your table will appear in front of you.

The following method for creating borders in a table

There is one more, easy option- click on the top line with the tools located on the “Home” tab in the “Font” section, on the outlined square.

This is how the tool for working with boundaries is designated. WITH right side there is an inverted triangle next to it. Click on it and select the “All borders” line from the menu.

And I think the easiest option is to use the hotkey “Ctrl+1”. First, select the desired range, then simultaneously hold down “Ctrl+1”. Then immediately in the “Cell Format” window mark the boundaries “Internal” and “External”.

Then go to the “Alignment” tab and set “Word Wrap”.

Working with Columns

After defining the boundaries and creating the table, we move on to the main part - working with columns. Perhaps I’ll start with the fact that, first of all, it’s worth deciding on several tools into which, as the mouse moves across the cell, it can turn.

If you hover over top corner cells, in front of you the mouse will turn from a white plus into a black cross, which has arrows on the right and left.

Using this tool, you can increase or decrease the width of a column. If the column needs to be expanded not in length, but in width, then do the same.

Only in this case, move the mouse over desired number rows and click on a cell from the first column. After the cell lights up, move your mouse over the lower left corner and increase the line width.

By the way, if you hover the cursor over the lower right corner, right at the junction between the cells, the white plus changes to a small black cross. This tool allows you to select information from one cell and copy it using it for the entire column.

It is very convenient to use when using the same one for the entire column. For example, you have in the header such a criterion as “Cost” and you need to calculate it for each unit of goods.

To do this, we need to multiply the price per unit of goods by its quantity. This formula write in “Cost” and press “Enter”. Then we copy the formula for the entire column using a black cross and that’s it.

Another little advice, with , so as not to waste extra time clicking the mouse on new cell To fill it out each time, use the “Tab” key.

Just keep in mind that in this case the information will be filled in starting from the first cell horizontally. Once all the tabular data in one row is completed, click on the first cell of the next row and continue filling out.

Conclusion

Thus, if the question arises of how to create a table in Excel, then, as you can see, doing it is quite simple. And working with this program is not as difficult as it might seem at first glance.

The most important thing is to have the desire and patience to understand its basic functions, and then it will be much easier.

P.S. I am attaching a screenshot of my earnings in affiliate programs. And I remind you that anyone can earn money this way, even a beginner! The main thing is to do it correctly, which means learning from those who are already making money, that is, from Internet business professionals.

Get a list of proven Affiliate Programs in 2018 that pay money!

Download the checklist and valuable bonuses for free
=>>

In Part 2 of the Excel 2010 for Beginners series, you'll learn how to link table cells mathematical formulas, add rows and columns to already finished table, learn about the autofill feature and much more.

Introduction

In the first part of the “Excel 2010 for Beginners” series, we got acquainted with the very basics Excel programs, learning how to create regular tables in it. Strictly speaking, this is a simple matter and, of course, the capabilities of this program are much wider.

Main advantage spreadsheets is that individual cells with data can be linked together by mathematical formulas. That is, if the value of one of the interconnected cells changes, the data of the others will be recalculated automatically.

In this part, we will figure out what benefits such opportunities can bring using the example of the table of budget expenses that we have already created, for which we will have to learn how to create simple formulas. We will also get acquainted with the cell autofill function and learn how you can insert additional rows and columns into the table, as well as merge cells in it.

Perform basic arithmetic operations

In addition to creating regular tables, Excel can be used to perform arithmetic operations such as addition, subtraction, multiplication and division.

To perform calculations in any table cell, you need to create inside it the simplest formula, which must always begin with an equal sign (=). To specify mathematical operations within a formula, ordinary arithmetic operators are used:

For example, let's imagine that we need to add two numbers - “12” and “7”. Place the mouse cursor in any cell and type the following expression: “=12+7”. When you have finished entering, press the “Enter” key and the cell will display the calculation result - “19”.

To find out what a cell actually contains - a formula or a number - you need to select it and look at the formula bar - the area located immediately above the column names. In our case, it just displays the formula that we just entered.

After carrying out all the operations, pay attention to the result of dividing the numbers 12 by 7, which is not an integer (1.714286) and contains quite a lot of digits after the decimal point. In most cases, such precision is not required, and such long numbers will only clutter the table.

To fix this, select the cell with the number for which you want to change the number of decimal places after the decimal point and on the tab home in Group Number select team Decrease bit depth. Each click on this button removes one character.

To the left of the team Decrease bit depth there is a button that performs reverse operation- increases the number of decimal places to display more accurate values.

Drawing up formulas

Now let's return to the budget table we created in the first part of this series.

On this moment it records monthly personal expenses for specific items. For example, you can find out how much was spent on food in February or on car maintenance in March. But the total monthly expenses are not indicated here, although these indicators are the most important for many. Let's correct this situation by adding the line “Monthly expenses” at the bottom of the table and calculate its values.

To calculate the total expense for January in cell B7, you can write the following expression: “=18250+5100+6250+2500+3300” and press Enter, after which you will see the result of the calculation. This is an example application simplest formula, the compilation of which is no different from calculations on a calculator. Unless the equal sign is placed at the beginning of the expression, and not at the end.

Now imagine that you made a mistake when indicating the values of one or more expense items. In this case, you will have to adjust not only the data in the cells indicating expenses, but also the formula for calculating total expenses. Of course, this is very inconvenient and therefore in Excel, when creating formulas, not specific numerical values are often used, but cell addresses and ranges.

With this in mind, let's change our formula for calculating total monthly expenses.

In cell B7, enter an equal sign (=) and... Instead of manually entering the value of cell B2, left-click on it. After this, a dotted highlight frame will appear around the cell, which indicates that its value is included in the formula. Now enter the “+” sign and click on cell B3. Next, do the same with cells B4, B5 and B6, and then press the ENTER key, after which the same amount value will appear as in the first case.

Select cell B7 again and look at the formula bar. It can be seen that instead of numbers - cell values, the formula contains their addresses. This is very important point, since we just built a formula not from specific numbers, but from cell values that can change over time. For example, if you now change the amount of expenses for purchasing things in January, then the entire monthly total expense will be recalculated automatically. Give it a try.

Now let's assume that you need to sum not five values, as in our example, but one hundred or two hundred. As you understand, using the above method of constructing formulas in this case is very inconvenient. In this case it is better to use special button“AutoSum”, which allows you to calculate the sum of several cells within one column or row. In Excel, you can calculate not only the sums of columns, but also rows, so we use it to calculate, for example, total food expenses for six months.

Place the cursor on an empty cell on the side the desired line(in our case this is H2). Then click the button Sum on the bookmark home in Group Editing. Now, let's go back to the table and see what happened.

In the cell we selected, a formula appears with an interval of cells whose values need to be summed. At the same time, the dotted highlight frame appeared again. Only this time it frames not just one cell, but the entire range of cells, the sum of which needs to be calculated.

Now let's look at the formula itself. As before, the equal sign comes first, but this time it is followed by function"SUM" - in advance specific formula, which will add the values of the specified cells. Immediately after the function there are brackets located around the addresses of the cells whose values need to be summed, called formula argument. Please note that the formula does not indicate all the addresses of the cells being summed, but only the first and last ones. The colon between them indicates that range cells from B2 to G2.

After pressing Enter, the result will appear in the selected cell, but that’s all the button can do Sum don't end. Click on the arrow next to it and a list will open containing functions for calculating average values (Average), the number of data entered (Number), maximum (Maximum) and minimum (Minimum) values.

So, in our table we calculated the total expenses for January and the total expenses on food for six months. At the same time they did it with two different ways- first using cell addresses in the formula, and then using functions and ranges. Now, it's time to finish the calculations for the remaining cells, calculating the total costs for the remaining months and expense items.

Autofill

To calculate the remaining amounts, we will use one remarkable feature of Excel, which is the ability to automate the process of filling cells with systematic data.

Sometimes in Excel you have to enter similar data of the same type into a certain sequence, such as days of the week, dates, or line numbers. Remember, in the first part of this series, in the table header, we entered the name of the month in each column separately? In fact, it was completely unnecessary to enter this entire list manually, since the application can do it for you in many cases.

Let's erase all the month names in the header of our table, except for the first one. Now select the cell labeled “January” and move the mouse pointer to its lower right corner so that it takes the form of a cross called fill marker. Clamp left button mouse and drag it to the right.

A tooltip will appear on the screen, telling you the value the program is about to insert into the next cell. In our case, this is “February”. As you move the marker down, it will change to the names of other months, which will help you figure out where to stop. Once the button is released, the list will populate automatically.

Of course, Excel does not always correctly “understand” how to fill in subsequent cells, since the sequences can be quite diverse. Let's imagine that we need to fill a string with even numbers numerical values: 2, 4, 6, 8 and so on. If we enter the number “2” and try to move the autofill marker to the right, it turns out that the program offers to insert the value “2” again both in the next and in other cells.

In this case, the application needs to provide a little more data. To do this, in the next cell on the right, enter the number “4”. Now select both filled cells and again move the cursor to the lower right corner of the selection area so that it takes the form of a selection marker. Moving the marker down, we see that the program has now understood our sequence and is showing the required values in the tooltips.

Thus, for complex sequences, before using autofill, you need to fill in several cells yourself so that Excel can correctly determine general algorithm calculating their values.

Now let's apply this useful opportunity programs to our table, so as not to enter formulas manually for the remaining cells. First, select the cell with the amount already calculated (B7).

Now “hook” the cursor on the lower right corner of the square and drag the marker to the right to cell G7. After you release the key, the application itself will copy the formula into the marked cells, while automatically changing the addresses of the cells contained in the expression, substituting the correct values.

Moreover, if the marker is moved to the right, as in our case, or down, then the cells will be filled in ascending order, and to the left or up - in descending order.

There is also a way to fill a row using tape. Let's use it to calculate the cost amounts for all expense items (column H).

We select the range that should be filled, starting from the cell with the data already entered. Then on the tab home in Group Editing press the button Fill and select the filling direction.

Add rows, columns, and merge cells

To get more practice in writing formulas, let's expand our table and at the same time learn a few basic formatting operations. For example, let’s add income items to the expenditure side, and then calculate possible budget savings.

Let's assume that the revenue part of the table will be located on top of the expenditure part. To do this we will have to insert several additional lines. As always, this can be done in two ways: using commands on the ribbon or in the context menu, which is faster and easier.

Click any cell in the second row right click mouse and select the command in the menu that opens Insert…, and then in the window - Add line.

After inserting a row, pay attention to the fact that by default it is inserted above the selected row and has the format (cell background color, size settings, text color, etc.) of the row located above it.

If you need to change the default formatting, immediately after pasting, click the button Add Options icon that automatically appears near the lower right corner of the selected cell and select the option you want.

Using a similar method, you can insert columns into the table that will be placed to the left of the selected one and individual cells.

By the way, if in the end the row or column after insertion ended up on in an unnecessary place, they can be easily removed. Right-click on any cell belonging to the object to be deleted and select the command from the menu that opens Delete. Finally, indicate what exactly you want to delete: a row, a column, or an individual cell.

On the ribbon, you can use the button for adding operations Insert located in the group Cells on the bookmark home, and to delete, the command of the same name in the same group.

In our case, we need to insert five new rows into top part tables immediately after the header. To do this, you can repeat the adding operation several times, or you can, having completed it once, use the “F4” key, which repeats the most recent operation.

As a result, after inserting five horizontal rows into the top part of the table, we bring it to the following form:

We left the white unformatted rows in the table on purpose to separate the income, expenditure and total parts from each other by writing appropriate headings in them. But before we do that, we will learn one more operation in Excel - merging cells.

When several adjacent cells are combined, one is formed, which can occupy several columns or rows at once. In this case, the name of the merged cell becomes the address of the uppermost cell of the merged range. At any time, you can split a merged cell again, but you cannot split a cell that has never been merged.

When merging cells, only the data in the top left is saved, but the data in all other merged cells will be deleted. Remember this and do the merging first, and only then enter the information.

Let's return to our table. In order to write headings in white lines, we need only one cell, while now they consist of eight. Let's fix this. Select all eight cells of the second row of the table and on the tab home in Group Alignment click on the button Combine and place in the center.

After executing the command, all selected cells in the row will be combined into one large cell.

Next to the merge button there is an arrow, clicking on which will bring up a menu with additional commands, allowing you to: merge cells without central alignment, merge entire groups of cells horizontally and vertically, and also cancel the merge.

After adding headers, as well as filling out the lines: salary, bonuses and monthly income, our table began to look like this:

Conclusion

In conclusion, let's calculate the last line of our table, using the knowledge gained in this article, the cell values of which will be calculated using the following formula. In the first month the balance will consist of usual difference between the income received for the month and the total expenses in it. But in the second month we will add the balance of the first to this difference, since we are calculating savings. Calculations for subsequent months will be carried out according to the same scheme - savings for the previous period will be added to the current monthly balance.

Now let's translate these calculations into formulas that Excel can understand. For January (cell B14) the formula is very simple and will look like this: “=B5-B12”. But for cell C14 (February), the expression can be written in two different ways: “=(B5-B12)+(C5-C12)” or “=B14+C5-C12”. In the first case, we again calculate the balance of the previous month and then add the balance of the current month to it, and in the second, the already calculated result for the previous month is included in the formula. Of course, using the second option to construct the formula in our case is much preferable. After all, if you follow the logic of the first option, then in the expression for the March calculation there will already be 6 cell addresses, in April - 8, in May - 10, and so on, and when using the second option there will always be three of them.

To fill the remaining cells from D14 to G14, we can use their automatic filling, just like we did in the case of amounts.

By the way, to check the value of the final savings for June, located in cell G14, in cell H14 you can display the difference between total amount monthly income (H5) and monthly expenses (H12). As you understand, they should be equal.

As can be seen from the latest calculations, in formulas you can use not only the addresses of adjacent cells, but also any others, regardless of their location in the document or belonging to a particular table. Moreover, you have the right to link cells located on different sheets document and even in different books, but we will talk about this in the next publication.

And here is our final table with the calculations performed:

Now, if you wish, you can continue filling it out yourself, inserting both additional items of expenses or income (rows) and adding new months (columns).

In the next article we will talk in more detail about functions, understand the concept of relative and absolute links, we’ll definitely master a few more useful elements editing tables and much more.

For any office worker or student, and indeed every person associated with computers in Everyday life, it is absolutely necessary to be able to handle office programs. One of these is Microsoft Office Excel. First, let's look at how to make a table in Excel.

Basics

First of all, the user must realize that creating a table in Excel is a simple matter, because software environment this office application It was originally created specifically for working with tables.

After launching the application, the user appears Workspace, consisting of many cells. That is, you don’t have to think about how to make a table in Excel with my own hands because it's already ready. All the user needs to do is to outline the boundaries and cells in which he will enter data.

Beginner users naturally have a question about what to do with cells when printing. In this case, you need to understand that only those that you have outlined are printed; they will ultimately be your table.

Create the first table

To do this, select the area we need by holding down the left mouse button. Let's say we need to calculate a family's income for a month that has 30 days. We need 3 columns and 32 rows. Select cells A1-C1 and stretch them down to A32-C32. Then on the panel we find the border button and fill the entire area so that we get a table in a grid. After this, we proceed to filling out the cells. In the first line we fill in the table title. Let's title the first column "Date", the second - "Incoming", the third - "Expense". After this, we will begin filling in the table values.

It should be noted that table and cell borders can be colored in different ways. The program provides the user various options border design. This can be done in the following way. Having selected the required area, right-click on it and select “Format Cells”. Then go to the "Borders" tab. There is enough clear interface with a sample. You can experiment with the choice of coloring for your table borders, as well as the line type.

There is also a slightly different way to get to this menu. To do this, click on the “Format” button at the top of the screen on the control panel. Select "Cells" from the drop-down list. The exact same window will appear in front of you as in the previous version.

Filling out the table

Having figured out how to make a table in Excel, let's start filling it out. To facilitate this procedure, there is an auto-fill cell function. It is clear that in a real situation, the table that we created above will be filled line by line over the course of a month. However, in our case, it is better for you to fill in the cells of the “Income” and “Expense” columns yourself.

To fill in the "Date" column, we will use the autocomplete function. In cell A2 we will indicate the value “1”, and in cell A3 we will assign the value “2”. After this, select these two cells with the mouse and move the pointer to the lower right corner of the selected area. A crosshair will appear. Holding the left mouse button, stretch this area to cell A31. Thus, we have a data table in Excel.

If you did everything correctly, then you should have a column with numbers from 1 to 30. As you understand, Excel automatically determined the arithmetic principle by which you were going to fill out the column. If you entered the numbers 1 and 3 in sequence, then the program would give you only odd numbers.

Calculations

Tables in Excel serve not only to store data, but also to calculate values based on the entered data. Now let's figure out how to make a table in Excel with formulas.

After filling out the table, we are left with an empty bottom row. Here, in cell A32, we enter the word “Total”. As you understand, here we will calculate the total income and expenses of the family for the month. So that we do not have to manually calculate the total using a calculator, Excel has many built-in various functions and formulas.

In our situation, to calculate the total value, we will need one of the simplest summation functions. There are two ways to add the values in columns.

Fold each cell separately. To do this, put the "=" sign in cell B32, then click on the cell we need. It will turn a different color, and its coordinates will be added to the formula bar. For us it will be "A2". Then, again, put “+” in the formula bar and select the next cell again, its coordinates will also be added to the formula bar. This procedure can be repeated many times. It can be used in cases where the values needed for the formula are in different parts tables. When you have finished selecting all the cells, press "Enter".
Another, simpler method may be useful if you need to fold several adjacent cells. In this case, you can use the "SUM()" function. To do this, in cell B32 we also put an equal sign, then in Russian capital letters we write “SUM(” - with an open bracket. Select the area we need and close the bracket. Then press “Enter”. Done, Excel has calculated the sum of all cells for you.

To calculate the sum of the next column, simply select cell B32 and, as with autofill, drag it to cell C32. The program will automatically shift all values one cell to the right and calculate the next column.

Summary

In some cases, Excel PivotTables may come in handy. They are designed to combine data from several tables. To create one, select your table, and then in the control panel select “Data” - “Pivot Table”. After a series of dialog boxes to help you customize the table, a field with three elements will appear.

In the row fields, drag one column that you consider to be the main one and on which you want to make a selection. In the data field, drag the column with the data that needs to be calculated.

A field designed for dragging columns is used to sort data by any criterion. Here you can also move the necessary condition/filter for data selection.

Conclusion

After reading this article, you should have learned how to create and work with the simplest types of tables, as well as perform operations with data. We briefly looked at creating pivot table. This should help you get comfortable in Microsoft environment Office and feel more confident in Excel.

Do you want to know how to make a table in Microsoft program Office Excel? In fact, this question cannot be answered unambiguously. Moreover, there are dozens, if not hundreds of answers to this question - because the Excel spreadsheet editor contains incredibly extensive functionality. According to statistics, inexperienced user, of which the majority do not use approximately 90-95% of the program’s capabilities.

A universal way to create a table in Excel

Surely many users, when creating a table, mean setting some boundaries of elements or frames. Well, we have it in store for you universal method, which works equally well not only on old versions of the program, but also on fresh ones. Well, to make a table you have to do the following actions:
As you understand from all of the above, this instruction universal and suitable for any version of Excel. But even this is not all - you can always play with the settings in the fifth paragraph to modify the tables, borders, their color and many other settings. All in your hands.

Formatting a table

You probably remember that at the very beginning we mentioned a huge number options for creating tables in Excel, right?! We hasten to please you, at last we have saved an extremely simple method for giving them a unique appearance. To do this you will need to use standard types formatting tables. So let's get started:

Well, now you know how to make tables in Excel, how to give them specific formatting and styles. I would like to add one more thing: do not forget to experiment, the program has many interesting functions!