Excel 2013 In Depth
Que Corporation,U.S. (Verlag)
978-0-7897-4857-7 (ISBN)
- Titel erscheint in neuer Auflage
- Artikel merken
Build more trustworthy, error-resistant, flexible, extensible, intelligent, and understandable spreadsheets
Get more productive with Excel 2013’s new Start Screen and Timelines
Create formulas, charts, subtotals, and pivot tables faster with new Flash Fill and Analysis Lens
Quickly apply attractive, consistent formats
Master every function you’ll ever need,- including powerful new web services functions
Solve real-world business intelligence analysis problems
Create amazing PowerPivot data mashups that integrate information from anywhere
Use Power View to generate stunningly intuitive maps, dashboards, and data visualizations
Share workbooks on the Web and social networks
Leverage the improved Excel Web App to create highly interactive web pages and online surveys
Automate repetitive functions using Excel macros
Supercharge your workbooks with new apps from the Excel App Store
Like all In Depth books, Excel 2013 In Depth delivers complete coverage with detailed solutions, and troubleshooting help for tough problems you can’t fix on your own. Whatever you intend to do with Excel 2013, this is the only book you’ll need!
Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 36 books about Microsoft Excel, writes the monthly Excel column for Strategic Finance magazine, and his Excel tips appear regularly in the CFO Excel Pro Newsletter and CFO magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches–working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen.
Introduction
Part I Mastering the New User Interface
Chapter 1 Staying Connected Using Excel 2013
Displaying Two Workbooks on Two Monitors
Understanding the Dark Side of SDI
Forcing Excel to Open in a New Instance
Signing In to Excel 2013
Introducing the Excel 2013 Start Screen
Revealing the Fatal Flaw of the Start Screen
Dismissing the Start Screen with the Escape Key
Dismissing the Start Screen Permanently
Using the Cloud for Storage and More
Relying On the Cloud
Linking Excel to Various Accounts
Using the Open and Save As Panels
Chapter 2 Introducing Flash Fill and Analysis Lens
Cleaning Data with Flash Fill
Coaching Flash Fill with a Second Example
Inserting New Characters in a Telephone Number
Using Commas Helps Flash Fill
Flash Fill Will Not Automatically Fill in Numbers
Using Formatting with Dates
Using Filter to Flash Fill a Subset of Records
Troubleshooting Flash Fill
Discovering Interesting Things in Your Data Using the Analysis Lens
Color Coding Cells in the Data
Charting Your Data
Adding Statistics to the Bottom or Right Edge of Your Data
Creating a Summary Report from Your Data
Adding Tiny Charts to Each Row
I’ve Used the Analysis Lens! Now What?
Chapter 3 Using the Excel Interface
Using the Ribbon
Using Fly-out Menus and Galleries
Rolling Through the Ribbon Tabs
Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands
Resizing Excel Changes the Ribbon
Activating the Developer Tab
Activating Contextual Ribbon Tabs
Finding Lost Commands on the Ribbon
Shrinking the Ribbon
Using the Quick Access Toolbar
Adding Icons to the QAT
Removing Commands from the QAT
Customizing the QAT
Assigning VBA Macros to Quick Access Toolbar Buttons
Using the Full-Screen File Menu
Pressing the Esc Key to Close Backstage View
Recovering Unsaved Workbooks
Clearing the Recent Workbooks List
Getting Information About the Current Workbook
Marking a Workbook as Final to Prevent Editing
Finding Hidden Content Using the Document Inspector
Using Other Excel Interface Improvements
Adding White Space Around Icons Using Touch Mode
Previewing Paste Using the Paste Options Gallery
Accessing the Gallery After Performing a Paste Operation
Accessing the Paste Options Gallery from the Right-Click Menu
Accessing the Paste Options Gallery from the Paste Drop-Down
Using the New Sheet Icon to Add Worksheets
Navigating Through Many Worksheets Using the Controls in the Lower Left
Using the Mini Toolbar to Format Selected Text
Getting the Mini Toolbar Back
Disabling the Mini Toolbar
Expanding the Formula Bar
Zooming In and Out on a Worksheet
Using the Status Bar to Add Numbers
Switching Between Normal View, Page Break Preview, and Page Layout View Modes
Chapter 4 Customizing Excel
Performing a Simple Ribbon Modification
Adding a New Ribbon Tab
Sharing Customizations with Others
Questions About Ribbon Customization
Introducing the Excel Options Dialog
Getting Help with a Setting
New Options in Excel 2013
Using AutoRecover Options
Controlling Image Sizes
Working with Protected View for Files Originating from the Internet
Working with Trusted Document Settings
Ten Options to Consider
Five Excel Oddities
Chapter 5 Extending Excel with Excel Apps and Add-Ins
Using Apps for Office
Using Traditional Add-Ins
Charting Utilities from Jon Peltier
Loading PDF Data to Excel by Using Able2Extract
Accessing More Functions by Using MoreFunc.dll
General-Purpose Utility Suites
Utilities for Data Analysis Tasks
Chapter 6 Keyboard Shortcuts
Using New Keyboard Accelerators
Selecting Icons on the Ribbon
Selecting Options from a Gallery
Navigating Within Drop-Down Lists
Backing Up One Level Through a Menu
Dealing with Keyboard Accelerator Confusion
Selecting from Legacy Dialog Boxes
Using the Shortcut Keys
Using My Favorite Shortcut Keys
Quickly Move Between Worksheets
Jumping to the Bottom of Data with Ctrl+Arrow
Selecting the Current Region with Ctrl+*
Jumping to the Next Corner of a Selection
Pop Open the Right-Click Menu Using Shift+F10
Crossing Tasks Off Your List with Ctrl+5
Date-Stamp or Time-Stamp Using Ctrl+; or Ctrl+:
Repeating the Last Task with F4
Adding Dollar Signs to a Reference with F4
Finding the One Thing That Takes You Too Much Time
Using Excel 2003 Keyboard Accelerators
Invoking an Excel 2003 Alt Shortcut
Determining Which Commands Work in Legacy Mode
Chapter 7 The Big Grid and File Formats
Excel Grid Limits
Why Are There Only 65,536 Rows in My Excel 2013 Spreadsheet?
Other Limits in Excel 2013
Tips for Navigating the Big Grid
Using Shortcut Keys to Move Around
Using the End Key to Navigate
Using the Current Range to Navigate
Using Go To for Navigation
Understanding the File Formats
A Brief History of File Formats
Using the New Binary File Format: BIFF12
Using the New XML File Formats: XLSX and XLSM
Version Compatibility
Opening Excel 2013 Files in Excel 2002 or 2003
Minor Loss of Fidelity
Significant Loss of Functionality
Creating Excel 2013 File Formats in Excel 2003
Opening Excel 2013 Files in Excel 2007
Part II Calculating with Excel
Chapter 8 Understanding Formulas
Getting the Most from This Chapter
Introduction to Formulas
Formulas Versus Values
Entering Your First Formula
Building a Formula
The Relative Nature of Formulas
Overriding Relative Behavior: Absolute Cell References
Using Mixed References to Combine Features of Relative and Absolute References
Using the F4 Key to Simplify Dollar Sign Entry
Three Methods of Entering Formulas
Enter Formulas Using the Mouse Method
Entering Formulas Using the Arrow Key Method
Entering the Same Formula in Many Cells
Copying a Formula by Using Ctrl+Enter
Copying a Formula by Dragging the Fill Handle
Double-Click the Fill Handle to Copy a Formula
Use the Table Tool to Copy a Formula
Chapter 9 Controlling Formulas
Formula Operators
Order of Operations
Stacking Multiple Parentheses
Understanding Error Messages in Formulas
Using Formulas to Join Text
Joining Text and a Number
Copying Versus Cutting a Formula
Automatically Formatting Formula Cells
Using Date Math
Troubleshooting Formulas
Highlighting All Formula Cells
Seeing All Formulas
Editing a Single Formula to Show Direct Precedents
Using Formula Auditing Arrows
Tracing Dependents
Using the Watch Window
Evaluate a Formula in Slow Motion
Evaluating Part of a Formula
Chapter 10 Understanding Functions
Working with Functions
The Formulas Tab in Excel 2013
Finding the Function You Need
Using AutoComplete to Find Functions
Using the Insert Function Dialog to Find Functions
Getting Help with Excel Functions
Using On-Grid Tooltips
Using the Function Arguments Dialog
Using Excel Help
Using AutoSum
Potential Problems with AutoSum
Special Tricks with AutoSum
Using AutoAverage or AutoCount
Function Reference Chapters
Chapter 11 Using Everyday Functions: Math, Date and Time, and Text Functions
Examples of Math Functions
Using SUM to Add Numbers
Using AGGREGATE to Ignore Error Cells or Filtered Rows
Using COUNT or COUNTA to Count Numbers or Nonblank Cells
Using ROUND, ROUNDDOWN, ROUNDUP, INT, TRUNC, FLOOR, FLOOR.MATH, CEILING, CEILING.MATH, EVEN, ODD, or MROUND to Remove Decimals or Round Numbers
Using SUBTOTAL Instead of SUM with Multiple Levels of Totals
Using SUBTOTAL Instead of SUM to Ignore Rows Hidden by a Filter
Using RAND and RANDBETWEEN to Generate Random Numbers and Data
Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits
Using ABS() to Figure Out the Magnitude of ERROR
Using PI to Calculate Cake or Pizza Pricing
Using =COMBIN to Figure Out Lottery Probability
Using COMBINA to Calculate Triple-Dip Ice Cream Bowls
Using FACT to Calculate the Permutation of a Number
Using GCD and LCM to Perform SeventhGrade Math
Using MULTINOMIAL to Solve a Coin Problem
Using MOD to Find the Remainder Portion of a Division Problem
Using QUOTIENT to Isolate the Integer Portion in a Division Problem
Using PRODUCT to Multiply Numbers
Using SQRT and POWER to Calculate Square Roots and Exponents
Using SIGN to Determine the Sign of a Number
Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data
Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
Dates and Times in Excel
Understanding Excel Date and Time Formats
Examples of Date and Time Functions
Using NOW and TODAY to Calculate the Current Date and Time or Current Date
Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart
Using DATE to Calculate a Date from Year, Month, and Day
Using TIME to Calculate a Time
Using DATEVALUE to Convert Text Dates to Real Dates
Using TIMEVALUE to Convert Text Times to Real Times
Using WEEKDAY to Group Dates by Day of the Week
Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks
Alternative Calendar Systems and DAYS360
Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time
Using EDATE to Calculate Loan or Investment Maturity Dates
Using EOMONTH to Calculate the End of the Month
Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays
Using International Versions of WORKDAY or NETWORKDAYS
Examples of Text Functions
Joining Text with the Ampersand (&) Operator
Using LOWER, UPPER, or PROPER to Convert Text Case
Using TRIM to Remove Trailing Spaces
Using CLEAN to Remove Nonprintable Characters from Text
Using the CHAR or UNICHAR Function to Generate Any Character
Using the CODE or UNICODE Function to Learn the Character Number for Any Character
Using LEFT, MID, or RIGHT to Split Text
Using LEN to Find the Number of Characters in a Text Cell
Using SEARCH or FIND to Locate Characters in a Particular Cell
Using SUBSTITUTE and REPLACE to Replace Characters
Using REPT to Repeat Text Multiple Times
Using EXACT to Test Case
Using TEXT, DOLLAR, and FIXED to Format a Number as Text
Converting Number Punctuation Using the NUMBERVALUE Functions
Using the T and VALUE Functions
Using Functions for Non-English Character Sets
Chapter 12 Using Powerful Functions: Logical, Lookup, Web, and Database Functions
Examples of Logical Functions
Using the IF Function to Make a Decision
Using the AND Function to Check for Two or More Conditions
Using the AND Function to Compare Two Lists
Using OR or XOR to Check Whether One or More Conditions Are Met
Using the TRUE and FALSE Functions
Using the NOT Function to Simplify the Use of AND and OR
Using the IFERROR or IFNA Function to Simplify Error Checking
Examples of Information Functions
Using the IS Functions to Test for Errors
Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells
Using IS Functions to Test for Types of Values
Using the SHEETS and SHEET Functions to Dynamically Build a 3-D Reference
Using the ISREF Function to Check a Reference
Using the N Function to Add a Comment to a Formula
Using the NA Function to Force Charts to Not Plot Missing Data
Using the INFO Function to Print Information About a Computer
Using the CELL Function
Using TYPE to Determine the Type of Cell Value
Examples of Lookup and Reference Functions
Using the CHOOSE Function for Simple Lookups
Using VLOOKUP with TRUE to Find a Value Based on a Range
Using COLUMN to Assist with VLOOKUP When Filling a Wide Table
Using HLOOKUP for Horizontal Lookup Tables
Using the MATCH Function to Locate the Position of a Matching Value
Using INDEX and MATCH for a Left Lookup
Using MATCH and INDEX to Fill a Wide Table
Performing Many Lookups with LOOKUP
Using FORMULATEXT to Document a Worksheet
Using Functions to Describe the Shape of a Contiguous Reference
Using AREAS and INDEX to Describe a Range with More Than One Area
Using Numbers with OFFSET to Describe a Range
Using ADDRESS to Find the Address for Any Cell
Using INDIRECT to Build and Evaluate Cell References on the Fly
Using the HYPERLINK Function to Quickly Add Hyperlinks
Using the TRANSPOSE Function to Formulaically Turn Data
Using the RTD Function and COM Addins to Retrieve Real-time Data
Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table
Cube Functions Introduced in Excel 2007
Examples of Web Functions
Using ENCODEURL to Replace Reserved Characters
Returning XML or JSON Using the WEBSERVICE Function
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function
Examples of Database Functions
Using DSUM to Conditionally Sum Records from a Database
Using the DGET Function
Chapter 13 Using Financial Functions
Examples of Common Household Loan and Investment Functions
Using RRI to Calculate the Investment Return After Many Years
Using PDURATION to Calculate How Long It Will Take Before You Are a Millionaire
Using PMT to Calculate the Monthly Payment on an Automobile Loan
Using RATE to Determine an Interest Rate
Using PV to Figure Out How Much House You Can Afford
Using NPER to Estimate How Long a Nest Egg Will Last
Using FV to Estimate the Future Value of a Regular Savings Plan
Examples of Functions for Financial Professionals
Using PPMT to Calculate the Principal Payment for Any Month
Using IPMT to Calculate the Interest Portion of a Loan Payment for Any Month
Using CUMIPMT to Calculate Total Interest Payments During a Time Frame
Using CUMPRINC to Calculate Total Principal Paid in Any Range of Periods
Using EFFECT to Calculate the Effect of Compounding Period on Interest Rates
Using NOMINAL to Convert the Effective Interest Rate to a Nominal Rate
Examples of Depreciation Functions
Using SLN to Calculate Straight-Line Depreciation
Using DB to Calculate Declining-Balance Depreciation
Using DDB to Calculate Double-Declining- Balance Depreciation
Using SYD to Calculate Sum-of-Years’- Digits Depreciation
Using VDB to Calculate Depreciation for Any Period
Functions for Investment Analysis
Using the NPV Function to Determine Net Present Value
Using IRR to Calculate the Return of a Series of Cash Flows
Using MIRR to Calculate Internal Rate of Return, Including Interest Rates
Using XNPV to Calculate the Net Present Value When the Payments Are Not Periodic
Using XIRR to Calculate a Return Rate When Cash Flow Dates Are Not Periodic
Examples of Functions for Bond Investors
Using YIELD to Calculate a Bond’s Yield
Using PRICE to Back into a Bond Price
Using RECEIVED to Calculate Total Cash Generated from a Bond Investment
Using INTRATE to Back into the Coupon Interest Rate
Using DISC to Back into the Discount Rate
Handling Bonds with an Odd Number of Days in the First or Last Period
Using PRICEMAT and YIELDMAT to Calculate Price and Yield for ZeroCoupon Bonds
Using PRICEDISC and YIELDDISC to Calculate Discount Bonds
Calculating T-Bills
Using ACCRINT or ACCINTM to Calculate Accrued Interest
Using DURATION to Understand Price Volatility
Examples of Miscellaneous Financial Functions
Using DOLLARDE and DOLLARFR to Convert Between Decimals and Fractions
Using FVSCHEDULE to Calculate the Future Value for a Variable Scheduled Interest Rate
Chapter 14 Using Statistical Functions
Functions That Have Been Renamed
Using Worksheets with Legacy Function Names
Examples of Functions for Descriptive Statistics
Using MIN or MAX to Find the Smallest or Largest Numeric Value
Using LARGE to Find the Top N Values in a List of Values
Using SMALL to Sequence a List in Date Sequence
Using MEDIAN, MODE.SNGL, MODE.MULT, and AVERAGE to Find the Central Tendency of a Data Set
Using TRIMMEAN to Exclude Outliers from the Mean
Using GEOMEAN to Calculate Average Growth Rate
Using HARMEAN to Find Average Speeds
Using AVERAGEIF or AVERAGEIFS
Using RANK to Calculate the Position Within a List
Using QUARTILE.INC to Break a Data Set into Quarters
Using PERCENTILE.INC to Calculate Percentile
Using PERCENTRANK.INC to Assign a Percentile to Every Record
Using AVEDEV, DEVSQ, VAR.S, and STDEV.S to Calculate Dispersion
Examples of Functions for Regression and Forecasting
Considerations When Using Regression Analysis
Regression Function Arguments
Functions for Simple Straight-Line Regression: SLOPE and INTERCEPT
Using LINEST to Calculate Straight-Line Regression with Complete Statistics
Using FORECAST to Calculate Prediction for Any One Data Point
Using TREND to Calculate Many Future Data Points at Once
Using LOGEST to Perform Exponential Regression
Using GROWTH to Predict Many Data Points from an Exponential Regression
Exponential Regression Used to Predict Future Generations
Using PEARSON to Determine Whether a Linear Relationship Exists
Using RSQ to Determine the Strength of a Linear Relationship
Using STEYX to Calculate Standard Regression Error
Using COVARIANCE.P to Determine Whether Two Variables Vary Together
Using CORREL to Calculate Positive or Negative Correlation
Using FISHER to Perform Hypothesis Testing on Correlations
Using SKEW, SKEW.P, and KURTOSIS
Examples of Functions for Inferential Statistics
Understanding the Language of Inferential Statistics
Using BINOM.DIST to Determine Probability
Using BINOM.DIST.RANGE to Calculate the Probability of N to N Binomial Events
Using BINOM.INV to Cover Most of the Possible Binomial Events
Using NEGBINOM.DIST to Calculate Probability
Using POISSON.DIST to Predict a Number of Discrete Events Over Time
Using FREQUENCY to Categorize Continuous Data
Using NORM.DIST to Calculate the Probability in a Normal Distribution
Using NORM.INV to Calculate the Value for a Certain Probability
Functions for the Standard Normal Distribution
Using PHI to Plot a Standard Normal Curve
Using NORM.S.INV to Calculate a z Score for a Given Probability
Using STANDARDIZE to Calculate the Distance from the Mean
Using Student’s t-Distribution for Small Sample Sizes
Using CHISQ.TEST to Perform Goodness-of-Fit Testing
The Sum of Squares Functions
Testing Probability on Logarithmic Distributions
Using GAMMA.DIST and GAMMA.INV to Analyze Queuing Times
Calculating Probability of Beta Distributions
Using F.TEST to Measure Differences in Variability
Other Distributions: Exponential, Hypergeometric, and Weibull
Using Z.TEST, CONFIDENCE.NORM, and CONFIDENCE.T to Calculate Confidence Intervals
Using Z.TEST to Accept or Reject an Hypothesis
Using PERMUT to Calculate the Number of Possible Arrangements
Using PERMUTATIONA to Calculate the Number of Possible Arrangements When Repeats Are Allowed
Using the Analysis ToolPak to Perform Statistical Analysis
Installing the Analysis ToolPak in Excel 2013
Generating Random Numbers Based on Various Distributions
Generating a Histogram
Generating Descriptive Statistics of a Population
Ranking Results
Using Regression to Predict Future Results
Using a Moving Average to Forecast Sales
Using Exponential Smoothing to Forecast Sales
Using Correlation or Covariance to Calculate the Relationship Between Many Variables
Using Sampling to Create Random Samples
Using ANOVA to Perform Analysis of Variance Testing
Using the F-Test to Measure Variability Between Methods
Performing a z-Test to Determine Whether Two Samples Have Equal Means
Performing Student’s t-Testing to Test Population Means
Using Functions Versus the Analysis ToolPak Tools
Chapter 15 Using Trig, Matrix, and Engineering Functions
A Brief Review of Trigonometry Basics
Radians Versus Degrees
Pythagoras and Right Triangles
One Side + One Angle = Trigonometry
Using TAN to Find the Height of a Tall Building from the Ground
Using SIN to Find the Height of a Kite in a Tree
Using COS to Figure Out a Ladder’s Length
Using the “Arc” Functions to Find the Measure of an Angle
Using ATAN2 to Calculate Angles in a Circle
Emulating Gravity Using Hyperbolic Trigonometry Functions
Examples of Logarithm Functions
Common Logarithms on a Base-10 Scale
Using LOG to Calculate Logarithms for Any Base
Working with Imaginary Numbers
Using COMPLEX to Convert a and b into a Complex Number
Using IMREAL and IMAGINARY to Break Apart Complex Numbers
Using IMSUM to Add Complex Numbers
Using IMSUB, IMPRODUCT, and IMDIV to Perform Basic Math on Complex Numbers
Using IMABS to Find the Distance from the Origin to a Complex Number
Using IMARGUMENT to Calculate the Angle to a Complex Number
Using IMCONJUGATE to Reverse the Sign of an Imaginary Component
Calculating Powers, Logarithms, and Trigonometry Functions with Complex Numbers
Solving Simultaneous Linear Equations with Matrix Functions
Using MDETERM to Determine Whether a Simultaneous Equation Has a Solution
Using SERIESSUM to Approximate a Function with a Power Series
Using SQRTPI to Find the Square Root of a Number Multiplied by p
Using SUMPRODUCT to Sum Based on Multiple Conditions
Examples of Engineering Functions
Using CONVERT to Convert English to Metric
Performing Bitwise Operations for Electrical Engineering
Converting to Other Number Systems
Converting from Other Number Systems to Decimal
Converting from Binary to Hexadecimal
Converting Using the Legacy Functions
Explaining the Two’s Complement for Negative Numbers
Using DELTA or GESTEP to Filter a Set of Values
Using ERF and ERFC to Calculate the Error Function and Its Complement
Calculating the BESSEL Functions
Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)
Chapter 16 Connecting Worksheets, Workbooks, and External Data
Connecting Two Worksheets
Creating Links Using Paste Options Menu
Creating Links Using the Right-Drag Menu
Building a Link by Using the Mouse
Links to External Workbooks Default to Absolute References
Building a Formula by Typing
Creating Links to Unsaved Workbooks
Using the Links Tab on the Trust Center
Opening Workbooks with Links to Closed Workbooks
Dealing with Missing Linked Workbooks
Preventing the Update Links Dialog from Appearing
Connecting to Data on a Web Page
Setting Up a Connection to a Web Page
Managing Properties for Web Queries
Setting Up a Connection to a Text File
Setting Up a Connection to an Access Database
Setting Up SQL Server, XML, OLE DB, and ODBC Connections
Connecting Using Microsoft Query
Managing Connections
Chapter 17 Using Super Formulas in Excel
Using 3D Formulas to Spear Through Many Worksheets
Referring to the Previous Worksheet
Combining Multiple Formulas into One Formula
Calculating a Cell Reference in the Formula by Using the INDIRECT Function
Using OFFSET to Refer to a Range That Dynamically Resizes
Assigning a Formula to a Name
Turning a Range of Formulas on Its Side
Replacing Multiple Formulas with One Array Formula
Setting Up an Array Formula
Understanding an Array Formula
Coercing a Range of Dates Using an Array Formula
Chapter 18 Using Names in Excel
Advantages of Using Names
Naming a Cell by Using the Name Dialog
Using the Name Box for Quick Navigation
Avoiding Problems by Using Worksheet- Level Scope
Defining a Worksheet-Level Name
Referring to Worksheet-Level Names
Understanding Phantom Names in Excel 2010 and Earlier
Using Named Ranges to Simplify Formulas
Retroactively Applying Names to Formulas
Using Names to Refer to Ranges
Dealing with Invalid Legacy Naming
Adding Many Names at Once from Existing Labels and Headings
Using Intersection to Do a Two-Way Lookup
Using Implicit Intersection
Managing Names
Filtering the Name Manager Dialog
Using a Name to Simplify an Absolute Reference
Using a Name to Hold a Value
Assigning a Formula to a Name
Using Basic Named Formulas
Using Dynamic Named Formulas
Using a Named Formula to Point to the Cell Above
Chapter 19 Fabulous Table Intelligence
Defining Suitable Data for Excel Tables
Defining a Table
Keeping Headers in View
Freezing Worksheet Panes
Clearing Freeze Panes
Using the Old Version of Freeze Panes for Absolute Control
Adding a Total Row to a Table
Toggling Totals
Expanding a Table
Adding Rows to a Table Automatically
Manually Resizing a Table
Adding New Columns to a Table
Adding New Formulas to Tables
Stopping the Automatic Copying of Formulas
Formatting the Results of a New Formula
Selecting Only the Data in the Column
Selecting by Right-Clicking
Selecting by Using Shortcuts
Selecting by Using the Arrow Mouse Pointers
Using Table Data for Charts to Ensure Stickiness
Replacing Named Ranges with Table References
Referencing an Entire Table from Outside the Table
Referencing Table Columns from Outside a Table
Using Structured References to Refer to Tables in Formulas
Creating Banded Rows and Columns with Table Styles
Customizing a Table Style: Creating Double-Height Banded Rows
Creating Banded Rows Outside a Table
Dealing with the Filter Drop-Downs
Part III Business Intelligence
Chapter 20 Sorting Data
Introducing the Sort Dialog
Using Specialized Sorting
Sorting by Color or Icon
Factoring Case into a Sort
Reordering Columns with a Left-to-Right Sort
Sorting into a Unique Sequence by Using Custom Lists
One-Click Sorting
Sorting by Several Columns Using One- Click Sorting
Sorting Randomly
Chapter 21 Removing Duplicates and Filtering
Filtering Records
Using a Filter
Selecting One or Multiple Items from the Filter Drop-Down
Identifying Which Columns Have Filters Applied
Combining Filters
Clearing Filters
Refreshing Filters
Resizing the Filter Drop-Down
Filtering by Selection–Hard Way
Filtering by Selection–Easy Way
Filtering by Color or Icon
Handling Date Filters
Using Special Filters for Dates, Text, and Numbers
Sorting Filtered Results
Totaling Filtered Results
Formatting and Copying Filtered Results
Using the Advanced Filter Command
Using Remove Duplicates to Find Unique Values
Removing Duplicates Based on Several Columns
Handling Duplicates Other Ways
Combining Duplicates and Adding Values
Chapter 22 Using Automatic Subtotals
Adding Automatic Subtotals
Working with the Subtotals
Showing a One-Page Summary with Only the Subtotals
Sorting the Collapsed Subtotal View So the Largest Customers Are on Top
Copying Only the Subtotal Rows
Formatting the Subtotal Rows
Removing Subtotals
Using Specialty Subtotal Techniques
Summing Some Columns While Counting Another Column
Add Subtotals by Two Fields
Chapter 23 Using Pivot Tables to Analyze Data
Creating Your First Pivot Table
Browsing Four “Recommended” Pivot Tables Using the Quick Analysis Icon
Browsing Ten “Recommended” Pivot Tables
Starting with a Blank Pivot Table
Adding Fields to Your Pivot Table Using the Field List
Changing the Pivot Table Report by Using the Field List
Dealing with the Compact Layout
Rearranging a Pivot Table
Finishing Touches: Numeric Formatting and Removing Blanks
Four Things You Have to Know When Using Pivot Tables
Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!
One Blank Cell in a Value Column Causes Excel to Count Instead of Sum
If You Click Outside the Pivot Table, All the Pivot Table Tools Disappear
You Cannot Change, Move a Part of, or Insert Cells in a Pivot Table
Calculating and Roll-ups with Pivot Tables
Grouping Daily Dates to Months and Years
Adding Calculations Outside the Pivot Table
Changing the Calculation of a Field
Showing Percentage of Total Using Show Value As Settings
Showing Running Totals and Rank
Using a Formula to Add a Field to a Pivot Table
Formatting a Pivot Table
Finding More Information on Pivot Tables
Chapter 24 Using Slicers and Filtering a Pivot Table
Filtering Using the Row Label Filter
Filtering Using the Search Box
Clearing a Filter
Filtering Using the Check Boxes
Filtering Using the Label Filter Fly-Out
Filtering Using the Date Filters
Filtering Using Value Filters
Filtering to the Top 10
Filtering Using Filter Fields
Arranging the Filters
Selecting Multiple Items
Filtering Using Slicers
Adding Slicers
Arranging the Slicers
Formatting the Slicers
Using the Slicers
Filtering Using Timelines
Adding a Timeline
Choosing Between Timelines or Grouped Slicers
Filtering Oddities
AutoFiltering a Pivot Table
Applying Row Label Filters to Fields Not in the Pivot Table Report
Replicating a Pivot Table for Every Customer
Sorting a Pivot Table
Chapter 25 Mashing Up Data with PowerPivot
Joining Multiple Tables Using the Data Model in Regular Excel 2013
Preparing Data for Use in the Data Model
Adding the First Table to the Data Model
Adding the Second Table and Defining a Relationship
Understanding the Limitations of the Data Model
Benefits of Moving to PowerPivot
Benefits of the Server Version of PowerPivot
Enabling PowerPivot
Case Study: Building a PowerPivot Report
Import a Text File
Add Excel Data by Copying and Pasting
Add Excel Data by Linking
Define Relationships
Add Calculated Columns Using DAX
Build a Pivot Table
Some Things Are Different
Two Kinds of DAX Calculations
DAX Calculations for Calculated Columns
Using RELATED() to Base a Column Calculation on Another Table
Using DAX to Create a Calculated Field in the Pivot Table
Count Distinct Using DAX
Defining KPIs with PowerPivot
Using QuickExplore
Other Notes
Combination Layouts
Report Formatting
Refreshing the Pivot Table Refreshes PowerPivot
Getting Your Data into PowerPivot with SQL Server
Other Issues
Chapter 26 Creating Interactive Dashboards with Power View or GeoFlow
Preparing Your Data for Power View
Creating a Power View Worksheet
Every New Dashboard Element Starts as a Table
Convert the Table to a Chart
Creating a New Element by Dragging
Every Chart Point Is a Slicer for Every Other Element
Adding a Real Slicer
The Filter Pane Can Be Confusing
Use Tile Boxes to Filter One or a Group of Charts
Replicating Charts Using Multiples
Showing Data on a Map
Using Table or Card View with Images
Animating a Scatter Chart Over Time
Using Drill-Down
Some Closing Tips on Power View
Creating a Map in GeoFlow
Chapter 27 Using What-If, Scenario Manager, Goal Seek, and Solver
Using What-If
Creating a Two-Variable What-If Table
Using Scenario Manager
Creating a Scenario Summary Report
Adding Multiple Scenarios
Using Goal Seek
Using Solver
Installing Solver
Solving a Model Using Solver
Chapter 28 Automating Repetitive Functions Using VBA Macros
Checking Security Settings Before Using Macros
Enabling VBA Security
Recording a Macro
Case Study: Macro for Formatting for a Mail Merge
How Not to Record a Macro: The Default State of the Macro Recorder
Relative References in Macro Recording
Starting the Macro Recorder
Running a Macro
Everyday-Use Macro Example: Formatting an Invoice Register
Using the Ctrl+Down Arrow Key to Handle a Variable Number of Rows
Making Sure You Find the Last Record
Recording the Macro in a Blank Workbook
Editing a Macro
Understanding VBA Code–An Analogy
Comparing Object.Method to Nouns and Verbs
Comparing Collections to Plural Nouns
Comparing Parameters to Adverbs
Comparing Adjectives to Properties
Using the Analogy While Examining Recorded Code
Using Simple Variables and Object Variables
Using R1C1-Style Formulas
Fixing Calculation Errors in Macros
Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
Finding the Last Row with Data
Looping Through All Rows
Referring to Ranges
Combining a Loop with FinalRow
Making Decisions by Using Flow Control
Putting Together the From-Scratch Example: Testing Each Record in a Loop
A Special Case: Deleting Some Records
Combination Macro Example: Creating a Report for Each Customer
Using the Advanced Filter for Unique Records
Using AutoFilter
Selecting Visible Cells Only
Combination Macro Example: Putting It All Together
Chapter 29 More Tips and Tricks for Excel 2013
Speeding Up Calculation
Watching the Results of a Distant Cell
Opening the Same Files Every Day
Comparing Documents Side by Side with Synchronous Scrolling
Calculating a Formula in Slow Motion
Inserting a Symbol in a Cell
Edit an Equation
Adding a Digital Signature Line to a Workbook
Protecting a Worksheet
Sharing a Workbook
Separating Text Based on a Delimiter
Translating Text
Auditing Worksheets Using Inquire
Analyzing a Workbook
Highlighting Potential Problems Using Interactive Diagnostics
Building Relationship Diagrams
Comparing Two Versions of a Workbook
Part IV Visual Presentation
Chapter 30 Formatting Worksheets
Why Format Worksheets?
Using Traditional Formatting
Changing Numeric Formats by Using the Home Tab
Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog
Changing Numeric Formats Using Custom Formats
Aligning Cells
Changing Font Size
Changing Font Typeface
Applying Bold, Italic, and Underline
Using Borders
Coloring Cells
Adjusting Column Widths and Row Heights
Using Merge and Center
Rotating Text
Formatting with Styles
Understanding Themes
Choosing a New Theme
Creating a New Theme
Other Formatting Techniques
Formatting Individual Characters
Changing the Default Font
Wrapping Text in a Cell
Justifying Text in a Range
Adding Cell Comments
Copying Formats
Pasting Formats
Pasting Conditional Formats
Using the Format Painter
Copying Formats to a New Worksheet
Chapter 31 Using Data Visualizations and Conditional Formatting
Using Data Bars to Create In-Cell Bar Charts
Creating Data Bars
Customizing Data Bars
Showing Data Bars for a Subset of Cells
Using Color Scales to Highlight Extremes
Customizing Color Scales
Using Icon Sets to Segregate Data
Setting Up an Icon Set
Moving Numbers Closer to Icons
Mixing Icons or Hiding Icons
Using the Top/Bottom Rules
Setting Up Conditional Formatting Rules
Using the Highlight Cells Rules
Highlighting Cells by Using Greater Than and Similar Rules
Comparing Dates by Using Conditional Formatting
Identifying Duplicate or Unique Values by Using Conditional Formatting
Using Conditional Formatting for Text Containing a Value
Tweaking Rules with Advanced Formatting
Using a Formula for Rules
Finding Cells Within Three Days of Today
Finding Cells Containing Data from the Past 30 Days
Highlighting Data from Specific Days of the Week
Highlighting an Entire Row
Highlighting Every Other Row Without Using a Table
Combining Rules
Clearing Conditional Formats
Extending the Reach of Conditional Formats
Special Considerations for Pivot Tables
Chapter 32 Graphing Data Using Excel Charts
Choosing from Recommended Charts
Using Paintbrush Icon for Styles
Deleting Extraneous Data Using the Funnel
Changing Chart Options Using the Plus Icon
Showing Two Orders of Magnitude Using a Combo Chart
Using Ctrl+1 to Format Any Chart Element
Labeling Charts
Controlling the Axis Range
Seeing the Relationship Between Two Variables in a Scatter Chart
Adding a Third Variable with a Bubble Chart
Plotting Two Populations on One Scatter Chart
Summarizing Data Using the Quick Analysis Icon
Saving Time with Charting Tricks
Adding New Data to a Chart by Pasting
Adding New Data to a Chart by Using a Table
Adding Drop Lines to a Surface Chart
Predicting the Future by Using a Trendline
Creating Stock Charts
Dealing with Small Pie Slices
Saving a Favorite Chart Style As a Template
Chapter 33 Using Sparklines
Fitting a Chart into the Size of a Cell with Sparklines
Understanding How Excel Maps Data to Sparklines
Creating a Group of Sparklines
Built-in Choices for Customizing Sparklines
Controlling Axis Values for Sparklines
Setting Up Win/Loss Sparklines
Showing Detail by Enlarging the Sparkline and Adding Labels
Other Sparkline Options
Chapter 34 Using SmartArt, Shapes, WordArt, and Text Boxes
Using SmartArt
Elements Common in Most SmartArt
Tour of the SmartArt Categories
Inserting SmartArt
Changing Existing SmartArt to a New Style
Micromanaging SmartArt Elements
Controlling SmartArt Shapes from the Text Pane
Adding Images to SmartArt
Special Considerations for Organizational Charts and Hierarchical SmartArt
Using Limited SmartArt
Deciphering the Labeled Hierarchy Layouts
Using Shapes to Display Cell Contents
Working with Shapes
Using the Freeform Shape to Create a Custom Shape
Using WordArt for Interesting Titles and Headlines
Using Text Boxes to Flow Long Text Passages
Chapter 35 Using Pictures and Clip Art
Getting Your Picture into Excel
Inserting a Picture from Your Computer
Inserting Multiple Pictures at Once
Inserting a Picture or Clip Art from Online
Adjusting the Picture Using the Ribbon Tab
Resizing the Picture to Fit
Adjusting the Brightness and Contrast
Adding Interesting Effects Using the Picture Styles Gallery
Applying Artistic Effects
Removing the Background
Reducing a Picture’s File Size
Adding Captions to Images
Using the Format Picture Task Pane
Rotating a Shape over a Picture
Inserting Screen Clippings
Selecting and Arranging Pictures
Part V Sharing Information
Chapter 36 Printing
Printing in One Click
Finding Print Settings
Previewing the Printed Report
Using the Print Preview on the Print Panel
Using Full Screen Print Preview
Making the Report Fit On the Page
Setting Worksheet Paper Size
Adjusting Worksheet Orientation
Adjusting Worksheet Margins
Adding Print Titles
Excluding Part of Your Worksheet from the Print Range
Forcing More Data to Fit on a Page
Working with Page Breaks
Manually Adding Page Breaks
Manual Versus Automatic Page Breaks
Using Page Break Preview to Make Changes
Removing Manual Page Breaks
Adding Headers or Footers to the Printed Report
Adding an Automatic Header
Adding a Custom Header
Inserting a Picture or a Watermark in a Header
Using Different Headers and Footers in the Same Document
Scaling Headers and Footers
Printing from the File Menu
Choosing a Printer
Choosing What to Print
Changing Printer Properties
Changing Some of the Page Setup Settings
Using Page Layout View
Exploring Other Page Setup Options
Printing Gridlines and Headings
Centering a Small Report on a Page
Replacing Error Values When Printing
Printing Comments
Controlling the First Page Number
Chapter 37 Excel Web App and Other Ways to Share Workbooks
Viewing Your SkyDrive Workbooks from Anywhere
Editing Excel on the iPad
Editing Excel on the Surface RT Tablet
Group-Editing Using the Excel Web App
Understanding the Limitations of the Excel Web App
Using the Excel Web App Instead of Excel Starter
Designing a Workbook as an Interactive Web Page
Sharing a Link to Your Web Workbook
Embedding Your Workbook in a Blog Post or Your Web Page
Collecting Survey Data in the Excel Web App
Make Any Web Table Interactive with Excel Everywhere
Creating a PDF from a Worksheet
Interacting with Other Office Applications
Sending a Workbook via Outlook
Pasting Excel Data to Microsoft OneNote
Using Excel Charts in PowerPoint
Creating Tables in Excel and Pasting to Word
Pasting Word Data to an Excel Text Box
Creating Labels in Word from Excel Data
Chapter 38 Saving Time Using the Easy-XL Program
Downloading and Installing Easy-XL
Easy-XL Works Best with Tabular Data
Doing Away with VLOOKUP
Using a Fuzzy Match
Text to Columns on Steroids
Sorting Columns Left to Right
Summarizing Data
Adding Statistics to the Report
Getting Quick Statistics
Cleansing Data Without Using TRIM(), PROPER(), or CLEAN()
Adding Text to Cells
Filling in the Annoying Outline View
There’s More
Deal with Fiscal Years
Record Easy-XL Commands into VBA Macros
9780789748577 TOC 12/18/2013
| Reihe/Serie | In Depth |
|---|---|
| Sprache | englisch |
| Maße | 179 x 229 mm |
| Gewicht | 1842 g |
| Themenwelt | Informatik ► Office Programme ► Excel |
| ISBN-10 | 0-7897-4857-6 / 0789748576 |
| ISBN-13 | 978-0-7897-4857-7 / 9780789748577 |
| Zustand | Neuware |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
