Practical Excel skills sharing: 7 ways to improve the efficiency of table viewing

In the previous article " Practical Excel skills sharing: How to sum data with units? 》, we learned the data summation method with units. Today we will talk about data presentation skills and share 7 Excel tips to improve the efficiency of table viewing. Take a look!

In daily work, our tables often reference other tables across tables. When viewing a table, we need to open multiple tables at the same time to view; when we have one When the table is particularly large, you need to compare the data before and after viewing or the left and right data, etc. These problems may seem inconspicuous at ordinary times, but after learning some tricks, the efficiency of viewing the table can be greatly improved. Today I will introduce to you some tips on data presentation in excel.

1. Freeze rows and columns for viewing at the same time

When viewing the company’s product schedule, I need to compare and view the product quantity information at different times. . As shown in the table below, the table is very long and wide. When I view the data, I need to always see the title row and the data information in the first three columns.

Here are two ways to deal with it.

1. Split first and then freeze

Click the "Split" button under the "View Tab".

You will see a gray "cross" appear on the screen. Drag the "cross" to the back and below the area you want to freeze.

I want to freeze the first 2 rows and the first 3 columns, so drag the "cross" frame, place the horizontal bar under the 2nd row, and place the vertical bar After reaching column C.

Click "Freeze Panes" on the "View" tab and select "Freeze Split Panes" in the drop-down menu.

You can see that the gray "cross" cross has turned into a thin black line. At this time, if we drag the left scroll bar up and down, we will see that the first two rows of the table are fixed. If we drag the lower scroll bar left and right, we will see that the first three columns of the table are fixed.

If you want to cancel the freezing effect, in this case where you split first and then freeze, you can directly click "Split" again. After clicking, the form returns to its original state.

2. Directly freeze designated cells

In fact, there is a simpler method. First select the first cell in the active area. I want to fix the first two rows and the first three columns, so the first cell in the active area is D3.

Click "Freeze Split Panes" directly.

At this point you can see a thin black split line appearing in the table. Drag the scroll bar to see the same result as the previous animation demonstration.

2. View multiple tables at the same time

At different times, when we view the table, there will be Different needs, such as when cross-table references appear in table formulas, you need to view different workbooks and different worksheets at the same time; when you need to compare and view data in different time periods, you need to view the data before and after the worksheet at the same time, etc., as follows: Let me explain to you the different ways to view the table.

1. Window rearrangement to view tables

If multiple workbooks are opened at the same time, most friends switch workbooks through the taskbar at the bottom of the computer screen. In fact, excel comes with the function of switching workbooks.

As shown in the figure below, click "Switch Window" under the "View" tab, and select the workbook you want to view in the drop-down menu, so that you can quickly switch between different workbooks. (Note: Press the shortcut key Ctrl Tab to rotate between multiple workbooks.)

If you want to view all workbooks on one screen at the same time, click "Reorder All".

Just check any desired arrangement in the pop-up dialog box. You can come down and try it out and compare the effects of different arrangements.

I choose the "tiled" arrangement here, and you can see the following results. At this time, you can view three workbooks at the same time.

#If you want to restore the table after viewing it, just click the maximize button on the upper right side of the table.

2. Create a new window to view the table

How to view different worksheets in the same workbook at the same time? If you have learned the previous operations, it will be very simple. We can use the operation of viewing different workbooks to view different worksheets at the same time. As shown below, I only have one workbook open.

Click "New Window".

Look at the drop-down menu of "Switch Window" and you can see that an identical workbook has been copied.

Same as the previous operation, click "Rearrange All" to view the same workbook in separate windows.

We can switch the two windows to different worksheets below the workbook.

#At this point we can view different worksheets in the same workbook at the same time. This method is very convenient when entering formulas across tables, and will be used frequently in our function tutorials in the future.

This method can also be used to view different locations of the same worksheet at the same time.

3. Split the table to view it

If the worksheet is very long or wide, and you want to view the data before and after it at the same time, in addition to using a new window, We can also use splitting.

Click "Split" under the "View" tab, and a gray "cross" cross will appear.

The "cross" cross frame can be placed at any position as needed. It copies the form into 4 copies and displays them on the screen at the same time. You can see two scroll bars at the bottom and left side of the screen.

By adjusting these four scroll bars, we can view 4 different positions in this worksheet at the same time.

3. Hide unnecessary data

Often we don’t need to view all the data in the table, or some of the data in the table If it is not commonly used, you can hide the data that does not need to be viewed or is not commonly used to facilitate the viewing and use of the data. Here are two ways to hide data.

1. Right-click to hide

and select the row or column you want to hide.

Right-click the mouse and select "Hide" in the drop-down menu

You can see that column C and Column D between columns E is hidden.

Select columns C and E, right-click the mouse and select "Unhide" to unhide column D.

When there are many hidden rows or columns in the table, click the Select All block in the upper left corner of the table to select the entire table.

Then right-click on any row number and column number and select "Unhide" to unhide all.

2. Hierarchical display

The above method is only suitable for hiding tables with relatively few rows and columns. When there are many places that need to be hidden, it will be more convenient to use hierarchical display. .

Select the data in columns C-E.

Click the "Create Group" button under the "Data" tab.

At this time, you can see a stretchable line appearing above the data.

Click the "-" button on the right side of the line, you can see that the three columns between B and F are hidden.

Click the "+" button above the data area to expand the three columns of data.

In the same way, rows can be hidden. As shown in the figure below, I created three hierarchical display groups.

We can clearly see the advantages of this method from the picture. We can clearly see where there are hidden rows and columns, and they can be expanded and folded at any time, which is very convenient.

Some friends will say: If the hidden data is very long, after clicking the "+" button to expand the data, the "-" button will appear very If the location is far away, it will be very troublesome when you want to fold the data.

In fact, this problem is also easy to solve.

After we select the data area that needs to be hidden, click the expansion arrow in the lower right corner of the "Outline Group".

You can see in the pop-up dialog box that Excel has two directions checked by default, which means that the fold button appears below or to the right of the data.

We cancel the selection of the direction. After clicking OK, you can see that the fold button is fixed above the hidden data.

#If you want to cancel all outlines at once, click "Clear Outline" under the "Data" tab.

#If you only want to cancel a certain group, first select the data area of ​​the group and then click "Ungroup".

Through the above study, we can see that hierarchical display is a good alternative to hiding rows and columns. Today’s tutorial ends here.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: 7 ways to improve the efficiency of table viewing. For more information, please follow other related articles on the PHP Chinese website!