Common Scenarios for Using Excel Include

1453

Excel Notes

Contents

What is Excel

Common scenarios for using Excel include:

Terms

What is a function?

Ways to add a Basic Function

Common Functions

Simple addition and subtraction

Add the values in a range by using a function

Notes

Subtotal

Cell Referencing

Absolute cell referencing –

Relative cell reference-

Mixed cell reference-

Including values from other worksheets or workbooks in a formula

Paste Special

SUMIF

Syntax

Remarks

Example

COUNTIF

Syntax

Remarks

Examples

IF function

Description

Syntax

Remarks

Examples

Common Problems

Use nested functions in a formula

Examples

VLOOKUP

Syntax

Remarks

PivotTable

What is a PivotTable report

Ways to work with a PivotTable report

Goal Seek

Trace Precedents

Watch Window

Add cells to the Watch Window

Remove cells from the Watch Window

What is Excel

•Spreadsheet program that allows users to organize data, complete calculations, make decisions, and graph data.

•4 Major Parts

Work Sheets
Lists
Charts
Web Support

Common scenarios for using Excel include:

•Accounting You can use the powerful calculation features of Excel in many financial accounting statements—for example, a cash flow statement, income statement, or profit and loss statement.

•Budgeting Whether your needs are personal or business related, you can create any type of budget in Excel—for example, a marketing budget plan, an event budget, or a retirement budget.

•Billing and sales Excel is also useful for managing billing and sales data, and you can easily create the forms that you need—for example, sales invoices, packing slips, or purchase orders.

•Reporting You can create various types of reports in Excel that reflect your data analysis or summarize your data—for example, reports that measure project performance, show variance between projected and actual results, or reports that you can use to forecast data.

•Planning Excel is a great tool for creating professional plans or useful planners—for example, a weekly class plan, a marketing research plan, a year-end tax plan, or planners that help you organize weekly meals, parties, or vacations.

•Tracking You can use Excel to keep track of data in a time sheet or list—for example, a time sheet for tracking work, or an inventory list that keeps track of equipment.

•Using calendars Because of its grid-like workspace, Excel lends itself well to creating any type of calendar—for example, an academic calendar to keep track of activities during the school year, or a fiscal year calendar to track business events and milestones.

Terms

Workbook- the Excel file that stores your information.

Limited by available memory and system resources

Sheet- Each workbook may contain numerous worksheets.
Cell -17,179,869,184 cells per sheet.
Column- 16,384 columns moving from left to right.
Row- 1,048,576 rowsmoving from top to bottom
Grid Lines- Gridlines are lines on a chart that can make critical data comparisons easier.
Cell Reference- A cell reference, or cell address, identifies a particular cell
Active Cell- current cell selected.
Range - a block of cells that can be selected, manipulated, named, or formatted as a group.

What is a function?

function:

A prewritten formula that takes a value or values, performs an operation, and returns a value or values.
Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations

Ways to add a Basic Function

Type the formula in manually into a cell
Use the Inset Function button
Use the AutoSum button
Point and click method

Common Functions

•Standard format: =functionname(parameters)

•=Sum(cell1:cell2) adds a range of cells

•=MIN(cell1:cell2) finds the minimum cell value in a range

•=MAX(cell1:cell2) finds the maximum cell value in a range

•=AVERAGE(cell1:cell2) finds the average of a cell range

•=COUNT(cell1:cell2) counts the amount of items in a cell range

Excel

Simple addition and subtraction

Add the values in a cell by using a simple formula

If you just need a quick result, you can use Excel as a mini calculator. Do this by using the plus sign (+) arithmetic operator.

Formula / Description / Result
=5+10 / Uses the + (plus sign) operator to add two or more values. / 15
=A2+B2 / Adds the values in two or more cells. In this case, assume A2 = 5 and B2 = 10. / 15
=A2+B2+20 / Adds the values in two cells to a number that you enter directly in the formula. In this case, assume A2 = 5 and B2 = 10. / 35

Subtract the values in a cell by using a simple formula

Do this by using the minus sign (-) arithmetic operator. For example, the formula =12-9 displays a result of 3.

Add the values in a column or row by using a button

You can use AutoSum to quickly sum a range of numbers in a column or row. Click an empty cell below a column of numbers or to the right of a row of numbers, and then click AutoSum. Excel selects what it determines to be the most likely range of data. Click AutoSum again to accept the range that Excel selects, or select your own range and then click AutoSum.

Add the values in a range by using a function

The SUM function is useful when you want to add or subtract values from different ranges or combine number values with ranges of numbers. Use the SUM function to add all the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that you specify within the opening and closing parentheses. Each argument can be a range, a cell reference, or a positive or negative numeric value.

To enter a simple formula, type =SUM in a cell, followed by an opening parenthesis. Next, type one or more numbers, cell references, or cell ranges, separated by commas. Then, type a closing parenthesis and press ENTER to display the result. You can also use your mouse to select cells that contain data that you want to sum.

1
2
3
/ A
Attendance
4823
12335

For example, using the data in the preceding table, all of the following formulas use the SUM function to return the same value (17158):

=SUM(4823,12335)

=SUM(A2,A3)

=SUM(A2:A3)

=SUM(A2,12335)

The following figure shows the formula that uses the SUM function to add the value of cell A2 and 12335. Below the formula, a ScreenTip provides guidance for using the SUM function.

Notes

There is no SUBTRACT function in Excel. To subtract values by using a function, use the negative values with the SUM function. For example, the formula =SUM(30,A3,-15,-B6) adds 30 to the value in cell A3, subtracts 15, and then subtracts the value in cell B6.

You can include up to 255 numeric values or cell or range references, in any combination, as arguments in the SUM function.

The following illustration shows an outline with subtotals, grouped by region, and a grand total.

Subtotal

Insert subtotals in a list of data in a worksheet

You can automatically calculate subtotals and grand totals in a list for a column by using the Subtotal command.

Subtotalsare calculated with a summary function, such as Sum or Average, by using the SUBTOTAL function. You can display more than one type of summary function for each column.

Grand totals are derived from detail data, not from the values in the subtotals. For example, if you use the Average summary function, the grand total row displays an average of all of the detail rows in the list, not an average of the values in the subtotal rows.

If the workbook is set to automatically calculate formulas, the Subtotal command recalculates subtotal and grand total values automatically as you edit the detail data. The Subtotal command also outlines the list so that you can display and hide the detail rows for each subtotal.

Insert subtotals

Insert one level of subtotals

You can insert one level of subtotals for a group of data as shown in the following example.

1. At each change in the Sport column…

2. …subtotal the Sales column.

To sort the column that contains the data you want to group by, select that column, and then on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A.

On the Data tab, in the Outline group, click Subtotal.

The Subtotal dialog box is displayed.

In the At each change in box, click the column to subtotal. For example, using the example above, you would select Sport.

In the Use function box, click the summary function that you want to use to calculate the subtotals. For example, using the example above, you would select Sum.

In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. For example, using the example above, you would select Sales.

If you want an automatic page break following each subtotal, select the Page break between groups check box.

To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. For example, using the example above, you would clear the check box.

Optionally, you can use the Subtotals command again by repeating steps one through seven to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.

Cell Referencing

Absolute cell referencing – An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

Relative cell reference-A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

Mixed cell reference- A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1

Including values from other worksheets or workbooks in a formula

You can add or subtract cells or ranges of data from other worksheets or workbooks in a formula by including a reference to them. To refer to a cell or range in another worksheet or workbook, use instructions in the following table.

To refer to: / Enter this / Examples
A cell or range in another worksheet in the same workbook / The name of the worksheet followed by an exclamation point, followed by the cell reference or range name. / Sheet2!B2:B4
Sheet3!SalesFigures
A cell or range in another workbook that is currently open / The file name of the workbook in brackets ([]) and the name of the worksheet followed by an exclamation point, followed by the cell reference or range name. / [MyWorkbook.xlsx]Sheet1!A7
A cell or range in another workbook that is not open / The full path and file name of the workbook in brackets ([]) and the name of the worksheet followed by an exclamation point, followed by the cell reference or range name. If the full path contains any space characters, surround the start of the path and the end of the worksheet name with single quotation marks (see the example). / ['C:\My Documents\[MyWorkbook.xlsx]Sheet1'!A2:A5

Paste Special

•Used to paste data, formula or reference to other datasheets.

•When you choose paste, instead select paste special.

•You can choose exactly what information you want to paste

SUMIF

Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want evaluated by criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".

Sum_rangeare the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

Remarks

Sum_range does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:

If range is / And sum_range is / Then the actual cells are
A1:A5 / B1:B5 / B1:B5
A1:A5 / B1:B3 / B1:B5
A1:B4 / C1:D4 / C1:D4
A1:B4 / C1:C2 / C1:D4

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

NoteDo not select the row or column headers.

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1
2
3
4
5
/ A / B
Property Value / Commission
100,000 / 7,000
200,000 / 14,000
300,000 / 21,000
400,000 / 28,000
Formula / Description (Result)
=SUMIF(A2:A5,">160000",B2:B5) / Sum of the commissions for property values over 160000 (63,000)
=SUMIF(A2:A5,">160000") / Sum of the property values over 160000 (900,000)
=SUMIF(A2:A5,"=300000",B2:B3) / Sum of the commissions for property values over 160000 (21,000)

COUNTIF

Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

Rangeis the range of cells from which you want to count cells.

Criteriais the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Remarks

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Examples

1: Common COUNTIF formulas

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
/ A / B
Data / Data
apples / 32
oranges / 54
peaches / 75
apples / 86
Formula / Description (result)
=COUNTIF(A2:A5,"apples") / Number of cells with apples in the first column above (2)
=COUNTIF(A2:A5,A4) / Number of cells with peaches in the first column above (1)
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) / Number of cells with oranges or apples in the first column above (3)
=COUNTIF(B2:B5,">55") / Number of cells with a value greater than 55 in the second column above (2)
=COUNTIF(B2:B5,">"&B4) / Number of cells with a value not equal to 75 in the second column above (2)
=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") / Number of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above (3)

Example 2: COUNTIF formulas using wildcard characters and handling blank values

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
6
7
/ A / B
Data / Data
apples / Yes
oranges / NO
peaches / No
apples / YeS
Formula / Description (result)
=COUNTIF(A2:A7,"*es") / Number of cells ending with the letters "es" in the first column above (4)
=COUNTIF(A2:A7,"?????es") / Number of cells ending with the letters "les" and having exactly 7 letters in the first column above (2)
=COUNTIF(A2:A7,"*") / Number of cells containing text in the first column above (4)
=COUNTIF(A2:A7,">"&"*") / Number of cells not containing text in the first column above (2)
=COUNTIF(B2:B7,"No") / ROWS(B2:B7) / The average number of No votes including blank cells in the second column above formatted as a percentage with no decimal places (33%)
=COUNTIF(B2:B7,"Yes") / (ROWS(B2:B7) -COUNTIF(B2:B7, ">"&"*")) / The average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%)

IF function