Excel is a spreadsheet program from Microsoft that you need to use for various functions, like making a funds plan, revenue and expenditure information, and so forth. Whereas creating information in an Excel spreadsheet, there could be circumstances when you want to spotlight the rows with dates lower than a selected date. On this submit, we’ll see tips on how to spotlight rows with dates earlier than immediately or a selected date in Microsoft Excel.
Highlight Rows with Dates using Conditional Formatting in Excel
We are going to present you the next two strategies to spotlight the rows with dates sooner than immediately’s date or a selected date:
- By using the In the present day() perform.
- With out using the In the present day() perform.
1] Highlight rows with dates sooner than immediately’s date by using the In the present day() perform
The In the present day() perform represents the present date in Microsoft Excel. When you kind the =In the present day() perform in any cell and press Enter, Excel will present you the present date. Subsequently, this methodology is used to spotlight the rows with dates sooner than the present date. On the time I used to be writing the article, the present date was 11 November 2021.
The directions for a similar are listed under:
- Launch Microsoft Excel.
- Choose your entire vary of columns alongside with the rows.
- Apply Conditional Formatting to the chosen vary of rows and columns.
- Click on OK.
Let’s see the above steps in element.
1] Launch Microsoft Excel and create a brand new spreadsheet or open the present one.
2] Now, choose the vary of rows and columns for highlighting the dates (see the screenshot under).
3] Now, it’s important to create a brand new rule by way of Conditional Formatting to spotlight the rows with dates earlier than immediately’s date. For this, click on on the Residence tab after which go to “Conditional Formatting > New Rule.” Now, choose the Use a method to find out which cells to format possibility.
4] Click on contained in the field after which choose the date in the primary row. You will notice that Excel robotically detects and fills its location contained in the field.
As you may see in the above screenshot, the method that appeared in the field after deciding on the date in the primary row is =$B$1. This method signifies the place of the date on the spreadsheet, i.e., the primary row of column B. The $ signal in the method signifies that row 1 and column B are locked. Since we’re going to spotlight the dates in totally different rows however in the identical column, we have to lock solely the column and never the row. Subsequently, delete the $ signal earlier than 1 in the method. The method will then grow to be =$B1.
5] Now, kind after the method =$B1. If you use the In the present day() perform, Excel will robotically decide the present date and examine the info accordingly. The whole method ought to appear to be this:
=$B1
After that, click on on the Format button and choose your favourite colour for highlighting the rows. You will see that this feature below the Fill tab. You too can choose the Font fashion and border types for the highlighted rows. When you're finished, click on OK. You will notice your formatting fashion in the Preview part.
6] Click on OK in the New Formatting Rule window to use the conditional formatting to the chosen rows and columns. It will spotlight the rows with dates earlier than immediately’s date.
2] Highlight rows with dates sooner than immediately’s date or a selected date with out using the In the present day() perform
You need to use this methodology to spotlight the rows with dates earlier than immediately’s date or a selected date. We now have listed the directions under:
- Launch Microsoft Excel and open your doc in it.
- Write the reference date in a separate cell.
- Choose the rows and columns.
- Apply the conditional formatting.
- Click on OK.
Let’s see these steps in element.
1] Launch Microsoft Excel and open your doc in it.
2] To focus on rows sooner than a selected date, it's important to write a reference date for comparability in a separate cell. Within the under screenshot, I've written the reference date 10 October 2021 as a result of I need to spotlight dates earlier than this date.
3] Now, choose the rows and columns and go to “Home > Conditional Formatting > New Rule.” After that, choose Use a method to find out which cells to format. Now, click on contained in the field below the Edit the Rule Description part and choose the cell containing the date in the primary row. After that, Excel will robotically fill the cell location. It's a must to delete the $ signal as you've got finished earlier than. Now, as an alternative of typing the =In the present day() method, it's important to kind solely lower than image after which choose the cell containing the reference date (see the under screenshot).
4] Now, click on on the Format button and apply to format the rows and columns as you've got finished earlier than. When you're finished, click on on the OK button. Excel will present you the outcomes by highlighting the rows earlier than the reference date.
That is all about how one can spotlight the rows with dates sooner than immediately’s date or a selected date in Excel.
How do I autofill dates in Excel?
The AutoFill function in Microsoft Excel permits you to fill days, information, and numeric sequence simply. Merely kind a date in a cell and drag it down. After that, Excel will fill the dates in growing order robotically.
If you wish to fill the dates with a sure hole between them, let’s say odd dates in a month, it's important to kind two consecutive odd dates in the 2 consecutive rows of a column. Now, choose each the cells and drag them down. It will fill the cells with odd dates.
How do I spotlight rows in Excel if dates have handed?
You may spotlight the dates older than immediately or a selected date in Excel with and with out using the In the present day() perform. We now have defined each of those strategies in element above in this text.
Hope this helps.
Learn subsequent: How one can create and run Macros in Microsoft Excel.
