# Gcflearnfree Excel 2010 Lesson 17 Homework

Excel charts let you illustrate your workbook data graphically to see trends. Also use charts in Excel to visualize comparisons.

### Introduction

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

#### Video: Working with Charts in Excel 2010

Watch the video (4:31).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.

Optional: You can download this example for extra practice.

#### Types of charts

Click the arrows in the slideshow below to view examples of some of the types of charts available in Excel.

Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.

Column charts use vertical bars to represent data. They can work with many different types of data, but they're most frequently used for comparing information.

Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.

Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's easy to see which values make up the percentage of a whole.

Bar charts work just like column charts, but they use horizontal instead of vertical bars.

Area charts are similar to line charts, except the areas under the lines are filled in.

Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time.

#### Identifying the parts of a chart

Click the buttons in the interactive below to learn about the different parts of a 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.

### 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.

### 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.

### Title

The **title** should clearly describe what the chart is illustrating.

### 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.

#### 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.Selecting cells - Click the
**Insert**tab. - In the
**Charts**group, select the desired**chart category**(**Column**, for example).Selecting the Column category - Select the desired
**chart type**from the drop-down menu (**Clustered Column**, for example).Selecting a chart type - The chart will appear in the worksheet.The new chart

### 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.

#### To change chart type:

- From the
**Design**tab, click the**Change Chart Type**command. A dialog box appears.The Change Chart Type command - Select the desired
**chart type**, then click**OK**.Selecting a chart type

#### 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 Switch Row/Column command - The chart will readjust.Book sales, grouped by year

#### 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.Viewing all of the chart layouts - Select the desired layout.Selecting a chart layout
- The chart will update to reflect the new layout.

Some layouts include **chart titles**, **axes**, or **legend labels**. To change them, place the **insertion point** in the text and begin typing.

#### 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.Viewing all of the Chart Styles - Select the desired style.Selecting a chart 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.The Move Chart command - Select the desired location for the chart (choose an existing worksheet, or select New Sheet and name it).Selecting a different worksheet for the chart
- 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.

Watch the video below to learn how to use tables to keep charts up to date.

### Challenge!

- Open an
**existing Excel 2010 workbook**. If you want, you can use this example. - Use worksheet data to create a
**chart**. - Change the
**chart layout**. - Apply a
**chart style**. - Move the chart to a
**different worksheet**.

When using Excel functions play an important role in finding values for a range of cells. Learn all about using functions in Excel.

### Working with basic functions

Figuring out formulas for calculations you want to make in Excel can be tedious and complicated. Fortunately, Excel has an entire library of **functions**—or **predefined formulas**—you can take advantage of. You may be familiar with common functions like **sum**, **average**, **product**, and **count**, but there are hundreds of functions in Excel, even for things like formatting text, referencing cells, calculating financial rates, and analyzing statistics.

In this lesson, you'll learn the basics of inserting common functions into your worksheet by utilizing the **AutoSum** and **Insert Functions** commands. You will also become familiar with how to **search **and** find various functions**, including exploring Excel's **Functions Library**.

### Basic functions

#### Video: Working with Basic Functions in Excel 2010

Watch the video (5:11).A **function** is a **predefined formula** that performs calculations using specific values in a particular order. One of the key benefits of functions is that they can save you time because you do not have to write the formula yourself. Excel has hundreds of functions to assist with your calculations.

To use these functions correctly, you need to understand the different **parts of a function** and how to create **arguments** in functions to calculate values and cell references.

You can download this example for extra practice.

#### The parts of a function

The order in which you insert a function is important. Each function has a specific order—called **syntax**—which must be followed in order for the function to work correctly. The basic syntax to create a formula with a function is to insert an **equals sign (=)**, **function name **(SUM, for example, is the function name for addition), and **argument**. Arguments contain the information you want the formula to calculate, such as a range of cell references.

#### Working with arguments

Arguments must be enclosed in **parentheses**. Individual values or cell references inside the parentheses are separated by either **colons** or **commas**.

**Colons**create a reference to a range of cells.

For example,**=AVERAGE(E19:E23)**would calculate the**average**of the cell range E19 through E23.

**Commas**separate individual values, cell references, and cell ranges in parentheses. If there is more than one argument, you must separate each argument by a comma.

For example,**=COUNT(C6:C14,C19:C23,C28)**will**count**all the cells in the three arguments that are included in parentheses.

#### To create a basic function in Excel:

- Select the cell where the answer will appear (
**F15,**for example). - Type the
**equals sign (=)**, then enter the**function name**(**SUM**, for example).Creating a SUM function - Enter the cells for the
**argument**inside the parentheses.Adding cells to the function argument - Press
**Enter**, and the result will appear.Result

Excel **will not always tell you** if your function contains an error, so it's up to you to check all of your functions. To learn how to do this, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

#### Using AutoSum to select common functions

The **AutoSum **command allows you to automatically return the results for a range of cells for common functions like SUM and AVERAGE.

- Select the cell where the answer will appear (
**E24**, for example). - Click the
**Home**tab. - In the
**Editing**group, click the**AutoSum**drop-down arrow and select the function you want (**Average**, for example).AutoSum command - A formula will appear in
**E24**, the selected cell. If logically placed, AutoSum will select your cells for you. Otherwise, you will need to click the cells to choose the argument you want.AutoSum selects and displays cell range - Press
**Enter**, and the result will appear.Result

The **AutoSum **command can also be accessed from the **Formulas** tab.

You can also use the **Alt+=** keyboard shortcut instead of the AutoSum command. To use this shortcut, hold down the **Alt** key and then press the **equals sign**.

Watch the video below to see this shortcut in action.

### The Function Library

There are hundreds of functions in Excel, but only some will be useful for the type of data you're working with. There is no need to learn every single function, but you may want to explore some of the different types to get ideas about which ones might be helpful to you as you create new spreadsheets.

A great place to explore functions is in the **Function Library** on the Formulas tab. Here, you can search and select Excel functions based on categories such as **Financial**, **Logical**, **Text**, and **Date & Time**. Click the buttons in the interactive below to learn more.

### Date & Time

The **Date & Time** category contains functions for working with dates and time and will return results like the current date and time (**NOW**) or the seconds (**SECOND**).

### AutoSum

The **AutoSum** command allows you to automatically return results for common functions.

### Recently Used

Use the **Recently Used** command to access functions you have recently worked with.

### Financial

The **Financial** category contains functions for financial calculations like determining a payment (**PMT**) or interest rate for a loan (**RATE**).

### More Functions

**More Functions** contains additional functions under categories for **Statistical**, **Engineering**, **Cube**, **Information**, and Compatibility.

### Math & Trig

The **Math & Trig** category includes functions for numerical arguments. For example, you can round values (**ROUND**), find the value of Pi (**PI**), multiply (**PRODUCT**), and subtotal (**SUBTOTAL**).

### Lookup & Reference

The **Lookup & Reference** category contains functions that will return results for finding and referencing. For example, you can add a hyperlink (**HYPERLINK**) to a cell or return the value of a particular row and column intersection (**INDEX**).

### Text

The **Text** category contains functions that work with the text in arguments using tasks like converting text to lowercase (**LOWER**) or replacing text (**REPLACE**).

### Logical

Functions in the **Logical** category check arguments for a value or condition. For example, if an order is more than $50 add $4.99 for shipping, but if it is more than $100 do not charge for shipping (**IF**).

### Insert Function

The **Insert Function** command allows you to easily search for a command by entering a description of what you are looking for.

#### To insert a function from the Function Library:

- Select the cell where the answer will appear (
**I6**, for example). - Click the
**Formulas**tab. - From the
**Function Library**group, select the**function category**you want. In this example, we'll choose**Date & Time**. - Select the desired
**function**from the Date & Time drop-down menu. We'll choose the**NETWORKDAYS**function to count the days between the order date and receive date in our worksheet.Function Library Date & Time category - The
**Function Arguments**dialog box will appear. Insert the cursor in the**first field**, then enter or select the cell(s) you want (**G6**, for example).Selecting cell for the Start_date field - Insert the cursor in the
**next field**, then enter or select the cell(s) you want (**H6**, for example).Selecting cell for the End_date field - Click
**OK**, and the result will appear. Our results show that it took five days to receive the order.Result

### The Insert Function command

The** Insert Function** command is convenient because it allows you to search for a function by typing a description of what you're looking for or by selecting a category to peruse. The Insert Function command can also be used to easily enter or select more than one argument for a function.

#### Using the Insert Function command

In this example, we want to find a function that will count the total number of supplies listed in the Office Supply Order Log. The basic COUNT function only counts cells with numbers; we want to count the cells in the Office Supply column, which uses text. Therefore, we'll need to find a formula that counts cells with text.

- Select the cell where the answer will appear (
**A27**, for example). - Click the
**Formulas**tab, then select the**Insert Function**command.Insert Function command - The
**Insert Function**dialog box will appear. - Type a
**description**of the function you are searching for, then click**Go (Count cells with text**, for example). You can also search by selecting a category.Searching for a function - Review the results to find the function you want (
**COUNTA**, for example). Click**OK**.Reviewing function search results - The
**Function Arguments**dialog box will appear. Insert the cursor in the**first field**, then enter or select the cell(s) you want (**A6:A14**, for example).Selecting cell range for Value1 field - Insert the cursor in the
**next field**, then enter or select the cell(s) you want (**A19:A23**, for example). You can continue to add additional arguments if needed.Selecting cell range for Value2 field - Click
**OK**,and the result will appear. Our results show that 14 Total Supplies were ordered from our log.Result

If you're comfortable with basic functions, you may want to try a more advanced one like **VLOOKUP**. You can check out our article on How to Use Excel's VLOOKUP Function for more information. If you want to learn even more about functions, check out our Excel Formulas tutorial.

### Challenge!

- Open an
**existing Excel 2010 workbook**. If you want, you can use this example. - Create a function that contains
**more than one argument**. - Use
**AutoSum**to insert a function. If you are using the example, insert the MAX function in cell E15 to find the highest-priced supply. - Insert a function from the
**Functions Library**. If you are using the example, find the PRODUCT function (multiply) to calculate the Unit Quantity times the Unit Price in cells F19 through F23. - Use the
**Insert Function**command to search and explore functions.

## 0 Thoughts to “Gcflearnfree Excel 2010 Lesson 17 Homework”