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.
Pages to are hidden for
"Intro"Please download to view full document