100 Top Tips - Microsoft Excel

Sean McManus writes inspiring books about business and technology. His books include Scratch Programming in easy steps and Web Design in easy steps and he has written for magazines including The MagPi, Raspberry Pi Geek, and Internet Magazine. Sean is also a Code Club volunteer.

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