1 / 40

Intermediate Excel 2007

Intermediate Excel 2007. Objectives. Sorting and Filtering Charts and Graphs Formulas and Functions Working with Sheets Tips and Tricks. Assumptions. You have a good working knowledge of Microsoft Excel (any version)

ananda
Download Presentation

Intermediate Excel 2007

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. Intermediate Excel 2007

  2. Objectives • Sorting and Filtering • Charts and Graphs • Formulas and Functions • Working with Sheets • Tips and Tricks

  3. Assumptions • You have a good working knowledge of Microsoft Excel (any version) • You have some experience with Office 2007 (familiarity with concept of Ribbons/Groups) • You know what a Left Click, Right Click and Double Click are • You know where these Keys are: • Ctrl, Alt, Logo

  4. To Save you Some Typing • We have created a workbook that you will work with today • Create a folder called “Your Name” on the Desktop • Using Internet Explorer, visit this site: • www.myotherbrotherweb.com/ncyf/main.asp • Right click on Excel Samples link and click Save Target As • Save the Target document in the new Folder • Open the workbook called: Samples.xlsx

  5. Sorting and Filtering • Sorting can be done on individual columns, groups of columns or portions of one or more columns • You can do a quick sort, or you can define multiple sort levels • Filters change displayed data, but do not remove data from the sheet • With Filters turned on, selecting multiple rows may have unexpected results

  6. Quick Sort All Data • Click SampleSalesData Sheet • On Home Tab, in Editing Group, Choose Sort & Filter DropDown • Experiment with first 2 options and clicking in Different columns Note: You can always Undo if you get unexpected results

  7. Custom Sort • On Home Tab, in Editing Group, Choose Sort & Filter DropDown • Click Custom Sort • Notice Field is based on Headings row • Add Level allows multilevel sorts • Increasing/Decreasing is applied at each level

  8. Sorting a Highlighted Range Lets say you wanted to know who sold the most of each product in Ontario on any date • Click in cell B2…Do a Quick Sort A to Z • Highlight all the Ontario Rows • Click Custom Sort • Notice Sort By now references Columns • Choose Column D for 1st level • Choose Column E for 2nd level • Choose Largest to Smallest for Order • Click OK

  9. Sorting less than a Complete Row In very rare instances, you may want to reorganize just a portion of the data, you can select columns from multiple rows to sort, but bear in mind doing this corrupts your overall data • Highlight cells A6 to C12… • Do a Quick Sort, Descending order. Notice that only the highlighted cells are reorganized, the other columns are not lined up with their original data • Click Undo to restore the data

  10. Filtering • On Home Tab, in Editing Group, Choose Sort & Filter DropDown • Click Filter This turns on/off the filtering function. You will notice a dropdown arrow appears on all of the column headings • Click Dropdown for Region • Click Select All…Click Alberta…Click OK • ReSelect All for Region

  11. Advanced Filtering I want to know anyone who sold more than 10 units of anything • Click Units DropDown • Click Number Filters…Greater Than…10…OK • Click Units DropDown • Click Sort Largest to Smallest

  12. Pie Charts and Graphs Chart or Graph • Visual representation of a set of data • Shows trends or relationships in data • Must have a data source • Data source consists of Series Name, Series Values and Category Values • Can be superimposed on a sheet or in its own chart sheet • Overall look/style can be customized

  13. Chart Types • Column • Line • Pie • Bar • Area • XY (Scatter) • Stock • Surface • Doughnut • Bubble • Radar

  14. Chart Elements Five common elements • Chart Area – Outer Box • Chart Title – Descriptive Label • Plot Area – Graphical Representation • Data Marker – Represents a Data Value • Legend – Explains Markers and Symbols

  15. 3d Pie Chart • Click the Sheet entitled: PieChartData • Highlight Cells A2 to B8(do not include the heading or the total line) • Go to Insert Tab, Charts Group and click the Pie Drop Down • Choose Exploded Pie in 3-D(Chart is placed in sheet with data) • In the Design Contextual Tab, click Move Chart • Choose New Sheet

  16. Customizing the 3D Chart • Click Contextual Tab Layout • Click Data Labels Drop Down…Click Center • Click Data Labels Drop Down…More Data Label Options • Click Percentage Check Box • Uncheck Value Box • Click Close • Click Design Tab…Experiment with Chart Layouts

  17. Multiple Series Line Chart • Click the Population Statistics Sheet • Highlight Cells A2 to C7 • Click Insert…Line Dropdown…2-D Line • Right Click Massachusetts (Lower) Line • Choose Format Axis • Click Secondary Axis • Click Close

  18. Formulas containing Functions Function • A named operation that returns a value • May or may not require arguments • =SUM(A1:A10) • =TODAY() • Over 300 different functions built-in • Works with Numbers, Dates and Text

  19. Good Formula Practices • Don’t hide important data within a formula • e.g. =A2*0.05 vs =A2*E2, with Tax rate in E2

  20. Good Formula Practices • Keep formulas simple. Use functions where possible • Break up formulas for clarity, storing intermediate values in other cells. • C11=SUM(A1:A10)/SUM(B1:B10) is ratio of 2 sums, but “hides” each of the sums • Better would be: • A11=SUM(A1:A10) • B11=SUM(B1:B10) • C11=A11/B11

  21. Simple Formulato Calculate Working Days • Click WorkingDays Sheet • Click in Cell B7 • Click fx button next to formula bar • Type Working Days in search box and click Go • Double click NETWORKDAYS • Click Jump to Sheet button next to Start_date • Click Cell B3…Click Jump Back Sheet Button • Click Jump to Sheet button next to End_date • Click Cell B5…Click Jump Back Sheet Button • Click OK • Play with other dates

  22. Conditional Formulas • WeeBee Shippers ships DVDs for a local warehouse. The Shipping price charged is based on the number of DVDs being shipped, according to the following scale: • Create a formula to calculate the shipping charge based on the number of DVDs shipped

  23. Solution Use the IF Function: IF(Calculated Expression, Result if True, Result if False) • e.g. IF # Shipped > 20, then shipping cost is $1.00 otherwise (# shipped is less than 20) and if # shipped > 5 then shipping cost is $1.24 otherwise (# shipped is 5 or less) so shipping cost is $1.49 • The statements in ( ) are assumed, based on the calculated expression not being true The Result of the IF is then multiplied by the number of DVDs (B1)

  24. Loan Calculator Formula • Click the LoanCalc Sheet • In Cell B6 type:=PMT(B2/B3,B3*B4, B1) • In Cell B7 type:=B3*B4*B6 • In Cell B8 type:=B7 + B1 • Fill in Loan Data e.g. $100,000, 5%, 12 per year, 30 years

  25. Formulas based on data in other sheets • Click PaymentDetails Sheet • Click in C2…type = • Click LoanCalc sheet • Click B1 • Click Check Mark • Autofill 3 remaining lines

  26. Naming Ranges • You can assign “Names” to ranges of cells • These Names can be used in formulas • The Name will appear in the “Name Box” to the left of the Formula Bar • Right click on a Cell or Cell Range and Click Name a Range

  27. Name Cells • Click LoanCalc Sheet • Right Click on Cell B1…Click Name a Range • Name the Range Loan • Name the following cells: • In Cell B6 type:=PMT(Rate/PPY,PPY*Years, Loan)

  28. Working with Worksheets • To Insert a New Sheet, click on the Insert Worksheet Tab • To Delete a Sheet, right click on the sheet name and click Delete • To Move a Sheet, simply click and hold and drag the sheet name

  29. Working with Worksheets • To select multiple adjacent sheets – click the first sheet name, hold down shift, click the last sheet • To select multiple nonadjacent sheets – click the first sheet name, hold down CTRL to add (or subtract) sheets from selection • NOTE 1: When multiple sheets are selected, editing occurs simultaneously in the same cell on all sheets • NOTE 2: Pressing Print with multiple sheets selected, prints all of the sheets

  30. Working with Worksheets • To copy a complete sheet – Two options • Right click sheet name…click Move or Copy…click box next to Create a Copy…Indicate destination…Click OK • Right Click the diagonal triangle to the left of Column Heading A (this selects entire sheet)…Click Copy…Insert New Sheet… Right Click the diagonal triangle to the left of Column Heading A…Click Paste

  31. Working with Worksheets • To move a Sheet from one Workbook to another • Both Workbooks must be open • Right click Sheet to be moved • Click Move or Copy • Choose Destination Workbook from dropdown • Indicate where you want the sheet to “land” • To keep a copy in the original workbook, click Create a copy box

  32. Find and Replace

  33. Protection • Individual Cells are Locked by default to prevent unintended editing. However, the worksheet’s protection must be turned on before protected cells is in effect • Review Tab…Protect Sheet or Protect Workbook

  34. Rotated Column Headings • Right click on Cell…Format Cells …Alignment…Rotate Text 60 degrees

  35. Paste Special

  36. Split and Freeze Panes

  37. Using VLookup

  38. Tips and Tricks • To enter two lines of text within a cell • Press Alt – Enter • To enter a number as text • Start the entry with a single quote (next to Enter) • To see Formulas instead of Results • Formula Tab…Formula Auditing Group…Show Formulas Toggle Button • To view multiple worksheets from the same workbook simultaneously • View Tab…Window Group…New Window • View Tab…Window Group…Arrange All

  39. Tips and Tricks • In Formulas, & used to concatenate two entries • =“Today is “ & TODAY() yields Today is 8/12/10 • To refer to a “fixed” cell, use $ before the Cell Reference Column and/or Row. This reference doesn’t change when cell w/formula is copied, moved or AutoFilled (Absolute Reference) • =$A$7 * 5 • To copy a formula to another cell WITHOUT changing the cell references, edit the formula in the formula bar, highlight it, CTRL-C, click on destination cell, click in formula bar, CTRL-V

  40. Tips and Tricks • Adjacent ranges: (Hold and Drag to Select) • A1:G5 • Nonadjacent ranges: • A1:A5;F1:G5 • Select nonadjacent cells by holding CTRL during selection process • While a range is selected, ENTER and SHIFT-ENTER move you back and forth through the cells in the range

More Related