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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s