Written by Allen Wyatt (last updated July 5, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When formatting a chart, you might want to change the characteristics of the font used in various chart elements. This ...
Discover MoreIf you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. ...
Discover MoreMacros allow you to make changes to virtually anything you can see in Excel. This tip examines how to make changes (even ...
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 © 2023 Sharon Parq Associates, Inc.
Comments