A feature in Microsoft Excel, LibreOffice Calc, and some other spreadsheet programs that allows the user to automatically add color, text styles, and so on to certain cells or ranges of cells based on the data in the spreadsheet.

learn more… | top users | synonyms

0
votes
1answer
15 views

How do I get the cells with no dates to have no color until the date is entered? All of the unused cells are red

=DATEDIF(F2,TODAY(),"m")<=47 THE CELL TURNS NO COLOR =DATEDIF(F2,TODAY(),"m")>57 THE CELL TURNS RED =DATEDIF(F2,TODAY(),"m")>54 THE CELL TURNS PINK WITH RED WRITING ...
0
votes
2answers
26 views

In Mac Excel 2011, how to format cell as percentage or currency based on content of another cell?

As far as I can tell, the Conditional Formatting feature doesn't support percentages. You can format styles (color, font, etc.), but not the actual content presentation itself. I have two columns, ...
1
vote
2answers
18k views

Excel: Conditional formatting using icon sets?

I would like to format the Subtotal row such that within each Subtotal cell is the total and an "icon set" icon that will show if that month is greater than or less than the previous month's total. ...
0
votes
1answer
25 views

Excel formula related to conditional formatting

Column A has 10 numbers expressed as percentages and is conditionally formatted to highlight the top 40%. I want column B to show a "1" next to the corresponding highlighted top 40% of numbers in ...
0
votes
0answers
23 views

Excel 2007 Conditional Formatting On Formula Not Updating When Cell Updates

I have set the following conditional formatting rules: =$B97=1 Set Format (Custom) ">" @ On cell =$D97 =$B97=2 Set Format (Custom) "> >" @ On cell =$D97 =$B97=3 ...
0
votes
2answers
39 views

IF statement with multiple criteria and multiple possible results

I have a spreadsheet and I need a formula to give me one of three possible results (good, neutral, bad) based on data from two cells. MTD and YTD on budget would be good, MTD on budget & YTD below ...
1
vote
0answers
21 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting [migrated]

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
3
votes
3answers
24k views

conditional formatting for subsequent rows or columns

I have data in a range of cells (say six columns and one hundred rows). The first four column contains data and the sixth column has a limiting value. For data in every row the limiting value is ...
1
vote
1answer
15 views

Conditional Formatting in Excel via a different worksheet

I've been trying to figure out how to make a cell bold and color red based on conditions entirely in a different worksheet. For example, I'd like Sheet1!A1 to be bold and red when Sheet2!A1 = ...
0
votes
1answer
27 views
0
votes
2answers
43 views

Is it possible to have an Excel formula dependent on value input?

Is it possible to have Cell G24 recognize the number in D24, and do its math depending on the size of the number, using the in Cells B37 through B42? I have a basic spreadsheet for cataloging sales ...
5
votes
4answers
4k views

Colour coding blocks of rows using conditional formatting

My macros have been taken away; never to be used again! I'm gutted. I now need to use formatting to help me highlight the different users and their data. Doing this manually is a chore. Using Excel ...
0
votes
1answer
35 views

How do I superimpose a conditional formated heatmap based on one set of values over a different set of values in Excel 2013

I would like one cell to be formatted with a gradient color based on the value in another cell. For example, I would like cells A1 and A2 to be highlighted based on the values in B1 and B2. If I ...
1
vote
1answer
60 views

Align rows from different columns (which contain some cells that have identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table ...
1
vote
1answer
26 views

Lock color scale (convert to fixed)

I have used conditional formatting to apply a color scale to a range of values. Now I would like to permanently freeze those colors. I have poked around a bit, and can't find event the beginning of ...
6
votes
2answers
11k views

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

I have a spreadsheet with approx 300 rows. Each row has about 15 columns, and each cell contains a numeric value. Each row corresponds to a product. I want to get Excel, for each product, to ...
0
votes
0answers
15 views

Conditional formatting in Excel 2010 - Highlight a whole row in which a cell value falls between a specific day range

I need the whole row highlighted in yellow if the value entered in a cell (in Column C) falls between the 6th and 19th of any month or year, or in blue if it's between the 20th and the 5th. Thank you ...
0
votes
0answers
16 views

Excel - Conditional formatting retention through data validation in another sheet

I have applied conditional formatting in an Excel sheet (Sheet2) to change the font color in column A if the result in the adjacent cell in column B is "No." I then used this conditionally formatted ...
8
votes
3answers
42k views

Color Cell Based On Text Value

An Excel column contains a text value representing the category of that row. Is there a way to format all the cells having a distinct value a unique color without manually creating a conditional ...
1
vote
1answer
29 views

Use Conditional Formatting on a Cell Based on matching a value in a range

I want to change the cell background of a cell (M16) if the number in it matches one of the numbers in a table of random numbers (M3:Q12) Table of numbers - M3:Q12 Cell I want to format is M16. ...
1
vote
1answer
27 views

How to get Excel conditional formating to recognize boolean operations

I have constructed a spreadsheet to color code incoming scientific data based on different levels of criteria. My problem is with data coming in as 'less than'. For example, if the data shows ...
0
votes
2answers
32 views

Conditional formatting to mark rows depending on contents

I have an excel sheet that for the forseeable future will remain as a Work In Progress regarding a project. To easily be able to keep track of rows that need some work I would like to create a macro ...
2
votes
2answers
35 views

A Conditional Format to change the background color but keep the Date format

I have and OpenOffice Calc document that has a "table" with Text, Numeric and Date columns. I have a conditional format making every odd row have a different background color so it will be easier to ...
5
votes
11answers
40k views

Prevents cells from printing in Excel

Is it possible to prevent the contents of specific cells (or entire row or column) from printing in Excel? I need to maintain the cell size. I've been setting the text color to white before printing, ...
1
vote
2answers
2k views

How to highlight calendar cells based on given start and end date

For a while now, a coworker has been using a chart similar to the one shown below and wants to automate it (i've already added the days before/after due date). The chart is printed or worked on at ...
1
vote
1answer
39 views

Conditional formatting based on specific cells

Is it possible to set conditional formatting based on a value written in a specific cell? I would like to colour the rows of saturday and sunday and not colour the working days. The condition is ...
1
vote
1answer
28 views

Dealing with absolute references in a conditional formatting macro

Say I have the following simple data in Excel: A B C D E F G 2 1 3 2 5 4 2 4 2 1 3 2 5 2 1 3 2 4 I want to set up ...
0
votes
3answers
52 views

Adding a date to a formula in excel [closed]

=$M2="COMPLETED" I am using this formula to shade a row when COMPLETED is written in column M, but I would prefer the 'date' or 'COMPLETED and the date' but can't seem to work out how to do this. ...
-1
votes
1answer
62 views

Formatting - conditional - vlookup- values from one cell to another different worksheets based on value

I would like to be able to enter a part# in column L of sheet 2 and a formula would look through column A of sheet 1 for a match, and then return their price from column F of sheet 1 into column K of ...
1
vote
1answer
195 views

Excel VBA - Conditionally format a cell using 2-color scale based on 2 values (max/min) on other cells

I'm trying to write VBA codes to conditionally format (2-color scale) cell A1 based on its value that between is >= cell A2 and <= cell A3. I'm using cells A2 and A3 so I can change the specific ...
0
votes
1answer
27 views

EXCEL: Icon Conditional Formatting Based on Range of Values

I am creating a spreadsheet for work and I need to format cells such that I get a green check mark when the value in the cell is between 100 and 101, yellow ! if it is between 97 and 100, and red X if ...
1
vote
3answers
15k views

How to hide number but keep Excel cell background color?

I got a sheet like the below figure. I want to hide all the number and just keep the background color. How can I do it?
8
votes
3answers
9k views

How do I keep Conditional Formatting formulas and ranges from automatically changing?

I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things ...
1
vote
1answer
43 views

Excel: Conditional formatting not filling in cells if adjacent cell is filled

I am trying to fill a pair of cells in if both are blank. Which works unless the cell next to the pair is filled in. How do I go about resolving this issue? Formula: =AND(ISBLANK(F2),ISBLANK(G2)) ...
0
votes
1answer
37 views

Combining the ISBLANK and Date function

I have been searching through forums and can not figure out how to succesfully combine the two functions ISBLANK and a date function. What I need to do is use this formula in google sheets for ...
0
votes
1answer
77 views

how to highlight the cell with the max value

given a range of non adjacent columns (not sure if it matters if they're adjacent) i.e F1:F100,J1:J100,N1:N100 for each row i'd like to highlight the cell with the maximum value. i tried ...
0
votes
0answers
31 views

How do I apply conditional formatting to many workbooks?

I have 120 similar excel workbooks. They each have one sheet containing a small customer databases. I want to apply conditional formatting to all these workbooks. I can do it in one workbook. Is ...
1
vote
0answers
47 views

How do I use an IF-THEN or Condition Statement for Excel 2013?

What would the condition statement be if I wanted all my sold items to go red in column F, but leave the ones un-shaded that have the word "Received" also in column F? The Headings of my Excel sheet ...
2
votes
1answer
29 views

Excell 2007 conditional formatting

I have created an excel spreadsheet where in each cell there is a formula which gives a value with two decimals. I need to use the conditional formatting to highlight the cells in which the second ...
1
vote
1answer
86 views

How to use Conditional Formatting to change text color to match conditional fill color

I have used the Conditional Formatting to change the Cell fill color based on the percentage number. However, I would like to hide the text so I just have a bunch of colored squares with no value ...
26
votes
8answers
117k views

Find Items in one column that are not in another column

I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A. What's the quickest way to do this?
0
votes
0answers
86 views

Excel: Conditional formatting for column merge

I'm looking to create a timetable that is formed by combining material from several worksheets. It does not seem to be possible to automatically consolidate text from several worksheets, but it is, of ...
0
votes
1answer
260 views

Conditional formatting in Excel 2013 based on inequality between two cells

This is part of a problem for college for which I can't find a solution. Use conditional formatting to format, in blue and bold, the value in column 8 for which the value in column 5 is greater ...
0
votes
1answer
64 views

Finding Clustered/Consecutive Values in Excel

I am trying to find values in an Excel spread sheet where numbers greater than 0 will be highlighted. However, those highlighted must be clustered, consecutive numbers which should be (at least) three ...
0
votes
4answers
1k views

Excel function determine if a cell is visible

In conditional formatting I want to detect if the row above the current cell is hidden or visible. How can I detect if a cell is visible or not? The only hack I can think of is: Create a column ...
1
vote
1answer
746 views

How do i right align an icon set arrow under conditional formatting so that both number and arrow are on right side of cell neatly together?

Cell displays a conditional format arrow Icon set which is on far left side of cell. The number in that cell is right aligned and I want to bring the arrow directly next to the number. I want the ...
2
votes
3answers
5k views

Vertical Histogram in Excel with Conditional Formatting

I'm able to do data bar conditional formatting in Excel 2010, but I can't figure out how to switch the bar axis to make it vertical. Is there any way around this? Can I simulate a vertical histogram ...
0
votes
0answers
20 views

Conditionally change colors of events in calendar on mac?

Is it possible to conditionally change colors of particular events in calendar for mac ? the same way week cal on iOS works? Maybe with applescript? I havent found any apps for mac that can do that. ...
0
votes
1answer
1k views

How to format cells between two dates with a colour based on a value in vba?

(apologies if this question is difficult to understand, i'm unable to post an image) I've tried finding a solution for this online but haven't had much luck. Ideally, I want to use conditional ...
0
votes
3answers
68 views

How to highlight where a number in another cell falls in a column? Bracket it with the next highest and next lowest?

This is probably an easy fix. I have one cell (Cell B8) with say the number 2000 in it. I have another column of 6 numbers (Cell A1 to A6) (Say 4000, 3050, 2100,2100, 1150, 200) that will change with ...