Conditional formatting in a Table

[candybg]

I have a table with metrics down the left side (one per row), along with a Threshold columns with a value for each metric that should be equaled or exceeded each month. Across the top of the columns I have dates.

Objective #1 -
I want to format the cells in each row that do not equal or exceed the threshold.

Currently I *am* able to apply conditional formatting row by row to accomplish what I need, but since this is a table, it SEEMS like I should be able to either apply the CF in one step, or somehow reference the column of the table, like other table functions, so that each row looks at the threshold for that row.

Attached is a demo of what I'd like to see (yellow highlights manually applied to show which values are below the threshold for that row.

Objective #2 -
When I add a new column, can the CF be applied automatically? So far I can't think of a way.

Thanks for any ideas!

 

[NARAYANK991]

Hi ,

See if the first objective is met ; as you add new rows / columns to the table , the CF rule is automatically applied to the new row / column.

Narayan

 

[candybg]

Narayank991 - Your formula for the CF was exactly what I needed but was unable to figure out.

For the second part, having the CF expand as the table expands to the right with additional columns, I haven't yet come across a way to have it happen automatically. I currently start a new column by typing the date in the next available column, with does expand the Table, but the CF doesn't carry over.

Anyone have any ideas for that?

 

[NARAYANK991]

Hi ,

When you work with tables , always insert columns by positioning the cursor where ever required and selecting the appropriate option from the menu which appears when you right-click.

Thus , if you position the cursor in the right-most column of the table and right-click , and then select Insert table column to the right , Excel automatically carries over the CF rules to the newly inserted column.

Similarly , if you position the cursor in the bottom right-most cell of the table , and press the TAB key , a new row is inserted , and the CF rules are automatically carried over to this newly inserted row.

Narayan

 

[bobhc]

Your problem may be due to the AutoCorrect Settings

Change the AutoCorrect Settings
NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

To fix the tables, so they automatically expand to include new rows or columns, follow these steps:

1. At the left end of the Ribbon, click the File tab
2. Then click Options
3. In the Excel Options window, at the left, click Proofing
4. In the AutoCorrect options section, click AutoCorrect Options
   
   
   
   
   
5. Click the AutoFormat As You Type tab – you've finally reached the settings!
6. Add check marks to "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns"
   
   
   
   
   
7. Click OK, twice, to return to Excel

Thanks to Contextures.

 

[candybg]

Thank you to both Narayank991 and bobhc! I didn't realize that, unlike adding rows below an existing table, tables didn't automatically expand to include the next column.

Bob - the autocorrect option for FILL FORMULAS IN TABLES was also new to me.

Thanks so much for your time!