| IT-Savvy | 12-Mar-07 | ||||||||
| Excel Intro | 8:30 - 12:00 | 1:00-4:30 | |||||||
| Tour Excel | Boundaries (Cols,Rows,Sheets) | ||||||||
| Status Bar | |||||||||
| Text, Numbers,Formulas, Functions | |||||||||
| Icons | |||||||||
| HELP | |||||||||
| CASH.XLS | Note text vs numbers | ||||||||
| Review formats | |||||||||
| Note formulas (make formulas not numbers) | |||||||||
| Delete formulas | |||||||||
| Build first | |||||||||
| Copy paste | |||||||||
| Build sum (autosum) | |||||||||
| Change count to 1 to test formula | |||||||||
| Enter number in amount to kill formula | |||||||||
| 1+1=3 | |||||||||
| MoreFormulas.XLS | Note assumptions | ||||||||
| Inspect TRIGGER (note name box) | |||||||||
| Note tax table (lookup table sorted using "price is right" logic) | |||||||||
| Review formulas and use of range names | |||||||||
| Delete FICA and using absolute cell reference | |||||||||
| Note Average …Low NET cells | |||||||||
| Insert record in the table and see the model work-could be external database | |||||||||
| Auditing Go to F24 press <CTRL> + <~> | |||||||||
| Repeat in D28 | |||||||||
| WEATHER.XLS | Note date | ||||||||
| Note data vs formulas | |||||||||
| Select range of data | |||||||||
| Select chart wizard | |||||||||
| Accept defaults | |||||||||
| Click on the chart to get chart menu | |||||||||
| Select chart options | |||||||||
| Add titles can use cell reference if =$cell | |||||||||
| Build a column with average highs from table <F4> | |||||||||
| Select chart, add data | |||||||||
| Identify average range | |||||||||
| CETime_Report.XLS | Note multiple sheets | ||||||||
| Click on gray cells to identify as pivot table | |||||||||
| Note data sheet of detail data | |||||||||
| Note auto filters | |||||||||
| Multiple auto filters are anded | |||||||||
| Select projects sheet | |||||||||
| Make a few Selections | |||||||||
| Click on gray cells then right click and select pivot table wizard and layout | |||||||||
| Note PAGE ROW COLUMN DATA areas in the wizard | |||||||||
| Drag a new field to the page area for filter | |||||||||
| Drag TEAM above Project in ROWS area | |||||||||
| Filter out blank team data | |||||||||
| Add time again to data area | |||||||||
| Add FY to Columns | |||||||||
| Double click on data for a copy of the records in a new sheet | |||||||||
| Select cell containing results and select Format Conditional Formatting | |||||||||
| Click on a Pivot table and select Pivot Chart | |||||||||
| Grouping.XLS | Select multiple sheets for grouping by <Shift> Click | ||||||||
| Format one formats all | |||||||||
| =SUM(First:Second!B2) using mouse pointing | |||||||||
| Copy and paste in all cells | |||||||||
| Text.XLS | Inspect cells containing formulas (<CTRL> + <~> | ||||||||
| CONCATENATE literals using " " | |||||||||
| FIND locates the position of a string of text. It can be used to find where to break text | |||||||||
| Fill in the correct formulas | |||||||||
| Notice the cell notes | |||||||||
| Outline.xls | Used to collapse/expand details when subtotals are present | ||||||||
| Select data range name | |||||||||
| Select Data Sub Totals note current settings | |||||||||
| Pivot tables do much of this work | |||||||||
| Rules of Thumb | Know where your files are (Place them where they are backed up note: File, Properties, General) | ||||||||
| Save early and often (note: Tools, Options, Save) | |||||||||
| Know when NOT to save | |||||||||
| Start building with applications you know well | |||||||||
| Distrust your work (note Tools, Formula Auditing, Auditing Mode <CTRL> + <`>) | |||||||||
| Don't use a number where a formula would work (design for change) | |||||||||
| Enter simple numbers to test formula | |||||||||
| Delete all formulas except first one when editing a formula | |||||||||
| Be careful about entering numbers as text and text as numbers | |||||||||
| Enter Dates as Dates and understand that they are values | |||||||||
| Sorting can be very destructive if you do not include all related columns | |||||||||
| Text Formulas that everyone should know exist | |||||||||
| CONCATENATE (text1,text2,...) | |||||||||
| LEFT(text,num_chars) | |||||||||
| RIGHT(text,num_chars) | |||||||||
| MID(text,start_num,num_chars) | |||||||||
| LEN(text) | |||||||||
| FIND(find_text,within_text,start_num) | |||||||||
| TEXT(value,format_text) | |||||||||
| Learning more | <F1> Help | ||||||||
| http://office.microsoft.com/en-us/training/default.aspx | |||||||||
| Colleagues | |||||||||
| Helpline | 656-2604 | ||||||||
| DSV | 656-1181 | ||||||||
| Try something new every week | |||||||||