CS6: Electronic Spreadsheets II

This unit explains Electronic Spreadsheets II

Working with Charts

A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends much easier. In this lesson, you’ll learn how to insert charts and modify them so they communicate information effectively.

Charts

Excel workbooks can contain a lot of data, and this data can often be difficult to interpret. For example, where are the highest and lowest values? Are the numbers increasing or decreasing?

The answers to questions like these can become much clearer when data is represented as a chart. Excel has various types of charts, so you can choose one that most effectively represents your data.

Types of charts

  • Bar chart
  • Line chart
  • Pie chart
  • Area chart
  • Column chart
  • Scatter chart
  • Surface chart
  • Stock chart
  • Radar chart

Identifying the parts of a chart

Vertical Axis

The vertical axis, also known as the y axis, is the vertical part of the chart.

In this example, a column chart, the vertical axis measures the height—or value—of the columns, so it is also called the value axis. However, in a bar chart, the horizontal axis would be the value axis.

Data Series

The data series consists of the related data points in a chart. If there are multiple data series in the chart, each will have a different color or style. Pie charts can only have one data series.

In this example, the green columns represent the Romance data series.

Legend

The legend identifies which data series each color on the chart represents. For many charts it is crucial, but for some charts it may not be necessary and can be deleted.

In this example, the legend allows viewers to identify the different book genres in the chart.

Horizontal Axis

The horizontal axis, also known as the x axis, is the horizontal part of the chart.

In this example, the horizontal axis identifies the categories in the chart, so it is also called the category axis. However, in a bar chart, the vertical axis would be the category axis.

How to create a chart

Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart.

Click the Insert tab.

In the Charts group, select the desired chart category (Column, for example).

Select the desired chart type from the drop-down menu (Clustered Column, for example).

The chart will appear in the worksheet.

Chart tools

Once you insert a chart, a set of chart tools arranged into three tabs will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.

How to change chart type

From the Design tab, click the Change Chart Type command. A dialog box appears.

Select the desired chart type, then click OK.

How to switch row and column data

Sometimes when you create a chart, the data may not be grouped the way you want. In the clustered column chart below, the Book Sales statistics are grouped by Fiction and Non-Fiction, with a column for each year.

However, you can also switch the row and column data so the chart will group the statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart contains the same data—it’s just organized differently.

Select the chart.

From the Design tab, select the Switch Row/Column command.

The chart will readjust.

How to change chart layout

Select the Design tab.

Click the More drop-down arrow in the Chart Layouts group to see all of the available layouts.

Select the desired layout.

The chart will update to reflect the new layout.

To change chart style

Select the Design tab.

Click the More drop-down arrow in the Chart Styles group to see all of the available styles.

Select the desired style.

The chart will update to reflect the new style.

To move the chart to a different worksheet

Select the Design tab.

Click the Move Chart command. A dialog box appears. The current location of the chart is selected.

Select the desired location for the chart (choose an existing worksheet, or select New Sheet and name it).

Click OK. The chart will appear in the new location.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the new data. To fix this, you can adjust the data range. Simply click the chart, and it will highlight the data range in your spreadsheet. You can then click and drag the handle in the lower-right corner to change the data range.

If you frequently add more data to your spreadsheet, it may become tedious to update the data range. Luckily, there is an easier way. Simply format your source data as a table, then create a chart based on that table. When you add more data below the table, it will automatically be included in both the table and the chart, keeping everything consistent and up to date.

Worksheet Page Layout

By default, Microsoft Excel prints worksheets in portrait orientation (taller than wide). You can change the page orientation to landscape on a worksheet-by-worksheet basis.

Change the page orientation

Select the worksheet or worksheets for which you want to change the orientation.

Tip: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

On the Page Layout tab, in the Page Setup group, click Orientation, and then click Portrait or Landscape.

Notes: 

If you don’t have a printer set up, the Orientation option will appear dimmed, and you won’t be able to select it. To resolve this, you must set up a printer. The option also appears dimmed when you’re editing the contents of a cell. To resolve this, press Enter to accept the changes or Esc to cancel the changes.

Because you can set page orientation on a worksheet-by-worksheet basis, you might print some worksheets in a workbook in one orientation (such as portrait orientation) and other worksheets in the same workbook in the opposite orientation (such as landscape orientation). Simply set the orientation for each worksheet as appropriate, and then Print a worksheet or workbook.

Change the page orientation when you are ready to print

Select the worksheet, worksheets, or worksheet data that you want to print.

Click File > Print.

In the Page Orientation drop-down box, under Settings, click Portrait Orientation or Landscape Orientation.

When you are ready to print, click Print.

Create a template that uses landscape orientation by default

To save time, you can save a workbook configured to print in landscape orientation as a template. You can then use this template to create other workbooks.

Create the template

Create a workbook.

Select the worksheet or worksheets for which you want to change the orientation.

How to select worksheets

A single sheet Click the sheet tab.
Two or more adjacent sheets Click the tab for the first sheet. Then hold down Shift while you click the tab for the last sheet that you want to select.
Two or more nonadjacent sheets Click the tab for the first sheet. Then hold down Ctrl while you click the tabs of the other sheets that you want to select.
All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets.

Note: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

On the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape.

Note: If you don’t have a printer set up, the Orientation option will appear dimmed, and you won’t be able to select it. To resolve this, you must set up a printer. The option also appears dimmed when you’re editing the contents of a cell. To resolve this, press Enter to accept the changes or Esc to cancel the changes.

Make any other customizations as necessary.

Tip: If you want to customize several worksheets at one time, you can temporarily group the worksheets, make your changes, and then ungroup them. To group the worksheets, right-click any tab at the bottom of the worksheet. On the shortcut menu, click Select All Sheets. In the title bar, you should see the name of the workbook followed by the word [Group]. Then, change the orientation to landscape or make any other customization that you want. To turn off the grouping, right-click any tab, and then click Ungroup Sheets (or just click another worksheet tab).

Click the File tab.

Click Save As, then select the location where you want to save your worksheet. For example, click Computer (or This PC in Excel 2016), and then click Desktop.

In the File name box, type the name that you want to use for the template.

In the Save as type box, click Excel Template (*.xltx), or click Excel Macro-Enabled Template (*.xltm)if the workbook contains macros that you want to make available in the template.

Click Save.

The template is automatically sent in the Templates folder.

To use the template to create a workbook, do the following (for Excel 2016, Excel 2013, and Excel 2010 only):

Click File > New.

Click Personal.

Click the icon or name of your saved template.

A VIDEO SHOWING WORKSHEET PAGE LAYOUT

Printing a Worksheet

You can print entire or partial worksheets and workbooks, one at a time, or several at once. And if the data that you want to print is in a Microsoft Excel table, you can print just the Excel table.

You can also print a workbook to a file instead of to a printer. This is useful when you need to print the workbook on a different type of printer from the one that you originally used to print it.

Before you print

Before you print anything in Excel, do remember that there are many options available for an optimal print experience. For more information, see Printing in Excel.

Important: Some formatting, such as colored text or cell shading, may look good on the screen but not look how you expect when it prints on a black-and-white printer. You may also want to print a worksheet with gridlines displayed so that the data, rows, and columns stand out better.

Print one or several worksheets

Select the worksheets that you want to print.

Click File > Print, or press CTRL+P.

Click the Print button or adjust Settings before you click the Print button.

Print one or several workbooks

All workbook files that you want to print must be in the same folder.

Click File > Open.

Hold down CTRL click the name of each workbook to print, and then click Print.

Print all or part of a worksheet

Click the worksheet, and then select the range of data that you want to print.

Click File, and then click Print.

Under Settings, click the arrow next to Print Active Sheets and select the appropriate option.

Click Print.

Note: If a worksheet has defined print areas, Excel will print only those print areas. If you don’t want to print only the defined print area, select the Ignore print area check box. Learn more on setting or clearing a print area.

Print an Excel table

Click a cell within the table to enable the table.

Click File, and then click Print.

Under Settings, click the arrow next to Print Active Sheets and select Print Selected Table.

Click Print.

Print a workbook to a file

Click File, and then click Print, or press Ctrl+P.

Under Printer, select Print to File.

Click Print.

In the Save Print Output As dialog box, enter a file name and then click OK. The file will be saved in your Documents folder

Important: If you print the saved file on a different printer, the page breaks and font spacing may change.

THIS VIDEO EXPLAINS PRINTING A WORKSHEET

s

 

Welcome to FAWE

STEM Elearning

We at FAWE have built this platform to aid learners, trainers and mentors get practical help with content, an interactive platform and tools to power their teaching and learning of STEM subjects, more

How to find your voice as a woman in Africa

top
© FAWE, Powered by: Yaaka DN.
X