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

Original title: "after reading the tax information to fill in the template, I learned these four super practical Excel skills!" "

Hello, everyone. I am Der Spiegel in mind.

The annual summary of comprehensive income in 2022 has already begun. How much tax have you refunded from or?

However, when it comes to taxes, it is necessary for all companies to declare personal income tax.

Some companies are declared by the personnel department, and some companies are declared by the financial staff.

Of course, some companies have administrative staff to do this at the same time.

So, today, let's take a look at what we can learn from the IRD's personal income tax information import template.

1. Find the magic use of replacement. Let's first open the personnel information collection and import template of the Inland Revenue Department. As shown below:

In the figure, an asterisk (*) is required for a red font.

In fact, when you first enter the data, it goes like this:

The default font color for the form is black.

How do I change the contents of the cells with an asterisk (*) to a red font?

Select those cells by hand, and then add colors?

Well, there's not much data, so it's fine. If there is a lot of data, of course not.

Here you can ask [find and replace] this guy to do us a little favor.

Press [Ctrl+H] to bring up the find and replace dialog box.

Enter "*" in the search

You do not need to enter anything in replace with, but you need to set the font color to red in the format next to it.

Click [Font]-[Color] Red-[OK] as shown in the figure above. As shown below:

Finally, click "replace all", and the results are as follows:

All the cells are filled with red fonts!

What kind of divine operation is this?

It turns out that the problem lies in the wildcard star (*) itself.

Two asterisks (*) are entered in the search, but the first asterisk works exactly the same as the second asterisk, which is still a wildcard.

Represents any number of characters.

We need to treat the wildcard asterisk as a normal character here. Just precede it with a zigzag (~), that is, enter: ~ * * in the search.

The function of this wave character is to remove the role of wildcards and turn them into ordinary characters.

Finally, click [replace all], and everything will be all right!

2. The magic use of data verification one:

Let's take a look at the name column in the second row.

Click on the [B2] cell and a prompt box will appear to remind the user what to pay attention to when typing.

This prompt box is implemented with the data validation function.

Click "data"-"data tools"-"data Verification"-"data Verification".

Click [input Information]-[Show input Information when selected Cell]-[input Information] in the data validation dialog box.

In the enter Information text box, enter the prompt you want to display.

Of course, in addition to the above usage, we can also do this by inserting annotations.

For example, an annotation is inserted into the bank of deposit cell in the [AR] column to prompt the user how to do it.

Insert a comment by right-clicking and finding New comment from the menu.

PS: Microsoft's OFFICE365 has renamed the original comment to comment.

In addition, some friends must want to ask, is there any difference in the function of these two cue boxes?

The difference is that the prompt function for data validation requires the cell to be selected before the prompt box appears. The prompt function of the annotation does not need to click on the cell, just move the mouse over the cell to display the prompt content.

Magic use two:

When you click the [C2] cell, a drop-down button appears, and a drop-down menu appears when you click the drop-down button.

This function is also implemented by data validation.

Follow the above method to bring up the data validation dialog box.

Click "Settings"-"sequence"-enter the details of the drop-down menu in "Source".

It should be noted here that:

❶ can enter content directly, without the need for double quotation marks for text and numbers.

❷ can refer to a cell directly (or define a cell as a name and then reference it directly).

❸ can generate a dynamic reference region with a formula.

The Inland Revenue Department refers directly to a defined name as the drop-down menu here.

How do I find out what this proof type is?

The easiest thing to do is to directly click anywhere in the source box to jump to the referenced range of cells.

Of course, another way to view it is to press [Ctrl+F3] to open the name Manager dialog box and view it in it.

3. The ingenious use of the conditional format when we have typed the name and document type, the [Erio G] column will automatically turn gray.

As shown below:

This function is implemented in a conditional format.

Select the [E2] cell, and click "start"-"conditional format"-"Management rules".

The conditional formatting applied to the currently selected cell will be displayed in the conditional formatting Rule Manager.

Double-click the rule as shown below:

The figure shows that a simple formula is used to format conditional values in [format values that conform to this formula].

When the content of the [C1] cell is equal to the resident ID card, the fill color of the cell is automatically set to [gray].

PS: the text of the resident ID card in this condition must be enclosed in double quotation marks. This is different from the text requirements entered in the drop-down menu of the sequence in the data validation above. Please pay special attention to my friends.

4. At the end of today, we share some functions that the Inland Revenue Department often uses in the introduction template of personal income tax.

❶ find and replace format

❷ data validation

❸ conditional format

Mastering these functions can greatly improve our efficiency in our work. And we often encounter it when we look at the forms of other companies. I hope my friends can use it skillfully.

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.