1 / 34

Power Excel - OACUBO

Power Excel - OACUBO. Bill Jelen. Presentation Overview. Charting Secrets Fill Handle Text Formulas Customizing Excel Worksheets & Group Mode Customize All Future Workbooks Excel 2007 Preview Automatic Subtotals Pivot Tables Formula Auditing New Functions Formulas AutoFilter

ghita
Download Presentation

Power Excel - OACUBO

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. Power Excel - OACUBO Bill Jelen

  2. Presentation Overview • Charting Secrets • Fill Handle • Text Formulas • Customizing Excel • Worksheets & Group Mode • Customize All Future Workbooks • Excel 2007 Preview • Automatic Subtotals • Pivot Tables • Formula Auditing • New Functions • Formulas • AutoFilter • Misc Tips Consult@MrExcel.com

  3. Charting Secrets • R-Click, Format, Options, Angle of First Slice • Leave top left corner cell blank • Create chart with one keystroke: F11 • Right-click anything to customize • Custom number format thousands: $#,##0,K • Custom number format millions: $#,##0,,K • Display Units on Scale tab • Drag new data to chart, or use blue resize handles • Use 2nd axis for different orders of magnitude • Customize all future charts • Detecting chart lies Consult@MrExcel.com

  4. Fill Handle Tricks • January: February, March, April… • Monday: Tuesday, Wednesday, Thursday • Jan: Feb, Mar, Apr • Q1: Q2, Q3, Q4, Q1, Q2 • 1st Period: 2nd Period, 3rd Period • Use Ctrl+; for todays date, Ctrl+: for time • 4/27/07: 4/28/07, 4/29/07 • 1: 1, 1, 1, 1, 1! • Ctrl+Drag for 1: 2, 3, 4 and 4/27: 4/27, 4/27, 4/27 • Right-click+Drag for Fill Weekdays, Fill Months • Tools – Options – Custom Lists Consult@MrExcel.com

  5. Text Formulas • Concatenation operator - & • =A2&B2 • Double-click the fill handle • Joining text with a space in between =A2&“ ”&B2 • Convert to proper case =PROPER(A2&B2) • Also =UPPER(), =LOWER() • Joining text with a date or formatted number: • =TEXT(A2,”mm/dd/yyyy”) • =TEXT(A2,”dddd, mmmm d, yyyy”) • Changing Formulas to Values • Select formulas. Right-click right edge. Drag right, drag left. Release mouse button. Choose Copy Here as Values Only Consult@MrExcel.com

  6. Customizing Excel • Always show full menus with Tools, Customize, Options • Add buttons to toolbars with Tools, Customize, Commands • Create Your Own toolbar • Reset toolbars with Tools, Customize, Toolbars • Recently Used File List with Tools, Options, General • Move Cellpointer Direction after Enter (Tools, Options Edit) Consult@MrExcel.com

  7. Worksheets & Group Mode • Move or Copy Worksheets • Change All Sheets With Group Mode • Arrange Windows to See Two or More Open Workbooks • Synchoronized Scrolling – new in Excel 2003 • Window - New Window to see two worksheets of the same workbook side by side Consult@MrExcel.com

  8. Customize All Future Workbooks • Remember your favorite settings such as headers, footers, margin, column widths. • Tools – Options – General, At Startup, Open All Files In: • Specify a new empty folder • Save file with book.xlt and sheet.xlt names to the folder. • Book.xlt – used with File – New • Sheet.xlt – used with Insert - Worksheet Consult@MrExcel.com

  9. Excel 2007 Preview

  10. Excel 2007 offers larger grid • Big Grid – 1.1 million rows x 16k columns • 17 Billion cells per worksheet Consult@MrExcel.com

  11. Excel 2007 data visualizations • 3 clicks to create: Consult@MrExcel.com

  12. In-cell data bars Consult@MrExcel.com

  13. Sort by Color Consult@MrExcel.com

  14. Chart Improvements Consult@MrExcel.com

  15. Remove Duplicates Consult@MrExcel.com

  16. More Features • Live Preview • Improved Pivot Tables • Easier to find important features • Floating right-click toolbar fades in and out • New IGX Graphics • New Functions: IfError, AverageIf, SumIfS Consult@MrExcel.com

  17. New User Interface – The Ribbon • Large icons, with words. • Logical groupings • Old shortcut keys Ctrl or Alt – still work Consult@MrExcel.com

  18. Excel Features expect “List Format” • Single Heading above each column in the list • If you need a two-row heading, enter it in a single cell with <alt>Enter between each line of the heading. • No entirely blank rows or columns (single blank cells OK) • Advantages: • Sort ranges with single click • Excel intellisense will determine list boundaries Consult@MrExcel.com

  19. Quick Tip #1: Quickly See Sum/Avg • The status bar in Excel offers a quick glimpse of the sum of a range of numbers. Highlight any range, and the 2nd block on the right side of the status bar will show the sum of the range. • Right click this box to change to average, min, max, count, etc. Consult@MrExcel.com

  20. Subtotals • Adding subtotals with Data – Subtotals • Defaults to Sum or Count function based on the type of information in the right-most column. Need to change from Count to Sum if right-most column contains text • Uses =SUBTOTAL() function, like SUM but ignores other totals • Use Group & Outline buttons to show only summary • To copy summary, Edit, GoTo, Special, Visible Cells Only, or Alt+; • When adding two sets of subotals, start with the less-granular measure; add Division totals first and then branch totals. • Formatting only the subtotal rows. • Adding blanks after subtotals Consult@MrExcel.com

  21. Pivot Table Basics • Introduced in Excel 95 – incredibly powerful. • Create a summary of 50,000 rows of data with 6 clicks and without writing formulas. • Use with transactional data in list format. - Should not have months going across your columns • Each header should be unique Consult@MrExcel.com

  22. Pivot Tables • Creating a Pivot Table – Data, Pivot Table…, Finish • Dragging fields to drop zones in pivot table (watch the cursor) • Replace blanks with zeroes • Grouping daily dates to months, quarters, weeks • Limitations – can not move or change • Drill-down • Changing underlying data requires refresh • AutoShow & AutoSort Functions • Manually resequence • Page Fields to add filter criteria • Show Pages to produce many reports by department or customer Consult@MrExcel.com

  23. AutoFilter • Great for seeing only certain records in a list. • Select any cell in the list, Data > Filter > AutoFilter • Able to combine multiple filters on multiple columns • If you need to create complex criteria, Advanced Filter might be the best option. • Clear with Data > Filter > AutoFilter again. Consult@MrExcel.com

  24. Formula Auditing • Show Formulas Mode with Ctrl~ or Ctrl` • Trace Precedents • Trace Dependents • New in Excel 2003: Evaluate Formula • New in Excel 2003: Watch Window Consult@MrExcel.com

  25. AutoSum Tricks • Beware Using AutoSum with Yearly headings • Add many SUM functions with one click: • AutoSum dropdown includes Average, etc. • Cool trick to sum filtered rows: • Use Data – Filter – Autofilter • Add a filter to one column • Add AutoSum. Excel uses =SUBTOTAL instead of SUM Consult@MrExcel.com

  26. Discover New Functions • Function Wizard • Goal Seek • =CONVERT • =WORKDAYS, =NETWORKDAYS • =GCD, =LCM, • Reducing Fractions with Custom Number Format • =ROMAN Consult@MrExcel.com

  27. Formula References • Most references, such as A2, are relative references. When you copy a formula pointing to A2 down, the reference will change to A3, A4, A5, etc. • Sometimes, you want a formula to always point to H1. Use an absolute reference of $H$2. • Use F4 key as shortcut for adding dollar signs • Mixed references have only one dollar sign =$A2*B$1 Consult@MrExcel.com

  28. Making Decisions with IF, AND, OR • =IF(A2>10000,0.02,0)*A2 will calculate a 2% bonus on sales over 10K • =IF(OR(A2>10000,B2>5000),0.02,0)*A2 pays the bonus if one of two criteria are met. • Also functions for AND, NOT • Nest up to 7 If statements: =IF(A2>20000,0.02,IF(A2>5000,0.01,0)) Consult@MrExcel.com

  29. Lookup Functions - VLOOKUP • VLOOKUP is great when the “key” is to the left of the data: =VLOOKUP(A2,MyTable,2,False) • If your data is to the left of the key in the lookup table, you need to use INDEX() and Match() • =MATCH(A2,$Z$1:$Z$10,FALSE) will tell which cell in Z1:Z10 contains a value to match A2. • =INDEX($Z$1:$Z$10,WhichRow, WhichColumn) will return a specific cell from Z1:Z10. Use the Match function to indicate which Row • =INDEX($Z$1:$Z$10,MATCH(A2,$Z$1:$Z$10),FALSE),1) Consult@MrExcel.com

  30. CSE Formulas • Replace literally thousands of formulas with a single CSE formula. • After typing the formula, hold down Ctrl, Shift while pressing enter. • Multiply each cell in B2:B1000 by C2:C1000 and total with this formula: =SUM(B2:B1000*C2:C1000) • Create SUMIF functions with multiple criteria: =SUM((A2:A1000=“East”)*(B2:B1000=“ABC”)*(G2:G1000)) • Incredibly powerful, unknown by most. • See http://www.mrexcel.com/tip011.shtml Consult@MrExcel.com

  31. Macro Introduction • Easy: Macros that format a selection • Harder: Macros that move. Use Relative Recording button! Consult@MrExcel.com

  32. Misc. Tips Web Queries (Learn Excel Page 580) Page Numbers in Multi-Sheet Workbook (P 684) Format Painter (p 711) Fit a Slightly Too-Large Value in a Cell (Page 722) Show Results as Fractions (Page 724) Add Text to an AutoShape (Page 756) - NewShapes.xls Place Cell Contents in an AutoShape (Page 789) - ShapeFormula.xls Use Find and Replace to Find an Asterisk (Page 445) - ReplaceStar.xls Use a Custom Header of “Profit & Loss” (Page 447) Protect Cells with Formulas (Page 350)Use Excel as a Word Processor (Page 56) Quickly Turn a Range on Its Side (Page 78) Copy Cells from One Worksheet to Many sheets (P 84) Have Excel Talk to You (Page 86) Rank a List Without Ties (Page 221) Conditional Formatting Consult@MrExcel.com

  33. Daily Video Podcast • Free • 2-minute tip each weekday • Download from iTunes to your PC or to your iPod. • 600+ Episodes produced so far. Consult@MrExcel.com

  34. www.MrExcel.com/oacubo.html • Free 6 minute video with highlights from today’s presentation. • Link to a low-res e-book version of the book you received today. Feel free to pass on to your associates. • Handout from today with page numbers. Consult@MrExcel.com

More Related