Free Microsoft Excel 2013 Quick Reference

For the life of me I haven't been able to figure out a proper way to do this besides manually updating each cell. This seems like such a simple task but hard to figure out. After 1 year of manually doing it I decided to ask for help and see if there is a faster way to do this because I will be doing this for more years to come lol. So here is my test excel sheet:

A1 Value = "167"

B1 Value = "0"

I click on B2 and click on Conditional Formatting and set it up so that [IF B1 value =A1 value then B1 bgcolour is Green]. Simple right? Yes.

Heres, where it gets tricky for me...

How do I copy and paste this conditional formatting so that I can continue this pattern so that [IF B2 value = A2 then bgcolour is Green] ?

If you tell me to click on the fill button and drag down, it won't work because Excel will screw up the conditional formatting so that

[IF B2 value is A1 then bgcolour is Green]

[IF B3 value is A1 then bgcolour is Green]

[IF B4 value is A1 then bgcolour is Green]

[IF B5 value is A1 then bgcolour is Green]

The conditional formatting does not want to increase A1 to A2, A3, A4, A5 etc.

What is the best way to copy and paste my conditional formatting formula so that the conditional formatted cell checks the cell to its left to verify data?

I have included a visual example of an attached XLS file to make it easier for you to understand and try out yourself.

Thank you!

A1 Value = "167"

B1 Value = "0"

I click on B2 and click on Conditional Formatting and set it up so that [IF B1 value =A1 value then B1 bgcolour is Green]. Simple right? Yes.

Heres, where it gets tricky for me...

How do I copy and paste this conditional formatting so that I can continue this pattern so that [IF B2 value = A2 then bgcolour is Green] ?

If you tell me to click on the fill button and drag down, it won't work because Excel will screw up the conditional formatting so that

[IF B2 value is A1 then bgcolour is Green]

[IF B3 value is A1 then bgcolour is Green]

[IF B4 value is A1 then bgcolour is Green]

[IF B5 value is A1 then bgcolour is Green]

The conditional formatting does not want to increase A1 to A2, A3, A4, A5 etc.

What is the best way to copy and paste my conditional formatting formula so that the conditional formatted cell checks the cell to its left to verify data?

I have included a visual example of an attached XLS file to make it easier for you to understand and try out yourself.

Thank you!

- Applying conditional formatting to emtpy cells in a spreadsheet
- Applying Conditional Formatting to Multiple Worksheets - Excel 200
- How to apply a formula to all cells in a column
- How to apply similar formula to all cells in a column?
- Conditional formatting to multiple cells
- Copy format to visable cells in a column
- Apply formula to all cells in a column
- Combining multiple cells in a column into one cell on top of one another?
- Can I add a quantity to every cell in a column using a formula?
- How to add a fixed amount to all cells in a column
- Adding a fixed value to the celles in a column
- Apply conditional formatting to a specific range of cells using VBA
- Applying conditional formatting to Mutiple cells in one shot? Please Help
- Macro to Add Conditional Formatting to multiple cells
- Apply conditional formatting to individual cell Excel 2007
- Conditional Formatting - Help!
- How to count cells in a column that text is red
- Adding number to each cell in a column
- Does cell contain a formula or not?
- Create a drop down list for each cell in a column
- Merge cells in a column based on cells highlighted in other column
- Apply Conditional Formatting to multiple Rows dependant on data in one Column
- Applying conditional formatting to cell based on another cell's in
- Using a macro to apply conditional formatting to multiple columns

blank in a spreadsheet, but I don't know how to represent a blank cell - I

have tried " " and "" with no luck. I am applying two conditions to an

entire column - one that says if the value is not equal to "Yes" (there can

be any number of alternate values in the cell) - then fill with red, I want

the second condition to be that if the cell is blank then no fill. The end

result I am looking for is that values of Yes and blank cells will not have

the conditional formatting applied - all others will be colored red. Maybe

I'm going about it backward.

Can this be done?

Thanks for any insights,

Gail

in Excel 2007?

In 2003, you could select mutliple worksheet tabs, apply conditional

formatting to one worksheet, and it would populate to the other worksheets

simultaneously. This function is not available in Excel 2007. How can this

be done in Excel 2007?

Thanks.

clicking on the bottom right corner and dragging down through the rows.

What I need now is to apply the formula to ALL cells in a column. I don't

want to have to hold and drag down through 65000 rows.

Is there any menu option I can use to write the formula I need in cell A1,

then have it automatically apply to cells A2 through A65000 (or whatever the

end row value is).

Thanks for any help.

Gillian

=IF(VLOOKUP(C3, Sheet2!A3:B5, 2, FALSE) = "yes", A3*0.05, " ")

The C column is the vendor entry

Sheet2 is the vendor list

The A column is the dollar amount entry

For this particular row, it takes the vendor(C3), locates it in the A column of my vendor list (Sheet2!) and returns a yes or no from the B column. If it's "yes" then it calculates a 5% tax on the dollar amount(A3), if not it leaves it blank.

For the next row, the formula should look like this

=IF(VLOOKUP(C4, Sheet2!A3:B5, 2, FALSE) = "yes", A4*0.05, " ")

only the vendor(C4) and dollar amount(A4) change not the lookup table.

However, if I click on the bottom right corner of the first cell in the tax column and extend it to the next cell down so as to copy the formula it ends up with this

=IF(VLOOKUP(C4, Sheet2!A4:B6, 2, FALSE) = "yes", A4*0.05, " ")

You will notice that the lookup tables parameters have changed from A3:B5 to A4:B6.

How can I apply the same formula to all cells in a column and control which variables are affected?

I want to create a conditional format which will change the color of a cell

depending on the user's choice. The cells which I am referencing are

scattered througout the worksheet (i.e. E84, E88, E90, etc.) How can I set

this conditional format to multiple cells? Or is there a way to set the

conditional format to text within a cell? any suggestions would be greatly

appreciated.

Thank you,

How do I format only the visable cells?

Thanks for your help

My first time in using Excel with calculations. Problem is as follows:

I have three rows: First: beginning date, Second: end date, Third: time difference.

third is calculated by obviously subtracting second from first. My problem is, I want to apply this formula to all cells in third column. i.e. :

c3 = b3 - a3

c4 = b4- a4

c5 = b5 - a5

...

cn = bn - an

I need something like a for loop. How do I do that without manually writing the formula seperately to all cells in my third column?

Thanks in advance..

I have been looking everywhere on how to combine multiple cells in a column so they end up in one cell on top of one another. The script or command would also need to know when there is a space in one of the cells and stop, then start all over once the next set of data is found. The amount of cells in the column will vary.

Example:

Cell A1: data1

Cell A2: data2

Cell A3: data3

Cell A4: data4

Cell A5: data5

Cell A6: data6

Output:

B1:

data1

data2

data3

B4:

data4

B5:

data5

data6

Any assistance would be appreciated, thanks.

I need to be able to add a quantity of 3 to every row in a column.

Any help would be appreciated.

Thanks!

I am trying to figure out how to add a fixed amount in percent (ie. 10%) to all cells in a column.

Here is an example:

Price:

1.00

2.00

2.50

3.00

3.00

5.00

I want to add 10% to everything in the above column to give:

Price:

1.10

2.20

2.75

3.30

3.30

5.50

The column has thousands of items so manual editing is not something I can complete this year ;-)

If this involves some kind of formula would this prevent me from exporting as a CSV file - or would the CSV simply contain the numbers rather than the underlying formula?

Apologies if this is a simple question but I am stumped :-?

Any help appreciated!

Thanks,

S

I am trying to create a conditional format using VBA and apply that format to several rows in 2 columns. When I walk through the code the formula string contains the correct cell reference, however when the macro completes and I review the conditional format for the cells in the sheet the cell reference starts 9 rows and 1 column past what I want. I want the formula in cell A10 to be "=LEN(A10)>0" but when its executed I get "=LEN(A19)>0"

Below is the code I've created. (Note: wrkSht is a worksheet variable initialized to the correct sheet. This code is in a method inside of a class module)

VB:firstRow = 10 firstCol = 1 lastRow = 500 lastCol = 3 Set rng = wrkSht.Range(wrkSht.Cells(firstRow, firstCol), wrkSht.Cells(lastRow, lastCol)) rng.FormatConditions.Delete strFormula = "=LEN(A10)>0" rng.FormatConditions.Add Type:=xlExpression, Formula1:=strFormula rng.FormatConditions(1).Interior.ColorIndex = 24 Set rng = NothingIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The condition is this,

If the (1-Jan-07) Google ranking No. is greater than (1-Dec-06) the number is displayed in red & Bold

If the (1-Jan-07) Google ranking No. is lesser than (1-Dec-06) the number is displayed in green & Bold

Actually I applied the condition for one cell, How to apply this condition to mutiple cells/other cells using drag & drop or one shot.

If i select the conditional cell and drag to apply to other cells, the condition is applying but, the corresponding cell no. is not changing

Please Help!

Kind Attenation Forum Administor: I tried to attach a Excel file, but it is saying invalid file.

Regards

Ramki

I'm trying to add conditional formatting to my worksheet using a macro.

The condition is:

Look at all cells in Column B

If the cell value equals "Capacity" then change the background color of that cell and the rest of the cells in that row (e.g. from Column B to O).

E.G. if cell B6 = "Capacity" then change the background color of cells B6 to O6 to light blue

If cell B7 = "Availability" then change the background color of cells B7 to O7 to red

This should apply to all cell in column B and change the background color of appropriate cells.

The code I have so far is

VB:This works to a certain extent but only changes the background color of the cell in column B, but should change Column B to OColourConditioning() ' Dim rngToFormat As Range Set rngToFormat = ActiveSheet.Range("B6:B100") 'first, clear any old Cond format for these cells rngToFormat.FormatConditions.Delete 'add cond 1: if = "Capacity" rngToFormat.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlEqual, Formula1:="Capacity" rngToFormat.FormatConditions(1).Interior.ColorIndex = 34 'add cond 2: if = "Availability" rngToFormat.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlEqual, Formula1:="Availability" rngToFormat.FormatConditions(2).Interior.ColorIndex = 3 End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

See attachment

Can anyone help please

Andy

conditional formatting for one cell. In Excel 2007, it seems I have to choose

rules for the entire sheet....I don't want to do that. I just want to apply

a conditional format to one individual cell. How can I accomplish that?

--

Thank you all for your help!

I have a college asignment and I need to apply conditional formatting to show if a value has increase by more than 3%. I have cells similar to the following:

E---F

30 27

98 95

48 47

40 35

I need to apply conditional formatting to the cells in E column, if they have increased by more than 3% to that of the values in the F column.

Any suggested solution would be greatly appreciated.

I typed the list on worksheet 2 and named it "TaxCatagory" in the box next to the function sign. I then went to worksheet 1 (where my spreadsheet is), clicked on the header cell of a blank column, went to DATA > VALADATE > LIST > =TaxCatagory, and enter. NADA. I then tried highlighting the whole column of cells and repeating the process. NADA again. I get a warning sign, but I am not savy enough with the terminology to know to what it refers. Help would be great!

Sorry for being such a neophyte!

I am trying to write a code to merge cells in a column based on the cells highlighted in another column and then add data to it. I use the .merge function but gets all messy.

Below is an example of what i am trying to achieve.

A B

---------------------------------------

Yellow

Yellow

Yellow 5

Yellow

Yellow

---------------------------------------

Green

Green 2

---------------------------------------

Red

Red 3

Red

---------------------------------------

Any help would be highly appreciated.

thanks in advance.

I have a dataset that needs to have the following macro/Conditional Formatting applied to each subsequent row:

Sub CondFrmt()

Range("C9:EZ9").Select

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=$B$9"

Selection.FormatConditions(1).Interior.ColorIndex = 40

End Sub

Basically, I want a cell in a row to be filled if it is 'equal to or greater than' the cell value in Column B of the same row. This Column is subject to being changed by another sheet and the way I have been doing it before was via the menus and the formatting remains correct when the data in Column B is changed, but the row number is due to increase, so want an automatic way to achieve this!

My problem is, I can't figure out how to get it to apply this to each row (until there are no more rows). Putting a loop in would also increase the time, which may be a problem in the future (but may suffice for now)...

It should be really obvious and I'm sure I've done something like this before, but I'm suffering from a mental block - probably because it's Friday!

I appreciate any help that can be provided.

that same cell's info BUT what I want to do now is something like this...Can

I apply conditional formatting to cell A3 based on the information in cell

B3? If so, how do I do that?? I completely suck at writing formulas so, if

you know the answer, can you help me with how to write the formula, too?

Basically, I want to apply formatting to A3 if B3 has a value greater than

zero. Thank you!!

I am new to this forum and completely new to Excel macros so I hope someone can advise me!

I have a worksheet consisting of 85 columns of reaction time data, each column representing a variable or condition.

I wish to highlight outliers - in this case, those that are more than 2.5 standard deviations away from the column mean.

So, under each column I've calculated the mean, standard deviation, and standard deviation x 2.5.

To highlight outliers in the first column I did the following:

1) selected the range of cases (a2 - a40)

2) applied conditional formatting (red type) if values were greater than the mean + sd*2.5 or less than the mean - sd*2.5 (the mean and sd*2.5 values are taken from the previously calculated values at the bottom of each column.

Which did the trick. However, as I have 85 columns and then over 30 workbooks with an identical format, it would be great to automate this process.

I've never used macros before in Excel, but I tried recording a macro while completing the above actions for a few columns:

Range("A2:A40").Select Application.CutCopyMode = False Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$A$45+$A$50" Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$A$45-$A$50" Selection.FormatConditions(2).Font.ColorIndex = 3 Range("B2:B40").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$B$45+$B$50" Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$B$45-$B$50" Selection.FormatConditions(2).Font.ColorIndex = 3 Range("C2:C40").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=$C$45+$C$50" Selection.FormatConditions(1).Font.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$C$45-$C$50" Selection.FormatConditions(2).Font.ColorIndex = 3 End SubSo, my question is, other than manually recording a macro and repeating this action for the entire 85 columns, or manually editing the code to apply to the different columns, is there a simpler way to achieve this? The range of cells I want to highlight is the same in each column. Also the position of the mean, sd and sd*2 is the same in each column. I rather suspect that they wouldn't be necessary at all to someone with a modicum of macro experience.

Any suggestions would be greatly appreciated. Many thanks for reading and do let me know if any further information is required.