9 Steps to Simpler Chart Formatting

A complaint about many charts in general, and Excel charts specifically, is that they look awful and are hard to understand. However, you have the power to make your charts clear and clean and easy to read. The way to improve your chart is to remove clutter and reduce the amount of ink used to print the chart. To do this you must keep asking: does the chart need this feature, and if so, does it need to stick out so strongly?

By “Simpler Chart Formatting”, I don’t necessarily mean easier ways to format the chart. Instead, I mean ways to make the formatting of a chart simpler, less cluttered, and easier to understand. The techniques are also pretty easy. At first, the hard part is just remembering to carry them out.

As we all know, the Excel defaults are pretty ugly. They are somewhat better in Excel 2007, but I still never use the defaults. Aside from this set of images, I will stick to “Classic” Excel, that is, Excel 2003 and earlier, but the general concepts hold true for any version of Excel, and any graphics package.

Default Excel Column Chart     Default Excel XY Chart
Default chart formats in Classic Excel (2003 and earlier).

Default Excel Column Chart     Default Excel XY Chart
Default chart formatting in Excel 2007.

1. Backgrounds

The default charts in Excel 2003 and earlier feature a dull gray background (British and Canadian versions use dull grey instead). The first step to cleaning up your chart is to change this ugly background to white.

Excel Column Chart     Excel XY Chart
Removing the muddy background is a major improvement.

2. Borders

How important is the border around the plot area, around the legend, or around the entire chart? The legend never needs a border. Other borders can often be removed entirely, or at least lightened.

Excel Column Chart     Excel XY Chart
Removing the outer border and the legend border cleans up the charts.

Excel Column Chart     Excel XY Chart
Removing the plot area border and gridlines makes the charts simpler.

Sometimes I use a light gray chart area border on embedded charts, so the chart border matches the gridlines between cells in the worksheet.

Excel XY Chart

Sometimes a light border is okay.

3. Gridlines

If your chart uses gridlines, they should be the lightest features on your chart. The custom palette I use for my own work has a special gridline gray several shades lighter than the 25% gray on the default palette. But often gridlines are not even needed.

Excel Column Chart with Dark Gridlines     Excel Column Chart with Light Gridlines
Above: Dark gridlines (left) and light gridlines (right).
Below: No gridlines and no plot area border.

Excel Column Chart with No Gridlines

4. Axes

De-emphasize the axis lines by using a light or medium gray instead of black. You could even use medium gray for the axis tick text, but I usually stick with black text. For a category X axis, you can eliminate tick marks.

Excel Column Chart     Excel Column Chart

If you are using gridlines, you may be able to remove the corresponding axis line, leaving only the tick labels.

Excel XY Chart

You can often unclutter a chart by using fewer tick mark labels. As pointed out in the comments, keeping the unlabeled tick marks is useful.

Excel XY Chart     Excel XY Chart

5. Number Formatting

Use custom number formats to reduce the complexity of axis labels and other text in the chart.

Excel XY Chart     Excel XY Chart

Above left: original cluttered axis labels.
Above right: remove unnecessary ‘cents’.
Below: replace thousands by ‘K’ suffix.

Excel XY Chart

6. Chart Types

Replace every 3D chart with the corresponding 2D type.

Excel 3D Column Chart     Excel Column Chart

Only use pie charts if there are three or fewer wedges. Use column or bar charts for more categories.

Excel Pie Chart     Excel Pie Chart
Cluttered pie (above left) and simpler pie (above right).

Excel Column Chart     Excel Bar Chart
Unclutter a pie chart’s data by using a column or bar chart.

Use column charts to show values for discrete categories. Use line charts to trends over time.

Excel Column Chart to Show Values for Categories     Excel Line Chart to Show Trends in Values

7. Series Formatting

For column, bar, and area charts, remove the black outline. Use light to medium colors for fills, and don’t use patterns or gradients. Use darker, saturated colors for lines and markers. Use colors with appropriate contrast against the background and compared to other series colors. Avoid combinations that react with each other.

Too gaudy (left) and too faint (right).

Excel Column Chart     Excel Column Chart

Better color combinations.

Excel Column Chart

Excel Area Chart     Excel X& Chart

8. Horizontal Text

Vertical text is difficult to read, and inclined text on a monitor is bad because of distortion of the characters.

Excel Column Chart     Excel Column Chart

In a column chart, Excel will leave out some tick labels if they would overlap, but this leaves the readers guessing about the missing labels.

Excel Column Chart     Excel Column Chart

Turn the whole chart on its side.

Excel Bar Chart

9. Labels, not Legends

It is easier for a reader to identify series if they are directly labeled. Using a font color that matches the series formatting is also helpful. A legend takes up valuable space and makes the reader divert attention back and forth.

Excel Pie Chart     Excel Pie Chart

Top left: Ineffective Legend. Top right, bottom left and right: Effective Labels.

Excel Column Chart     Excel XY Chart

 

Peltier Tech Charts for Excel

Comments

  1. Jon, thanks for the clarification on the Commonwealth background colors :). Seriously, this is a good reference.

    I don’t do many charts but have worked on some lately for other people at work. They specified pie charts, but I had taken all the comments about the ineffectiveness of pie charts to heart, and designed some pretty clean bar charts. I stopped somebody in the hall and asked them what the charts meant. I then realized one critical fact about pie charts: people know immediately what they mean, i.e., that the slices are part of a whole. They don’t necessarily get that with bar charts. I’m sad to admit the finished product contains pie charts.

  2. You can often unclutter a chart by using fewer tick marks.

    I definitely like to unclutter a chart with fewer tick mark labels, but I often find the ticks themselves to be harmless enough at the density chosen by Excel by default, so I reduce the labelled (long) ticks, then introduce unlabelled (short) ticks to restore the original density.

    If I’m feeling particularly energetic, or the scale warrants it, I sometimes use dummy axis techniques to simulate a three level scheme, long labelled, long unlabelled, and short. I find this particularly useful for long horizontal axes where I don’t want labels overrunning each other, but don’t want to lose the accuracy of the scale.

    You can easily mark multi-century-long time series this way, with labels every fifty years, long ticks every ten years (5:1), and short ticks every two years (5:1 again). Points are identifiable to the exact single year by whether they fall on a tick or between ticks.

    Another item on my never-ending list of demands to MS is that they implement the three-level tick scheme as default to avoid the workaround. I’m also inclined to think that they should reference each lesser tick by how many there are to the next level up, instead of absolute size, since 99% of applications will involve the same simple small integer ratio.

  3. It ‘s quite great. thank you !
    To my surprise, as a expert , a maestro of excel,you write these basic things yet. Is it a part of your new book?

  4. Anonymous –

    Is that you Derek? Good point about the tick marks. I’ve adjusted that section of this post to reflect this.

  5. Yes, well-spotted (it was the never-ending list of Excel wants that gave me away, no doubt). I don’t know my details weren’t automatically filled in that time.

  6. “But usually gridlines are not even needed.” – I’d disagree. Usually tick marks aid in the assessment of patterns. See e.g. Section 3 of W. Cleveland. A model for studying display methods of statistical graphics. Journal of Computational and Graphical Statistics, 2: 323–364, 1993. URL http://stat.bell-labs.com/doc/93.4.ps. Tufte’s advice is generally good, but he often goes too far and removes things that actually are important. See e.g. W. A. Stock and J. T. Behrens. Box, line, and midgap plots: Effects of display characteristics on the accuracy and bias of estimates of whisker length. Journal of Educational Statistics, 16(1): 1–20, 1991.

    Otherwise, great advice!

  7. Hadley –

    Maybe I should have said “sometimes” instead of “usually”. I admit to using gridlines frequently, maybe about half the time.

    I concur that Tufte sometimes goes too far towards minimalism with his data/ink philosophy, and he tends to blame the tool for the sins of the artisans. The tool does have some ill-advised defaults and capabilities that ought to be ignored, and there is no easy way for the craftsmen to learn the proper use of the tool.

    This was originally going to be “10 Steps…”, but I only came up with nine good topics. Perhaps #10 should be something along the lines of “your mileage may vary” or “like all rules, these may be selectively and carefully ignored”.

  8. Great article, thanks for taking the time to write it. I have a question about labels vs. legends. I’d like to implement the label on the last point like your example shows, but I’m a bit confused about how to do this with excel 2002. Is it possible?

    Thanks,
    Steve

  9. Steve –

    Select the series, then select the last point, right click and choose Format Data Point, select the Data Labels tab, and choose the Series Name option (I believe Excel 2002 introduced these options). If this option is not available, simply use any of the options, then edit the resulting label.

    You can right click on the data label to bring up its distinct formatting dialog.

  10. Number 8 is such a simple tip yet not done often enough. I see text at crazy angles so it fits (sort of), but you end up tweaking your neck trying to read it.

    Number 9 is one that comes at a perfect time. In the next day or two I have a post that shows how to label line graphs instead of using a legend. Stay tuned.

    Great list! And I like the fact that it’s a list of 9. Everyone uses a list of 10… and ten is overrated. Nine is the new ten.

  11. I’m embarrassed to see how many of these I am guilty of, especially #6 (pie charts) and 8. But charting was never my strong point; I’m sure someday when I need to do it, I’ll figure all this out.

    –JP

  12. Thanks for your reply John,

    Using other tips on this site I have a chart that auto updates as new data is added. I want to use labels like your example 9, but see that as the chart auto extends to include the new data it automatically adds a new label meaning my last 2 values have labels and it all looks a bit ugly.

    So I have some VBA that deletes all but the last label, I thought I’d share it.

    ActiveSheet.ChartObjects("Chart 8").Activate
    For i = 1 To ActiveChart.SeriesCollection.Count
        For j = 1 To ActiveChart.SeriesCollection(i).Points.Count - 1
                on error resume next
                If ActiveChart.SeriesCollection(i).Points(j).HasDataLabel Then
                    ActiveChart.SeriesCollection(i).Points(j).DataLabel.Delete
                End If
                on error goto 0
        Next
    Next
     

    I’m not convinced by the “on error resume next” bit in there, but I’m seeing errors if I try to check for the existence of a label using the hasdatalabel property where I have empty cells.

    Anyway, this might be useful for someone. Thanks again for your post.
    Cheers,
    Steve

  13. Steve –

    Thanks for the code. I actually have a similar routine that labels the last point in a series:

    Label Each Series in a Chart

  14. Back when I did this sort of thing monthly, I knew even less VBA than I do today. I dealt with the “moving end label” problem by entering the data back-to-front, and graphing them backwards. Entering a new month meant copy-inserting the first row into a new second row, then changing the first row. Easier to do than to describe.

  15. Derek –

    I was going to suggest this as an alternative, then I tried it with my sample data, and it didn’t work. I was using dates and a date axis, which Excel sorts internally before plotting, so the first point ends up being the last point, and the label doesn’t keep up with it.

    However, one alternative I’ve used (maybe I should save it as another post) is to make a new series which plots hidden points using the X and Y values of the last point in each series. Through the magic of OFFSET and LOOKUP, these points always link to the last points in each series.

  16. Huzzah!
    Great post.
    Now if I could only get everyone here at work to adopt these simple changes!

  17. Jon

    Thanks for your very helpful blogs. I have been trying to write code for re-formatting charts. I’ve been battling trying to work out how to change the font size of the axes numbers (xlValue and xlCategory). I would have thought that something like this might have worked:

    ActiveChart.Axes(xlValue).Select
    With Selection.Font
    .Size = 8
    End With

    Have you got any ideas on how the font size can be changed?

    Jim

  18. Jim –

    Whenever I get stuck, or I’m too lazy to consult the object Browser, I record a macro:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 10/23/2008 by Jon Peltier
    '
    
    '
        ActiveChart.Axes(xlValue).Select
        Selection.TickLabels.AutoScaleFont = True
        With Selection.TickLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
    End Sub
     

    This can be cleaned up:

    Sub ResizeAxisTickFont()
    ' Macro cleaned up 10/23/2008 by Jon Peltier
    
        With ActiveChart.Axes(xlValue).TickLabels.Font
            .Size = 8
        End With
    End Sub
     
  19. Thanks again for your help, Jon. Very much appreciated!

    Actually, I had tried to record the macro to format both axes. This is is how it turned out:

    Sub Macro6()
    ‘ Macro6 Macro
    ActiveSheet.ChartObjects(“Chart 1”).Activate
    ActiveChart.Axes(xlValue).Select
    ActiveSheet.ChartObjects(“Chart 1”).Activate
    ActiveSheet.ChartObjects(“Chart 1”).Activate
    ActiveChart.Axes(xlCategory).Select
    End Sub

    Is there a setting that I’ve got turned off that causes the macro recorder to miss internal operations once the Axes(xlValue) is selected? Or is this just a quirk of Excel 2007?

    Best regards, Jim

  20. Jim –

    Excel 2007 uses new shapes for the elements of its charts. Development of these new shapes was finished late in the process of finishing Office 2007, and there was not time to incorporate them into the macro recorder. When you record a macro involving shapes, the recorder misses everything, and when a recorded macro deals with charts, it misses almost all formatting. There is enough compatibility with previous versions that code recorded in 2003 will (mostly) work in 2007, and the formatting code in 2003 will still produce thee equivalent formatting in 2007.

  21. Great tips there Jon, I’ve made this standard reading for all involved in creation of charts at my workplace.

  22. Great site–lot’s of good advice, and this article is no exception! Tip #8 concerns me, in that some scientists and engineers are taught to read a chart with the x-axis as the independent variable and the y-axis as the dependent. Flipping them willy-nilly may lead to false conclusions about what the author is trying to present. I suggest this one be included in the category of “consider when appropriate”.

  23. Hi George –

    Even scientists and engineers should be flexible enough to get the swapping of dependent and independent axes, and in fact, they would probably understand more quickly that the vertical axis in a horizontal bar chart is the X axis in an Excel chart.

    Of course, a bar or column chart is generally inappropriate for most scientific or engineering charts, eh?

  24. Hi John,

    Just want to thank you for your great website. Keep up with the good work.

  25. Definitelly, it’s a great example for those improve step by step. good job!

  26. Jon – firstly thankyou so much this site of yours helps me so much it ‘s untrue – everyone at work thinks I’m some sort of excel expert- and it’s mostly because of your simple easy to follow explanations. however I have run into some trouble that has me stumped.
    I need some help as my VB is more basic than most. I have a page of graphs (52 of them to be honest – 26 line graphs and 26 bar graphs of the same data – my boos likes a choice!) I was having to update them on a monthly basis which was becomming painful as you can no doubt imagine. So I wrote a little macro to create a new lot of graphs based on dynamic ranges and another to clear out the previous graphs at the touch of a button – great! it worked almost first time too! – then I added a little bit more to get the line graphs and bar graphs to conform to the traffic light format the graphs display – a simple red, amber, green lines / bars and a grey for N/A’s. I used a custom templates and it seemed fine till I made it live and let others use it – now it seems to have switched axies on most of the graphs and is duplicating the first 2 graphs (both of which are always fine and formatted correctly, the duplicates and all the following graphs have their axies’s switched and I have no idea why. The duplication seems to be slightly perverse as well when I simply moved the graph titles to compensate for the duplicates it duplicates 3 times so my headings are still off – is it my code or is my computer just trying to wind me up?

    See code below you may recognise the last bit for ordering the graphs it’s one of yours – thanks again.

    Any ideas please

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_component_requirements_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_component_requirements_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_review_gate_meetings_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_review_gate_meetings_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_attribute_trades_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_attribute_trades_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_attributes_on_target_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_attributes_on_target_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Does_the_IPT_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Does_the_IPT_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_there_an_overall_cht”)
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_there_an_overall_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_Intellectual_Property_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_Intellectual_Property_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_checks_for_infringement_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_checks_for_infringement_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Does_the_definition_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Does_the_definition_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_level_of_definition_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_level_of_definition_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_interfaces_suitably_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_interfaces_suitably_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_component_aligned_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_component_aligned_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_supply_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_the_supply_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_manufacturing_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_manufacturing_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_technology_readiness_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_technology_readiness_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_aftermarket_requirements_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_aftermarket_requirements_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_maintenance_policy_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_maintenance_policy_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_life__reliability_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_life__reliability_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_maintainability_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Are_maintainability_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_aftermarket_IPR_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_aftermarket_IPR_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_Aftermarket_strategy_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_Aftermarket_strategy_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_repair_technology_readiness_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_repair_technology_readiness_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_risk_management_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_risk_management_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_relevant_lessons_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_relevant_lessons_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_new_lessons_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Have_new_lessons_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_unit_cost_cht”)

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
    “c:\excel\templates\metrics excel graph templates\bar default.crtx” _
    )
    ActiveChart.SetSourceData Source:=Sheets(“data sheet”).Range(“Is_unit_cost_cht”)

    Dim iChart As Long
    Dim nCharts As Long
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nColumns As Long
    dTop = 75 ‘ top of first row of charts
    dLeft = 100 ‘ left of first column of charts
    dHeight = 225 ‘ height of all charts
    dWidth = 375 ‘ width of all charts
    nColumns = 2 ‘ number of columns of charts
    nCharts = ActiveSheet.ChartObjects.Count
    For iChart = 1 To nCharts
    With ActiveSheet.ChartObjects(iChart)
    .Height = dHeight
    .Width = dWidth
    .Top = dTop + Int((iChart – 1) / nColumns) * dHeight
    .Left = dLeft + ((iChart – 1) Mod nColumns) * dWidth
    End With
    Next

    End Sub

  27. James –

    Couldn’t you define the series data in each chart to use the named ranges in the worksheet, thus avoiding the deletion and creation of all those charts? For example, define Is_Unit_Cost_X and Is_Unit_Cost_Y for the last chart listed in your code, select the chart, go to the Select Data dialog, and instead of cell addresses for X and Y, enter these names. This will remove 98% of the code, probably more, because the last bit looks like it’s just positioning the charts, which wouldn’t have to be done with the existing charts.

  28. Jon, you’re a legend, thanks. That is deffinately the way to go with this.

Trackbacks

  1. […] van Office eenvoudiger zijn om professionele grafieken te maken tot die tijd is het handig om de 9 stappen van John Peltier te gebruiken om grafieken van onnodige opsmuk te bevrijden.   March 03 […]

  2. […] van Office eenvoudiger zijn om professionele grafieken te maken tot die tijd is het handig om de 9 stappen van John Peltier te gebruiken om grafieken van onnodige opsmuk te bevrijden.   March 03 […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0