Excel Module 4

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