Hot answers tagged

4

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.


4

(1) Copy the column you want to format to an empty worksheet (2) Select the column, and then choose "Remove Duplicates" from the Data ribbon (3) To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your lookup table. (4) In a new ...


3

Use the IF function: =IF(A1=B1, "Yes", "No")


3

Set B2 to =IF(A2=A3, 1, -2) and set B3 to =IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2))) and drag it down to B30 (or the last row that contains data, whatever that is).  This will evaluate to a positive number if this row is part of a duplicate value group and a negative value if it isn’t (i.e., if Column A contains a unique ...


3

I don't believe it's possible to apply a colour to numbers in a chart data table. However, I may have a workaround for you that means you can use a formatted table stored on a different worksheet, so that it won't affect the existing row and column heights on your worksheet. It's a bit of a hack, but will get the job done. Use Excel's Camera Tool To use ...


2

You can use conditional formatting to achieve this. You will use a formula to calculate todays date against the value in the cell (Which must be of a date format). The formula would be along the lines of: =(TODAY()-13)>A1 Depending on how many days you want to compare against, and which cell has your date. Note, this will trigger blank cells as they ...


2

With conditional formatting: Select the row containing your data go to: Home - conditional formatting - new rule - use a formula to decide... if your data is in row 1, starting at A1, then enter this formula: =COUNTIF($A$1:A1,A1)>1 specify the condition you need (italic, font colour...) Update If you want to format multiple rows based on hear ...


2

No, but you can fake it You can fake it but it takes some setup. It will work unless you need really big shapes. Here are the steps for creating n different conditional format shapes in Excel 2010. tl;dr Use 1 extra cell and 1 text box for every condition you want. Each text box is setup to show giant text with huge outlines and shadows, all the same ...


2

Use the formula =EXACT($S$5, K5) instead of SEARCH. EXACT makes an exact comparison. SEARCH is useful to search a string inside another, that's why you are getting all those fake results.


2

Use relative references (no dollar sign), by writing down a rule for the first cell of range (ex. B2>A2)


2

If you highlight column J and use Conditional Formatting's "Use formula to determine which cells to format" with the following formula, it should work. =COUNTIF(E:E, J1) This will count any cells in column J that match column E, then apply the formatting you choose. Don't forget to set the formatting you want if the formula is true.


2

Are you looking for this: =((TODAY()-A1)>30)*(B1="")*(A1<>"")


2

There is no way to move the icon set. However, you can achieve a visual effect that will help eliminate confusion with multiple columns displaying icons. Highlight the column which you wish to bring the numbers and icon closer together. Right Click. Select Format Cells. Click the Alignment tab if it does not appear by default. Locate the Horizontal Text ...


2

Go to the cell. Press CTRL+1 to open the Format Cell. Go to the Number Tab Under "Category", select "Custom" Where it says "Type", enter 0## #### #### This should ensure that you get your leading zero, and that the spaces are added. Excel also has a format under the "Special" category just for Phone Numbers. If you click that, then click "Custom" it ...


2

This works in Excel 2010. I'm not sure if it'll work in Google sheets. Select the range for which you want conditional formatting. Add conditional formatting using the following formula: (be sure to change the row from 1 to whatever the first row of your selected area is) =AND($D1<TODAY()+7,ISBLANK($I1)) Select whatever formatting condition you want ...


2

Your problem occurs because the formula you use looks at the columns with "relative reference". That is, unless you specify that the cells you want to check are always in columns F and G by using the dollar-sign ($), the cells the conditional formatting checks will be offset with the same amount of rows and columns as they are removed from the top left cell ...


2

As I understand your situation, there are three relevant columns: a column with a completion date (let's say it's in column L) column M, which may contain the string COMPLETED a column where you want your output (let's say it's column N) You use the formula =$M2="COMPLETED", which results in TRUE or FALSE. If I understand correctly, instead of ...


2

Let's suppose the letter (L/F) is in column B, then: Select the first row. Open Conditional Fomatting / New Rule. On type of formula, select "Use a formula" In the box put =$B1="F" On the button Format select the format that you want. With the row still selected use the Format Painter to copy this format to the other rows.


2

In Conditional Formatting, apply the formula =OR(B1="",MATCH("?",1:1,-1)>0) to column A. The MATCH portion will look for a question mark anywhere in any field in the row, not just a standalone question mark.


2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this: =IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral")) B1 = 0.83 (just my value for budget) B2 = YTD value entered by user B3 = MTD value ...


2

Here’s a tricky way (that might be considered elegant).  I’ll assume that < 83 is the “good” condition.  Use this formula: =CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good") Logical expressions like D1<83 evaluate to one of the Boolean values TRUE and FALSE, which are numerically equal to 1 and 0, respectively.  By adding ...


2

You can use the OR() function in your conditional formatting... =OR( CELL=$A$1, CELL=$B$1, CELL=$C$1 ) = TRUE etc When you select the rule for your conditional formatting you shouldn't select the simple rule Equal To. You need to select More Rules and then Use a formula to determine which cells to format -- Thanks to @User15 for the suggested ...


2

Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No. It's important to note that it will only change the apparent value in the cell and not the actual value. If you ...


2

Use relative formulas ($ or not) and set the Applies to range to the entire row. Just make sure that the first row in your formula and the first row in your Applies to are the same. (Screenshots show just some range example. Change to match your exact setup.)


2

Instead of subtotal using a sum on another column, you can use subtotal using counta to see if a (known-non-blank) cell is hidden or not. For example, if column A will normally be visible (unless the row is hidden) = IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" ) You can put this formula in a column that may be hidden, and it will still work. ...


2

Inserting and deleting rows does not cause conditional formatting to get fragmented. The cause is copy/pasting between cells or rows using the standard copy/paste. The fix is to always use paste-value or paste-formula. On the destination right click and the Paste Options section will offer 123 (values) and f (formulas). Don't copy/paste formatting as that ...


2

AFAIK, there's no perfect solution for this kind of task. The best practice depends on the contents of your sheet - especially, if you want to keep formulas, too. Generally, you can drop the rules regarding conditional formatting by saving the sheet as HTML, and re-loading the HTML into Calc. Of course, this will drop stuff like functions, too - just ...


2

I solved the same issue by creating a separate sheet and copying a link to the information in there. To do this, highlight the whole of the original sheet and copy it (Ctrl+C). Go to the new sheet and right click the first cell. Select the link icon in the Paste Options. Everything pasted will be unformatted, so you'll have to correct this. Empty cells ...


1

This solution doesn’t require you to take special action before and after each time you need to print the file.  It builds on the idea of using a “Print Area”, as suggested by Terence and Don Livezey.  Put the cell(s) that you don’t want to print outside the print area.  If that’s good enough for you, you’re done. But you might want the excluded cell(s) to ...


1

I don't have enough reputation points to comment on other posts, so I'll add an "answer" with some observations. I had the same or similar need, and there are pros and cons to various approaches. Setting print area: This can work depending on the layout of the sheet, but it doesn't handle a non-contiguous print area selection too well. Or, perhaps it ...



Only top voted, non community-wiki answers of a minimum length are eligible