Software

Quick Tip: Apply a uniform size to all charts on an Excel sheet

After adding several charts to a sheet, you might want to adjust their sizes so they're all the same. You can do so manually or by using the easy macro.

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:

  1. Select all of the charts by holding down the [Shift] key as you click each chart to create a multiple selection.
  2. Click the contextual Format tab. In Excel 2003, right-click the selection, and choose Format Object.
  3. 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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
claycorbin
claycorbin

ok, this is helpful, but what about "plot" area?  Sometimes different styles of charts and axes produce different plot areas.  so, even though the chart area is the same, the actual plots within the charts have different dimensions. is there an analogous function or macro for this? thanks!  

ps. i'm not much on writing macros, i could do it tho.  however, is there a button and numbers i could push ;)  ?

JSmotherman
JSmotherman

I noticed your comment at the end of the article that the macro only functions for the active sheet. You can easily add a second macro that resizes all the charts in all the sheets of the current workbook. Here's an example: Sub ResizeAllCharts() 'Make all charts in the workbook a uniform size. Dim ChtObj As ChartObject Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets For Each ChtObj In WkSht.ChartObjects ChtObj.Height = Application.InchesToPoints(2) ChtObj.Width = Application.InchesToPoints(4) Next Next End Sub Insert both macros. Then if you want to resize the charts on a single sheet, use the ResizeCharts() macro, and to resize all the charts in the workbook, use ResizeAllCharts().

simon.freeman
simon.freeman

Sue - be nice to have the macro that asks for the dimensions - Thanks.

Dr_Zinj
Dr_Zinj

I frequently have to do multiple charts within a spreadsheet for later transfer to another document. Uniform sizing makes that process a lot smoother. Just never had the time to look it up. Thanks!

JSmotherman
JSmotherman

add the following lines right above the line that starts "for each" Dim iChtHt As Integer, iChtWd As Integer iChtHt = Application.InputBox("Enter chart height:") iChtWd = Application.InputBox("Enter chart width:") Then on the two lines where the original macro sets the height to 2 and the width to 4, change those two lines to: cht.Height = Application.InchesToPoints(iChtHt) cht.Width = Application.InchesToPoints(iChtWd) That should do it.

ssharkins
ssharkins

I'm glad you found this useful!

Editor's Picks