The operation is more convenient to learn to use Excel table in the table

We all know that the basic unit of Excel is the form. We usually use the Excel form to perform various operations step by step. What many friends don't know is that we can also form "tables in tables" in Excel by using the "insert form" method and use it to solve many daily problems.

You change I also change, use the table synchronization data in the table

In order to make your report look like text, many friends will insert perspective charts in Excel. However, many times the data referenced by the chart will be changed. After the source data is changed, such as adding or deleting a row of data, the chart reference source must be changed synchronously. If there are more changes, it will take time and effort to synchronize. 1).

Figure 1 The regular insert chart method cannot achieve synchronous changes

Data and charts can now be changed synchronously with the "tables in the table". For example, the above figure, first select A1 → B6 data, click "insert → table", the data range select A1 → B6, so that the original data title will become a screening form, then insert the chart, you can see the icon default reference A1 → B6 Data (Figure 2).

Figure 2 Data Format Transformation after Table Insertion in Table

Now if we need to make changes to the original data, such as adding a row of G product data, and following the prompts to enter the data, we can see that the chart on the right side will automatically increase the G product's chart. That is, after using the “table in the table”, the chart is The data in the table in the dynamic reference table, whether it is added or deleted, the chart data will change simultaneously (Figure 3).

Figure 3 table data synchronization changes

Many tables are not lost, quickly locate the worksheet

For some complex reports, we often create multiple worksheets based on the product name. For example, the inventory count table of the total library is based on the production workshop, a worksheet for each workshop summary statistics, so that a lot of worksheets later find it very inconvenient. For multi-task report files, custom classification can now also be performed using the “Tables in Table” method to facilitate subsequent search and positioning.

Select the data in the first table, operate the same as the selected data and click “Insert → Table”. After inserting the table, “table name” is at the top and the name of the current worksheet is used. The operation is the same as above, all the worksheets are set as the table in the table, and the name of the table and the name of the worksheet are one-to-one correspondence (Figure 4).

Figure 4 Name the insert table

After completing the above operations, if we need to find the corresponding worksheet, switch to any worksheet and open the drop-down list in the edit window on the left side of the insert function. This will list all the above-mentioned established table names, and click the corresponding table. You can quickly switch to the corresponding worksheet (Figure 5).

Figure 5 Quickly locate the worksheet in the drop-down list of table names

Of course, if the original worksheet name is long, we can insert the name of the table here for simplification, such as extracting keywords. Because here select the table name in the table is directly positioned to select the data area, so for many data sheets, we can choose any area of ​​data as the table table data, such as often query 501 rows of data, select the data here As an insert data source, select the table name later to quickly locate the row of the specified worksheet.

In addition to being able to quickly locate, for multiple worksheet data, it is often necessary to cross table references for multi-table data in daily use. The conventional method is to directly input the name of the referenced worksheet in the function. If there are many worktables and the names are complex, it is inconvenient to refer to them. But the use of the above method for the name of the insert table is more convenient to refer to, such as the Index function, when you refer to other tables in any worksheet as long as you enter the beginning of the insert table name, Excel will automatically be listed, as long as a simple click to quickly achieve Cross table reference (Figure 6).

Figure 6 cross table reference input table name can be the beginning of the character

In addition, after selecting the insert form, there will be "form tool → design menu", in the "table style" Excel built a lot of standard styles, for friends who need to beautify the table, just select a favorite style in the style list Quickly complete the beautification of the form (Figure 7).

Figure 7 Selecting styles can quickly complete the beautification of the table

The formula is not deformed, the template application is simpler

For frequently used worksheets, we often use a lot of formulas in it, and make it a template file for the convenience of the next use. However, conventional template formulas can be easily lost or edited incorrectly when editing data, causing the formulas to make mistakes in the next use. Now it is easier to ensure that the formulas are stable by inserting forms.

For example, in the above example, it is necessary to increase the unit price and the amount column, and the amount is calculated using the “quantity X unit price” formula. Then, as long as the unit price is increased in the original table C1, and D1 is increased, the “Amount” will be filled into the list after entering. Follow the prompts to enter the formula “=B2*C2” in D2, so that all the contents of column D will automatically fill in the formula. And as long as you do not delete the contents of the first row column header (for example, you can change the title name used in other reports), the original formula will not change can continue to use, save the document as a template more convenient for subsequent applications (Figure 8).

Figure 8 formula will automatically fill