How-to Make an United States Flag Excel Chart (Happy 4th of July)

Happy July 4th (United States Independence Day).

I am home and not working too much this holiday, but wanted to send out a post on how to make a flag in Excel using Combination Chart.

Here is what the final chart looks like:image

Looks pretty good to me Smile but I am biased.

 





The Breakdown

1) Create 2 Data Sets (One for Stars and one for Stripes)

2) Create Stacked Bar Chart

3) Reverse Vertical Axis Categories

4) Change Colors of the Bars and Gap Width



5) Add a New Series

6) Changes Series to XY Scatter with Markers Only

7) Change XY Scatter Series Data Set

8) Insert Star Shape and Change Fill Color

9) Copy and Paste Star to XY Series





10) Change Axis Min and Max

11) Chart Clean Up

 

Step-by-Step

1) Create 2 Data Sets (One for Stars and one for Stripes)



This is how I set up my data.  Columns A:D are for the Stripes and the Stars are Column E:Fimage

 

2) Create Stacked Bar Chart

Highlight A2:D15 and the Insert a Stacked Bar Chart:image

image





Your chart should now look like this:image

 

3) Reverse Vertical Axis Categories

Click on the Vertical Axis and press CTRL+1 to bring up the Format Series Dialog Box and then select “Categories in Reverse Order” SNAGHTML6d936a

Your chart should now look like this:image



 

4) Change Colors of the Bars and Gap Width

Now select any series and press CTRL+1 and change the Gap Width to No Gap (0%)SNAGHTML700e92

Your chart should now look like this:image

This start with the Blue Series and change the Fill Color to Blue if it is not already by pressing CTRL+1 and from the Fill section, choose Solid Fill and then Dark Blue.image





Repeat this step for the White series and choose white as the Solid Fill Color.  And repeat the same steps for the Red series fill color.  Your chart should now look like this:image

 

5) Add a New Series

Click on the chart, then from the Design Ribbon, choose the Select Data ButtonSelectDataMenu

Then choose the Add button from the Legend Entries (Series) area:SNAGHTML79c083



Then from the next dialog box, enter Stars as the series name and press the okay button:SNAGHTML7acbd8

Your chart will now look like this:image

 

6) Changes Series to XY Scatter with Markers Only

Now we need to change the Stars series Chart Type to a XY Scatter Chart Markers Only.  Do this by clicking on the Stars data series in the chart.  Then from the Design Ribbon, click on the “Change Chart Type” button from the Type Group.  ChangeChartTypeMenu





Then choose the XY Scatter with only markers from the Change Chart Type dialog box:SNAGHTMLfe2caf

Your chart should now look like this, but don’t worry, we will fix this in the next step:image

 

7) Change XY Scatter Series Data Set

Now that we have changed the Excel Chart Type, we need to update the data series for the Stars data points.  To do this, first click on the chart and then click on the Select Data button on the Design Ribbon.SelectDataMenu



Then from the Select Data dialog box, choose the Stars data series and then click on the Edit button:SNAGHTML1033595

Then from the Edit Series dialog box, you need to update the X’s and Y’s to the data that represents the 50 stars in columns E and F:SNAGHTML10637c7

Once you click on OK button a few times, your Excel Chart should now look like this:image

 

8) Insert Star Shape and Change Fill Color





The Excel US flag chart is starting to get its final shape, but we need to change the Stars from regular markers to white stars.  To do this, we first need to create a Star shape from the Insert Ribbon and then choose the Shapes button from the Illustrations group.  Choose the 5 Point Star shape in the Stars and Banners group and then Drag/Drop the image of a small star into your spreadsheet:image

Now that you have created a star in your spreadsheet, it is most likely a blue fill color and we need to change it to a white fill color.  You can do this by clicking on the star and then press CTRL+1 and change the fill color to white: image

And also the line color of the Star shape to white or no line:image

 

9) Copy and Paste Star to XY Series

Now you want to make sure you have the white star shape selected and then press CTRL+C to copy the shape.  From there select your chart and then select the Stars data series: image

then once you have the stars data series selected, press CTRL+V to paste the star shape and it will replace the markers.  Your chart should now look like this:image

Wow, our United States Flag is almost complete.  The next few steps will make it all come together.

 

10) Change Axis Min and Max





Our axis Max values are affecting our data.  Click on the Secondary Vertical Axis and press CTRL+1 to bring up the Format Axis dialog box.  Then change the vertical axis Minimum to 0.0 and the Maximum to 13.0SNAGHTML1161f19

Your chart should now look like this:image

Notice how the stars are now in alignment.  Now click on the horizontal axis at the top of the flag and press CTRL+1 and format the Minimum to 0.0 and the Maximum to 1.0SNAGHTML11872a5

Your chart should now look like this:image

Almost done.  One last step.

 

11) Chart Clean Up

Now we just need to clean up the chart.  Delete the legend and all axis by selecting them one at a time and pressing your delete key.

Here is what your final July 4th Excel Chart Flag looks like:image

How cool is that?  Also, because it is done with an Combination Excel Stacked Bar Chart and XY Scatter Chart, your chart will line up.  If you tried to do this with lines and shapes and rectangles, you would have a tough time making them line up and you would have to group all the shapes together to make sure that if you changed the size of the image that it would maintain its shape.  Probably overkill as you can find images on the web, but this Excel chart shows you how to combine charts and add custom markers.  Pretty cool if you ask me Smile





 

Video Tutorial

 

Happy 4th of July!  Make sure to come back next week for other exciting charts and graphs for your Excel Dashboard presentations.

Steve=True





LEAVE A REPLY

Please enter your comment!
Please enter your name here