Excel Chart: How to Clip Picture Background into Non-Regular Shape in Plot Area

Download Office Tab

Enable Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

Yesterday, a friend sent me a message and demonstrated how to display an no-regular picture background in plot area. As we all know, it's easy and quick to fill plot area with a rectangle picture in all version of Excel. But it's very difficult to clip the picture into a non-regular shape. This friend's ideal is some creative even though it's not very complex. I review some magazines and find an example for this scenario.



I collect some data from newspaper and practice his ideal. The and get the following chart.

Prerequisite: Prepare Data.
Suppose we have the following data. Column B is pseudo CPI (Customer Price Index), which becomes higher and higher recently. Column C is our buy capability. It looks mismatch with the increase of CPI. Where is our money?

Step 1: Prepare two auxiliary data as area chart.
Column D is the auxiliary data, which will be created as a shape to cover the picture background. In fact, we all know, we cannot create a non-regular picture background directly. But we can create some no-regular shapes and overlap them over the background picture. The result background will displayed as non-regular.

Select column $A1-$D6 and insert a stacked area into your workbook. The area chart looks like:

Step 2: Plot the Column C & the auxiliary area chart to the secondary axis.
Select the area chart matching with column C and column D and plot them to the secondary axis.




And then limit both the primary & secondary vertical axis to [0-100].Your chart will become the left picture. Select the red one and change its fill to transparent.

Step 3: Add real data into the chart.
The real data will be created as a scatter line chart. Select column B, C again and insert them into the draft chart. Choose series A and change chart's type to scatter line chart.

Step 4: Change plot area’s and all area charts’ background.

Step 5: Add special mark to line chart.
Prepare the following icon. Select the first icon and press Ctrl+C to copy it to clipboard. Select the CPI line and press Ctrl+V to paste the icon as it's mark icon. Do the same operation for the other one.

Step 6: beautify it with Orange + Green theme.
Prepare the following icon. Select the first icon and press Ctrl+C to copy it to clipboard. Select the CPI line and press Ctrl+V to paste the icon as it's mark icon. Do the same operation for the other one.

Excel Chart: How to Alternate “Plot Area” Background in Excel

Ad Office Tab

Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

The "Business Week" has given us many cool examples about what are BUSINESS charts. These charts are much too professional and all of us want to create this kind of chart in our daily report. Ucmapi will start a thread to interduct how to create them in the most common used software - Microsoft Excel. 

Let's begin with the above business week's example. How to alternate plot area color with different color. We have two methods to solve this issue. But here, we only interduct one. We ask an auxiliary data sequence to simulate interlaced coloring. Even though this method is a little technique, it is not too difficult. We only need 7 steps. 

[Notice: The chart is created on Excel 2010]

Prerequest: Suppose we have two data sequences and one legend sequence. We add an auxiliary column D and fill with 0,1,0,1...

Step 1: Insert a blank 2D-Bar chart into your worksheet.

Step 2: Select the auxiliary data sequence and press Ctrl+C to copy data to clipboard. Then click the blank to select chart and press Ctrl+V. Two bars will display in chart like the following picture. 

Step 3: [Important step] Convert the auxiliary data sequence into interlaced coloring band.

3.1 Change the order and Maximun value of “Primary Horizontal Axis”. 

 

3.2 Change the show direction of “Primary Vertical Axis”.

Step 4: Change default gap of the auxiliary data sequence. Select auxiliary data sequence by clicking on the blue bar and click right mouse button to launch context menu. Choose "Format Data Series..." to change "Gap width" to 0

 

  

Step 5: Add real data sequence now. Select the second column and press Ctrl+C and select chart then press Ctrl+V. The second data sequence will be added into chart. But the current picture is very confuse.

Step 6: Select the new added series and right click mouse. Select "Change Series Chart Type..." and select 2D-Column. Add new series and change all new series's gap width back to 100%.

 

Step 7: Insert a "Rectangle" shape into your chart as title and then format the basic picture to make it professonal. Change "Chart Area" color to gray. Change "Plot Area" color to gray too. Change "Data Series" color to light gray. Great, you’ve got a professional chart now!

The finial chart looks like:

Copyright © Ucmapi Software Studio

Mail to support@ucmapi.com when you have issues