Skill sharing of quickly switching Digital format Units in Excel

Behind the headlines! Hello, everyone. I'm Satellite Jam!

When I was studying WPS recently, I found that there was a ready-made [unit: ten thousand yuan] to choose from in its [cell format].

In other words, we do not have to recite the complex format code, we can put on the "coat" of "ten thousand yuan" directly with one click.

It looks very moving.

So, for our Exceller, although we can't set "ten thousand yuan" units with one button

However, can you think of some way to simplify the steps of setting the digital format in Excel, for example, setting it at one time, and then you can switch units as long as you click with the mouse?

In this way, you don't have to rack your brains every time to recall those complex format codes!

1. I use a table like this as an example:

Set up data verification in C4 cell, select [sequence]; [Source] fill in: "Yuan, 10,000 yuan, million yuan, 10 million yuan, 100 million yuan".

Be careful to be separated by English commas! )

Then set the conditional format:

Select B6:B14 (cross-column center is used in the figure), correspond to the amount entered in the table; under the "start" tab, find "conditional format" and select "New Rule".

Select the rule type: [use the formula to determine the cell to format], enter the formula:

= $Category 4 = "ten thousand yuan" format, select "number"-"Custom", and fill in: 0room.0

Look, when we choose "ten thousand yuan" as the unit, the numerical format in the table will be adjusted automatically!

The formulas and format codes for other units are as follows:

In this way, we can use any number unit we want in our table!

You see, as long as you are prepared in advance, the work in the future will be much easier.

2, with the unit format, some people may think that this is still not very satisfactory: the number still does not have a unit!

simple

On the basis of the above steps, just add the Chinese unit!

Cancel the cross-column centering, and do the following ↓ in the corresponding part of column C

For example, set "yuan": 0.0 "yuan"

Set ten thousand yuan: 0 yuan. 0, "ten thousand yuan"

……

And it ends up like this:

The actual application to the table is as follows:

We have whatever ▲ boss wants, although it may take some time in the early stage, but your efforts will eventually pay off-using skills to standardize tabulation can avoid a lot of ineffective overtime!

Just like Excel, if you put in a little time to study, you can improve your efficiency in the office.

3. You can also try other commonly used formats:

👉 distance Units:

👉 decimal settings:

👉 Color:

4. Write at the end by using the drop-down list and conditional formatting, and set the number units in the table in advance like this, so that you don't have to worry about the problem that the code can't remember when you use it in the future!

You can also make a form like mine, which can be used as an assistant for viewing the format code.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: satellite paste