We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel Conditional Formatting

Options
I have a spreadsheet with several columns formatted with data bars, so that each cell is colour filled in proportion to the value of that cell as a proportion of the maximum value in the whole column.

If I insert a new line in the sheet by copying an existing line and editing the cell contents, all is fine except that it's not copying the format rules. Instead of the new line formatting cells according to the percentage of the column maximum like all the other lines, it's just comparing each cell with itself and colour filling 100%.

I can edit and correct the rules with format manager, but it takes about 20 mins every time I put just one new line in. I've just tried using right click to insert a new line and copy all in one step, but that makes no difference.

It's a bit of a bind, has anyone got any ideas?
«1

Comments

  • Stompa
    Stompa Posts: 8,375 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    It would be easier to figure out what's going on if you could tell us the formula you're using. But it sounds like a relative/absolute references issue of some sort.
    Stompa
  • [Deleted User]
    [Deleted User] Posts: 0 Newbie
    Part of the Furniture 1,000 Posts Name Dropper
    edited 26 February 2013 at 2:09PM
    Format Style = "Data Bar"
    Minimum = "Automatic"
    Maximum = "Automatic"
    Applies to = "=$K$13:$K$145" for example.

    The problem is that the "Applies to" range gets broken in half either side of the new line, so that the cell in the new line has itself as the "applies to" range. So for example, if I insert a line between 22 and 23 I'll get:

    Applies to = "=$K$13:$K$22,$K$24:$K$146"

    and the new line:

    Applies to = "=$K$23"
  • rmg1
    rmg1 Posts: 3,159 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    If you take out the $ signs in the original formula you should get what you need.
    :wall: Flagellation, necrophilia and bestiality - Am I flogging a dead horse? :wall:

    Any posts are my opinion and only that. Please read at your own risk.
  • Stompa
    Stompa Posts: 8,375 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    Hmmm, I see what you mean. Have you tried using named ranges?
    Stompa
  • rmg1 wrote: »
    If you take out the $ signs in the original formula you should get what you need.

    That doesn't work. (BTW If the dollar signs aren't necessary, what on earth does it put them all in for?)
  • Stompa wrote: »
    Hmmm, I see what you mean. Have you tried using named ranges?

    No, I try to keep named cells to a minimum, it's too much aggro thinking up hundreds of them. (Editing the "applies to" box would be alot less aggro if the arrow keys moved the cursor in the dialog box rather than the cursor on the spreadsheet.)
  • stevemcol
    stevemcol Posts: 1,666 Forumite
    have you tried using the format painter?
    Apparently I'm 10 years old on MSE. Happy birthday to me...etc
  • stevemcol wrote: »
    have you tried using the format painter?

    Just did, that doesn't work either....:(
  • paddyrg
    paddyrg Posts: 13,543 Forumite
    How about $K:$K - format the whole column?
  • paddyrg wrote: »
    How about $K:$K - format the whole column?

    Not quite sure what you're getting at, isn't that what I'm already doing?

    After adding the new line I select the whole column and re enter the range of cells for the formatting to apply to. The problem is that by the time I've done the whole spreadsheet it takes about 20 minutes. What I need is a way to prevent it from dividing the "applies to" range in half when the new line is inserted.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 351K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.6K Spending & Discounts
  • 244K Work, Benefits & Business
  • 598.9K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.3K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.