Often when you are adding several charts to your report or project plan, it may be important to have them all aligned to a proper baseline – a fundamental design principle. Take a look at this:
But if you try to select each chart individually and adjust the alignment, it may not provide correct results as charts / other drawing objects can be moved by pixels instead of cells. Here is a simple trick to ensure proper alignment.
1. Select all the charts, objects you want to align to same baseline (top aligned, left aligned etc.)
2. Click on draw menu on the bottom right corner, select “Align or distribute” option, and specify your alignment choice like “align left” or “align bottom”
3. You can also distribute charts evenly across the available space on the sheet using this menu.
Btw, if you do not see “draw” menu, probably the drawing tool bar is not yet turned on.
14 Responses to “Aligning charts, objects on spreadsheet [excel layout tip]”
Here is another good tip:
Holding the ALT-key pressed during moving or resizing a chart makes excel to autofit the position and size of the chart to the grid of cells underneath. This makes it quite easy to align several charts and ensure that they have all the same size.
Works for shapes, form controls, etc. as well.
Very nice trick for Dashboard Charts.
Thanks for sharing.
Andreas
A donut chart, a 3d column chart, and a 3d conic column chart. The best way to align these monstrous chart types is with the DELETE key.
Your post does illustrate one of the great advantages of Office 2003 (and earlier) over Office 2007. That Align or Distribute submenu can be separated from the menu and dragged as a floating palette to wherever you may want to use it. Click on the top edge (see the border above Align Left?) and drag. This and many similar formatting palettes were removed from the Office 2007 interface, supposedly to simplify the interface. The unintended consequence is that, for people who actually use this feature, it now takes two, three, or more clicks to carry out an operation that took a single click with the floating palette. Not only do you have ot learn where the commands are hidden in the new interface, it takes longer to actually use them.
@Robert: thanks for the alt+drag idea. 🙂
@Jon ... : "A donut chart, a 3d column chart, and a 3d conic column chart. The best way to align these monstrous chart types is with the DELETE key."
haha, you are right. Believe it or not, when I created these charts I knew someone would say this.. but I wanted to add a little sarcasm to the alignment idea 🙂
Thankfully, I am still using office 2003. May be I will have to migrate to 2007 pretty soon... and then I will write a bunch of posts cribbing how I cannot get a simple thing done...
@Jon:
I totally agree, especially with regards to the chart engine, I can’t hardly see any improvements Excel 2007 would have made. Far from it!
Most annoying in my daily work is the following:
In Excel 2003 or earlier you selected something on your chart – let’s say an axis – went to the format dialogue and changed several things, e.g. the font type, the font size, the scale, …, and finally the line color. Then you selected the other axis or the axis of another chart, pressed F4 or ctrl-Y and Excel repeated all changes you have made to the axis in your step before. A time-saving feature, especially if you have to format a lot of charts, one after the other.
With Excel 2007 F4 only repeats the last change you made. In the example above: only the line color. You have to do all the other things again. Does Microsoft really think this would be an improvement for us?
Robert -
Yep, the loss of the F4 key is another productivity-sapping "unhancement" in Excel 2007. The modeless dialogs at first seemed like a good idea, but on second thought, I want the old dialogs back.
While we're flaming on the new UI, how about the tabs on those dialogs. There are four tabs covering markers and two covering lines, whereas Excel 2003 had one tab for lines and markers. It's so much easier to format the series if you can see all of the controls on one tab, rather than having to move from one to the next.
@Jon
Complaining about the Excel 2007 UI and the chart engine: If I only knew where to start! True, the tabs in the dialogs are not intuitive and complicated to use. As you said, almost everything takes one or 2 clicks more now. First I thought “Robert, you’re unfamiliar with it, this will become better with using Excel 2007”. But I was wrong. After working with Office 2007 for one and a half year now, it is still not intuitive and I am still far away from being as fast as I have been with 2003. I have so many complaints and questions like
- why doesn’t double clicking on a chart element bring me to the format dialog anymore?
- why do not all number custom formats (that are defined and available for the cells) show up in the number format dialog of a chart? And what is that extra add button for? I don’t have to do an extra click on an add-button when defining a custom number format in a cell.
- ….
I have to admit that I didn’t search for a user discussion focused on the Excel 2007 chart engine. Maybe you want to think about starting one on your PTS blog? I think you as an MVP and your website / blog as the number one reference for Microsoft Excel charts on the internet would be perfect for starting such a discussion.
If there is already such a discussion, please let me know.
@Chandoo: No offence! You know that I love your blog and try to make some humble contributions to PHD wherever I can. But I think in this case Jon’s PTS blog would be the perfect place for a discussion dedicated to pros and cons of the Excel 2007 chart engine.
Hi,
I tried to summarize my Pain Points about the new Chart Formatting UI on:
http://blog.xlcubed.com/excel-2007-usability-pain-points/
The Chart Formatting Dialogs looks as if the programmers hammered the UI together at the very last minute without the involvement of any UI designer; and I really think that is what happened. Approaching the deadline MS left out an important step in software design - the UI design - and left that to the programmers. And they did what programmers usually do: cluttered the UI and tabs with lots of options, not knowing how users format charts. A programmer only sees all the features and functions he worked so hard for. All are equally important from the programmers perspective
Andreas
Robert -
In addition to Andreas' article, I've posted a couple:
A Belated Review of Excel 2007
Changes to Charting in Excel 2007
I'm sure Google will help you find more.
Recent Links Tagged With Spreadsheet...
Bookmarked your site as spreadsheet at JabberTags!...
[...] Just select the chart and adjust its size and location until it fits inside the table. You may want to use aligning chart objects on spreadsheet trick. [...]
Can you please suggest on how to center align the images in Excel cells?
Hunted a lot, but couldn't get a solution..
Great tips.
Hi Chandoo,
I am facing issue in subtotal. I have the third subtotal coming above the second subtotal. Can you help on this