--- title: Creating and formatting a chart automatically in Excel with VBA description: Creating and formatting a chart automatically in Excel with VBA --- [Skip to content](https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba#main-content) ![Conny-Söderholm](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg) - [Home](https://www.connysoderholm.com/) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) Open main navigation Close main navigation - [Home](https://www.connysoderholm.com/) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) July 3, 2018 # Creating and formatting a chart automatically in Excel with VBA ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) Share: [facebook-f icon](http://www.facebook.com/share.php?u=https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba) [Twitter icon](https://twitter.com/intent/tweet?url=https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba) [pinterest-p icon](http://pinterest.com/pin/create/link/?url=https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba) [envelope icon](mailto:?body=https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba) # Creating and formatting a chart automatically in Excel with VBA Using Excel involves creating a lot of charts and tables. Most of the time, the operations are really simple but time consuming. Creating and formatting a chart takes time. In this post, we are exploring how we can create a chart automatically with some VBA code. ## The preparations and the result Start by creating some data labels and values in the range A1:B7. I created the labels "Load \[%\]" for the x-axis and "Fuel consumption \[g/kWh\]" for the y-axis. Press alt+F11 to go into the VBA editor. Write or paste the code below in the VBA editor. Go to the run macro and run the Creat\_chart sub ## The code First, we are adding the chart to the active sheet. The chart is a Scatter with lines and with the style 2 formatting (241).  Next, we select the source data from the cells A1:B7 on the active sheet. You can format the axes with the MaximumScale and MinimumScale methods. The height and width are done with the With (pun intended). We finish by setting the chart's title, x-axis title, and y-axis title. The axes titles are picked from the cells A1 and B1 where we had the text for the axes. ``` Sub Create_chart() ' Add the chart and select it ActiveSheet.Shapes.AddChart2(241, xlXYScatterLines).Select ' Set the source data ActiveChart.SetSourceData Source:= ActiveSheet.Range("$A$1:$B$7") ' Set scales for x and y axes ' X-axis ActiveChart.Axes(xlCategory).MaximumScale = 100 ' Y-axis ActiveChart.Axes(xlValue).MinimumScale = 150 ' Set the height and width of the chart With ActiveChart.Parent .Height = 300 .Width = 600 End With ' Set chart title ActiveChart.ChartTitle.Text = "Fuel consumption at different loads" ' Add axis titles With ActiveChart 'X axis name .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("A1").Value 'y-axis name .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("B1").Value End With ``` Happy coding! [Excel](https://www.connysoderholm.com/blog/tag/excel) ## Related posts [Excel](https://www.connysoderholm.com/blog/tag/excel) ## [Copy an Excel 2016 formula without changing the cell references](https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) June 13, 2018 How do you copy an Excel 2016 formula without changing the cell references?- Have you copied a... [Read more](https://www.connysoderholm.com/blog/copy-excel-formula-cell-reference) [![](https://www.connysoderholm.com/hs-fs/hubfs/Imported_Blog_Media/28269236-99e7ea34-6b01-11e7-8ff9-21285576122f.png?height=200&name=28269236-99e7ea34-6b01-11e7-8ff9-21285576122f.png)](https://www.connysoderholm.com/blog/carnac-keypress-capture-software) ## [Carnac, Keypress capture software](https://www.connysoderholm.com/blog/carnac-keypress-capture-software) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) August 27, 2019 For anybody searching for a great Keypress capture software, that also is free, check out Carnac [Read more](https://www.connysoderholm.com/blog/carnac-keypress-capture-software) [Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel) ## [Control Excel with Python & OpenPyXL](https://www.connysoderholm.com/blog/control-excel-with-python-openpyxl) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) August 1, 2019 I have finally published my first course, Control Excel with Python & OpenPyXL! [Read more](https://www.connysoderholm.com/blog/control-excel-with-python-openpyxl) [linkedin-in icon](https://www.linkedin.com/in/connysoderholm) [Follow us on Facebook](https://www.youtube.com/@connysoderholm1473) - [Home](https://www.connysoderholm.com) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) - [Privacy Policy](https://www.connysoderholm.com/privacy-policy) - [Disclaimer](https://www.connysoderholm.com/disclaimer) --- [![Conny Söderholm Logo](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg "Conny Söderholm Logo")](http://Conny%20Söderholm) Copyright © 2026 Conny Söderholm ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Conny Söderholm", "url" : "https://www.connysoderholm.com/blog/author/conny-soderholm" }, "dateModified" : "2023-07-26T14:39:22.458Z", "datePublished" : "2018-07-03T19:07:27.000Z", "headline" : "Creating and formatting a chart automatically in Excel with VBA", "mainEntityOfPage" : { "@id" : "https://www.connysoderholm.com/blog/creating-and-formatting-a-chart-automatically-excel-vba", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg" }, "name" : "Conny Söderholm" } } ```