100 Top Tips: Microsoft Excel in Easy Steps

In this Book

• Entering Data with Auto Fill
• Filling to Match the Neighboring Column
• Using Flash Fill
• Adjusting Column Widths and Row Heights
• Inserting Multiple Rows or Columns
• Managing Large Spreadsheets
• Forcing Valid Data Entry
• Adding a Drop-Down Menu
• Creating Custom Validation
• Removing Duplicates
• Highlighting Duplicates
• Using Advanced Paste Options
• Expanding the Clipboard
• Moving Data
• Entering Formulas
• Understanding Formula Symbols
• Finding the Highest/Lowest Value in a Range
• Ranking Data Items
• Finding an Item by its Ranking Using LARGE
• Finding Averages
• Sorting Data
• Using Filters
• Using IF for Cell Contents
• Combining IF Functions
• Totaling Up Values with SUM
• Choosing Values to Sum with SUMIF
• Using Several Sum Criteria with SUMIFS
• Using Cumulative Sums
• Using SUMPRODUCT
• Counting Cells
• Using More Count Criteria
• Using Wildcards
• Grouping Data
• Calculating Subtotals
• Using Subtotals
• Copying Visible Data Only
• Naming Ranges
• Using the Name Manager
• Using VLOOKUP
• Using HLOOKUP
• Finding Data with MATCH
• Using the INDEX Function
• Using the CHOOSE Function
• Rounding Data Values
• Using Advanced Rounding Functions
• Calculating with Dates
• Making Dates and Breaking them Down
• Creating Custom Date Formats
• Using Multiple Lines of Text in Cells
• Cleaning Your Text
• Joining Text from Different Cells
• Getting the Length of a Piece of Text
• Searching in Text
• Extracting Pieces of Text
• Splitting First and Last Names
• Splitting Text Across Columns
• Replacing Text in a Cell
• Counting the Number of Words in a Cell
• Counting Occurrences of a Word or Phrase
• Debugging: Tracing Precedents
• Debugging: Tracing Dependents
• Debugging: Evaluating Formulas
• Using the Watch Window
• Adding Simple Conditional Formatting
• Adding Advanced Conditional Formatting
• Creating New Rules for Conditional Formatting
• Managing Conditional Formatting
• Visualizing Your Data
• Using Quick Analysis
• Using What-If? Scenarios
• Using Data Tables
• Modeling Two Formulas in a Data Table
• Using Two-Dimensional Data Tables
• Using Goal Seek
• Preparing Data for a Pivot Table
• Creating a Pivot Table
• Deeper Analysis with Pivot Tables
• Using Averages and Counts in Pivot Tables
• Calculating Percentages in Pivot Tables
• Using Slicers
• Creating a Pivot Chart
• Tracing Totals Back to Data
• Refreshing a Pivot Table
• Inserting a Table
• Adding Totals to a Table
• Formatting Your Table
• Setting (and Clearing) a Print Area
• Printing to Fit the Page or Across Multiple Pages
• Adding a Page Header
• Adding a Watermark
• Choosing Cells to Leave Unlocked
• Hiding Formulas in Cells
• Password Protecting Ranges
• Protecting the Worksheet
• Protecting the Workbook
• Protecting Excel Files
• Data Entry Shortcuts
• Formatting Shortcuts
• Navigation Shortcuts
• Selection Shortcuts