[ Pobierz całość w formacie PDF ]
.To do so, follow these steps:1.Select the chart.2.Choose Chart ª' Chart Type to display the Chart Type dialog box.3.Select the Custom Types tab.4.Choose the User-Defined option.5.Click the Add button and you ll see the Add Custom Chart Type dialog box.6.Enter a name and (optionally) a description for the custom chart type (seeFigure 17-18).7.Click OK and you ll see your new custom chart type listed in the Chart Typelist box.8.Click OK again to close the Chart Type dialog box.Figure 17-18: The Add CustomChart Type dialog box.Test and refineThe final step is to test your creation.You should test the custom chart type with adifferent data series.Voilà! The data will be displayed in a highly customized chart that is perfect inevery way.Or at least it should be.If the chart doesn t look right, you may need tomake some additional changes to it.After doing so, you need to go through the pre-vious steps to add the chart to the custom gallery.If you use the same name, Excelasks whether you want to replace the existing user-defined format.Answer in theaffirmative. 21 539671 ch17.qxd 8/28/03 10:04 AM Page 398Part III &' Creating Charts and Graphics398Learning Some Chart-Making TricksThis section teaches you some interesting chart-making tricks.Some of these tricksuse little-known features, and several tricks enable you to make charts that youmay have considered impossible to create.On theAll of the examples in the following sections are available on the companionCD-ROMCD-ROM.Creating picture chartsExcel makes it easy to incorporate a pattern, texture, or graphic file for elements inyour chart.Figure 17-19 shows an example of a chart that displays a graphic.Figure 17-19: You can include graphicimages in the columns of a chart.To convert a data series to pictures, start with a column or bar chart (either stan-dard or 3-D).Then, select the series and choose Format ª' Selected Data Series toaccess the chart s Format Data Series dialog box.Select the Patterns tab and clickthe Fill Effects button to get the Fill Effects dialog box.In the Fill Effects dialog box,click the Picture tab and then click the Select Picture button to locate the graphicsfile that you want to use.You ll probably find that you need to play around with thesettings to make your graphics work out the way you d like.TipYou can also copy any graphic image to the Clipboard.Then, select the chartcolumn or bar series and choose Edit ª' Paste. 21 539671 ch17.qxd 8/28/03 10:04 AM Page 399Chapter 17 &' Learning Advanced Charting399Creating a thermometer chartYou re probably familiar with a thermometer type display that shows the percent-age of a task that s completed.It s very easy to create such a display in Excel.Thetrick involves creating a chart that uses a single cell (which holds a percentagevalue) as a data series.Figure 17-20 shows a worksheet set up to track daily progress toward a goal: 1,000new customers in a 15-day period.Cell B18 contains the goal value, and cell B19contains a simple sum formula:=SUM(B2:B16)Cell B21 contains a formula that calculates the percent of goal:=B19/B18Figure 17-20: This chart displays progresstoward a goal.As you enter new data in column B, the formulas display the current results.To create the chart, select cell B21, click the Chart Wizard button, and create a columnchart.Notice the blank row before cell B21.Without this blank row, Excel uses theentire data block for the chart, not just the single cell.Because B21 is isolated fromthe other data, the Chart Wizard uses only the single cell.Other changes required: 21 539671 ch17.qxd 8/28/03 10:04 AM Page 400Part III &' Creating Charts and Graphics400&' Select the category (x) axis and press Delete.This removes the category axisfrom the chart.&' Remove the legend.&' Add data labels (choose the Value option).&' Set the Gap width to 0 (This makes the column occupy the entire width of theplot area.) To do so, double-click the column to display the Format Data Seriesdialog box.Then select the Options tab, and change the Gap Width setting&' Double-click the value axis to access the Format Axis dialog box.In theFormat Axis dialog box, select Scale tab; set the Minimum to 0 and theMaximum to 1.You can then make any other adjustments to get the look you desire.Creating a gauge chartFigure 17-21 shows a pie chart set up to resemble a gauge.Although this chart dis-plays a single value (entered in cell B1), it actually uses three data points (in A4:A6).Figure 17-21: This chart resemblesa speedometer gauge and displays avalue between 0 and 100%.One slice of the pie the slice at the bottom always consists of 50%, and thatslice is hidden.(The slice s Area and Border were set to None.) The other two slicesare apportioned based on the value in cell B1.The formula in cell B4 is=MIN(B1,100%)/2This formula uses the MIN function to display the smaller of two values: either thevalue in cell B1 or 100%.It then divides this value by 2 because I m only dealingwith the visible half of the pie chart.Using the MIN function prevents the chart fromdisplaying more than 100%. 21 539671 ch17.qxd 8/28/03 10:04 AM Page 401Chapter 17 &' Learning Advanced Charting401The formula in cell A6, shown below, simply calculate the remaining part of thepie the part to the right of the gauge s needle
[ Pobierz całość w formacie PDF ]