Home### Spreadsheets

- Getting started
- Modify Columns, Rows and Cells
- Formatting Cells
- Basic formulas
- Sorting data
- Create a Complex Formulas
- Working with Basic Functions
- Formatting Tables
- Conditional Formatting
- Calculation with dates and time
- Text Functions
- Statistical functions
- Logical functions
- Nested IF function
- Functions
- Vlookup functions
- Charts
- Video Support

Excel is a **spreadsheet program** that allows you to store, organize, and analyze information. In this lesson, you will learn your way around the Excel 2010 environment, including the new **Backstage view**, which replaces the Microsoft Button menu from Excel 2007.

The Ribbon

The Ribbon contains multiple **tabs**, each with several **groups** of commands. You can add your own tabs that contain your favorite commands.

Backstage View Backstage view gives you various options for saving, opening a file, printing, or sharing your document. It is similar to the Office Button menu from Excel 2007 or the File menu from earlier versions of Excel. However, instead of just a menu, it is a full-page view which makes it easier to work with. To Get to Backstage View: On the Ribbon, click the File tab.

- Choose your desired option, or return to your workbook by clicking on any tab on the Ribbon.

Click the buttons in the interactive below to learn about the different things you can do in Backstage view.

- Click the
**File**tab. This takes you to**Backstage view**. - Select
**New**. - Select
**Blank workbook**under**Available Templates**. It will be highlighted by default. - Click
**Create**. A new, blank workbook appears in the Excel window.

When you open a new, blank workbook, the cells are set to a **default size**.You do have the ability to modify cells, and to insert and delete columns, rows, and cells, as needed. In this lesson, you will learn how to **change row height and column width**; **insert and delete rows and columns**; **wrap text** in a cell; and **merge cells**.

- Position your mouse over the
**column line**in the**column heading**so that the**white cross**becomes a**double arrow**.

**Click and drag the column** to the right to increase the column width or to the left to decrease the column width.

- Select the columns you want to modify.
- Click the
**Format**command on the**Home**tab. The format drop-down menu appears. - Select
**Column Width**.

The **Column Width** dialog box appears. Enter a specific measurement.

- Click
**OK**. The width of each selected column will be changed in your worksheet.

Select **AutoFit Column Width** from the format drop-down menu and Excel will automatically adjust each selected column so that all the text will fit.

- Position the
**cursor**over the**row line**so that the**white cross**becomes a**double arrow**.

**Click and drag the row** downward to increase the row height or upward decrease the row height.

**Click and drag the row** downward to increase the row height or upward decrease the row height.

- Select the rows you want to modify.
- Click the
**Format**command on the**Home**tab. The format drop-down menu appears. - Select
**Row Height**.

The **Row Height** dialog box appears. Enter a specific measurement.

- Click
**OK**. The selected rows heights will be changed in your spreadsheet.

Select **AutoFit Row Height** from the format drop-down menu and Excel will automatically adjust each selected row so that all the text will fit.

- Select the
**row***below*where you want the new row to appear.

Click the **Insert** command on the **Home** tab.

The new row appears in your worksheet.

When inserting new rows, columns, or cells, you will see the **Insert Options** button by the inserted cells. This button allows you to choose how Excel formats them. By default, Excel formats inserted rows with the same formatting as the cells in the row above them. To access more options, hover your mouse over the Insert Options button and click on the drop-down arrow that appears.

- Select the
**column**to the*right*of where you want the new column to appear. For example, if you want to insert a column between A and B, select column B.

Click the **Insert** command on the **Home** tab.

The new column appears in your worksheet.

If a cell contains more text than can be displayed, you can choose to wrap the text within the cell or merge the cell with empty, adjoining cells. **Wrap text ** to make it display on multiple lines of the cell. **Merge cells** to combine adjoining cells into one larger cell.

- Select the cells with text you want to wrap.

Select the **Wrap Text** command on the **Home** tab.

The text in the selected cells will be wrapped in your worksheet.

- Select the cells you want to merge together.

Select the **Merge & Center** command on the **Home** tab.

The selected cells will be merged and the text will be centered.

If you change your mind, re-click the **Merge & Center** command to unmerge the cells.

Click the drop-down arrow next to the **Merge & Center** command on the Home tab. The **merge** drop-down menu appears.

**Merge & Center:**Merges selected cells into one cell and centers the text.**Merge Across:**Merges each*row*of selected cells into larger cells. This command is useful if you are merging content across multiple rows of cells and do not want to create one large cell.**Merge Cells:**Merges selected cells into one cell.**Unmerge Cells:**Unmerges the selected cells.

- Right-click the
**worksheet tab**you want to rename. The**worksheet**menu appears. - Select
**Rename**.

The text is now highlighted by a black box. Type the name of your worksheet.

Click anywhere outside of the tab. The worksheet is renamed.

A **formula** is an equation that performs a calculation. Like a calculator, Excel can execute formulas that add, subtract, multiply, and divide.

One of the most useful features of Excel is its ability to calculate using a cell address to represent the value in a cell. This is called using a cell reference.

In order to maximize the capabilities of Excel, it is important to understand how to **create simple formulas and use cell references**.

Excel uses standard operators for equations, such as a **plus sign** for addition (+), a **minus sign** for subtraction (-), an **asterisk** for multiplication (*), a **forward slash** for division (/), and a **caret** (^) for exponents.

The key thing to remember when writing formulas for Excel is that all formulas must begin with an **equal sign** (=). This is because the cell contains, or is equal to, the formula and its value.

- Select the cell where the answer will appear (B4, for example).

- Type the
**equal sign (=)**.

- Type in the formula you want Excel to calculate. For example, "75/250".

When a formula contains a cell address, it is called a **cell reference**. Creating a formula with cell references is useful because you can update data in your worksheet without having to rewrite the values in the formula.

- Select the cell where the answer will appear (B3, for example).

- Type the
**equal sign (=)**.

- Type the cell address that contains the first number in the equation (B1, for example).

- Type the operator you need for your formula. For example, type the
**addition sign (+)**.

- Type the cell address that contains the second number in the equation (B2, for example).

With over 17 billion cells in a single worksheet, Excel 2010 gives you the ability to work with an **enormous amount of data**. Arranging your data alphabetically, from smallest to largest, or other criteria, can help you find the information you're looking for more quickly.

**Sorting** is a common task that allows you to change or customize the order of your spreadsheet data. For example, you could organize an office birthday list by employee, birthdate, or department, making it easier to find what you're looking for. Custom sorting takes it a step further, giving you the ability to sort multiple levels (such as department first, then birthdate, to group birthdates by department), and more.

- Select a cell in the column you want to sort by. In this example, we will sort by Last Name.

- Select the
**Data**tab, and locate the**Sort and Filter**group.

- Click the ascending command to
**Sort A to Z**, or the descending command to**Sort Z to A**.

The data in the spreadsheet will be organized alphabetically.

Sorting options can also be found on the Home tab, condensed into the **Sort & Filter** command.

- Select a cell in the column you want to sort by.

- From the
**Data**tab, click the ascending command to**Sort Smallest to Largest**, or the descending command to**Sort Largest to Smallest**.

- The data in the spreadsheet will be organized numerically.

- Select a cell in the column you want to sort by.

- From the
**Data**tab, click the ascending command to**Sort Oldest to Newest**, or the descending command to**Sort Newest to Oldest**.

- The data in the spreadsheet will be organized by date or time.

You can use a **Custom List** to identify your own sorting order, such as days of the week, or, in this example, t-shirt sizes from smallest to largest (Small, Medium, Large, and X-Large).

- From the
**Data**tab, click the**Sort**command to open the**Sort**dialog box.

Identify the column you want to **Sort by** by clicking the drop-down arrow in the **Column** field. In this example, we will choose T-Shirt Size.

- Make sure
**Values**is selected in the**Sort On**field.

- Click the drop-down arrow in the
**Order**field, and choose**Custom List...**

- Select
**NEW LIST**, and enter how you want your data sorted in the**List entries**box. We will sort t-shirt sizes from smallest to largest.

- Click
**Add**to save the list, then click**OK**.

Click **OK** to close the Sort dialog box and sort your data.

The spreadsheet will be sorted in order of Small, Medium, Large, and X-Large.

- From the
**Data**tab, click the**Sort**command to open the**Sort**dialog box. - Identify the column you want to
**Sort by**by clicking the drop-down arrow in the**Column**field. - Choose whether you want to sort by Cell Color, Font Color, or Cell Icon in the
**Sort On**field. In this example, we will sort on**Font Color**.

In the **Order** field, click the drop-down arrow to choose a color, then decide whether you want it ordered **On Top** or **On Bottom**.

Click **OK**. The data is now sorted by attribute rather than text.

Excel is a spreadsheet application that can help you calculate and analyze numerical information for household budgets, company finances, inventory, and more. To do this, you need to understand **complex formulas**.

In this lesson, you will learn how to write complex formulas in Excel following the order of operations. You will also learn about **relative and absolute cell references** and how to **copy and fill formulas** containing cell references.

Excel calculates formulas based on the following **order of operations**:

- Operations enclosed in
**parentheses** **Exponential**calculations (to the power of)**Multiplication**and**division**, whichever comes first**Addition**and**subtraction**, whichever comes first

A mnemonic that can help you remember the order is **P**lease **E**xcuse **M**y **D**ear **A**unt **S**ally.

The following example demonstrates how to use the order of operations to calculate a formula:

In this example, we will review how Excel will calculate a complex formula using the order of operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.

- First, Excel will calculate the amount sold in parentheses:
**(19*1.99)=37.81**White Lily seeds and**(33*1.99)=65.67**Total Lily seeds. - Second, it will divide the White Lily seeds amount by the Total Lily seeds amount:
**37.81/65.67=.5758**. - Last, it will multiply the result by 100 to obtain the value as a percent:
**.5758*100=57.58**.

Based on this complex formula, the result will show that **57.58%** of the total Pete Lily seeds sold were white. You can see from this example, that it is important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately.

In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.

Excel will interpret cell references as either **relative** or **absolute**. By default, cell references are **relative references**. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).

**Absolute references**, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.

Relative references can save you time when you are repeating the same kind of calculation across multiple rows or columns.

In the following example, we are creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.) For convenience, we can copy the formula in B4 into the rest of row 4 and Excel will calculate the value of the bills for those months using relative references.

- Select the first cell where you want to enter the formula (for example, B4).

Enter the formula to calculate the value you want (for example, add B2+B3).

Press **Enter**. The formula will be calculated.

- Select the cell you want to copy (for example, B4) and click on the
**Copy**command from the**Home**tab.

- Select the cells where you want to paste the formula and click on the
**Paste**command from the**Home**tab. (You may also drag the fill handle to fill cells.)

Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.) and the values are calculated.

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an **absolute reference** to keep a row and/or column constant in the formula.

An absolute reference is designated in the formula by the addition of a **dollar sign ($)**. It can precede the column reference, the row reference, or both.

In the below example, we want to calculate the sales tax for a list of products with varying prices. We will use an absolute reference for the sales tax ($B$1) because we do not want it to change as we are copying the formula down the column of varying prices.

- Select the first cell where you want to enter the formula (for example, C4)

- Click on the cell that contains the first
**value**you want in the formula (for example, B4).

- Type the first
**mathematical operator**(for example, the multiplication sign).

- Type the
**dollar sign ($)**and enter the**column letter**of the cell you are making an absolute reference to (for example, B).

Type the **dollar sign ($)** and enter the **row number** of the same cell you are making an absolute reference to (for example, 1).

- Press
**Enter**to calculate the formula.Result in C4

- Select the cell you want to copy (for example, C4) and click on the
**Copy**command from the**Home**tab.

- Select the cells where you want to paste the formula and click on the
**Paste**command from the**Home**tab. (You may also drag the fill handle to fill cells.)

Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.) and your values are calculated.

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** that you can take advantage of. You may be familiar with common functions like **sum**, **average**, **product **or **count**, but there are hundreds of functions in Excel, even for things like formatting text, referencing cells, calculating financial rates, analyzing statistics, and more.

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 since you do not have to write the formula yourself. Excel has hundreds of different functions to assist with your calculations.

In order 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.

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

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

- Select the cell where the answer will appear (F15, for example)
- Type the
**equal sign (=)**and enter the**function name**(SUM, for example).

Enter the cells for the **argument** inside the parenthesis.

Press **Enter** and the result will appear.

Result

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 on the
**Home**tab. - In the
**Editing**group, click on the**AutoSum**drop-down arrow and select the function you desire (Average, for example).

A formula will appear in the selected cell E24. If logically placed, AutoSum will select your cells for you. Otherwise, you will need to click on the cells to choose the argument you desire.

- Press
**Enter**and the result will appear.Result

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

There are hundreds of functions in Excel, but only some will be useful for the kind of data you are working with. There is no need to learn every single function, but you may want to explore some of the different kinds 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 may search and select Excel functions based on categories such as **Financial**, **Logical**, **Text**, **Date & Time**, and more. Click the buttons in the interactive below to learn more.

To Insert a Function from the Function Library:

- Select the cell where the answer will appear (I6, for example)
- Click on the
**Formulas**tab. - From the
**Function Library**group, select the**function category**you desire. In this example, we will choose Date & Time. - Select the desired
**function**from the Date & Time drop-down menu. We will choose the NETWORKDAYS function to count the days between the order date and receive date in our worksheet.

The **Function Arguments** dialog box will appear. Insert the cursor in the **first field** and then enter or select the cell(s) you desire (G6, for example).

Insert the cursor in the **next field** and then enter or select the cell(s) you desire (H6, for example).

Click **OK **and the result will appear. Our results show that it took 5 days to receive the order.

The** Insert Function** command is convenient because it allows you to search for a function by typing a description of what you are 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.

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 will need to find a formula that counts cells with text.

- Select the cell where the answer will appear (A27, for example)
- Click on the
**Formulas**tab and select the**Insert Function**command.

- The
**Insert Function**dialog box will appear.

- Type a
**description**of the function you are searching for and click**Go**. For our example, we will type:*Count cells with text*. (You may also search by selecting a category.)

Review the results to find the function you desire. We will use COUNTA. Then click **OK**.

The **Function Arguments** dialog box will appear. Insert the cursor in the **first field** and then enter or select the cell(s) you desire (A6:A14, for example).

Insert the cursor in the **next field** and then enter or select the cell(s) you desire (A19:A23, for example). (You may continue to add additional arguments if needed.)

Click **OK **and the result will appear. Our results show that 14 Total Supplies were ordered from our log.

Once you have entered information into a spreadsheet, you may want to format it. Formatting your spreadsheet can not only improve the look and feel, but also make it easier to use. In a previous lesson, we discussed many manual formatting options such as bold and italics. In this lesson, you will learn how to **format as a table**, to take advantage of the tools and predefined table styles available in Excel 2010.

Just like regular formatting, tables can help to **organize** your content and make it easier for you locate the information you need. To use tables effectively, you'll need to know how to **format** information as a table, **modify** tables, and apply **table styles**.

- Select the cells you want to format as a table. In this example, an invoice, we will format the cells that contain the column headers and the order details.

Click the **Format as Table** command in the **Styles** group on the Home tab.

A list of predefined **table styles** will appear. Click a table style to select it.

- A dialog box will appear, confirming the
**range**of cells you have selected for your table. The cells will appear selected in the spreadsheet, and the range will appear in the dialog box.

- If necessary,
**change**the range by selecting a new range of cells directly on your spreadsheet.

- If your table has headers, check the box next to
**My table has headers**.

Click **OK**. The data will be formatted as a table in the style that you chose.

Tables include **filtering** by default. You can filter your data at any time using the **drop-down arrows** in the header. To learn more, review our Filtering Data lesson.

To convert a table back into "normal" cells, click the **Convert to Range** command in the **Tools** group. The filters and the Design tab will then disappear, but the cells will retain their data and formatting.

__Modifying Tables__

**To Add Rows or Columns:**

- Select
**any cell**in your table. The**Design**tab will appear on the Ribbon. - From the Design tab, click the
**Resize Table**command.

- Directly on your spreadsheet, select the new
**range**of cells that you want your table to cover. You must select your original table cells as well.

Click **OK**. The new rows and/or columns will be added to your table.

- Select
**any cell**in your table. The**Design**tab will appear. - Locate the
**Table Styles**group. Click the**More**drop-down arrow to see all of the table styles.

- Hover the mouse over the various styles to see a live preview.

- Select the desired style. The table style will appear in your worksheet.

To Change the Table Style Options:

When using an Excel table, you can turn various options **on** or **off** to change its appearance. There are six options: **Header Row**, **Total Row**, **Banded Rows**, **First Column**, **Last Column**, and **Banded Columns**.

- Select
**any cell**in your table. The**Design**tab will appear. - From the
**Design**tab,**check**or**uncheck**the desired options in the**Table Style Options**group.

Depending on the **Table Style** you're using, certain **Table Style Options** may have a different effect. You may need to **experiment** to get the exact look you want.

Imagine you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One of these tools is called **conditional formatting**. With conditional formatting, you can apply formatting to **one or more cells** based on the value of the cell. You can highlight **interesting** or **unusual** cell values, and visualize the data using formatting such as **colors**, **icons**, and **data bars**.

Conditional formatting applies one or more **rules** to any cells that you want. An example of a rule might be **"If the value is greater than 5,000, color the cell yellow."** By applying this rule to the cells in a worksheet, you'll be able to see at a glance which cells are over 5,000. There are also rules that can mark the **top 10 items**, all cells that are **below the average**, cells that are within a certain **date range**, and many more.

- Select the
**cells**that you want to add the formatting to. - In the
**Home**tab, click the**Conditional Formatting**command. A drop-down menu will appear. - Select
**Highlight Cells Rules**or**Top/Bottom Rules**. We will choose Highlight Cells Rules for this example. A menu will appear with several**rules**. - Select the desired rule (Greater Than, for example).

- From the dialog box, enter a
**value**in the space provided, if applicable. In this example, we want to format cells that are greater than $5,000, so we'll enter 5000 as our value. If you want, you can enter a**cell reference**instead of a number.

- Select a formatting style from the drop-down menu.

The formatting will be applied to the selected cells.

If you want, you can apply more than one rule to your cells.

Excel has a number of **presets** that you can use to quickly apply conditional formatting to your cells. They are grouped into three categories:

**Data Bars**are horizontal bars added to each cell, much like a**bar graph**.

**Color Scales** change the color of each cell based on its value. Each color scale uses a **two or three color gradient**. For example, in the **Green - Yellow - Red** color scale, the **highest** values are green, **average** values are yellow, and the **lowest** values are red.

**Icon Sets** add a specific icon to each cell based on its value.

- Select the cells you want to add the formatting to.
- In the
**Home**tab, click the**Conditional Formatting**command. A drop-down menu will appear. - Select
**Data Bars**,**Color Scales**or**Icon Sets**(Data Bars, for example). Then, select the desired preset.

The conditional formatting will be applied to the selected cells.

- Select the cells that have conditional formatting.
- In the
**Home**tab, click the**Conditional Formatting**command. A drop-down menu will appear. - Select
**Clear Rules**. - A menu will appear. You can choose to clear rules from the
**Selected Cells**,**Entire Sheet**,**This Table**, or**This PivotTable**. In this example, we will clear rules from the entire sheet.

You can edit or delete **individual** rules by clicking on the **Conditional Formatting** command and selecting **Manage Rules**. This is especially useful if you have applied **multiple rules** to the cells.

To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

Note: Date is in US Format. Months first, Days second. This type of format depends on your windows regional settings.

Dates are stored as numbers in Excel and count the number of days since January 0, 1900. Times are handled internally as numbers between 0 and 1. To clearly see this, change the number format of cell A1, B1 and C1 to General.

Apparently, 41083 days after January 0, 1900 is the same as June 23, 2012. 6:00 is represented as 0.25 (quarter through the day).

To get the year of a date, use the YEAR function.

Note: use the MONTH and DAY function to get the month and day of a date.

1. To add a number of days to a date, use the following simple formula.

2. To add a number of years, months and/or days, use the DATE function.

ote: the DATE function accepts three arguments: year, month and day. Excel knows that 6 + 2 = 8 = August has 31 days and rolls over to the next month (23 August + 9 days = 1 September).

To get the current **date** and **time,** use the NOW function.

Note: use the TODAY function to get the current date only. Use NOW()-TODAY() to get the current time only (and apply a Time format).

To return the hour, use the HOUR function.

Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.

LEFT | Extracts one or more characters from the left side of a text string |

RIGHT | Extracts one or more characters from the right side of a text string |

MID | Extracts characters from the middle of a text string; you specify which character position to start from and how many characters to include |

CONCATENATE | Assembles two or more text strings into one |

REPLACE | Replaces part of a text string with other text |

LOWER | Converts a text string to all lowercase |

UPPER | Converts a text string to all uppercase |

PROPER | Converts a text string to proper case |

LEN | Returns a text string’s length (number of characters) |

**Left **

To extract the leftmost characters from a string, use the LEFT function.

To extract the rightmost characters from a string, use the RIGHT function.

To extract a substring, starting in the middle of a string, use the MID function.

To get the length of a string, use the LEN function.

The CONCATENATE Function joins several text strings into one text string. You can also use the Ampersand (&) calculation operator instead of the CONCATENATE Function.

The CONCATENATE Function has one required argument and up to 255 arguments, all separated by commas. The arguments can be text strings, numbers, or single-cell references.

In my spreadsheet example, cell B2 has the Month and Day (Jun 30), while cell C2 has the Year (2010). The following formula will combine these two in cell D2:

=CONCATENATE(B2,”, “,C2)

Notice that “, ” is the second argument, which is a comma followed by the space character, all in quotes. The result is: Jun 30, 2010. You get a better sense of the arguments by looking at the Function Arguments dialog box.

While the CONCATENATE Function is all nice and proper and well documented, the Ampersand (&) operator is much easier to use in practice.

The same formula in cell E2 using the Ampersand operator:

=B2 & “, ” & C2

The Ampersand operator separates the different arguments to be combined in a text string. Much easier to use than typing out CONCATENATE, don’t you think?

In any event, *both the CONCATENATE Function and Ampersand Operator return a Text String*. Excel will recognize this text string as a Date, but there are problems associated with Dates entered as Text. You can’t change it’s numeric formatting, to name just one.

Statistical functions

These functions calculate averages, minima, maxima, and so on. The required syntax is:

- =min(range)
- =max(range)
- =average(range)
- =count(range)
- =counta(range)

The count function counts cells containing numbers, and the counta counts alphanumeric.

MODE | Mode of a set of numbers |

MEDIAN | Median of a set of numbers |

To calculate the average of a range of cells, use the AVERAGE function.

To find the median (or middle number), use the MEDIAN function.

To find the most frequently occurring value, use the MODE function.

To find the minimum value, use the MIN function.

To find the maximum value, use the MAX function.

To find the third largest number, use the following LARGE function.

To find the second smallest number, use the following SMALL function.

The **IF function** checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

1. Select cell C2 and enter the following function.

The IF function returns Correct because the value in cell A1 is higher than 10.

The **AND Function** returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

1. Select cell D2 and enter the following formula.

The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.

The **OR function** returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

1. Select cell E2 and enter the following formula.

The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.

General note: the AND and OR function can check up to 255 conditions.

The **IF function** can be **nested**, when you have multiple conditions to meet. The FALSE value is being replaced by another If function to make a further test. For example, look at the formula below.

1a. If cell A1 equals 1, the function returns Bad.

1b. If cell A1 equals 2, the function returns Good.

1c. If cell A1 equals 3, the function returns Excellent.

1d. If cell A1 equals another value, the function returns No Valid Score.

Here's another example.

2a. If cell A1 is less or equal to 10, the function returns 350.

2b. If cell A1 is greater than 10 and less or equal to 20, the function returns 700.

2c. If cell A1 is greater than 20 and less or equal to 30, the function returns 1400.

2d. If cell A1 is greater than 30, the function returns 2000.

Note: to slightly change the boundaries, you might want to use "<" instead of "<=" in your own formula.

It is possible to nest multiple **IF function**s within one Excel formula. You can nest up to 7 **IF** functions to create a complex IF THEN ELSE statement.

The syntax for the nesting the **IF function** is:

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

This would be equivalent to the following IF THEN ELSE statement:

IF condition1 THEN value_if_true1 THEN value_if_true2 ELSE value_if_false2 END IF

This syntax example demonstrates how to nest two **IF function**s. You can nest up to 7 **IF function**s.

*condition* is the value that you want to test.

*value_if_true* is the value that is returned if *condition* evaluates to TRUE.

*value_if_false* is the value that is return if *condition* evaluates to FALSE.

SUM | Calculates the sum of a group of values |

AVERAGE | Calculates the mean of a group of values |

COUNT | Counts the number of cells in a range that contains numbers |

INT | Removes the decimal portion of a number, leaving just the integer portion |

ROUND | Rounds a number to a specified number of decimal places or digit positions |

IF | Tests for a true or false condition and then returns one value or another |

NOW | Returns the system date and time |

TODAY | Returns the system date, without the time |

SUMIF | Calculates a sum from a group of values, but just of values that are included because a condition is met |

COUNTIF | Counts the number of cells in a range that match a criteria |

**Count **

To count the number of cells that contain numbers, use the COUNT function.

To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

To sum a range of cells, use the SUM function.

To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).

To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

1. The COUNTBLANK function counts the number of blank cells.

The COUNTA function counts the number of nonblank cells. COUNTA stands for count all.

Basically, VLOOKUP lets you **search for specific information** in your spreadsheet. For example, if you have a list of products with prices, you could search for the price of a specific item.

We’re going to use VLOOKUP to find the price of the **Photo frame**. You can probably already see that the price is $9.99, but that’s because this is a simple example. Once you learn how to use VLOOKUP, you’ll be able to use it with larger, more complex spreadsheets, and that’s when it will become truly useful.

We’ll add our formula to cell **E2**, but you can add it to any blank cell. As with any formula, you’ll start with an equal sign (=). Then, type the formula name. Our arguments will need to be in parentheses, so type an open parenthesis. So far, it should look like this:

**=VLOOKUP(**

Now, we’ll add our arguments. The arguments will tell VLOOKUP what to search for and where to search.

The first argument is the **name of the item** you are searching for, which in this case is **Photo frame**. Since the argument is text, we’ll need to put it in double quotes:

**=VLOOKUP(“Photo frame”**

The second argument is the **cell range that contains the data**. In this example, our data is in A2:B16. As with any function, you’ll need to use a comma to separate each argument:

**=VLOOKUP(“Photo frame”, A2:B16**

Note: It’s important to know that VLOOKUP will **always search the first column** in this range. In this example, it will search column A for “Photo frame”. In some cases, you may need to move the columns around so that the first column contains the correct data.

The third argument is the **column index number**. It’s simpler than it sounds: The first column in the range is 1, the second column is 2, etc. In this case, we are trying to find the **price of the item**, and the prices are contained in the **second column**. That means our third argument will be **2**:

**=VLOOKUP(“Photo frame”, A2:B16, 2**

The fourth argument tells VLOOKUP whether to look for **approximate matches**, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Since we’re only looking for exact matches, the fourth argument should be **FALSE**. This is our last argument, so go ahead and close the parentheses:

**=VLOOKUP(“Photo frame”, A2:B16, 2, FALSE)**

And that’s it! When you press enter, it should give you the answer, which is **9.99**.

Let’s take a look at how this formula works. It first **searches vertically** down the first column (VLOOKUP is short for “vertical lookup”). When it finds “Photo frame”, it **moves to the second column** to find the price.

If we want to find the price of a different item, we can just change the first argument:

**=VLOOKUP(“T-shirt”, A2:B16, 2, FALSE)**

or:

**=VLOOKUP(“Gift basket”, A2:B16, 2, FALSE)**

OK, are you ready for a slightly more complicated example? Let’s say we have a third column that has the category for each item. This time, instead of finding the price, we’ll find the category.

To find the category, we’ll need to **change the second and third arguments** in our formula. First, we’ll change the range to **A2:C16** so that it includes the third column. Next, we’ll change the column index number to **3**, since our categories are in the third column:

**=VLOOKUP(“Gift basket”, A2:C16, 3, FALSE)**

When you press Enter, you’ll see that the Gift basket is in the **Gifts** category.

If you’d like more practice, see if you can find the following:

• The price of the coffee mug

• The category of the landscape painting

• The price of the serving bowl

• The category of the scarf

Now you know the basics of using VLOOKUP. Although advanced users sometimes use VLOOKUP in different ways, you can do a lot with the techniques that we’ve covered. For example, if you have a contact list, you could search for someone’s name to find their phone number. If your contact list has columns for the email address or company name, you could search for those by simply changing the second and third arguments, as we did in our example. The possibilities are endless!

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

__Charts__

Excel workbooks can contain **a lot of data**, and that 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 the data is represented as a** chart**. Excel has many different types of charts, so you can choose one that most effectively represents the data.

**Types of Charts**

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

**Identifying the Parts of a Chart**

Click the buttons in the interactive below to learn about the different parts of a chart.

__To Create a Chart:__

- Select the
**cells**that you want to chart, including the**column titles**and the**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.

**To Change the Chart Type:**

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

Select the desired **chart type** and click **OK**.

**To Switch Row and Column Data:**

Sometimes when you create a chart, the data may not be grouped the way you want it to be. In the **clustered column chart** below, the Book Sales statistics are grouped **by Fiction/Non-Fiction**, with a column for each year. However, you can also **switch the row and column data** so that 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 then readjust.

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

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

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

- 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 (i.e., choose an existing worksheet, or select New Sheet and name it).

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