Megan has a monthly report that she creates in Excel. She has most of the report automated, except for one annoying problem that she must handle manually. The report includes four pie charts used to illustrate some values from the report. Each pie chart comes out a bit different in size, and the manual task is that Megan needs to make them all the same size. She wants each of them to be 5 centimeters square, and would love a way to remove the manual drudgery of formatting them each month.
The reason that each of the pie charts is a little bit different in size is because when you create a chart with the default settings, Excel decides it can adjust the chart size as it sees fit. This sizing can depend on several factors, such as available space, label sizes, number of data points, etc. One way to improve the chances that each chart will be the same size is to create your first chart and then use Ctrl+C and Ctrl+V to copy the chart the other three times. Each should be identical, and then you can adjust the data ranges reflected in the charts so that they display the desired ranges.
If it is not practical to copy and paste the charts (for instance, if the charts are created by macros), then you may be interested in just using a quick macro to adjust the size of all the charts in the worksheet. The following macro will step through each chart and adjust the Height and Width properties to 5 centimeters.
Sub AdjChartSizes() Dim cht As ChartObject For Each cht In ActiveSheet.ChartObjects cht.Chart.ChartArea.AutoScaleFont = False cht.Height = Application.CentimetersToPoints(5) cht.Width = Application.CentimetersToPoints(5) Next cht End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12235) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Specifying Chart Sizes.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel is a whiz at creating charts from your worksheet data. When the program tries to determine what should be included ...
Discover MoreOnce you create a chart, you aren't limited to keeping the data series in the order they originally appeared. You can ...
Discover MoreYou can spice up your bar chart by using a graphic, of your choosing, to construct the bars. This tip shows how easy it ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-02-25 19:28:53
Alex B
A quicker way to select all charts is to select 1 chart then use the Short-Cut key ctl+a
2012-12-07 14:03:55
Ajesh Barod
Forget to mention,
After selecting all the charts through F5 >> Special >> Objects you can also de-select ones that you don't want to make changes to by Clicking on that chart holding Ctrl key down.
thanks.
2012-12-07 13:58:00
Ajesh Barod
If your worksheet contains only Charts objects you can press F5 >> Special >> Objects.
This will select all the charts in the worksheet and then you can resize them by right clicking on any one chart (keeping all selected) and then selecting Size and Properties.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments