Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Specifying Chart Sizes.
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
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 More
If you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. ...Discover More
Macros allow you to make changes to virtually anything you can see in Excel. This tip examines how to make changes (even ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.