Always include as much information on the chart as possible. All charts should contain the following information:
• chart title
• legend (unless only one data series is used) • y-axis title (unless it is self-evident) • x-axis title (unless it is self-evident)
Options
Always consider the following format options to improve the chart’s appearance: • Size the chart to make the content clear and readable.
• Use colour (background and individual parts) sparingly. A chart that contains too many colours may be too distracting.
• Use fonts, font type, and size sparingly. A chart that contains many different fonts and font types can also be very distracting.
TOPIC 7.9
Using Excel Help
Excel Help enables you to review some of the techniques learned in this lesson. Use the following steps to display the appropriate Help windows:
1. Click on the Help icon.
2. Type creating charts and click Search.
3. Select Create a chart.
4. After viewing the Help information, search other Help topics.
5. If you had difficulty with any of the following topics, display the corresponding Help information to review:
• Ways to select chart items • Formatting charts • Printing charts • Types of charts • Customize a chart
TOPIC 7.10
Self-testing questions
1. Is it always necessary to have at least two data ranges to create an Excel chart? 2. What is an XY chart? What is it used for?
3. Suppose you want to plot the price of gold over a 12-month period. Range B4:B15 contains the average monthly prices. All other cells are empty.
a. Briefly describe the steps you would use to construct a simple column chart. b. Is a column chart the best way to represent this information? Why or why
not? What other chart type would you consider?
4. Suppose you have a worksheet with range B6:B12 containing the labels for five sources of income, and range C6:C12 showing the actual income for the five sources for the month of January. Describe the steps you would use to present this
information in a pie chart that shows percentages for each source of income. 5. Describe how to change a line chart to a 3-D column chart.
6. What are the differences between a chart on a chart sheet and an embedded chart on a worksheet?
7. Describe how you would: a. Print an embedded chart.
b. Print an embedded chart with the data on the worksheet. c. Print a chart sheet.
LESSON 8
Lists
Topic outline
8.1 Basic concepts 8.2 Using a data form 8.3 Locating a record 8.4 Sorting a list
8.5 Using lookup functions 8.6 Filtering a list
8.7 Using Advanced Filter 8.8 Extracting filtered records 8.9 Using Excel Help 8.10 Self-testing questions
Overview
A list in Excel is a labelled series of columns and rows containing related data. You can create lists to manage and analyze organized sets of data such as inventory records,
transactions, client names and addresses, or any related data that can be set up in columns and rows. In this lesson, you will create and modify a list, look up records, sort them, and use filters to search and report data that meet specified criteria.
Learning objectives
• Create a list in Excel.
• Add and modify records in a list using a data form. • Browse records in a list using a data form.
• Set criteria in a data form to look up records. • Sort a list in different ways.
• Use VLOOKUP to look up values in a list. • Use HLOOKUP to look up values. • Filter records in a list.
• Use the Advanced Filter to filter a list. • Extract filtered records from a list. • Obtain Excel Help on how to use lists.
TOPIC 8.1
Basic concepts
A list in Excel is a worksheet range containing related data organized as columns and rows. You can consider a list as a simple database in which the columns are the fields and the rows are the records. A field is a column that contains a single category of data such as names. A
record is a row containing data for each field such as an individual’s name, ID number,
department, and telephone number. The top cell in each column in the list must contain a unique column label (field name), which is text that identifies the data in the column. You set up a list with column labels, fields, and records so you can manage large amounts of data to obtain precisely the information you need. Once you set up an Excel list, you can look up information and you can find, add, and delete records.
Exhibit 8-1 shows the first 14 records of a list. The list starts in row 4. The column labels are Invoice, Date, Sales amount, Commission, Salesperson, and Store. Notice each column label is unique. Each record contains data for each of the five fields for an actual sale. For example, the first record (row 5) lists the invoice number, date, sales amount, and commission of the sale transacted by M. Aguila at the Main Street store.
EXHIBIT 8-1
A list as a simple database
Although a worksheet can contain as many lists as you want, it is advisable to use only one list for each worksheet. Some list management tasks, such as filtering, can work with only one list at a time on a worksheet. A list can be as large as the worksheet: up to 1,048,576 rows by 16,384 columns.
When working with lists, you can create a name for the list and then use that name, or you can work directly with the range reference. Most users prefer to name the list (using the Define Name command in the Formulas ribbon) and work with that name.
Once a list is created (and usually named), you can add, edit, and delete records. You can also find records, sort them, and add subtotals. A list must be separated from any other data on the worksheet by at least one blank row and one blank column.