Office hacks: Imitation knob circle chart cleverly made

Ordinary circular data charts lack visual impact. If the style of the ordinary circle chart can be modified and further beautified, the bland circle chart will be transformed into a three-dimensional knob chart with artistic effect (Figure 1).


1. Create a chart

Take the example of a product sales circle chart showing the percentage of sold and unsold. First, you need to process the original data before creating the chart. Enter “=100-B2” for the unsold data. On the basis of the original data need to add two auxiliary columns, the first column of auxiliary data all 1; the second column of auxiliary data will be the original data from two split into three data: the top and bottom two are the original data minus a relatively small number (for example, minus 5, that is, cell D2 enter “= B2-5”, cell D4 enter “= B3-5”), the middle is the sum of their subtracted parts (that is, cell D3 enter 10).

After the data is ready, select A1:B3 to create a circle chart. Select the data in the auxiliary column 1 (i.e. C1:C4), hold down Ctrl+C to copy, then select the circle chart, and then hold down Ctrl+V to paste, so that the data in the auxiliary column 1 is added to the chart. Similarly, the data in auxiliary column 2 will also be pasted into the chart (Figure 2).


2. Modify the chart

Right-click the chart, select “Select Data”, in the pop-up window “Legend Items”, move Auxiliary column 2 to the top, then Auxiliary 1, and the bottom is Sales Rate. Select the circle in the middle layer of the chart, right-click it, select “Set Data Series Format”, and in the “Fill and Line” tab, set it to no fill and no line. This way, the outer circle is spatially separated from the inner circle by using the hidden circle in the middle (Figure 3).


Next, select the data in the auxiliary column 1 (i.e. C1:C4), copy it, select the chart, paste it, and display it as the outermost circle on the chart. This is done to create the knob graphic in the center area. Select the outermost circle you just added and set its fill color to distinguish it from the other series colors. Select the whole chart and click on “Change Chart Type” to change the whole chart to a pie chart. From top to bottom, the chart type is changed to “Circle” in order, but the last auxiliary column 1 chart type select “Pie” (Figure 4).


Select the pie chart, right-click, select “Set Data Series Format”, and set the pie chart separation to 30%. Double click on a sector in the pie chart (i.e., select a sector) and drag the sector back to the center of the circle with the mouse. Use the same method to drag the other two sectors back to the center of the circle in turn (Figure 5).


Select the chart, click “Select Data”, select the series representing the pie chart at the legend item (i.e. the bottom auxiliary column 1), click “Edit”, and enter “={1}” at the series value in the pop-up window. “. Select the pie chart in the chart, and in the “Fill and Line” tab of “Set Data Series Format”, select “Gradient Fill” for Fill and “Gradient Line” for Line. “Gradient Line”, adjust the fill and line color as needed; then use the “Format” tab in the “Shape Effect”, add “Prism “Effect”, add shadows, etc., you can set different knob shape according to personal preferences and needs (Figure 6).


3. Beautify the chart

Select the innermost circle, right-click it, select “Add Data Label”, and set its fill color to no fill, no line, and hide it. Draw a circle with a self-selected graphic, select the circle, use the settings in “Shape Effect→Prism” to set the circle as a button, copy the circle button, select the data label “10” in the chart, right-click, select “Set Data Label Format”, select “Picture or Texture Fill” for Fill in the “Label Options→Fill and Line” tab, click “Clipboard”, check “Tile picture as texture”, and adjust the label size. Select all data labels, and in the “Text Options→Text Fill & Outline” tab, set the text fill to no color and hide it. Beautify the outer circle, including the use of gradient fill, set the shadow and other effects (Figure 7).


In a blank cell in the form of a formula to calculate the percentage of sales, such as in cell E2, enter “= B2/100”, and the format of this cell is set to a percentage; then add a text box on the chart, select the text box, edit the formula in the column to modify the cell where the formula (that is, enter “= E2” in the formula bar), so that you can get a data label that can automatically change with the data (Figure 8).


Finally, set the color of the data labels as needed, delete the legend, add the chart title, and set the background needed for the chart and you are done.

Leave a Comment