I am trying to format cells in excel which contain years i.e. 1999, 2000, 2001 etc but instead of a 4 digit year I want only 2 digit i.e. 99,00,01. Does anyone know if this is possible? Currently if I try to input the 2 digits I get 99, 0 and 1!!
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View RelatedThis is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies View RelatedI'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies View RelatedI would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies View RelatedI've set a conditional format to flag the row green if cell e2 has a Y or a N in the cell.
Currently i have the following conditional format formula is as
=IF($E$2="y",TRUE,FALSE) this is set to change row to green
=IF($E$2="n",TRUE,FALSE) this is set to change row to red
My Questions =
What i want to do is copy this formula through the 500 or so rows and have the formula adust to
=IF($E$3="y",TRUE,FALSE) For row 3
=IF($E$4="y",TRUE,FALSE) for row 4
=IF($E$5="y",TRUE,FALSE) for row 5
Ect.
I've tried using the formate painter but all it does is set all row to conditionally format dependant on what is in Cell E2. Thoughts on how to copy this formula is conditional format so i don't have to manually adjust it line by line. Doing this 500 times over would be a pain.
I would like to create a cell that will tell me if a dimension has failed.
I have a tolerance that the value must be between and if it is not then i would like a cell that will input an X
some cells in column C:C and row 6:6 contains information [are not empty]
Now I'd like to place a border on all cells in between, but only if both, the cell in C:C and the cell in row 6:6 of the corresponding cell in between are not empty.
this formula =H5*I5*J5/144*G5 in "M5" gives me board feet. I have another cell, "K5", with the species of wood in it using data validation and a species list. How can I format the color of "M5" based the value of "K5"?
View 9 Replies View RelatedA question regarding conditional formatting. Here is my current situation.
A
B
1
Name
DOB
2
John
01/01/01
3
Jack
02/01/01
4
John
01/01/01
I want it so that when there are 2 of the same names with the same DOB that all of those fields in A will be highlighted.
I want to conditionally format cells K8, L8 and M8 so that when cell C3 contains a 3 it causes the previously mentioned three cells to fill black.
I cannot seem to work out how to do this using the options available under conditional formatting. Is there another way?
Excel 2003 Scenario:
Column H contains text data as follows down the rows: BIKE/CAR/TRUCK.
Column N contains text data that would be entered free text by user down the rows; however, if the data entered in column N is not contained in column H it would warn the data is not contained in column H. Example: BOAT is not found in column H. Note; it could contain all text in column H for that row or just one word in column H for that row.
The goal is to recognize the text entered in column N is not contained in column H, warn and change the color of the text in column N to red.
I attempted to use Data/Validation, and Conditional Formatting; none seem to have a contain function.
Is there a formula or VBA I may use or any thoughts?
When using the format cell function, I have found some cells don't respond to the format until I enter and exit the cell (or edit). Other cells with the same data change when formatting is applied. The cell format is being changed from text to decimcal number. I have a few thousand records and about 30 cells and about half of them change when the format is applied. It would be impossible to manually edit each non-responsive cell to change the format.
View 3 Replies View RelatedIs there a way to build a drop down into a chart so that the user can select which items they want to view on a chart? Attached is a sample file below. Just wondering if there is a way to allow users to select to view all items in the chart or to select just a few at a time to compare and contrast.
View 4 Replies View Related1. A1 needs to turn red if a value greater than $2,000 is inserted.
2. A1 will turn back to white if either "John Doe" or "Jane Doe" is inserted in B1.
3. If no value is inserted in A1, A1 needs to remain white.
4. I'll need to do the same for A2-B2, and so on.
That's it. I tried this in C1: =IF(AND(A1>2000,B1="JANE DOE"),1,IF(AND(A1>2000,B1="JOHN DOE"),1,0)).
I then used Conditional Formatting in A1 to point to C1 and turn the red in A1 off or on based on the result. Hey, it worked! Problem is, A1 needs to stay white until a value is entered, and no value entered returns 0 (red), which oddly enough is exactly what I'm telling it to do. Surprise! How do I get A1 to default to white until a value is entered in A1?
I have been trying, without any luck to find a way of doing the following: If I enter a figure for example of 1.23, I would like a macro to then evaluate the cell and format it to two decimal places. But if the enter figure has only 1 decimal, to leave it at the standard 1 decimal format. I would like to be able to incorporate it in the following
View 4 Replies View RelatedI have several tables of results in which some cells contain a percentage value followed by an odds ratio in brackets - e.g. - 14.2 (1.3); some other cells would have ns within the brackets to indicate it is not significant. I would like to have the info in the brackets (and including the brackets) to be a slightly smaller font size than the percent value. Aside from manually highlighting each OR and changing the font size, is there a formula/format/function/macro I can create and then simply apply to the relevant cells? Thanks for any help you can provide. This whole area of functions and formulas is new to me!
I am creating a worksheet that uses a series of if formulas to determine the correct cell to use in a table outside the printing/viewing area. Something along the lines of "if X, Y, and z happens then use what is in cell AY34". I have the formula working, so I can get it to go pick up what is in the cell I want.
The problem I am having is that there is a heading to the text in cell AY34 that is bold, but the rest of the text in the cell is not bold. Is there any way to drag the formatting along with the data and not just the values?
A1 is not in any pattern colour. But if A1 is changed into, say, Red, B1 will be equils to D5+F5. Otherwise if A1 is changed into Blue, B1 will be equils to D6+F6. Can it been done by Excel 2003?
View 2 Replies View RelatedI am using Excel 2007. I want to put into a cell the symbol for the mean of a set of x values ie x-bar or x with a line over it. One way is to go to Word and use the equation builder, then copy it into Excel. However that restricts me to the default equation font, and I would prefer to be able to use my own choice of font. Is there a way to do this within Excel (perhaps by somehow combining two symbols, the x and a raised bar)? Alternatively can I do it within Word, without using the equation builder, and then copy it to Excel?
View 2 Replies View RelatedI have a dropdown box to select sizes.
e.g.
0929
0930
0931
09.2529
09.2530
09.2531
09.529
09.530
09.531
My issue is if I format the number to accept 4 decimal places the 09.529 number becomes 09.5290. I don't know what I can do to fix this as some need 4 decimal places and others will require none.
Is it possible to format a cells properties based on the value of the cell? I want to automatically format numbers that fall within certain ranges. For example, if I enter a number less than x, the number will be bold, in a blue color. If I enter a number greater than y, that number will be bold and red. All numbers outside the specified ranges would remain the default font. I would prefer that this property be set for a specified range of cells prior to entering any data, and automatically format as I enter the values.Can it be done?
View 8 Replies View RelatedI have spreadsheet that was sent to me but for some reason column A-D do not have the grey grid lines. They have the row lines but not the column lines. The data fits into column A but when I click on Center it all disappears and and I have to open the cell up nearly half the page to view the data again.
I have attached a screen shot of the problem. What I need to know is how to undo that so that it has normal formatting like a new spreadsheet. excell.jpg
My problem is the data and getting the formula's to read it. I copied and pasted the data from a pivot table and the columns are set to accounting two digits. But thats not what
shows in the cell. Each cell shows many digits. The formulas are working
now if I put the curser in the cell and select return. The values then
update to the accounting format it is supposed to be already and everything
works fine.
Hi All, I have Column I, which has a drop down menu in it, which if "WITH NOU" is selected - i would like it to change the colour of the text in column B, to a dark blue?
View 9 Replies View Related
