Excel 2013: The Missing Manual (e-book)

Excel 2013: The Missing Manual (e-book) - Opis

The world...s most popular spreadsheet program is now more powerful than ever, but it...s also more complex. That...s where this Missing Manual comes in. With crystal-clear explanations and hands-on examples, Excel 2013: The Missing Manual shows you how to master Excel so you can easily track, analyze, and chart your data. You...ll be using new features like PowerPivot and Flash Fill in no time.The important stuff you need to know:Go from novice to ace. Learn how to analyze your data, from writing your first formula to charting your results.Illustrate trends. Discover the clearest way to present your data using Excel...s new Quick Analysis feature.Broaden your analysis. Use pivot tables, slicers, and timelines to examine your data from different perspectives.Import data. Pull data from a variety of sources, including website data feeds and corporate databases.Work from the Web. Launch and manage your workbooks on the road, using the new Excel Web App.Share your worksheets. Store Excel files on SkyDrive and collaborate with colleagues on Facebook, Twitter, and LinkedIn.Master the new data model. Use PowerPivot to work with millions of rows of data.Make calculations. Review financial data, use math and scientific formulas, and perform statistical analyses. Spis treści:
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 (...) więcej 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 O autorze: Matthew MacDonald jest autorem, szkoleniowcem i programistą z tytułem MSCD. Napisał kilkanaście książek o programowaniu .NET. W dawnym życiu, którego już prawie nie pamięta, studiował literaturę angielską i fizykę teoretyczną. mniej