How Excel makes a smart form of dummies

Automatic reminder before date expires

A company is to do leasing services, for equipment rentals have arrears of customers should be reminded in advance, but because of the number of orders to rent, manual access to the reminder information very laborious, so I want to design the expiration of automatic reminder function, according to the lease date, a few days in advance with color reminders, A cell that is less than or equal to 7 days from the collection date becomes yellow, the cell that is less than or equal to 5 days from the collection date becomes red, and the expired cell becomes green (Figure 1).

Select the column (column g) that you want to remind, select conditional format → admin rule, click New Rule in pop-up window, select "Use formula to determine cell to format", enter Formula =and (g1<> "", Days ($G 1,today ()) <=5) ", click the Format button to set the fill color of the cell to red, and other conditions similar to the setting.

Small tip:

The smaller the number of days to put on the above, so as to ensure that all conditions are met.

Replace cell input automatically

When you enter data into an Excel table, some text item data needs to be entered repeatedly (for example, a title in the teacher's registration form), and a step-by-step entry is cumbersome, and if you only enter 1 digits or letters, you can automatically replace the text item you want, which can be more convenient for you. If you enter "0" in the Title column cell automatically replaced with "High school advanced", enter "1" automatically replaced with "secondary level", enter other numbers automatically replaced with "secondary two", so that the efficiency of the input quickly increased.

When the title column is selected, select format → format cells, select Custom under Categories in the Number tab, and enter [= 0] High School; [= 1] secondary level; secondary school level two ". In this way, when you enter the corresponding number in this column, the corresponding text is changed (Figure 2).

or select file → options in Excel 2013 and click AutoCorrect in proofing in the pop-up window. Replace "enter" 0 "," to "enter" High School ", click" Add ", enter" 1 "at" Replace "," enter "middle school level". In this way, when you enter the corresponding number in the cell, it becomes the appropriate text (Figure 3).

Small tip:

The 1th method defines a finite type, but can constrain to a column, a row of cells, work on a particular cell, and a 2nd method type can define infinity, but it works for all cells. Excel Tutorials