Shulou(Shulou.com)11/24 Report--

Hello, everyone! Did you use Excel today?

I don't know if you have ever been bothered by such a long and wide form.

What? Can't see clearly?

. If you can't see clearly, that's right!

Look! Even if I zoom the table to the limit size that I can recognize, I still can't see the full picture of the table.

As a patient with advanced obsessive-compulsive disorder, I abhor such a large table with full screen, incomplete display and dense data.

Today we're going to talk about five essential skills for efficiently browsing oversized tables.

Let's take a look, ⬇⬇⬇.

01. Shortcut key

When browsing large tables, the most direct pain is to increase the amount of operation we do.

Originally, you can have a panoramic view of the table data by just rubbing the wheel, but now you have to drag the slider back and forth with the mouse to view it, and you may accidentally drag it out of the screen, which is very maddening.

How can we quickly and accurately locate the head and tail of the watch?

▋ [Ctrl + key]

Select any cell in the table area with the mouse and press [Ctrl+ ⬆], [Ctrl+ ⬇], [Ctrl+ ⬅], [Ctrl+ ➡] respectively, and you can move to the upper and lower left and right edges of the table.

However, when using this set of keyboard shortcuts, it is important to note that the data in the table should be contiguous, otherwise the box will stop when it encounters a blank cell.

▋ [Ctrl+Home] / [Ctrl+End]

If there are a large number of blank cells in the table, we can also use another set of shortcut key combinations to achieve the effect of quick browsing.

Press the [Ctrl+Home] key to quickly switch the marquee to the upper left corner of the table, and press the [Ctrl+End] key to quickly switch the marquee to the lower right corner of the table.

▋ [Ctrl+Shift + key]

So why is it that sometimes when we gently drag the slider, the table jumps thousands or even tens of thousands of rows?

This situation is mostly caused by the input of some cells in the blank area at the bottom of the table, and the solution is very simple.

Use the [Ctrl+End] key combination to quickly locate the bottom of the table, use the mouse to select the entire blank row below, press the [Ctrl+Shif+ ↓] key combination, batch select all the cell areas below, and click the right mouse button to delete rows in batches.

Save after deletion, and the scroll bar will return to normal.

02. Freeze Pane

When reading large tables, because of the large amount of data, the row and column headings become an important reference for us to view the data, so we need to fix them to make sure that we can still see them when we drag the slider.

Lock the header row:

Select the entire row of cells below the table title row with the mouse, click the freeze Pane button in the View tab, and select freeze Pane to complete the title row locking.

Lock the title column:

Select the entire column cell to the right of the table title column with the mouse, click the freeze Pane button in the View tab, and select freeze Pane to complete the title column locking.

Lock both the header row and the header column:

Find the cell in the table where the title row and the title column intersect, click the cell in the lower right corner of the cell with the mouse, click the freeze pane button in the View tab, and select the freeze pane. you can lock the title row and title column at the same time.

03. Super form

When we want to summarize long tables, there is a function that can help us complete the calculation with one click instead of setting complex formulas.

Select the first row of the table and press the [Ctrl+Shift+ ↓] key combination to quickly select the entire table

Click the Table button in the insert tab, or press the key combination of Ctrl+T directly, and click OK in the pop-up dialog box to complete the creation of the super table.

Note: if there are merged cells in the table, the super table will automatically split the merged cells, so make sure the table is normative before using the super table.

After creating the super table, we will use its two features:

The header row of the ❶ table is automatically pinned to the top to achieve the same effect as the freeze pane.

❷ click the Table Design tab, in the Table style options function group, find the summary row and tick it. The summary row will be generated at the bottom of the table.

In addition to summing up, the summary bank also provides statistical calculation functions such as average, count, maximum and minimum, which is very convenient.

Move the mouse to the lower right corner of the summary cell and fill in the formula to the left. You can also realize the data statistics of other columns.

04. Split

Now that we have fixed the header with the super table and completed the summary statistics of the large table, is there any way to observe the title row and summary row at the same time when we scroll the wheel?

In this way, don't you have a panoramic view of the whole form?

Haha, it's time to split the function next.

Select the summary line at the bottom of the mouse, click the split button in the View tab, and the table is divided into two.

Click anywhere in the table above with the mouse and slide the mouse wheel to see the effect.

05. Slicer

Usually, we can use the filtering function to further browse and analyze the table data, but because of the large amount of data and various categories of large tables, the filtering conditions and dimensions become more complex. At this time, the traditional filtering function is not so convenient.

Therefore, we have to use the last killer mace: slicer, to complete the rapid query of large table data.

Note that converting a normal table to a super table first is one of the necessary prerequisites for using the slicer function.

Click any area of the table, click the insert slicer function in the Table Design tab, check the key fields that need to be filtered in the pop-up dialog box, such as marketer, product, region, and click OK.

In this way, we have created three sets of slicer buttons, click the corresponding options, and you can quickly filter and view them. If you want to select more than one button in the slicer, hold down the [Ctrl] button and click the mouse to check.

What do you think? is it very cool?

06. Summary

Today, I share with you five tips commonly used when browsing large tables:

❶ shortcut key: Ctrl+ key; Ctrl+Home / End;Ctrl+Shift + key

❷ freeze pane: freeze first row, first column

❸ super table: fix the first row to the title bar; add summary rows

❹ split: fixed trailing

❺ slicer: rapid screening

Did you learn it? I hope these skills can help you in your work!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhang Kaiyuan, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.