Microsoft excel accounting formulas pdf free download

Joins several text items into one text item. Rounds a number down, toward zero. Returns the individual term binomial distribution probability. Returns the one-tailed probability of the chi-squared distribution. Returns the test for independence. Returns the confidence interval for a population mean. Returns the inverse of the lognormal cumulative distribution.

Returns the cumulative lognormal distribution. Returns the most common value in a data set. Returns the normal cumulative distribution. Returns the inverse of the normal cumulative distribution. Returns the standard normal cumulative distribution. Returns the inverse of the standard normal cumulative distribution. Returns the k-th percentile of values in a range. Returns the percentage rank of a value in a data set. Returns the Poisson distribution. Returns the quartile of a data set.

Returns the rank of a number in a list of numbers. Estimates standard deviation based on a sample. Calculates standard deviation based on the entire population. Estimates variance based on a sample. Calculates variance based on the entire population. Returns the inverse of the F probability distribution. Returns a value along a linear trend. Returns the beta cumulative distribution function.

Returns the inverse of the cumulative distribution function for a specified beta distribution. Returns covariance, the average of the products of paired deviations. Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.

Returns the exponential distribution. Returns the F probability distribution. Returns the gamma distribution. Returns the inverse of the gamma cumulative distribution. Returns the hypergeometric distribution. Returns the negative binomial distribution. Calculates variance based on the entire population, including numbers, text, and logical values. Returns the one-tailed probability-value of a z-test. Returns a key performance indicator KPI name, property, and measure, and displays the name and property in the cell.

RReturns a member or tuple in a cube hierarchy. Returns the value of a member property in the cube. Returns the nth, or ranked, member in a set. Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.

Returns the number of items in a set. Returns an aggregated value from a cube. Extracts from a database a single record that matches the specified criteria. Adds the numbers in the field column of records in the database that match the criteria. Returns the average of selected database entries.

Counts the cells that contain numbers in a database. Counts nonblank cells in a database. Returns the maximum value from selected database entries. Returns the minimum value from selected database entries. Multiplies the values in a particular field of records that match the criteria in a database. Estimates the standard deviation based on a sample of selected database entries.

Calculates the standard deviation based on the entire population of selected database entries. Estimates variance based on a sample from selected database entries. Calculates variance based on the entire population of selected database entries. Returns the serial number of a particular date. Converts a date in the form of text to a serial number. Converts a serial number to a day of the month.

Converts a serial number to an hour. Converts a serial number to a minute. Converts a serial number to a month. Returns the serial number of the current date and time. Converts a serial number to a second. Returns the serial number of a particular time. Converts a time in the form of text to a serial number. Converts a serial number to a year. Calculates the number of days between two dates based on a day year. Returns the serial number of the date that is the indicated number of months before or after the start date.

Returns the serial number of the last day of the month before or after a specified number of months. Returns the number of whole workdays between two dates. Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Converts a serial number to a day of the week. Converts a serial number to a number representing where the week falls numerically with a year. Returns the serial number of the date before or after a specified number of workdays.

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days. Returns information about the formatting, location, or contents of a cell. Returns TRUE if the value is blank. Returns TRUE if the value is any error value.

Returns TRUE if the value is not text. Returns TRUE if the value is a number. Returns TRUE if the value is text. Returns a number corresponding to an error type. Returns information about the current operating environment.

Returns TRUE if the number is even. Returns TRUE if the value is a logical value. Returns TRUE if the number is odd. Returns TRUE if the value is a reference. Returns a value converted to a number. Returns a number indicating the data type of a value. Specifies a logical test to perform. Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Reverses the logic of its argument.

Returns the logical value TRUE. Looks up values in a vector or array. Returns a reference as text to a single cell in a worksheet. Returns the column number of a reference. Returns the number of columns in a reference. Looks in the top row of an array and returns the value of the indicated cell. Uses an index to choose a value from a reference or array.

Returns a reference indicated by a text value. Looks up values in a reference or array. Returns a reference offset from a given reference. Returns the row number of a reference. Returns the number of rows in a reference. Looks in the first column of an array and moves across the row to return the value of a cell. Chooses a value from a list of values. Returns data stored in a PivotTable report.

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. Returns the transpose of an array. Returns the number of areas in a reference. Checks to see if two text values are identical. Converts text to lowercase.

Capitalizes the first letter in each word of a text value. Removes spaces from text. Converts text to uppercase. Returns the character specified by the code number. Removes all nonprintable characters from text. Returns a numeric code for the first character in a text string. Formats a number as text with a fixed number of decimals. Extracts the phonetic furigana characters from a text string. Repeats text a given number of times. If you didn't find any accounting template here, please use of our suggestion form.

Accounts Payable Template is a ready-to-use template in Excel, Google Sheets, and Open Office Calc that helps you to easily to record your payable invoices all in one sheet. Just download the template and start using it entering by your company details. We have created a simple and easy Accounts Receivable Template with predefined formulas and formating.

The template shows invoice outstanding as well as total outstanding accounts receivable at any given point of time. Enter the transaction on the debit or credit side and it will automatically calculate the cash on hand for you. These templates can be helpful for accounting professionals like accountants, accounts assistants, small business owners, etc.

Ready-to-use Invoice templates in Excel, Google Sheets, and Open Office Calc in different formats according to a different industry, different languages, and different currencies.

This will help you to issue an invoice to your customer for the goods or services provided. This template can be used for reimbursement purposes for business trips and can also be helpful to analyze expenses about a specific department or a project.

Petty Cash Book is a ready-to-use template in Excel, Google Sheets, and Open Office Calc to systematically record and manage your petty or small daily routine payments. Inventory Control template is a document that keeps track of products purchased and sold by a business.

It also contains information such as the amount in stock, unit price, and stock value, etc. Furthermore, while preparing Profit and Loss Accounts for a company we require the cost of Inventory.

This is also be derived using Inventory Control Template. Purchase Return Book Template is a ready-to-use template in Excel, Google Sheets, and OpenOffice that helps you to easily manage and record purchase return transactions.

As soon as you group one by month, the other auto- matically groups itself by month. And when you group the other pivot table by quar- ter, the first one follows suit.

The solution in this sort of situation is to base both pivot tables on the same data source, but to avoid basing one pivot table on the other.

You can choose Yes to base it on the existing table, and No to keep the tables separate. If you want to base two or more pivot tables on the same data, but group them differently, you should choose No, so as to keep them separate.

Now you will be able to choose different grouping levels for the different tables. Page Field Problems You can safely ignore this section if you're using Excel or a later version. Many companies, however, even in , continue to use Excel And many of them have very good reasons for living in the past. The conversion of hundreds — to say nothing of thousands — of workstations from Office 97 to a later version is a complex and expensive project.

If you are using Office 97, be aware that there is a problem with page fields. If you have several pivot tables on different worksheets and refresh them periodically, it can happen that two or more pivot tables wind up with the same selection in their page fields. Therefore, you might have to deal with a pivot table on a worksheet named January that selects January records, and another pivot table on a worksheet named February that also selects January records.

This can be embarrassing, and the solution is the same as suggested in the prior section: base all the pivot tables on the same data source, but don't base them one another. The final major section in this chapter, Using Named Ranges as Data Sources, has a recommendation that makes it much easier to base several pivot tables on the same data source. Refreshing the Cache Automatically Later in this chapter, in the section named Building and Refreshing a Pivot Table From a Dynamic Range, this book describes how you can arrange for a pivot table's underlying data range to redefine itself automatically.

As new data on, say, costs comes into the workbook, you don't need to tell pivot tables to look further into the worksheet to find the most complete set of information. But redefining the reference to the data source is only half the job.

The other half is getting the pivot table to refresh itself based on that new data. One is by hand: all you need to do is right-click any cell in the pivot table and choose Refresh Data from the shortcut menu.

But you have to remember to do that; it's all too easy to assume that the pivot table contains the most current information and forget that you haven't refreshed it. So consider doing something to refresh the table automatically. Refreshing the Pivot Table When You Open the Workbook One way to arrange for an automatic refresh is to select an option that forces a refresh.

If you right-click a cell in a pivot table, a shortcut menu appears, and one of its items is Table Options. Select that item to see the dialog box shown earlier in Figure Clearly, there are many options you can set using this dialog box.

The one that's pertinent to this section is Refresh on Open. Fill this checkbox to get Excel to refresh the pivot table when you open the workbook. If you don't, the option will keep the value it already had — and if the checkbox was cleared that's the default then the pivot table won't automatically refresh when you open the workbook.

With this option set, Excel refreshes the pivot table while it is opening the workbook, before it turns control over to the user. For most situations, that could very well be all you need. If you open the workbook that contains the pivot table only occasionally you'll want the pivot table to have refreshed itself — new data could easily have been put in the workbook since you last opened it. And if all you do is take a quick look at what the pivot table displays when you open the workbook, you're in good shape.

Only in exceptional cases could you have the workbook open while a different user is updating the data source. A workbook is shared when more than one user at a time can have it open and save changes to it. You cannot edit — or even build — a pivot table in a shared workbook, not even if you're the only one who has the workbook open.

One of the other reasons is that they have a tendency to hang — to quit responding to user input — when they get fairly large. You do not want a workbook with a lot of data in it to hang. In a shared workbook, another user could easily edit a pivot table's data source, and you would not necessarily know that had happened. External Data Sources The other case occurs when a pivot table is based on an external data source. The most typical external data sources are text files, other Excel workbooks, and true databases.

You build a pivot table that's based on an exter- nal data source starting with the pivot table wizard's first step covered earlier in this chapter. You would not necessarily know that another user had updated the pivot table's data source when that source is located in a database or in a different Excel work- book.

That's why you might find useful another checkbox in the Table Options dia- log box. The checkbox, and the associated spinner, are enabled only if the pivot table is based on an external data source. You can use this checkbox and the spinner to cause Excel to refresh the pivot table as frequently as you wish from the external data source.

But use a little caution, at least: if the pivot table is based on a large amount of data, it's possible to clog up a network with frequent, possibly unneces- sary refreshes. Then base the pivot table on that database, using the External Data Source option in step 1 of the pivot table wizard. If you do this, you leverage the data management and retrieval strengths of the database and the analysis and graphic display strengths of Excel.

Using Named Ranges as Data Sources Excel has a way of referring to a collection of cells on a given worksheet. Such a collection is called a range, and it's every bit as important as a list. A range can con- sist of a single cell, such as cell D5 — in fact, a cell is a range in a formal sense. A range can also consist of thousands of cells, such as the range A1:Z Ranges are in some ways less formal than lists, and in some ways more so.

For example, you can put any sort of data in a range and orient it as you like. A list — to be a list — requires that you have field names in the first row, that each subsequent row represent a different record, and that each column contain a different field.

But ranges are much more forgiving. You'd have to be irrational to do it, but you could put a record in one row of a range, and another record in one of the range's columns. A range, in other words, has all the flexibility of the worksheet it's on as to what goes where: that's all up to you. On the other hand, a defined range has a couple of things that a list doesn't. One is a name: all defined ranges have names, like PhoneList or Q4Actuals.

The other is an address: Excel requires that an address, like D5 or A1:Z, be associated with the name of the defined range. In contrast, recall from Chapter 1 that lists don't have names, and although they occupy cells they don't have specific cell addresses. All defined ranges must have names, and those names must be associated with cell addresses. Creating a Named Range for an Aging Report One useful example, seen in Figure , is a named range for an aging report.

Here's how to create a range named AgingReport: 1. Select the cells in the range you want to name. In Figure , that's A1:D We'll ignore the closing date information in F1:G1, but of course you could include it. In the Names in Workbook box, type a descriptive name, such as AgingReport. You now have a range named AgingReport in the active workbook.

Figure There are other ways to add a named range, but this gives you the most control. There are many other ways to use a named range, and most of them have to do with using the name instead of typing a cell reference such as A1:D It's easy to forget or to miskey a cell reference; if you chose a good name for the range, such as ChartOfAccounts, it's harder to forget or miskey.

Before you start looking into using a named range as input to a pivot table, fol- lowing are a few basics about named ranges to bear in mind: Embedding Blanks The name of the range can't contain embedded blanks. It would be nice to name a range Aging Report, but Excel won't let you. Click the Close button if you want to abandon the operation it just means Cancel. Click the Add button to add the current name — the dialog box will stay open so that you can add another named range.

In Figure , you can see the Name Box above Column A — it shows the address of the active cell, A1, and it has a dropdown arrow immediately to its right. To name a range using the Name Box, select a range of cells that you want to name, click in the Name Box, type the name, and press Enter. That creates the named range.

You can also use the Name Box to go to that range: click in the Name Box, type the name of the range or click the dropdown arrow and select it from a list and press Enter. Suppose you're in cell A1 and you want to go to cell Z Click in the Name Box, type Z90, and press Enter. Another helpful feature of the Name Box is that if you have selected a named range, the name of the range appears in the Name Box. In Figure , if you had already named the range AgingReport, and subsequently selected that range, its name would appear in the Name Box.

Static and Dynamic Named Ranges The prior section described one type of named range — a static range, the type that is best known.

It doesn't move, and its dimensions don't change. Left to its own devices, a static range that consists of 24 rows and 10 columns will always have 24 rows and 10 columns. But what if you have use for a range that can grow or shrink as a function of the number of values it contains? For example, to return to the account aging report in Figure , what happens when another account becomes past due?

You add it to the range of data that's currently in A1:D23, of course, but now the data would occu- py A1:D24, and the range name still refers to A1:D If you used the range name AgingReport in an analysis, you'd miss the 23rd record in the 24th row. Notice at the bottom of its window is a box labeled Refers To. Thus, it has aspects similar to a formula in a worksheet cell. In particular, it can be calculated and recalculat- ed.

Because the name of the worksheet in this case has a blank in it between "Aging" and "Report" , Excel puts single quotes around it. This convention occurs elsewhere in Excel, such as in charts. The col- umn designations, A and D, and the row designations, 1 and 23, are each preceded by dollar signs. These dollar signs anchor the address, making it fixed, absolute, static. You can tinker with this definition of where to find the range named AgingReport. In particular, you can make the definition sensitive to the number of accounts that should be included in the named range — so that when a new account is added, the range's definition expands to include the new account.

Then the named range is no longer static, it's dynamic. If you base a pivot table on a dynamic named range, you don't need to change the range address of the pivot table's input data: it changes automatically as new data becomes available. If you use pivot tables extensively to synthesize, analyze and chart, for example, your clients' operating costs and revenue sources, the ability to get them to update their addresses automatically is huge.

You still have to refresh the pivot table. But that too can be automated, as you saw earlier in this chapter's section titled Refreshing the Cache Automatically. When you can automatically change the address of the input data and automati- cally refresh the pivot table, you're in a position to hand your client a valuable resource. Or, if you prefer to keep it to yourself, you've arranged to save yourself a lot of time and grief going forward.

In this exam- ple, A1 is the function's first argument. But there are two more arguments that we haven't looked at yet: the numerals 4 and 5. In their absence, Excel assumes one row and one column. The problem is that the range is still static. It always occupies four rows and five columns and starts in cell A1. You're about to make it dynamic, though.

Making the Range Name Dynamic It's wise to back up a moment to review the purpose of all this stuff. The problem is to get a pivot table to react automatically after new information enters the worksheet range that contains its underlying data.

The pivot table might be getting the total of revenues for each month — then, when a sales region reports its total revenue for the current month, that information goes into a worksheet list and we want the pivot table to add that revenue to its analysis. What you can do is count the number of records in the list. Suppose that the list occupies cells A1:B The list has expanded by one row. Suppose that you want the worksheet to keep track of the number of records in the list.

The reason for subtracting 1 is that one value in Column A is the list's column header — something such as the word "Month". The other is COUNTA, which counts the num- ber of values in a range of cells, regardless of whether the values are numeric or text.

You know the nature of your data better than I do. Now you're ready to define a dynamic range name. Try interpreting it piecemeal, from the inside out, and begin by assuming that you have values in the range A1:A5 and nowhere else in Column A. For the purpose of this example, it does- n't matter whether there are values in columns B through D, but as a practical matter you'd usually have values in those columns, associated with values in Column A.

Now suppose that you add new data to that range, in cell A6 and perhaps B6:D6. You've defined a dynamic range name. Enter, say, four values in A1:A4. In the Names in Workbook box, type a name such as DynamicRange. Click in the Name Box and type the name you chose in Step 3. Press Enter. Excel will select the cells defined by columns A through D, and by as many rows as you have values in Column A. Suppose you have a simple "q" in cell A That's a value in Column A, and if you're defining your dynamic range using a count of the num- ber of values in the column, you would wind up with one more row in the dynamic range than you really want.

Now, enter another value in a blank cell in Column A, and repeat steps 6 and 7, above. Excel will select one row more than it did before. Before you leave this section, be sure you follow the rationale for all this — which might well seem like a bunch of handwaving from some deranged geek.

By defining a dynamic range name, you can arrange for the range's dimensions usually, its number of rows to be automatically recalculated when new data is added.

Building and Refreshing a Pivot Table From a Dynamic Range In this chapter's section titled Building Pivot Tables, you saw how you could make ref- erence to a range of cells occupied by a list, that would serve as the underlying data for the pivot table. If you do so, Excel determines the boundaries of the list and proposes the range it occupies as the pivot table's data source.

If you do not begin by selecting a cell in the list, you must identify the range address of the list by typing it, or by dragging through the range with your mouse. If you base the pivot table on a dynamic range name, then both at the outset and later on you don't need to bother with that.

Just type the name of the range in the Range box that you see in Step 2 of the pivot table wizard, and proceed just as before. Figure starts an example of how this is done. Figure Note the defined name of the range, and what it refers to. But with a dynamic range name, it's pointless to do so because you're setting the range up so that it will redefine itself. Now take the following steps: 1. In the Names in Workbook box, type a name such as PivotData.

Click OK to get the name defined. Click Next. In Step 2 of the wizard, type into the Range box the name, perhaps PivotData, that you used in Step 1 of this list. In Step 3 of the wizard, make your own choice about where to locate the pivot table, and click Finish. The schematic appears on the worksheet, and the PivotTable Field List dialog box shows up too.

Specific instructions are in the Building Pivot Tables section, earlier in this chapter. TIP: When I'm just starting to develop a pivot table, I like to put it on the worksheet that contains the underlying data. That makes it easier to see what's going on if I get a stupid result.

If all the data you entered for Amount is truly numeric, the pivot table defaults to Sum as the data summary, and you'll get the sum of the dollars for each account. Now, to test your dynamic named range's capabilities, add a record to the list, as shown in Figure Lastly, right-click anywhere in the pivot table and choose Refresh Data from the shortcut menu.

You should see the pivot table summary update to reflect the presence of the new record. You have several tools available to help with different purposes for common sized statements: for example, you might create a common sized statement that can recalculate as new information comes to hand, or one that is static — that is, one that has values only, and no formulas. This chapter begins by discussing the rationale for common sized statements.

That may seem pretty basic, but I've met many accountants who are unfamiliar with the concepts involved. If the idea is old hat to you, by all means skip ahead to the section titled Common Sizing Income Statements.

The chapter then goes on to describe how you can use Excel to common size income statements and balance sheets, to make comparisons with earlier accounting periods or with another compa- ny or even an entire industry.

The Rationale for Common Sizing We're all so used to looking at income statements and balance sheets that are denom- inated in dollars that seeing one denominated in percentages seems a little odd.

The idea itself is pretty straightforward. Suppose that you're looking at an income statement. Many of the values shown there — especially on an income state- ment that's been structured to support management decisions — are driven by net sales.

Salaries, COGS and virtually any other variable cost, even fixed costs — in a rationally-managed company all of these rise and fall, if only eventually, with net sales. So it's sensible to look at how dollars are allocated to various cost categories as a percentage of net sales, something to which they react.

Just sending a CFO to jail for six years could easily account for the difference. You might find something of interest, or problematic — or nothing at all — but at least your attention was directed to a difference that's a little bit unusual. It would be more difficult to notice that difference if you were simply looking at raw dollars, especially if you're looking through income statements for 20 or 30 companies. With net sales or revenues all over the ballpark, it's hard to tell if any- thing's out of whack.

But when you've common sized the statements, you eliminate the effect of one principal source of variation: net sales measured in raw dollars. With every- thing on the income statement cast in terms of percent of net sales, it's much easier to make comparisons between companies.

And that, of course, puts you in a posi- tion to assess how two or more companies differ in terms of how they structure their costs. There are various sources of income statements and balance sheets, available for particular business sectors and already common sized. This is one way to compare a particular company in which you are interested with a larger number of companies in the same general line of busi- ness.

It's not necessary to limit the comparison to one company and another, or one company and an entire sector. It can be helpful to look at a comparative income statement that describes the activities of one company at different points in time, usually consecutive accounting periods. In that case, of course, you would express all costs from, say, in terms of percentage of net sales for , and all costs from in terms of net sales for Once you've removed the effect of variation in net sales, you can easily see the change in cost allocation from year to year.

Common Sizing Income Statements You have a choice, early in the process of common sizing an income statement, as to whether you want the common sized statement to be fixed or changeable. If it's fixed, that means you'll see the same percentages whether or not you get new num- bers for the original income statement: such a common sized statement stores fixed percentages.

On the other hand, if the common sized statement is changeable, you can see the percentages change as new data arrives. It's changeable because you've stored the percentages as formulas, and if you get more information about costs or sales, the common sized statement recalculates its formulas and show you updated percent- ages.

Probably, the best time to "freeze" a common sized statement is at the end of the accounting period it covers, after adjusting entries have been made and the only further change would be restatements.

Bear in mind, though, that you can always convert the statement back to formula-based common sizing by going back to the original statement. The Mechanics: Using Formulas Figure shows an ordinary income statement. It's highly simplified and con- densed for reasons of space. This section describes how to create a common sized income statement that's based on formulas, and retains them.

To convert the statement shown in Figure to a common sized statement, using Net Sales, take these steps: 1. If your workbook has a blank worksheet in it, go on to Step 2. If necessary, activate a blank worksheet and select cell C1. In this case C1 is the cell that contains the first of the numeric values in the original income statement. Type an equal sign, switch to the worksheet with the income state- ment, and click in cell C1.

Type a slash that is, the divided by sign or the division operator. Click in cell C1 or wherever the income statement has stored the net sales value. Excel returns you to the blank worksheet. If necessary, select cell C1 again. C1 depending on the name of the worksheet that contains the original income statement. Hold down the mouse button and drag the mouse pointer across the reference that's to the right of the division operator in the formula bar, to highlight it.

In this case, that's the second instance of C1 in the formula. Press the F4 key once, and then press Enter. This will convert the highlighted portion of the formula from a relative reference to an absolute reference. You could also just type the dollar signs directly into the reference, but the F4 key is easier, especially when you're using a laptop on a bumpy flight.

If necessary, activate cell C1 again. Click the Percent Style button on the Formatting toolbar. Cell C1 will now be formatted as a per- cent, and it is the percentage of net sales. Move your mouse pointer over the fill handle on cell C1. The fill handle is the dark square found on the cell's lower right corner; it is visible only on the active cell, or on the lower right cell of a multi- cell selection.

Click the fill handle, hold down the mouse button and drag down through cell C Switch back to the original income statement and select the labels in cells A1:B Select columns A, B and C by clicking on the A at the top of the first column, holding down the mouse button, and dragging right into Column C. Double-click the boundary between the A and the B, or between the B and the C. This auto-sizes each column width to match the maxi- mum width of the column's entries.

Clear the Zero Values checkbox. The option applies only to the worksheet that's active when you clear the checkbox. You now have a common sized income statement in what had been a blank sheet; the result appears in Figure Figure A common sized income statement, standardized on net sales. By default, Excel selects the next cell down. If you want to leave the active cell selected when you press Enter, clear the Move Selection After Enter checkbox.

That's why a comparative income statement can be helpful see Figure Figure Two years of data is better than one, but it's still difficult to interpret them at a glance. The income statements shown in Figure are for two consecutive fis- cal years of the same company.

That's convenient: the company tends to group its costs into the same categories from year to year, making compar- isons easier. Of course, if you wanted to structure a comparative income statement that compares one company's annuals with another's, or with a sector's, you'd proba- bly have some re-arrangement to do to bring the statements into alignment with one another.

But if the categories are well defined, this seldom poses any real problem. So, convert them to percentages, as shown earlier in Figure The steps are more complicated, but trivially so, and you can simplify the process by putting the per- centages on the original income statement's worksheet.

With a worksheet laid out as in Figure , select cell F3. With cell F3 active, click the Percent Style button. Adjust the num- ber of decimals to display by using the Increase Decimal or Decrease Decimal buttons on the Formatting toolbar.

Using the fill handle on cell F3, drag through F4:F Remove them by selecting each cell in turn and pressing Delete. Select cell H3. Using the fill handle in cell H3, fill H3 into I3. Select H3:I3. Use the fill handle in cell I3 to drag through row You will now have figures that show costs as a percent of net sales in cells H3:I Notice what's happened to the formula by the time you get to row However, the numerator adjusts, first to column D in step 6, then from row 4 to row 23 in step 7.

You can eliminate them by selecting the cells and pressing Delete, or by choos- ing not to show zero values see step 15 in the prior list of steps.

Enter the labels as shown in Figure for columns F and H. To get the labels to span columns H and I in rows 1 and 2, select H1:I2. Select H1 and enter Percentage. Select H2 and enter of Net Sales. If you want to use single or double underlines, select the cells where you want to use an underline. The result is shown in Figure Figure The common sized comparative income statement makes it easier to see what's happening to a company's cost structure over time.

Formulas Recalculate Using formulas, you can change any number in the original income statement; its companion cell on the common size income statement will adjust accordingly. The formulas in the common size statement recalculate when their precedents change.

Formats Are Copied Too In Steps 6 and 7 of the instructions presented in the preceding section, when you fill into other cells, the Percent Style format follows along with the formula.

This is typ- ical behavior in Excel. When you copy-and-paste the contents of a cell and in effect that's what you do when you use a cell's fill handle you also copy-and-paste the cell's formats. The dollar signs indicate that the row reference is absolute, and should not adjust when you copy-and-paste its formula into another cell.

The absence of a dollar sign indicates that the column reference is relative, and should adjust when you copy-and-paste its formula. These are examples of mixed references, where either the column e. Consider Combining Absolute with Relative References It is this aspect of the formula — a cell whose reference adjusts as the formula moves, paired with a cell whose reference remains fixed — that makes it so easy to replicate a formula across many different precedent cells.

You might use this tech- nique to get a cumulative total. Enter that formula into, say, B1 and then use the fill handle to drag it through B2:B5 — notice what happens to the relative reference A1.

Chapter 5 discusses this feature in more detail. So you can get a preliminary look at costs as a func- tion of net sales, but retain the capability of recalculation as precedent cells change. If you're ready to build a final common sized statement for a given period, you might prefer it to contain static values instead of formulas.

In that case, you'd take slightly different steps than are shown earlier in this chapter. There are actu- ally several ways to do this, but these steps get you there fastest. Switch to the current income statement. Click the Select All button this is the gray rectangular button just above the worksheet's row headers and just left of its column head- ers. All the cells in the income statement — in fact, all the cells in the worksheet — are selected. Switch to the new, blank worksheet.

All the values and formulas from the origi- nal income statement are pasted into the new worksheet. Switch back to the original income statement. Select the cell that contains the net sales. Switch back to the new worksheet. Select the cells that contain the original income statement. Click the Divide option, and then click OK. While the income statement range is still selected, click the Percent Style button on the Formatting toolbar. If you do, Step 12 will cause Excel to divide every one of the 16,, depending on the version you're using cells in the worksheet by the net sales value.

If you've followed all the steps to get this common sized statement, with its fixed values, as well as the steps to get the common sized statement with its formu- las, the two statements should look exactly alike. Refer back to Figure The difference between the two versions is not visible — it's hidden inside the cells, which have either formulas or values, but not both. Bear in mind that you can select all the cells in a worksheet using the Select All button. If there is an object — such as a figure or a chart — on the worksheet, it will also be selected.

This capability can be very convenient, but it can also select a lot more than you might want. The sections that follow present some other points to remember from the sec- ond exercise. This option gives you access to a variety of operations that take place as the paste occurs.

To common size a financial report, you would use the Divide option, as just illustrated. More specifically, here's what the Divide option does: it divides any numeric values that it finds in the target range by the number that was copied. Suppose that you have copied the number 2. You intend to paste-special into cells D2 and E2, which contain the numbers 6 and 9, and specify the Divide operation.

When you paste, Excel divides 6 by 2 and enters the result, 3, in D2. Notice in Figure that you have other numeric options available during the paste operation, and they work in ways that are similar to the Divide option: if you choose Add, for example, the copied value is added to any values in the target range. The other options show that you can paste formats as well as other aspects of a copied cell or range.

Those options are mutually exclusive: that is, you cannot choose both the Formats option and the Validation option in the same Paste Special operation. However, you can use Paste Special repeatedly, each time selecting a dif- ferent option. TIP: Microsoft Office applications, including Excel, use this convention: you can select only one of a group of options that are represented by radio buttons for example, the various Operation options in the Paste Special dialog box.

But you can select any or all of a group of options that are represent- ed by checkboxes for example, the Skip Blanks and the Transpose options grouped in the dialog box. Values If the copied range contains values only, it does not matter which option you choose — only values are pasted. If the copied range contains formulas, or a mix of formulas and values, formulas are pasted as formulas if you choose Formulas.

If you choose Values, formulas are first converted to their results and are then pasted as values. Converting Text Values to Numeric In operations work, I often find that I need to convert text values to numeric values.

For example, here are the names of two fire-resistant doors as shown in a hospital's equipment inventory: 7REF 7REF23 In fact, I'm usually dealing with 50 or more such values. As shown, the two names are sorted in text order: "22" in "" precedes "23" in "23". But my client's requirement involves sorting them in numeric order according to whatever numerals follow the "REF" string.

The number of characters would be different if the starting value were 7REF23, and the starting position would differ if the starting value were 17REF If A1 contains 7REF, the formula returns If A1 contains 7REF23, it returns That's all well and good, but the results of the formula are themselves text, and will still sort before The solution is to use Paste Special on the formulas.

I enter zero in a blank cell, copy it, select the range containing the formulas, and paste special, choosing to add. Adding zero to the text-results of the formulas converts them to numbers. Values and Number Formats When you paste Formats with Paste Special, you paste all formats that apply to the range you copied. So, if the copied range uses a bold font, has a border drawn around it and shows numbers as percents, then the target range gets the same format characteristics.

On the other hand, when you paste Values and Number Formats, you are past- ing only formats that pertain to how a number appears, which is a subset of the for- mats that determine how a range appears. So, pasting number formats means that you paste whether the number is shown as currency, as a date, as a phone number, as a social security number, and so on.

You do not paste other formatting aspects, such as borders, shading, font size or style, and so on. The same distinction applies to the choice of Formulas and Number Formats. You might find it quicker to avoid Paste Special entirely if the copied source contains a value instead of a formula, or if you want the formula to adjust any cell references it contains.

Skip Blanks This option is the source of some anguish among users who haven't yet found that the term "skip blanks" is ambiguous: does it mean that Paste Special will skip blanks in the copied range, or in the pasted range? Neither, really. The Skip Blanks option can't avoid copying blank cells from the copied range, because the copying occurs before you choose the Skip Blanks option.

Nor does it skip blanks in the pasted range: if it did, you could use the option only to overwrite existing data. No, what this option does is fail to overwrite data in the pasted range with a blank from the copied range.