In recent years, new “smart” technologies have appeared: smart phones, smart homes, refrigerators, phone cases, televisions, etc. Spreadsheets are no exception. Excel spreadsheets have been introduced since 2010.
The concept of smart tables
The Excel workbook itself is a huge spreadsheet. However, this table requires constant adjustment: drawing out formulas, sorting, design, if this table needs to be presented in any document or presentation.
Microsoft Corporation, which created Excel, proposed calling smart tables tables, and what was previously called tables is a range. However, in our country, these ideas did not take root, so the tables remained tables, and the innovation was called smart tables.
With the introduction of smart tables, the perception of a spreadsheet document improves, data processing becomes simpler, and they are reduced to a specific system.
Creating a smart table in Excel
Suppose we have already entered some data in Excel. In this case, select them, go to the main menu of the feed and look at the groups of commands, find Styles, in it click on the "Format as table" button.
A menu will open in which you will be asked to choose a table style - select any one that you like.
Next, a dialog box will appear in which you will be asked to clarify the range of cells included in this table, and if the table contains headers ("header"), then check the box next to "Table with headers".
Then it is necessary to confirm the performed actions by clicking on the "OK" button or pressing Enter.
As a result, the table will change in accordance with the selected style and will include filtering for each column.
Thus, without much effort, you can easily create a smart table in Excel.
Drop down lists
One of the secrets of increasing the speed of entering data into a spreadsheet is the use of drop-down lists.
First, create a list of data that should be in the drop-down list on another sheet of the application or aside from the data for printing.
Select the cell in which this list will be located, go to the "Data" tab, select the "Work with Data" group and click "Check Data".
In the window that appears, click on the arrow next to "Data Type" and select "List".
Then we move down this window to the "Source" field. We place the cursor there or click on the icon on the right of this field and select the range of cells that will be included in the drop-down list.
We confirm the changes made. As a result, we get a drop-down list with your data.
In order to avoid an error message when entering incorrect data, you must again click on "Data Verification", click on the tab "Error Message" and uncheck "Display error message". This will allow you to enter into the cell with a drop-down list any necessary data, and not just those that are in the drop-down list.
In order to constantly add new data to this list, you need to use smart Excel tables for drop-down lists.
First, create a smart table with a heading that contains only the heading and listing the components of the drop-down list, then create this list as described above without highlighting the heading. After that, we confirm the changes made. This will allow you to constantly replenish the list with new values.
The presented method is one of the secrets of effective work with smart tables in Excel.
Change the style of the smart table
If you follow the instructions, you will get a smart table of a certain style, but in the menu it appears symbolically and may not always like how it looks on the screen. Therefore, if necessary, the style can be changed.
How to make a smart table in Excel with a new style? To do this, you do not need to select the entire table, just place the cursor in the form of a black rectangle on any cell of the smart table.
After that, we move to the tab "Designer", the group "Table Styles". If there are not enough of the presented styles, click on "Advanced Options" and see the entire palette of styles.
Choose the style you like, which will be applied to the created smart table.
By the way, if you don’t like any style, below you can click on the link “Create table style”.
Style options
Smart tables in Excel 2010 and later, as well as in Excel 2007 can be customized according to your preferences and needs.
To do this, move the cursor to any cell in the table. We go to the tab “Designer”, the group “Parameters of table styles”, and there we put / uncheck the options that interest us: “Filter button”, “Alternating columns or rows”, “Heading or totals row”, “First or last column” .
Using formulas
Formulas in Excel smart spreadsheets do not need to be copied or pasted. Creating a new column or a new row leads to the fact that the formulas themselves are pulled into the newly created table elements in the necessary cells.
If you need to create a new column in which you want to enter a formula, in the cell of this column, enter a formula that is slightly different from the standard for this type of spreadsheet. It takes the form [@ [{Column Name}]] arithmetic operations.
Here {Column name} is the specific name of the column in which the calculation is performed, for example, "Amount, rub.", Arithmetic operations are algebraic expressions accepted in Excel formulas and, if necessary, numbers, @ - indicates that the data will be taken from the same line.
These formulas can be entered as usual in Excel by clicking on the appropriate fields and only putting down the necessary functions, all the auxiliary icons necessary for the formula in this case will be added automatically.
Pressing Enter will recalculate in this column in all cells of the table.
If it is necessary to calculate at the end of the totals table on the tab “Constructor” in the group “Parameters of table styles” we put a tick on the option “Totals row”, which will allow you to calculate the totals for the columns where the formulas were entered.
If you are interested in this information, have a good command of foreign languages ​​and will look for additional sources of information abroad, remember that in Russia and abroad there are different separators of list items. They have a comma, and we have a semicolon, which must be taken into account in the formulas.
Autofiltration
As mentioned above, when creating smart tables in Excel, an autofilter appears in each column of their heading, with which you can specify what needs to be displayed or, conversely, not displayed at this particular moment in time. For example, you can use keys such as "more", "less", "contains", "does not contain", etc. This feature can be applied when working with a large data array.
For example, there is a table with customers, and in another column months of purchases are indicated. We are only interested in February. In order not to manually search for February buyers, click on the arrow of the “Month” column and put a checkmark there near “February”, unchecking the rest of the checkboxes. Thus, only buyers who made purchases in February will be shown. The rest will not disappear anywhere, if you again click the arrow in the "Month" heading and tick all the months, all the buyers will appear on the screen again.
Set the table name
After a smart table is created in Excel, the application assigns a name to it in accordance with the order of its sequence (if this is the first table will be given the name "Table1", etc.).
In order to find out the name of the table, put the cursor in any of its cells, go to the tab "Designer", the "Properties" group and there we find the parameter "Table Name", which can be edited, for which you need to place the cursor on "Table1", select and enter a new name, then press Enter.
This name can be used in formulas, built-in functions, drop-down lists.
The name of the table can be used when applying the functions VPR, GPR. For example, we want to find who we had the 1229th customer. To do this, we enter 1229 in some free cell, for example K1, in the cell L1 we enter the formula = VLOOKUP (K1; Table1 (or its changed name); 1). Here 1 is the number of the column in which the search will be performed. As a result, the buyer’s full name will appear in cell L1 under No. 1229 (unless, of course, he was in the first column).
Smart Tables Addressing
Smart tables in Excel allow you to use field names instead of a range of cells with data. So, to calculate the sum of column C from the 1st to the 21st cell, you can enter the formula generally accepted in Excel = SUM (C1: C21). Suppose that column C contains information on the amount of the purchase made by each specific buyer, and this column is called “Amount, rub.”, And the table has been renamed to “Buyers”. Thus, the following expression can be introduced as a formula: = SUM (Buyers [Amount, rub.]).
In addition, you can use special addressing:
- “= Buyers [# This line]” will refer to the current line.
- “= Buyers [# Data]” will refer to the data, excluding the header lines.
- “= Buyers [# Headers]” will refer to the table column headers located on the first row of the table.
- “= Buyers [# Results]” will refer to the summary line (if available in the smart table).
- “= Buyers [# All]” will refer to the entire table, including all of the above formulas.
So, for example, you can calculate the amount of purchases by entering the formula = SUM (Buyers [# All]).
Additional "chips" smart tables
Scrolling columns of a table with its rather large size does not require fixing areas, since the names of the columns of the table are moved to the column headers of a particular sheet of a particular book.
Excel 2010 introduced the ability to create slices when working with smart tables. They are an autofilter of a separate column, presented as a graphic element. Go to the tab "Designer", the group of commands "Tools", click "Insert slice", in the dialog box, select the names of the columns (or one column) from which the slice will be formed. If you need to filter the table, select the necessary category or categories by holding down the Ctrl button.
How to delete a smart Excel spreadsheet
Sometimes a situation may arise when you need to save data in the usual spreadsheet format, removing sorting, auto-substitute formulas, addressing, and other lotions of smart tables from it, that is, leave the table only as a database.
In Excel, you can turn a smart table into a regular one. To do this, place the cursor in any cell of the smart table, go to the tab "Designer", a group of commands "Service", click on "Convert to range".
As a result, a dialog box appears, confirm the action by pressing the consent button or by pressing Enter. After that, the table will be saved in its usual form with the last applied style and data.
Finally
Thus, smart tables are the same tables that you can create yourself. But their use allows you to effectively use the time for preparation by reducing manual operations, beautifully designed tables, use graphic elements. If you suddenly don’t like the created smart table, you can always transform it into a regular one.