INFO MODULE 4
Change the font size of a chart element
Change the font size of a chart or axis title to make it easier to read, or fit better in the space allotted. 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.
Apply Sparkline style
Changing the style of a sparkline can add emphasis or contrast it with nearby cells. 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 Orange, Sparkline Style Accent 2, (no dark or light), the second option in the third row of the Sparkline Style gallery. Excel applies the new style to the sparkline.
Insert a 3-D Pie Chart
Using a pie chart helps to illustrate how one data point, or value, relates to the whole, such as how one month of sales relates to an entire year of sales. Click and drag to select range A5:B16. Excel highlights the selected range, which is the range of values to be charted. Click the Insert tab on the ribbon. In the Charts group, click the Insert Pie or Doughnut Chart button. A gallery of pie and doughnut chart types opens. In the 3-D Pie section, click the 3-D Pie option. Excel inserts a 3-D Pie chart into the worksheet based on the selected range.
EX9872 Insert markers into a sparkline.
While a sparkline shows a trend in nearby data, adding markers to the sparkline can emphasize specific data such as high or low points.Click and drag to select range E5:E8.Each cell in the range contains a sparkline; all four sparklines are selected. The Sparkline Tools Design tab appears on the ribbon.Click the Sparkline Tools Design tab.In the Style group, click the Marker Color button.You can add markers of different colors for high, low, first, last, and negative points on the sparkline. You can also add markers of the same color for every point.On the menu, point to High Point.You will use one color to indicate the highest point on each sparkline.In the Color gallery, in the Standard Colors section, click Green (the sixth option in the Standard Colors section).In the Style group, click the Marker Color button again.You will use a different color marker to indicate the lowest point in the sparkline.On the menu, point to Low Point.In the Color gallery, in the Standard Colors section, click Red (the second option in the Standard Colors section).Excel marks the lowest value in each sparkline with a red dot.
Move a chart legend to an approximate location
A chart with carefully placed elements, such as a legend, can represent your worksheet data more clearly. You can easily change the location of the legend by dragging it to a new location. Click the chart. The chart is selected. The Chart Tools Design and Format tabs appear on the ribbon. Click the chart legend to select it. As you make the selections, the mouse pointer changes to the move pointer, denoted by a block arrow and a four-headed arrow. In move mode, you can use the mouse to click and drag a selection to reposition it. Press and hold the left mouse button as you drag the legend to the upper-right corner of the chart area. Then, release the mouse button. The legend appears in its new location.
Edit a Data Bar conditional formatting rule.
Data bars can show what portion of a goal has been met. If an automatic value for the data bars results in filling nearly all the affected cells, you can edit the Data Bars conditional formatting rule to compare the data more clearly.Click and drag to select range D5:D8.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 format using gradients and colors, or you can customize the 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 box in the Maximum section, 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.
Edit the label options of a data label in a chart.
Data labels make charts easier to read by identifying the names and values in a data series. Apply the data label options that work best with your chart and the type of data it contains.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 pane opens. The Label Options tab and button are active, and the Label Options section is expanded.In the 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 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 pane, click the Close button.The pane closes.
Move a chart on a worksheet
If the chart in your worksheet is covering data or the cells where you wish to enter new data, moving the chart is quick and easy. You can simply drag the chart to a new location. 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.
Switch the row/column data used in a chart.
If you decide after creating a chart that the data in the rows and columns would better suit your needs if they were transposed, or switched, Excel provides features for doing this easily without creating a new chart. This will save you time and effort and improve your efficiency.Click the chart.The chart is selected, and the Chart Tools Design and Format tabs appear on the ribbon.Click the Chart Tools Design tab on the ribbon.In the Data group, click the Switch Row/Column button.Excel switches the chart rows and columns.
Insert a Stacked Column chart.
If you want to compare categories of worksheet data to one another, such as several years of sales for various branches of a company, a stacked column will probably suit your needs. This type of chart lets you compare the sales for each year to the total sales.Click and drag to select range A4:D8.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 A4:D8.
Remove a data series
Remove a data series when you no longer want to include it in your chart. For example, remove data for a product you no longer sell or a business unit that was sold off. 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 San Diego entry. The San Diego 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 exclude the data series.
Insert a trendline into a chart.
Use trendlines in charts to highlight trends in the data or forecast out future values. Click the bar chart. The chart is selected and the Chart Tools Design and Format tabs appear on the ribbon.Click the Chart Tools Design tab.In the Chart Layouts group, click the Add Chart Element button. The Add Chart Element menu opens. Point to Trendline. The Trendline gallery appears with options for different types of trendlines. In the Trendline gallery, click Linear.Excel inserts a linear trendline in the chart.
Insert a Histogram chart.
Using a Histogram chart in your worksheet displays the distribution of your data values within a series of columns, or bins, with ranges that are automatically determined by Excel. Histogram charts are useful for analyzing data such as test scores or sales amounts.Click and drag to select range D5:D13.You want to chart the sales values for each location. 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.
Edit a bin size used in a Histogram chart
A histogram chart plots the distribution of values in a data series by placing a range of values within each column, or bin. You can change the bin size quickly and easily by changing the Bin width setting.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 23.0 points. You want each bin to represent 15 data points, starting with billable rates of $75 through $90, $90-$105, and so on.Select the existing text in the Bin width text box, type 15.0 in the box, then 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.
Apply a fill color to a shape.
Add fill colors to the shapes in your worksheet to create visual interest and impact.Right-click the arrow shape in column E.The mini toolbar and the shortcut menu appear.On the mini toolbar, click the Fill button.The Shape Fill gallery appears. You can point to an option to see a preview of the color.In the Theme Colors palette of the Shape Fill gallery, click the Gold, Accent 4 option, the eighth color option in the top row.Excel changes the shape fill color.
Apply outline formatting to a shape.
Adding a shape, such as a callout or block arrow, is a good way to add interest to your worksheet. You can customize the shape to suit your particular needs and the overall look of the worksheet.Click the red callout shape.Sizing handles appear on the shape.Click the Drawing Tools Format tab on the ribbon.The Drawing Tools Format tab lets you insert and customize shapes, as well as manage WordArt and accessibility settings. In the Shape Styles group, click the Shape Outline button. The Shape Outline gallery opens. In the Shape Outline gallery, click the Blue, Accent 5 option (Theme Colors section, first row, second option from right)Excel applies the color you selected to the outline. In the Shape Styles group, click the Shape Outline button again.At the bottom of the Outline gallery, point to Weight.The Weight gallery opens.In the Weight gallery, click the 3 pt option. Excel changes the thickness of the outline.
Add gridlines to a chart
Adding gridlines to a chart can make the chart data easier to read and understand. Choose the gridline options that best suit your 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 bounds of a chart axis
Adding the most appropriate labels and spacing on your chart can make it easier to understand. Removing any extra space by changing the bounds on an axis can emphasize the data. 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 box, type 20000 and press ENTER.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 box, type 140000 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
Applying the right labels and spacing for your chart make it easier to understand. Adjusting the major and minor units of an axis can declutter the chart and make it easier to read. 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 Format Axis pane, select the value in the Major text box, type 20000, and then press ENTER. Values on the vertical axis currently appear in increments of 10,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 then 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.
Change the Horizontal Axis Label values used in a chart.
Choosing the right labels for chart data makes your chart easier to read and understand. You can change labels by editing the data source for an axis.Click the chart.The chart is selected. Range B5:D8 is highlighted in the worksheet as the chart data source. By default, Excel has added the generic labels 1 through 4 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 value labels are displayed in the Horizontal (Category) Axis Labels box. You want to edit these labels to display the Location 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 A5:A8.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 worksheet.
Change a chart layout.
Choosing the right layout makes your chart more meaningful. Use simple layouts for graphic impact, or use more detailed layouts when data clarification is important.Click the chart.The Chart Tools tabs appear on the ribbon.Click the Chart Tools Design tab.In the Chart Layouts group, click the Quick Layout button.The Quick Layout gallery opens. You can point to a layout option to see a live preview.Click the Layout 2 option, the second option in the top row of the gallery.Excel applies the layout 2 option to the chart.
Change the number format of a chart axis
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. In the chart, right-click the vertical (value) axis. Depending on the chart, the value axis might be either the vertical or the horizontal axis. In this chart, it is the vertical axis. When you right-click the axis, the mini toolbar and shortcut menu appear. On the shortcut menu, click Format Axis. The Format Axis task pane opens. The Axis Options tab and Axis Options button are active. Click the Number arrow to expand the Number section. Excel expands the Number section so all number formatting options are visible. Click the Category arrow. Excel displays a list of available number formats. Click Currency. Excel applies the Currency number format to the values on the vertical axis. In the upper-right corner of the Format Axis pane, click the Close button. Excel closes the Format Axis pane.
Add a data table to a chart.
Data tables are handy when you display a chart on a different worksheet from where the data is stored. This makes your chart easier to read and understand by avoiding the need to switch worksheets to read and compare the data to the chart itself.Click the chart.The chart is selected. The Chart Elements, Chart Styles, and Chart Filters buttons appear at the right of the chart.Click the Chart Elements button.Excel displays a gallery of chart elements. You select an element's check box to display it on the chart.In the gallery, click the Data Table check box.Excel displays the chart data table at the bottom of the chart, above the legend.
Move a chart to a chart sheet
The appearance and placement of a chart can add professionalism to your finished product. To make the chart stand on its own, place it on a separate chart sheet. Click the chart. The chart is selected, and the Chart Tools Design and Format tabs appear on the ribbon. Click the Chart Tools Design tab on the ribbon.In the Location group, click the Move Chart button. The Move Chart dialog box opens, with the current chart selected as an object in the Sales by Branch worksheet. You can move the chart to an existing worksheet or to a new chart sheet. In the Move Chart dialog box, click the New sheet option button to select it. Excel will set a default name for the new chart sheet based on the number of existing chart sheets and other worksheet settings. In this case, Excel names the sheet Chart1, a name you will accept. Click OK. The dialog box closes. Excel moves the chart to the new Chart1 chart sheet, which is now the active sheet.
Delete a chart element
The elements of a chart can help explain or identify the data. But when you no longer need an element, deleting it can reduce clutter and make the chart easier to understand. 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.
Insert a PivotChart
Use PivotCharts instead of regular charts when you want to switch back and forth between data views without creating new charts for each view. Click cell A2.The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon. Click the Insert tab on the ribbon. In the Charts group, click the Recommended Charts button. The Insert Chart dialog box opens with chart options listed on the left, and a preview of the active selection on the right. The Clustered Column chart is selected by default. You will accept this selection. Click OK. Excel inserts the PivotChart in the worksheet with the PivotChart Fields pane open. You can make changes to the PivotChart fields, filters, and formatting as desired.
Insert a Clustered Column Chart
Use a clustered column chart when you want to compare groups of data side by side in a visual representation. Position the mouse pointer over cell A4, press and hold the left mouse button as you drag the pointer down and to the right until it is over cell D8. Release the mouse button. 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 3-D Column section, click the 3-D Clustered Column chart type (the first 3-D Column type). Excel inserts a 3-D Clustered Column chart in the worksheet based on the range A4:D8.
Create a data bars conditional formatting rule
Use data bars in a range of numbers to see at a glance which values are largest and which are smallest. Data bars are easy to insert and require less screen space than charts. 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.
Create an X-Y scatter plot.
Using a Scatter chart can make your data easier to understand when you want to find the correlation between two sets of data such as performance ratings and sales.Click and drag to select range D4:E10.You want to insert a chart that plots the Performance Rating on one axis and the 2020 Sales values along the other axis.Click the Insert tab on the ribbon.In the Charts group, click the Insert Scatter (X, Y) or Bubble Chart button.The Scatter and Bubble galleries appear.In the Scatter section of the gallery, click the Scatter option, the first option in the gallery.Excel inserts a Scatter chart into the worksheet, with each piece of data being represented by a dot.
Change a chart type
Using a chart to represent worksheet data can make the data much easier to understand. If your first choice for a chart does not suit your needs, you can change the chart type quickly and easily. Click the Pie chart. The chart is selected, and the Chart Tools Design and Format tabs appear on the ribbon. Click the Chart Tools Design tab. In the Type group, click the Change Chart Type button. The Change Chart Type dialog box opens. The All Charts tab is active, and the current chart type is selected. In the left pane, click Column. You will change the chart type to a Column chart. The Clustered Column chart option is selected by default, with two sample charts displayed in the right pane. The sample on the left is selected by default. Click OK. The chart changes from a Pie chart to a Clustered Column chart. The columns make it easier to see individual data points and compare them to one another.
Change chart type
Using a chart to represent worksheet data can make the data much easier to understand. If your first choice for a chart does not suit your needs, you can change the chart type quickly and easily. the Pie chart. The chart is selected, and the Chart Tools Design and Format tabs appear on the ribbon. Click the Chart Tools Design tab. In the Type group, click the Change Chart Type button. The Change Chart Type dialog box opens. The All Charts tab is active, and the current chart type is selected. In the left pane, click Column. You will change the chart type to a Column chart. The Clustered Column chart option is selected by default, with two sample charts displayed in the right pane. The sample on the left is selected by default. Click OK. The chart changes from a Pie chart to a Clustered Column chart. The columns make it easier to see individual data points and compare them to one another.
Insert a Combination chart
Using a combo chart can let you plot two different but related groups of data within a single chart. When the values for one data series will not be legible on the axis for the other data series, however, you can assign one data series to a secondary axis. The secondary axis will have a different scale suitable for that set of data values, making all values legible.Click and drag to select range B3:D17.You want to graph both the Sales and Years as Client data for each client. 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.In the Insert Chart dialog box, click the All Charts tab.A list of all chart types appears in the left pane 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 Clustered 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 Years as Client data series, click the Secondary Axis check box to select it.The Years as Client values are much lower than the Sales values. If plotted on the same axis, the Years as Client values will not be readable. Using a secondary axis scaled to fit values in the Years as Client data series will make the chart more readable.Click OK.Excel inserts the combo chart in the worksheet. You can customize the chart later to suit your needs.
Move a chart element
When an element such as the legend is set by default into a crowded area, moving the element can make the chart easier to read and comprehend. 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, and then 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.
