Use Excel grid to implement project progress chart

This article brings you relevant knowledge about excel, which mainly organizes the related issues of using Excel grid to implement project progress chart. Set conditional formatting based on this form, which can be displayed intuitively. Let’s take a look at how many remaining grids are left. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

If you are given a table with 30 rows × 30 columns like the one below, what will you do? What comes to mind?

If each cell represents a month, the 900 cells in this picture are enough to accommodate most of life.

Set conditional formatting based on this table, which can visually show how much remaining grid is left in life.

Step 1:

Select cells A1:G1, set merge cells, and then fill in your date of birth.

Select a cell area of ​​30 rows*30 columns starting from A2, adjust the row height and column width, and add borders.

Step 2:

Set conditional formatting

Select cells A2:AD31 and click [Start] → [Conditional Formatting] 】→【New Rule】, follow the steps shown in the figure to set conditional formatting.

The conditional format formula is:

=(ROW(A1)-1)*30 COLUMN(A1)

Let’s briefly talk about the meaning of the formula:

Let’s talk about DATEDIF($A$1,TODAY(),” m") part, used to return the number of whole months from the date in cell A1 to the current date of the system.

Let’s talk about the (ROW(A1)-1)*30 COLUMN(A1) part:

Copy the formula to the right, COLUMN(A1) will become COLUMN(B1), COLUMN( C1)...The function is to get the sequence value starting from 1 and increasing by 1.

Copy the formula downwards, (ROW(A1)-1)*30 will become (ROW(A2)-1)*30, (ROW(A3)-1)*30..., that is Each time the formula is copied down a row, the sequence value is incremented by 30.

The effect of this part of the formula on the cell is as shown below:

Use the number of whole months from the date of birth to the current date (that is, the number of months Age) is compared with the above set of sequence values. If the sequence value is less than the age in months, the entire formula returns the logical value TRUE and executes the specified conditional format.

Save after setting is completed. As long as you open this workbook in the future, the red area in the cell will automatically increase month by month, and the blank area will decrease month by month...

Related learning recommendations: excel Tutorial

The above is the detailed content of Use Excel grid to implement project progress chart. For more information, please follow other related articles on the PHP Chinese website!