Talismans 

How can you create an excel table. Differences between different versions of Excel

To compile large lists of related data, it is most convenient to use tables. In tables, as a rule, columns and rows are labeled, which makes it very easy to find the required value in the list. You can make tables in many applications, but it is best to use a special program for this, for example, Excel. Next we will look at how to make a table in Excel.

In fact, the Excel program is already one large table, and to make a table in Excel, we just need to enter the necessary data and somehow delimit it.

As a rule, a table should have a header, thanks to which it will be clear which column contains which data, and this is the best place to start creating tables in Excel. To create a header, simply select any cells in a new Excel document and label them, thus identifying the data columns used in the table.

Now you can enter the necessary data under the signed columns.


Now our data is presented in the form of a table, but on the worksheet it all looks like an ordered arrangement of values. To finally create a table in Excel, you need to circle it, i.e. make a frame and draw dividing lines between the columns.


Now it looks like a table, but there is still some work to be done. The table can be given a more attractive appearance by changing the sizes of the columns and rows, also giving them different colors, and can be more conveniently positioned on the sheet, and the entered data can be formatted as a table. Using this formatting, the table design will be given a certain style and it will also be possible to give it a unique name.

On the tab "Home" find the sector "Styles", and clicking on the item "Format as table" choose the design style you like, first selecting any cell of our table.


Next, Excel will select our entire table and offer to confirm its location and, if necessary, correct the address.


Now we have a real table in Excel. When you select such a table, a tab appears "Constructor", where you can set the table name and make additional settings. The previously installed black table borders will have to be removed, otherwise they will overlap the lines of this design style.


We get the same result, but using the default style, when using the item "Table" on the tab "Insert".

Although Excel looks like a large table in which you can highlight the outer boundaries, the question of how to create a table in Excel remains a popular one. Today, a table in Excel can be built in several ways, by making it free or specifying exact dimensions.

How to create a simple table in Excel?

To create a simple table with certain parameters, you should follow these steps:

  • Open the Excel file and select the area that should become a table.
  • Go to the “Insert” tab and click “Insert table” or “Table” (depending on the version of the program).

  • A small window will open indicating the range of cells covered. Click "OK".

  • A ready-made table will appear with columns that can be edited.

  • Fill out the table with the necessary data.

How to create a free table for calculations?

To create a free table in Excel, you first need to build a regular table with data for analysis. For example, let's create a table with three categories: product, sale date, profit.

After creating a simple table with data, select the first cell with the date or other information (depending on what you wrote in this cell). Go to the “Insert” tab and select “Free Table”.

A small window will open. The program itself will select the range of a simple table, so just click “OK”.

A free table layout will appear. In the right menu we set the values ​​that should be displayed. We set all the values ​​that we initially set. Individual data can be highlighted.

A free table will be created. Now you can analyze the data, since at the bottom of the table the “Total Total” period will appear indicating the profit for 2 months or for the period that you designate.

How to create a table with exact dimensions in Excel?

To build a table with exact dimensions, you should follow these steps:

  • Open the document and set the page layout. To do this, go to the “View” tab and select the desired item. A ruler will appear above the table cells.

  • If your table is small, you can leave it in portrait orientation. However, if there is a lot of data, it is worth choosing landscape orientation in advance so as not to break the format. To do this, in the “Page Layout” tab, select “Orientation” and set it to “Landscape”.

  • Next, to avoid problems with printing, you should specify in advance the format of the sheet on which you will print this table. In the “Page Layout” tab, click “Size” and select A3 or A4.

  • After all the preparations, you can start creating the table. To do this, we set boundaries. This tool is located on the “Home” tab.

  • The size of the speakers should be adjusted using a ruler.

  • You can also adjust the lines.
  • After the table is drawn, you can enter data.

  • There will be no problems with printing such a page.

Do you want to learn how to make a table in Microsoft 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, an inexperienced user, of whom the majority are, does 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 prepared a universal method for you that works equally well not only on old versions of the program, but also on fresh ones. Well, to make a table you must do the following:
As you understand from all of the above, these instructions are universal and are 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. Everything is in your hands.

Formatting a table

You probably remember that at the very beginning we mentioned a huge number of 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 table formatting. 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!

Are you working with data that needs to be structured? Do you carry out a lot of monotonous operations and would like to automate the calculations? Perhaps you have just begun to master special programs? Spreadsheets will help you. In this article we will tell you how to create a table in Excel step by step.

Excel is the informal name for the Microsoft Excel program, which is used in the Russian-speaking professional environment. A wide range of functions and programming capabilities for specific tasks make Excel universal.


MS Excel is a powerful software package with the ability to process data using spreadsheets. The main goal of Excel is to make it easier to work with large amounts of data.

With Excel, you can perform complex calculations without having to resort to programming in most cases. However, the ability to use additional code is also included - using the internal programming language VBA (which stands for “Visual Basic for Application”).

Excel allows you to work with three types of data:

  • numbers
  • text
  • formulas

The Excel workspace is clearly organized: all columns are numbered with letters of the Latin alphabet, and rows are numbered.

Formulas allow you to automate calculations. If, for example, the value in cell A1 changes, then in all other cells that contain formulas with the designation of cell A1, the data is automatically recalculated.

Examples of tasks that can be solved using Excel functionality:

  • Same type of complex calculations
  • Automation of calculations
  • Visual representation of data in charts
  • Statistical analysis
  • Preparing tables and charts for printing
  • Generating reports
  • Solving optimization problems

Work on a computer or in the cloud? Excel and Google Sheets


Everyone knows that Excel is a program that is installed directly on a personal computer. If you work alone, this strategy is suitable.

How to establish collective access to tables?

It is possible to simplify the interaction of team members when working collectively with data. In Excel, this is solved using network access. However, it is easier to use Google Sheets (or Google Spreadsheets).

Perhaps the main advantage of Google Sheets is that this service is located in the cloud and has simple settings for organizing simultaneous remote access to data.

The functionality of Google Sheets is practically no different from Excel. In addition, it is free to use, which is important for companies.

The downside of Google Sheets may be the need for a constantly working Internet at hand. But who will this “shortcoming” stop during the general availability of the Internet?

Advantages of working with tables in Excel


A table in Excel is a rectangular range of cells containing data (numbers, text, charts, functions) that are structured in a special way.

Each table in the document is assigned a name and properties.

Many people do not use or use the “Table” function little in their work. By the way, you can find it through the top menu “Insert”. Select the required range with the mouse. Then go to the Insert tab and click on Table.

What are the advantages of working with Sheets?

  • Easy sorting of data in columns according to various criteria (color, ascending, descending, etc.).
  • There is no need to lock the top row with column labels when scrolling down the workspace.
  • Convenience when customizing the design - formatting cells (fill/font color).
  • The ability to create a pivot table in a couple of clicks.
  • You can increase or decrease the size of the Table with the mouse. To do this, click on the small black triangle in the lower right corner of the Table.
  • If you want to insert the same formula into all cells of one column, it is enough to place it only in the top cell. The program will automatically assign this formula to the remaining cells of the Table.
  • The ability to quickly form “slices” on individual lines (the function allows you to enable/disable unnecessary lines in one click).
  • Ability to quickly generate charts.
  • If you create a chart that you bind to Tableau data, the chart will dynamically change as you add rows.
  • You can summarize the results by activating the cell.

How to create a table in Excel step by step in 3 clicks

This and all further examples are considered for the MS Excel 2016 version.

Launch the program or open the Excel document you need using the function: File → Open.


Use your mouse to select the required data range. It is important that the range contains column names.


Apply the Table command to the range: Top menu → Insert → Table. A pop-up window will appear in which the system will automatically enter your selected range. Click OK.


The data range has been converted to a Table.


How to summarize in a table

Activate any of the cells in the Table by clicking on it with the mouse.

Run the following command: Top menu → Working with tables → Total line(you need to check the box).


Excel will add a new row to your table with the calculated total for the last column. Using the triangle button, select the desired calculation formula criterion.


If you need to display totals for another column, then click on the desired column in the totals row. Click on the triangle button that appears. Select the formula criterion you are interested in.


How to enter automatic row numbering in a table

If you need to automatically number table rows, do the following.

First you need to give your Table a unique name. Run the following command: Top menu → Working with tables. In the left line, write the name of the Table. For example, YourTable.


Place the cursor in any Table cell in the numbering column. Place the text in it: =ROW()-ROW(YourTable)+1. The function will automatically return all cells in the column numbered in order.


How to create a “slice” across table rows

If you need to temporarily hide unnecessary rows in a table, use the Slice command: Top menu → Insert → Slice. Select the columns you need and click OK.


Excel will create a window (one or more) in the workspace of the worksheet and give it a name.


By default, a slicer shows all rows in a table. Use your mouse to select the lines you want to keep. Excel will hide unnecessary rows automatically.


If you need to expand all the rows, click on the button with the image of a filter and a red cross in the upper right corner of the slicer window.

How to create a pivot table in Excel

Pivot tables help you create different reports from the same data. The function can be useful for accountants, internet marketers, commercial directors, and sales managers to quickly group and analyze data according to various criteria.

Let's look at creating a pivot table in Excel using a specific example.

We have a table where the following data is entered: article, product name,IDstore, date of sale, quantity, price.


Each line contains information about a specific sale. Let's assume that the document contains sales data for the entire chain of stores during the year. In this case, the table will consist of many thousands of rows, and it will be impossible to analyze the data in this form.

Task

  • What is the amount of goods sold for the entire period in the store with ID #11?

Apply the command: Top menu → Insert → Pivot Table. In the window that opens, set the parameters for the new pivot table and click OK. It would be best to place it on a new sheet.


On a new sheet, click on the PivotTable window. A menu will appear on the right.


Select fields to display in the final report: IDstore, price. The data in the pivot table is summarized for each ID separately. Now it's very easy to find the right number.


Answer: 6230.

If you want to hide unnecessary rows and leave, for example, only the total for ID #11, do the following.

Go to the PivotTable menu, select to the right of Criteria Store ID filter icon. In the window that opens, uncheck all unnecessary ID values ​​and click OK.


Smart tables in Excel - 2 secrets of effective work

Secret 1.

Using tables when working with large amounts of data can greatly simplify your life. Tables can be modified and customized to suit your needs. Use table style options for this.

Top menu → Working with tables → Table style options.

  • Header row - enables or disables the first row of the table, where, as a rule, the names of the criteria are located.
  • Total line - enables or disables the total line.
  • Alternating Rows - Allows you to customize the color of the rows.
  • First Column - Bold the contents of the first column.
  • Last Column - Bold the contents of the last column.
  • Alternating Columns - Allows you to customize the color of the columns.
  • Filter button - enables and disables filters on column headers in the table.

Secret 2.

Excel allows you not only to work with one table at a time, but also to link several tables together. You can link certain ranges (parts of tables), tables within one document (on different sheets), and tables in different documents.

Separating data into different documents (also called “books”) helps to avoid confusion and excessive file size growth.

To link data on different sheets within one document, use the function Sheet.

To link data from multiple documents, use the command Paste Options → Paste connection.

Conclusion

Tables in Excel greatly simplify working with data and calculations. Use pivot tables, filters in your work, and maintain a data hierarchy across sheets and files to increase personal efficiency.

Do you want to start working remotely, end financial slavery, devote more time to family and friends, live anywhere and travel with your family 3-4 times a year? Subscribe to

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

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

If your job involves data processing, then knowing how to work with Excel tables will help you save a lot of time and increase efficiency.

How to work with tables in Excel. Step by step instructions

Before working with tables in Excel, follow these 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 should not have empty rows and absolutely empty columns.

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

Select the area of ​​cells in which you want to create a table. Cells can be either empty or with 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 and also customize the display of headings. When everything is ready, click “OK”.

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

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

Pre-configured styles are available to customize the table format in Excel. All of them are located on the “Design” 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 table styles, all available styles will open. In addition to the styles preset by the system, you can customize your format.

In addition to the color scheme, in the table “Designer” menu you can configure:

  • Show header row – enables or disables headers in the table;
  • Total line – enables or disables the line with the sum of the values ​​in the columns;
  • Alternating lines – highlights alternating lines in color;
  • First column – makes the text in the first column with data “bold”;
  • Last Column – makes the text in the last column “bold”;
  • Alternating columns – highlights alternating columns with color;
  • Filter Button – Adds and removes filter buttons to column headers.

How to add a row or column to an Excel table

Even within 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 on the 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 go 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 the principle by which to sort the data: “ascending”, “descending”, “by color”, “numeric filters”.

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

  • “Text filter” is displayed when there are text values ​​among the column data;
  • “Filter by color”, just like a text filter, is available when the table has cells painted in a color different from the standard design;
  • “Numerical filter” allows you to select data by 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”, and also set up your own filter.
  • In the pop-up window, under “Search”, all data is displayed, by which you can filter, and with one click, select all values ​​or select only empty cells.

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

How to calculate a sum in an Excel table


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


A subtotal will appear at the bottom of the table. Left-click 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”, “quantity”, “number of numbers”, “maximum”, “minimum”, etc.

How to fix a table header in Excel

The tables you have to work with are often large and contain dozens of rows. Scrolling down a table makes it difficult to navigate the data if the column headings are not visible. In Excel, you can attach a header to a table so that when you scroll through the data, you will see the column headings.

To fix headers, do the following:

  • Go to the “View” tab in the toolbar and select “Freeze Panes”:
  • Select “Freeze top row”: