When you create a chart, Excel adjusts the chart’s size, depending on space, labels, number of data points, and so on. So, you might want to adjust the size of each chart so they’re all the same. There’s not much work to forcing a chart’s size after the fact, but the task might become redundant if you have several charts or if you have to resize a few charts often. Fortunately, you can create a multiple selection and enter the appropriate size dimensions for all of the charts, at once. Let’s illustrate this simple manual technique on the charts shown below.
If one of the charts is about the right size, select it and note its dimensions by clicking the contextual Format tab. Excel will display the selected chart’s dimensions in the Size group. In Excel 2003, right-click the chart and choose Format Object.
To assign the same size dimensions to all of the charts at the same time, do the following:
- Select all of the charts by holding down the [Shift] key as you click each chart to create a multiple selection.
- Click the contextual Format tab. In Excel 2003, right-click the selection, and choose Format Object.
- Enter the appropriate dimension values using the Shape Height and Shape Width options in the Size group.
Excel assigns the size settings to all of the selected charts, not just one. That way, you don’t have to repeat this particular task several times-once is enough! (To align the charts, you can use the Align options in the Arrange group.)
That was easy enough, but if you repeat this process often, you might want to use a macro to automate the process. The following macro resizes all of the charts on the active sheet to two inches high and four inches wide (relative to the printed page, not what you see on screen):
Sub ResizeCharts()
 'Make all charts on active sheet uniform size.
 Dim cht As ChartObject
 For Each cht In ActiveSheet.ChartObjects
   cht.Height = Application.InchesToPoints(2)
   cht.Width = Application.InchesToPoints(4)
 Next
End Sub
To enter this macro, press [Alt]+[F11] to open the Visual Basic Editor (VBE). Then, choose Module from the Insert menu and enter the macro into the module.
The size settings are hard-coded, but you could enhance the macro to solicit both from the user. If you’re more comfortable working with centimeters, use the CentimetersToPoints method instead of InchesToPoints. You could also supply the dimensions in raw values, but that’s not as exact as using one of the measurement conversion methods. For instance, two inches is approximately 144 points and four inches is 288. The equivalent statements follow:
cht.Height = 144
cht.Width = 288
If you don’t know how to convert inches to points, use can use an online calculator.
To run the macro, return to Excel and select the sheet with the charts you want to resize. Click the Develop tab and then choose Macros in the Code group. Choose ResizeCharts() and click Run. In Excel 2003, choose Macro from the Tools menu, and then select Macros from the resulting submenu. Choose ResizeCharts() and click Run.
Remember, this macro works only on the active sheet, not the entire workbook.
