An unofficial blog that watches Google's attempts to move your operating system online since 2005. Not affiliated with Google.

Send your tips to gostips@gmail.com.

October 16, 2007

Google Spreadsheets Adds Conditional Formatting

If you select one or more cells in Google Spreadsheets, you'll see a new option in the context menu: "Change format with rules". You can change the color of the text or the background of a cell if that cell verifies one or more rules. Google Spreadsheets lets you format the cells that contain a specific text, a date or have a value in a certain range.

Other spreadsheet apps, like Microsoft Excel 2007, have more advanced options like showing gradient colors for the values, highlighting the top x% values, finding duplicate values or defining a rule based on a custom formula.


The second new feature lets you hide rows and columns that shouldn't be displayed all the time. Select the column or the row, right-click and choose "Hide column (row)". To make it visible again, click on the small rectangle that replaces the column or the row.


Google also improved printing by converting the spreadsheet to PDF and printing that output. There's also a new function that provides an alternative output in case an expression cannot be evaluated: =IFERROR(0/0, "Undefined") displays "Undefined" instead of an error.

Google Spreadsheets seems to evolve much faster that Google's word processor and there aren't too many important missing features. What would you like to see in the next iterations of the product?

59 comments:

  1. i find Conditional Formatting very useless. does anyone use it 4 real?

    ReplyDelete
  2. Its incredibly useful to see negative/positive values quickly without having to read the figure (lazy, I know).

    ReplyDelete
  3. Conditional formatting makes it easy to highlight specific values from a column or row. Let's say you have a report that shows aggregated data about your website's traffic. You may want to highlight the days when your website had more than 30,000 visitors and see if there's a pattern.

    ReplyDelete
  4. Perhaps could it be interesting to let the user choose a cell value (eg 'Is equal to C2' or 'Greater than D2') for the comparison and not only a value (eg 'Is equal to 10', 'Greater than 100'). In that way, a rule could be generic for a range of cells...

    ReplyDelete
  5. Conditional formatting would be useful, if it would be extended for rows and columns

    (e.g. paint row 3 light red, if cell a3 >= 8, and red if it's >=10)

    ReplyDelete
    Replies
    1. Exactly what I thought it would do ... format the entire row not just one cell

      Delete
    2. You can apply it whatever you want, you simply have to select what you want the formatting to apply to. if you want to apply it to the whole thing, select the entire document first.

      Delete
  6. Data filters! I know this feature has been worked on and there's a solution out there but it would be nice if it were actually in the spreadsheets.

    ReplyDelete
  7. It's a very cool. I'm waiting for sum of hour greater than 23:59.

    ReplyDelete
  8. I've been hoping they'd add image support for awhile...

    Spreadsheets are a great way to manage invoices, but without being able to add in your logo it's kind of a bummer. Also, I wish they didn't force you to print all of the grey lines that make up the cells.

    ReplyDelete
    Replies
    1. If you download to Excel then print you don't see all the gridlines

      Delete
  9. Google Docs introduced new few features but Right Click options are still not working. Google has to eliminate this problem from Google Docs.

    ReplyDelete
  10. Oh how I wish it accepted formulas, far and away the best method to draw alternate shaded rows, for example:

    =MOD(ROW(),2)=0

    would be the criteria to change the background color to a light gray. Pretty, pretty please???

    ReplyDelete
  11. Autocomplete. I want to start typing in a cell and have autocomplete from other cells in the column. Helps a lot for my checking account register.

    ReplyDelete
  12. some features that I'd like to see:
    * DEC2HEX & HEX2DEC - one of the most useful XLS sheets I have uses these 2 functions to calculate "percent color" from foreground & backgrounds to CSS's #RRGGBB syntax
    * text rotation - vertical text can be very useful for columns with narrow cell data and long headers

    Thanks!

    ReplyDelete
  13. I was super excited about the conditional formatting until I tried to use. It's been said by others here but I'll reiterate that the conditions need to be more specific. I have a list of wedding guests and I would like to have the names of the wedding guests that aren't showing up be grayed out. I have a column for that so it should be easy but all i can do is color the 1/0. Highlighting another cell or simply the entire row would be far more useful than it's current implementation.

    ReplyDelete
  14. Other features that I see here and believe are essentials to get it out of beta (in no order):
    - Vertical Text
    - more print options
    - hour sums greater than 23:59
    - =MOD(ROW(),2)=0
    - auto complete

    ReplyDelete
  15. Probably the best way would be to define custom formulas and have an option for highlighting the entire row.

    ReplyDelete
  16. #1 on my list for basic usability is being able to freeze columns as you can rows.

    ReplyDelete
  17. I need badly to block formula cells from being edited by clueless users.

    ReplyDelete
  18. Yes basic usability certainly includes the possibility to freeze columns.

    ReplyDelete
  19. hi,

    do you know a way, how when a user fills in a cell with a color, this leads to another cell displaying true or false?

    thx a lot for any insights...

    ReplyDelete
  20. Conditional formatting is hugely helpful. I only hope they can quickly add to this. Because right now only being able to change the cell with the conditional data is very limiting. I would at least like to be able to specify a range of cells that are affected by the contents of another range of cells. But at least we have something. Better than nothing right now.

    ReplyDelete
  21. Hi,

    I need too the alternate color for lines

    +1 for
    =MOD(ROW(),2)=0

    Please upgrade Google Doc Soon.

    a+,=)
    -=Clement=-

    Configuration :
    Windows XP SP3
    Firefox v3

    ReplyDelete
  22. We actually want 'Auto Formate' work regardless of row or colomon. Like MR. Billou description. Also the post of 'Anonymous' just bellow of posting of 'Billou'. I like to make this type of auto format formula. Like:

    paint row 3 light red, if cell a3 >= 8, and red if it's >=10)

    Like If Cell A6 has text value 'PAID' then A4 baground color will green, if A6 is NULL then A4 will have RED baground color etc etc. How do you people think the idea ?

    Thanks

    Ahamed Bauani
    http://www.bauani.org/thinkings/

    ReplyDelete
  23. This Conditional formatting rules is applicable for only selected cell only, what can i do if i apply for other cell.

    ReplyDelete
  24. I would like to add to the chorus of requests for the ability to uses cell references and formulas in the formatting rules

    ReplyDelete
  25. Would be nice to have the possibility to alternate the background colors.
    Something like = if(mod(column,2) == 0, grey, white )

    ReplyDelete
  26. I only use conditional formatting when I need ANOTHER cell to change formatting when a value is changed in the selected cell. I would like this soon.

    ReplyDelete
  27. If you're going to add "Cell is Empty" how do you not add "Cell is not Empty"

    ReplyDelete
  28. I can't make a rule that say "If the date entered is after the date in C3". I have to specify the exact date in the rule. I would prefer to write a rule that scecified a cell rather than a value, eg C$3

    ReplyDelete
  29. PLEASE OFFER ABILITY TO LOCK EDITING BY EVERYONE EXCEPT ADMIN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    ReplyDelete
  30. I have an action item spreadsheet. I'd like late action items to go red automatically. (Needed functionality for this killer app for Google Spreadsheet!)

    AND(status field <> "COMPLETE",TODAY()>target date field)

    I can't do this today without the more complex formulas.

    ReplyDelete
  31. I would really like google spreadsheets to have to allow little bit more complex conditional formatting rules. For example to be able to set background of cell A12 depending on value of cell B12.

    ReplyDelete
  32. Gregory Daigle Jr.April 1, 2009 at 6:46 PM

    Another request for conditional formatting that allows for references to other cells and formulas. This has been requested for almost two years! I'd like to eliminate Excel but I can't because of the severe limitations inherent in Google's design.

    ReplyDelete
  33. Like to add my ++ to the need for support for formulas in conditional formatting. It makes finding errors so much easier when working with financial info and probably many other types of data too!

    ReplyDelete
  34. New features I would like:
    - Filtering in SpreadSheet view (autofilter)
    - Sharing ciretira based on sheets, not on a whole book (Excel terminology)

    ReplyDelete
  35. We need a feature where an entire row / column can be highlighted based on a single cell's value within that row / column.

    And we use conditional cell formatting all the time. Works great!

    ReplyDelete
  36. Great features, I would really like google spreadsheets to have to allow little bit more complex conditional formatting rules.

    ReplyDelete
  37. Need ability to change color of single words, or underline single words in a cell full of text.

    Would be useful because many flashcard study programs use google docs spreadsheet.

    ReplyDelete
  38. Although formating individual cells, it would be massively more useful to be able to format a range of cells based on a formula,ie shading an entire row based on an IF statement. This functionality is available in Excel, StarOffice Calc etc.

    ReplyDelete
  39. This is a case of programmers saying "WOW, LOOK WHAT OUR PRODUCT CAN DO!" instead of "WOW, OUR PRODUCT IS EXTREMELY LACKING COMPARED TO ANY OTHER SPREADSHEET PROGRAM." In reality Google Spreadsheet is extremely lame and half done compared to Excel and any other non browser based program.

    The conditional formatting doesn't even support formatting based on another cell's value. You have to actually know the text value. Say, I wanted to change the

    In addition the only formatting that can be done is to change the text color or the cell background color. This too is extremely lacking. Compare it with Excel.

    The goal of Google Docs should not be to just provide a neat browser based spreadsheet program. It should be to create a better spreadsheet program than Excel which just happens to be browser or web based.

    At this point it is completely out of the question to use Google Docs for anything other than simple data entry and tracking.

    ReplyDelete
  40. @eittom:
    to find a cell that's not empty, how about this: "cell is greater than -1". This allowed me to format only cells in my case that had a value in them, and that was greater than or equal to 0. It worked a treat, left all my blank cells alone, and only coloured the ones I was interested in.

    ReplyDelete
  41. I wish there was an easy way to find duplicates...can anybody shed some light?

    ReplyDelete
  42. yes, formatting based on another cell value would be great.

    ReplyDelete
  43. Data filtering please, - urgently needed!

    ReplyDelete
  44. this works for numbers or text:
    rule 1: cell is empty, background = white
    rule 2: text does not contain "", background = whatever

    ReplyDelete
  45. I'd like to import docs that contain conditional formatting [without the formatting disappearing] and to support formulas like
    AND(TODAY>=START,TODAY<=END)
    where the values are dates.

    ReplyDelete
  46. first of all I would like to have condition formatting where the whole row or a range of cells gets highlighted based on a cell value. This is incredibly useful in tracking tasks where the current date > completion date etc. or if the job is completed, turn the range of cells in a list green.
    Also, is there a way to see the blog discussion with the latest discussion posts first and the oldest at the bottom ?

    ReplyDelete
    Replies
    1. I would like to second this request for whole row conditional formatting based on the contents of a single cell in that row.

      Delete
  47. Being able to format a whole range of columns with conditional formatting is indeed sorely needed.

    ReplyDelete
  48. data filtering, and custom based IF statements for conditional formatting please. Far too limited to be used for what I require it for. Makes a mockery of you providing our companies gmail and docs when it cant provide the level of services we are all used to from Microsoft. Find a way to let us add Microsoft Excel to your docs and we have a winner!

    ReplyDelete
  49. Try Tools > Script Gallery > AlternateBackgroundColors

    ReplyDelete
  50. I have been using Excel Conditional Formatting to track expiration dates, I was so excited to put these on Google Docs so that my boss could see the spreadsheets as I worked on them when he is working out of the office. I knew this was too good to be true---I lose the conditional formatting. If I download to Excel I can set up the conditional formatting I need to check on expiration dates, but I lose it when I close and have to redo each time I want to check.
    What a huge pain....PLEASE some write script for conditional formatting.

    ReplyDelete
  51. I'm trying to build a test plan according to "How Google Tests Code" and it would be very helpful if I could pivot a value into the background color for a cel (so I could have the counts and the priorities, just like it's in the book.) In general, this is a useful technique -- if it's already supported I haven't figured out how, so happy to have a pointer to the doc I missed in that case...

    ReplyDelete
  52. I want to color a cel as a part of a pivot -- so I can display more information per cel. Really, this is because I'm reading "How Google Tests Code" and want to make a test plan spreadsheet that works the way they do in the book, with number of tests as a number and the risk as the background color of the cel.

    ReplyDelete
  53. I want to compare to Cells and get the feedback on the third cell on google doc with conditional formatting

    For example:
    If A1>=B1 Let C1 Turn Green if true,if false,C1 cell color remains un change. But If cell A1<B1, Cell C1 should turn Red

    Please Help

    ReplyDelete
  54. Hi All,
    I want to compare two cells to get the feedback on a third cell on Google doc in conditional formatting
    I.e,
    =IF(B1>=A1) C1 should turn Green if the test value is true, and remain unchanged if False,
    =IF(B1<A1) C1 should turn Red if the test Value is True and Remain unchanged if false

    Please help

    ReplyDelete