Intro by Levone

VIEWS: 6 PAGES: 15

									Excel
Out of the Ordinary
Formulas Formatting Drawing & Graphics Special Features

Formulas
1. IF and Nested IF statements
Provide branching in calculations. 25 Elephant

The number is 25! The number is 25 and the animal is Elephant.

2. Join first and last names
Join a list of first names in one column with a list of last names in another column using a formula. First John Kaspar Emma Last Smith Johnson Bovary Formula 1 John Smith Kaspar Johnson Emma Bovary Formula 2 Smith, John Johnson, Kaspar Bovary, Emma

3. Join a date with text
To concatenate two values (as above) use the concatenation (&) operator. To connect a date or time value stored in a cell with a text string, use the TEXT function. Date: Text: Wrong: Right: 9/12/2000 Date: Date: 36781 Date: 09-12-00

(uses concatenation only) (uses concatenation plus the TEXT function)

4. Create a total based on a condition
Use the SUMIF function to create a total value for a range based on a value in another range. For example, for every cell in the range below that contains the value "NorthWind", the example formula calculates the total for the corresponding cells in the range next to it. EastWind NorthWind SouthWind NorthWind WestWind Northwind Formula: 50 100 75 100 35 100 300 The formula contents: =SUMIF(B30:B35,"Northwind",C30:C35)

5. Create a total based on multiple conditions
Same as above, but more than one condition.

Uses an array formula which must be entered using CTRL+SHIFT+ENTER . EastWind Regular NorthWind Premium SouthWind Regular NorthWind Regular WestWind OK Northwind Premium Formula: 50 100 75 100 35 100 200

The formula contents: =SUM(IF((B44:B49="NorthWind")*(C44:C49="Premium"),D44:D49

6. Count the occurrences of a condition
The COUNTIF function counts the occurrences of a value in a range of cells. In this example, it counts the number of cells in the range below that contain the text value "NorthWind". EastWind NorthWind SouthWind NorthWind WestWind Northwind Formula: 3 The formula contents: =COUNTIF(C59:C64,"NorthWind")

7. Count the occurrences of multiple conditions
Same as above, but checks for a second condition. The first condition is whether "NorthWind" is present; the second is whether "Premium" is also present. Then the number of rows that contain both is returned. Again, uses an array formula (CTRL+SHIFT+ENTER). EastWind Regular NorthWind Premium SouthWind Regular NorthWind Regular WestWind OK Northwind Premium Formula: 2 The formula contents =SUM(IF(B74:B79="NorthWind",IF(C74:C79="Premium",1,0)))

)*(C44:C49="Premium"),D44:D49))

"NorthWind"

="Premium",1,0)))

Formatting
Cell Formatting Autoformatting Conditional Formatting

Cell formatting 1. Colors, font

Fuqua

Fuqua

Fuqua
F u q u a

2. Alignment Fuqua 3. Alignment, continued Fuqua

Fu qu a

Fu qu a

FuquaFuquaFuqua

4. Patterns

Fuqua The Fuqua School The Fuqua School

5. Wrap text 6. Center across selection

Back to Top

Autoformatting
1st Quarter Projected Actual 75000 84819 1st Quarter 75000 84819 2nd Quarter 85000 99123 2nd Quarter 85000 99123 3rd Quarter 92000 108000 3rd Quarter 92000 108000

Projected Actual

1st Quarter

2nd Quarter

3rd Quarter

Projected Actual

75000 84819 1st Quarter 75000 84819

85000 99123 2nd Quarter 85000 99123

92000 108000 3rd Quarter 92000 108000

Projected Actual

Back to Top

Conditional Formatting Example: For the column of numbers below, if a number is >10 then special formatting is applied. 8 9 10 11 12 13
Back to Top

4th Quarter 101000 120000 4th Quarter 101000 120000

4th Quarter

101000 120000 4th Quarter 101000 120000

mber is >10

Drawing & Graphics

Freehand Drawing with the Drawing Toolbar 1. Autoshapes

How do you get those effects?
2. Clip Art

3. Overlaps, order

4. WordArt

Out of the Ordinary Excel

Special Features

1. Capture on-screen data to include in a spreadsheet

2. Record simple macros
Return to Intro Page

3. Create controls with the FORMS Toolbar tools Pick your favorite fruit from the list.
1 2 3 4 5 6 7 8 9 10 11 Apples Oranges Pears Peaches Grapes Melons Bananas Raspberries Strawberries Gooseberries Ligonberries

4. Use the choice to retriev
The Fruit Table
Apples Oranges Pears Peaches Grapes Melons Bananas Raspberries Strawberries Gooseberries Ligonberries

Apples Oranges Pears Peaches

Index for price: Index for color:

The user's choice:

1

Index for fruit:

5. Use the retrieved data in a dynamic report. Our Dynamic Report
You selected Apples from the list of fruits. The color of your fruit is red; today's current market price in Durham is 0.35.

4. Use the choice to retrieve data from a table.
The Fruit Table $0.35 red $0.50 orange $0.75 yellow $0.40 peach $1.19 green $2.59 melony $0.75 yellow $2.99 red $3.50 red $5.59 green $8.87 purple Index for price: Index for color: Index for fruit: $ 0.35 red Apples

mic report.


								
To top