Excel 2013: The Missing Manual Matthew MacDonald

Spis treści książki

• Excel 2013: The Missing Manual
• The Missing Credits
  • About the Author
  • About the Creative Team
  • Acknowledgments
  • The Missing Manual Series
• Introduction
  • What You Can Do with Excel
  • The New Features in Excel 2013
    • The Office 365 Subscription Service
    • Office RT: Office for Tablets
    • The Office Web Apps
  • About This Book
    • About the Outline
    • AboutTheseArrows
      • Contextual Tabs
      • Buttons with Menus
      • Dialog Box Launchers
      • Backstage View
      • Ordinary Menus
    • About Shortcut Keys
  • Online Resources
    • Registration
    • Feedback
    • Errata
    • Examples
    • Safari Books Online
• 1. Worksheet Basics
  • 1. Creating Your First Spreadsheet
    • Starting a Workbook
    • Adding Information to a Worksheet
      • Adding Column Titles
      • Adding Data
      • Editing Data
      • Editing Cells with the Formula Bar
    • Using the Ribbon
      • The Tabs of the Ribbon
      • Collapsing the Ribbon
      • Using the Ribbon with the Keyboard
      • The Quick Access Toolbar
    • Using the Status Bar
    • Going Backstage
    • Saving Files
      • The Excel File Format
      • Sharing Your Spreadsheet with Older Versions of Excel
      • Saving Your Spreadsheet for Excel 2003
      • Saving Your Spreadsheet As a PDF
      • Password-Protecting Your Spreadsheet
      • Disaster Recovery
        • AutoRecover Settings
    • Opening Files
      • Opening Files in Other Formats
      • 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
      • Moving Around the Grid with Shortcut Keys
      • The Go To Feature
      • AutoComplete
      • AutoCorrect
      • AutoFit
      • AutoFill
        • Custom AutoFill Lists
      • Flash Fill
    • 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
      • Inserting Rows
      • Inserting Copied or Cut Cells
      • Deleting Columns and Rows
  • 4. Managing Worksheets
    • Worksheets and Workbooks
      • Adding and Removing Worksheets
      • Moving Between 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 Birds-Eye View of Your Worksheet
• 2. Formulas and Functions
  • 8. Building Basic Formulas
    • Creating a Basic Formula
      • Excels 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 Window
  • 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 Numbers Sign
      • RAND() and RANDBETWEEN(): Generating Random Numbers
      • GCD() and LCM(): Greatest and Least Common Denominator
      • COMBIN() and PERMUT(): Figuring Combinations and Permutations
      • BASE() and DECIMAL(): Converting Numbers to Different Bases
      • ROMAN() and ARABIC(): Using Roman Numerals
    • 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 Need to Pay Off a Loan or Meet an Investment Target
      • RATE(): Calculating the Interest Rate You Need to Achieve a Target
      • RRI(): Calculating the Interest Rate Your Investments Achieve
      • 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
      • 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
      • IFNA(): Dealing with Failed 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
      • FORMULATEXT(): Getting the Text of a Formula
      • Other Reference and Lookup Functions
      • INDIRECT() and ADDRESS(): Working with Cell References Stored As Text
      • TRANSPOSE(): Changing Rows to 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 Values Data Type or Error Type
      • INFO() and CELL(): Gathering Info About Your Computer and Your Worksheets Cells
    • Tutorial: Generating Invoices from a Product Catalog
      • Dealing with Blank Values
      • Dealing with Incorrect Values
  • 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
• 3. Organizing Your Information
  • 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 a Table
      • Applying a Simple Sort Order
      • Sorting with Multiple Criteria
      • Sorting by Color
    • Filtering a Table
      • Hiding Specific Values
      • Filtering Dates
      • Filtering Numbers
      • Filtering Text
      • Applying Filters with Slicers
    • 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
    • Using the Office Online Templates
      • Creating a New Workbook from an Office Online Template
      • Make Your Favorite Templates Stick Around
      • Downloading Templates from the Office Online Website
    • Rolling Your Own Templates
      • Setting Up a Personal Template Folder
      • Building a Custom Template
• 4. Charts and Graphics
  • 17. Creating Basic Charts
    • Charting 101
      • Embedded and Standalone Charts
      • Adding a Recommended Chart
      • Picking from the Full Range of Charts
      • Selecting a Chart
    • Basic Tasks with Charts
      • Moving and Resizing a Chart
      • Creating a Standalone Chart
      • Editing and Adding to Chart Data
      • Filtering 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
      • Scatter (XY)
      • Stock
      • Surface
      • Donut
      • Bubble
      • Radar
  • 18. Formatting and Perfecting Charts
    • Chart Styles and Layouts
      • Chart Styles
      • Chart Colors
      • Chart Layouts
    • Adding Chart Elements
      • Adding Titles
      • Adding a Legend
      • Adding Data Labels to a Series
      • Adding Individual Data Labels
      • Adding a Data Table
    • Formatting Chart Elements
      • Selecting a Chart Element
      • Coloring the Background
      • Fancy Fills
        • Gradient Fills
        • Texture Fills
        • Picture Fills
      • Fancy Borders and Lines
      • Formatting Data Series and Data Points
    • Improving Your Charts
      • Controlling a Charts 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 from a File
      • Positioning and Resizing a Picture
      • Picture Touch-Up
      • Removing the Background Behind a Picture
      • Cropping and Shaping a Picture
      • Picture Borders, Effects, and Styles
      • Compressing Pictures
    • Excels Clip Art Library
    • Drawing Shapes
      • Drawing a Shape
      • Adding Text to a Shape
      • Selecting and Arranging Shapes
      • Connecting Shapes
    • SmartArt
  • 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
• 5. Sharing Data with the Rest of the World
  • 21. Protecting Your Workbooks
    • Understanding Excels 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 an Entire Workbook
      • Protecting Cell Ranges (with More Passwords)
      • Allowing Specific Windows Users to Edit a Range
  • 22. Worksheet Collaboration
    • Your Excel Identity
    • Preparing Your Workbook
      • Workbook Protection
      • Checking for Issues
      • Document Properties
    • Distributing Your Workbook
      • Sending by Email
    • Adding Comments
      • Inserting a Comment
      • Showing and Hiding Comments
      • Fine-Tuning Comments
      • Reviewing Comments
      • Printing Comments
    • Tracking Changes
      • Turning 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
    • Reviewing Workbooks with Inquire
      • Turning on the Inquire Add-In
      • Generating a Spreadsheet Report
      • Comparing two Workbooks
      • Following the Formula Trail
  • 23. Using Excel on the Web
    • Putting Your Files Online
      • Introducing SkyDrive
      • Using SkyDrive
      • Your Web Workflow
      • Uploading a File to SkyDrive Using Your Browser
      • Uploading a Workbook to SkyDrive Using Excel
    • Using the Excel Web App
      • Supported Features
      • Unsupported Features
      • Partially Supported Features
      • Saving Files
      • Taking a Workbook Back to Desktop Excel
    • Sharing Your Files
      • Inviting Specific People
      • Creating a Sharing Link
      • Spreading the Word on Social Networks
      • Collaboration: The Excel Web Apps Specialty
      • Creating a Survey
  • 24. Exchanging Data with Other Programs
    • Sharing Information in Windows
    • Embedding and Linking Objects
      • Embedding an Excel Chart in Another Program
      • Creating a Linked Chart Object
      • Importing Objects into Excel
    • Transferring Data
      • Exporting Tables of Data
      • Importing Tables of Data
      • Importing Text Files
• 6. Advanced Data Analysis
  • 25. 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
  • 26. 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
      • Timelines
      • Group Filtering
    • Pivot Charts
      • Creating a Pivot Chart
      • Manipulating a Pivot Chart
  • 27. Analyzing Databases
    • Excel and Databases
    • Creating a Data Connection
      • Connecting to an Access Database
      • Refreshing Data
      • Data Source Security
      • Connecting to a SQL Server Database
      • Reusing Your Database Connection
    • The Data Model: Boosting Pivot Tables
      • Dealing with Big Tables
      • Understanding Relationships
      • Defining a Relationship
      • Importing Relationships
      • Going Behind the Scenes with PowerPivot
      • Creating a Calculated Field with PowerPivot
  • 28. Analyzing XML and Web Data
    • Understanding XML
      • What Is XML, Really?
      • Three Rules of XML
        • The Prolog
        • Elements
        • Nesting
      • XML Files and Schemas
    • Retrieving Information from XML
      • Mapping a Simple Document
      • Importing and Exporting XML
      • Mapping Lists
      • Gaining the Benefits of XML Mapping
    • Creating Web Queries
      • The Limitations of Web Queries
      • Creating a Web Query
    • Connecting to Online Data Services with OData
      • Creating an OData Query
• 7. Programming Excel
  • 29. 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
  • 30. 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
• 8. 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
      • Exporting Your Custom Ribbon
• Index
• About the Author
• Copyright

pokaż cały spis treści

ukryj recenzje