OmniDocHow to make an Excel chart: the complete steps from selecting data to drawing the chart
Explain the general process of making basic charts in Excel: first organize the data table, select the right area, select the chart type, and then modify the title, axis and legend. The method is applicable to most charts, and you can independently make a usable chart after reading it.
Key points of this article
- Write field names in the first row of the data table, and only put one type of data in each column so that the chart can correctly identify the series.
- When selecting data, bring the title row, Excel will use it as the series name or category name
- First select the chart type according to the data relationship: column chart for comparison, line chart for trend, and pie chart for proportion.
- After generation, use chart elements to complete the title, axis and data labels, so readers can understand it without reading the text.
- After drawing, check a few values and check the scale, and then copy it to a document or presentation
If you have a sales breakdown, experimental record, or survey statistics form in your hand, and want to turn the numbers into a picture that can be included in the report, you are often stuck in the first step: where to start. Many people directly select the entire table and insert it into the chart. As a result, the series is messed up and the horizontal and vertical coordinates are reversed. They can only delete it and redo it.
This article talks about the general method of making Excel charts: first organize the data into a structure suitable for charting, then select the area, insert the chart, and finally modify the title, axis and legend. This process is applicable to most charts such as column charts, line charts, and pie charts, and does not rely on the shortcut operations of a certain chart.
After reading, you should be able to complete a picture independently: know which cells to select, know how to judge whether the series is correct, and know where manual inspection is required after generation.
How to make Excel charts? One sentence conclusion
Conclusion in one sentence: first organize the data table into a structure of "one row, one record, and the first row is the field name", then select the area containing the title to insert the chart, and finally select the chart type according to the data relationship.
| your situation | Recommended practices | reason |
|---|---|---|
| Want to compare the numerical values of several items | Column or bar chart | The length is intuitive and easy to compare differences. |
| Want to see changes in data over time | Line chart | Connecting lines can highlight successive trends |
| Want to see the proportion of each part to the whole | Pie chart or donut chart, not too many categories | The fan shape is difficult to identify when there are too many categories |
| The data table has total rows or blank rows | First delete the total row, fill in the empty cells and then draw the graph | Total rows are treated as one data series |
| The two sets of data are quite different in magnitude. | Split into two graphs, or use secondary axis | Small values on the same coordinate axis will be squashed |
# before you start: confirm these points first
- whether the data is continuous and whether there are merged cells : merging cells will cause Excel to misplace the identification of rows and columns. Cancel the merge before drawing and fill in each cell.
- whether the header line is on the first line : only when the field name is placed on the first line can Excel automatically recognize it as a series name, and the legend is easier to understand.
- whether to express comparison, trend or percentage : this determines the type of chart. If the type is wrong, even if the number is correct, it is difficult for the reader to get to the point.
- whether the chart should be updated with the source data : if the diagram is only used for one-time display, you can paste the result of the formula as a numerical value first to prevent the chart from changing after the source data is changed.
# how to make a chart in Excel
- organize the data area : make sure that there is only one kind of data in each column, such as "month" column and "sales" column, and do not write two indicators in the same cell.
- Select data : drag the title row from the upper left corner to the lower right corner of the data with the mouse, and select the title row together. When the data is discontiguous, hold down the Ctrl key (macOS is the Command key) to select multiple areas at the same time.
- insert Chart : find the chart option in the relevant area of the software, and select the type of column chart, line chart or pie chart according to the data relationship.
- check the default chart : after generation, see if there are several series on the chart and whether the horizontal axis is the category you want. If not, use the switch row and column function in the chart tool to adjust the arrangement of the series.
- add chart elements : find the button next to the chart to add chart elements, or open the chart element option in the chart tool, check the chart title, axis title, data label and legend, and keep only what the reader needs.
- modify the title and axis : change the default "chart title" to a sentence that can explain the conclusion, such as "quarterly sales comparison (ten thousand yuan)", and indicate the units for the axis.
- uniform format : unify colors and fonts, remove excess gridlines, and make the data itself the focus of the graph.
- Save or copy : save the workbook. When you need to put it into the document, copy the chart and choose to paste as a picture, or keep the editable chart format.
# check item by item after completion
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Series quantity | Is the number of series in the legend consistent with the number of indicators you want to compare? | Switch rows and rows in case of inconsistency, or reselect areas |
| Is the value consistent with the source table? | Two or three data points are randomly selected and checked against the cells. | Check whether there are numbers in text format that are not recognized and change to numeric format |
| Abscissa order | Whether the time is from morning to night, and whether the order of categories is in line with expectations. | Check whether the date is in the true date format, or adjust the order of the source data |
| Axis calibration | Whether the starting point and spacing are reasonable, is there any artificial exaggeration of the difference? | Set the small value of the scale manually, or use a more appropriate chart type instead |
| Title and unit | Can the reader understand the picture without looking at the text | Description of supplementary units and data sources |
# frequently asked questions (FAQ)
1. The horizontal and vertical coordinates of the chart are reversed, what should I do?
Usually the selected area does not contain the title row, or the direction of the row is opposite to the default recognition. You can adjust it by using the function of switching rows and rows in the chart tool. If the adjustment is still messy, delete the chart, re-select the data area and insert it.
2. The numbers look normal, but the charts can't be drawn. Why?
When the number is aligned to the left and there is a small green triangle in the upper left corner of the cell, it is mostly in text format. Change the cell format to numerical value, and then redraw the picture.
3. The data is in PDF, how to make a chart quickly?
You can first use the PDF editor of the OmniDoc Desktop client (download.omnidoc.top) to identify the scanned piece, then export it to an Excel file, and finally draw a picture in Excel. The identified numbers need to be checked one by one, especially the decimal point and the minus sign.
4. Can the chart change automatically after the data is updated?
Yes. The chart is bound to the source data area, and the chart will be updated synchronously after the cell value is modified. If you add a new row, you need to expand the data area to a new row, or convert the data area into a table first, so that the new data is automatically included in the chart.
5. After the chart is done, how to put it into the Word document?
After copying the chart, select paste as a picture in Word to retain the appearance. The diagram in this way no longer changes with the Excel data, and the source data needs to be copied again after modification.
# Summary
The key to Excel charting is not the keyboard shortcuts, but the data structure and selection. First sort out the table, select the right area, and then select the chart type according to the data relationship, and finally complete the title and unit and check it item by item, and an available map will be completed.
