excel mania 99

Range Name Syntax

2011-04-25T05:48:00.000-07:00

Name syntax rules:

The Name string must begin with a text character, not a number, and consists of adjacent characters.

Two words can be joined with an underscore (_). For example, to enter the Name "Excel Book", you should type Excel_Book.

You cannot use a Name that could otherwise be used as a cell reference. For example, A1 or IS2002.

Notes:

There is no limit on the number of Names you can define.

Be sure to define unique Names for a specific workbook. Defining Names that resemble Names in other sheets will only complicate your work.

Deleting a Range Name

2011-04-25T05:47:00.001-07:00

Why it is highly recommended to delete unnecessary range Names:

Large numbers of range Names makes it more difficult to locate a specific Name.

Range Names create references and unwanted links.

To find unnecessary/unwanted range Names:

1. Select a cell in a new sheet.

2. Press <F3> and click Paste List. A full list of range names and their

references is pasted into the new sheet; delete each unwanted Name.

To delete a range Name:

Press <Ctrl+F3>, select the Name, and then click Delete.

Defining a Range Name

2011-04-25T05:47:00.000-07:00

To define a range Name, use one of the following two techniques:

Type the text directly into the Name box

Select cell A1.
In the Name box, type the text, and then press <Enter>.

Define a Name using the Define Name dialog box

1. Select cell B1.

2. Press <Ctrl+F3>.

From the Insert menu, select Name and then Define.

3. Type the text in the Names in workbook box, and then click OK.

Array Formulas

2011-04-24T05:42:00.000-07:00

Understanding Arrays

For those who do not have a background in programming or mathematics, the expression Array may not be familiar.

So what exactly is an Array?

For our purposes, an Array is simply a set of values which can be stored in a formula, a range of cells, or the computer’s memory.

The size of an Array can range from two to thousands of values.

Using Arrays in Formulas

There are several different types of Arrays used by Excel when working with formulas:

An Array stored in a Worksheet in a range of cells: For example, when the SUM function sums the values stored in range of cells, it is treating those values as an Array.

An Array stored in a formula:

Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an Array of values into the SUM function arguments: =SUM(1,2,3,4,5).

Enter an Array enclosed in brackets into the formula argument. For example, use the MATCH function to return the position of the number 10 in an Array of values: =MATCH(10,{3,7,10,15,20}). The result = 3

Excel formulas create Arrays to store values:

Formulas such as SUMPRODUCT utilize computer memory to store values temporarily while calculating complicated math problems.

These values are stored in an Array.

Example:

To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the

SUMPRODUCT formula stores each multiplication product in an Array (Array size is 3) and then adds the three values from the Array.

The SUMPRODUCT formula: =SUMPRODUCT(A1:A3,B1:B3),

Result - total sales=$260.

Let Excel create an Array formula:

As explained in the previous section, many formulas create Arrays when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an Array/Arrays to store values.

Example:

Use the SUM function to return total sales (see previous example).

The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result total sales=$260.

To apply an Array formula:

Enter the formula, select the cell, press <F2>, and then simultaneously press <Ctrl+Shift+Enter>.

Printing Formula Syntax

2011-04-24T05:38:00.000-07:00

To print the formula syntax for a range of cells:

Display the formula syntax by pressing <Ctrl+‘> (the key to the left of the number 1).
Print the desired area.

Adding a Comment to a Formula

2011-04-24T05:37:00.000-07:00

To add a Comment to a formula:

At the end of the formula, add a + (plus) sign.
Type the letter N, and, in parentheses, type your Comment in quotation marks.

Example:

=CurrentAssets / CurrentLiabilities+ N("The formula returns Current Ratio")

Pasting Values

2011-04-24T05:35:00.000-07:00

To paste the calculated value of a single formula into a cell (thus overwriting the formula):

Press <F2> to edit and then <F9> to calculate.

To paste the calculated value of a single formula into the cell below the cell containing the formula:

Press <Ctrl+Shift+">.

To paste values in a range of cells, use the Paste Special dialog box:

Copy a range of cells containing formulas, press <Shift+F10> or right-click, and then select Paste Special from the shortcut menu.
Select Values and click OK.

Use the Paste Values Icon:

Add the Paste Values icon from the Edit category in

the Customize dialog box.

New in Excel 2002 and Excel 2003

The Paste icon has been expanded, enabling some options from the Paste Special dialog box to be quickly accessed.

Selecting Cells That Contain Formulas

2011-04-23T05:32:00.000-07:00

To select cells containing Formulas in order to color, delete, or protect, use the Go To dialog box:
1. Press <F5>.
OR
From the Edit menu, select Go To.
2. In the Go To dialog box, click Special.
3. Select Formulas, and then click OK.

Using a Range Name in a Formula

2011-04-22T18:35:00.001-07:00

To use a range Name in a formula:

Define the following range Names for ranges B2:B11, C2:C11, and D2:D11 respectively: Jan_2004, Feb_2004, and Mar_2004 (see the screenshot below).
Select a cell and type the formula =SUM.
Press <Ctrl+A>.
Select the first argument box and press <F3>.
Select the Name Jan_2004, and then click OK.
Paste the Names Febr_2004 and Mar_2004 in the next two argument boxes, and then click OK. The following formula has now been inserted into the cell:

=SUM(Jan_2004, Feb_2004, Mar_2004)

Displaying Both Formulas and Values for Cells

2011-04-21T05:27:00.000-07:00

To display both formulas and values for cells:

From the Window menu, select New Window.
From the Window menu, select Arrange.
Select the Horizontal option button and click OK.
Select one of the two windows and press <Ctrl+‘> (the key to the left of the number 1).

To move between windows, press <Ctrl+Tab> or <Ctrl+F6>.

Displaying Formula Syntax

2011-04-20T05:27:00.000-07:00

To display the syntax of all formulas in a sheet:

Press <Ctrl+‘> (the ‘ symbol is located to the left of the number 1 on the keyboard).

From the Tools menu, select Options, the View tab, Formulas, and then click OK.

To return to the normal display, press <Ctrl+‘> again (this keyboard shortcut is a toggle).

Regular Display :

Displaying Formulas :

Technorati Tags: Working With Formulas

Working With Formulas

2011-04-05T03:17:00.000-07:00

This chapter contains fundamentals, shortcuts, tips, and techniques that are essential when working with Formulas & Functions. It includes the following sections:

Inserting, Editing, & Copying Formulas : This section covers easy techniques on inserting, editing, and copying formulas, understanding the correct use of relative/absolute references, and creating power formulas.
Selecting, Displaying, Printing & Pasting Formulas: In this section you will find techniques on displaying formula syntax, displaying formulas and values, selecting cells containing formulas, pasting values, adding comments to formulas, and printing formula syntax. Array Formulas, page 12: This section provides an explanation of the concept of an Array and how Excel uses Arrays in formulas, You will also learn how to create an Array formula. Using Range Names in Formulas, page 13: In this section, learn about range name rules, how to define range names, and how to use range Names in formulas.
Auditing Formulas: In this section, find essential shortcuts and techniques on tracing and moving between precedent and dependent cells, stepping into formulas, and tracing errors in a formula.
Protecting Formulas: In this section, you will learn how to protect formulas in both protected and unprotected sheets.

Copying Formulas from a Range of Cells without Changing the Absolute or Relative References

2011-04-05T03:09:00.000-07:00

To copy/paste Formulas from a range of cells without changing the Absolute or Relative references:
1. Select the range of cells containing the formulas and press <Ctrl+H>.
2. In the Find what box, type the = sign.
3. In the Replace with box, type the # symbol (to change the formulas to
text).
4. Click Replace All, and then click Close.
5. Copy and paste the cells to a new location.
6. Repeat steps 1 through 3, reversing the # and = signs (to change the
text to formulas).

Copying a Formula from a Cell While Keeping the Absolute Reference or Relative Reference

2011-04-05T03:06:00.000-07:00

Avoid the nightmare of pressing <F4> multiple times when coping and pasting formulas.
To copy/paste a Formula without changing the Absolute or Relative references:

Option 1: Select a cell under the cell containing a formula and press <Ctrl+ ’>.
Option 2: Copy and paste the formula from the Formula Bar to a cell, instead of from a cell to another cell.
Example, cell C12 contains a formula:
1. Select the formula string in the Formula Bar and press <Ctrl+C> to copy it.
2. Leave the Formula Bar by clicking the Enter or Cancel icons to the left of the fx on the Formula Bar.
3. Select another cell and press <Ctrl+V>.

Nesting Formulas

2011-04-05T03:02:00.000-07:00

A formula can be copied and pasted into the appropriate place within another formula in the Formula Bar by using the <Ctrl+C> and <Ctrl+V> keyboard shortcuts.

To combine Formulas into one long nested power Formula:
1. Insert the following formula into a cell:
=SUMIF(TB_DB_Level3,A12,G12)
2. Insert the following formula into an adjacent cell:
=OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2)
3. In the Formula Bar of the second formula, select the formula without the = sign, and then press <Ctrl+C>.
4. Click Cancel or Enter (the two buttons between the Name Box and the formula in the Formula Bar) to exit edit mode.
5. Select the cell containing the first formula, and in the Formula Bar, select the reference G12, and then press <Ctrl+V>

Inserting/Editing Formulas

2011-04-05T02:58:00.000-07:00

Inserting/Editing Formulas

To open the Insert Function dialog box:

Select an empty cell and press <Shift+F3>.

To open a Function Arguments dialog box:

Select a cell containing a formula and press <Shift+F3>.

To insert a new Formula into a cell using the Function

Arguments dialog box:
1. Select an empty cell, and then type the = sign.
2. Type the formula name and press <Ctrl+A>.

To insert a formula by typing it while being guided by the
formula syntax tooltip:
1. Select an empty cell, and then type the = sign followed by the
formula name and a left parenthesis, i.e. (.
2. Press <Ctrl+Shift+A> (in Excel version 2003 the syntax appears
immediately after step 1 above).

Adventure Games

2011-04-04T07:58:00.000-07:00

Age of Castles
Battle over 60 enemies throughout the town in this fun strategy game! Goblins, Orcs and the dreaded dragons are all out to destroy your castle!
This is Adventure Games

download click here
and key

Hidden Object Games

2011-04-04T06:42:00.000-07:00

Abra Academy 2
In Abra Academy 2 the school of magic is under attack - unknown creatures have broken into the campus and are causing havoc! The children are frightened and the monsters are creating a huge mess.
Wanda and her friends have been called in to investigate the attacks and find a way to stop them. Download Abra Academy 2 now and help Wanda in her quest!
this is Hidden Object Games

Download click here

and key

Agatha Christie-Death On The Nile
A great hidden object adventure brings a whole new way of enjoying the timeless Agatha Christie story Death on the Nile to life. Assume the role of renowned detective Hercule Poirot seeking clues to a mysterious murder aboard a ship cruising the River Nile.
Find needed objects, question suspects and uncover the truth behind the Death on the Nile!
this is Hidden Object Games

Download click here

and Key

Agatha Christie-Peril at End House

Calling all seek and find fiends - The biggest selling novelist in history is back with Peril at End House - a spine-tingling challenge for Hercule Poirot and his faithful companion Hastings.
Set on the beautiful Cornish coast, there has always been an air of Peril at End House and with three attempts on our heroine Nick Buckley`s life in as many days, it is up to the famous Poirot to unravel the mystery before it's too late.
this is Hidden Object Games

Download click here
and Key

What's New in Excel 2007

2011-04-01T09:44:00.000-07:00

Here's a quick and dirty overview of the new features in Excel 2007:

A new tab/Ribbon user interface
New XML file formats
Worksheet tables
Significantly larger worksheet grid (1,048,576 rows x 16,384 columns)
Ability to use more memory
Unlimited conditional formats per cell
100 levels of undo
Maximum formula length increased to 8,000 characters
Support for 64 levels of nesting in a formula
Formula autocomplete
Better-looking charts
Workbook themes
Skins
Page Layout view
New conditional formatting options
Less confusing Excel Options dialog box
New collaboration features (requires SharePoint)
SmartArt and Improved WordArt
Compatibility checker
Easier pivot tables
Twelve new worksheet functions, plus integration of the Analysis ToolPak functions
PDF output (via a downloadable add-in)
Resizable formula bar
Many new templates
More control over the status bar

The Workings of Workbooks

2011-04-01T08:00:00.003-07:00

The core document of Excel is a workbook. Everything that you do in Excel takes place in a workbook.

Beginning with Excel 2007, workbook "files" are actually compressed folders. You may be familiar with compressed folders if you've ever opened a file with a .zip extension. Inside the compressed folders are a number of files that hold all the information about your workbook, including charts, macros, formatting, and the data in its cells.

An Excel workbook can hold any number of sheets (limited only by memory). The four types of sheets are

Worksheets
Chart sheets
MS Excel 4.0 macro sheets (obsolete, but still supported)
MS Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you want (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a different sheet, click its corresponding tab at the bottom of the window, or press Ctrl+PgUp (for the previous sheet) or Ctrl+PgDn (for the next sheet). To change a sheet's name, double-click its Sheet tab and enter the new text for the name. Right-clicking a tab brings up a shortcut menu with some additional sheet-manipulation options.

You can also hide the window that contains a workbook by using the View --->Window --->Hide command. A hidden workbook window remains open but not visible. Use the View --->Window ---> Unhide command to make the window visible again. A single workbook can display in multiple windows (choose View --->Window---> New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet-which you normally think of when you think of a spreadsheet. Every Excel 2007 worksheet has 16,384 columns and 1,048,576 rows. After years of requests from users, Microsoft finally increased the number of rows and columns in a worksheet.

How Big Is a Worksheet?

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using a 1024 × 768 video mode with the default row heights and column widths, you can see 15 columns and 25 rows (or 375 cells) at a time-which is about .000002 percent of the entire worksheet. In other words, more than 45 million screens of information reside within a single worksheet.

If you entered a single digit into each cell at the relatively rapid clip of one cell per second, it would take you over 500 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36 million sheets of paper-a stack about 12,000 feet high (that's ten Empire State Buildings stacked on top of each other).

Note :
Versions prior to Excel 2007 support only 256 columns and 65,536 rows. If you open such a file, Excel 2007 enters compatibility mode to work with the smaller worksheet grid. In order to work with the larger grid, you must save the file in one of the Excel 2007 formats. Then close the workbook and reopen it.

Having access to more cells isn't the real value of using multiple worksheets in a workbook. Rather, multiple worksheets are valuable because they enable you to organize your work better. Back in the old days, when a spreadsheet file consisted of a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now, you can store information on any number of worksheets and still access it instantly.

You have complete control over the column widths and row heights, and you can even hide rows and columns (as well as entire worksheets). You can display the contents of a cell vertically (or at an angle) and even wrap around to occupy multiple lines.
Note :
By default, every new workbook starts out with three worksheets. You can easily add a new sheet when necessary, so you really don't need to start with three sheets. You may want to change this default to a single sheet. To change this option, use the Office --->Excel Options command, click the Popular tab, and change the setting for the option labeled Include This Many Sheets.

Chart Sheets

A chart sheet holds a single chart. Many users ignore chart sheets, preferring to use embedded charts, which are stored on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to locate a particular chart, and they prove especially useful for presentations. I discuss embedded charts (or floating charts on a worksheet) later in this chapter.

Macro Sheets and Dialog Sheets

An Excel 4.0 macro sheet is a worksheet that has some different defaults. Its purpose is to hold XLM macros. XLM is the macro system used in Excel version 4.0 and previous versions. This macro system was replaced by VBA in Excel 5.0 and is not discussed in this book.

An Excel 5.0 dialog sheet is a drawing grid that can hold text and controls. In Excel 5.0 and Excel 95, they were used to make custom dialog boxes. UserForms were introduced in Excel 97 to replace these sheets.

The Object Model Concept

2011-04-01T05:11:00.000-07:00

If you've dealt with computers for any length of time, you've undoubtedly heard the term object-oriented programming. An object essentially represents a software element that a programmer can manipulate. When using Excel, you may find it useful to think in terms of objects, even if you have no intention of becoming a programmer. An object-oriented approach can often help you keep the various elements in perspective.

Excel objects include the following:

Excel itself
An Excel workbook
A worksheet in a workbook
A range in a worksheet
A button on a worksheet
A ListBox control on a UserForm (a custom dialog box)
A chart sheet
A chart on a chart sheet
A chart series in a chart

Notice the existence of an object hierarchy: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy is called Excel's object model. Other Microsoft Office products have their own object model. The object model concept proves to be vitally important when developing VBA macros. Even if you don't create macros, you may find it helpful to think in terms of objects.

The History of Excel

2011-04-01T05:04:00.000-07:00

You probably weren't expecting a history lesson when you bought this book, but you may find this information interesting. At the very least, this section provides fodder for the next office trivia match.

Spreadsheets comprise a huge business, but most of us tend to take this software for granted. In the pre-spreadsheet days, people relied on clumsy mainframes or calculators and spent hours doing what now takes minutes.

It Started with VisiCalc

Dan Bricklin and Bob Frankston conjured up VisiCalc, the world's first electronic spread- sheet, back in the late 1970s when personal computers were unheard of in the office environment. They wrote VisiCalc for the Apple II computer, an interesting machine that seems like a toy by today's standards. VisiCalc caught on quickly, and many forward-looking companies purchased the Apple II for the sole purpose of developing their budgets with VisiCalc. Consequently, VisiCalc is often credited for much of Apple II's initial success.

Then Came Lotus

When the IBM PC arrived on the scene in 1982, thus legitimizing personal computers, VisiCorp wasted no time porting VisiCalc to this new hardware environment. Envious of VisiCalc's success, a small group of computer enthusiasts at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathon Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. Released in January 1983, Lotus Development Corporation's 1-2-3 proved an instant success. Despite its $495 price tag (yes, people really paid that much for a single program), it quickly outsold VisiCalc and rocketed to the top of the sales charts, where it remained for many years. Lotus 1-2-3 was, perhaps, the most popular application ever.

Microsoft Enters the Picture

Most people don't realize that Microsoft's experience with spreadsheets extends back to the early 1980s. In 1982, Microsoft released its first spreadsheet-MultiPlan. Designed for computers running the CP/M operating system, the product was subsequently ported to several other platforms, including Apple II, Apple III, XENIX, and MS-DOS. MultiPlan essentially ignored existing software user-interface standards. Difficult to learn and use, it never earned much of a following in the United States. Not surprisingly, Lotus 1-2-3 pretty much left MultiPlan in the dust.

Excel partly evolved from MultiPlan, first surfacing in 1985 on the Macintosh. Like all Mac applications, Excel was a graphics-based program (unlike the character-based MultiPlan). In November 1987, Microsoft released the first version of Excel for Windows (labeled Excel 2 to correspond with the Macintosh version). Excel didn't catch on right away, but as Windows gained popularity, so did Excel. Lotus eventually released a Windows version of 1-2-3, and Excel had additional competition from Quattro Pro-originally a DOS program developed by Borland International, then sold to Novell, and then sold again to Corel (its current owner).

Excel Versions

Excel 2007 is actually Excel 12 in disguise. You may think that this name represents the twelfth version of Excel. Think again. Microsoft may be a successful company, but its version-naming techniques can prove quite confusing. As you'll see, Excel 2007 actually represents the tenth Windows version of Excel. In the following sections, I briefly describe the major Windows versions of Excel.

EXCEL 2

The original version of Excel for Windows, Excel 2 first appeared in late 1987. It was labeled Version 2 to correspond to the Macintosh version (the original Excel). Because Windows wasn't in widespread use at the time, this version included a runtime version of Windows-a special version with just enough features to run Excel and nothing else. This version appears quite crude by today's standards

EXCEL 3

At the end of 1990, Microsoft released Excel 3 for Windows. This version offered a significant improvement in both appearance and features. It included toolbars, drawing capabilities, worksheet outlining, add-in support, 3-D charts, workgroup editing, and lots more.

EXCEL 4

Excel 4 hit the streets in the spring of 1992. This version made quite an impact on the marketplace as Windows increased in popularity. It boasted lots of new features and usability enhancements that made it easier for beginners to get up to speed quickly.

EXCEL 5

In early 1994, Excel 5 appeared on the scene. This version introduced tons of new features, including multisheet workbooks and the new Visual Basic for Applications (VBA) macro language. Like its predecessor, Excel 5 took top honors in just about every spreadsheet comparison published in the trade magazines.

EXCEL 95

Excel 95 (also known as Excel 7) shipped in the summer of 1995. On the surface, it resembled Excel 5 (this version included only a few major new features). However, Excel 95 proved to be significant because it presented the first version to use more advanced 32-bit code. Excel 95 and Excel 5 use the same file format.

EXCEL 97

Excel 97 (also known as Excel 8) probably offered the most significant upgrade ever. The toolbars and menus took on a great new look, online help moved a dramatic step forward, and the number of rows available in a worksheet quadrupled. And if you're a macro developer, you may have noticed that Excel's programming environment (VBA) moved up several notches on the scale. Excel 97 also introduced a new file format.

EXCEL 2000

Excel 2000 (also known as Excel 9) was released in June of 1999. Excel 2000 offered several minor enhancements, but the most significant advancement was the ability to use HTML as an alternative file format. Excel 2000 still supported the standard binary file format, of course, which is compatible with Excel 97.

EXCEL 2002

Excel 2002 (also known as Excel 10) was released in June of 2001 and is part of Microsoft Office XP. This version offered several new features, most of which are fairly minor and were designed to appeal to novice users. Perhaps the most significant new feature was the capability to save your work when Excel crashes and also recover corrupt workbook files that you may have abandoned long ago. Excel 2002 also added background formula error checking and a new formula-debugging tool.

EXCEL 2003

Excel 2003 (also known as Excel 11) was released in the fall of 2003. This version had very few new features. Perhaps the most significant new feature was the ability to import and export XML files and map the data to specific cells in a worksheet. It also introduced the concept of the List, a specially designated range of cells. Both of these features would prove to be precursors to future enhancements.

EXCEL 2007

Excel 2007 (also known as Excel 12) was released in early 2007. Its official name is Microsoft Office Excel 2007. This latest Excel release represents the most significant change since Excel 97, including a change to Excel's default file format. The new format is XML based although a binary format is still available. Another major change is the Ribbon, a new type of user interface that replaces the Excel menu and toolbar system. In addition to these two major changes, Microsoft has enhanced the List concept introduced in Excel 2003 (a List is now known as a Table), improved the look of charts, significantly increased the number of rows and columns, and added some new worksheet functions. For more, see the sidebar