What is Excel - University of Winnipeg



1453Excel NotesContents TOC \o "1-3" \h \z \u What is Excel PAGEREF _Toc463252852 \h 3Common scenarios for using Excel include: PAGEREF _Toc463252853 \h 3Terms PAGEREF _Toc463252854 \h 4What is a function? PAGEREF _Toc463252855 \h 5Ways to add a Basic Function PAGEREF _Toc463252856 \h 5Common Functions PAGEREF _Toc463252857 \h 5Simple addition and subtraction PAGEREF _Toc463252858 \h 5Add the values in a range by using a function PAGEREF _Toc463252859 \h 6Notes PAGEREF _Toc463252860 \h 7Subtotal PAGEREF _Toc463252861 \h 7Cell Referencing PAGEREF _Toc463252862 \h 9Absolute cell referencing – PAGEREF _Toc463252863 \h 9Relative cell reference- PAGEREF _Toc463252864 \h 9Mixed cell reference- PAGEREF _Toc463252865 \h 10Including values from other worksheets or workbooks in a formula PAGEREF _Toc463252866 \h 10Paste Special PAGEREF _Toc463252867 \h 11SUMIF PAGEREF _Toc463252868 \h 11Syntax PAGEREF _Toc463252869 \h 11Remarks PAGEREF _Toc463252870 \h 12Example PAGEREF _Toc463252871 \h 12COUNTIF PAGEREF _Toc463252872 \h 13Syntax PAGEREF _Toc463252873 \h 13Remarks PAGEREF _Toc463252874 \h 13Examples PAGEREF _Toc463252875 \h 13IF function PAGEREF _Toc463252876 \h 15Description PAGEREF _Toc463252877 \h 15Syntax PAGEREF _Toc463252878 \h 15Remarks PAGEREF _Toc463252879 \h 16Examples PAGEREF _Toc463252880 \h 16Common Problems PAGEREF _Toc463252881 \h 16Use nested functions in a formula PAGEREF _Toc463252882 \h 16Examples PAGEREF _Toc463252883 \h 17VLOOKUP PAGEREF _Toc463252884 \h 18Syntax PAGEREF _Toc463252885 \h 18Remarks PAGEREF _Toc463252886 \h 19PivotTable PAGEREF _Toc463252887 \h 20What is a PivotTable report PAGEREF _Toc463252888 \h 20Ways to work with a PivotTable report PAGEREF _Toc463252889 \h 21Goal Seek PAGEREF _Toc463252890 \h 22Trace Precedents PAGEREF _Toc463252891 \h 23Watch Window PAGEREF _Toc463252892 \h 24Add cells to the Watch Window PAGEREF _Toc463252893 \h 25Remove cells from the Watch Window PAGEREF _Toc463252894 \h 25 What is ExcelSpreadsheet program that allows users to organize data, complete calculations, make decisions, and graph data.4 Major PartsWork SheetsLists ChartsWeb SupportCommon 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. TermsWorkbook- the Excel file that stores your information. Limited by available memory and system resourcesSheet- 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 rows moving from top to bottomGrid 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 cellActive 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 calculationsWays to add a Basic FunctionType the formula in manually into a cellUse the Inset Function buttonUse the AutoSum buttonPoint and click methodCommon FunctionsStandard 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 rangeExcel Simple addition and subtractionAdd the values in a cell by using a simple formulaIf you just need a quick result, you can use Excel as a mini calculator. Do this by using the plus sign (+) arithmetic operator.FormulaDescriptionResult=5+10Uses the + (plus sign) operator to add two or more values.15=A2+B2Adds the values in two or more cells. In this case, assume A2 = 5 and B2 = 10.15=A2+B2+20Adds the values in two cells to a number that you enter directly in the formula. In this case, assume A2 = 5 and B2 = 10.35Subtract the values in a cell by using a simple formulaDo 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 buttonYou 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 functionThe 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.? 123AAttendance482312335For 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.SubtotalInsert subtotals in a list of data in a worksheetYou can automatically calculate subtotals and grand totals in a list for a column by using the Subtotal command. Subtotals?are 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 subtotalsInsert 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. ReferencingAbsolute 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$1Including values from other worksheets or workbooks in a formulaYou 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 thisExamplesA cell or range in another worksheet in the same workbookThe name of the worksheet followed by an exclamation point, followed by the cell reference or range name.Sheet2!B2:B4Sheet3!SalesFiguresA cell or range in another workbook that is currently openThe 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!A7A cell or range in another workbook that is not openThe 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:A5Paste SpecialUsed 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 pasteSUMIFAdds the cells specified by a given criteria.SyntaxSUMIF(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_range are 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.RemarksSum_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 isAnd sum_range isThen the actual cells areA1:A5B1:B5B1:B5A1:A5B1:B3B1:B5A1:B4C1:D4C1:D4A1:B4C1:C2C1:D4ExampleThe 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. ?Note???Do 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.? 12345ABProperty ValueCommission100,0007,000200,00014,000300,00021,000400,00028,000FormulaDescription (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)COUNTIFCounts the number of cells within a range that meet the given criteria.SyntaxCOUNTIF(range,criteria)Range is the range of cells from which you want to count cells.Criteria is 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.RemarksYou 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 formulasThe 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.? 12345ABDataDataapples32oranges54peaches75apples86FormulaDescription (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.? 1234567ABDataDataapplesYes??orangesNOpeachesNo??applesYeSFormulaDescription (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 functionUse the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. DescriptionThe IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.SyntaxIF(logical_test, [value_if_true], [value_if_false])The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):logical_testRequired. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.value_if_trueOptional. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.value_if_falseOptional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is blank (that is, there is only a comma following the value_if_true argument), the IF function returns the value 0 (zero).RemarksUp to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions. Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF or the COUNTIFS worksheet functions. To calculate a sum based on a string of text or a number within a range, use the SUMIF or the SUMIFS worksheet functions.ExamplesCopy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. To see the formula in a formula cell, select the cell and press F2. Actual ExpensePredicted Expense$1,500$900$500$900 $500$925 =IF(A2>B2,"Over Budget","OK")Because the actual expense of $1500 (A2) exceeded the predicted expense of $900 (B2), the result is Over Budget . =IF(A2<B2,TRUE, IF(A3>B3,"over budget","OK"))The first IF function is false. Therefore, the second IF statement is calculated and because it too is false, the result is OK.=IF(A4=500,B4-A4,"")Because A4 equals 500, the Actual Expense $500 is subtracted from Predicted Expense $925 to tell you how much over budget you are. The result is 425. If A4 didn't equal 500, then empty text ("") would be returned.=IF(A2<B2,TRUE, IF(A3>B3,"over budget","OK"))The first IF function is false. Therefore, the second IF statement is calculated and because it too is false, the result is mon ProblemsProblemWhat went wrongO (zero) in cellThere was no argument for either value_if_true or value_if_False arguments. To see the right value returned, add argument text to the two arguments, or add TRUE or FALSE to the argument. #NAME? in cellThis usually means that the formula is misspelled .Use nested functions in a formulaUsing a function as one of the arguments in a formula that uses a function is called nesting, and we’ll refer to that function as a nested function. For example, by nesting the AVERAGE and SUM function in the arguments of the IF function, the following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50. Otherwise, it returns 0.1. The AVERAGE and SUM functions are nested within the IF function. You can nest up to 64 levels of functions in a formula.ExamplesThe following shows an example of using nested IF functions to assign a letter grade to a numeric test score.Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.Score 459078Formula Description Result '=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))Uses nested IF conditions to assign a letter grade to the score in cell A2.=IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F"))))'=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F"))))Uses nested IF conditions to assign a letter grade to the score in cell A3.=IF(A3>89,"A",IF(A3>79,"B",IF(A3>69,"C",IF(A3>59,"D","F"))))'=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F"))))Uses nested IF conditions to assign a letter grade to the score in cell A4.=IF(A4>89,"A",IF(A4>79,"B",IF(A4>69,"C",IF(A4>59,"D","F"))))VLOOKUP You can use the VLOOKUP function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado").The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.SyntaxVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):lookup_value Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. ?ImportantIf range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.RemarksWhen searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value.For more information, see CLEAN function and TRIM function.When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value.If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters?— the question mark (?) and asterisk (*)?— in lookup_value. 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 (~) preceding the character.What is a PivotTable reportWays to work with a PivotTable reportWhat is a PivotChart reportComparing a PivotTable report and a PivotChart reportDifferences between a PivotChart and a standard chartWorking with the source data of a PivotTable or PivotChart reportPivotTableWhat is a PivotTable reportA PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTable report to analyze numerical data in detail and to answer unanticipated questions about your data. A PivotTable report is especially designed for:Querying large amounts of data in many user-friendly ways.Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want.You often use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the PivotTable report illustrated below, you can easily see how the third-quarter golf sales in cell F3 compare to sales for another sport, or quarter, or to the total sales.?Source data, in this case, from a worksheet?The source values for Qtr3 Golf summary in the PivotTable report?The entire PivotTable report?The summary of the source values in C2 and C8 from the source dataIn a PivotTable report, each column or field in your source data becomes a PivotTable field (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) that summarizes multiple rows of information. In the preceding example , the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.).A value field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the preceding report contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3. By default, data in the Values area summarize the underlying source data in the PivotChart report in the following way: numeric values use the SUM function, and text values use the COUNT function.To create a PivotTable report, you must define its source data, specify a location in the workbook, and lay out the fields.Ways to work with a PivotTable reportAfter you create the initial PivotTable report by defining the data source, arranging fields in the PivotTable field List, and choosing an initial layout, you can perform the following tasks as you work with a PivotTable report:Explore the data????by doing the following:Expand and collapse data, and show the underlying details that pertain to the values.Sort, filter, and group fields and items.Change summary functions, and add custom calculations and formulas.Change the form layout and field arrangement????by doing the following:Change the PivotTable report form: compact, outline, or tabular.Add, rearrange, and remove fields.Change the order of fields or items.Change the layout of columns, rows, and subtotals????by doing the following:Turn column and row field headers on or off, or display or hide blank lines.Display subtotals above or below their rows.Adjust column widths on refresh.Move a column field to the row area or a row field to the column area.Merge or unmerge cells for outer row and column items.Change the display of blanks and errors????by doing the following:Change how errors and empty cells are displayed.Change how items and labels without data are shown.Display or hide blank linesChange the format????by doing the following:Manually and conditionally format cells and ranges.Change the overall PivotTable format style.Change the number format for fields.Goal SeekIf you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.How to Use Excel Goal SeekCreate a spreadsheet in Excel that has your data.Click the cell you want to change. ... From the Data tab, select the What if Analysis… ... Select Goal seek.. ... In the Goal Seek dialog, enter the new “what if” amount in the To value text box. ... We also need to tell Excel which cell to change. ... Click OK. PrecedentsSometimes, checking formulas for accuracy or finding the source of an error can be difficult when the formula uses precedent or dependent cells:Precedent cells?are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10. Dependent cells ????contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependents commands to graphically display, or trace the relationships between these cells and formulas with tracer arrows.Click File>Options>AdvancedIn the Display options for this workbook section, select the workbook you want, and then check that All is selected under For objects, show.If formulas reference cells in another workbook, open that workbook. Microsoft Office Excel cannot go to a cell in a workbook that is not open. Do one of the following. Trace cells that provide data to a formula (precedents) Select the cell that contains the formula for which you want to find precedent cells.To display a tracer arrow to each cell that directly provides data to the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Precedents . Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon . The other workbook must be open before Excel can trace these dependencies.To identify the next level of cells that provide data to the active cell, click Trace Precedents again. To remove tracer arrows one level at a time, starting with the precedent cell farthest away from the active cell, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then click Remove Precedent Arrows . To remove another level of tracer arrows, click the button again. Trace formulas that reference a particular cell (dependents) Select the cell for which you want to identify the dependent cells.To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Dependents .Blue arrows show cells with no errors. Red arrows show cells that cause errors. If the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon . The other workbook must be open before Excel can trace these dependencies.To identify the next level of cells that depend on the active cell, click Trace Dependents again.To remove tracer arrows one level at a time, starting with the dependent cell farthest away from the active cell, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then click Remove Dependent Arrows . To remove another level of tracer arrows, click the button again.See all the relationships on a worksheet In an empty cell, type = (equal sign).Click the Select All button. Select the cell, and on the Formulas tab, in the Formula Auditing group, click Trace Precedents twice. WindowWhen cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window toolbar. The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don't need to repeatedly scroll or go to different parts of your worksheet. This toolbar can be moved or docked like any other toolbar. For example, you can dock it on the bottom of the window. The toolbar keeps track of the following properties of a cell: workbook, sheet, name, cell, value, and formula.Note:?You can only have one watch per cell.Add cells to the Watch WindowSelect the cells that you want to watch. To select all cells on a worksheet with formulas, on the Home tab, in the Editing group, click Find & Replace, click Go To Special, and then click Formulas.On the Formulas tab, in the Formula Auditing group, click Watch Window.Click Add Watch .Click Add.Move the Watch Window toolbar to the top, bottom, left, or right side of the window.To change the width of a column, drag the boundary on the right side of the column heading.To display the cell that an entry in Watch Window toolbar refers to, double-click the entry.Note:?Cells that have external references to other workbooks are displayed in the Watch Window toolbar only when the other workbook is open.Remove cells from the Watch WindowIf the Watch Windowtoolbar is not displayed, on the Formulas tab, in the Formula Auditing group, click Watch Window.Select the cells that you want to remove. To select multiple cells, press CTRL and then click the cells. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download