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 FillFilling to match the neighboring columnUsing Flash FillAdjusting column widths and row heightsInserting multiple rows or columnsManaging large spreadsheetsForcing valid data entryAdding a drop-down menuCreating custom validationRemoving duplicatesHighlighting duplicatesUsing advanced paste optionsExpanding the clipboardMoving dataEntering formulasUnderstanding formula symbolsFinding the highest/lowest value in a rangeRanking data itemsFinding an item by its ranking using LARGEFinding averagesSorting dataUsing filtersUsing IF for cell contentsCombining IF functionsTotaling up values with SUMChoosing values to sum with SUMIFUsing several sum criteria with SUMIFSUsing cumulative sumsUsing SUMPRODUCTCounting cellsUsing more count criteriaUsing wildcardsGrouping dataCalculating subtotalsUsing subtotalsCopying visible data onlyNaming rangesUsing the Name ManagerUsing VLOOKUPUsing HLOOKUPFinding data with MATCHUsing the INDEX functionUsing the CHOOSE functionRounding data valuesUsing advanced rounding functionsCalculating with datesMaking dates and breaking them downCreating custom date formatsUsing multiple lines of text in cellsCleaning your textJoining text from different cellsGetting the length of a piece of textSearching in textExtracting pieces of textSplitting first and last namesSplitting text across columnsReplacing text in a cellCounting the number of words in a cellCounting occurrences of a word or phraseDebugging: Tracing precedentsDebugging: Tracing dependentsDebugging: Evaluating formulasUsing the Watch WindowAdding simple conditional formattingAdding advanced conditional formattingCreating new rules for conditional formattingManaging conditional formattingVisualizing your dataUsing Quick AnalysisUsing What-If? scenariosUsing data tablesModeling two formulas in a data tableUsing two-dimensional data tablesUsing Goal SeekPreparing data for a pivot tableCreating a pivot tableDeeper analysis with pivot tablesUsing averages and counts in pivot tablesCalculating percentages in pivot tablesUsing slicersCreating a pivot chartTracing totals back to dataRefreshing a pivot tableInserting a tableAdding totals to a tableFormatting your tableSetting (and clearing) a Print AreaPrinting to fit the page or across multiple pagesAdding a page headerAdding a watermarkChoosing cells to leave unlockedHiding formulas in cellsPassword protecting rangesProtecting the worksheetProtecting the workbookProtecting Excel filesData entry shortcutsFormatting shortcutsNavigation shortcutsSelection shortcuts