Pay attention to the [New Elite Charging Station] ability improvement is visible!
When analyzing data in Excel, most of them will use charts for analysis, because compared to tables, charts are more intuitive to show the data, and can quickly see the differences or relationships between the data.
In daily work, column charts and bar charts are often used when comparing data, but in fact, we can also use other charts for comparative analysis according to the specific situation of the analysis
Today I will share with you a few classic charts of comparative analysis, let's take a look at it!
1． Last year's revenue comparison with this year's analysis of the histogram
Column charts are one of the most commonly used charts and are often used for comparing data. The following is a brief introduction to the production of this chart.
Step 1 Open the Last Year vs Year Analysis Column Chart .xlsx file, select any data cell, such as cell B2, click the Insert Column chart or bar chart button in the Chart group under the Insert tab, and select the Clustered Column Chart option in the expanded list, as shown in the following figure.
Step 2 After inserting the chart, change the Chart Title to Last Year vs. This Year Revenue Chart, right-click on the chart's legend and select the Format Legend command in the shortcut menu that pops up, as shown in the following figure.
Step 3 Open the Format Legend pane, select the Top radio button in the Legend Options options area under the Legend Options tab, and uncheck the Show legend, but do not overlap with the chart checkbox, as shown in the following figure.
Step 4 Right-click on the gridlines in the chart and choose the Delete command in the shortcut menu that pops up, as shown in the following figure. You can also select the gridlines directly and press the "Delete" key to delete the gridlines.
Step 5 Change the font of the chart to get the "Last Year vs. This Year Revenue Chart" as shown in the image below.
2． A stacked column chart that displays the summed-up values
When you have multiple data series and want to emphasize the total comparison, you can use a stacked column chart to achieve this goal, as follows.
Step 1 Open the Stacked Column Chart .xlsx shown by summations file, select any data cell, click the Insert Column or Bar Chart button in the Chart group under the Insert tab, and select the Stacked Column Chart option in the expanded list, as shown in the following illustration.
Step 2 After inserting the chart, right-click the data series representing the total in the chart and select the "Format Data Series" option in the pop-up shortcut menu, as shown in the following figure.
Step 3 Open the Format Data Series pane, select the No Fill radio button in the Fill options area under the Fill & Line tab, and the Wireless Bar radio button in the Border options area, as shown in the following figure.
Step 4 Switch to the Series Options tab and select the Minor Axis radio button in the Series Options options area, as shown in the following figure.
Step 5 Right-click the data series representing the total again in the chart, and select the "Add Data Label" and "Add Data Label" command in the pop-up shortcut menu, as below screenshot shown.
Step 6 Right-click the secondary axis and choose the Delete command in the shortcut menu that pops up, as shown in the following figure. You can also press the "Delete" key directly.
Step 7 Select the "Total (Piece)" legend item in the legend, press the "Delete" key, delete it, set the fill color for the data series of the chart, and adjust the position of the data label to the top of the column, beautify the chart, you can get the effect as shown in the following figure.
According to the chart, users can compare and analyze the number of sales of commodity A and B on a certain day, and can also compare the number of sales under multiple days.
3． Operating expenses versus bar chart
In addition to using column charts to visualize how your data compares, you can also use bar charts to do the comparison horizontally.
Step 1 Open the Operating Expense Vs Bar Chart .xlsx file, select any data cell, click the Insert Column chart or bar chart button in the Chart group under the Insert tab, and select the ClusterEd Bar Chart option in the expanded list, as shown in the following figure.
Step 2 After inserting the chart, you can see that some of the expense category items are not displayed in the chart because the chart is not high enough. Position the mouse pointer over the middle handle on the bottom border line of the chart, at which pointer changes to a shape, hold down the left mouse button, drag down, and increase the height of the chart, as shown in the following figure. When you're done, you can release the mouse and apply the same method to change the width of the chart.
Step 3 Double-click the Vertical (Category) Axis, open the Format Axis pane, and select the Reverse Category check box in the Axis Options area under the Axis Options tab, as shown in the following figure.
Step 4 Select the horizontal (value) axis in the chart and set the Maximum value to 10 000.0 under the Axis Options tab under the Format Axis pane, as shown in the following figure.
Step 5 Select the data series in the chart and set the "Class Spacing" to "90%" under the Series Options tab of the Format Data Series pane, as shown in the following figure. Close the pane when you are finished.
Step 6 Remove the horizontal (value) axis, add data labels, change the chart title, and set the fill color of the chart data series, as shown in the following figure after beautifying the chart. Based on this chart, users can visualize spending across expense categories.
4． Radar charts for comparison across multiple categories
The radar chart is radiated outward by the center of multiple coordinate axes, each multidimensional data in each dimension of the value occupies a coordinate axis, and connected with the data points on the adjacent axis, forming an irregular polygon.
Radar charts can compare multiple categories of a single series or multiple series at the same time, especially for combined comparisons between series.
It should be noted that there must be 3 or more dimensions to be suitable for radar charts. Of course, the dimensions should not be too much, otherwise it will affect the aesthetics and greatly reduce the readability of the chart.
Step 1 Opens the Radar Chart .xlsx file for Multiclass Comparison, where A is listed as the 8 main performance items for a display, and columns B and C are scored out of 10 for the two factories that tested and produced the product. Select any data cell, click the Insert Surface Or Radar Button in the Chart group under the Insert tab, and select the Fill Radar Option in the expanded list, as shown in the following figure.
Step 2 Delete the chart title, adjust the position of the legend, and right-click the gridlines in the chart to select the "Format Gridlines" command in the pop-up shortcut menu, as shown in the following figure.
Step 3 Open the Format Major Gridline pane and select the Solid Radio button in the Line Options area under the Fill & Line tab to set the appropriate color, as shown in the following figure. Then set the appropriate settings for the two data series in the chart
Fill colors and lines.
Step 4 After completing the setup, you can see that there are no center-starting ray-like lines in the radar chart, as shown in the following figure. Although this ray does not affect the final analysis of the graph, it is always poor in professionalism and cannot be set by radar axis (value).
Step 5 At this point you can change the chart to a different type to set, click the "Change Chart Type" button in the "Change Chart Type" button in the "Type" group under the "Chart Tools - Design" tab, open the "Change Chart Type" dialog box, select the "Area Chart" option under the "All Charts" tab, and then select any chart subtype, and click the "OK" button when you are finished, as shown in the following figure.
Step 6 Right-click the ordinate axis of the area chart, click the Outline button in the floating toolbar that pops up, and select the appropriate fill color in the expanded list, as shown in the following figure. Then apply the same method to change the outline color of the abscissa axis.
Step 7 Then change the area chart back to a radar chart to see that the rays starting from the center will be displayed in the radar chart, making the chart more aesthetically pleasing and personalized, as shown in the following figure. Through this chart, the user can see that the display produced by Plant A is basically better than Plant B in terms of performance, except for the two performance indicators of "viewing angle" and "brightness".