Conditonal formating - last row

Resolved · Low Priority · Version 2013

Tue 4 Sep 2018, 17:53 Delegate Meifong said...

Meifong has attended:
Excel Advanced - Formulas & Functions course

Conditonal formating - last row

Hi there :)

I have a excel file for loan amortisation.
Inside, depending of the assumption key in (number of periods to amortised, which is linked to the number of row), the number of row in the amortisation table will increase or decrease automatically, and the table will also color the first row till the "adjusted" last row.
How can I do such request by my self on a new excel spreadsheet, in general?

Thank you :)
Mf

Wed 5 Sep 2018, 10:40 Trainer Sarah said...

RE: Conditonal formating - last row

Hi Meifong

Thanks for your question!

As far as I can understand here, you are looking to create dynamic conditional formatting that moves as more/less data is added.

First, turn your data into a dynamic range by going to the Home ribbon>Styles section>Format as Table. Try using a design with no fill colours, as this can override your conditional formatting.

Now add your conditional formatting. Because the data is now a dynamic range, if more data changes and the row number changes, the conditional formatting will move accordingly.

Hope this answers your question, let us know if you have any further issues.

Kind Regards,

Sarah
Excel Trainer

Wed 5 Sep 2018, 10:40 Trainer Sarah said...

RE: Conditonal formating - last row

Wed 12 Sep 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips