Introduction

The power of a spreadsheet comes from its ability to generate data using formulas and functions that calculate values. Formulas become even more meaningful when the results are based on data from your worksheet. For example, when you want to know the total of the expenses in your budget worksheet, you use a function in a formula to add the expenses and display the total.

Data is often easier to understand when it’s presented in a chart. You can use charts to demonstrate a trend, show an area of growth or decline, and support your overall analysis. Charts are often used in conjunction with data to draw a conclusion because the numbers alone don’t provide the picture needed to understand a situation fully.

Excel tables are another way to analyze related data. Tables include commands for searching, sorting, and filtering data.

Using Formulas

A formula is a mathematical statement that calculates a value. To create a formula in your worksheet, you must begin with an equals sign (=). For example, if you type “=25+2” in a cell, it will display “27”.

The following symbols represent operators:

Exponentiation ^
Multiplication *
Division /
Addition +
Subtraction -

(Transcript downloaded)

The exponentiation symbol raises a number to a power, as in 32. To perform this calculation in Excel, you use the formula =3^2 to display 9.

Formulas are evaluated using the mathematical order of operations, which states that exponentiation is calculated first, followed by multiplication and division, and then addition and subtraction. The following formulas demonstrate the order of operations:

The exponentiation symbol raises a number to a power, as in 32. To perform this calculation in Excel, you use the formula =3^2 to display 9.

Formulas are evaluated using the mathematical order of operations, which states that exponentiation is calculated first, followed by multiplication and division, and then addition and subtraction. The following formulas demonstrate the order of operations:

Formula Value =3*4+2 14 =10*2^2 40 =8+6/3-1 9

To change the order of operations, you can put parentheses around calculations to be performed first. To change the order of operations, you can put parentheses around calculations to be performed first.

Formula Value =3*(4+2) 18 =(10*2)^2 400 =(8+6)/(3-1) 7

Writing Formulas

(Transcript downloaded)

Displaying Formulas

When you enter a formula into a cell, the result of the calculation is displayed. To review or edit the actual formula, click the cell and use the Formula bar to access the actual cell contents. This can be inconvenient when reviewing an entire worksheet. To see all the formulas in a spreadsheet at once, click Show Formulas on the Formulas tab or press Ctrl+`.

When formulas are displayed, column widths adjust automatically to display the entire formula. Selecting Show Formulas again reverts it back to the original formatting.

Modifying Values in Formulas

To modify an existing formula, you can either double-click the cell to edit or select the cell containing the formula and then click on the Formula bar. Make the change and press Enter. The video uses an example spreadsheet where you need to change the formula used for the tax calculation. In the example, you can double-click cell D5 so that the formula may be edited. Otherwise, click the cell to edit the formula in the Formula bar.

Error Checking

Excel checks formulas when you enter them. When a formula can’t be calculated, an error is displayed and a green triangle is added to the upper-left corner of the cell. For example, the formula =4/0 displays #DIV/0!. This displays because numbers can’t be divided by 0. You can select the cell with the error and click Error Checking to display a description of the error and a list of options. You can click Show Calculation Steps to display a dialog box that examines the formula.

If errors are displayed in your worksheet, but the Error Checking menu isn’t available, you can click Error Checking on the Formulas tab to check all the cells in your worksheet.

Using Cell References

When you want to make calculations based on the data in your spreadsheet, you usually include a cell reference in your formula rather than the actual number. For example, in the image, cell C1 displays a value based on numbers in cells A1 and B1.

By using cell references in formulas, your spreadsheet becomes easier to update. If you change a value in a cell referenced in a formula, the formula automatically recalculates.

Although you can type the cell name in a formula to create the cell reference, you can avoid typing errors by selecting the cell instead. To do this, you type a formula up to the point where the cell name appears and then click on the cell to be used in the calculation. You can also use the arrow key to select the cell.

You’ll almost always want to use cell references in worksheet formulas rather than actual numbers. It’s much easier to have the calculation results change automatically when values are entered than to update each formula with the correct numbers manually. Whether your values are sales figures, age ranges, horsepower, voltage, or grades, your data will likely change over time, and formulas with cell references make sure your spreadsheet stays up to date and accurate.

You can’t create a formula in a cell that you want to reference because this will cause a circular reference error. The formula can’t be calculated until the cell has a value, but the cell can’t have a value until the formula is entered.

Displaying Formulas with Cell References

When you click Show Formulas on the Formulas tab, you can select a cell with a formula to outline the cells referenced in the formula. If you want to examine just one formula, double-click the cell to display the formula and outline referenced cells.

Copying Formulas

You’ll often want to copy a formula you’ve created to the next cell in a row or column. For this, you can use Copy and Paste, but when copying to adjacent cells, you can also use the Fill Handle, a point in the lower-right corner of the active cell that you can drag to include other cells.

To do this, select the cell with the formula you want to copy, and then drag its Fill Handle to copy the formula to a range of cells.

When a formula is copied, cell references automatically change relative to the new row or column. For example, in the figures, the formula in cell C2 is =B2*A2. When copied to the cells below, it changes to =B3*A3 and =B4*A4, respectively. When cell references can change in this way, they’re called relative cell references.

Relative references change when a formula is moved or copied from one cell to another. Relative references are a powerful tool in applying the same calculation to hundreds of rows and columns of data.

Absolute and Mixed Cell References

If you want a cell reference in a formula to remain the same when you copy it to another cell, then you need to make it an absolute cell reference, which is a cell reference that doesn’t change when a formula is copied to other cells. For example, the formula in the figure refers to a tax rate in cell B2.

The cell reference has dollar signs in front of the column letter and row number to indicate that neither should change when you copy the formula to another cell. You create an absolute reference by pressing F4 after you type or click on the cell to be referenced.

In some cases, you may need a mixed cell reference in your formula, where either the column or row doesn’t change when copied. In this case, you can press F4 until the appropriate reference appears.

Excel references column headings and table names instead of cell references when formulas are inserted into a table. Cell names and range names assigned to a given cell or range make it easier to understand what calculations are being performed in a formula and to reuse the references as necessary.

Suppose you want to deposit $500 in the bank and then calculate how much money you’ll have at the end of one year if interest is compounded semiannually at 8%. To find the answer, you’ll first need to find the interest earned during the first compounding period. Then, you’ll add this to the original principal. Next, find the interest on this new principal for the second compounding period. Finally, add this interest to the new principal to obtain the final balance. If you want to calculate manually, you would use the interest formula of principal multiplied by rate, multiplied by time. Then, you would substitute the values and multiply them to solve. You would need to calculate this again to obtain the final balance. The final balance is $540.80, which is the future value of the $500 deposit.

If the interest were compounded quarterly, there would be four periods in a year. You would calculate the interest the same way, except that you would have to repeat the calculation four times for each year. For monthly compounding, you would repeat the calculations 12 times for each year. Although this process isn’t difficult, it’s tedious and time-consuming. Fortunately, with Excel, you can create formulas that make calculating compound interest much easier. Begin by creating a compound interest calculator. The initial investment, found in B3, is $500; the annual interest rate, found in B4, is 8%; the compounding period, found in B4, is semiannual (2); and the number of years, found in B6, is 1. The formula to calculate compound interest is the initial investment (or present value) multiplied by (1 plus the interest rate, which is the annual interest rate divided by the compounding periods per year) to the power of the number of periods (the number of years multiplied by compounding periods per year). Once you’ve written the formula, Excel automatically calculates the compound interest. As you know, the answer is $540.80.

Remember, if you want a cell reference in a formula to remain the same when you copy it to another cell, then you’ll need to make it an absolute cell reference. An absolute cell reference doesn’t change if the formula is copied to other cells. In the video example, the cell reference for B2 has dollar signs in front of the column letter and row number to indicate that neither should change when you copy the formula to another cell. You can create an absolute cell reference by pressing F4 after you type or click on the cell to be referenced.

Using Functions in Formulas

You might be thinking about all the work needed to create something as simple as a formula that sums the values in five cells. From what you’ve learned so far, the formula might look like =A1+A2+A3+A4+A5. That’s a long formula for a simple calculation. But Excel is a spreadsheet application with many features for analyzing data, so it includes built-in functions for use in formulas.

A function is a named set of operations that takes one or more values and produces a single output. For example, the SUM function accepts a cell range and returns the sum of the values in those cells, as in =SUM(A1:A5). With this simple formula, you can calculate the sum of the values in cells A1 through A5.

Functions are always used as part of a formula and usually require data, called arguments, inside parentheses after the function name. The SUM function, for example, requires a cell range or a set of cell names separated by commas. When you need to specify a cell range in a function, use a colon between the first cell and the last.

To add a cell range to a function, type the function up to the point where the cell range is needed, and then drag from the first cell in the range to the last before typing the closing parenthesis. To help you, Excel displays a colored outline showing the range.

If you want to edit the range after you’ve entered the formula, click the cell with the formula, and then drag the cell outline or delete the range and select a new one.

Commonly Used Functions

Commonly used Excel functions include the following:

SUM adds the values in a range and returns the sum.
AVERAGE adds the values in a range and then divides the total by the number of values.
COUNT returns the number of cells in a range that contain values.
COUNTA counts the number of cells that aren’t empty in a range of text or in logical or error values.
COUNTBLANK is a statistical function on the Formulas ribbon on the Functional Library group that will count the number of empty cells in a range of cells.
MAX returns the maximum value in a range of cells.
MIN returns the minimum value in a range of cells.

In many cases, you’ll need to create the formula with one of these functions to make the calculation you need. However, the Totals tab in the Quick Analysis gallery is another option for creating totals.

The IF Function

Another commonly used function is IF, which displays a value based on a comparison. For example, if you want to display “STUDY” when the grade average in cell G7 is below 85 or “GREAT” otherwise, use =IF(G7<85, “STUDY”, “GREAT”).

The IF function takes the general form IF(comparison, value if true, value if false) and can use the following logical operators in the comparison. If the logical test entered as an argument is true, the IF function will return a value. If the logical test isn’t true, it will return a different value. For example, if the test is true, it might return a 1, and if the test is false, it might return a 0.

Equal to | =

Less than | <

Greater than | >

Less than or equal to | <=

Greater than or equal to | >=

Not equal to | <>

FINANCIAL SPREADSHEETS

Excel has hundreds of built-in functions, many of which are used in financial calculations for mortgages, loan payments, depreciation, accrued interest, and other complex calculations. You can search for these functions in Excel’s Help menu or the Show Me box to see what each does.

Sorting Data

Your spreadsheet should present data in an organized way. One way to make sure information is presented in a logical format is to order rows of data by sorting, which means organizing by using a set parameter. You can sort in ascending order, from low to high, or descending order, from high to low. If the data you’re basing the sort on is a date or time, then an ascending sort puts rows into chronological order.

Sort your worksheet by first selecting the rows to be sorted. Drag from the first row number to the last in the range to be sorted, and then on the Home tab, select Sort & Filter for options.

Use From Smallest to Largest for ascending order or From Largest to Smallest for descending order. These commands automatically base the sort on the data in column A. Use Custom Sort to display a dialog box where you can select a different column to base the sort on.

Charts

A chart, sometimes called a graph, visually represents a range of data. A pie chart displays data as a percentage of a whole.

Chart Terminology

A chart is a graphic with many elements. For example, pie charts include the following:

A chart title, which corresponds to the title of the data series column
Slices, or chunks of the chart that are sized relative to the values in the data series
A legend labeling the components of the chart based on the category labels in the first column of data
A chart area that provides the boundaries of the graphic
A Chart Elements button with quick access to elements that can be added or removed
A Chart Styles button for selecting a style and color scheme for the chart
A Chart Filters button for adding and removing categories, series title, and category titles, and for editing the data source

A pie chart has only one data series, but other chart types can include multiple columns of values.

Column charts are useful for comparing differences in value by using vertical bars sized relative to the values in the data series.
Bar charts are useful for comparing differences in value by using horizontal bars sized relative to the values in the data series.
Line charts connect data values with a line to show how data changes over time.
Area charts are used to demonstrate the total value across a trend.

With the rise of the internet as the main information-gathering tool, people have become accustomed to getting information in smaller and smaller chunks. Often, a social news website will publish an article with just a graphic and one paragraph explaining that graphic. Charts in presentations serve the same purpose as these news graphics. They’re a way to present the information in a condensed and easy-to-understand format without needing to dig through the details of the data. In your career, there may be times when your supervisor asks for a report, and he or she will likely be expecting a chart to summarize the information. If you simply hand in a bunch of numbers, you might spend hours breaking them down when a chart would have done the same in minutes.

Creating and Customizing a Chart

The easiest way to create a chart is to first select the data you want in your chart. Your selection should include column titles and row labels so that Excel can automatically include this information. Next, select Recommended Charts on the Insert tab to display a dialog box.

Navigate through recommended charts on the left of the dialog box to see a preview, or click the All Charts tab and click charts there to see additional previews. After finding a chart that’s close to what you want, select the preview and then click OK. The chart is placed on the worksheet, where you can customize it.

Because the chart is a graphic, there are many ways to customize it. For example, in a pie chart, you can drag a slice away from the others to create an exploding pie chart. To make any changes, you must first select a chart. A selected chart displays handles and the Chart Elements, Chart Styles, and Chart Filters buttons. The Ribbon displays the Chart Tools Design and Chart Tools Format tabs. These tabs contain numerous options for further formatting your chart.

Moving, Sizing, Deleting, and Changing a Chart

Because a chart is a graphic, you can move it to another area of the worksheet by dragging. In some cases, your workbook will be better organized if you place your chart on a separate worksheet. To move a chart to an individual worksheet, select the chart and then click Move Chart on the Chart Tools Design tab. In the displayed dialog box, click New Sheet and then OK. You can access the chart by clicking the appropriate sheet tab below the worksheet.

To size a selected chart, drag a handle. Pressing the Delete key removes the chart from the worksheet. If the chart is on a different sheet, right-click the tab for that sheet and click Delete to remove the chart and any data on that sheet.

To change the actual chart type, select the chart and click Change Chart Type on the Chart Tools Design tab to display a dialog box for selecting a new chart.

Quick Analysis Charts and Sparklines

Another option for creating charts is to use the Quick Analysis button, which is displayed when you select a range of numeric data. The Charts tab has several options, or you can click More Charts to display the Insert Charts dialog box.

Sparklines are tiny charts inside single cells. They’re useful for showing a data trend.

In the Sparklines tab of the Quick Analysis button, you can point to a Sparkline to see a preview and then click an option to insert the chart into the selected cell.

You’ll probably want to change the cell width to better accommodate the Sparkline chart. Sparklines can be customized with commands on the Sparklines Tools Design tab, which is added to the Ribbon by selecting a cell with a Sparkline.

When you want to remove a Sparkline chart, right-click the cell with the chart and click Delete or click Clear > Clear All on the Home tab.

Printing a Chart

You may need to change the worksheet margins or drag a chart below data to print it on a page. To determine how a chart will look, choose Print on the File tab for a preview.

Key Points

Add formulas to your worksheet to make calculations. A formula must begin with “=”.
To see your formulas in cells instead of the calculated value, click Show Formulas on the Formulas tab.
When a formula can’t be calculated, you can display a description of the error with the Error Checking menu.
Use cell references instead of values in your formulas to calculate data from your worksheet.
Common functions include SUM, AVERAGE, COUNT, MIN, MAX, and IF.
The IF function displays a value based on a comparison.
To organize the rows in your spreadsheet based on the value in a column, click Sort & Filter on the Home tab and then select a sort option.
A chart is a graphic that contains many elements.
A selected chart displays the Chart Elements, Chart Styles, and Chart Filters buttons for editing, and the Chart Tools Design tab and Chart Tools Format tab on the Ribbon.
Sparklines can be created in individual cells with commands in the Quick Analysis button.
To change your chart to a different type, click Change Chart Type on the Chart Tools Design tab.
If you want to see how a chart will print, click Print on the File tab.

Próximo

Anterior

Formulas, Functions, and Charts

Descrição