Conditional Formatting - Leave Your Smarter Sheets With Colors

210
65234
Conditional Formatting in Excel Colors
Conditional Formatting in Excel Colors
Excel Spreadsheets

Conditional formatting, using colors in Excel is undoubtedly a feature that greatly facilitates the interpretation of spreadsheet.

For example, in which of the images below is the result of the spreadsheet clearer?

conditional formatting excel

Ao develop your spreadsheets, you can paint cell by cell manually, which is a laborious practice and subject to human error, or use conditional formatting. This feature of microsoft excel allows you to create conditions for your cells to be formatted automatically.

Conditional Formatting - Leave Your Smarter Sheets With Colors

We will exemplify the formats below, but the conditions can be given through Ranking, - greater values ​​of a color and a gradient until the smaller ones - Conditional and even through Formulas. In addition, color cell and text formatting, cell status bars, and custom icons can be chosen. See the example below:

examples of conditional formatting

From now on, in this post, we'll be teaching you how to use the Conditional Formatting feature in a practical way.

See also: Free ebook with conditional formatting tips!

1) How to use the conditional formatting feature?

It is quite simple to use the conditional formatting feature. It is on the "Home Page" tab within the "Styles" sub-tab, as shown in the image below.

excel color conditional formatting

menu excel conditional formatting

To start the practice example, create a list of 15 rows with values ​​from 1 to 10 in excel, as shown in the example below:

conditional formatting excel

The first item in the "Highlight Cell Rules" menu is to create automatic formatting from pre-set conditions. Its sub items are:

Excel Course - Conditional Formatting

a) It is greater than ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values ​​greater than this value.

b) It is smaller than ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values ​​less than this value.

c) Is Between ... - you set two values ​​and you can choose cell formatting (font, font color, borders, and background color) for all values ​​within the range between them.

d) It is the same as ... - you set a value and you can choose cell formatting (font, font color, borders, and background color) for all values ​​equal to it.

e) Text Containing ... - you define a text snippet and you can choose cell formatting (font, font color, borders, and background color) for all occurrences of this snippet. For example, by choosing "larger" in a list, cells with "greater", "greater", "greater", and more will be affected by the chosen formatting.

f) A Date Occurring ... - Same thing of item "equals ...", but with dates.

g) Duplicate values ​​... - any duplicate values ​​will be affected by the chosen formatting.

When selecting your list and clicking on "Conditional Formatting"> "Highlight Cell Rules"> "Is Greater Than ...", you should see the following form or something similar depending on your version of excel:

formatting conditional formatting

When you click on any sub-item, in addition to the "Is greater than ...", the form that will open is very similar in all cases varying only the pre-established rule.

The second sub-item of the conditional formatting menu "First / Last Rules" works the same way. You choose pre-set criteria to form formatting conditions, but this time, you'll decide whether the rule will focus on 10 higher values, 10% higher, 10 lower, 10% lower, and above the list average and below the list average.

Conditional Formatting - Leave Your Smarter Sheets With Colors

The next 3 menu options are discussed earlier in this post: "Data Bars", "Color Scales" and "Icon Sets". Just to show it again, now that you already know how to use Conditional Formatting, I will exemplify the 3 in the table that we created. You can choose custom layouts and these are very self-explanatory. In my case, the result follows below:

conditional formatting excel

The last items of the "Clear Rules" menu, "Manage Rules" and "New Rule" function as a custom rule manager. In this case, you are free to create as many rules as you want and with the conditions you want. This is good because you are not conditioned by the rules pre-established by Excel itself.

To start, click on "New Rule ...":

You should see a frame very similar to this one in the image. Note that in "Show formatting rules for:" you can switch from "Current Selection" to "This Worksheet" and view all rules created in the worksheet. In the "Applies to" column you will see where the rule in question is being addressed.

When we click on "New Rule", we can see a new frame, this time for creating custom rules from conditions thought by you:

excel conditional formatting 4

In this chart you can repair the following facts:

- "Format all cells based on their respective values" - similar to "Enhancing Cell Rules" we saw earlier, but you can customize the colors and scale format.

- "Format only the first or last values" - similar to "First / Last Rules" that we have already seen in this post, but with customizable values ​​and colors, this time.

- "Format only above or below average" - same as the previous one.

- "Format only unique or duplicate values" - we have also seen this in "Enhancing Cell Rules".

- The only new features are "Format only cells that contain" and "Use a formula to determine which cells to format". We will focus on exemplifying them.

Excel Course - Conditional Formatting

(a) "Format only cells containing"

example conditional formatting rule

In this example, you are free to create your own formatting conditions. In the first box you select what will be analyzed in the cell among: Cell Value, Specific Text, Empty, No Empty, With Errors and No Errors. Each choice will condition you to a fill type of the rest of the condition.

For example, when choosing Cell Value, you can select from the following options: Is Between, Is Not Between, Is Equal To, Is Not Equal To, Greater Than, Less Than, Greater Than Equal To Minor Or Equal To. From this choice, simply fill in the reference values. You can choose a value or a cell that contains a value for the condition to make the comparisons in your list and format it.

By clicking "Format ..." you can customize the colors that will appear for this condition. You can create two similar rules (one at a time), for example, to paint cells with a value greater than zero and to paint red cells with a value less than zero. Once created, you can view the rules by clicking on "Conditional Formatting"> "Manage Rules ...".

b) "Use a formula to determine which cells should be formatted"

excel conditional formatting 6

In this case, you will use a formula to define what will be formatted. Note that these formulas are different from the formulas that you write in the Excel main formulas tab. In conditional formatting, you need to use a signal.

Excel Course - Conditional FormattingFor example, if you select the list that we created and use this type of conditional training with the formula "= 5> 1" with the ">" sign, all cells in the list will be impacted by the chosen formatting. The reading that Excel does is as follows: it goes from line to line and asking itself: "5 is greater than 1?" Since the answer will always be true, it will format all lines.

To make this formula dynamic, we need to use cell references. For example, let's type now "= $ A1> 5". The result should be as follows:

conditional formatting excel

Note that if you type "= $ A $ 1> 5", Excel will not format any cells because you are locking the cell line A1 and it will always be shorter than 5.

We can also use more complex formulas in this type of formatting. For example, I'll change the formula to "= SE ($ A1 = 7; 1; $ A1)> 5". The new result follows:

conditional formatting excel

Excel did not apply the formatting to the 7 value as it read my formula and it says that if the value is 7, I want to assign the new 1 value to it. Not being 7, the cell value is retained. Because the new 1 value is smaller than 5, formatting is not applied.

You can also use other cells to define what will be formatted or not. For example, let's say this column represents the grades of students in a college. The average to be approved is 6 and I want to format their names in column B from the numbers in column A. I need to select the list of names in column B, go to create rule and in the formula type "= $ A1> 5" . See the result:

conditional formatting excel

Now you already know how to do conditional formatting and can create smart spreadsheets! Congratulations! If you have questions, comment below in this post.

In the LIGHT Business Spreadsheets, we developed ready-made spreadsheets developed by accredited professionals. Here we have some worksheets from LUZ where you will find conditional formatting:

1 - Cash flow

2 - Customer Prospecting

3 - Assessment of Performance and Competencies

4 - Inventory control

Conditional Formatting - Leave Your Smarter Sheets With Colors

Are you in doubt? Get in on LUZ Excel Forum and send your question!

Excel Spreadsheets

210 COMMENTS

  1. Hi Oliveiro, for this you need to do a row count (for example putting an auxiliary column with the value 1 in each row). Then it is only add up to the first duplicate appearance, for the third it will suffice to subtract the result of it by the second and so on

  2. Hi Castro, Excel does not work very well with use of color formulas. It works well for numbers and texts. So instead of using colors, use numbers (which will turn color with conditional formatting) to determine the colors of each item. Then organize them in numerical order even though the colors will be together.

  3. Hi Lara, let's break it down:
    1 - conditional formatting is very simple, you select the cell with the result and implement comparing with the cell where the record is
    2 - For teams, if you have a table where the results data is entered, just use a SOMASE or SOMASES in another table to add them

  4. Hello Rafael, first of all congratulations for the content of the site, it helped me a lot to organize the question of the logic of the functions.
    I would like a help, we have a spreadsheet that meets our needs about competitions, where it has a register of athletes, weight and age categories, team and the modality that will do, validate and invalidate movements, but I would like to add a formatting conditional in case of "record break" if valid, and sum of winning teams. I think I have to create a new worksheet with the data to be "broken" and for the teams to assign values ​​to the first places ... But I do not know where to begin to increase =
    If it has not been clear, I can send some prints.
    Thank you.

  5. Hello
    Could you help me, I need a formula that puts the cells in order of color.
    For example I have an extensive spreadsheet with colored cells with 9 colors. The color represents a link between the contents of the cell that dialogue on the same subject, but when I finished coloring I realized that the vision is not clear, I need to look for the cells, so I thought the solution would be a formula that ordered order , so that it joins cells of the same color and repositions the cells in an established color order. Can you help me?

  6. Hi Rafael,
    Would you help me? I have a spreadsheet with numeric occurrences where eventually a few occurrences repeat. I need to conditionally format the cell of an "x" occurrence and at the same time add up how many rows have passed until the second, third, fourth ... occurrence occurs, and record that value in another column. I know that for you this is a cinch, but I do not know how.
    I thank you.

  7. Hi Joel, it will depend on the structure of the worksheet, but yes, it is possible to do this type of action

  8. Good afternoon, Rafael.
    I would like to know if there is any possibility, for example, in a spreadsheet have multiple clients and contain many old open plots, but each in a different time period, adding the oldest 7 for each client will be if there is any formula, or in the PivotTable or in conditional formatting.
    Thank you.

  9. Hi Paulo, you can use the DIA.DA.SEMANA formula and then use a PROCV to leave the correct name of the day of the week

  10. I have a spreadsheet from b2 to b60 with days in sequence, I need a rule to highlight the days of the week

  11. Hi Arthur, it is possible yes, just use the function SE together with the function E to connect two conditions instead of one

  12. Good afternoon, Rafael.
    The grade system in my college entitles us to a revaluation that replaces the lower of the two grade notes if it is less than the average passing grade (7). I'm doing a spreadsheet with my notes and was wondering if I could format it so as to consider the "Revaluation" column only if the conditions are met (one of the two notes is at least smaller than 7 and the larger revaluation note than the note to be replaced). Thank you in advance for your willingness to help.

  13. Hi Andrew, yes, just add a conditional formatting to be applied on the 2 and 3 lines according to the rule established for 2

  14. Good day.

    I need the following help:
    1 - 32.000 (Meta)
    2 - 32.000 (Green) (yellow) or (red) (Varies with target)
    3 - 100% (I need the color of this cell to always equal the cell color of the 2 line)

    However due to the large amount of goals and data I have in the worksheet it would take a lot of time to calculate the individual goal and establish the conditional formatting.
    the question is how does the 3 line color always follow the color of the 2 line?

  15. Hi John, Excel is very flexible. Let me "show" an example. Assuming you want to subtract A1 from the Plan1 tab of A1 from the plan2 tab. In this case, just make the correct references. Assuming you want to put this result in the Z2 cell, simply place = Plan1! A1-Plan2! A1!

  16. Thank you! Just one more question, how would I do to subtract one cell with another only in another tab in the spreadsheet?

  17. Assuming current stock is in A1, minimum stock in A2, and maximum stock in A3. It would be something like = SE (A1A3, "High Stock", "Normal Stock"))

  18. Hi Wesley, you can create an additional column by counting from days past from one date to another. It would be one date, not the other. And then create the conditional formatting for when that cell passes the desired running days, that changes the color

  19. Good afternoon!

    I would like to know how do I put names using the SE formula for example I'm making a stock sheet and it has current, minimum and maximum stock and in status depending on the quantities in stock I wanted it to appear written Normal Stock, Low Stock and High Stock as do ?

  20. I have a spreadsheet and would like to do a conditional formatting by date.
    Example: I would like you to change the color when you spend 14 days running (or 10 working days) from the opening date
    23 / 07 / 2018 - 06 / 08 / 2018 OK, when it is 07 / 08 / 2018, change the color.
    how do I do that?

  21. Hi Gabriel, I would create an extra column with the SOMA of the items and would do a conditional formatting related to those cells. Assuming that this column was B, in B1 it would compare the value 1 with 110, in B2 51 with 110, in B3 101 with 110 and in B4 111 with 110 and paint exactly the first 3 only according to the adopted criterion

  22. Correcting there. Paint until the sums are less than 110 and not 100 as I wrote

  23. Does anyone know if there is any way to solve the following problem in VBA or conditional formatting?

    Having values ​​in A1, A2, A3, A4 ... .. need to paint the cells until the sum of the cells is smaller than 100.

    For example: A1 = 1; A2 = 50; A3 = 50; A4 = 10; should paint until the cell A3, when adding the cell A4 the value will pass of 100 then it is not necessary to paint

  24. Hi Sérgio, you can use the conditional formatting of icon set in the A2 cell and then go on to manage formatting to edit the rules used. Instead of leaving the values, you can set the cell reference A1 as comparative

  25. I have 2 cells A1 and A2, with values ​​in%.
    I want to create a conditional with arrows to:
    A2> A1, then up arrow;
    A2 = A1, then arrow to the side; and
    A2 <A1, then arrow down.

  26. Funny to do right individually, because it does not make much sense for a range to return a fixed value. I would create auxiliary formulas to account for what you want. For example, to see the time in cell A1 = SUM (D28: AD8) and to see the Closed in cell A2 = CONT.SE (D27: AD27; "Closed") and then would create the formula = E (A1 = 0; A2 > 0)

  27. Hi Rafael, how are you?

    I need to do something like what "Leandro 15 from June of 2015 at 10: 17" did, only with two conditions.
    When I apply one or another of the right, but not both, can you help me?

    In the case in one line (27) this is written the status and in the other line (28) the time.
    I need if in the 27 line the status is "Closed" and in the line 28 the time is 0, the line 29 turns red.

    I'm using this formula:
    =AND(D28:AD28=0;D27:AD27=”Closed”)
    Hi, my excel is in English
    and marking D29: AD29

    When I use this:
    = D28: AD28 = 0
    ou
    = D27: AD27 = "Closed"
    THE RIGHT!

    Can you help me???
    Thank you!

  28. Hi Salvador, you're probably making a mistake in the formula, but I'd have to see to understand it better. It may be the use of $ in the conditional formatting reference

  29. Yes Mark, just use the conditional formatting respecting the exhausted value. Assuming you take a date and subtract from the TODAY function, when this value is negative or zero, the formatting can automatically change the color

  30. I want to make a deadline worksheet, but I still can not figure out a way for the cell to change color when the deadline runs out, is there any possibility of this happening?

  31. Good morning mate! I am not able to apply Conditional Formatting with Bold Font and Fill .... the Source accepts but Prenchimento appears in all cells, even in those that do not fulfill the desired condition ....
    Since I thank you for your help !!

  32. You can create a column with a formula that compares the date of the contract closed with today and use conditional formatting for whenever the formula results in a value equal to or less than 75 the cell will turn green, and so on.

  33. Hi Jardel, yes, just create a conditional format with a formula that analyzes another cell as a criterion

  34. hello friend, good afternoon, I work with worksheets of classification of NCM product codes, each product has its calculation percentage, in the worksheet that I import comes messing up, would it have to, for example, mark a color for each code? for example from 100 to 200 red, and other yellow at last, I await your return.

  35. Hi Rafael,
    I have a worksheet where I have the date the contract was closed and I need to put the signs to indicate if the work is being done on time.
    It would basically be as follows:
    contract signed in 01 / 01 / 2018 - up to 75 days after that date - green signal;
    - between 76 days to 90 days - yellow signal; and
    - above 91 days - red signal.

    Can you help me?

  36. Hi Genival, I did not understand the doubt, can you explain in other words in more detail?

  37. Hello, good afternoon, how are you?
    I have a spreadsheet that receives data directly from a form. I was able to make a D column, for example, change its color depending on the data in column F.
    Example Column D has names, column F has The class group: T-25-Tweak Matutine. If I change to only 25 or 26 (the other class) the conditional works normal, but I would need to change it with all the content without having to change to a number. I tried: = $ F1 = T - 25 - Tuesday morning but it does not work. Can you help me?

  38. Let's go through parts Lorena:
    1 - having PROCV does not interfere with anything, what matters is the value written on them
    2 - As you have the possibility of appearing # N / A, I recommend that you change the formula by adding an SE before PROCV - so you would put IF the result of PROCV for # N / D, you return the value "" (empty) - nothing appears in the cell
    3 - in relation to the formula to take the averages, just use the AVERAGE function taking the interval of the three columns

  39. Hi Rafael,

    I really want to take the course because I'm realizing that it will help me at work, but I would like to know if you can help me solve an issue that I need to solve quickly:
    I'm having a lot of questions to make a formula that calculates average unit prices. This average should appear in Column E (of sheet 2). The unit values ​​of several items are in 3 columns (W; AB and AG) on the 1 sheet. First doubt: these columns do not always have a numerical value with the unit price value, since there was no one who supplied the item. Therefore, some are marked "# N / A". So, the formula should consider only the columns that have values> 0, and average between them.
    Another is that the unit values ​​of these columns (W, AB and AG) are with PROC-V, as they may vary, according to data from another sheet. Does the fact that these columns have proc-v interferes with the formula of conditional formatting?
    Thanks in advance!

  40. Hi Rafael,

    Which of the excel courses offered on this site do you recommend me to do well to learn the application of conditional formatting formulas, PROC-V formulas and ways to link spreadsheets? Is there a face-to-face course?

  41. Hi Lu, just use the conditional formatting of cell rules highlighting and choose the option IS LARGER THAN or go in the more rules option to mark IS LESS OR EQUAL

  42. Hi Thiago, you have. You can create formulas for a conditional formatting to occur. For example = D1 = A1, that is, whenever the value of D1 is the same as that inserted in A1 you will have the color change automatically

  43. Hi Patrício, just select all the values ​​in question, go in conditional formatting (in the INITIAL PAGE tab). There you will choose the second option> First / Last Rules, choose the last 10 and edit to change the color of the last one only (by setting the 1 value).

  44. Sorry to intrude on the answer, but you can try this here:
    Go to Conditional Formatting, choose New Rule, select all cells based on their respective values, below the rule description where the term is Minimum, click the Type check box and select number. Enter the value 6 and choose the color red. Next, in the Maximum entry, choose the color blue. Give OK. Should work

  45. Say good afternoon, good afternoon.
    Can you give me a help please?
    I have a spreadsheet with names and registration data for a draw, in columns A, B and C.
    Is it possible to change these data to green color automatically as soon as I enter the booth number in column D? Thus, at the end of the draw, all contemplated will be in green color.

    Thank you very much

  46. Good afternoon!
    how do I do when the results are> 5 is blue and when <= 5 is red?
    Thank you!

  47. Hi Italo, you can stop cell usage, but if it is unprotected and you paste information, formatting can get lost depending on how you do the collage or data usage

  48. Hello, I would like to know if there is a way to lock the conditional formatting of a cell?
    So in case you accidentally copy and paste a result do not miss this formatting.

  49. Hi Ana, I think it does not exist, but it is possible for you to make a simple reference (= A1 for example) to a second cell and put the conditional formatting of icon set there. Then just paint the white font that it will "fade"

  50. Hello good afternoon!

    I would like to know if there is a possibility of using the conditional set of icons using information from another cell. Can you help me?

  51. Hi Marcelo, you can use an SE at the beginning of the formula, it would be something like = SE (formula <10; 0; formula)

  52. I'll try to explain better.

    The following worksheet is an hourly worksheet, where you do not count any extra minutes until the tenth, you only start counting from the eleventh. So I would like every cell below 10 minutes to zero.

  53. Good morning, Rafael.

    Can you help me with this doubt.
    Is there any way to leave a particular cell smaller than "10", so that it is zeroed.
    Ex. Certain cell is with the final result "8", wanted it to have "0", and when it is a number greater than 10 it would be the real number.

  54. Hi Jaqueline, just use the OR function with the SE function, it would look something like = SE (OU (N51 = 2101; N51 = 2102; N51 = 1101; N51 = 1102);

  55. Hi Giovani, it depends on the structure you use in your spreadsheet. A relatively simple option is to create an auxiliary column with the number of days missing for the current day. In conditional formatting, just use a formula saying that and the value is less than 90 (days), the cell should be red.

  56. I NEED TO INSERT AN FORMULA BUT I DO NOT KNOW HOW TO DO IT, THE FORMULA WOULD BE FOR, IF N51 IS EQUAL TO 2101 OR 2102 OR 1101 OR 1101, THEN YOU WILL HAVE THE CALCULATION OF L51-O51, IF IT WILL NOT BE EQUAL TO ZERO. Can you help me?

  57. Hi good afternoon. I need to know, in conditional formatting, how do the cells that are with dates (to beat in three months) appear in red color. I know I can do this with the cells that are coming through this month, but I do not know how to do it in three months. Help me? Thank you!

  58. Hello .. Good evening
    I wish that when the same text in the column was the same, it would turn red or the cell.
    how do I do that ?

  59. Yes you have Paula, in this case you need to add a formula with two conditions within the criterion of conditional formatting. The function E seems to me ideal = E ($ H5 = "OK"; J5 = "Sentence"). It is necessary to test, but it seems to me the correct way

  60. Hi Rafael, how are you?

    I have a spreadsheet already with the conditional formatting of an entire line.

    Eg

    = $ H5 = "OK" The entire line turns blue.

    There are several formatting in this style for column H.

    However, I would like to create a conditional format with two conditions, such as: leave the blue line if in the J5 cell is written "Sentence" and in the cell H5 is written "OK". That is, you have to fulfill both conditions so that the line turns blue.

    Can you help me?

  61. Hello,

    I have a spreadsheet with the due dates of all the documents of a wide fleet of vehicles, making monitoring cumbersome. I wanted the cells to change color when they were close to maturity, but without the need of an auxiliary column, because it becomes unfeasible for what I want to use. Would you help me?

  62. Hello,

    I have a doubt.
    How do I assign colors from strings, words, names ...?

    I do not think so. Only with numbers and formulas.

  63. Rafael, good morning.

    I need to hide the conditional formatting of the formulas bar, ie in the cell it appears, but in the formulas bar it is blank. Can I do this without having to protect the Excel spreadsheet?

    Thank you!

  64. Hi Washington, you can do that. You can create a formula for conditional formatting or even reference helper cells that you create to calculate the values ​​that represent this difference.

  65. I would like my cells to change color with 7% difference between them, how?

  66. Hi Angelo, as I said, you have 2 fields of use to place references. One is unique to parsing the formula in question and the other is for applying the formatting you want. I believe that something in those two fields is being done in the wrong way.

  67. Rafael, I did according to instructions and only continues in red the cell in question and not the line! I believe this is because it looks for the incidence of the word "PROBLEM" in all cells when it should use only the reference I7 cell to fill the whole line.

  68. Hi Angelo, you should use exactly this formatting you are trying to use, but you should watch out for some items.

    1 - after creating your formatting and opening the conditional formatting rule manager, you will see an item called APPLY TO: - it is here that you must select the entire line (taking care to take the $ if you want to copy and paste this formatting into other lines)

    2 - still in the manager, you will realize that there is an order of the created formations. In this case, at times when two of the formatting is true, Excel defaults to the one that is higher in the list. To do this, simply change the order using the little ones

  69. Hi Carlos, I do not know if I understood very well, but it is possible to compare the value of a cell with that of another cell in another column. From what you said, you want to see lower values ​​or higher values. In these cases you need to create two formatting, one for when the value is higher and another for when the value is lower.

    If the problem is another, just explain me a little more that it will be a pleasure to try to help with a solution.

  70. Good Night!
    I have a spreadsheet with a fleet control and I need some formatting in it:
    it goes from the A7 field to the N7 and the crucial ones are:
    ==> H7 = checklist field of vehicles; when the date filled in G7 exceeds 15 days the conditional formatting I did paints the entire line of yellow.
    ==> I7 = field with Vehicle Status; different 3 status may occur:
    - "OK" = with this status the conditional formatting does not fill the line with colors.
    - "LOAD PACKAGING" = with this status the line is painted orange and there is no conflict with the formatting of expiration that paints the yellow line because these cars do not check-list.

    Now comes my problem; the third status is when the car makes the check list and presents a problem in some sensor; I want the conditional formatting to fill the entire vehicle line with red when the word "PROBLEM" is mentioned in the bad status field; I can not just write "problem"; I have to specify the problem in the worksheet and I want the line to be filled in red when the word appears in a text and yet, this field needs priority over the expiration of the checklist that fills the line of that vehicle in yellow ... I tried the formatting conditional "cells that contain text" but this formatting deals only with one cell and needs the entire row in red. https://uploads.disquscdn.com/images/ccd94ce5f7f4dcf015522643ddc2aa89421db1c26e6652b103563ec85b569e41.jpg Any idea how to proceed?

  71. Hello, I already researched this entire post and if it is already written I did not realize it. I want to highlight with a color fill, only 1 (a cell) value lower, or higher, between comparative price columns. It's possible?

  72. Yes you have, just use the SE function. For example, if the numerical condition is one number greater than another and the word is RAFAEL, the formula is:

    = SE (A2> A1, "RAFAEL", "error")

  73. I must have accidentally deleted it, I'm sorry hehe. How nice that the solution helped you and you were already following this path. If you need anything, I'm at your disposal. Hugs

  74. Good day.

    Man, I'd like to apply the same conditional on multiple cells, for example: A1> B1 (paint green); A1B1 green), (A1Bn green), (An <Bn red).

    NOTE: I already know how to apply the conditional, I would like to know if it is possible to replicate it in another cell with the same function based on different cells.

  75. I would like to know if you have a numerical condition but the result if it is true would be a word.

  76. They deleted my comment but vlw for the answer kk, it was exactly what I ended up doing before you answered .. I did not want to fill the column but it's worth.

  77. Hi Edson, yes it is possible, but it may be necessary to create an auxiliary column to make it easier. For example, in a new column you create these conditional functions (example, in B2 you put = SE (A2> A1; 1; 0))

    With this you will have a series of 1 and 0 throughout your column. Then create the conditional formatting for each of the rules respecting these markup.

  78. Good evening.
    I want to make a column of values ​​(result of several accounts), and every time a line's value is greater than the previous one (eg A2> A1) turns green, but if A3 <A2, then A3 turns red, so for the whole column.
    It's possible?

  79. It's yes Paul, you just have to create the two rules separately, one for values ​​above and another for values ​​below.

    Just be careful that you are using 0,3 in the example, and this would show different values ​​of the desired 30%, to find a lower 30 value, you must multiply by 0,7 or 1,3

  80. .very grateful, opss went wrong in the calculation, you're right. but the doubt persists, I would like all values ​​in the range that are above 30% or below 30% to receive the formatting and not just the exact values ​​for this condition, for example: 0,3 * A1 each condition in a different rule. it's possible?

  81. In this case, 0,7 * A1 (which is the 2 value) should format the 1,4 values, not 14. Maybe the mistake lies in that perspective.

  82. Hi Icaro, when creating references from one tab to another, it searches for the value of the cell, but does not repeat its formatting (color, cell format, etc). In my view this automatic color change would only be possible with a macro or something.

  83. Good day.
    Can I create a link with another tab in Excel by copying its content and formatting every time it changes?
    Eg
    On the 2 tab I have the word "table" in blue. Then I create a link in the 1 tab of this cell where it contains the word "table". It will be copied with the formatting and content, then on the 2 tab I change the color of the word "table" to red and it makes that change in the 1 tab.
    Can I do this?

  84. Hello, thank you very much for the quick response. Unfortunately the doubt continues, I did what it instructed but it does not return all the values ​​of the range that respect the criterion only those that have the exact value, for example: 0,7 * A1 (where A1 = 2) it will only format the ones that are the same to 14. I used the Custom Formula condition, is it wrong?

  85. Yes, you can create a rule in its own conditional formatting. For example if this 8 value is in cell A1, in conditional formatting you will create a new rule that is 0,7 * A1 or 1,3 * A1 according to the need

  86. Good morning, congratulations to the blog and the enlightenment, I have a doubt.
    Is it possible to highlight all values ​​greater than 30% of a cell? Best, I have in a cell the value 8 which is the average of a set of values, I need to highlight any values ​​that are larger than 11 and smaller than 5, in this case the average is equal to 8.

    Still, if possible, find this solution for spreadsheet in google docs, thanks!

    Thank you very much, I hope you can help me.

    Paulo

  87. You can create conditional formatting so that color is applied to teams that have lower or higher scores. For this, you would need to use a function within conditional formatting.

    In this case, the function would be LOWER and LOWER.

    Another alternative is to leave the color always filled up and down and, according to the filling of an auxiliary table, the teams would change lines according to their score. This would be a bit more complex and would solve with some PROCVs and ORDER function

  88. Good afternoon,
    wanted to know how to create a table (soccer style) in which the 3 first rows have a color and the last 3 another color. When doing automatic punctuation the colors change place in the table.
    Is it possible for me to remove this doubt?

    Thank you.

  89. I do not know if I understood your Albano question very well. But trying to explain better, the rule that is created must have as a condition a textual or numerical value. If this is what you are calling a title, simply make the condition use that value as the criterion

  90. Hi Gabriel, yes, just select the cell A3 and put a conditional formatting of it using VALUE ENHANCEMENT if it is LARGER THAN and select the field A1 to make the comparison

  91. the cell already has a title; but I can put the yellow name (in white, or invisible color) and, would it work or would I just put the title?

  92. Hello good afternoon
    I wonder if you have the following comparison
    if the A1 field is larger than the A3 field, the A3 field is red
    could do?

  93. OI Albano, I believe that is not possible. Conditional formatting respects defined text rules normally, that is, if yellow is written in the cell, it is possible, but if you only paint the same, the other cell will not identify the change

  94. Hi John, not that I know of, but it is possible to make them change color, to be visually easy to identify these cells

  95. Hello! Would it be possible to play a sound when the value of the cell reaches a certain value?

  96. Good Morning; can I create a rule where the B12 cell (Calculation tab) is YELLOW Fill color, if the B12 cell (Paid Funds tab) is YELLOW Fill color?

  97. Hi Luigi, in the post is explained the process. Did you even try to make the process explained? Anyway, just select the cell, use conditional formatting and set the color and condition. In this case, one condition will be green when revenue appears and another condition is green when expenses appear

  98. Good afternoon. I have a financial spreadsheet and would like to know how I enter a color for a cell:
    when the word 'RECIPES' is typed, I would like it to go green, and 'EXPENSES' would turn orange.

  99. You can use the OR formula. I believe it would look something like = OR ($ I2 "SENT"; $ I2 "ON SIGHT")

  100. Hi Matheus, I do not know how to create the dropdown list with colors (I believe there is no way to do this in Excel), but you can create a dropdown list with the names, red, yellow and green for example and then create a conditional formatting in the cell in question that leaves the fill color (background) and the letter of the same color (red, green, and yellow) according to the selection.

  101. Hello, I am using the following conditional formatting -> = $ I2 "SENT", in case it serves to make the entire table row turn red if the contents of column I are not "SENT", but I need the line to be red if I is not written "SENT" or "ON SIGHT". What is the procedure for this?

  102. How do I create a drop-down list of only colors, for example: in a search, if you agree, you select the yellow color, otherwise you select the color red.

  103. Hi Rafaelle,

    have up to have but I believe you would need a macro for this. Because to be automatic, you need a formula like TODAY, for example, but when you arrive tomorrow, if it is still written like this, it will change to the next day. The macro I thought would use this formula and then copy and paste only that same value in the indicated places, so you would not have the problem of keeping it dynamic.

    The logic is as follows:
    1 - creates a conditional with the TODAY function, of type the TODAY function appears if cell x is filled
    2 - creates a macro that takes the filled cells and copies and paste their value into the same cell

  104. Good morning, I would like guidance as to the following doubt, I have a monthly control, in which I fill in daily, with distinct information in cells, I have to by the date of each action, wanted to automate the cells next, ex: every time I fill the cell I did data validation the date cell automatically populate with the recent update date and so on.

  105. Hi Claudio, are two parts:

    1 - You can create a conditional formatting that paints the cell of column H when column I has a certain value

    2 - you can create a SOMASE conditioned to the values ​​of column I

  106. Hi David, but these cells have some fill or not? Because if they do not, you can create a conditional so that a value appears according to that amount. And then just create the conditional formatting so that the color changes when that specific value appears. If you already have values ​​in the cells, it may be a bit trickier, but you can use a helper row or column to use a rule similar to the one I just explained.

  107. Good evening,
    I would like to know if I can create a conditional or formula it!
    ex: the amount is 5 and automatically 5 cell are painted either column or row ???

  108. Good afternoon
    I need to make a rule that when any cell in column I has the number 10, the cell next to column H should have its value in red and at the end of that column, only the red ones added, can you guide me?

  109. Well, from what I've seen here it does not stay the same color with different name, because I was wanting it when writing in column "J" reception (as cell j15), its entire line is in the same color as it is independent of what is written in other cells in the line ..

  110. Hi Will, for this you will have to go in managing the conditional formatting rules and modify the selection of cells that modify color. Instead of using only the J-line, you can pick up all the other

  111. Good afternoon, fellow.
    I'm in a situation that maybe you can help me. I want one cell to always have the color equal to another cell. Let's say I put the color "green" in D22, I want it in K22 to be the same color and whenever I change the D22 to K22 if it updates and stays the same. Do you know how?

  112. Good evening.
    I have already broken my head and can not format a cell to make the color change as a condition.

    You have a spreadsheet where you have 'n' calculations and in a given cell, based on these calculations the number 30 appears (so far there are no errors), but then this number 30 would have to follow the color, according to this reference table below:

    TABLE REFERENCE.
    VALUE between 50 and 100 - BLUE
    VALUE between 0 and 49 - GREEN
    VALUE between -49 and -1 - ORANGE
    VALUE between -100 and -50 - RED

    Has anyone done any of these formatting? Can you help me please?

    Grateful for the attention.

    Ricardo

  113. Speech boy, thank you very much for this very well done Blog that helps us a lot.
    I have a column with values ​​that repeat from 1 to N times, and wanted to create a conditional formatting so that when the Value changes the color tmb changes.
    Doing something like "Color yes, color not" between values.
    Eg:
    1 (Color)
    1 (Color)
    1 (Color)
    2 (No Color)
    2 (No Color)
    3 (Color)
    4 (No Color)
    4 (No Color)
    4 (No Color)

  114. Rafael, good morning.
    I have a worksheet that I have a date compared to the date of the day, I would like it to go green when this difference was over 31 days, yellow between 31 and the same date, and red when the date was less than the date of the day .

    due date dates today
    18 / 10 / 2016 19 / 10 / 2016 coloring the date expiration in red
    21 / 10 / 2016 19 / 10 / 2016 coloring the date expiration in yellow
    01 / 12 / 2016 19 / 10 / 2016 coloring the date expiration in green

    Thank you.

  115. Hi, I need help with the organization of my spreadsheet. I have 5 columns in plan2 that fetch information from one another in plan1. This information is in the order that is in plan1, so the information stays in separate cells, thus leaving some cells blank in the middle of the information. I want to leave the information organized one below the other at the beginning of the columns, without blank cells between one information and another.
    NOTE: The 5 columns pull different information.

  116. Hi Cristina, you can use the Paste Special> Values, so it will take only the content without the formatting of the cell

  117. Hello I need to know how to change place writing and the colors of the lines remain the same without taking the color of the formatting together, just the sayings ????

  118. In the case of conditional formatting, you can create formulas. To facilitate your understanding and the spreadsheet itself, I would first create an auxiliary column with the value 1 whenever the cells were the same, and then create the conditional formatting to change color whenever that (auxiliary) cell was 1

  119. Hello, I have a question and I need help ...
    I am making a table, where I need a particular cell to change color, if two other values ​​equal values, how should this formula?
    If F equals D, B changes color.
    Or If E is equal to or less than 0, B changes color
    Can you help me?

  120. Hi Sérgio,

    I tried to do it here, but for some reason I could not. My logic was to use the color scale, but go in the personalized part of it and when it's time to set the lowest and highest value of the scale I tried to apply a MINIMUM and MAXIMUM formula, but I did not get the desired result.

    The logic seems solid to me, but Excel is a mouse nest sometimes. If you want to try to see if your getting any results was what I did and I thought. If not, another worse alternative, but what would work is to apply the color scale directly in the AVERAGE of all previous values

  121. Hello, see if you can help me: I have a comparative worksheet of cars, where each column is a distinct vehicle and the lines contain the characteristics of each one (power, consumption, trunk, etc ...). I applied one of the standard Excel color-based conditional formatting in some of these features: the largest trunk went green, the others changed the tone until the worst trunk went red; the best consumption was in green and the worst in red, the most powerful was in green and the worst in red, and so it goes. If I add a new vehicle, with a feature that surpasses that of others, the colors are automatically 'redistributed' so to speak, and this new car has a certain characteristic in green, while others vary the tone automatically (normal, as it is to be expected).

    What I want to do now is to apply conditional formatting to the vehicle name based "on average" of the previous conditional formatting; I want to create a sort of ranking, where the vehicle that got the best "average" of the conditional formatting of its characteristics has the name in green (as long as the others change the tone until the worst of them gets the shade in red).

    Is it possible to do this in an automated way (because there would have to be a change in this ranking, if another car enters the list and a cell reformat occurs)? If I was confused by my explanation, I sent the spreadsheet or a print without problems.

    Thanks for the space.

  122. I believe the easiest and quickest way is to create an auxiliary column. In this column you can place the SE function with the conditions you want. To do this, use the TODAY function together when writing the logic.

    Then just put the conditional formatting for the results. Assuming the date is in cell A1, it would look something like this.

    = SE (A1 <TODAY (); "Delayed"; SE (A1 <TODAY () + 60; "Near expiration";

  123. Hello,
    I have a due date to be followed, remembering that there are several different dates. I would put a conditional that if the date expires, the color should be red, if it is on time, the color should be green. However, I want to include a conditional with the expiration date close to expiring, 2 months close to expiring, and it should be yellow in color ... How should I proceed?

  124. You can put it in the first cell (by taking the $ of the added reference so that it is relative) and then just drag the cell down

  125. Hi Rafael.
    Fine and you?

    So they are not actually in text format, they are all like the abbreviated date. By highlighting larger cells it works, but I want to know how I can apply it to the entire worksheet without having to put one cell at a time.

  126. Hi Franciele, everything good?

    apparently what is written is in text format, which would make it difficult to create conditional formatting based on the deadline.

    The conditional formatting highlighting cell rules is> greater than would be enough. Assuming the finished day is in column B, it would just mark the value that is in A. The problem is that this formatting does not analyze textual values, only numerical values.

    This is why you need to adjust the formatting of the inserted dates. If the date formatting is not enough (you need the time), you can put the date in one column and the time in another column and create an auxiliary column with the function E to analyze the two conditions

  127. Hello, I have a worksheet that I need to use conditional, but I do not know how to do it.

    I have a column with deadlines and another with dates that I actually finished. I want excel to identify when I performed these tasks after the deadline, but it's different dates and I want to apply to the entire worksheet, how do I?

  128. Hi Cristiano, we were happy to have helped. If you have other questions related to Excel just search our posts or comment on our forum - http://forum.luz.vc/

    Hugs and success with Excel

  129. Congratulations on the tutorial. I understand perfectly. Helped me a lot, solved my problem. I thought it was a seven-headed creature, but I saw it was not !!

  130. Hi Nakelson,

    in this case, the rule is a color scale. If you double-click this rule, you'll understand what's happening with this conditional formatting.

    At first the color scale automatically sets the color according to a comparison of values ​​between the numbers of the selected range.

    For example, in a color scale with the colors brown (smaller numbers), light blue (intermediate) and purple (larger numbers). If you have the numbers 1, 2 and 3, 1 will look brown, 2, light blue and 3, purple. Understood?

    This is what is happening on your table. Now you need to understand that this analysis is being done per row in your table, so the rule will not always be the same for each row as the numbers vary.

  131. Hi Nakelson, is everything good?

    This value you are seeing is the selection range where the formatting is being applied. If you want to see the rule have to look on the left side of the conditional formatting management window.

    Let me give you the step by step.

    1 - Select the cell you want to see the rule for
    2 - go to the HOME tab> Conditional Formatting
    3 - from the list, choose the manage rules option
    4 - will open a window with the rules that apply to that cell
    5 - in the first column (rule - applied in the order that appears) will be the rules applied in that cell

    will be something written as <50, = "text," or something like that. This is the rule that you should use in SOMASES. Note that if you have more than one rule, you should use all the rules in SOMASES to work okay?

    Before you send me the worksheet, try this path.

  132. Where do I see the rule ??? when I edit it only has "applies to = $ C $ 18: $ G $ 18", and I typed it there in the somase criterion and it did not work, but at least the formula recognizes the sum, but it is zeroed, if I can send you the spreadsheet by e-mail. You give me a look and give me strength.

  133. But the cells do not have conditional formatting to turn blue? If so, just use a rule similar to the rule used in conditional formatting

  134. I CAN NOT, BECAUSE THE PLIX IS VARIABLE, AND THE CELLS ARE NOT RECOGNIZED AS BLUE, IN CASE IT WOULD HAVE TO BE EQUAL TO A BLUE CELL (CRITERION) ... MORE N TA GIVING!

  135. Hi Nakelson, is everything good?

    Adding by color with the basic features of Excel is impossible. But since you're using conditional formatting, you're probably using some rule to make the values ​​blue. You can use these same rules in a SOMASES to add only the values ​​that are within your criteria

  136. I have a worksheet already with conditional formatting, I wanted to add only the values ​​in blue, it's a price search worksheet ... can anyone help ?? I can send it to you by email.

  137. through a conditional in a single cell, I have 3 possibilities: non - partial - yes, being that for I do not want to use red color, partial yellow and yes green, see the conditional below, the cell is formatted in Wingdings

    = E6 = "NO", "L", SE (E6 = "PARTIAL", "K", SE (E6 = "YES"; "J"

    My question, how to insert colors into a single cell in 3 conditions
    Can someone help me…

  138. Hi Hayslan,

    in fact I can not think of a totally simple way to do this only with the functionality of conditional formatting.

    I would make an auxiliary column with the function CONT.SE, so it would count every time the client name appears in the column. Then you would do the conditional formatting by taking this value from CONT.SE. If it is larger than 5 it turns green. It's not complicated, but you need to create a new column.

  139. Hello ... I'm having a hard time solving a problem that seems to be simple !!

    I have a spreadsheet with my clients, I want to do a conditional formatting in which the names that appear more than once are italicized.

    EX: If the name only appears once the formatting turns red;
    if the name appears twice the formatting turns yellow;
    these two I got.

    now the problem is here

    if the name appears in my column more than 5 times I want it to turn green, this shows that it is a sign that the customer is always buying.

    If you can help I thank you very much !!!

    Thank you

  140. Good afternoon, I'm in a pretty dilemma. See if you can help me please:

    In a column I am shown the following values: "1", "2" and "3". These values ​​are the result of a formula that measures the expiration of certain documents. The formula that results in these numbers is: "= SE (E (G9 = M $ 1)," 1 ", SE (E (G9 = N $ 1)," 2 " ; "9"; ""))) "The cells that are locked are the contents if the document is expired, the document is about to expire and the document is active.

    My problem is that I need to put a conditional formatting with sets of icons, in the cell that shows the result of the formula: "1" for expired document; "2" for active document and "3" for document expires. The set of icons that will work with the numbers are GREEN for active documents, YELLOW for documents that are about to expire, and RED for expired documents, but I can not make the formula work because the cells that show the numbers "1", "2 "And" 3 "are the result of a formula.

    Does anyone have a light for my case?

    Please come back if I am not clear.

    Thank you in advance.

    A hug!

  141. Hi Ari,

    to hide a worksheet you just have to right-click on the worksheet you want to hide and select the HIDE option.

    Now your employee will still be able to access this worksheet if you use the RE-VIEW option. This is unlikely to happen.

    Anyway, to have a 100% security of your file, there are 3 steps you can take:

    1 - format your cells to the format LOCKED - just select all cells from the worksheet> right button> cell formatting> protection option> mark locked cells
    2 - You can hide all the columns of the worksheet that contain content (just select the columns> right button> hide columns).
    3 - After that you can lock the entire worksheet in the REVIEW tab and BLOCK THE BLANK option> add a password that will only be able to open the worksheet who has a password

    Hugs

  142. Good afternoon Friend Rafael, I have a file that has 5 spreadsheets in one of the plan1 type plan2 ...... I would like to know how I can protect some of these plans so that nobody can access them, ie I would even hide them when I send them to the sellers because there are some of these worksheets that have the price of increase that does not interest sellers. I hope I have explained my situation. Thanks again.

  143. Hi, Ramon, everything good?

    you can create a table to count how many cases you have of each type. In this table you will use the function CONT.SE and will use the list of status of these clients and sort according to the nomenclatures.

    If you have nothing written and just color, I even think you can do this count, but it would have to be via VBA code. It is impossible to do color counting in Excel in a simple way (without code) because it does not understand color as a measurable value.

  144. Good afternoon,

    I have a spreadsheet in my company with thousands of clients, where I put customer data and in each case I select with colors for the resolved ones, in progress or lack of documents.
    I wanted to know if there's anything I can do, to know how many of each. How many solved, how many have to bring documentations and how many are in progress. But I wanted to know if you can do this through the colors I already have.

  145. It worked perfect!

    Thank you

    =O60>PROCV($N60;Tabelas!$F$50:$Y$67;11;FALSO)

  146. I will try to exemplify. Imagine that in sheet1 you have the name of the fruit, its quantity and minimum and maximum value (in columns A, B, C and D respectively)

    On sheet 2 do you already have the product name and can pull the quantity with correct PROCV? You will only create more 2 PROCVs by looking for the minimum and maximum values ​​for the A2 cell (as it varies).

    That way, if apple appears in the cell A2 (in sheet 2). You would do a PROCV fetching the A2 cell, but returning the result of column C (which would be the minimum value for that item). Then it would do the same for column D (maximum value).

    With this done, you only have to enter conditional formatting and use column C and D. As an example, the value of B2 must be between the values ​​of C2 and D2. If it is not, it changes color. Because C2 and D2 values ​​vary by fruit, formatting will always work dynamically.

    understood?

  147. Or even if it is possible to mirror the formatting of another cell! Then I would know how to solve with PROCs

  148. Thank you Rafael, but I did not understand how to do it!
    Can you please exemplify?

  149. Hi Leandro,

    I believe you can achieve this result if on sheet2 you create an auxiliary PROCV with the conditions for the selected fruit. So if Pear appears on sheet2, you will get 2 PROCVs. One showing the lowest value and the other showing the highest value.

    then in conditional formatting, you create conditions that analyze this value of PROCV, which will always be changing.

  150. Hi Leandro, good afternoon!
    I'm having a hard time also regarding conditional formatting.
    I need to do the conditional formatting, with formula however with 2 variables, eg:
    On Sheet 1 I have an array table that is all my stock (Pear, Apple, Plum, Peach, Etc ...)
    On Sheet 2, I have my stock control is has the following structure:
    A1 - Product | B1 - Quantity
    I would color the font shape distinctively for each product.
    If my apple stock (A2) is between 2kg and 3kg (B2) it turns yellow, if <2kg is Red. but
    If my Pear stock (A3) is between 4kg and 5kg, (B3) turns yellow, if <4kg is Red.

    So it's very simple, but my complicator is that in column A I use a procv in and out of products in the seasonality (sometimes A2 is Pear, another day may be Plum, and another day may be Papaya).

    Is there formatting so that the training understands and interprets by fruit and how much of each?

    Thank you

  151. Hi Leandro, you can do for a cell, then edit the conditional formatting (in the tool edit command itself) to get the absolute reference of the cell that you used ($). So, when you drag the cell to the rest of the column, you should parse it dynamically.

    One detail is that the conditional formatting is a little weird. Sometimes it gives you trouble at those times that you drag, so it's worth giving two clicks on some cells to make sure the reference is correct after doing this procedure

  152. Hi Rafael, I would like to know how I can do column relation? in case here I want to put the amount due in one column and in another the amount paid and do. If the amount paid is equal to the due to turn green if it does not turn red. I can only make cell by cell, how can I do the entire column? thank you.

  153. Hi Thiago,

    you now have a very good chance of being your version of Excel. I did a new test here and it worked perfectly. Unfortunately Excel 2007 is very limited and as I use 2013, they may have differences in functionality (I imagine that creating formulas within conditional formatting is one of them)

    Thinking of one more solution, you could use a VBA code to identify the service value in one cell and change the color formatting of the others, but it would be a much more complex solution to this problem that seems very simple and easy to solve you are using an updated version of Excel)

  154. Hi Maria, check out the exercise correction below. He had made a miscalculation.

  155. Hi Luiz,

    it really was a summation mistake that I ended up doing. Actually the director costs add 7,3 (2,5 + 1,5 + 3,3). Obviously this change changes the final result:

    MC = 12 - 7,3 = 4,7
    PEQ = 70000 / 4,7 = 14.893,6> rounding up is 14.894

  156. Funny, I went to test it again with your data and it was not.

    Try it another way. Select all A468 values: J468 and G468. Then put conditional formatting and create the new rule, all the same I've said before, but now put only g468 = "service"

    I believe it will work. The biggest problem will be dragging later, because conditional formatting is not 100% good for dragging. Remember to take the absolute references ($) from within the formulas used in conditional formatting

  157. Rafael,
    I need to color the 468 line, from letter A to J, so I'm using the following reference:
    The word "service" is in cell G468, so I used = SE (G468 = "service"; A468: J468; G468), am I doing it right?
    I already tried to use it to color the entire column and also could not.

  158. Hi Thiago,

    has a good chance of being your version of Excel yes. I made it here and it went perfectly.

    Obviously I used the reference A1 and A2: A1000 as an example, in your case, you would need to put instead of A1 the reference where the word service is and instead of A2: A1000 the column you want to paint

  159. Rafael
    I could not with this formula, can it be the version of excel? What I use is 2007.

  160. Hi Thiago, everything good?

    You can create conditional formatting to get this result. I'll take the step by step:

    1 - Select the cell where the word service will appear
    2 - go in conditional formatting (home page tab> conditional formatting)
    3 - choose the option to create new rule
    4 - Choose the option to use a new formula to determine which cells should be formatted
    5 - within the reference use the conditional SE in this way:
    = SE (A1 = "service"; A2: A1000; A1)
    6 - now click the format button and choose the type of fill color you want

    I believe that it will arrive at the result you want

  161. Rafael Good evening,
    I would like to know how do I color a whole row or column, from entering the word 'SERVICE' in that row or column?

  162. I was thinking even if he was already included or not going to redo, so I have difficulty that it is college work but it has not been given in the room yet. But once thanks! God help you, for helping me.

  163. 2 - be careful, he asks for the financial break-even point, that is, that does not take into account depreciation. So the calculation should be done over 72.500 and not over 75.000 like you did.

    3 - Profitability is the expected return on an investment. If he said that the PL is of 8MM and that 15 wants% of it, it means that he wants 1,2MM of profitability.

    In this case, add this 1.200.000 as fixed costs. In this case you would have:

    MC = 2500 - 1450 = 1050
    PE to profitability = (1.200.000 + 500.000) / 1050 = 1620 units need to be sold to achieve profitability of 15%

    I think that's it, but it's worth checking

  164. As you said if I had but doubt was to put here, then with your explanation I solved this:

    2) Determine the Contribution Margin and the financial break-even point by knowing that:

    Unit Price: R $ 180,00

    Unit Costs Production Variables: R $ 95,00

    Fixed Costs for the Period: R $ 75.000,00 (included in this amount R $ 2.500,00 for depreciation).

    MC = 180-95 = 85

    75.000 / 85 = 882,35

    already the third exercise entered new information and I could not solve.

    3) Determine the Contribution Margin and Equilibrium Point knowing that:

    Unit Price: R $ 2.500,00

    Unit Costs Production Variables: R $ 1.450,00

    Fixed Costs of the Period: R $ 500.000,00.

    Amount of Equity in the previous year R $ 8.000.000,00

    The company wants a profitability of 15 %%.

  165. No problem, Maria, I'm glad to have helped,

    if you have other questions just send it here or through our Excel Forum - forum.luz.vc

    Hugs

  166. You only have to worry about references if you're dragging or copying formulas. If you have a large list with many formulas you need to understand in what direction you will copy.

    For example, if you have a list with 15 sellers (each in a row) and a function in column B that is a SOMASE for the 1 vendor. That is, you will want to drag this function down so you do not have to rewrite it all over again several times.

    In this case, you should leave the relative references (without $) to not lock the cell and you can drag down keeping the reference on the correct seller and not caught on the first seller, understood?

  167. Thank you!
    As I have a very large list, is it important to leave the formula cells without relative reference?

  168. That same Paul, the name of this fixation is relative reference or absolute reference. In the cases you have listed

    $ A1 - means the column will stay locked
    A $ 1 - means the line will stay locked
    $ A $ 1 - the whole cell is locked

  169. For excel to understand the condition of comparing different column values, just look at the $ A $ 1 binding of the cells I'm formulating. right ? How is the configuration of the cell fixations? $ A1, A $ 1, $ A $ 1 ??

  170. Hi Maria, how are you?

    To calculate the contribution margin you need to pick up the sales price and subtract from the direct costs. In this case the selling price is 12 and the direct costs add 7,7

    ie MC = 12-7,7 = 4,3

    the break-even point is calculated by dividing your fixed cost by the contribution margin, ie 70.000 / 4,3 = 16.279,06. Because you normally do not sell a unit and 0,06 of a product, you need to round up, which generates a result of 16.280

    if you need to, I highly recommend our break-even worksheet, which automatically does those calculations that I've indicated here - https://luz.vc/planilhas-empresariais/planilha-de-calculo-de-ponto-de-equilibrio-excel

  171. Hi Rafael, can you help me solve this? Please.
    A company after thorough analysis has concluded that the costs of its product are:

    Raw material: R $ 2,50 per unit

    Direct Labor: R $ 3,30 per unit

    General Manufacturing Costs: R $ 1,50 per unit

    Fixed costs and expenses for the period: R $ 70.000,00

    Knowing that each unit is sold for R $ 12,00 (free of taxes)

    Determine the unit contribution margin and the accounting balance point.

  172. For some reason this response from above is bugged, inside the quotation marks, he is putting the sign of equal and more quotes wrongly, I do not know the reason,

    anyway it's just take the signs of incorrect quotes and equals that it will work. I tested it here.

  173. I managed to understand, yes Raul,

    for this you need to create an SE function with TODAY this way here (assuming that the cell with the date you called is cell C2:

    = SE (TODO) <C2 + 7; "Connection made in the last 7 days"; SE (TODO) <C2 + 14; 14, 2, 21 and 21, and would format the color for each of the arguments, in this case it is the numbers, so I would use 7, 14, 21 and 30 one of them.

    Now just drag it down and you're done.

  174. Good morning,

    I have a connection control worksheet, so I call a certain client and I put the one that I called on the date X, I want in a cell next to put if the call was made in the last 7 days, in the last 14 days, in the last ones 21 days in the last 30 days and coloring these cells with the colors blue and red amaranth green. Can you help me ? Can you understand my need?

  175. I'm happy to have helped Ari,

    If you have other questions about Excel, we usually respond to our forum too - forum.luz.vc

    big hug

  176. Dear Rafael, I was very happy for your interest in responding quickly. I'm already using your instruction. We need people just like you. My thanks and congratulations !!!!

  177. Hi Ari,

    just use the conditional function SE in column H. Assuming we are putting the formula in cell H7. Would be like this:

    = SE (B7 = "Monday", "RODÍZIO", "any text you want")

    note that where I wrote Monday, you could write any other day of the week as long as it is spelled exactly as it appears in column B okay?

    If you want to post for more than one day of the week, you would need to use an SE within SE, like this:

    = SE (B7 = "Monday", "RODÍZIO"; SE (B7 = "Tuesday"; "RODÍZIO"; "any text you want")

  178. I have a spreadsheet of which in the line B7 ... B60 contains the days of the week, But I want to use column with cells H7 ...... H60 mentioning the word "RODIZIO" when it is a certain day of the week. Could you help me how do I do this?

  179. Hi Luzmar,

    unfortunately I do not know (and I believe there is not) a way to do color variation in the data bar.

    What you can do is to use the data bar with gradual fill, which has color shade change, but does not change the color itself.

  180. Hello, I would like to know if you have how to make the data bar change color according to the value of the cell, I am grateful if anyone can help me

  181. Good afternoon, Raphael,
    Sorry, you can not change these icons.
    Needing more is just talking,
    Hug

  182. Good morning, I wonder if there is a way to change the icons of conditional formatting.

    For example, when you assemble the formatting rules, you can choose between banners, arrows, balls among others to appear in the cell. Would you like to change these images?

  183. Hi Ge,

    one of the types of conditional formatting you have is the first and last rule.

    Just go to the home tab> conditional formatting> first and last rules> 10 first items or last 10 items> when opening the window, choose the option to decrease only the formatting to the first (major) / (minor) in each case respectively,

    This way you can only change the formatting of the highest and lowest item. Hugs

  184. I have a little doubt. I have a price list but conditional formatting inserts colors into all cells and I only wanted it to do so with the highest and lowest values. how can I do it?

  185. Hi Laudifer, how are you?

    I do not know and I do not know a way to make the set of conditional formatting icons on the right side of the numbers. I think it's an Excel standard.

    I thought of a way to get the result you want. To do this, follow these steps:

    1 - Enter the conditional formatting in the column you want (assuming it is column C)
    2 - In the left column (column B), create a formula = C1 (row number)
    3 - In the column with conditional formatting, paint the white font

    So you will have all the values ​​in column B and the conditional formatting of icons in column C left aligned, getting to the right side of the numbers. Now just take out the grid linahs and edges so you will have the desired effect.

    I hope I have helped, hugs

  186. I would like the icon set to be on the direct side of the number and not the left, how do I change the side?

Comments are closed.