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

The original title: "the 4 pits in Excel, those who have been cheated, please raise your hand!" "

Do you guys often encounter some strange problems in the daily use of Excel?

For example:

The merge cell feature cannot be used

A strange finger icon appears when selecting a cell

Most of the functions in the insert tab are grayed out and cannot be used

The automatic filtering feature cannot be used

……

No doubt, this is not Excel out of Bug, but enabled some special Excel features.

Want to know what this function is? And why use it?

Come and have a look with me.

The merge cell feature is not available, although we do not advocate the use of merge cells in Excel, but it may still be used under some special requirements.

Normally, the merge cells feature is available.

However, sometimes it will be the following gray unavailable state T T

Other functions can be used normally, but not this guy, why?

Does Excel want to disable this feature? Or some other ghost?

There are actually two reasons:

First: the table sets the function of [protecting worksheet].

Normally, we click [Review] = "[protect worksheet] =" [OK]. The settings for protecting the worksheet are now complete.

However, in this way, the functions in the entire cell formatting will not be available.

At this point, all we need to do is to select the "format cells" option in the "protect worksheet" dialog box.

At this time, the functions in the start tab can be used normally in addition to the merge cells function.

In this way, we can control those hands-on friends!

Second: set the original area to [Super Table].

First select any cell in the data region, and then click [insert] = "[Table] =" [OK]

After that, the merge cells function is not available in the start tab, and when the super table is selected, there is an additional Table Design tab. Other functions of formatting cells can be used normally.

The reason why the super table disables the merge cell function is that it is designed according to the idea of an one-dimensional table, each row is a record, each column is a field, each field must have a title, there can be no merged cells, and so on.

We usually use the super table as the data source, and then use it to generate charts or PivotTable that can be expanded automatically as the data source changes.

Magic finger icon normally, when we select or point to a cell, the mouse pointer will show the shape of a cross icon.

However, sometimes when we select a cell, the mouse will appear with a finger icon like this.

What the heck is this?

In fact, this is a special effect that can only occur when [data validation] is set in the cell, and the [protect worksheet] function is set up at the same time.

In addition, this finger icon also appears when the mouse hovers over the autofilter button.

Do you guys know what it is this time?

Some of the functions in the insert tab fail as shown in the following figure. In the insert tab, there is a gray area, and most of the functions are not available.

What the heck is this?

There are two reasons:

First: set up the [protect worksheet] function.

The reason is the same as above.

If we want to use most of these functions while setting up the protect worksheet, we can check Edit object in the setting up the protect worksheet.

When [Edit object] is checked, the insert tab has the following effect.

The second reason is that in [Excel option] = "[Advanced] = =" Select [for objects, show: no content (hidden objects).

The effect of the setting is as follows:

Most of these features are also in a gray unavailable state.

In addition to setting in [Excel options] = "[Advanced], we can also use the shortcut key [CTRL+6] to switch between displaying and disabling objects.

Automatic filtering cannot be used in some cases, we will set up the [protect worksheet] function in order to prevent others from tampering with the form.

However, we can let others do automatic filtering.

As shown in the following figure: we check the "use automatic filter" function in the "protect worksheet" dialog box.

After setting up, as shown below

The [automatic filter] function is grayed out and cannot be used!

Is this Microsoft fooling us? Or is there BUG for this feature? Or are we operating improperly?

In fact, we just need to call up the Auto filter button before setting up the sheet Protection, and then check the use Auto filter function in the protect worksheet.

The effect of the setting is as follows:

In this way, automatic filtering can be used normally!

At the end of today, we share some strange problems in the actual use of Excel.

In fact, most of them are set up by Excel in advance to prevent users from doing irregular operations (such as merging cells), or to remind users to do something special (such as finger icons).

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.