contains the actual values that are plotted or displayed on the chart. This data series shows the total number of each type of backpack
data series
are the groups or categories to which the data series values belong
category values
each chart has a __, which is the range that contains the data to display in the chart.
data source
contains the chart and all of the other chart elmeents
chart area
is text associated with an individual data marker, such as the percentage value next to a pie slice
data lavel
displays the values from the data series
vertical axis
displays the category values from each data series
horizontal axis
provides a collection of commands to format chart elmeents
format pane
displays an individual value from a data series.
data marker
identifies the data markers associated with each data series
chart legend
FV (rate,nper,pmt [,pv=0] [,type=0])
Calculates the future value of an investment, where rate is the interest rate per period, nper is the total number of periods, pmt is the payment in each period, pv is the present value of the investment, and type indicates whether payments should be made at the end of the period (0) or the beginning of the period (1)
PMT(rate,nper,pv [,fv=0][,type=0])
Calculates the payments required each period on a loan or an investment, where fv is the future value of the investment
IPMT(rate, per,nper,pv[,fv=0][,type=0])
Calculates the amount of a loan payment devoted to paying the loan interest, where per is the number of the payment period
PPMT(rate, per,nper,pv[,fv=0][,type=0])
Calculates the amount of a loan payment devoted to paying off the principal of a loan
PV(rate,nper,pmt[,fv=0][,type=0])
Calculates the present value of a loan or an investment based on periodic, constant payments
NPER(rate, pmt,pv [,fv=0][,type=0]))
Calculates the number of periods required to pay off a loan or an investment
RATE(per,pmt,pv[,fv=0][,type=0])
Calculates the interest rate of a loan or an investment based on periodic, constant payments
Principal
the amount of the loan
The PMT function syntax is
PMT(rate,nper,pv[,fv=0][,type=0]), where rate is the interest rate for each payment period, nper is the total number of payment periods required to repay the loan, and pv is the present value of the loan or the amount that needs to be borrowed.
To calculate the monthly payment
select a cell to make it the active cell. You will enter the PMT function in this cell. On the ribbon, click the Formulas tab. In the Function Library group, click eh Financial button, and then scroll down and click PMT in the list of financial functions. The Function Arguments dialog box opens.
Creating a Chart
Select the range containing the data you want to chart.
On the insert tab, in the charts group, click the Recommended Charts button or a button representing the general cart type, and then click the chart you want to create.
On the Chart Tools Design tab, in the Location group, click the Move Chart button, select whether to embed the chart in a worksheet or place it in a chart sheet, and then click the OK button
Description: Chart Category: Column or Bar
Compares values from different categories. Values are indicated by the height of the columns or the length of a bar.
Description: Chart Category: Hierarchy
Displays data that is organized into a hierarchy of categories where the size of the groups is based on a number
Description: Chart Category: Line
Compares values from different categories. Values are indicated but he height of the lines. Often used to show trends and changes over time.
Description: Chart Category: Statistic
Displays a chart summarizing the distribution of values from a sample population
Description: Chart Category: Pie
Compares relative values of different categories to the whole. Values are indicated by the areas of the pie slices.
To select eh surgery results as the pie chart's data source
Go to the worksheet. Select the range containing the overall results of the survey for both en and women.
With the range selected, click the Quick Analysis button in the lower-right corner of the selected range to open the Quick analysis tool
click the Charts category. Click Pie.
To remove the chart title and add data labels
With the chart still selected, click the Chart Elements button. Click the Chart Title check box to deselect it. Point to the Data Labels check box..
Click the Data Labels check box
To choose a different chart style for the pie chart
Click eh Chart Styles button next to the selected pie chart.
To format the pie chart legend
With the chart selected, click the Chart Elements button.
Point to Legend in the Chart Elements menu, and then click the right arrow icon next to the Legend entry, displaying a submenu of formatting options for that chart element
Point to Left to see a Live Preview o the pie chart with the legend aligned along the left side of the chart area
Click Right to place the legend along the right side of the chart area.
When a data label is placed far from its pie slice, a __ is added to connect the data label to its pie slice
leader line
To display percentage labels in the pie chart
At the top of the Format pane, click the Legend Options arrow to display a menu of chart elements, and then click Series "Total" Data Labels to display the formatting options for data labels.
Near the top of the Format Data Labels pane, click the Label Options button and then click Label Options. Click the Percentage check box to add the percentage.
To change the color of a pie slice
Click any pie slice to select al loft eh slices in the pie chart.
Click the Ext. Frame slice, which is the darker blue slice
On the ribbon, click the Home tab
In the Font group, click the Fill Color button arrow
To change the chart area color
Click a blank area within the chart
ON the Home tab, in the Font group, click the Fill Color button arrow
To filter the pie chart to show only a certain # of packs
Click the pie chart to select it
Click the Chart Filters button next to the chart
Click the boxes to unselect them
displays data values as columns with the height of each column based on the data vlaue
column chart
a column chart turned on its side is called a
bar chart
displays the data series in separate columns side by side so that you can compare the relative height of the columns in the three series
clustered column chart
places the data series values within combined columns showing how much is contributed by each series.
stacked column chart
makes the same comparison as the stacked column chart except that the stacked sections are expressed as percentages
100% stacked column chart.
To create a clustered column chart
select the range containing the categories
On the ribbon, click the Insert tab.
In the Charts group, click the Recommended Charts button
to move a chart to a different worksheet
make sure the chart is selected
On the Chart Tools Design tab, in the Location group, click the Move Chart button
Click the Object in arrow to display a list of the worksheets in the active workbook, then click the worksheet you want to move the chart to.
to change the title of a chart
At the top of the chart, click Chart Title to select the placeholder text.
Type the new title, then press enter
combines two or more Excel chart types into a single graph.
combination chart
extend the values of the major or minor tick marks across the plot area
gridlines
is descriptive text that appears next to an axis
axis title
is a column chart displaying the distribution of values from a single data series
histogram
Data values from a histogram are grouped into ascending categories called
bins
provides commands to format sparklines
The Sparkling Tools Design contextual tab
are data labels that appear as callout bubbles pointing to data markers
Data callouts
a conditional format that adds a horizontal bar to the background of a cell proportional in length to the cell's value
data bar
a chart that is displayed within a cell.
sparkline
a combination chart used to display which factor is the largest contributor to an outcome value
Pareto chart
identify the main units on the chart axis
Major tick marks
minor tick marks
identify the smaller intervals between the major tick marks
to change the scale of the vertical axis
Click the chart to select it.
Double-click the vertical axis. The Format Axis pane opens with the Axis Options list expanded.
In the Units section, click in the Major box, delete the current value, and type a new value for interval between major tick marks, then press enter.
to add vertical gridlines to a chart
With the chat selected, click the Chart Elements button to display the menu of chart elements.
Point to Gridlines, and then click the right arrow that appears to open a submenu of gridline options
Click the Primary Major Vertical check box to add vertical gridlines
to format the chart columns
Make sure the chart is selected
Click the Axis Options arrow at the top of the Format pane, and then click Series "Total Revenue" from the list of chart elements. The Format pane title changes to "Format Data Series," ad all of the columns are selected.
In the Format pane, click the Series Options button
Drag the Gap Width slider
To display and format the line chart data markers
double click the line within the chart.
Click the Fill & Line button
click Marker, and then click Marker Options to expand the list of options for the line chat data markers
Click the Automatic button
Click Fill to expand the list of fill options, fi necessary
Click the Solid fill option button
To add a data label to the line chart
Click the last point on the line to select only that point
Click the Chart Elements button next to the line chart, and then click the Data Labels check box to select it.
Click the data Labels arrow to display a menu of data label positions and options, and then click Data Callout
On the Data Labels menu, click More Options
click the Label Options button, then click Label Options, if necessary, to expand the list of those options
To change the fill colors of the chart and plot areas
Click the Projected Revenue and Expenses chart to select it
On the ribbon, click the Chart Tools Format tab
IN the Current Selection group, click the Chart Elements arrow to display a list of chart elements int he current chart, and then click Chart Area. In the Shape Styles group, click the Shape fill button arrow, and then click the Green, Accent 6, Lighter 60% theme color
In the Current Selection group, click the Chart Elements arrow, and then click Plot Area to select that chart elmenet
to add gridlines to a chart
Select the chart you want to format
-On the ribbon, click the Chart Tools Design tab
- In the Chart Layouts group, click the Add Chart Element button, scroll down the chart elements, point to Gridlines, and then click Primary Major Vertical on the submenu
to create a combination chart
go to the worksheet with the chart, then select the nonadjacent range that contains the values for the chart
On the ribbon, click the Insert tab, and then click the Recommended Charts button in the Charts group.
Click the All Charts tab to view a list of all chart types and subtypes
Click Combo in the list of chart types, and then click the Custom Combination subtype
Click the Secondary Axis Check box to display the values for that series on a secondary axis
To add titles to the primary and second axes
Click the Chart Elements button next to the combination chart, and then click the Axis Titles check box to select it.
Click the left axis title to select it, type Net Income as the descriptive title, and then press the Enter key
With the left axis title selected, change the font color
Select the numbers on the left axis scale, and then change the font color.
To modify the secondary axis scale
double-click the secondary axis scale to select it and open the Format pane
Click the Axis Options button, if necessary, to display the list of axis options
In Axis Options section, click the Minimum box, change the value from 0.0 to -100000, and then press the Enter key
Modifying a Chart's Data Source
Click the chart to select it
ON the Chart Tools Design tab, in the Data group, click the Select Data button
In the Legend Entries (Series) section of the Select Data Source dialog. box, click the Add button to add another data series to the chart, or click the Remove button to remove a data series from the chart.
Click the Edit button in the Horizontal (Category) Axis Labels section to select the category values for the chart
To modify the bins used in the histogram
Double-click the horizontal axis values to select them and open the Format Axis pane
Click the Axis Options button near the top of the Format pane, and then click Axis Options to expand the list. Excel displays a list of options to set the size and number of bins used in the histogram.
Clici the Bin width option button, change the width of the bins from the default value of 9700 to 20000, and then press the Tab key.
To create a Pareto chart
Go to the worksheet and select the range
On the ribbon, click the Insert tab
In the Charts group, click the Insert Statistic Chart button, and then click the Pareto subtype.
used to track the effect of adding and subtracting values within a sum
waterfall chart
are like pie charts in that they show the relative contribution of groups to a whole
Hierarchy charts
You can create the following types of sparkles in Excel;
-A line sparkling for highlighting trends
-A column sparkling for column charts
-A win/loss sparkling for highlighting positive and negative values
Creating and Editing Sparklines
-ON the Insert tab, in the Sparklines group, click the Line, Column, or Win/Loss button
-In the Data Range box, enter the range for the data source of the sparkline
-In the Location Range box, enter the range into which to place the sparkline
-Click the OK buton
-On the Sparkling Tools Design tab, in the Show group, click the appropriate check boxes to specify which markers to display on the sparkling
-In the Group group, click the Axis button, and then click Show Axis to add an axis to the sparkline
To create column sparklins that show projected production
Go to the Financial Summary worksheet, then select the range
On the ribbon, click the Insert tab
In the Sparklines group, click the Column button,
With the insertion point in th eData Range box, click the Projected Production sheet tab, and then select the data range. This range contains the data you want to chart in the sparkles.
To set the scale of the column sparklines
On the Financial Summary worksheet, make sure the range is still selected.
On the Sparkling Tools Design tab, in the Group group, click the Axis button, and then click Custom Value in the Vertical Axis Maximum Value Options section.
Select the value in the box, and then type 6000.
To ungroup and format the column sparklines
Make sure the range is still selected
On the Sparkling Tools Design tab, in the Group group, click the Ungroup button. The spark lines are ungrouped, and selecting any one of the spark lines will no longer select the entire group
Creating Data Bars
-select the range containing the data you want to chart
-On the Home tab, in the Styles group, click the Conditional Formatting button, point to Data Bars, and then click the at a bar style you want to use.
-To modify the data bar rules, click the Conditional Formatting button, and then click Manage Rules
To add data bars to the worksheet.
Select the range
On the Home tab, in the Styles group, click the Conditional Formatting button, and then click Data Bars.
In the Gradient fill section, click the Green Data Bar style