1 / 168

INTERMEDIATE EXCEL 2010

INTERMEDIATE EXCEL 2010. Nolan Tomboulian. Tomboulian@ yahoo.com. Tomboulian.wikispaces.com. INTERMEDIATE EXCEL 2010. The class titles of Introduction, Intermediate and Advanced are relative to each user.

aisha
Download Presentation

INTERMEDIATE EXCEL 2010

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INTERMEDIATEEXCEL 2010 Nolan Tomboulian Tomboulian@yahoo.com Tomboulian.wikispaces.com

  2. INTERMEDIATE EXCEL 2010 The class titles of Introduction, Intermediate and Advanced are relative to each user. This class will address some of the more common Excel features that go beyond basic navigation, data entry formula creation and formatting. This class can only expose you to what EXCEL can do. The short class time does not allow for in-depth presentation and analysis of the many features and powers of Excel. Nolan Tomboulian Tomboulian@yahoo.com Tomboulian.wikispaces.com 252-675-0176

  3. EXCEL Intermediate Course Objectives • This class is for users who have some experience using and creating Excel workbooks, understand cell and worksheet formatting, worksheet navigation, basic mathematical operands (+, -, *, /, ^), relative and absolute cells, Move/Cut/Copy/Paste, AutoFill and Functions. • Quick Skill Review of: • [File] Tab and Back Stage View • Ribbon / Tabs / Groups / Commands / Dialogs boxes • Quick Access and Ribbon Customization • Workbook navigation and keyboard commands, Keyboard Tips • Cell and Data Formatting • Copy / Paste / Paste Special Options • AutoFill Options • Selecting Ranges • Relative and Absolute cell reference • AutoSum and basic Functions

  4. EXCEL Intermediate Course Objectives • Data & Tables: • Filtering / Sorting / and Custom Sort Lists • Data Validation • Conditional Formatting • Data Lookups: VLookUp, HLookUp, Match and Index Functions • Sub-Totals and Groupings • Logical functions (IF, AND, OR, NOT) • Conditional Data Summaries (COUNTIF, SUMIF, AVERAGEIF, COUNTA) • Intermediate Formulas and Functions (Round, INT, RANK) • Introduction and creation of simple Pivot Tables • Intermediate charts and graphics • Using other Functions • Other Topics?

  5. Table Of Contents

  6. Blank for Duplex Printing

  7. Blank for Duplex Printing

  8. Blank for Duplex Printing

  9. RULES INTRODUCTIONS LOGISTICS AGENDA FILE INFORMATION CONTACT INFORMATION Housekeeping

  10. Logistics • Parking • Bathrooms • Student Facilities • Smoking • Fire Alarm / Code Red • Breaks and Lunch

  11. This class will not be… Not too many people know that the word “LECTURE”is actually an acronym. It stands for Lengthy Endless Continuous Torture with Unending Repetition of Explanations. A LECTURE Class participation is welcomed and beneficial to you (and others)

  12. Code Of Conduct • Respect each other (Talking) • Food and Drink • Participate • Patience (with me and yourself) • Ask questions • Have FUN • Have your own projects or ideas? • Turn in the Evaluation Survey!

  13. Class Flow • General Flow • Overview of Topic • Step-by-Step Exercises • Independent Practice • Questions • Collaboration

  14. Technical Issues • The Exercise Data Files could be: • 1) On your Desktop • 2) On the T: Drive • Excel • Workbooks • Many of the files should be in a READ ONLY mode to keep you from changing them. • Create a File Folder on the T: drive, Desktop or flash drive where you plan to save your work. • Do a SAVE AS to add files to YOUR File Folder. • The SAVE AS does not remove the Read Only status. • Press the Office Button and then Prepare – Mark as Final

  15. END OF SECTION Housekeeping Nolan Tomboulian Tomboulian@yahoo.com 252-675-0176

  16. This Page Blank for Duplex Printing

  17. Keyboard Shortcuts [Alt] for Keyboard Tip Codes <Right Click> for Option Dialogs http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx ALT Menu KeyTips Ctrl <F1> compress Ribbon <F1> Help <F2> Edit <F3> Name Manger Ctrl <1> Format Cells Dialog <F4> Absolute Cell Reference <F7> Spell Check <F5> Go To Ctrl Z Undo Ctrl <Y> Redo Shift <space> Select Row Ctrl <space> Select a Column Ctrl <T> Activate as Table Ctrl <A> Select Range <F9> Recalculate Ctrl ~ Show formulas Ctrl <0> Hide Column

  18. Copy / Paste / Paste Special Work Smart – Not Hard Keyboard: Ctrl C = Copy Ctrl X = Cut Ctrl V = Paste <Right Click> Copy Paste Paste Special 2010 2007 Not a Formula

  19. Paste Special The Clipboard can hold 20 items that have been cut and can be reused. http://www.dummies.com/how-to/content/using-paste-special-in-excel-2007.html

  20. Copy with AUTOFILL Excel is pretty smart in that it can interpret “patterns of data” and copy / fill the information to other cells. • Select the cell or range that contains the formula, formulas or data to copy. • <Left Click> and Drag the fill handle in the direction you want to copy the data or formula(s) and then release the mouse button. • To copy only the formats or only the formulas, click the AutoFill Options button (that appears after dragging the fill handle) and select the appropriate option. • Shortcut: Double Left Click on Fill Handleif there is data in the column to the Left!

  21. AUTOFILL To copy only the formats, formulas, or data, left click the AutoFill Options button (that appears after dragging the fill handle) and select the appropriate option.

  22. Cell Formatting • <Right Click> in a Cell and select Format Cells, • or use the Ribbon [HOME] tab • Or use <Ctrl> <F1>

  23. Number Cell Formatting <Right Click> in a Cell and select Format Cells or use the Ribbon [HOME] tab

  24. Alignment Cell Formatting <Right Click> in a cell and select Format Cells or use the Ribbon [HOME] tab

  25. Font Cell Formatting <Right Click> in a Cell and select Format Cells or use the Ribbon [HOME] tab

  26. Border Cell Formatting <Right Click> in a cell and select Format Cells or use the Ribbon [HOME] tab

  27. Fill Cell Formatting <Right Click> in a cell and select Format Cells or use the Ribbon [HOME] tab

  28. Protect Cell Formatting <Right Click> in a cell and select Format Cells or use the Ribbon [HOME] tab

  29. Nolan Tomboulian Tomboulian@Yahoo.com Tomboulian.Wikispaces.com Tables and Data Ranges

  30. TABLE Objectives • Create / Define / Name tables • Explore the Ribbon Table Tab • Add and delete records (Rows) and Fields (Columns) • Add Formulas and change column formatting • Sort data • Single Key Multi Key Custom Sort • Filter data • Use the Styles options • Use the Total Row to summarize a table • Use the Outline buttons to show or hide details SubTotal is a special group function that can be done on a Range of data using the [DATA] tab, BUT CANNOT BE USED ON A TABLE!

  31. Structured Range of Data • One of the more common uses of Excel is to manage data – It is not just a calculation tool! • Using Excel, you can: • Store and update data • Sort data • Filter or Search for and retrieve subsets of data • Summarize data • Create reports and graphs

  32. Structured Range of Data • In Excel, a collection of similar data can be structured in a range of rows and columns • Each column in the range represents a field • Each row in the range represents a record • You Cannot change the Excel Column Headings (A,B, C…, but you can create (SINGLE ROW) Headings to be used with your table)

  33. Creating Fields (columns) Keep in mind these best practices • Create fields that require the least maintenance • Ex. Hire_Datevs Time_With_Company • Don’t use <Spaces> in Descriptions • Store the smallest unit of data possible • Ex. Store City, State, and Zip in separate fields • Apply a text format to fields with numerical text data (esp. if there are leading zeros) • Ex. Zip Code, Social Security Number, Product code

  34. Structured References • Structured References • You can reference a specific cell or range in a table with a structured reference • Uses the field names, as opposed to the cell reference • Similar in functionality to a named cell or range • Easy to understand: =SUM(G20:G123) =SUM(Employee[Annual_Salary])

  35. Structured References • To use a structured reference in a formula or function: • Type a left bracket - [ • to open the field list for the table • Double-click the field name • Type a right bracket ]

  36. Structured References • Structured References can also use special qualifiers to refer to special portions of the table, such as the Total Row.

  37. Structured Range of Data Museum

  38. Tables • Range of related data, managed separate from other data on a worksheet • Easy access to data management and analysis tools • Can have multiple Tables in a worksheet

  39. Table Features • Table Formats and Styles (Depending on selected options, you have different STYLES for Row and Column highlighting) • Adding or inserting new rows or columns automatically expands the Table Range. • Easily add a Total Row to calculate summary statistics (Sum, Count, Average, etc) • Formulas applied in one cell will automatically be applied to all cells in that field (column) – no need to highlight or Copy Down! • Formatting can be applied to an entire column • Can use the table and field names as a cell reference in a formula. –(It looks complex when you see the formula – but when using – Point-and-Click it is more clear.) • For Example to add a formula to increase the Appraised Value by 10%: =DATA[[#This Row],[Appraised Value]]*0.1

  40. Creating an Excel Table • Highlight / Select the range of data to be included in the table. • Tables don’t need “Column Headings” but the features are more useful when they are defined. • <Ctrl A> is a shortcut to select a range of data • <Ctrl> <T> selects the data and makes it a Table • Go to the Insert tab – Tables group • Click the Tablebutton • Ctrl-T is a shortcut to create a new table • Verify the RANGE is correct and Check if the table has column headings. Excel will: Create a context Ribbon [Table Design] Tab Add Filter dialog commands to fields (Column Headings) Enter a Table Name to reference the table (Optional)

  41. Creating an Excel Table

  42. Using a Structured Table If you <click> outside the table, the <Table Tools> goes way. Click ANYWHERE on the Table to reactive the [Table Tools] and then <Click> the {Design Tab} You may NAME the table so you can use the Table Name as a reference in formulas rather than Absolute Cell Addresses: Inserting a column or row into the table will EXTEND the Table Name Range Adding an Adjacent Column or Row will also EXTEND the Table Name Range. (Use Resize if adding nonadjacent columns)

  43. The Ribbon <Table Design> Tab GROUPS Define or Rename a Table range Extend the size of a Table if Nonadjacent columns or rows were added Pivot Tables are another Topic Be Careful with REMOVE Duplicates – It actually deletes rows from the Table. • Convert to Range: Un-define the Table: Cell Styles will still be applied!- You must <CLEAR> Formatting. • May also cause problems with Defined Formulas

  44. The Ribbon <Table Design> Tab GROUPS External Table Data is an Advanced function Banded Rows and Columns allow for different Color Styles to be applied. Header Row – Turns on and off the Titles. First and Last Column allow the “MEDIUM” style formatting . Total Row is a fast way to get some Summary Statistics about the table.

  45. Using the Total Row • A Total Row, which you can display at the end of the table, is used to calculate summary statistics for the columns in an Excel table. • Total rows can be set to display different summary statistics for each field. • Total rows usually automatically recalculate as records are added and deleted.

  46. Using the Total Row • Go to the [Table Tools Design] tab –{Table Style} Options group • Click the Total Rowcheckbox to insert a check mark • Scroll to the end of the table • Set the desired summary function

  47. Navigation in a Worksheet - REVIEW • <Tab> Move to the right • <Shift> <Tab> Move to the left • <Enter> Move <DOWN> a row • <shift> <Enter> Move <UP> a row • <Home> Move to the start of the Row • <Shift><Home> Select from the Cell to the A Column • <Ctrl> <Home> Move to the top of the WORKSHEET • <End> <Down Arrow> Move to the last Row • <End> <Up Arrow> Move to the top Row • <Ctrl> <Left Arrow> Move to first column • <Ctrl> <Right Arrow> Move to last column • <Up>, <Down>, <Left>, <Right> arrow

  48. Finding and Editing Records • Go to the [Home] tab – {Editing group}, click the Find & Select button, and then click Find • Type your search criteria in the Findwhatbox, and then click the Find & Select button • You could also use Table <FILTER> to select certain record items

  49. Exercise – Table Creation • Create a Table • Renaming a Table • Formatting a Table • STYLES • Column Formats • Adding a Column • Adding and Deleting records (Rows) • Adding a TOTAL ROW

  50. Page Left Blank For Duplex Printing

More Related