EXCEL tutorial Charts, Formulas and Conditional Formatting

Télécharger EXCEL tutorial Charts, Formulas and Conditional Formatting

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :

EXCEL tutorial Charts, Formulas and Conditional Formatting

Advanced Chart Elements

Objective 4.1

Formatting a Simple Chart

The Excel charting feature is an extremely powerful tool that displays your data in a visual manner. The Chart Tools group of tabs includes a wide variety of options you can use to customize the appearance of your chart.

Because there are so many combinations to choose from, you may want to start with one of the selections under the Chart Layouts group in the Chart Tools/Design tab. Each of these options commonly uses a pre-selected mix of titles, legend, and other chart formatting settings.

Learn the Skill

This exercise is a refresher on how to create a chart, move and resize it, and perform some common customizations.

1 Open the Sales by Type and Year workbook and save as Sales by Type and Year – (your name).

Create a chart using the data in this worksheet.

2 Click any cell in the range A5:J14, then on the Insert tab, in the Charts group, click Insert Column Chart.

Click Clustered Column in the 2-D Column section.

Move the chart so that it is below the data and make it bigger. Also add a chart title.

3 Click and drag the chart to a new location on the worksheet with the upper left corner in cell A17.

4 Click and drag the bottom right corner handle down to cell L40.

Switch the data so that the years are displayed on the horizontal X-axis.

5 Under Chart Tools, on the Design tab, in the Data group, click Switch Row/Column.

6 Select the Chart Title label, and change the label to: Sales by Type and Year.

The chart includes the Total row because it was directly below the rest of the data. It is skewing the chart by creating the very tall columns. Remove this row so that the other data columns are easier to see.

7 On the Design tab, in the Data group, click Select Data.

8 In the Select Data Source dialog box, scroll down to the bottom of the Legend Entries (Series) list. Click the Total entry and click Remove.

9 Click OK to close the dialog box.

The screen should look similar to the following example:

10 Save the workbook and leave it open for the next exercise.

Add a Secondary Vertical Axix

The Y-axis is always displayed on the left side of the chart. You can also add an optional secondary Y-axis on the right side. The primary reason for having two axes is that the chart may have two types of data, each needing its own scale. An example is a chart that shows data containing prices and sales volume. The sales volume may use the primary axis on the left with the scale reaching up to 1,000,000 units. The price data may use the secondary axis on the right with the scale reaching up to $10.00. If you displayed the price data using the primary axis, each data point will be very low compared to sales data and therefore not appear to be meaningful. In many cases, you may want to configure the chart using two different chart types at the same time. This is known as a combo chart, in which some of the data series use one chart type (for example clustered column) while the rest of the data series use a different chart type (such as a line). You can also select more than two chart types but the chart will look too complex. You can then combine the combo chart with dual axes so that one chart type uses the primary axis, and the other chart type matches to the secondary axis.

To change these chart type and secondary axis options, click Change Chart Type on the Design tab, in the Type group:

An alternative method of shifting a data series to the secondary axis is to right-click on one of the data points (or bar), and click Format Data Series from the shortcut menu. To switch to the other axis, select the Secondary Axis option from the Series Options in the Formatting Data Series pane. Note that you must select one of the data series – four resize handles will appear around each of the data bars in the series.

Learn the Skill

The Marketing Department manager for Tolano Adventures has been reviewing her sales data for the past several years, looking for a pattern. In this exercise, you will modify a chart as a combo chart and use a secondary Y-axis.

1 Ensure the Sales by Type and Year – (your name) workbook is open.

Add a new row of data to the chart.

2 Click in a blank area of the chart to select it.

3 On the Design tab, in the Data group, click Select Data.

4 In the Select Data Source dialog box, click Add.

5 In the Edit Series dialog box, click in the Series name text box, then click cell A16.

6 Delete the current contents of the Series values text box, then select cells B16 to J16, then click OK.

7 In the Select Data Source dialog box, click OK.

This new row of data represents an expense, unlike the other data series which represent income. It would therefore make better sense to display it as a different chart type with its own Y-axis.

8 On the Design tab, in the Type group, click Change Chart Type.

9 In the Change Chart Type dialog box, click the Combo chart type on the left.

By changing to the combo chart type, Excel changes the second half of the data series to the line type.

You will need to manually change them all back to clustered column except the last one.

10 Scroll down the Choose the chart type and axis for your data series list box, and change the Chart Type for every series except Advertising and Promotions (the bottom one) from Line to Clustered Column.

11 Click the Secondary Axis check box for the Advertising and Promotions series, and click OK.

Notice that if you had left the new data row as another column bar, its significance would not have been obvious. By changing the data series to a line, you can now see a very distinct pattern – the Advertising and Promotions amount rises and falls in direct proportion to the sales of the various travel types. This significance has been enhanced especially with the use of the secondary Y-axis using its own scale. The Marketing Department manager will be able to use this chart to convince senior management to increase her spending budget, because it clearly shows how an increase in spending on advertising and promotions coincides with sales volume.

12 Save the workbook.

Dynamic Charts

Up to this point, your experience with Excel charts has been with using static data. The chart simply displays all of the data within a defined range of cells. But once the chart has been created, any changes to the data will also cause the chart to be updated automatically. Furthermore, if your data is set up as a table, you can leverage the AutoFilter capability to easily update any charts attached to it. With tables, new data is automatically added to the range. With AutoFilter, data can be temporarily hidden. When you link the chart to tables or AutoFiltered data lists, the chart will also change dynamically.

Learn the Skill

This exercise demonstrates how to update a chart dynamically while making changes to the table containing the data.

1 Ensure the Sales by Type and Year – (your name) workbook is open.

Delete two rows from the range so that the Advertising and Promotions row is next to the rest of the data.

2 Click on the row headers on both rows 14 and 15 to select both, then on the Home tab, in the Cells group, click Delete.

Convert the range into a table.

3 Click on any cell in the range A5:J13.

4 On the Insert tab, in the Tables group, click Table.

5 In the Create Table dialog box, accept the default settings and click OK.

6 In cell A4, enter: Travel Type to replace the default column heading inserted by the table.

Extend the table by adding more columns.

7 Enter the following values into the designated cells:

Cell Value

K4 2014

L4 2015

M4 2016

For these additional columns, we will create a forecast using an estimated rate of growth.

8 In cell O14, enter: 10%.

9 In cell K14, enter: =J14*(1+$O$14).

10 Select the cell range K5:K14, then drag the AutoFill handle in the bottom right corner of cell K14, and drag it across to column M.

11 Scroll down the worksheet so that row 14 and the chart are fully visible on the screen.

Notice that the chart is already updated with the additional columns of data. Now observe the effects on the chart when you change the growth rate.

12 Select cell O14, change the value to: 15%.

13 Change the value in cell O14 to 20%, and then again to 5%.

The chart will also automatically adjust when you apply filter conditions on the table.

14 Click the AutoFilter button for cell A4 (Travel Type). Click the (Select All) check box to turn it off, then click on each of the following three travel types to turn on their check boxes again:


American Vacation Packages

Sun Seeker Vacation Packages

15 Click OK to update the table and the chart.

The screen should look similar to the following example:

Filtered out. Add that data series back in.

16 Click the AutoFilter button for cell A4 and click the Advertising and Promotions check box to turn it back on, then click OK.

17 Click the AutoFilter button for cell A4 and click the Select All check box to turn it back on, then click


18 Save the workbook.

The secondary axis still appears, but the scale is meaningless because the related data series has been

Animated Charts

As described above, charts will automatically update themselves when changes are made to the source data. Prior to Excel 2013, the chart will update instantaneously, even on the slowest computer. Charts are now animated, which means that chart elements will move in visible increments to their new positions.