Creating Effective Charts - Pro Office for iPad: How to Be Productive with Office for iPad (2014)

Pro Office for iPad: How to Be Productive with Office for iPad (2014)

Chapter 10. Creating Effective Charts

Excel for iPad gives you all the features you need to create attractive and compelling charts directly on your iPad. You can choose from a wide variety of chart types and subtypes to find the chart that presents your data clearly and persuasively. After creating a chart, you can change its type and subtype as needed, switch its source data or transpose its rows and columns, and give it the layout and style that will work most effectively.

After creating a chart, you can paste it into a Word document or a PowerPoint presentation in moments—and even change the chart type, layout, and appearance in that app.

Learning the Essentials of Charts in Excel

This section goes quickly through the essentials that you need to know to use charts effectively in Excel. It starts with the difference between embedded charts and charts on chart sheets and then cover the various components of charts—the chart area, plot area, axes, categories, data series, and so on.

Understanding Embedded Charts and Chart Sheets

In the desktop versions of Excel, you can create a chart either embedded on a worksheet or on a separate chart sheet. Placing a chart on a worksheet enables you to look at the chart alongside the data that produces it, which is often helpful when you’re creating the chart and deciding which data to include. Placing the chart on a separate chart worksheet lets you view or present the chart separately from its data.

At this writing, Excel for iPad can create only embedded charts, but it can display and edit charts on chart sheets as well. So if you need to create a chart on a chart sheet, you need to use a desktop version of Excel. You can then work with it on your iPad as needed.

Tip You can use a desktop version of Excel to switch an embedded chart you’ve created in Excel for iPad to a chart sheet. In Excel on Windows or the Mac, right-click or Ctrl+click the chart, and then click Move Chart on the contextual menu to open the Move Chart dialog box. Click the New Sheet option button, type the name for the chart sheet, and click the OK button.

Similarly, you can move a chart from a chart sheet to a worksheet so that you can manipulate it more easily in Excel for iPad. In the Move Chart dialog box, click the Object In option button, select the worksheet in the drop-down list, and then click the OK button.

Understanding the Components of a Chart

Excel’s charts vary widely in looks and uses, but most of them use the same set of components. Figure 10-1 shows a column chart of rainfall data for six months for seven weather stations, with the main parts of the chart labeled.


Figure 10-1. This column chart contains most of the typical elements of Excel charts. The chart area is the whole area occupied by the chart, and the plot area is where the data series are plotted

Chart Area and Plot Area

The chart area is the whole area occupied by the chart. If the chart has a white background, the easiest way to see the extent of the chart area is to tap the chart so that Excel displays a border around it.

The plot area is the area of the chart that contains the plotted data—in other words, the main part of the chart area, excluding the areas occupied by the chart title, the axis titles, and the legend (if it appears outside the plot area).

Chart Axes

Most charts have one, two, or three axes:

· Horizontal axis: This is the axis along which the data categories are laid out—for example, the weather stations in the sample chart. This axis is also called the category axis or the x-axis.

· Vertical axis: This is the axis along which the data series are laid out. This axis is also called the y-axis.

· Depth axis: In 3-D charts, this is the axis that provides the third dimension. This axis is also called the z-axis. Figure 10-2 shows a 3-D version of the rainfall chart.


Figure 10-2. A 3-D chart has a depth axis, or z-axis, as well as the horizontal axis and vertical axis

Each axis has tick marks that show where the values appear on it.

Categories and Data Series

The categories are the subdivisions of data that appear on the horizontal axis. For example, in the sample chart, each weather station is a category.

The data series are the sets of data used to create the chart. For example, in the sample chart, the data series contain the rainfall measurements.

Chart Title and Axis Titles

The chart title is text that identifies the chart as a whole. The most straightforward position for the chart title is at the top of the chart, where the reader will see it immediately, but sometimes you may want to place it elsewhere. For example, you may place the chart title in open space in the chart area so that you can make the chart itself larger without the title making it taller.

Note In charts you create in Excel for iPad, the chart layout that you apply controls whether the chart title and axis titles appear and (if so) where they appear. In the desktop versions of Excel, you can toggle the chart title and axis titles off manually, reposition them as needed, and reformat them.

The horizontal axis title is text that explains what is shown on the horizontal axis, such as the weather stations in the sample chart.

The vertical axis title is text that explains what the vertical axis shows, such as the amount of rainfall in the sample chart.

A 3-D chart can also have a depth axis title that explains what the depth axis shows.

Note Chart titles and axis titles are optional, so you can include only those your chart needs. Generally, people viewing your charts will benefit from having clear chart titles and axis titles. But if a chart’s content is obvious without them, you may prefer to let it speak by itself.

Data Markers, Gridlines, and Data Labels

The data markers are the points on the chart that show where each data point appears. Each series typically contains several data markers. Depending on the chart type and layout, the data markers may appear as separate points, or they may be linked together.

To make it easy to see how the data markers relate to the axes, some chart layouts display gridlines—lines that run across or up from the data markers.

Data labels are text items that display the exact value of data points. Data labels appear in some chart layouts and help viewers to see the exact figure for each data point rather than judging the value from the chart.

Note In Excel for iPad, you control whether data markers, gridlines, and data labels appear by choosing a layout that includes the items you want to show. In the desktop versions of Excel, you can toggle these items on and off individually.

Choosing the Best Chart Type for Your Data

Excel enables you to create an impressive variety of different types of charts. Some of the charts are useful for many purposes, whereas others are highly specialized. Table 10-1 describes the types of charts that Excel provides and suggests typical uses for them.

Table 10-1. Excel’s Chart Types and Suggested Uses

Chart Category


Suggested Uses


Displays data in vertical bars.

Comparing equivalent items (such as sales results) or sets of data that change over time (such as rainfall).


Displays each series in a line.

Showing evenly spaced values that change over time, such as temperatures.


Displays a single data series as a pie divided up by the contribution of each data point.

Showing how much each item contributes to the whole; for example, breaking down expenses by department.


Displays data in horizontal bars.

Comparing similar items or indicating progress.


Displays data as lines but with the areas between the lines shaded.

Showing how values have changed over time, especially the contribution of different data points in the series.


Displays each data point as a point (or cross, or similar marker) on the plot area. Also known as an XY chart.

Showing values sampled at different times or that are not directly related to each other.


Displays each data series as a vertical line or bar indicating three or more prices or measurements (such as high, low, and closing prices).

Showing the daily prices of stocks. Also suitable for some scientific data.


Displays the data points as a three-dimensional surface.

Comparing two sets of data to find a suitable combination of them.


Displays the data series as a sequence of concentric rings.

Showing how much each item contributes to the whole—like a pie chart, but it works with two or more data series.


Displays the data points as bubbles of different sizes depending on their values.

Showing the relative importance of each data point.


Displays the combined values of different data series.

Showing how the combined values of separate data series compare to each other (for example, the sales contributions of several different products over several periods of time).

Note The table lists the charts in the same order as Excel for iPad’s Charts pop-up panel except for the following chart types: the Donut chart type appears in the Pie category, the Bubble chart type appears in the Scatter category, and the Stock, Surface, and Radar chart types appear in the Others category.

Creating, Laying Out, and Formatting a Chart

In this section, you’ll look at how to create a chart from your data, lay it out with the components and arrangement you want, and format it using a style and a set of colors.

Creating a Chart

Here’s how to create a chart.

1. Select the cells that contain the data for the chart. Normally, it’s easiest to tap the first cell, and then drag the selection handle to select the rest of the range.

2. Tap the Insert tab of the Ribbon to display its contents.

3. Tap the Recommended button to display the Recommended panel, which shows the chart types that Excel recommends based on a quick analysis of the selected data. Figure 10-3 shows an example. If one of the recommended charts is suitable, tap it and skip the rest of the steps in this list.


Figure 10-3. Look at the charts on the Recommended pop-up panel to see if any suit your needs

Note The Recommended chart types tend to be a bit hit and miss, because Excel doesn’t actually understand what your data represents. But it’s well worth looking at the recommendations, because doing so takes only a second and the previews give you a good idea of what the chart will look like.

4. Tap the Charts button to display the Charts pop-up panel (see Figure 10-4), which gives you access to the full range of charts that Excel for iPad offers.


Figure 10-4. On the Charts pop-up panel, tap the chart type to use for the data you’ve selected

5. Tap the chart type: Column, Line, Pie, Bar, Area, X Y (Scatter), or Others (which gives you access to the Stock, Surface, and Radar chart types). The appropriate pop-up panel appears, showing the available subtypes for that chart type. For example, if you tap Column, the Column pop-up panel appears, showing the 2-D Column subtypes and the 3-D Column subtypes (see Figure 10-5).


Figure 10-5. On the pop-up panel for the chart type you selected, tap the chart subtype to use

6. Tap the chart subtype you want to use. Excel creates the chart as an embedded object in the worksheet (see Figure 10-6).


Figure 10-6. Excel creates the chart as an embedded object on the same worksheet as its source data

You can then reposition the chart by tapping it and dragging it, and you can resize it by tapping it and dragging a resizing handle. As usual, drag a corner handle to resize the chart proportionally; drag a side handle to resize the chart only in that dimension.

Changing the Chart Type

If you find the chart type you’ve chosen doesn’t work for your data, you can change the chart type easily. Tap the chart to select it, and then tap the Chart tab on the Ribbon to display its contents. Tap the Types button to display the Types pop-up panel, tap the chart type, and then tap the subtype.

Excel changes the chart in place. If the result is worse than before, either tap the Undo button or tap the Types button again and try a different chart type, subtype, or both.

Switching the Rows and Columns in a Chart

When Excel displays the chart, you may realize that the data series are in the wrong place; for example, the chart is displaying months by rainfall instead of rainfall by months.

When this happens, you don’t need to transpose the data in the data source. Simply tap the chart in the workbook, tap the Chart tab on the Ribbon, and then tap the Switch button. Excel switches the data series in the chart, which changes accordingly.

Changing the Source Data for a Chart

Sometimes you may find that your chart doesn’t work well with the source data you’ve chosen. For example, you may have selected so much data that the chart is crowded, or you may have missed a vital row or column.

When this happens, you don’t need to delete the chart and start again from scratch. Instead, tap the chart to select it, and then drag the handles on the data source to select the data you need. Excel color-codes the data source to help you see which data you have selected. Excel changes the chart instantly, so you can see if you’ve got the data you need.


In the desktop versions of Excel, you can change the order in which the data series appear in the chart from the order in which they appear in the data source. For example, if your data source includes columns B, C, and D, you can make the chart display the columns in a different order, such as C, B, D or D, B, C. You can’t do this in a chart on the iPad—instead, you need to change the order of the series in the data source and then create the chart.

Another neat move you can make in the desktop versions of Excel is to add to a chart a data series from a separate set of data. For example, say your chart shows your actual sales figures, but you have a separate table or a separate worksheet that shows projected sales figures. You can use the Source Data command to add a data series from the projections to the actual sales figures in the chart without rearranging the data in your worksheet.

You can’t do this either on the iPad. Instead, you need to rearrange the data so that it’s in a contiguous range, and then create the chart.

Choosing the Layout and Adding Any Titles Needed

When you’ve got the right chart type and the source data, choose the layout for the chart. For each chart type, Excel provides various preset layouts that control whether and where the title, legend, and other elements appear. After applying a layout, you can customize it further as needed.

To choose the layout, tap the chart, tap the Chart tab, and then tap the Layout button. On the Layouts pop-up panel (see Figure 10-7), tap the layout you want to use for the chart.


Figure 10-7. You can quickly change a chart’s layout by using the Layouts pop-up pane on the Charts tab of the Ribbon

After choosing the layout, you can fill in any information the chart needs. For example, if the layout includes a chart title, tap the chart, double-tap the Chart Title placeholder, and then type the text for the title. Similarly, if the layout has axis titles, add them now.

Applying a Style and Changing the Colors

To set the overall graphical look of a chart, apply one of Excel’s styles to it. To apply a style, tap the chart, tap the Chart tab on the Ribbon, tap the Styles button, and then tap the style on the Styles panel (see Figure 10-8).


Figure 10-8. Apply a style from the Styles pop-up panel to set the overall look of a chart

After applying the style you want, you can change the colors as needed by tapping the chart, tapping the Colors button on the Chart tab of the Ribbon, and then tapping the set of colors in the Colors pane.

Note The Colors pane contains a Colorful category and a Monochromatic category. The Colorful category tends to produce charts that are easier to read, but sometimes you may need the more subdued looks that the Monochromatic category offers.

Copying and Pasting a Chart

After creating a chart in Excel, you can copy it and then paste it into a Word document or onto a PowerPoint slide by following these steps.

1. In Excel, tap the chart, and then tap Copy on the Edit menu that appears.

2. Switch to Word or PowerPoint, either by pressing the Home button and then tapping the app’s icon on the Home screen or (if Word or PowerPoint is already running) by double-pressing the Home button and then tapping the app’s preview or icon on the app-switching screen.

3. Tap at the appropriate point in the document or on the destination slide. The Edit menu appears.

4. Tap Paste. The chart appears in the document or presentation.

Now that you’ve pasted the chart, you can move it or resize it as usual. You can also change the chart if necessary without going back to Excel.

· Change the chart type: Tap the Column button, the Line button, the Pie button, the Bar button, the Area button, the X Y (Scatter) button, or the Others button (see Figure 10-9). The pop-up panel of the same name as the button appears, and you can choose the chart subtype. The Others pop-up panel contains four types of Surface charts and three types of Radar charts.


Figure 10-9. After pasting a chart from Excel, use the controls on the Chart tab of the Ribbon in PowerPoint or Word to format the chart or to change it to a different type

· Change the chart style: Tap the Styles button to display the Styles pop-up panel, and then tap the style you want. The styles are different visual designs for the chart type and subtype you’ve chosen.

· Change the colors: Tap the Colors button to display the Colors pop-up panel, and then tap the set of colors you want.

· Change the Layout: Tap the Layouts button to display the Layouts pane, and then tap the layout you want to apply.


In this chapter, you learned how to choose the right type of chart for your needs and how to create a chart from your data. You now know how to change the chart type and subtype; change the source data; and apply a suitable layout, style, and colors. And you can now take a chart you’ve created and paste it onto a PowerPoint slide or into a Word document so that you can use it instantly.