tvnovellas.info History EXCEL FOR ACCOUNTANTS CONRAD CARLBERG PDF

EXCEL FOR ACCOUNTANTS CONRAD CARLBERG PDF

Using real-world examples and downloadable workbooks, Carlberg helps you choose the right technique for each problem and get the most out of Excel's statistical features. Along the way, he clarifies confusing statistical terminology and helps you avoid common mistakes. You'll learn how to use correlation and regression, analyze variance and covariance, and test statistical hypotheses using the normal, binomial, t, and F distributions. To help you make accurate inferences based on samples from a population, Carlberg offers insightful coverage of crucial topics ranging from experimental design to the statistical power of F tests. Updated for Excel , this guide covers both modern consistency functions and legacy compatibility functions. Becoming an expert with Excel statistics has never been easier!

Of course, if you had another region, say Central, you'd get five unique records. If your list consists of Region and Year, you'll have up to twenty unique records: four regions times five years. If there's no record for a given year in a given region, that maximum of twenty will drop to an actual of nineteen. Finally, if your list consists of Region and Revenue, it's likely that your list of unique records will be identical to your original list except in the unlikely situation where two or more regions have exactly the same revenue.

But in that case you'll want to specify some criteria for the filter otherwise, your filter will trivially return all the records in your list. There are two general steps for setting up criteria for Advanced Filter: Choose a range of cells at least one column away from your list. In the first row of that range, put the names of fields in your list that you want to filter on. This is the first row of your criteria range. Below the name of each field in the criteria range, enter the values that you want to keep: that is, the values that you want to filter for.

Figure shows an example. Figure You can avoid typing errors in the criteria range by copying and pasting from the original list. In Figure , the criteria range is F1:G2.

The names of the fields in the list that Excel should use as criteria for filtering are in F1:G1. The values of those two fields that Excel should filter for are in F2:G2. These two criteria fields are treated as if they were connected by an AND; Return any records where the Region is Northwest and the Year is The filtered list has been copied to another location, starting in cell I1.

Chapter 1 Using Lists in Excel 23 You'll have less use for an implied OR connector in a criteria range, but here's how to make one. Suppose, strangely, that you want to return any records that are either from the Northwest region or from year Put those two criteria in the same columns as before, but in different rows.

Figure shows how to do this. Figure The filtered list is copied to another location, to avoid hiding rows as AutoFilter does. Figure showed the filtered list copied to a new location. If, instead, you choose to filter the list in place, Excel hides the rows that don't conform to your criteria, just as with AutoFilter.

The easiest way to get those rows back is to choose Data Filter Show All. Managing a List There's a way of managing lists that first showed up in Excel It offers little you can't do in earlier versions, but it does bring some existing capabilities together in one place.

If you're new to lists, that can be handy. Click any cell in the list and choose List from the Data menu.

You'll see a shortcut menu that has only one useful item: Create List. As soon as you select that item, you see the window shown in Figure Figure If you take the advice given in this chapter about setting up lists, your list will have headers. Confirm the range address of your data, and if necessary correct it you can drag through the correct range on the worksheet. Accept or correct Excel's guess about whether your list has headers, and click OK.

You'll see the result shown in Figure Figure shows that Excel automatically adds the following elements to your list: Filter drop-downs so you don't have to select AutoFilter to get them A border around the outside of your list. This border might help you analyze the data more effectively, but I can't think how. A row where you can enter a new record. You can do this at the bottom of any list, but this row has an asterisk. Chapter 1 Using Lists in Excel 25 List headers, if you didn't supply them yourself.

The headers get the names Column1, Column2, Column3 and so on, so it's a good idea to specify them before you formally create the list. A total row, which you can toggle on-and-off it's off at first. This can be a time-saver, so let's look at it a little more carefully.

Figure Rows 12 through 21 have been hidden to make room for special rows at the bottom of the list. First, though, note that some aspects of the list disappear when you activate a cell outside the list. The AutoFilter drop-downs disappear, and the border around the list, while it stays there, is no longer bold, and the new-record row the one with the asterisk disappears.

Handling the Total Row So does the List toolbar disappears, that is. It was shown in Figure To get at the List toolbar, just click inside the list. Figure showed the toolbar's Toggle Total Row button.

If your list has a column with at least one numeric value, that column defaults to Sum as its total. A column that contains no numeric value has no default total.

However, you can set it to show Count as its total by following these steps: 1. Click the cell that would contain a total if its column were not all text values. In Figure that cell could be B33 or C A drop-down appears immediately at the right side of the cell.

Click the drop-down to display the totals that are available to you. If you want to know the number of text values in that column of the list, choose Count.

By default, the left-most column in your list has the word Total in the Total Row. You can override this with the cell's drop-down and choosing anything, including None, from the list. Average: Show the mean of the numeric values in the column. Because the term "average" is sometimes used to mean different things, let's be explicit: here, it means the total of the numeric values divided by the number of numeric values.

Count: Show how many values are in the column. Count Nums: Show how many numeric values are in the column. If your column contains 12, 8, B, 27, and Fred, Count Nums returns 4. Min: Show the smallest numeric value in the column.

Sum: Show the total of the numeric values in the column. This is the default total for a column with numeric values. StdDev: Show the standard deviation of the numeric values in the column. I've been using Excel for over 20 years, including the years since Excel was released, and I've never seen anyone use the Total option for standard deviation on a list. Var: Show the variance the square of the standard deviation of the numeric values in the column. Getting the variance of a list's column is even rarer than the standard deviation.

Okay, that's less than zero, but you know what I mean. Totals in the Status Bar These options go a little farther than the options you can get from the Status Bar. What's the Status Bar got to do with it?

Well, first, the Status Bar is the horizontal bar at the bottom of the Excel window, the one that's normally just beneath the sheet tabs, with the word "Ready" at its left.

Figure shows the Status Bar. Figure If you put a toolbar at the bottom of the Excel window, it will separate the worksheet tabs from the Status Bar. The Status Bar can tell you some useful things. It's a quick way to tell what keyboard options you've selected.

Excel for Accountants

When I press 9 on my keyboard's numeric keypad, and the Excel window scrolls up a page, instead of putting 9 in the active cell, a quick glance at the Scroll Bar tells me that I forgot to turn on Num Lock. But that's incidental to what we're talking about. Try this: 28 Excel for Accountants 1. Select a group of cells that contain some numbers cells that are adjacent and in the same column B1:B5, for example , or the same row maybe B2:F2 , or that are in a range such as CF This step isn't really necessary but it makes things a little clearer.

Right-click anywhere on the Status Bar. You'll see a menu pop up, with the names of various totals you can choose from. Choose one of the totals say, Max. The maximum number in the group of cells you selected in Step 1 appears in the Status Bar. The same sort of thing happens if you choose any of the six options in the menu you saw in Step 2. So, what's to choose from if you want a total from a list by using the Data List command, discussed in the prior section or to see it on the Status Bar?

There are two points to choose from: You don't have as many options on the Status Bar. The list totals give you the standard deviation and the variance. Like me, you might not regard this as a compelling reason to use a list for calculating measures of dispersion. Using the Status Bar, your data don't have to be laid out in a list. The numbers can be scattered all over the sheet. If you take a notion that you want the total of revenue, inventory turns, and EBITDA, just make sure you've selected Sum for the Status Bar totals Step 3, above and then select the cells, one by one, wherever they are on the worksheet.

TIP: To select several cells that are not adjacent, hold down the Ctrl key as you click in the cells. Getting Rid of a List By "getting rid of a list," I don't mean the data; just the bells and whistles that Excel adds when you use Create List. On either the List toolbar or in the menu you get by choosing Data List, you'll find a Convert to Range command.

All this item does is remove the drop-down arrows, border, and so on items it added when you used Data List Create List. Chapter 1 Using Lists in Excel 29 Using the Data Form In Excel, a data form is a special kind of window that opens in response to something you do, such as clicking a custom button.

The data form pops up and someone starts entering data. When he or she is through, the data is saved on a worksheet. Figure shows this situation. Figure A moderately experienced developer can develop a form like this in just a few minutes. What you see in Figure is a custom data form. It's one that someone experienced in Excel constructs and ties to a worksheet, so that when the user hits the OK button, Excel takes the data in the form and writes it to the sheet.

There are plenty of good reasons to arrange the entry of data with a data form. For example: Checking the data on the form for validity Displaying some fields or some records for certain users, and other fields or records for other users usually in a context that requires different levels of security 30 Excel for Accountants Situations where there are so many fields that it's clumsy to display them all on one form and several tabs are needed.

Excel's Options form is one example. An experienced developer can create and test the form shown in Figure in no time. But Excel provides you with a built-in Data Form that offers some of the functionality of a custom form. Figure shows how the Data Form looks with a list Ive used extensively in this chapter. In this case, because the custom form in Figure is pretty rudimentary, the Data Form in Figure has much more functionality than does the custom form. Figure The Data Form's name, on its title bar, is by default the same as the name of the active worksheet.

Before looking at the individual capabilities, notice how difficult it is to create the form: 1. Click in a cell in your list. Choose Form from the Data menu. That's all that's needed to get the form you see in Figure It's a good way to impress someone who doesn't know that Excel can do this for you.

Chapter 1 Using Lists in Excel 31 Now, here's what you or your data entry person can do with the data form: You can tell how many records are in your list, and which record is active, by looking just above the New button.

Move through the records using the scroll bar just left of the buttons. Establish a new record at the bottom of the list by clicking New. Delete the current record by clicking Delete. Revert to original values that you've edited in the current record, by clicking Restore. The Restore button is dimmed until you make a change to a value. You can't Restore a record that you've Deleted. Instead, close the data form, and then close the workbook. Do not save changes when you're prompted.

Lastly, re-open the workbook. Go to the prior record, by clicking Find Prev short for previous. If you have established selection criteria by clicking the Criteria button, this takes you to the closest prior record that qualifies according to the criteria. Go to the next record, by clicking Find Next. The criteria act in the same way as with Find Prev, except that Find Next takes you to the following qualifying record.

Establish selection criteria, also known as filters, by clicking the Criteria button. Close the Data Form by clicking the Close button. If you click the Criteria button, you see something very similar to the data form; the main difference is that the boxes will be empty. Type the values that you want to select in the appropriate boxes: for example, to select the Northwest region and the Towels product, type Northwest into the Region box and Towels into the Product box.

Chapter 1 mentioned some of the ways that you can use lists to set up financial analyses that are more sophisticated than just looking at transactions. One of those ways is by means of a pivot table.

A pivot table is the most powerful and flexible method of data analysis and synthesis available in Excel. The Purpose of Pivot Tables We're often confronted with a mass of data that probably has some interesting patterns in it but there's so much detail that it's almost impossible to tell the forest from the trees.

A page detail report of expenses by cost center is hard enough to 34 Excel for Accountants deal with, but it can become a real headache if someone needs to total up the costs by vendor. Try this:. Select a group of cells that contain some numbers — cells that are adjacent and in the same column B1: B5, for example , or the same row maybe B2: F2 , or that are in a range such as C This step isn't really necessary but it makes things a little clearer. Right-click anywhere on the Status Bar.

You'll see a menu pop up, with the names of various totals you can choose from. Choose one of the totals — say, Max. The maximum number in the group of cells you selected in Step 1 appears in the Status Bar. The same sort of thing happens if you choose any of the six options in the menu you saw in Step 2. So, what's to choose from if you want a total from a list by using the Data List command, discussed in the prior section or to see it on the Status Bar? There are two points to choose from: The list totals give you the standard deviation and the variance.

Like me, you might not regard this as a compelling reason to use a list for calculating measures of dispersion. The numbers can be scattered all over the sheet. If you take a notion that you want the total of revenue, inventory turns, and EBITDA, just make sure you've selected Sum for the Status Bar totals Step 3, above and then select the cells, one by one, wherever they are on the worksheet. To select several cells that are not adjacent, hold down the Ctrl key as you click in the cells.

Getting Rid of a List By "getting rid of a list," I don't mean the data; just the bells and whistles that Excel adds when you use Create List. On either the List toolbar or in the menu you get by choosing Data List, you'll find a Convert to Range command. All this item does is remove the drop-down arrows, border, and so on — items it added when you used Data List Create List.

A moderately experienced developer can develop a form like this in just a few minutes. What you see in Figure is a custom data form. It's one that someone experienced in Excel constructs and ties to a worksheet.

The data form pops up and someone starts entering data. When he or she is through. Excel takes the data in the form and writes it to the sheet. Figure shows this situation. There are plenty of good reasons to arrange the entry of data with a data form.

The Data Form's name. Choose Form from the Data menu. Click in a cell in your list. Excel's Options form is one example. But Excel provides you with a built-in Data Form that offers some of the functionality of a custom form.

An experienced developer can create and test the form shown in Figure in no time. Before looking at the individual capabilities. It's a good way to impress someone who doesn't know that Excel can do this for you. That's all that's needed to get the form you see in Figure Do not save changes when you're prompted. You can't Restore a record that you've Deleted. The Restore button is dimmed until you make a change to a value.

Type the values that you want to select in the appropriate boxes: The criteria act in the same way as with Find Prev. If you have established selection criteria by clicking the Criteria button. If you click the Criteria button. N The Purpose of Pivot Tables We're often confronted with a mass of data that probably has some interesting patterns in it — but there's so much detail that it's almost impossible to tell the forest from the trees.

Chapter 1 mentioned some of the ways that you can use lists to set up financial analyses that are more sophisticated than just looking at transactions. A page detail report of expenses by cost center is hard enough to. A pivot table is the most powerful and flexible method of data analysis and synthesis available in Excel.

One of those ways is by means of a pivot table. You can change the summary categories from. But I think you'll find. I believe that you'll be a good bit better served if you base standard Excel charts on pivot tables. There are so many advantages. You might have noticed that I'm referring here to "pivot tables. Any field in the data — for example.

PO number. That's the sort of thing that pivot tables are intended to do for you. And the pivot table can be thought of as either an ad hoc report. Pivot tables have some problems. You build a pivot table on a set of detail records.

They're a Microsoft product and Microsoft can call their products whatever they want. Your summaries can be any statistic you wish. But I don't have to compound the felony. Since Excel Although pivot charts have some useful features. I'm a big fan of using pivot tables to analyze information such as financials and operational data. Chapter 1 discussed data summaries. Data Summaries A data summary isn't part of how the structure of a pivot table is defined.

Notice that the pivot tables shown in Figures and don't differ in structure. The main difference between the two pivot tables is that one shows sales dollars as sums. In ordinary English. This pivot table shows the sum of the sales for each region.

It is the way that you choose to look at the data in the pivot table. I don't much care for the term "totals. Figure shows the difference due to looking at averages instead of sums. There's much more you can do with what I'll call summaries than just look at numeric totals.

The pivot table in Figure is one in which the user has chosen to look at numeric totals — the data will look familiar to you if you've worked your way through Chapter 1's material on lists. They both show information about sales dollars according to sales region.

Sometimes you'll be interested in the average of the sales for each region or product. Besides sums and averages. In the context of reporting sales figures on a region-by-region basis. The number of numeric values found for each region. The largest or the smallest numeric value that Excel finds. The product of all the values Excel finds for a given region. The number of values that Excel finds in the data source.

Continuing the current example. This summary does not distinguish between numeric and other types of values. The structure of the pivot table is unchanged by showing averages rather than sums. But if you have a mix of numeric values and other types of values in the same field — which is the only reason for using Count Nums — then there is some question whether you've defined the field properly.

The standard deviation of.

+ Best Excel Books - Learning Spreadsheets - Excel TV

There's an argument for getting these summaries if you're developing a confidence interval on sales for each region. Not to say that one doesn't exist. Row Fields A pivot table's row field contains a different value in each row.

A pivot table can handle thousands of items the upper limit has increased with every release since Excel There's another type of field that you can put in a pivot table called a column field. What does make a difference to a pivot table's structure is how you use row fields and column fields.

But it's a stretch. These data summaries apply only to the field that you choose to treat as a data field. Var is the square of StDev. We've established that the data summary you choose doesn't determine the structure of the pivot table. Instead of a different item in each row. The values you see in cells A3: A6 are called items. Both are measures of the variance of a set of numbers.

Virtually all of what this section says about row fields applies to column fields as well. It's possible to argue that these summaries would be useful if you're working on an Analysis of Variance.

A pivot table's structure consists not only of data fields. In Figures and In theory. Figure shows a pivot table that summarizes Sales by Region and by Product. I'll get into this issue more in the section on building pivot tables.

With the Regional subtotals. Notice that Region now has subtotals. Sorting a Row Field's Items Many of the tasks you handle with values that are stored on the worksheet in the normal fashion can also be done with data in a pivot table. The automatic subtotals are for the outer row field or fields in Figure Figures and show how this is done.

Figure gives an example. To sort values in. You can take care of several pivot field tasks by starting with Field Settings. But you usually have to take a somewhat different route if you're using a pivot table's fields. One example is sorting. You can. But you take a different approach. Do not select a data field first if you want to set sorting or top ten options. Figure shows your options as to sorting and top ten settings. The sorting options have these effects: You can drag a row field item up or down into a different row or a column field item left or right into a different column.

The top ten options are the same as those described in Chapter 1. The Using Field dropdown is enabled if you select the Ascending or Descending sort. Click the Advanced button. The associated data field items will follow their row field item. Select the options you prefer and click OK. Select Field Settings from the shortcut menu. All you have to do is click a row field item. The PivotTable Field dialog box appears. Right-click any cell in a row field or in a column field or page field.

Excel will sort by the data field's grand total across columns. Excel adds it to the pivot table in ascending row field order but not immediately: To summarize your sorting options: The Using Field dropdown is disabled unless you choose the Ascending or the Descending sort option. If you choose to use the data field as the sort key. The row field items will sort themselves automatically in ascending order.

You can also sort a pivot table by selecting a row field item and choosing Sort from the Data menu. If you add a new record to the pivot table's data source. This option has the same effect as choosing Ascending. Northwest and Southeast. Grouping Row Values What if you want to use a numeric variable as a row field?

So far. See the section named Grouping on a Purely Numeric Field. What actually happens is that the numeric values are converted to text labels such as It's unfortunate. There are other grouping methods available for a numeric row field.

The solution is to group on the numeric field Sales so as to create categories of values. But Sales is a numeric variable. The first pivot table's analysis is completely redundant. Figure has another pair of. Figure shows a pivot table with the row field Sales first ungrouped.

And you can combine the grouping factors: There are plenty of ways to aggregate dates and times — for example. There's no help for it. Excel will display the error message Cannot Group That Selection. One problem with the grouping feature is that it can't handle missing data. If you have dates as your row field's items.

If you do either one of those. When you do so. The pointer turns to crossed double-arrows. The term "pivot table" is more marketing hype than functional description. Release the mouse button. To pivot the table as shown in Figure Move your mouse pointer over cell A2. Figure is shown only to introduce the notion of pivoting a pivot table. Excel and Microsoft insist on the term "pivottable" without the space. Hold down the mouse button and drag the Region label to the right of cell A1. Figure shows a pivot table.

If you're looking up a pivot table's attributes in a Help document. The second pivot table is identical to the first. You would seldom create a pivot table with a row field and then pivot the table so that it became a column field unless you're using Excel 97 and you want to group a column field: In fact. I've had reason to pivot a table. On rare occasions. That structure can be more informative visually. January's dollars appear in Column B. Column Fields Column fields give you the same functionality that you get with row fields: It's true that pivoting a table has limited usefulness.

The blank cells represent missing data: February's in Column C. The choice of whether to orient a field as a row field or a column field is usually a matter of personal preference. March's in Column D and so on. We're accustomed to seeing dates and times progress from left to right in tables and charts. Your figures could be in rows instead of columns. Another reason to orient a field as a column rather than as a row is that you might have two fields to show.

Figure shows an example of three pivot. You can make a pivot table as complicated as you wish. A lot of good research done during the s showed that a user's ability to mentally process the data in a table plummets with every added layer of complexity.

But always keep in mind the needs and capabilities of your clients. Page Fields A page field in a pivot table is a structural element. But its use is conceptually different: All you have to do to change from one item to another is use the page field's dropdown arrow see Figure Each has a page field.

As I mentioned in Chapter 1. Select the item you want to filter for and then click OK. They make it very easy for you to focus on one item of a field. You can use more than one page field at once. You use a page field to filter for only those records that belong to a particular item in that field. Some clients appreciate this approach. I have several clients who want to see financials.

The reason is discussed later in this chapter.. On each worksheet is a pivot table and a chart based on that pivot table that shows the expenses for that month. The page fields act as though they were connected by ANDs. So we provide an Excel workbook for each fiscal year. In my own business. The simplest way is to start with a list. Building Pivot Tables Now that you've looked at some of the features of pivot tables.

To build a pivot table from that list. This step isn't necessary but it makes things easier. You need a list structure if you want to build a pivot table from figures on a worksheet. Click any cell in the list. If you begin by selecting a cell in the list. Verify the address and click Next to go the third and final step of the wizard.

But see a Tip on that topic later in this chapter. You'll often want a pivot table to appear on a new worksheet. Excel finds its boundaries for you. Click Next to see the wizard's second step. For this example. In that case: When the wizard closes you see the table schematic and the field list shown in Figure At this point you decide how you want the pivot table to summarize your data.

Suppose that you wanted to view the sum of sales dollars for each of four regions. Click the Layout button if you're more comfortable with the Excel 97 method of designing the pivot table. This is why it's smart to start by selecting a cell in the list. Click the Product button. The moment that you drag a field into the Data area. That's easy to do: It's usually a good idea to add row fields and column and page fields too. Creating Multiple Row Fields Earlier in this chapter specifically.

Click the Add To button. I showed a pivot table that breaks down Sales dollars by Region and by Product. Excel assumes that you're through designing the table and it removes the schematic from the worksheet.

The schematic for the pivot table appears on the worksheet prior to Excel Click the None button under Subtotals. Unless you've customized that toolbar. Dealing with Subtotals Referring again to Figure If you don't want those subtotals. When the mouse pointer grows a stylized red X. The PivotTable Field dialog box shown in Figure appears. Select any Region item. Some of the buttons in the dialog box differ if you start by selecting a Data field.

This approach. I mentioned that you can cause a pivot table to group numeric values into categories that are useful for analysis. Automatic in this context means that if the Data field is exclusively numeric. I often look at the count of various items by using something such as Product as a row field and also as a data field.

When you choose the Custom subtotal button. If you are going to base an Excel chart on a pivot table that's different from building a pivot chart. Grouping Numeric Fields Earlier. Using the list shown earlier in Figure Grouping on a Purely Numeric Field Suppose that you want to take a look at the number of sales a company makes.

This section shows you how to do that. I don't think you're going to have much use for this. It helps to remember that you group those values after you've built the pivot table. If there's even one text value in the Data field. You'll see the Grouping dialog box shown in Figure Excel figures the minimum and maximum values for you.

The quickest move now is to accept the default values and click OK. If you do. Right-click any cell in the row field. You can learn more if you take these steps: This sort of analysis gives you a way to determine where most of the sales are coming from. If the checkboxes are filled. From the shortcut menu. This gets rid of the decimal points in the row field.

My tendency with this data would be to set the Minimum to zero and the Maximum to Because the Sales Date field is measured by day in the underlying list. Figure shows a pivot table with Sales Date as the row field. Grouping on a Date or Time Field Things are somewhat different if you group a row field that shows dates or times. Cleaning up the automatic groupings improves readability. Excel recognizes the row field as a date field and shows you a different Grouping dialog box see Figure Figure shows the result of grouping by months.

You can select more than one grouping level: Choose one or more grouping levels and click OK. You can group a row field or a column field. Excel enables the Number of Days spinner if you choose Days from the listbox. Once the cache is refreshed. The data that the pivot table summarizes is stored in the cache. When you add data to your underlying data source. The presence of the cache dates back to the s. In a sense. The Data Cache Every pivot table has a supporting component called the data cache.

But if you want to know a bit more. What's in the Cache? If you've used pivot tables before. At the time. That's about as much as you really need to know about the cache: Unless you're thirsty for more information.

The cache is useful largely because it stores the data in a format that the pivot table can use quickly and efficiently. You refresh a pivot table by selecting one of its cells and choosing Refresh Data from the Data menu you can also use the pivot table toolbar.

Things are different with pivot tables. When you refresh a pivot table's data. This is different from Excel's normal behavior. Excel had to rebuild the cache. Excel responds by inserting a new worksheet and writing all the original. The underlying list is not needed for that kind of operation. Sum to Count. The underlying data in the list might be confidential on a record-byrecord basis.

Because all the data's in the cache. There are excellent reasons to keep the cache in the file. Once you've built the pivot table from the list.

You can clear the range that it occupies. So Excel tried to offer some ways for you to save space. Discarding the Underlying Data Suppose that you build a pivot table from an Excel list. Internal disks are now much. You'd prefer not to have that list hanging around so that people can view it. One of those ways was and still is to save a workbook containing a pivot table without the cache. Internal disks tended to offer only a few megabytes of capacity.

How the Cache Helps Beyond being just a place to store a pivot table's underlying data. It's the old tradeoff: Does it feel like you're taking a chance by throwing out the underlying data? Are you concerned that your data is floating away with the bathwater?

Don't stress: This made for a smaller workbook file. Select that item and be sure that the Enable Drill to Details checkbox is filled. For this to work. Right-click any cell in the pivot table. To get rid of the cache. If you don't see the Grand Total cell. Excel can do that because the cache has all the necessary information: If you right-click a cell in a pivot table. All this means is that if you already have one pivot table in the active workbook. Another advantage is that if you build one pivot table based say on a worksheet list.

You can make your task easier. That one refresh is enough to update the cache that's shared by all the pivot tables. In practice. So you might arrange to show the same data. You won't have to worry about getting records into one pivot table. Click the Copy to Another Location option button. The Copy to box becomes enabled. Click in the Copy to box, and then click in the cell where you want the filtered list to begin.

Fill the Unique Records Only checkbox. You'll get the result shown in Figure Figure The list's range is filled in automatically if you start by selecting a cell inside the list. If you're copying the result into columns C and D and there's anything anywhere in those columns, it will be lost and you can't Undo the results of the operation. NOTE: Your new location the location to which you want Excel to copy the filtered data must be on the same worksheet as the original list. As Figure showed, there is at least one record in your list that's supposed to be grouped with other Southeast region records, but it isn't; the trailing blank tells Excel that it's a different region.

Chapter 1 Using Lists in Excel 21 Now that you know that there is such a record or records , you can take a couple of simple steps to find it. Probably the quickest is to start by sorting your list on Region the record with the trailing blank after "Southeast" will sort to the bottom of other Southeast records.

However, you dont yet know how many such records there are. So, after sorting on Region, you might want to take these steps: 1. Select a cell that's outside the list, but in the same row as the first Southeast record which we'll assume is in cell A LEN stands for length.

In this case, it should return 9, the number of characters in Southeast. Autofill the LEN formula down until you reach the row with the final Southeast value. Edit any versions of the Southeast value where the LEN function does not return 9. In particular, a value with a trailing blank would return nine for the visible characters plus one for the trailing blank.

How Excel decides that records are unique is partly a function of how many columns are in the list that you filter. Using the data in the present example: If your list consists only of Region, there will be four unique records one for each region.

Of course, if you had another region, say Central, you'd get five unique records. If your list consists of Region and Year, you'll have up to twenty unique records: four regions times five years.

If there's no record for a given year in a given region, that maximum of twenty will drop to an actual of nineteen. Finally, if your list consists of Region and Revenue, it's likely that your list of unique records will be identical to your original list except in the unlikely situation where two or more regions have exactly the same revenue.

But in that case you'll want to specify some criteria for the filter otherwise, your filter will trivially return all the records in your list.

There are two general steps for setting up criteria for Advanced Filter: Choose a range of cells at least one column away from your list. In the first row of that range, put the names of fields in your list that you want to filter on.

This is the first row of your criteria range. Below the name of each field in the criteria range, enter the values that you want to keep: that is, the values that you want to filter for. Figure shows an example. Figure You can avoid typing errors in the criteria range by copying and pasting from the original list. In Figure , the criteria range is F1:G2. The names of the fields in the list that Excel should use as criteria for filtering are in F1:G1. The values of those two fields that Excel should filter for are in F2:G2.

These two criteria fields are treated as if they were connected by an AND; Return any records where the Region is Northwest and the Year is The filtered list has been copied to another location, starting in cell I1. Chapter 1 Using Lists in Excel 23 You'll have less use for an implied OR connector in a criteria range, but here's how to make one. Suppose, strangely, that you want to return any records that are either from the Northwest region or from year Put those two criteria in the same columns as before, but in different rows.

Figure shows how to do this. Figure The filtered list is copied to another location, to avoid hiding rows as AutoFilter does.

Figure showed the filtered list copied to a new location. If, instead, you choose to filter the list in place, Excel hides the rows that don't conform to your criteria, just as with AutoFilter.

The easiest way to get those rows back is to choose Data Filter Show All. Managing a List There's a way of managing lists that first showed up in Excel It offers little you can't do in earlier versions, but it does bring some existing capabilities together in one place.

If you're new to lists, that can be handy. Click any cell in the list and choose List from the Data menu. You'll see a shortcut menu that has only one useful item: Create List. As soon as you select that item, you see the window shown in Figure Figure If you take the advice given in this chapter about setting up lists, your list will have headers.

Confirm the range address of your data, and if necessary correct it you can drag through the correct range on the worksheet. Accept or correct Excel's guess about whether your list has headers, and click OK. You'll see the result shown in Figure Figure shows that Excel automatically adds the following elements to your list: Filter drop-downs so you don't have to select AutoFilter to get them A border around the outside of your list.

This border might help you analyze the data more effectively, but I can't think how. A row where you can enter a new record. You can do this at the bottom of any list, but this row has an asterisk. Chapter 1 Using Lists in Excel 25 List headers, if you didn't supply them yourself. The headers get the names Column1, Column2, Column3 and so on, so it's a good idea to specify them before you formally create the list.

A total row, which you can toggle on-and-off it's off at first. This can be a time-saver, so let's look at it a little more carefully. Figure Rows 12 through 21 have been hidden to make room for special rows at the bottom of the list. First, though, note that some aspects of the list disappear when you activate a cell outside the list.

The AutoFilter drop-downs disappear, and the border around the list, while it stays there, is no longer bold, and the new-record row the one with the asterisk disappears. Handling the Total Row So does the List toolbar disappears, that is.

Other books: FOOD FOR FIFTY PDF

It was shown in Figure To get at the List toolbar, just click inside the list. Figure showed the toolbar's Toggle Total Row button. If your list has a column with at least one numeric value, that column defaults to Sum as its total.

A column that contains no numeric value has no default total. However, you can set it to show Count as its total by following these steps: 1. Click the cell that would contain a total if its column were not all text values. In Figure that cell could be B33 or C A drop-down appears immediately at the right side of the cell. Click the drop-down to display the totals that are available to you.

If you want to know the number of text values in that column of the list, choose Count. By default, the left-most column in your list has the word Total in the Total Row. You can override this with the cell's drop-down and choosing anything, including None, from the list. Average: Show the mean of the numeric values in the column. Because the term "average" is sometimes used to mean different things, let's be explicit: here, it means the total of the numeric values divided by the number of numeric values.

Count: Show how many values are in the column. Count Nums: Show how many numeric values are in the column. If your column contains 12, 8, B, 27, and Fred, Count Nums returns 4. Min: Show the smallest numeric value in the column. Sum: Show the total of the numeric values in the column. This is the default total for a column with numeric values.

StdDev: Show the standard deviation of the numeric values in the column. I've been using Excel for over 20 years, including the years since Excel was released, and I've never seen anyone use the Total option for standard deviation on a list.

Var: Show the variance the square of the standard deviation of the numeric values in the column. Getting the variance of a list's column is even rarer than the standard deviation. Okay, that's less than zero, but you know what I mean. Totals in the Status Bar These options go a little farther than the options you can get from the Status Bar. What's the Status Bar got to do with it? Well, first, the Status Bar is the horizontal bar at the bottom of the Excel window, the one that's normally just beneath the sheet tabs, with the word "Ready" at its left.

Figure shows the Status Bar. Figure If you put a toolbar at the bottom of the Excel window, it will separate the worksheet tabs from the Status Bar. The Status Bar can tell you some useful things. It's a quick way to tell what keyboard options you've selected. When I press 9 on my keyboard's numeric keypad, and the Excel window scrolls up a page, instead of putting 9 in the active cell, a quick glance at the Scroll Bar tells me that I forgot to turn on Num Lock.

But that's incidental to what we're talking about. Try this: 28 Excel for Accountants 1. Select a group of cells that contain some numbers cells that are adjacent and in the same column B1:B5, for example , or the same row maybe B2:F2 , or that are in a range such as CF This step isn't really necessary but it makes things a little clearer.

Right-click anywhere on the Status Bar. You'll see a menu pop up, with the names of various totals you can choose from.

Choose one of the totals say, Max. The maximum number in the group of cells you selected in Step 1 appears in the Status Bar. The same sort of thing happens if you choose any of the six options in the menu you saw in Step 2. So, what's to choose from if you want a total from a list by using the Data List command, discussed in the prior section or to see it on the Status Bar? There are two points to choose from: You don't have as many options on the Status Bar. The list totals give you the standard deviation and the variance.

Like me, you might not regard this as a compelling reason to use a list for calculating measures of dispersion. Using the Status Bar, your data don't have to be laid out in a list. The numbers can be scattered all over the sheet. If you take a notion that you want the total of revenue, inventory turns, and EBITDA, just make sure you've selected Sum for the Status Bar totals Step 3, above and then select the cells, one by one, wherever they are on the worksheet. TIP: To select several cells that are not adjacent, hold down the Ctrl key as you click in the cells.

Getting Rid of a List By "getting rid of a list," I don't mean the data; just the bells and whistles that Excel adds when you use Create List. On either the List toolbar or in the menu you get by choosing Data List, you'll find a Convert to Range command.

All this item does is remove the drop-down arrows, border, and so on items it added when you used Data List Create List. Chapter 1 Using Lists in Excel 29 Using the Data Form In Excel, a data form is a special kind of window that opens in response to something you do, such as clicking a custom button.

The data form pops up and someone starts entering data. When he or she is through, the data is saved on a worksheet. Figure shows this situation. Figure A moderately experienced developer can develop a form like this in just a few minutes.

What you see in Figure is a custom data form. It's one that someone experienced in Excel constructs and ties to a worksheet, so that when the user hits the OK button, Excel takes the data in the form and writes it to the sheet. There are plenty of good reasons to arrange the entry of data with a data form. For example: Checking the data on the form for validity Displaying some fields or some records for certain users, and other fields or records for other users usually in a context that requires different levels of security 30 Excel for Accountants Situations where there are so many fields that it's clumsy to display them all on one form and several tabs are needed.

Excel's Options form is one example. An experienced developer can create and test the form shown in Figure in no time. But Excel provides you with a built-in Data Form that offers some of the functionality of a custom form. Figure shows how the Data Form looks with a list Ive used extensively in this chapter. In this case, because the custom form in Figure is pretty rudimentary, the Data Form in Figure has much more functionality than does the custom form.

Figure The Data Form's name, on its title bar, is by default the same as the name of the active worksheet. Before looking at the individual capabilities, notice how difficult it is to create the form: 1. Click in a cell in your list. Choose Form from the Data menu. That's all that's needed to get the form you see in Figure It's a good way to impress someone who doesn't know that Excel can do this for you.

Chapter 1 Using Lists in Excel 31 Now, here's what you or your data entry person can do with the data form: You can tell how many records are in your list, and which record is active, by looking just above the New button.

Move through the records using the scroll bar just left of the buttons.