Click the located in the lower-right corner of the cell. If Excel does not Group. The Sort dialog box appears. The Custom List dialog box Options button that appears and then click appears. Click your list and then click OK.
For Fill Series changes to. However, the real power of Excel comes from manipulating all this information. You can use Many Excel functions do special-purpose formulas and functions to calculate in Excel. The Function The more than functions built into Excel Wizard arranges functions in categories for enable you to perform tasks of every kind, easy access. The Payment PMT function in the from adding numbers to calculating the Financial category, for example, enables you to internal rate of return for an investment.
You determine an optimal loan payment for a given can think of a function as a black box. You put principal, interest rate, and length of loan. You do not need to know This chapter introduces useful techniques for any obscure algorithms to use functions.
You can also find tips for working an argument. Finally, you can find tips for functions more functions, their arguments, operators such as IF and special-purpose functions such such as the division and multiplication symbols, as PMT and Internal Rate of Return IRR. Enter Formulas Using a Variety of Methods. Functions simple arithmetic in three ways. Place the numbers you want to add inside divided, each separated by an operator; for example, the parentheses, separating them with commas.
Press Enter, and Excel does the math and the numbers are on the worksheet, click the cells. A list of options appears. To select other addresses, click and drag them and then press Enter.
The result appears in the cell selected in Step 2. You can click the chevron at When you click and drag over You can add buttons for equal, the end of the formula bar to multiple cells, Excel automatically plus, minus, divide, and multiply expand and collapse the bar. You Expanding the formula bar lets count, and the sum of the values can use these buttons to enter you enter longer formulas.
To learn how to of the screen. You can cells, called ranges. You can use the Name the corresponding cell address. You can use named Manager to delete named ranges. When you move a named The first character must be a letter. You cannot use range to a new location, Excel automatically updates spaces or symbols except for the period and any formulas that refer to the named range. It is best to create short, memorable When you name a range, you determine the scope of names.
To learn how to use a named range, see the name by telling Excel whether it applies to the Task Alternatively, click in a cell with a value to create a named cell. Excel creates a named range. Excel deletes the named range. If you click the Edit button in the When you click the down arrow When creating a formula, if you Name Manager dialog box, you on the left side of the formula bar, click and drag to select a group can change the range name or the a list of named ranges appears.
Named Ranges from a Selection. If you click one of the named ranges, you move to the cells it defines. With remain constant within a tax period. You can use constants in many applications. For When you define a constant, you determine the example, sales tax rate is a familiar constant that, scope of the constant by telling Excel whether it when multiplied by the subtotal on an invoice, results applies to the current worksheet or the entire in the tax owed. Likewise, income tax rates are the workbook.
To use the constant in any formula in the constants used to calculate tax liabilities. Although same workbook, simply use the name you defined. The New Name dialog box appears. You can now use the constant. When the menu appears, click the name and then press Enter. Note: To use named constants and ranges in formulas, see Task On the Formulas tab, click Name Manager.
The Name Manager dialog box appears. Double-click the name you want to edit. The Edit Name dialog box appears. Make the changes you want and then click OK. To delete a constant, click the name in the Name Manager dialog box and then click Delete. If you want to create a new constant, click New in the Name Manager dialog box. The New Name dialog box appears; you can make your entries.
In addition to formulas, you can also enter text as a constant value. Simply type the text into the Refers To field. A named constant is when you use several functions in the same formula a name you create that refers to a single, frequently or when multiple arguments are required in a single used value; see Task A named range is a name function.
Using named constants and named ranges you assign to a group of related cells; see Task An it, access it by using Use in Formula on the Formulas argument is information you provide to the function tab, or select it from the Function Auto-complete list. Double-click a value to place it in the formula.
If necessary, continue typing your formula and press Enter when you finish. To create several named constants at the same time, Naming a formula enables you to reuse it by merely create two adjacent columns, one listing names and typing its name. To create a named formula, click in the other listing the values — for example, state names the cell that contains the formula, click Formulas, and and state sales tax rates.
Select both columns. Click then click Define Name. Click a check box to indicate which column or row to use for the name. Click OK. Click Name Manager to see the constants listed in the Name Manager dialog box. Use the same procedure to create named ranges. Another way, which is a bit quicker, makes functions. You can take advantage of the wizard for sense when you know the name of your function. In financial, and engineering function. One simple but the list of functions that appears, double-click the useful function, ROUND, rounds off values to the function you want and then click the Insert Function number of places you choose.
You can access the Function Wizard in two ways. One Both methods bring up the Function Arguments box, way involves selecting a cell where the result is to where you type the values you want in your calculation appear and then clicking the Insert Function button or click in the cells containing the values.
Note: This example shows the ROUND function, which takes two 2 arguments, one indicating the number to be rounded and the other indicating the number of digits to which it is to be rounded.
A negative number refers 7 to decimal places to the left of the decimal point. If you do not know which function you want to Do not confuse the ROUND function with number use, type a question in the Search for a Function formatting. For help with argument and rounding it to the number of digits you the function itself, click Help on This Function in the specify in the second field of the Function Arguments Function Arguments dialog box.
When you format numbers, you simplify the appearance of the number in the worksheet, making the number easier to read. The underlying number is not changed. This function enables you to into the wizard. For based on factors such as the amount of the monthly RATE, enter an annual interest rate such as 5 percent payment.
For NPER, number of periods, enter using Excel, but using the PMT function may be the the number of loan periods for the loan you are simplest because you merely enter the arguments seeking.
For PV, present value, enter the amount of into the Function Wizard. To make your job even the loan. The monthly payment appears surrounded easier, enter your argument values into your by parentheses, signifying that the number is worksheet before launching the wizard.
Then by negative, or a cash outflow. Note: The result shows the amount of a single loan payment. Note: You can repeat Steps 1 to 10 for other combinations of the three variables. Place the labels payments. With Goal Seeking, you can set up a Principal, Interest, and Number of Months of a loan problem so you specify a goal, such as payments less period in a column.
Use references to those value to reach the goal. See Task 59 for more information. When using function to calculate the rate of return on an the Internal Rate of Return function, you must enter investment. When using the IRR function, the cash at least one positive and one negative number. As an example, you make a loan of your best-guess estimate as to the rate of return. You receive payments The default value, if you do not provide an estimate, every January 1 for four succeeding years.
You can is. Your use the IRR function to determine the interest rate estimate merely gives Excel a starting point at which you receive on the loan. Each payment is a cash inflow, 1 Type the series of projected cash flows into a worksheet. Repeat Steps 1 to 8 for each set of anticipated future cash flows. Cash NPV function, which calculates the net present flows must be regularly timed and take place at the value of future cash flows. Whereas IRR returns a same point within the payment period.
IRR may percentage — the rate of return on the initial perform less reliably for inconsistent payments, a mix investment — NPV returns the amount that must of positive and negative flows, and variable interest be invested to achieve the specified interest rate. LARGE takes two arguments: the range of cells result in another function, such as summing the top you want to evaluate and the rank order of the value five values. Apply It! They take one argument: a range of cell values.
The Team number is the condition and a calculation on numbers that meet the condition. The in the IF function. Because two functions are involved, first function, IF, defines the condition, or test, such when you use the Function Wizard, one function, IF, as players on Team 1. To create the condition, you is an argument of another function.
This example uses MAX, which finds the highest value in a list. IF is an array function. It compares every number IF has an optional third argument. Use the third in a series to a condition and keeps track of the argument if you want to specify what happens numbers meeting the condition. To create an when the condition is not met. You must surround arrays with curly values exist and False if they do not. Values that one easy-to-use function. SUMIF enables you to avoid determine the team a person is on and for all complicated nesting and to use the Function Wizard persons on Team 1 it can add the scores.
The third without making one function an argument of the argument, the range to which the condition applies, other. If you exclude it, Excel sums the range gives you more flexibility. For example, you can use you specify in the first argument. IF to create multiple complex conditions. Note: Each value in the list is 3 tested to see whether it meets a condition.
If it does, it is added to other values meeting the condition. The Conditional Sum Wizard has four self- two arguments: a series of values and the condition explanatory steps. The last step diverges from the by which the values are tested. You items that passed the test. To learn how to install this add-in, see Task Use MS to remember a value, MR to recall it, using a formula or function.
In Excel, you can place a and MC to clear memory. In this view, you can cube a number, You can use the calculator as you would any electronic find its square root, compute its log, and more. In calculator. The calculator displays in scientific mode. To calculate an average, switch to the scientific view For complete instructions on using the Excel and enter the first number to be averaged. Click the calculator, open the calculator.
Click Dat. The Calculator Back in the calculator, click another value to average dialog box appears. Click the Contents tab and then and click Dat. Keep entering data and clicking Dat Calculator. A list of topics appears. Click any topic to until you have entered all the values. Click Ave to find learn more about the calculator. Click the Insert Function button fx to function, you can find the square root of a number.
Excel can calculate the square roots of positive numbers only. Note: Typing the function directly 2 3 into a cell or into the formula bar preceded by an equal sign is an alternative to choosing it from the 5 Function Wizard. Optionally, you can type a value directly into the Number1 field. Optionally, you can type the value directly into the Number2 field. Optionally, you can click in a cell containing the value. These values power, use the Power function.
Arrays can be multiplied by each other. Each value in the array has to be a number. You can find, for between 0 and 1. Date consists of the date to the left of the decimal and a and time functions convert every date and time into a time to the right. Take the example August 25, , serial value that can be added and subtracted and at PM.
The date and time serial value is then converted back into a recognizable date or time. Excel calculates a result back into a date or time. If you 1 3 want p. The format displays hours:minutes. In subtracting times that cross If subtracting times or dates yields Showing a time or date in the midnight, such as 11 p.
To display a time in earlier time, 1. Like other Excel functions, Date and Time between the two dates. Excel automatically deducts functions make use of the Function Wizard. As with the number of weekend dates. If you use dates before then, data you have typed into cells in your worksheet.
Excel treats them as text and does not perform To calculate the number of workdays between two a calculation on them. You can store as many as 24 different retyping and errors. Basic copying in Excel is items on the Office Clipboard for pasting into similar: Select the value and then click the Copy Excel and other Office applications.
In the new location, click in the cell where you want to place the value In this chapter, you learn to use the Office and then click Paste. But copying can be both Clipboard. You also learn to transpose a row more involved and more powerful in Excel into a column, to copy styles from one because so many elements can occupy a cell: worksheet to another, to copy formulas from values, functions, formulas, formats, styles, and one cell to another, to change text to numbers, more.
You can copy any of these elements and much more. If you share your workbooks between cells, worksheets, workbooks, and with others, you may want to use the tip on even applications. You can copy one value at a keeping track of the changes. Check Your Formulas for Errors. Click the Evaluate button to begin the within another formula.
Because there are so many process. Excel underlines individual expressions. You intermediate steps when you nest formulas, can click the Evaluate button again to see the results determining the accuracy of your results may be of an expression. The results of expressions appear difficult. You can use the Evaluate Formula dialog box in italics. When you open the Evaluate Formula dialog box, you Click the Step Out button to return to the reference.
The Evaluate Formula dialog box After you step through the entire formula, Excel steps you through the calculation one expression at displays the result and a Restart button. Click the a time so you can see how Excel evaluates each Restart button to evaluate your expression again.
The maximum number of functions you can nest The error occurs when a cell is not wide within a function is You can use the Excel trace Dependents in the Formula Auditing group on the features to help you locate your error. Formulas tab. This option draws blue arrows to each You can view a graphical representation of the cells cell that contains a formula that uses the active cell a formula refers to by clicking in the cell and then as an argument.
If you perform this function before clicking Trace Precedents in the Formula Auditing deleting a value, you can determine if your deletion group on the Formulas tab. This option draws blue affects a formula in your worksheet. By selecting this option, you can identify the exact cells used by your formula.
Excel removes the arrows. When working with large or complicated worksheets, where the result of the formula is dependent on the results of another formula in another cell, you may want to monitor cell values.
To do so, you can use the Watch Window. The Watch Window appears at the bottom of your screen. Click the Add Watch button. The Add Watch dialog box appears. Click and drag to select the cells you want to watch and then click Add. You can now monitor the cells you have added to the Watch Window. You quickly and accurately on numbers, dates, or times. If a number always work.
A more reliable technique is to multiply is left-aligned in a cell, it is probably text; true each numeral by 1 to convert the data type from text numbers, by default, are right-aligned. In Excel, text and numbers are different data types. The numbers-as-text problem often occurs when you You should use numbers, but not text, in mathematical import data from another application — for example, formulas and functions. Trying to include text in a an external database such as Access. Chapter 9 mathematical calculation results in an error.
Note: The left-aligned numbers in this task are really text. In Excel, the default position for numbers is the right side of the cell. A contextual menu 4 appears. Excel can convert text-based numbers to actual numbers. Click the Office button and then click Excel Options. Click Formulas. Excel flags cells containing text by placing a green flag in the upper-left corner. Click the menu button appearing alongside any cells with this error.
Click Convert to Number. If Excel is able to convert the text to a number, the numbers will right-align. This method does not always work. At of the product: its ID, its price, the quantity in any time, you can insert new rows or columns, delete inventory, and so on. Tables typically have many rows or columns, and move entire rows or columns rows and fewer columns. Sometimes, With Excel, you can copy, or transpose, a row into however, you may want to transpose a row into a a column and vice versa by using the Paste Special column — or vice versa.
To ensure you have room for new Transposing comes in handy when you need to create worksheet data, you can place the transposed a table, a special kind of worksheet discussed in columns or rows on a different worksheet or in a Chapter 4. A table might consist of rows describing new workbook. Note: Make sure a series of blank cells is available to accommodate 1 the copied data. Start by selecting an area large them carefully. For long lists of people, things, enough to hold the new grid.
Excel calls this Function button. The Function Arguments dialog type of layout a table. For more about tables, see box appears. Select the cells you want to transpose Chapter 4. Excel transposes the grid. You can use storage area called the Clipboard and paste the the Clipboard to store a range of cells.
The Office content into Excel or another Office application. Cut Clipboard pastes the entire range, including all the and copied content stays on the Clipboard until you values, but any formulas in the cells are not included close all Office applications. The Office clipboard can when you paste. All the items on After you paste an item from the Clipboard, Excel the Clipboard are available for you to paste to a new provides the Paste Options icon menu. You can use location in Excel or in another Office document.
In source formatting or the destination formatting on Excel, access the Clipboard by clicking the launcher the pasted data. Excel places a copy of the cells 1 you selected on the Office Clipboard. The default is to match the formatting of the new location. Press Esc to accept the default and remove the menu.
To copy a cell range within or between worksheets, To keep the Clipboard Task Pane from unexpectedly click and drag to select a series of cells and then appearing while you are copying, open the pane, click the Copy button on the Home tab in the click the Options button on the lower left, and select Clipboard group. You can remedy this problem by else in your worksheet. Cells can contain a lot of pasting in two steps. In the first step, paste column information.
When you paste with Paste Special, you widths. Excel adjusts the column widths. In the decide exactly what information you want to paste. You can press Esc to end the copy session. Cutting moves use it to bring digital pictures, source includes any blanks. If you data from its current location to WordArt, and clip art from do, Excel will not overwrite a the location where you paste it.
You can also use Paste Special. Simply copy a cell with the format you want, and You can also use Paste Special to perform simple then use Paste Special to paste the format into other arithmetic operations on each cell in a range. For cells. See Chapter 7 to learn more about the Format example, in a list of prices, you may want to increase Painter.
You can use Paste Special You can use the same steps to copy formulas or to make the change quickly. Just type 1.
This example copies a formula. You can use the Paste Link option in the Paste You do not have to open the Paste Special dialog Special dialog box to keep your source and box to paste formulas and values. You can select destination data synchronized. If you click the these options directly from the Paste menu on the Paste Link button when pasting, when you make Home tab. To create a new style format numbers, text, and cells. A style is a named based on the current one, right-click the style in the collection of formats you can share among users and Cell Styles gallery and then click Modify.
The Style apply across workbooks. Styles streamline the work of dialog box appears. Click Format. The Cells dialog formatting so you and others can apply a consistent box appears. Use the dialog box to select one or set of formats to worksheet elements such as row more formats. Click OK when you finish. Back in the heads, column heads, and data values. Excel comes Style box, give your new style a name, and then with many styles, which you can view in the Style click OK.
The Styles gallery appears. The Style gallery appears. You can use the Format Painter to apply styles, You can build a style from the ground up rather and you can copy, and paste formats from one cell than basing it on a formatted cell.
Click the Home to another. However, if you use a format often, tab and then click Cell Styles. The Style gallery consider creating a style.
See Task 71 to learn appears. The Format cells dialog box appears. You can use it to design your style. With styles, you maintain consistency in applying the style to other cells. To use a style in the way numbers, dates, times, borders, and text another workbook, copy the style from one workbook appear in cells. You can create a style based on any to the other.
When you copy a style into another combination of formats available in the Format Cells workbook, Excel calls that merging. One workbook can from which you want to copy the style and the contain many styles. Styles simplify your work and reduce the time required to format worksheets. With styles, you can 1 1 Open the file with your custom style. Each style in a workbook must have a unique Using a style copied into a workbook works the name.
If you try to copy a style into a workbook same as if you created the style within the containing a style with the same name, a warning workbook. To use a copied style, select the cell box appears when you click OK in the Merge or range of cells to be styled.
Click Home and Styles dialog box. If you want the imported style then click Cell Styles in the Styles group. The to take the place of the existing style, click OK.
Click the imported style. Otherwise, click No or Cancel. For example, if your company Changes affect conditionally-formatted data. If, after offers a bonus whenever sales exceed , a change, a cell no longer meets the condition, Excel dollars, you can have Excel highlight cells containing removes the highlighting.
If, after a change, a cell sales figures whenever the value is more than meets the condition, Excel adds highlighting. You , dollars. You can also have Excel highlight determine exactly what the condition is and what a cell when the entry is less than, between, or equal should happen if a cell meets the condition. Excel to a specified value. A submenu appears. Note: This example uses Greater Than. You can choose to create a custom format.
Excel provides you with several colors to choose You can use color scales to conditionally format data. Color scales use gradations of color. For example, if You can select another color. To group. Click Data Bars and then apply a color scale, select the cells to which you click More Rules. The New Formatting Rule dialog want to apply the scale, click the Home tab, click box appears.
A data bar is a colored bar you place in a cell. The length of the bar represents the value of the cell Data bars, color scales, and icon sets all use rules to relative to other cells — the longer the bar, the higher determine when to display what. You can use the the value. Excel provides you with several bars from rules defined by Excel or you can create your own which to choose and you can design your own.
At the bottom of the data bar, color scale, or Color scales and icon sets are similar to data bars, icon set menu, click More Rules to adjust rules. Alternatively, you can click Data Bars or Color Scales. Each allows you to change the associated rules. A context menu appears. To learn more see Task Use Where to account for who makes what change, in which to specify the rows and columns whose data you cells, and when. To do so, you can use the Track want to monitor. Changes feature. When someone makes a change, Excel indicates the In the Highlight Changes dialog box, use the When, change by placing a small purple triangle in the upper- Who, and Where options.
Use When to define the left corner of the changed cell. Excel records cell time after which edits are tracked — for example, changes in automatically generated cell comments. Use You can view these comments by moving your mouse Who to identify the group whose edits you want to pointer over the cells.
The optional When, Who, and Where fields become available. Note: For more about comments, see Task 7. To view all worksheet changes after you or others You can review every change made to a worksheet make edits, open the Highlight Changes dialog box and either accept or reject the change. For the Who field, click Everyone.
The available options let you Click to uncheck the When and Where fields. Click restrict your review to changes by certain people and OK. Excel creates a new worksheet called History that at certain times. You can sort and filter the worksheet. You may want to eventually want to consolidate. For example, if you format your cells so that the incoming data displays keep sales information for several regions on properly.
You then select the function you want to separate worksheets, you may want to consolidate use to consolidate you data. The SUM function takes the worksheets to find the total sales for all regions. You tell Excel the location of the data you that. Excel provides a variety of functions you can want to consolidate.
To include data from another workbook in your If you click Create Links to Source Data changes worksheet, open the other workbook. In the to in the Consolidate dialog box, Excel updates Consolidate dialog box in your original workbook, your consolidated data each time you make a change place your cursor in the Reference field. Click the to the data on which you base your consolidation.
View tab, click Switch Windows, and select the other workbook. Click and drag to specify the data you want to consolidate. Click Add. Excel consolidates the data. Each Advanced filtering gives you tools for filtering column represents a single type of data. For out duplicate records and applying multiple example, a list might have three columns: complex filters to your data. Each row in the list is a record. For each record in the list, the name With data formatted as a list, you can count, column contains a name, the gender column average, and subtotal parts of your data that contains a gender, and the age column contains meet certain criteria.
In a customer survey, for an age. When you structure a worksheet as a example, you can count the number of senior list, you can tap the Excel database-like citizens who prefer a certain sport or compare capabilities that go beyond what is possible the time spent online among different age with a simple worksheet.
You carry out calculations by using the Ribbon or by using This chapter shows you how to work with lists database functions.
Refer to Chapter 2 to learn and other data that you structure as lists. Much of the chapter focuses on sorting and filtering. To sort means to arrange a list in When you organize data into a list, you have order, either alphabetically or numerically.
You access to lookups, a special way of searching can sort and re-sort lists as necessary and for data. You might use a lookup to retrieve a even sort within a sort.
To filter means to stock price by typing a stock symbol. You can display only the information that meets also create a powerful analytical tool called a certain criteria — temporarily hiding the rest. PivotTable, which is discussed in Chapter 5. Enter Data with a Form. You can move labels, one per cell, each describing the content of a backward and forward through your list to view or column. Then you enter the data. Excel enables you modify your data. The list form also doubles as a to generate a form to simplify data entry.
A form simplifies and speeds up data entry by You must add the Form button to the Quick Access providing a blank field for each column in your table. See Task 95 You type the data and press the Tab key to move to learn how to add a button to the Quick Access from field to field.
After you complete one set of toolbar. A list of your data records appears in the worksheet. You can use the data form to search for and edit your You can use a form with an existing list. Just click and data. In a blank field, type an operator, Form button on the Quick Access toolbar. If you are looking for text, place the text you are looking for in quotes; if you are looking for a number, do not place the number in quotes.
Ordinarily, players represented in his collection so he can create you use advanced filtering tools to create filters in a catalog.
Alternatively, a store manager may want to several columns or even several filters on a single know the number of unique individuals represented in column. Excel provides tools for displaying only unique records in a list.
Start with a worksheet formatted as 2 1 Click in a cell in your list. If you want to retain you unfiltered list, you can place Filtering duplicate records temporarily removes them your filtered list in another area of you worksheet. In from view. If you want to delete duplicate records the Advanced Filter dialog box, click Copy To Another permanently, select your list, click the Data tab, and Location changes to and then enter the then in the Data Tools group click Remove Duplicates.
If your the Copy To field. Select the columns you want to check for duplicates and then click OK. Excel deletes the duplicate records. When you sort, you rearrange your In descending order, numbers sort from the highest data in ascending or descending order. When you sort a list, you can easily The meaning of these terms depends on the kind of find data, group data, and present it meaningfully to data you have.
Customer data arranged by the date others. When your data, displaying only data that meets your you sort by customer name, the names appear in criteria. In a customer survey, for example, you can ascending A to Z or descending Z to A order. Click A to Z to sort from lowest to highest — ascending order. Click Z to A to sort from highest to lowest — descending order. In this example, CA and MA do not appear. When you click the down arrow Excel creates when When you perform a filter, Excel places down you click Filter, Excel provides options for filtering arrows next to your field headers.
Fields you have your data. It also provides options for sorting your filtered have a filter button on the down data. You can perform simple sorts, complex sorts, arrow. Fields you have sorted in ascending order or you can sort by cell color, font color, or icon. See Task s 39 and 40 to learn more about Fields you have sorted in descending order have a sorting.
You can also sort subtotals, averages, and counts at every break in by multiple criteria — a sort within a sort.
When a category — that is, for all people in a specific possible, sort first by a discrete category such as community, for men and women in that community, gender, community, region, or department. That way, and so on. Ascending contained within each category.
In the For example, after sorting your customer records by Sort dialog box, you can click Options to specify a community, you could sort them by gender to display custom order. For example, you could order months the communities in which the men and women live. Click OK to close the Custom Lists dialog box. Click OK to close the Sort dialog box.
Excel defines different sorts as follows. For numbers, In the Sort dialog box, click Delete Level to delete ascending order goes from the smallest number to a level of sort. Click Copy Level to copy a level of the largest. For text that includes numerals, as in U2 sort. Click to move a sort level up. Click to and K12, ascending order places numerals before move a sort level down.
Case does not matter unless you click Options in the Sort dialog box and then click the Case Sensitive check box changes to. For Then you can sort cells by these colors. Select a example, you can have the highest ten values in a single column and then click a sort button on the list appear in a particular color or font or with an Ribbon to sort your data.
You can nest your sorts. You can have values that meet other criteria In the Sort dialog box, you can select the color you appear in another color or font or with another icon. You can then sort your data based on the of the list. Note: If you have formatted your data as a list, click in any cell in the table.
Click On Top to send the items that match the criteria to the top of the list. Click On Bottom to send the items that match the criteria to the bottom of the list. Repeat Steps 5 to 8, as necessary.
An alternative way to sort by color, font, or icon By default, Excel sorts from top to bottom. If is to right-click in any cell that has the color, you want to sort from left to right, click Options font, or icon by which you want to sort. A menu in the Sort dialog box. The Sort Options dialog appears. Click Sort. Another menu appears. Click Sort Left to Right. With Excel, you can combine filters, applying Criteria look like this: Age is greater than 65 and State different criteria to different columns.
When you filter a list, down arrows down a long list to the few records of interest to you. To filter a list Missouri or Age greater than By applying a filter, by multiple criteria, start with a worksheet formatted you display only those records that contain certain as a list. Alternatively, click Text Filters 5 if you chose a text field or Date Filters if you chose a date field in Step 4.
Choose And if you want 9 both criteria to be met. Choose Or if you want either criteria to be met. Note: To sort the filtered records, click the Data tab and then click a sort option. You can use a filter to view the top or bottom N values in a list, where N stands for the number of values you want to view.
Click the down arrow next to the numeric field you want to filter. Click Number Filters and then click Top Ten. The Top 10 AutoFilter dialog box appears.
Select Top if you want to view the Top N values. Click Bottom, if you want to view the bottom N values. Type the number of values you want to view and then select whether you want Excel to return results based on a number or a percentage. Excel returns the results you request. Use one or more column limitations of the AutoFilter command discussed in heads from a list. In the cell below each head, type Task For example, you can filter find all males. The filtered list you use the Advanced Filter menu command.
You must be on the same worksheet as the original list. Note: Use operators to define criteria and place text in quotes. You can click Copy To Another Location to copy the list to another 5 location and retain the 6 original list.
You may need to format the results to accommodate wide columns. A criteria range can have several rows of criteria. Make sure your Copy To range has enough room When a row consists of two or more criteria, below it to include all the values that may return Excel looks through your list and returns only the in the filtered list.
If you place the Copy To range rows that meet all criteria. If you want Excel to above your original list, the results may overwrite return records that meet either criteria place the the list and disrupt the filtering. Placing the copy criteria on separate rows. When you calculate the on each category. Excel provides the tools for average, sum, or other calculation for a sorted list, performing simple calculations to compare one outlining enables you to view only the results of the category with another.
With a sort defined for at least calculation. In other circumstances, the another column. You can create several subtotals You can remove outlining by Remember that you can do a for a single sorted list. To display clicking the Data tab, and then calculation on a different column all your subtotals, make sure the in the Outline group, clicking from the one defining the sort.
You can position your chart the information in a worksheet or list. Charts show next to the data on which you base it, so when trends and anomalies that may otherwise be difficult you change the data you can instantly observe the to detect in columns of numbers. By choosing the changes in the chart. If you do not want convey patterns in your data. For more about Excel to remove filtered data, select the Show Data charting, see Chapter 6. Note: See Chapter 6 for detailed information on creating charts.
Note: See previous tasks in this chapter to learn more about filtering. By default, only the unfiltered data displays in the chart. If you want to reposition a chart, click the chart. You can delete the graphical representation of a A border with triple dots on all four sides of the value from a chart.
For example, you can delete chart and on the corners appears. Roll your the graphical representation of Region 2 in the mouse pointer over the dots. When your mouse chart shown in this task. Just click the graphic pointer turns into a four-headed arrow, you can and then press the Delete key. The first argument, Database, functions enable you to perform calculations and identifies the cell range for the entire list. The second summarize data patterns. You use database functions argument, Field, identifies the cell range for the with lists.
Database functions are especially good at column from which you want to extract data. In the summarizing the subsets of your list. Most database third argument, Criteria, you provide Excel a criterion functions combine two tasks: They filter a group of for extracting information.
You build the on the filtered data. Alternatively, click the function on the AutoComplete list. The names of database functions begin with a D You can use the DSUM function to add numbers to distinguish them from worksheet functions. As that match the criteria you specify. Type the match the criteria you specify.
If you enter TRUE or another value. Specify the column from which you leave this argument blank, the function looks for the want to retrieve the corresponding value. You must enter three required 1 Type the value you want to use to retrieve another value.
As you begin to type, the Function AutoComplete list appears. Double-click an 1 2 option to select it. For the same reason, if you search dates, make sure you format your dates as dates, not text. Defining a table is simple. Provides the reader with all of the new tips and tricks that are available with Excel whilst reinforcing and improving their basic Excel skills. Brilliant Microsoft Excel Tips and Tricks. Get Books. All over the world, Excel developers, power-users and gurus have been working overtime to uncover.
Brilliant Microsoft Access This book focuses on the three technologies that need mastering to get the most out of Access: forms, reports and queries. It is packed full of real-world examples and techniques to help the reader learn and understand the importance of each section. This text will improve you efficiency, enable you to take control of your digital media and data; use Internet Explorer 7, Windows Mail, and Calendar; configure Windows Defender and Firewall and avoid, troubleshoot, and fix problems.
Brilliant Microsoft Excel Pivot tables are the most powerful feature in Excel. A basic pivot table will allow you to summarize , rows of transactional data in 30 seconds with just a few mouse clicks. Business productivity would skyrocket if everyone knew how to use pivot tables. Brilliant Microsoft Office Office is a massive, expensive suite packed with powerful applications that feature a huge number of new features and functionality that require a steep learning curve of all users irrespective of previous experience.
Even when a user feels comfortable with the new verison of an application, many still feel. After 15 years with no updates to the Excel charting engine, Microsoft has provided a complete rewrite of the chart rendering engine in Excel This book will teach users to develop their skills so they can produce great looking charts that communicate clearly.
Brilliant Excel Charts and Graphs teaches techniques.
0コメント