Blue Flower

Formatting Data as Table
Format Work Table in Excel

Create work table in Excel 2013 involves formatting the content and layout of the work Table.

NOTE: Here I want to mention that in the first picture below you see the data structure that are formatted like a Table. But it is important that you know you Excel this structure is not seen as a realistic term Table. Otherwise Excel realistic table implies called Table1, Table2, Table3, etc. This data structure can be designed as a real table to Excel does not accept as a table.

For example, in the picture below I will show you how you can format the data range that looks like Table in Excel and the Excel is not seen as a real and realistic table that contains some data.

In the picture below, you notice two layouts of some data range (two visual "table"). The first look of the data (in red box) placed in the cell range of cells A1:E6. This data range is not marked with any colors, borders and the like.

The second table is also identical to the range of cells A1:E6, which has a first row in which the header information that is referred to as the data contained in a particular column. This range data is framed by borders of each cell and cell headers are colored yellow. Now you try it at the very beginning of this tutorial is to guess which of these two examples is a real table?

The range of data as a table in Excel

If you've decide to have any of the above versions of the picture and thought it was a table, you are mistaken. Neither version is not a real table, both versions are just the range of data that is formatted or unformatted cell frames and colors. Of course, if you watched the first theme of this web tutorial, adding color and frame cells can be done via the shortcut menu and Format Cells command. If you add information in one row below this range of data in other columns would not have any change.

From my experience I can say that there is a confusing version of the Table in Excel and that the appointment of the range of data and assigning identically named cell range.

So, if we name the range of cells called Table1, Excel again not realistic to accept this term and name. It's still a range of cells, but this time appointed. Look at the picture below and note that the Name Manager writes Table1, but pay attention to the front of the icon, this icon indicates that this is the range of cells that you can see in the Refers to:

The data range named named Table1 in Excel

How to Format a Range of Data as a Table in Excel

In the previous part of the tutorial you noticed that the cell range or range of graphical data can visually edit, format, paint, frame, etc. and it does not mean that Excel realistic means that this is the Table. Furthermore, in the next steps this tutorial you will see how you can convert a range of data in a dynamic table. The basis for the examination table in the Excel Workbook is Name Manager dialog window that you can run a command that is located on the Formulas toolbar or via keyboard combinations CTRL+F3.

To meet the criteria in an Excel formula as a requirement of the declared table, you need to create a table through commands on the ribbon. One important detail to work with real tables is that in some formulas can include column headers.

How to Create a Table in Excel

So, when you create a table you need to do the following:

Select the data range or a range of cells together with the header columns of data.

Click on Home toolbar => and group commands Styles click on the Format as Table. The offered templates you need to choose the design of the table.

The choice of form and design tables in Excel

In the next dialog box, you notice option my table has headers because our table has a header and finally click on the OK button. Of course if you have not selected a header columns of data this option will turn off.

Convert a range of data in Excel spreadsheet

Now we have this kind of look of tables that are created after converting a range of data in the table. You notice that Excel is set Filter for each column, it is the default when you create a Table in Excel.

Appearance the converted data as Table1 in Excel

To help you make sure that you really have created a Table that Excel realistically implies open Name Manager. So, click on the Formulas tab toolbar on ribbon => Name Manager and see if there is a name from Table1, Table2 and Table3 etc. .. and also pay attention to the icon before the name.

In the picture below you notice two names:

  • Table1 (named data range, the range name Table1)
  • Table2 (real table is created which is assigned the name of the Excel 2013 Table2)

You notice the name Table2. This is the table that we have created over the command Format as Table. Excel has created the name "Table2" because there is already a name Table1 although it is a named range of data. You notice the icon before the name Table2. This icon is essential to identify the real table in Excel. Also you notice in the Refers to: ='Sheet1(2)'!$A$2:$E$6 range of cells that is different from the range of cells at the beginning of this tutorial, i.e. when naming ranges of data. So, Excel did not take into account the header columns in the range of Excel cells.

How can you know if it's created a real table in Excel

After creating real tables (Table1, Table2, Table3, etc.) on the ribbon appears Format Design Tools toolbar. On this toolbar can further modify the design and appearance of the table. The group commands Table Styles Options you notice command Total Row. If you turn this option will then appear at the bottom of the table an extra line that will automatically add value because Excel will understand that in the last column, there are formulas that calculate some values ​​and give the result.

Once we have created a real table in Excel, you have some options that are not available in the regulated range of data. After creating the table, we can simply add a new serial number, and Excel will automatically format the other cells in the same row, or we can stretch the table and to keep formatting (drag-and-drop option when a two-sided arrow in the lower right corner of the table, see second image above).

Also if you activate the command Insert Slicer the ribbon will appear in new toolbar Slicer Tools Options. You explore other options on the relevant toolbar.

Converting Tables in a Range of data in Excel 2013

If you want to convert the Table to a range of data then we can do this via the command Convert to Range to Table Tools Design toolbar. Excel automatically removes the filter, and the toolbar disappears with Ribbons but the colors and design remain. Also you notice that in the Name Manager no Table2 name.

How to convert the table to a range of cells or range podatatka in Excel