conditonal formating last

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditonal formating - last row

Conditonal formating - last row

resolvedResolved · Low Priority · Version 2013

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

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

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 12 Sep 2018: Automatically marked as resolved.


 

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


Server loaded in 0.07 secs.