Category Archives: Uncategorized

Custom Number Formats

Number formats can specify one format for positive values, another for negative

values, a third for zero values, and even a fourth format for text in the

cells. In such complex formats, the format codes for positive values come

first, followed by the codes for negative values, and a semicolon separates

each group of codes. Any format codes for how to handle zeros and text in

a cell come third and fourth, respectively, in the number format, again separated

by semicolons. If the number format doesn’t specify special formatting

for negative or zero values, these values are automatically formatted like

positive values. If the number format doesn’t specify what to do with text,

text is formatted according to Excel’s default values.

 

Date number formats use a series of abbreviations for month, day, and year

that are separated by characters, such as a dash (—) or a slash (/). The

code m inserts the month as a number; mmm inserts the month as a threeletter

abbreviation, such as Apr or Oct; and mmmm spells out the entire

month, such as April or October. The code d inserts the date as a number;

dd inserts the date as a number with a leading zero, such as 04 or 07; ddd

inserts the date as a three-letter abbreviation of the day of the week, such as

Mon or Tue; and dddd inserts the full name of the day of the week, such as

Monday or Tuesday. The code yy inserts the last two digits of the year, such

as 05 or 07; yyyy inserts all four digits of the year, such as 2005, 2007, and

so on

 

Excel – General

Creating Custom Lists for AutoFill

File – Options – Advanced – Edit Custom Lists

Flash Fill

Enter a Formula in several cells – highlight cells, type formula in formula bar, press CTRL+Enter

SUM argument box for several ranges

ISNA

=IF(ISNA(+VLOOKUP(+A5,NBV,2,FALSE)),0,+VLOOKUP(+A5,NBV,2,FALSE))

Returns 0 if lookup returns #N/A

=IFERROR(+INDEX(‘Company Table’!B:B,MATCH(+’Borrowing(Interest Expense) (2′!E13,’Company Table’!J:J,0)),”99999″)

Quick Analysis

Custom Styles

Conditional Formatting

Paste Special – No Borders & Transpose

Clear button

AutoSelect

Select all cells in a table – click first cell, hold shift key while double clicking right or bottom edge of cell

 

Keystrokes for Moving the Cell Cursor

Home = Cell in Column A of the current row

Ctrl + Home = A1

Ctrl + End = last cell in active area of the worksheet

Moving Between Sheets

Ctrl + Page Down = move one sheet left

Ctrl + Page Up = move one sheet right

The Excel REPT Function

Basic Description

The Excel Rept function returns a supplied text string, repeated a specified number of times.The format of the function is :

REPT( text, number_times )

Where the arguments are as follows :

text The string to be repeated
number_times The number of times that the supplied text is to be repeated. Note :

·         If number_times is zero, the function returns an empty string

·         If number_times is a decimal, it is truncated to an integer

The resulting text string must be no longer than 32,767 characters. If it is longer than this, the Rept function returns the Excel #VALUE! error.

Rept Function Examples

The following spreadsheet shows several examples of the Excel Rept function. The format of the function is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right.

REPT

Note that in the above examples:

  • In cell B2, the number_times argument is set to 0, so the Rept function returns the empty string
  • In cell B4, the the ‘&’ operator has been used with the Rept function, to join together different text strings

NUMBERVALUE function

This article describes the formula syntax and usage of the NUMBERVALUE function in Microsoft Excel.

Description

Converts text to a number, in a locale-independent way.

Syntax

NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

The NUMBERVALUE function syntax has the following arguments.

  • Text    Required. The text to convert to a number.
  • Decimal_separator    Optional. The character used to separate the integer and fractional part of the result.
  • Group_separator    Optional. The character used to separate groupings of numbers, such as thousands from hundreds and millions from thousands.

Remarks

  • If the Decimal_separator and Group_separator arguments are not specified, separators from the current locale are used.
  • If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
  • If an empty string (“”) is specified as the Text argument, the result is 0.
  • Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, ” 3 000 ” is returned as 3000.
  • If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value.
  • If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
  • If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value.
  • If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value.
  • If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula. For example, =NUMBERVALUE(“9%%”) returns the same result (0.0009) as the formula =9%%.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula Description Result
=NUMBERVALUE(“2.500,27″,”,”,”.”) Returns 2,500.27. The decimal separator of the text argument in the example is specified in the second argument as a comma, and the group separator is specified in the third argument as a period. 2500.27
=NUMBERVALUE(“3.5%”) Returns 0.035. Because no optional arguments are specified, the decimal and group separators of the current locale are used. The % symbol is not shown, although the percentage is calculated. 0.035

Sum the Same Cell in Multiple Microsoft Excel Worksheets

Few people know how to do this, but once you learn, you’ll never forget.

Suppose you have twelve worksheets named January through December. You want to sum the total of cell A2 of all these worksheets on a sheet called Summary.

First, make sure your Summary worksheet is not amidst the others. In other words, make sure it appears at the far right or far left of the monthly worksheets. Whatever you do, you’ll want all the worksheets you’re summing to be side-by-side, with no “foreign” worksheets in between.

Then, just write your formula as shown below.

=SUM(January:December!A2)

Suppose your worksheets aren’t in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you’re constantly adding worksheets, so the beginning and end worksheet names may change?

In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet “First”. Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet “Last”. You can even hide these worksheets!

Then, your formula will be:

=SUM(First:Last!A2)

What if you add another worksheet between the first and last worksheet you use? Nothing! It’ll add the cells in the new worksheet, too. If you don’t want the values of a certain worksheet added, keep the worksheet outside of between the first and last worksheets you use in your formula.

(space) Intersection operator that produces one reference to cells in common with two references

=SUM(C3:C6 C3:E6)

Removing Errors from the Printout

What if you don’t have the time to trap all the potential formula errors or track them down and eliminate them before you have to print out and distribute the spreadsheet? In that case, you may just have to remove the display of all the error values before you print the report.

To do this, click the Sheet tab in the Page Setup dialog box opened by clicking the Dialog Box launcher on the right side of the Page Setup group on the Page Layout tab. Click the Sheet tab in the Page Setup dialog box and then click the drop-down button attached to the Cell Errors As drop-down list box. The default value for this drop-down list box is Displayed, meaning that all error values are displayed in the printout exactly as they currently appear in the worksheet. This drop-down list also contains the following items that you can click to remove the display of error values from the printed report:

✦ Click the <blank> option to replace all error values with blank cells.

✦ Click the — option to replace all error values with two dashes.

✦ Click the #N/A option to replace all error values (except for #N/A entries, of course) with the special #N/A value (which is considered an error value when you select the <blank> or — options).

Blanking out error values or replacing them with dashes or #N/A values has no effect on them in the worksheet itself, only in any printout you make of the worksheet.

Excel Formulas

Zooming In and Out

To quickly return to 100% (normal) magnification in the worksheet after selecting any another percentage, all you have to do is click the bar in the center of the Zoom slider on the Status bar or click the 100% button on the View tab of the Ribbon.

Splitting the Worksheet into Windows

To split a worksheet into two (upper and lower) horizontal panes, you simply position the cell pointer at the place in the worksheet where you want to split the worksheet and then click the Split button on the Ribbon’s View tab

Fixed Headings with Freeze Panes

Getting your comments in print

When printing a worksheet, you can print comments along with worksheet data by selecting either At End of Sheet or As Displayed on Sheet on the Comments drop-down list on the Sheet tab of the Page Setup dialog box. Open this dialog box by clicking the Dialog Box launcher in the lower-right corner of the Page Setup group on the Ribbon’s Page Layout tab (Alt+PSP).

Name that formula!

Cell names are not only a great way to identify and find cells and cell ranges in your spreadsheet, but they’re also a great way to make out the purpose of your formulas. For example, suppose that you have a simple formula in cell K3 that calculates the total due to you by multiplying the hours you work for a client (in cell I3) by the client’s hourly rate (in cell J3). Normally, you would enter this formula in cell K3 as =I3*J3 However, if you assign the name Hours to cell I3 and the name Rate to cell J3, in cell K3 you could enter the formula =Hours*Rate I don’t think there’s anyone who would dispute that the formula =Hours*Rate is much easier to understand than =I3*J3. To enter a formula using cell names rather than cell references, follow these steps (see Chapter 2 to brush up on how to create formulas):

1. Assign range names to the individual cells as I describe earlier in this section. For this example, give the name Hours to cell I3 and the name Rate to cell J3.

2. Place the cell cursor in the cell where the formula is to appear. For this example, put the cell cursor in cell K3.

3. Type = (equal sign) to start the formula.

4. Select the first cell referenced in the formula by selecting its cell (either by clicking the cell or moving the cell cursor into it). For this example, you select the Hours cell by selecting cell I3.

5. Type the arithmetic operator to use in the formula. For this example, you would type * (asterisk) for multiplication. (Refer to Chapter 2 for a list of the other arithmetic operators.)

6. Select the second cell referenced in the formula by selecting its cell (either by clicking the cell or moving the cell cursor into it). For this example, you select the Rate cell by selecting cell J3.

7. Click the Enter button or press Enter to complete the formula. In this example, Excel enters the formula =Hours*Rate in cell K3.

Naming constants

Certain formulas use constant values, such as an 8.25% tax rate or a 10% discount rate. If you don’t want to have to enter these constants into a cell of the worksheet in order to use the formulas, you create range names that hold their values and then use their range names in the formulas you create. For example, to create a constant called tax_rate (of 8.25%), follow these steps:

1. Click the Define Name button on the Ribbon’s Formulas tab or press Alt+MMD to open the New Name dialog box.

2. In the New Name dialog box, type the range name (tax_rate in this example) into the Name text box. Be sure to adhere to the cell range naming conventions when entering this new name.

3. (Optional) To have the range name defined for just the active worksheet instead of the entire workbook, click the name of the sheet on the Scope drop-down list. Normally, you’re safer sticking with the default selection of Workbook as the Scope option so that you can use your constant in a formula on any of its sheets. Only change the scope to a particular worksheet when you’re sure that you’ll use it only in formulas on that worksheet.

4. Click in the Refers To text box after the equal to sign (=) and replace (enter) the current cell address with the constant value (8.25% in this example) or a formula that calculates the constant.

5. Click OK to close the New Name dialog box.

After you assign a constant to a range name by using this method, you can apply it to the formulas that you create in the worksheet in one of two ways:

Type the range name to which you assign the constant at the place in the formula where its value is required.

Click the Use in Formula command button on the Formulas tab (or press Alt+MS) and then click the constant’s range name on the drop-down menu that appears.

When you copy a formula that uses a range name containing a constant, its values remain unchanged in all copies of the formula that you create with the fill handle. (In other words, range names in formulas act like absolute cell addresses in copied formulas — see Chapter 4 for more on copying formulas.) Also, when you update the constant by changing its value in the Edit Name dialog box — opened by clicking the range name in the Name Manager dialog box (Alt+MN) and then clicking its Edit button — all the formulas that use that constant (by referring to the range name) are automatically updated (recalculated) to reflect this change.

Create From Selection – to automatically name ranges

Research