Any line graph in Microsoft Excel 2010 with grid lines turned on will serve as an example.

I have major grid lines on both axis. The legend overlays with the plot area.

Question: Is there a way to snap the legend box to the grid?

I can manually try to adjust the legend to cover for example 3x2 grid-boxes. However, the alignment of the legend outline to the gridlines is generally poor.

Note: Yes, I can make plots using other software to achieve this effect. I'm curious if it can be done in Excel.

  • If anyone knows how to or can figure out how - to do this, it's John Peltier. Check out his web site for more info. – chuff Aug 7 '13 at 15:41
up vote 0 down vote accepted

Summary: No.

This is a quote from John Peltier on his website:

There is no automatic way to align a legend with anything. You can manually align it, and if the chart changes you are likely to have to realign it. Click on the legend and drag it with the mouse so the top of the legend (or bottom) lines up with a gridline. Then click on one of the resizing handles on the perimiter of the legend, and stretch the legend so that the legend entries are aligned between gridlines.

While technically Schorsch's answer is correct, there is a work-around to give you some control over snapping any element in Excel, Page Layout > Align > Snap to Grid. Turning this on will allow you to snap to the spreadsheet's grid lines.

A practical application would include setting up a worksheet for display/output purposes.

  1. Turn gridlines off, View > Show > Gridlines (uncheck).
  2. Use the spreadsheet grid to create a layout grid for your objects, including Charts and their interior components (e.g. plot area, legend, titles, etc...).
  3. Create your Chart and format to taste.
  4. Turn on Snap to Grid.
  5. Drag your Chart, and components, to their appropriate location on your spreadsheet page, and snap them in place.

Here's a sample with the Legend and Plot area snapped to the grid.

Snap Legend

This is also a good way to align data and its corresponding chart values.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.