Excel Module 4
Insert a Stacked Column chart.
Click and drag to select range A3:D6.Click the Insert tab on the Ribbon.In the Charts group, click the Insert Column or Bar Chart button.A gallery of 2-D and 3-D Column and Bar charts appears.In the gallery, in the 2-D Column section, click the Stacked Column chart type (the second option in the gallery).Excel inserts a Stacked Column chart in the worksheet based on the range A3:D6.
Insert a Line chart.
Click and drag to select range A3:E6.You will chart wages and salaries for the different employee categories over time.Click the Insert tab on the Ribbon.In the Charts group, click the Insert Line or Area Chart button.The Line chart gallery appears.In the 2-D Line section of the gallery, click the Line chart option, the first option in the section.Excel inserts a Line chart in the worksheet that graphs the selected data.
Insert a chart using the Quick Analysis Tool.
Click and drag to select range A4:B15. At the bottom-right of the selected range, click the Quick Analysis button. The Quick Analysis gallery opens with options for formatting, charts, totals, tables, and sparklines. Click Charts. The Charts gallery appears with chart options appropriate for the data you have selected. In the gallery, click the Clustered Column option.. Excel creates a Clustered Column chart based on the data in the selected cells.
Insert a Combination chart.
Click and drag to select range B3:D15.You want to graph both the sales and tenure data for each franchise. A combination chart makes sense here.Click the Insert tab on the Ribbon.In the Charts group, click the Recommended Charts button.The Insert Chart dialog box opens. You can choose chart options from the Recommended Charts tab or click the All Charts tab to see additional options.Click the All Charts tab.A list of all chart types appears in the left of the dialog box, with previews of chart subtypes at right.In the list of chart types, click Combo.Excel displays Combo chart options in the right pane. The Cluster Column - Line combo chart is currently selected. You will accept this chart type, but make a modification.In the Choose the chart type and axis for your data series box, for the Tenure data series, click the Secondary Axis check box to select it.The Tenure values are much lower than the Sales values. If plotted on the same axis, the Tenure values will not be readable. Using a secondary axis scaled to fit values in the Tenure data series will make the chart more readable.Click OK.Excel inserts the combo chart in the worksheet.
Create a Data Bars conditional formatting rule.
Click and drag to select range B4:B15.In the Styles group on the Home tab, click the Conditional Formatting button.The Conditional Formatting menu opens.On the Conditional Formatting menu, point to Data Bars.The Data Bars gallery appears.In the Data Bars gallery, in the Gradient Fill section, click the Green Data Bar option.Excel inserts data bars into cells in the range. The bars vary in length according to cell value; the larger the cell value, the longer the data bar.
Edit a Data Bar conditional formatting rule.
Click and drag to select range B5:B16.You want to change the maximum value for the data bars in this range.In the Styles group, click the Conditional Formatting button.The Conditional Formatting menu opens.On the menu, point to Data Bars.The Data Bars gallery appears. You can change the data bar formats using different gradients and colors, or you can customize data bar display by clicking More Rules.Click More Rules.The New Formatting Rule dialog box opens. The Maximum value is currently Automatic, meaning that Excel determines the maximum value of data bars for you.In the dialog box, in the Edit the Rule Description section, click the Maximum arrow.You will set a new maximum value.On the menu, click Number.You will specify a number as the maximum value.Select the current value in the Value text box under Maximum, and then type 75000 to replace it.Click OK.Excel adjusts the data bars to completely fill every cell with a value of 75,000 or above.
Insert a Histogram chart.
Click and drag to select range C4:C19.You want to chart the distribution of tenure values for store owners. You will use a histogram chart to do this.Click the Insert tab on the Ribbon.In the Charts group, click the Insert Statistic Chart button.The Statistic chart gallery opens.In the Histogram section, click the Histogram chart option, the first option in the section.Excel inserts a histogram chart in the worksheet. The number of bins, and the range of values in each, are determined automatically by Excel. You can modify these later if desired.
Create a formula using the PMT function.
Click cell B9.Type -PMT( in the current cell.Excel displays a ScreenTip with the arguments for the function. Note that you are using a - (negative) sign before the PMT function so that the formula will return a positive value.Click cell B6.This is the rate argument, specifying the monthly interest rate. Excel displays the cell address B6 in the formula.Type , (a comma) after the rate argument.Next you will enter the nper argument, specifying the number of monthly payments.Click cell B8.Type , (a comma) after the nper argument.Next you will enter the pv argument, specifying the amount of the loan.Click cell B4.Click the Enter button on the formula barExcel calculates the monthly payments on a $1,000,000 loan with a monthly interest rate of 0.4% and 24 payments over 2 years. Note that you did not specify values for the optional fv or type arguments.
Apply a sparkline style.
Click cell E5.When you select the cell containing the sparkline, Excel activates the Sparkline Tools Design tab on the Ribbon. You will modify the style of the sparkline in this cell.Click the Sparkline Tools Design tab on the Ribbon.In the Style group, click the More button.Excel displays a gallery of sparkline styles.Click Sparkline Style Accent 1, Darker 50% (the first option in the first row).Excel modifies the sparkline style. The new style is now listed first in the gallery, and the gallery closes.
Insert a sparkline
Click cell E5.You will place the sparkline in this location.Click the Insert tab on the Ribbon.In the Sparklines group, click the Line button.The Create Sparklines dialog box opens. The Location Range text box contains the value $E$5, because cell E5 was selected when you clicked the Line button. You need to select a data range for the sparkline.In the worksheet, click and drag to select cells B5:D5. Release the mouse button.A flashing border indicates that the range B5:D5 is selected, and the range appears in the Data Range text box.In the Create Sparklines dialog box, click OK.Excel inserts a Line type sparkline into cell E5, and the Sparkline Tools Design tab appears on the Ribbon. The sparkline indicates an upward trend in the data from 2020 to 2022
Apply a border to a chart element.
Click the Chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery opens.Point to Legend and click the Legend arrow.The Legend fly-out menu opens.On the Legend fly-out menu, click More Options.The Format Legend task pane opens.In the Format Legend task pane, click the Fill & Line button.The Fill and Border section headings appear.Click the Border arrow to expand the Border section.The Border options appear in the task pane. You can choose solid or gradient lines, line color, transparency, width, and more.Click the Solid line button to select it.Excel applies a solid black border around the legend.Click Close to close the task pane.Click on cell B3.The chart border is now visible.
Remove a data series from a chart.
Click the Chart.The Chart Tools tabs appear on the Ribbon. Excel highlights the chart data source in the worksheet.Click the Chart Tools Design tab on the Ribbon.In the Data group, click the Select Data button.The Select Data Source dialog box opens. The current chart data source is selected in the Chart data range text box. The chart data series are listed in the Legend Entries (Series) box.In the Legend Entries (Series) box, click the Licensing Fees entry.The Licensing Fees data series is selected.Click Remove.Excel removes the data series from the Legend Entries (Series) box and modifies the range in the Chart data range text box.Click OK.Excel removes the data series from the chart. Note that Excel also modifies the data source in the worksheet to
Apply a shadow effect to a chart element
Click the chart legend.The chart and the chart legend are selected.Right-click the chart legend.The shortcut menu and Mini toolbar open.On the shortcut menu, click Format Legend.The Format Legend task pane opens with available Legend Options.In the task pane, click the Effects button.The Effects options appear in the task pane.Click the Shadow arrow.The Shadow section expands to display shadow options.To the right of the Presets heading, click the Shadow Presets arrow.A menu of preset shadow options opens.On the menu, in the Outer section, click the Offset Bottom option (top row, second option)Excel adds an Offset Bottom shadow to the chart legend.In the task pane, click the Close button.Excel closes the pane and displays the new formatting for the chart.
Apply a shape fill to a chart element.
Click the chart plot area.Excel selects both the chart and the chart plot area.Right-click the chart plot area.The shortcut menu and Mini toolbar open.On the shortcut menu, click Format Plot Area.The Format Plot Area task pane opens. The Fill and Line button is active; the Fill and Border sections are collapsed.Click the Fill arrow to expand the Fill section.In the Fill section, click the Solid fill option to select it.Excel fills the plot area with the default Blue, Accent 1 fill color. You want to change the fill color to light gray so that the columns are more visible.In the Fill section, click the Color button.The Fill Color palette appears.In the Theme Colors section, click the Gray-25%, Background 2 option (top row, third option).Excel changes the fill color of the chart plot area as specified.
Change the font size of a chart element.
Click the chart title to select it.You can use the buttons in the Font group to format the chart title.In the Font group, click the Font Size arrow.A list of font sizes appears.Click 14.Excel changes the font size of the chart title to 14 points.
Move a chart element.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.Excel displays a list of the chart elements you can modify.Point to Legend until an arrow appears to the right of the option.Click the Legend arrow.The arrow indicates that a fly-out menu is available.The Legend fly-out menu opens with options for legend positions. The Bottom position is currently selected.Click Right.Excel moves the legend from the bottom to the right side of the chart area.
Add a chart element.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery appears showing a list of available chart elements. Those elements currently displayed in the chart are checked.Click the Chart Title check box.Excel inserts a chart title above the chart with the placeholder text Chart Title. You can modify the placeholder text if desired. Not all chart elements will contain placeholder text.
Edit the number format of data labels in a chart.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery appears showing a list of available chart elements. Those elements currently displayed in the chart are checked.Point to Data Labels and then click the arrow that appears.A fly-out menu appears with options for data label locations.Click More Options.The Format Data Labels task pane opens with the Label Options tab and button active.In the task pane, click the Label Options arrow.The Label Options section collapses.Click the Number arrow to expand the Number section.Click the Category arrow.Excel displays a list of available number formats.Click Currency.Excel applies the Currency format to the data labels.In the Decimal text box, replace the existing text by typing 0 (zero) in the text box.Press ENTER.Excel removes the decimals places.In the task pane, click the Close button.The task pane closes.
Edit the label options of a data label in a chart.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery appears showing a list of available chart elements. Those elements currently displayed in the chart are checked.Point to Data Labels, and click the arrow that appears.A fly-out menu appears with options for data label locations.Click More Options.The Format Data Labels task pane opens. The Label Options tab and button are active, and the Label Options section is expanded.In the task pane, in the Label Contains section, click the Percentage check box to select it.Excel adds percentage values to the data labels. You want to show the percentages only, not their numerical values.In the task pane, in the Label Contains section, click the Value check box to deselect it.Excel removes the numerical values from the data labels.In the Label Position section, click the Inside End option button to select it.Excel moves the data labels to the Inside End position.In the task pane, click the Close button.The task pane closes.
Add chart element at a specific location.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery appears showing a list of available chart elements. Those elements currently displayed in the chart are checked.Point to the Data Labels check box and then click the arrow.A fly-out menu appears with options for data label locations.Click Outside End.Excel inserts data labels to the right of each bar.
Add axis titles to a chart
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery lists available chart elements. Those elements currently displayed in the chart are checked.Click the Axis Titles check box.A check mark appears in the box. Excel adds axis title text boxes containing placeholder text to the primary vertical and horizontal axes. The Vertical Axis Title text box is selected.Type Dollars in the Vertical Axis Title text box.The text appears in the formula bar. The text will appear in the Vertical Axis Title text box once the text box is deselected.Click the Horizontal Axis Title text box.The title Dollars now appears in the Vertical Axis Title text box. You will now add a title to the horizontal axis.Type Food Category in the text box.Click cell C1.Excel completes the axis title entry and deselects the chart.
Delete a chart element.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Elements button.The Chart Elements gallery opens. The check boxes for Chart Title, Data Labels, and Legend are all selected.In the Chart Elements gallery, click the Legend check box.When you deselect the Legend check box, Excel deletes the legend from the chart.
Move a chart to different worksheet.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon, as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Tools Design tab on the Ribbon.In the Location group, click the Move Chart button.The Move Chart dialog box opens. The Object in option button is selected. You will move the chart as a chart object to a different worksheet.Click the Object in arrow.Excel displays a list of worksheets in the active workbook. You will specify where you want to move the chart.Click Category Sales.Excel will move the chart as an object into the Category Sales worksheet.Click OK.Excel moves the chart and displays the Category Sales worksheet.
Apply a chart style.
Click the chart.Excel activates the Chart Tools tabs on the Ribbon, as well as the Chart Elements, Chart Styles, and Chart Filters buttons to the right of the chart.Click the Chart Tools Design tab.The Chart Tools Design tab includes options for changing chart layouts, styles, data, type, and location.In the Chart Styles group, click the More button.The full Chart Styles gallery appears.In the gallery, click the Style 5 icon.Excel applies Style 5 to the chart.
Add gridlines to a chart.
Click the chart.The Chart Tools tabs appear on the Ribbon and the Chart Elements, Chart Styles, and Chart Filters buttons appear to the right of the chart.Click the Chart Elements button.The Chart Elements gallery appears showing a list of available chart elements. Those elements currently displayed in the chart are checked.Click the Gridlines check box to select it.By default, Excel adds Primary Major Horizontal gridlines to the chart. Additional gridline options are available on the Gridlines fly-out menu.
Change the Horizontal Axis Label values used in a chart.
Click the chart.The chart is selected. Range A9:D11 is highlighted in the worksheet as the chart data source. By default, Excel has added the generic labels 1, 2, and 3 to the Horizontal axis.Click the Chart Tools Design tab on the Ribbon.In the Data group, click the Select Data button.The Select Data Source dialog box opens. The current Horizontal axis label values are selected in the Horizontal (Category) Axis Labels box. You want to edit these labels to display the year values to which the data corresponds.In the Horizontal (Category) Axis Labels box, click the Edit button.The Axis Labels dialog box opens. You will use the mouse to enter a new range in the Axis label range text box.In the worksheet, click and drag to select range B3:D3.Excel inserts the range in the text box.Click OK.The Axis Labels dialog box closes. Excel updates the Horizontal Axis labels in the Horizontal (Category) Axis Labels box, and in the chart itself, to correspond to the values in the range you just specified.Click OK.The Select Data Source dialog box closes. The expanded chart data source is highlighted in the works
Resize a chart.
Click the chart.The chart is selected.Position the mouse pointer over the chart's lower-right corner until the pointer changes to a sizing handle, denoted by a double-headed diagonal arrow. Press and hold the left mouse button as you drag the chart corner down and to the right until the pointer is within cell D20, then release the mouse button.Excel resizes the chart, which now fills a larger area in the worksheet.
Change the bounds of a chart axis.
Click the vertical axis on the chart. Excel selects both the chart and the chart axis. Right-click the vertical axis. The shortcut menu and Mini toolbar open. On the shortcut menu, click Format Axis. The Format Axis task pane opens with Axis Options displayed. In the Bounds section of the task pane, click and drag to select the current value in the Minimum text box, type 25000 and press Enter. You will modify the Minimum bounds value to eliminate the extra space at the bottom of the chart. Click and drag to select the current value in the Maximum text box and type 70000 and press Enter. Excel modifies the chart axis as specified, changing the bounds as well as the corresponding chart units. There is less empty space in the chart and the chart data is easier to read.
Change the units of a chart axis.
Click the vertical axis on the chart.Excel selects both the chart and the chart axis.Right-click the vertical axis.The shortcut menu and Mini toolbar open.On the shortcut menu, click Format Axis.The Format Axis task pane opens with Axis Options displayed.In the Units section of the task pane, select the value in the Major text box, type 10000, and press ENTER.Values on the vertical axis currently appear in increments of 5,000. You want to make the major unit value larger so there are fewer major breaks along the axis.Select the value in the Minor text box, type 5000, and press ENTER.Excels modifies the chart axis as specified. There are now fewer gridlines in the chart and fewer labels and tick marks along the axis.
Edit a bin size used in a Histogram chart.
In the Histogram chart, double-click a value on the horizontal axis.The Format Axis task pane opens at the right of the Excel window. The Axis Options tab is active.In the task pane, click the Axis Options button.The Axis Options section names appear.Click the Axis Options arrow to expand the section and display all options.In the Bins section, click the Bin width option button.The width of each bin is currently 4.6 points. You want each bin to represent 3 data points, starting with 1-3 years, 4-6 years, and so on.Replace the existing text in the Bin width text box by typing 3.0 in the text box and press TAB.Excel reconfigures the chart so that each bin now spans three data points. Excel adds an additional bin to the chart to account for the new bin size.In the task pane, click the Close button.The task pane closes.
Insert a chart using the Recommended Chart button.
Position the mouse pointer over cell A3. Press and hold the left mouse button as you drag the pointer down and to the right until it is over cell B8. Release the mouse button.This is the range you will use for your chart.Click the Insert tab on the Ribbon.In the Charts group, click the Recommended Charts button.The Insert Chart dialog box opens with the Recommended Charts tab active. The left pane displays Recommended Chart options.In the left pane, click the Clustered Column option.Excel displays a preview of the Clustered Column chart in the right pane.Click OK.Excel inserts a Clustered Column chart in the worksheet.
Move a chart on a worksheet.
Position the mouse pointer over the chart border so that the pointer changes to a block arrow with a four-headed arrow. Press and hold the left mouse button and drag the chart so that the upper-left corner of the chart border is over cell F1. Release the mouse button.Excel "drops" the chart in its new location. The chart is selected.
Edit placeholder text in a chart element.
e In the chart, click the chart title placeholder.You will enter a more descriptive chart title.Type 2020 Sales in the chart title placeholder.As you type, the text appears in the formula bar, but the placeholder text in the chart title does not yet change.Click cell A11.Excel inserts the new text in the chart title.