How To Use This Book |
Getting Started / 1: |
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 / 2: |
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 / 3: |
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 / 4: |
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 / 5: |
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 / 6: |
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 / 7: |
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 / 8: |
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 / 9: |
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 / 10: |
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 / 11: |
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 / 12: |
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 / 13: |
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 / 14: |
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 / 15: |
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 Picture Effect |
Apply a Picture Style |
Add an Artistic Effect |
Remove a Background |
Illustrating Your Ideas / 16: |
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 / 17: |
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 / Appendix A: |
Index |
How To Use This Book |
Getting Started / 1: |
Introducing Data Analysis with Excel |