10 Tips for making charts in Excel

Here we disclose some tips for quickly creating the awesome pivot charts in Excel in a while. In the module, we will learn that without extra effort in some cases when we have to create the chart or make the report for the stakeholders, we can use some tips for making the charts. So we are discussing the top 10 tips.

 

Tip #1: Press ALT+F1 to quickly make a chart

(1) Click anywhere inside the data below, from cell B12 to B16. (2) Press ALT+F1 (make sure your F-lock is on). If you're using the laptop, try ALT+FN+F1. (3) Excel creates a simple chart for you. (4) Now change it to another type of chart on the design tab, click "Change Chart Type," and pick another chart that interests you.

Tip #2: Select specific columns before creating a chart.

(1) Click and drag to select cells B12 to B16 (from salesperson down to Sally). (2) Press and hold CTRL, and drag to select cells D12 to D16 (commission down to $10). Now let go of the CTRL key. (3) Press ALT+F1 (make sure your F-Lock key is on). If you're using a laptop, try ALT+FN+F1. (4) Excel creates a chart based only on the Salesperson and Commission columns. It excluded the sales column from the chart.

Tip #3: Use a table with a chart.

(1) Click inside the data below. Go to the insert tab, click table, and then click OK. (2) In cell F13, type Mar, and then press Enter. The table automatically expands to include the new column. (3) Type some numbers for each salesperson in the March column. (4) Notice the chart legend now has an entry for Mar, as well as the bars. That's the advantage of using a table with a chart: any new columns or rows will be reflected in the chart automatically.

Tip #4: Quickly filter data from a chart.

(1) Let's say you don't want this chart to include Doris's data. (2) Click the chart below , and then click the filters button to the right of the chart. (3) Uncheck Doris, and then click the Apply button at the bottom of the checkboxes. (4) The charts now exclude Doris's data (you can check the box for Doris to bring the data back if you want).

Tip #5: Use pivot charts when your data isn't summarized.

(1) Below, "Apples" and "Oranges" are repeated in several rows. This is called unsummarized data. Unfortunately, regular charts prefer the data to be summarized. (2) But PivotCharts can handle unsummarized data! Click inside the data below. Then on the Insert tab, click PivotChart. (3) Click Existing Worksheet, and then in the Location box, type E13. Click OK. (4) On the right, in the PivotChart Fields pane, click the product and Amount checkboxes. The PivotChart summarizes the data for you and charts it.

Tip #6: Create multi-level labels.

(1) Wouldn't this chart be better if it had another level of labels at the bottom that organized the columns by either "Fruits" or "Vegetables" ? (2) In cell G14, type the word "Fruits," and in cell G17, type the word "Vegetables." (3) Select the chart, and then on the Design tab, click Select Data. (4) With the dialog box open, drag to select cells G13 to I18 (from Department to 1,000). Click OK. Now the chart has another level of labels for "Fruits" and "Vegetables."

Tip #7: Use a secondary axis to create a combo chart.

(1) The chart below has two data series: Sales and Profit%. But we need profit % to be represented by a horizontal line on the chart, not a column. (2) Select the chart below, and then on the Design tab, click Change Chart Type. Click Combo in the lower left. (3) On the right, make sure the chart type for Profit% is set to line and the Secondary Axis checkbox is selected. (4) Click OK, and the profit percentage will be plotted with a line. Now you can compare the two series. For example, sales were low on 1/3, but profit was great!

Tip #8: Hook up a chart title to a cell.

(1) The chart below is called "Chart Title." That's not very helpful, so let's change that. (2) Click the chart title. (3) Then type "=" and click the green cell that contains the text "Week 1." (4) Press Enter. Now the chart title is "Week 1"—that's so much better. And from now on, the chart title will always show what's in the green cell. Try it out by typing something else in the green cell and pressing Enter. 

Tip #9: Split off slices into a second pie.

(1) We need to make a "pie of pie" chart, where the kiwis, grapes, and pears are "split off" into their own pie. (2) Click the chart, and then on the Design tab, click Change Chart Type. Then choose the Pie of Pie button. (3) We're not done yet. Right-click the smaller of the two pies, and choose Format Data Series. (4) In the Format Data Series pane on the right, go to the split series by menu, and choose value. Then type 1000 in the Value less than box. When you press enter, the kiwis, grapes, and pears are split off into their own pie, because each of their amounts is less than 1,000.

Tip #10: Hover over chart elements to get a preview.

(1) What should this chart look like with a legend ? With data labels? (2) Click the chart, and then click the chart elements button to the right. (3) Hover over any of the options (don't select one yet), and Excel will preview the elements for you. (4) When you see one that you like, click it to add the element to the chart.