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

The original title: "these days, Excel can DIY the calendar, you don't know, do you?" "

2022 is almost over and 2023 is coming.

We have spent the year of the Tiger in Ren Yin and are about to usher in the year of Guimao Rabbit.

What are your expectations for 2023?

Day by day, drag the progress bar, witnessing our unforgettable journey every day, but also putting an end to that special and unique day.

Every time I press, the year disappears for a day and takes a step to the right. Until, when we press 365 times, it is the end of the year and the moment when the progress bar is completely filled.

Although we can't catch the progress of the passage of time, we can grasp the Excel in our hands and learn how to do the above progress bar. 😏

Conditional format, the way to create a progress bar that belongs to you is actually very simple. Following the article, you can basically read it and do it.

Only two functions are needed: the numerical regulator that controls the data bar and the data bar in the conditional format.

The manufacture of the ▋ numerical regulator Let's first look at the button that controls the progress bar, that is,

. It is called both a numerical regulator and a fine-tuning frame.

How do I bring up the value regulator?

Under the developer tab, click insert, and there is a numeric regulator under the form control menu. Click it.

In the worksheet, hold down the left mouse button, then drag to the right, and it comes out.

If there is no development tools tab, you can call it up in [File]-[options]-[Custom Ribbon]-[check Development tools]-[OK].

Move the mouse over the numeric regulator, right-click, and select format Control.

In the [format Control] dialog box:

Minimum: 0

Maximum: 365

Step size: 1

The cell link is $Demp2.

Click the [OK] button after the above values are set.

PS. Because there are 365 days in 2023, so the maximum here, we set it to 365.

Now, every time we press the numeric regulator, the value of the D2 cell will be + 1.

▋ uses the conditional format to make a progress bar and sets the button to adjust the value. Now let's make a progress bar.

In the cell where the progress bar is stored (C45 in the figure), enter the formula = D2. D2 is the cell controlled by our numerical regulator.

Now, press and hold the regulator button, and the value of the progress bar will also increase at a step size of 1.

Select the progress bar cell, and under the start tab, click "conditional format"-"New Rule".

Edit format rules dialog box:

Format styles: data bar

Check to show only data bars

PS: since our progress bar does not need to show numbers, if you check it here, the data will not be displayed.

Minimum, type: number, value: 0

Maximum, type: number, value: 365

PS: there are 365 days in a year, and the maximum value is 365, that is, when the entire cell bar is filled.

Fills: solid fill

Color: Brown

PS: the progress bar color can be set according to your preferences.

Finally, click the [OK] button.

As soon as the conditional format is finished, our progress bar will be done.

Now, as the button is pressed, the progress bar begins to fill up until it fills the entire cell.

After the introduction of the progress bar, the date in the picture shows how we did it.

The way to make it is very simple, that is, in a digital format. The date is the same, but we "put on a make-up" to make it look different on different occasions.

Numeric format to display your date data we use a C2 cell as the connection between the date and the value regulator. Enter the formula:

= "2023-1-1" + D22023Accord 1 plus the cell value of the numeric regulator, that is, when we press the numeric regulator, the number of days of the date also increases.

C3 cell needs to display Chinese month, formula reference date (C2 cell).

Select the cell and hold down [Ctrl+1] to open the cell formatting dialog box.

Enter in the custom type: [DBNum1] m month

Similarly, C4 cells need to display English months.

Custom type: mmmm

Similarly, the H3 cell needs to display the number of days of the date.

Custom type: d

Similarly, C6 cells need to display the days of the week.

Custom type: aaaa

Here, there must be a small partner worried, these types can not remember what to do?

You can rest assured that you don't need to remember the custom type, just look for it when you need it. (secretly tell you, I didn't remember it on purpose.)

We just need to find the style we need in the date, then switch to custom, and we can see the symbol corresponding to this format.

As shown in the motion picture, we find the day of the week style in the date, switch to custom, and the type is displayed as aaaa, that is, the format type of the day of the week is aaaa.

At this point, our calendar is done.

Finally, the font colors of the two auxiliary units are set to the background color, and they can be hidden. I don't have to teach you that, do I? 😏

At the end of this paper, I mainly explain three knowledge points about the production of calendar progress bar.

Use of numerical regulator

Data bar setting of conditional format

Display of date number format

Three simple knowledge points constitute the effect of this calendar dynamic progress bar. Everyone has learned it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, 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.