How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

conditional formatting on excel. what is it?

Resolved Question:

conditional formatting on excel. what is it?
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Jess M. replied 2 years ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Basically, conditional formatting in Microsoft Excel simply means that "a certain format" will be applied or NOT applied based on a certain condition. This "format" is referring to cell properties like font color, background, style and others.

For instance, if the sum of cell A1 and A2 is greater than 1000, A3 where the SUM is shown, will be rendered as bold, italics and color red. So, if the sum is less than the set value (1000), nothing will happen, just display the sum in normal text since the "condition" is not met.

That is basically the basic structure of conditional formatting in MS Excel. For more information and samples on how to use Conditional Formatting, please click on these links:

http://office.microsoft.com/en-us/excel-help/creating-conditional-formatting-formulas-HA001111661.aspx


http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html


http://www.contextures.com/xlCondFormat03.html

http://www.wikihow.com/Apply-Conditional-Formatting-in-Excel


Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4471
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


ok how do i use this to match values in one column to values in another? if not by this means, then how do I do that?

Expert:  Jess M. replied 2 years ago.
Thank you for writing back.

What exactly is your scenario? Is it something like:

If Col A matches with Col B, THEN? Do you want to show something in Col C like "match"? Please spcify your requirements so that I can help you further.


Jess
Customer: replied 2 years ago.


oh great, thanks also for replying....


well....column A has codes, Column B describes what these codes are, Column C are other codes some identical to those in column A. I want column D to have the description of the codes that are already known based on Column A and B. Does this make sense? I don't want to have to manually do it as there are hundreds........

Expert:  Jess M. replied 2 years ago.
That is quite complicated and I doubt if there is a better way to do that than manual Undecided

Col A = codes
Col B = code description
Col C = other codes
Col D = code description

Now with Col D, how do you intent to ask Excel to populate it? Is it something like it will look for the code in WHICH column and copy description from Col B?


Jess
Customer: replied 2 years ago.

i want excel to look at column A to see if the code in Column A matches the code in Column C then the associated description in Column B to be in column A. I am sure there is a way, but it is complicated...

Expert:  Jess M. replied 2 years ago.
Are you saying that "then the associated description in Column B to be in column A"? Is it Column A or Column D? You cannot change the values in column A where the comparison is done.


Jess
Customer: replied 2 years ago.

no column D

Expert:  Jess M. replied 2 years ago.
To confirm:

You want excel to look at column A to see if the code in Column A matches the code in Column C then the associated description in Column B to be copied in column D.

Is this correct?

Also, are you using Excel 2007 o7 2010?
One more thing, can you upload a dummy file for me to work on? Like just a single page with col A, B, C and D so that I can work from there? Just a dummy of your actual file if it is possible.
Customer: replied 2 years ago.


yes you got it. i am using excel 2010. here is the file....


 

























CODES1codes2descript??
42hiv41
91syphyilis43
311depressive disorder91
Expert:  Jess M. replied 2 years ago.
Do you have a concrete list of codes in the first column? For instance, all the KNOWN codes are already there in the first column so that we can use VLOOKUP function instead.


Jess
Customer: replied 2 years ago.


yes

Expert:  Jess M. replied 2 years ago.
Can you send a dummy excel file to me? Just the dummy file, NOT the original files containing all the information.

If it is possible, please upload the file to this site:

http://wikisend.com

After uploading, please give me the 6digit file ID or download link so that I can get your file.


Regards,
Jess
Customer: replied 2 years ago.


Here you go:


 


454892

Expert:  Jess M. replied 2 years ago.
I got your file. Please me a moment while I check it and do the steps based on your requirements.

I will keep you posted.
Jess
Customer: replied 2 years ago.


you are taking way too long. if you don't know how to do it just say so, but keeping me suspended like this is not that nice. i need a quicker answer as it is for something urgent.

Customer: replied 2 years ago.
Relist: Answer came too late.
I need a quicker answer
Expert:  Jess M. replied 2 years ago.

Have you tried VLOOKUP? In this specific requirements of yours requires a VLOOKUP function in order to automatically enter the description if there is a "match" with the code.

 

However, if there is no match, NA is entered to the cell and you can then manually enter the description if that description is NOT in the table of descriptions.

 

Here is the modified file:

http://wikisend.com/download/747890/dummy_file_mod.xlsx

 

Now, in that file, please note that I have placed a sample lookup table. Enter the code and see how the sample description changes as you enter the correct code. If the code is NOT in the table, NA is displayed.

 

Now, regarding the formula or how to do the vlookup, please do this:

  1. Click on cell E2
  2. Click on the FX button in the formula bar to bring up functions list
  3. Then enter the data as see in this screen:




  4. The formula used here is
    =VLOOKUP(C2,A2:B28,2,FALSE)

  5. Please make sure that in the formula is made ABSOLUTE before you drag it to copy to the other cells.
  6. To make the cell reference ABSOLUTE, please insert $ around the cell reference and the formula shall become:

    =VLOOKUP(C2,$A$2:$B$28,2,FALSE)



  7. Now drag the cell handle to copy the formula and you are done.

 

 

Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.


Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4471
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


There is no file in the link you sent me. I get an blubberish xlm file. Please fix so I can rate.

Customer: replied 2 years ago.


Nevermind. on second look it worked.........

Expert:  Jess M. replied 2 years ago.
I am glad that you got the file. If you have further concerns regarding the VLOOKUP function that I used to resolve your issue, please let me know.


Best regards,
Jess
Customer: replied 2 years ago.


i am trying it on another file it's not working. here is the link: I am trying to do it for the same column as before.


 


http://wikisend.com/download/363720/ja.xlsx


 


Please answer asap as I have to have results by this morning

Expert:  Jess M. replied 2 years ago.
I am downloading it now, then report back to you in 5 minutes. One moment please.

Jess
Expert:  Jess M. replied 2 years ago.
I immediately see a problem with your excel file. With vlookup, the lookup table SHALL NOT contain duplicate entry. Can you avoid duplicates? For instance, 1 are recorded two times. It will not work because Excel has no way of telling which label to show when the code 1 is seen or entered. Will it be "Cholera" or "Due to Vibrio cholerae"? I hope that explains.

Also, your initial attempt did not make it because you use the range A:B when in fact column B is hidden and what you really want to show up are the label that is in column C. Thus, I adjusted the range to A:C.

Here is the corrected file. Please take note that entries with duplicates, what is automatically displayed is the first occurrence of the entry. Please note also that those CODES that are NOT SEEN in the lookup table are automatically marked as #N/A. It simply means "Value not available".

Here is the download link:
http://wikisend.com/download/262718/ja_done.xlsx


Please be sure to rate my answer with 3 or more stars/faces if this helped.
Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4471
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


Thanks a lot, I look forward to give you plenty of excellent feedback. Let me know if you think I should post this as a new question, but could you tell me:


 


1. how to add 1 zero before every two digit number in the column and 2 zeroes for every 1 digit number. The minimum no of digits should be three. Not also there are some values with a preceding letter. Nevertheless, I also want to fix those numbers in this way.


 


2. Once I do this, I want like to add a decimal point to each number (or letter-number) in a way that it is the decimal is in the second place, e.g.


 


100 to 10.0 and 010 to 01.0


 


Attached is the column in question. I will rate all aforementioned answers seperately so don't worry.


 


http://wikisend.com/download/410642/srgcd.xlsx

Expert:  Jess M. replied 2 years ago.
I suggest that you post this as a different question so that other experts can try a solution.

I have tried it here and I was able to do the 3-digit parameter padding zeros if the number is XXXXX than 3 digits, like 001, 002, ..099, 100.

The real problem now is how to insert the decimal between the ones and tens digit. I will try further.

Jess
Customer: replied 2 years ago.


Yes, exactly. i was able to add the zeroes, but it is the decimals that is difficult. Eventually, I will remove the decimals, but before that I have to put so that the numbers can end up in the format that is appropriate.

Expert:  Jess M. replied 2 years ago.
Hi,

If you are trying to convert the numbers in Column A to something like 010 and 01.0, I have done it after so many attempts.

Please click on this completed file:
http://wikisend.com/download/473206/srgcd_done.xlsx

For Coolumn B, I just made a direct copy from Col A. That is, in B2, I made =A2 formula and copying the formulas all the way down. Regarding the padding of initial zeros, you just need to go to select B2 which contains 10, then click on the small down arrow in the Number group in the Home menu. In the left panel named Category, click on Custom then click on the 3 zeros. That will make your cell value fit into 3 digits MINIMUM and pad zeros for 2-digit entries. After doing that and if B2 now shows 010, drag the cell/fill handle to copy the format to the rest of the cells in Column B. Here is my screen shot:


Now regarding the decimals, here is the trick. In Column C, you just need to copy or reference the value B and divide it by 10 so that it will move the decimal one digit to the left. So in C2, enter =B2/10.

Now, copy the formulas all the way down column C. For columns with 2 digits, the initial zeros are removed. So you need to select C2 thenclick the small down arrow in the Numbner group. Click Custom in the Category then select the option 00.0. That will pad zero for 2digit cell values. Here is my screen shot:




Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4471
Experience: Computer Software Specialist for more than 10 years
Jess M. and 6 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 2 years ago.

See if this is what you're looking for

http://www.filesharesite.com/files/201209/1346853559Copy_of_srgcd1.xlsx/

 

 

 

 

Customer: replied 2 years ago.


precisely....but can you tell me how to do it since this will not be the first time i will need to do this

Expert:  John D replied 2 years ago.

Sure, one moment please...

 

.

 

Here you go.

 

http://www.filesharesite.com/files/201209/1346854353Copy_of_srgcd2.xlsx

 

The formulas are in columns B and C

 

Hope this is ok. Let me know if you have any questions

 

 

 

Expert:  John D replied 2 years ago.

Here you go.

 

http://www.filesharesite.com/files/201209/1346854353Copy_of_srgcd2.xlsx

 

The formulas are in columns B and C

 

Hope this is ok. Let me know if you have any questions

 

 

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

this file is what i meant...


 

Expert:  John D replied 2 years ago.

You're right. My bad!

 

Here, please check this one and let me know if it needs anything else

 

http://www.filesharesite.com/files/201209/1346875569Copy_of_srgcd2a.xlsx

 

Customer: replied 2 years ago.


Still not quite right. As you recall, i needed the decimal after the second digit, not the first....

Expert:  John D replied 2 years ago.

Ok, here is the decimal after second digit

 

http://www.filesharesite.com/files/201209/1346931208Copy_of_srgcd2b.xlsx

 

Let me know if you have any questions

 

 

Customer: replied 2 years ago.


this is great, but why when ever I copy and paste the column it starts to give me two decimals? may be the reason is i don't understand the program. could you explain it to me...

Expert:  John D replied 2 years ago.

There are two columns with formulas which are dependent on each other, so you have to copy both and know where to paste them. If you are not sure I suggest keeping the formulas where they are and paste the new data in column A instead

 

Hope this makes it clear and brings this issue to conclusion

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


Hi, i found a mistake in this file, but it's not your fault at all. Those numbers with only two digits should be considered as 93.0


 


Thus, 56 should be 56.0


48 , 48.0


etc.........


we previously made it as 5.6, 4.8 because we added a leading zero, which was not right.........


can you fix it? here is the link..........http://wikisend.com/download/247446/Copy_of_srgcd2b.xlsx

Expert:  John D replied 2 years ago.

I'm a bit confused. Are we working with two files and two different issues?

 

 

 

 

Customer: replied 2 years ago.

yes, two files, slightly different issues

Expert:  John D replied 2 years ago.

Ok thanks

 

Expert:  John D replied 2 years ago.

Ok here you go

 

http://www.filesharesite.com/files/201209/1346966827Copy_of_srgcd2b1.xlsx

 

Again, I did away with the first formula column since it is no more needed with this new requirement. I also allowed for up to 5 digit numbers

 

Hope this helps

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.


I rated you excellent because you did what I asked you, though for me, it has not solved the problem since the programmers at my workplace have given me wrong information. Looks like we'll have to figure this out on our own, for now.


 


Could you tell me: in this series of right justified numbers, where would the decimal place go?


 


12345


1234


123


12


1


 


Please note, as before, if the numbers are two we add one leading zero and if one, we add two leading zeroes.

Expert:  John D replied 2 years ago.

Thanks

 

Ok will look into your last remarks

 

In the meantime you have not rated the main question, so could you please do so if the solution is as per your request. Here is the link

 

http://www.justanswer.com/microsoft-office/73yws-need-figure-numbers-right-justified.html

 

Thanks


.

UPDATE

 

Ok I checked your last message but you need to give me the requirement so I can give you the solution. Specifically please show me how you need to have the above 5 codes formatted and I will be happy to provide the formulas which you will be able to apply to all the list. If in doubt I suggest that you ask the proponent or your workplace how they want these 5 codes formatted to make sure that the solution will per their advice

 

 

 

Customer: replied 2 years ago.


as before, but easier.


 


decimal alwaysafter the third character/digit. Leading zeroes have already been added so that is irrelevent

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
John D
John D
385 Satisfied Customers
Bachelor of Science - Engineering Consultant