Excel 2010: The Missing Manual

Spis treści książki

• Excel 2010: The Missing Manual
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • The Missing Credits
    • About the Author
    • About the Creative Team
    • Acknowledgements
    • The Missing Manual Series
  • Introduction
    • What You Can Do with Excel
    • The Modern Face of Excel
      • The Ribbon
      • Using the Ribbon with the Keyboard
      • Backstage View
      • The Quick Access Toolbar
    • Excels New Features
    • About This Book
      • About the Outline
      • AboutTheseArrows
        • Contextual tabs
        • Buttons with menus
        • Dialog box launchers
        • Backstage view
        • Ordinary menus
      • About Shortcut Keys
      • Examples
      • About MissingManuals.com
      • Safari Books Online
  • One. Worksheet Basics
    • 1. Creating Your First Spreadsheet
      • Creating a Basic Worksheet
        • Starting a New Workbook
        • Adding the Column Titles
        • Adding Data
        • Editing Data
      • Moving Around the Grid
        • Shortcut Keys
        • The Go To Feature
      • A Tour of the Excel Window
        • The Tabs of the Ribbon
        • The Formula Bar
        • The Status Bar
        • Going Backstage
        • Excel Options
      • Saving Files
        • The Excel File Format
        • Sharing Your Spreadsheet with Excel 2007
        • Saving Your Spreadsheet for Excel 2003
        • Saving a Spreadsheet in Other Formats
        • Saving Your Spreadsheet As a PDF
        • Saving Your Spreadsheet As an HTML File
        • Saving Your Spreadsheet with a Password
        • Disaster Recovery
          • AutoRecover settings
      • Opening Files
        • Opening Recent Documents
        • Protected View
        • Opening Fileswith a Twist
        • Working with Multiple Open Spreadsheets
    • 2. Adding Information to Worksheets
      • Adding Different Types of Data
        • How Excel Identifies Text
        • How Excel Identifies Numbers
        • How Excel Identifies Dates and Times
        • Regional Dating
      • Handy Timesavers
        • AutoComplete
        • AutoCorrect
        • AutoFill
          • Custom AutoFill lists
        • AutoFit
      • Dealing with Change: Undo, Redo, and AutoRecover
        • Undo and Redo
        • AutoRecover
      • Spell Check
        • Spell Checking Options
      • Adding Hyperlinks
        • Creating a Link to a Web Page or Document
        • Creating a Link to a Worksheet Location
    • 3. Moving Data
      • Selecting Cells
      • Making Continuous Range Selections
      • Making Noncontiguous Selections
      • Automatically Selecting Your Data
      • Making Selections with the Keyboard
      • Moving Cells Around
      • A Simple Cut-and-Paste or Copy-and-Paste
      • A Quicker Cut-and-Paste or Copy-and-Paste
      • Fancy Pasting Tricks
      • Paste Special
      • Copying Multiple Items with the Clipboard
      • Adding and Moving Columns or Rows
      • Inserting Columns and Rows
      • Inserting Rows
      • Inserting Copied or Cut Cells
      • Deleting Columns and Rows
    • 4. Managing Worksheets
      • Worksheets and Workbooks
        • Adding and Removing Worksheets
        • Hiding Worksheets
        • Naming and Rearranging Worksheets
        • Moving Worksheets from One Workbook to Another
        • Grouping Worksheets
          • Managing grouped sheets
          • Formatting grouped sheets
          • Entering data in grouped sheets
      • Find and Replace
        • The Basic Find
        • Find All
        • More Advanced Searches
        • Finding Formatted Cells
        • Finding and Replacing Values
    • 5. Formatting Cells
      • Formatting Cell Values
        • Changing the Cell Value Format
        • Formatting Numbers
          • General
          • Number
          • Currency
          • Accounting
          • Percentage
          • Fraction
          • Scientific
          • Text
        • Formatting Dates and Times
        • Special Formats for Special Numbers
        • Custom Formats
          • Creating a custom format
          • Custom format string codes
          • Date and time format strings
          • Number format strings
          • Text format strings
      • Formatting Cell Appearance
        • Alignment and Orientation
        • Fonts and Color
          • Special characters
        • Borders and Fills
        • Drawing Borders by Hand
    • 6. Smart Formatting Tricks
      • The Format Painter
      • Styles and Themes
        • Custom Styles
        • Modifying Styles
        • Transferring Styles Between Workbooks
        • Themes: A Package of Styles
          • Fonts
          • Colors
          • Effects
        • Modifying Themes
      • Conditional Formatting
        • The Basics of Conditional Formatting
        • Highlighting Specific Values
        • Using Multiple Rules
    • 7. Viewing and Printing Worksheets
      • Controlling Your View
        • Zooming
        • Viewing Distant Parts of a Spreadsheet at Once
        • Freezing Columns or Rows
        • Hiding Data
        • Saving View Settings
        • Viewing Multiple Workbooks at Once
      • Printing
        • How to Print an Excel File
        • Page Layout View: A Better Print Preview
        • Creating Headers and Footers
      • Controlling Pagination
        • Page Breaks
        • Scaling
        • Page Break Preview: A Bird's-Eye View of Your Worksheet
  • Two. Formulas and Functions
    • 8. Building Basic Formulas
      • Creating a Basic Formula
        • Excel's Order of Operations
        • Cell References
        • How Excel Formats Cells That Contain Cell References
      • Functions
        • Using a Function in a Formula
        • Using Cell References with a Function
        • Using Cell Ranges with a Function
        • Excel Compatibility Functions
      • Formula Errors
      • Logical Operators
      • Formula Shortcuts
        • Point-and-Click Formula Creation
        • Point-and-Click Formula Editing
        • The Formulas Tab
        • Using the Insert Function Button
      • Copying Formulas
        • Absolute Cell References
        • Partially Fixed References
        • Referring to Other Worksheets
        • Referring to Other Workbooks
          • Updating links
          • Troubleshooting links
          • The Edit Links dialog box
    • 9. Math and Statistical Functions
      • Rounding Numbers
        • ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers
        • MROUND(), CEILING(), and FLOOR(): More Rounding Functions
        • INT() and TRUNC(): Chopping Off Non-Whole Numbers
        • EVEN() and ODD(): Rounding Up to Even or Odd Values
      • Groups of Numbers
        • SUM(): Summing Up Numbers
        • COUNT(), COUNTA(), and COUNTBLANK(): Counting Items in a List
        • MAX() and MIN(): Finding Maximum and Minimum Values
        • LARGE(), SMALL(), and RANK(): Ranking Numbers
        • AVERAGE() and MEDIAN(): Finding Average or Median Values
        • PERCENTILE() and PERCENTRANK(): Advanced Ranking Functions
        • FREQUENCY(): Putting Numbers into Grouped Ranges
        • MODE(): Finding Numbers That Frequently Occur in a List
      • General Math Functions
        • PRODUCT(), FACT(), POWER(), and SQRT(): Products, Factorials,Powers, and Square Roots
        • QUOTIENT() and MOD(): Higher Division
        • ABS() and SIGN(): Absolute Value and Determining a Number's Sign
        • RAND() and RANDBETWEEN(): Generating Random Numbers
        • GCD() and LCM(): Greatest and Least Common Denominator
        • COMBIN() and PERMUT(): Figuring Combinations and Permutations
      • Trigonometry and Advanced Math
      • Advanced Statistics
    • 10. Financial Functions
      • The World of Finance
      • Financial Functions
        • FV(): Future Value
        • PV(): Present Value
        • PMT(), PPMT(), and IPMT(): Calculating the Number of Payments You Need to Make
        • NPER(): Figuring Out How Much Time You'll Need to Pay Offa Loan or Meet an Investment Target
        • RATE(): Figuring the Interest Rate You Need to Achieve Future Value
        • NPV() and IRR(): Net Present Value and Internal Rate of Return
      • Depreciation
      • Other Financial Functions
    • 11. Manipulating Dates, Times, and Text
      • Manipulating Text
        • CONCATENATE(): Joining Strings of Text Together
        • LEFT(), MID(), and RIGHT(): Copying Portions of a Text String
        • LEN(), FIND(), and SEARCH(): Counting Characters in a String
        • UPPER(), LOWER(), and PROPER(): Changing Capitalization
        • TRIM() and CLEAN(): Removing Unwanted Spaces and Non-Printing Characters
        • SUBSTITUTE(): Replacing One Sequence of Characters with Another
        • TEXT(), VALUE(), FIXED(), and DOLLAR(): Converting Text to Numbers and Vice Versa
        • Other Text Functions
      • Manipulating Dates and Times
      • Math with Dates and Times
        • Using Dates and Times with Ordinary Functions
      • Date and Time Functions
        • TODAY() and NOW(): Inserting the Current Date and Time
        • DATE() and TIME(): Dates and Times in Calculations
        • DAY(), MONTH(), and YEAR(): More Date Calculations
        • HOUR(), MINUTE(), SECOND(): More Time Calculations
        • WEEKDAY(): Determining the Day of the Week
        • DATEDIF(): Calculating the Difference Between Dates
        • DATEVALUE() and TIMEVALUE(): Converting Dates and Times into Serial Numbers
        • DAYS360(): Finding Out the Number of Days Between Two Dates
        • EDATE(): Calculating Future Dates
        • YEARFRAC(): Calculating the Percentage of a Year Between Two Dates
        • EOMONTH(): Finding the Last Day of Any Month
        • NETWORKDAYS(): Counting the Number of Business Days
        • WORKDAY(): Figuring Out When Days Will Fall in the Future
        • WEEKNUM(): Figuring Out in Which Week a Date Falls
    • 12. Lookup, Reference, and Information Functions
      • The Basic Lookup
        • VLOOKUP(): Vertical Lookups
        • HLOOKUP(): Horizontal Lookups
      • Advanced Lookups
        • MATCH(): Finding the Position of Items in a Range
        • INDEX(): Retrieving the Value from a Cell
        • Performing a "Left Lookup"
        • Performing a Double Lookup
        • OFFSET(): Shifting Cell References
        • Other Reference and Lookup Functions
        • INDIRECT() and ADDRESS(): Working with Cell References Stored As Text
        • TRANSPOSE(): Changing Rows into Columns and Vice Versa
        • The HYPERLINK() Function: Creating a Dynamic Link
      • Information Functions
        • The "IS" Functions: Checking the Value Inside a Cell
        • TYPE() and ERROR.TYPE(): Finding a Value's Data Type or Error Type
        • INFO() and CELL(): Gathering Info About Your Computer and Your Worksheet's Cells
      • Tutorial: Generating Invoices from a Product Catalog
    • 13. Advanced Formula Writing and Troubleshooting
      • Conditions in Formulas
        • IF(): Building Conditional Formulas
        • COUNTIF(): Counting Only the Cells You Specify
        • SUMIF(): Adding Only the Cells You Specify
        • COUNTIFS() and SUMIFS(): Counting and Summing Using Multiple Criteria
      • Descriptive Names for Cell References
        • Creating and Using a Named Range
        • Creating Slightly Smarter Named Ranges
        • Naming Formulas and Constants
        • Managing Named Ranges
        • Automatically Creating Named Ranges
        • Applying Names to Existing Formulas
      • Variable Data Tables
        • Creating a One-Variable Data Table
        • Creating a Two-Variable Data Table
      • Controlling Recalculation
      • Solving Formula Errors
        • Step-by-Step Evaluation
        • Tracing Precedents and Dependents
        • Error Checking
  • Three. Organizing Worksheets
    • 14. Tables: List Management Made Easy
      • The Basics of Tables
        • Creating a Table
        • Formatting a Table
        • Editing a Table
        • Selecting Parts of a Table
      • Sorting and Filtering a Table
        • Applying a Simple Sort Order
        • Sorting with Multiple Criteria
        • Sorting by Color
        • Filtering with the List of Values
        • Creating Smarter Filters
          • Filtering dates
          • Filtering numbers
          • Filtering text
      • Dealing with Duplicate Rows
        • Highlighting Duplicates
        • Removing Duplicates Automatically
      • Performing Table Calculations
        • Dynamic Calculations
        • Column Names
        • Table Names
        • The Total Row
        • The SUBTOTAL() Function
        • The Database Functions
    • 15. Grouping and Outlining Data
      • Basic Data Grouping
        • Creating a Group
        • Nesting Groups Within Groups
        • Summarizing Your Data
        • Combining Data from Multiple Tables
      • Grouping Timesavers
        • Auto Outline
        • Automatic Subtotaling
    • 16. Templates
      • Understanding Templates
      • Creating a New Workbook from a Template
        • Downloading Templates (Method 1: From Backstage View)
        • Downloading Templates (Method 2: The Office Online Website)
      • Creating Templates
        • Understanding Custom Templates
        • Building a Custom Template
        • Sharing Templates with Others
  • Four. Charts and Graphics
    • 17. Creating Basic Charts
      • Charting 101
        • Embedded and Standalone Charts
        • Creating a Chart with the Ribbon
        • The Chart Tools Ribbon Tabs
      • Basic Tasks with Charts
        • Moving and Resizing a Chart
        • Creating a Standalone Chart
        • Editing and Adding to Chart Data
        • Changing the Chart Type
        • Printing Charts
          • Embedded charts
          • Standalone charts
      • Practical Charting
        • Charts with Multiple Series of Numbers
        • Controlling the Data Excel Plots on the X-Axis
        • Data That Uses a Date or Time Scale
        • Noncontiguous Chart Ranges
        • Changing the Order of Your Data Series
        • Changing the Way Excel Plots Blank Values
      • Chart Types
        • Column
        • Bar
        • Line
        • Pie
        • Area
        • XY (Scatter)
        • Stock
        • Surface
        • Donut
        • Bubble
        • Radar
    • 18. Formatting and Perfecting Charts
      • Chart Styles and Layouts
        • Chart Styles
        • Chart Layouts
      • Adding Chart Elements
        • Adding Titles
        • Adding a Legend
        • Adding Data Labels to a Series
        • Adding Individual Data Labels
        • Adding a Data Table
      • Selecting Chart Elements
      • Formatting Chart Elements
        • Coloring the Background
        • Fancy Fills
          • Gradient fills
          • Texture fills
          • Picture fills
        • Fancy Borders and Lines
        • Formatting Data Series and Data Points
        • Reusing Your Favorite Charts with Templates
      • Improving Your Charts
        • Controlling a Chart's Scale
        • Adding a Trendline
        • Adding Error Bars to Scientific Data
        • Formatting 3-D Charts
        • Changing the Shape of a 3-D Column
      • Advanced Charting
        • Exploding Slices in a Pie
        • Grouping Slices in a Pie
        • Gaps, Widths, and Overlays in a Column Chart
        • Creating Combination Charts
    • 19. Inserting Graphics
      • Adding Pictures to a Worksheet
        • Inserting a Picture
        • Positioning and Resizing a Picture
        • Picture Touch-Up
        • Compressing Pictures
        • Cropping and Shaping a Picture
        • Picture Borders, Effects, and Styles
      • Excel's Clip Art Library
      • Drawing Shapes
        • Drawing a Shape
        • Adding Text to a Shape
        • Selecting and Arranging Shapes
        • Connecting Shapes
        • SmartArt
  • Five. Advanced Data Analysis
    • 20. Visualizing Your Data
      • Data Bars
        • Editing a Formatting Rule
        • Fine-Tuning Data Bars
          • Putting data bars in a separate column
          • Changing the data bar scale
      • Color Scales
        • Fine-Tuning Color Scales
      • Icon Sets
        • Fine-Tuning Icon Sets
          • Giving your icons added significance
          • Hiding icons for some values
      • Sparklines
        • Creating a Sparkline
        • Changing the Axis
        • Markers
    • 21. Scenarios and Goal Seeking
      • Using Scenarios
        • Creating a New Scenario
        • Managing Scenarios
        • Creating a Summary Report
      • Using Goal Seek
        • Goal Seeking with Complex Equations
      • Solver
        • Understanding Solver
        • Defining a Problem in Solver
        • More Advanced Solver Problems
        • Saving Solver Models
        • Configuring Solver
    • 22. Pivot Tables
      • Summary Tables Revisited
        • Life Without Pivot Tables
        • Life with Pivot Tables
      • Building Pivot Tables
        • Preparing a Pivot Table
        • Pivot Table Regions
        • Laying Out a Pivot Table
        • Formatting a Pivot Table
        • Rearranging a Pivot Table
        • Getting to the Source
      • Multi-Layered Pivot Tables
        • Hiding and Showing Details
      • Fine-Tuning Pivot Table Calculations
        • Changing the Type of Calculation
        • Adding a Calculated Field
      • Filtering a Pivot Table
        • Report Filtering
        • Slicers
        • Group Filtering
      • Pivot Charts
        • Creating a Pivot Chart
        • Manipulating a Pivot Chart
    • 23. Analyzing Databases, XML, and Web Pages
      • Excel and Databases
        • Connecting to an Access Database
        • Refreshing Data
        • Data Source Security
        • Connecting to a SQL Server Database
        • Reusing Your Database Connection
      • Understanding XML
        • What Is XML, Really?
        • Three Rules of XML
          • The prolog
          • Elements
          • Nesting
        • XML Files and Schemas
      • Excel and XML
        • Mapping a Simple Document
        • Importing and Exporting XML
        • Mapping Lists
        • Gaining the Benefits of XML Mapping
      • Web Queries
        • The Limitations of Web Queries
        • Creating a Web Query
        • The Research PaneA Web Query Alternative
  • Six. Sharing Data with the Rest of the World
    • 24. Protecting Your Workbooks
      • Understanding Excel's Safeguards
      • Data Validation
        • Settings
        • Input Message
        • Error Alert
        • Data Validation with Formulas and Cell References
        • Data Validation with Lists
      • Locked and Hidden Cells
        • Protecting a Worksheet
        • Protecting the Entire Workbook
        • Protecting Cell Ranges (with More Passwords)
        • Allowing Specific Windows Users to Edit a Range
    • 25. Worksheet Collaboration
      • Your Excel Identity
      • Preparing Your Workbook
        • Workbook Protection
        • Checking for Issues
        • Document Properties
      • Distributing a Document
        • Sending by Email
        • Uploading to the Web
      • Adding Comments
        • Inserting a Comment
        • Showing and Hiding Comments
        • Fine-Tuning Comments
        • Reviewing Comments
        • Printing Comments
      • Tracking Changes
        • Switching On Change Tracking
        • Understanding the Change Log
        • Highlighting Changes
        • Examining the Change Log
        • Accepting and Rejecting Changes
        • Merging Multiple Revisions into One Workbook
      • Sharing Your Workbook
        • Multiple Users Without Workbook Sharing
        • Turning On Workbook Sharing
        • Workbook Sharing in Action
    • 26. Using Excel on the Web
      • Putting Your Files Online
        • Introducing SkyDrive
        • Your Web Workflow
        • Uploading a File to SkyDrive (Using Your Browser)
        • Uploading a Workbook to SkyDrive (Using Excel)
        • Uploading a Workbook to a SharePoint Server
        • Viewing a File in Your SkyDrive Account
        • Viewing a File in Someone Else's SkyDrive Account
      • Using the Excel Web App
        • Supported Features
        • Saving Files
        • Collaboration: The Excel Web App's Specialty
        • Taking a Workbook Back to Desktop Excel
        • Unsupported Features
        • Partially Supported Features
    • 27. Exchanging Data with Other Programs
      • Sharing Information in Windows
      • Embedding and Linking Objects
        • Exporting Charts Out of Excel
        • Editing a Linked Object
        • Editing an Embedded Object
        • Importing Objects into Excel
      • Transferring Data
        • Exporting Tables of Data
        • Importing Tables of Data
        • Importing Text Files
  • Seven. Programming Excel
    • 28. Automating Tasks with Macros
      • Macros 101
        • Macro-Free and Macro-Enabled Workbooks
      • The Macro Recorder
        • Relative and Absolute Recording
        • Where Macros Live
        • Recording a Macro
        • Playing a Macro
      • Macro Security
        • Trusted Documents
        • Temporary Trust
        • The Trust Center
        • Setting Up a Trusted Location
      • Creating Practical Macros
        • Inserting a Header
        • Alternating Row Formatting
        • A Combined Task
        • Placing a Macro on the Quick Access Toolbar
        • Attaching a Macro to a Button Inside a Worksheet
    • 29. Programming Spreadsheets with VBA
      • The Visual Basic Editor
        • The Project Window
        • Modules and Macros
        • Finding and Moving Macros
        • Debugging a Macro
      • Understanding Macro Code
        • The Anatomy of a Macro
        • Objects 101
        • Using Properties and Methods
        • Hunting for Objects
      • Exploring the VBA Language
        • Entering Text in the Current Cell
        • Interacting with Other Cells
        • Editing Specific Cells
        • Formatting Cells
        • Using Variables
        • Making Decisions
        • Repeating Actions with a Loop
        • Creating Custom Functions
  • Eight. Appendix
    • A. Customizing the Ribbon
      • Adding Your Favorites to the QAT
        • Adding Buttons
        • Customizing Specific Workbooks
      • Personalizing the Ribbon
        • Changing Existing Tabs
        • Creating Your Own Tab
        • Saving and Reusing Your Custom Ribbon
  • Index
  • About the Author
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje