100 Top Tips - Microsoft Excel

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