| |
| |
| How To Use This Book | |
| |
| |
| |
| Getting Started | |
| |
| |
| Introducing Data Analysis with Excel | |
| |
| |
| Tour the Excel Window | |
| |
| |
| Explore the Ribbon | |
| |
| |
| Using the Mini Toolbar and Context Menu | |
| |
| |
| Select Options on the Status Bar | |
| |
| |
| Take a Look at Backstage View | |
| |
| |
| Upload a File to Excel's Web-based Application | |
| |
| |
| Create a File Using Excel's Web-based Application | |
| |
| |
| Enter Data | |
| |
| |
| Select Cells | |
| |
| |
| |
| Formatting A Worksheet | |
| |
| |
| Using the Ribbon to Format Numbers | |
| |
| |
| Using the Format Cells Dialog Box | |
| |
| |
| Understanding Dates and Times | |
| |
| |
| Format Percentages | |
| |
| |
| Format Fractions | |
| |
| |
| Format in Scientific Notation | |
| |
| |
| Format as Text | |
| |
| |
| Add a Border | |
| |
| |
| Change the Font or Font Size | |
| |
| |
| Add a Background Color | |
| |
| |
| Change the Font Color | |
| |
| |
| Bold, Underline, or Italicize | |
| |
| |
| Align Data | |
| |
| |
| Rotate Data | |
| |
| |
| Wrap Text | |
| |
| |
| Merge and Center | |
| |
| |
| Apply a Style | |
| |
| |
| Using Format Painter | |
| |
| |
| Clear Formats | |
| |
| |
| |
| Structuring Your Workbook | |
| |
| |
| Cut, Copy, and Paste Cells | |
| |
| |
| Using Live Preview with Paste | |
| |
| |
| Paste from the Office Clipboard | |
| |
| |
| Insert or Delete | |
| |
| |
| Find and Replace Information | |
| |
| |
| Change the Name of a Worksheet | |
| |
| |
| Change Column Widths or Row Heights | |
| |
| |
| Hide Columns or Rows | |
| |
| |
| Hide a Worksheet | |
| |
| |
| Move or Copy a Worksheet | |
| |
| |
| Freeze Worksheet Titles | |
| |
| |
| Hide Gridlines, Headings, or the Formula Bar | |
| |
| |
| |
| Creating Formulas | |
| |
| |
| Understanding Formulas | |
| |
| |
| Calculate with an Operator | |
| |
| |
| Calculate Using a Function and Cell Addresses | |
| |
| |
| Create an Array Formula | |
| |
| |
| Using the Sum, Average, Count, Min, and Max Functions | |
| |
| |
| Create a Formula that Refers to Another Worksheet | |
| |
| |
| Understanding Relative and Absolute Cell Addresses | |
| |
| |
| Edit Formulas | |
| |
| |
| Name Cells and Ranges | |
| |
| |
| Define and Display Constants | |
| |
| |
| Create Formulas That Include Names | |
| |
| |
| Check Formulas for Errors | |
| |
| |
| Trace Precedents and Dependents | |
| |
| |
| |
| Using Functions | |
| |
| |
| Understanding the Function Wizard | |
| |
| |
| Round a Number | |
| |
| |
| Create a Conditional Formula | |
| |
| |
| Calculate a Conditional Sum | |
| |
| |
| Calculate a Conditional Count | |
| |
| |
| Find the Square Root | |
| |
| |
| Retrieve Column or Row Numbers | |
| |
| |
| Using VLOOKUP | |
| |
| |
| Determine the Location of a Value | |
| |
| |
| Using INDEX | |
| |
| |
| Perform Date and Time Calculations | |
| |
| |
| |
| Using Financial Functions | |
| |
| |
| Calculate Future Value | |
| |
| |
| Calculate Present Value | |
| |
| |
| Calculate Loan Payments | |
| |
| |
| Calculate Principal or Interest | |
| |
| |
| Calculate the Interest Rate | |
| |
| |
| Calculate the Internal Rate of Return | |
| |
| |
| Calculate Straight-Line Depreciation | |
| |
| |
| Calculate Declining Balance Depreciation | |
| |
| |
| Calculate Double-Declining Balance Depreciation | |
| |
| |
| Calculate Sum-of-the-Years-Digits Depreciation | |
| |
| |
| |
| Using Statistical Functions And Tools | |
| |
| |
| Calculate an Average | |
| |
| |
| Calculate a Conditional Average | |
| |
| |
| Calculate the Median or the Mode | |
| |
| |
| Calculate Rank | |
| |
| |
| Determine the Nth Largest Value | |
| |
| |
| Calculate Frequency | |
| |
| |
| Calculate Variance and Standard Deviation | |
| |
| |
| Find the Correlation | |
| |
| |
| Install Excel Add-Ins | |
| |
| |
| Calculate a Moving Average | |
| |
| |
| Compare Variances | |
| |
| |
| Using the Data Analysis Toolpak to Determine Rank and Percentile | |
| |
| |
| Calculate Descriptive Statistics | |
| |
| |
| |
| Organizing Worksheet Data | |
| |
| |
| Enter Data with a Form | |
| |
| |
| Perform Simple Sorts and Filters | |
| |
| |
| Perform Multilevel Sorts | |
| |
| |
| Perform a Custom Sort | |
| |
| |
| Sort by Cell Color, Font Color, or Cell Icon | |
| |
| |
| Perform Complex Filters | |
| |
| |
| Enter Criteria to Find Records | |
| |
| |
| Using Advanced Filtering Techniques | |
| |
| |
| Filter Duplicate Records | |
| |
| |
| Count Filtered Records | |
| |
| |
| Subtotal Records | |
| |
| |
| Using Auto Outline | |
| |
| |
| Define Data as a Table | |
| |
| |
| Modify a Table Style | |
| |
| |
| Using Database Functions with a Table | |
| |
| |
| |
| Working With Pivottables | |
| |
| |
| Create a PivotTable | |
| |
| |
| Modify a PivotTable Layout | |
| |
| |
| Summarize PivotTable Values | |
| |
| |
| Create a PivotTable Calculated Field | |
| |
| |
| Group the Rows or Columns in a PivotTable | |
| |
| |
| Apply a Style to a PivotTable | |
| |
| |
| Filter a PivotTable | |
| |
| |
| Sort a PivotTable | |
| |
| |
| Retrieve Values from a PivotTable | |
| |
| |
| Using Slicer | |
| |
| |
| |
| Charting Data | |
| |
| |
| Create a Chart | |
| |
| |
| Add Chart Details | |
| |
| |
| Create a Combination Chart | |
| |
| |
| Change the Chart Type | |
| |
| |
| Add or Remove Chart Data | |
| |
| |
| Add Sparklines | |
| |
| |
| Create a Trendline | |
| |
| |
| Add Error Bars | |
| |
| |
| Create a Histogram | |
| |
| |
| Chart Filtered Data | |
| |
| |
| Create a PivotChart | |
| |
| |
| Filter a PivotChart | |
| |
| |
| |
| Working With External Data | |
| |
| |
| Paste Link into Word | |
| |
| |
| Embed a Worksheet | |
| |
| |
| Hyperlink a Worksheet | |
| |
| |
| Query a Web Site | |
| |
| |
| Import a Text File | |
| |
| |
| Import an Access Database | |
| |
| |
| Query an Access Database | |
| |
| |
| |
| Using Data Analysis Tools And Techniques | |
| |
| |
| Perform What-If Analysis | |
| |
| |
| Optimize a Result with Goal Seek | |
| |
| |
| Using Solver | |
| |
| |
| Solve a Formula with a Data Table | |
| |
| |
| Extend a Series with Auto Fill | |
| |
| |
| Join Text | |
| |
| |
| Add a Calculator | |
| |
| |
| Consolidate Worksheets | |
| |
| |
| Highlight Cells that Meet Your Criteria | |
| |
| |
| Find the Highest or Lowest Ranked Values | |
| |
| |
| Add Data Bars to Your Worksheet | |
| |
| |
| Add Icon Sets to Your Worksheet | |
| |
| |
| Remove Conditional Formatting | |
| |
| |
| Change Conditional Formatting Rules | |
| |
| |
| Paste with Paste Special | |
| |
| |
| |
| Sharing Your Workbook With Others | |
| |
| |
| Validate with a Validation List | |
| |
| |
| Validate with Data Entry Rules | |
| |
| |
| Add Comments to Your Worksheet | |
| |
| |
| Track Changes | |
| |
| |
| Protect Your Worksheet | |
| |
| |
| Save Your Workbook as a Template | |
| |
| |
| Choose a Format When Saving a Workbook | |
| |
| |
| Print Your Workbook | |
| |
| |
| Print Multiple Areas of Your Worksheet | |
| |
| |
| |
| Automating With Macros | |
| |
| |
| Introducing Macros | |
| |
| |
| Set Macro Security | |
| |
| |
| Create a Digital Signature | |
| |
| |
| Record a Macro | |
| |
| |
| Assign a Digital Signature to a Macro | |
| |
| |
| Run a Macro | |
| |
| |
| Create and Launch a Keyboard Shortcut | |
| |
| |
| Assign a Macro to the Quick Access Toolbar | |
| |
| |
| Delete a Macro | |
| |
| |
| |
| Illustrating Your Worksheets | |
| |
| |
| Place a Screenshot in Your Worksheet | |
| |
| |
| Insert Clip Art into Your Worksheet | |
| |
| |
| Crop a Clip Art Illustration or a Photograph | |
| |
| |
| Insert a Picture into Your Worksheet | |
| |
| |
| Recolor a Picture | |
| |
| |
| Adjust the Sharpness, Brightness, and Contrast | |
| |
| |
| Compress a Picture | |
| |
| |
| Add a Border | |
| |
| |
| Add a Picture Effect | |
| |
| |
| Apply a Picture Style | |
| |
| |
| Add an Artistic Effect | |
| |
| |
| Remove a Background | |
| |
| |
| |
| Illustrating Your Ideas | |
| |
| |
| Insert a Text Box | |
| |
| |
| Format a Text Box | |
| |
| |
| Insert a Shape | |
| |
| |
| Add Text to a Shape | |
| |
| |
| Change the Size of an Object | |
| |
| |
| Rotate an Object | |
| |
| |
| Change the Stacking Order | |
| |
| |
| Group Objects | |
| |
| |
| Align Objects | |
| |
| |
| Insert a Symbol | |
| |
| |
| Using SmartArt Graphics | |
| |
| |
| Modify a SmartArt Graphic | |
| |
| |
| Apply a SmartArt Style | |
| |
| |
| Apply a SmartArt Layout | |
| |
| |
| |
| Customizing Excel | |
| |
| |
| Add a Form Control to a Worksheet | |
| |
| |
| Assign Values to a Form Control | |
| |
| |
| Add a Macro to a Form Control | |
| |
| |
| Customize the Quick Access Toolbar | |
| |
| |
| Customize the Ribbon | |
| |
| |
| |
| Using Excel Keyboard Shortcuts | |
| |
| |
| Using Excel Keyboard Shortcuts | |
| |
| |
| Index | |