BCIS Lab 4: Microsoft Excel 2013

Exploring Charts: > The plot area is the area where the data series are plotted. >The chart area is the area that encompasses the entire chart including the plot area and optional layout elements, such as title and legend. >The chart title is a text box above or overlaying the chart. To change the title text, click the box to select it and edit the text directly in the text box. You can also type new text in the formula bar and then press ↵Enter. >The legend tells you which data point or data series is represented by each color in the chart.

A chart is a graphic that represents numeric data visually. Data markers represent columns, bars, pie pieces, or other visual elements in a chart. Data series are a group of related data points plotted on a chart.

Adding Headers & Footers

To add a header or footer to a worksheet from Page Layout view: Switch to Page Layout view by clicking the Page Layout button on the status bar. The header area has three sections with the text Click to add header in the center section. Click the header section where you want to add information (left, center, or right). The Header & Footer Tools Design contextual tab appears. In the Header & Footer group, click the Header button and select one of the predefined headers, or click a button in the Header & Footer Elements group to add a specific header element such as the sheet name or the current date. Excel inserts the code for the header element. Once you click away from the header, you will see the actual header text.

Inserting a Pie Chart: Pie charts represent the data as parts of a whole.

To add a pie chart: (1) Select the data you want to include in the pie chart. (2) On the Insert tab, in the Charts group, click the Insert Pie Chart button. (3) Click the chart type you want to insert the chart into the worksheet.

Using the Recommended Charts Feature:

To add a recommended chart to a worksheet using the Quick Analysis tool: (1) Select the data you want to visualize as a chart. (2) The Quick Analysis Tool button appears near the lower right corner of the selected range. Click the Quick Analysis Tool button, and then click the Charts tab. (3) Hover the mouse cursor over each chart type to see a live preview of the chart. Click the button for the chart type you want. Recommended charts may include multiple versions of the same chart type. Check the live preview carefully to ensure that the data display exactly as you want.

Applying Quick Styles & Colors to Charts:

To change the chart style: (1) Select the chart. (2) On the Chart Tools Design tab, in the Chart Styles group, click the style you want to use, or click the More button to see all of the Quick Styles available. (3) To change the color scheme, on the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button and select the color scheme you want.

Entering Simple Formulas: A formula is an equation used to calculate value.

To enter a formula: (1) Click the cell in which you want to enter the formula. (2) Press =. (3) Type the formula. (4) To add a cell reference to a formula, you can type the cell address or click the cell. If you are in the middle of typing a formula and you click another cell in the worksheet, Excel knows to add that cell reference to the formula instead of moving to it. (5) Press ↵ Enter.

Using AutoSum to Insert a SUM function

To insert a SUM function using AutoSum: (1) Select the cell in which you want to enter the function. (2) On the Home tab, in the Editing group, click the AutoSum button. (3) Excel automatically inserts a formula with the SUM function, using the range of cells contiguous to (next to) the selected cell as the arguments for the function. You can increase or decrease the range of cells selected by clicking and dragging the corner of the highlighted cell range (4) Press ↵ Enter to accept the formula.

Merging Cells and Splitting Merged Cells Merge Across —lets you merge cells in multiple rows without merging the rows together. The cells in each row will be merged together, keeping the data in the leftmost cell in each row, but still keeping each row separate. Merge Cells —lets you merge cells together without centering the data. Like the Merge & Center command, Merge Cells will combine all the selected cells into one cell, keeping only the data in the uppermost left cell. Unmerge Cells —splits a merged cell back into its original cells. When the selected cell is a merged cell, clicking the Merge & Center button will also undo the merge.

To merge cells and center their content: (1) Select the cells you want to merge, making sure the text you want to keep is in the uppermost left cell. (2) On the Home tab, in the Alignment group, click the Merge & Center button.

Moving a Chart: To move a chart to a new sheet: >If necessary, select the chart. If you just created the chart, it will still be selected. >On the Chart Tools Design tab, in the Location group, click the Move Chart button. >The Move Chart dialog opens. >In the Move Chart dialog, click the New sheet radio button to move the chart to its own worksheet. If you want to specify a name for the new sheet, type it in the New sheet box. To move the chart to an existing worksheet, click the Object in radio button, and then select the name of the sheet you want to move the chart to. >Click OK.

To move a chart by dragging: Click in the chart area of the chart you want to move. (Be careful not to click in the plot area, or you will move just the plot area instead of the entire chart.) Your mouse cursor will change to the move cursor movechart icon. With your left mouse button depressed, drag the chart to the new location on the worksheet, and then release the mouse button to "drop" the chart at the new location.

Moving & Copying Worksheets: Note, To move or copy more than one worksheet, press ↑Shift and click the worksheets you want to move or copy. If the worksheets are not consecutive, then press Ctrl instead. Also, You can also open the Move or Copy dialog from the Ribbon: On the Home tab, in the Cells group, click the Format button, and select Move or Copy Sheet...

To move a worksheet within a workbook: (1) Click the worksheet tab and hold down the mouse button (2) Notice that the mouse pointer changes to the move cursor shape. (3) Drag the mouse cursor to the position where you want to move the sheet, and release the mouse button.

Naming Worksheets: You can also use the Ribbon to rename a worksheet. Click the sheet tab you want to rename. On the Home tab, in the Cells group, click the Format button. Click Rename Sheet. Type the new sheet name, and press ↵Enter.

To rename a worksheet- (1) right-click the worksheet tab, and select Rename. (2) excel highlights the sheet name, allowing you to replace it as you type (3) Type the new sheet name, and press ↵Enter.

Setting and Clearing the Print Area:

To set a print area- (1) select the area you want to print (2) On the page layout tab, in the page setup group, click the print area button (3) Click Set Print Area

Applying Number Formats

When you right‐click a cell, these formats are available from the Mini toolbar: Accounting Number Format, Percent style, and Comma style. The Increase Decimal and Decrease Decimal buttons are also available from the Mini toolbar. To apply the Percent Style, you can use the keyboard shortcut Ctrl + ↑ Shift + 5.