100 tips that will help you to boost your productivity and get the most out of Excel. A must-have for anyone using 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.

1. Entering data with Auto Fill


2. Filling to match the neighboring column


3. Using Flash Fill


4. Adjusting column widths and row heights


5. Inserting multiple rows or columns


6. Managing large spreadsheets


7. Forcing valid data entry


8. Adding a drop-down menu


9. Creating custom validation


10. Removing duplicates


11. Highlighting duplicates


12. Using advanced paste options


13. Expanding the clipboard


14. Moving data


15. Entering formulas


16. Understanding formula symbols


17. Finding the highest/lowest value in a range


18. Ranking data items


19. Finding an item by its ranking using LARGE


20. Finding averages


21. Sorting data


22. Using filters


23. Using IF for cell contents


24. Combining IF functions


25. Totaling up values with SUM


26. Choosing values to sum with SUMIF


27. Using several sum criteria with SUMIFS


28. Using cumulative sums


29. Using SUMPRODUCT


30. Counting cells


31. Using more count criteria


32. Using wildcards


33. Grouping data


34. Calculating subtotals


35. Using subtotals


36. Copying visible data only


37. Naming ranges


38. Using the Name Manager


39. Using VLOOKUP


40. Using HLOOKUP


41. Finding data with MATCH


42. Using the INDEX function


43. Using the CHOOSE function


44. Rounding data values


45. Using advanced rounding functions


46. Calculating with dates


47. Making dates and breaking them down


48. Creating custom date formats


49. Using multiple lines of text in cells


50. Cleaning your text


51. Joining text from different cells


52. Getting the length of a piece of text


53. Searching in text


54. Extracting pieces of text


55. Splitting first and last names


56. Splitting text across columns


57. Replacing text in a cell


58. Counting the number of words in a cell


59. Counting occurrences of a word or phrase


60. Debugging: Tracing precedents


61. Debugging: Tracing dependents


62. Debugging: Evaluating formulas


63. Using the Watch Window


64. Adding simple conditional formatting


65. Adding advanced conditional formatting


66. Creating new rules for conditional formatting


67. Managing conditional formatting


68. Visualizing your data


69. Using Quick Analysis


70. Using What-If? scenarios


71. Using data tables


72. Modeling two formulas in a data table


73. Using two-dimensional data tables


74. Using Goal Seek


75. Preparing data for a pivot table


76. Creating a pivot table


77. Deeper analysis with pivot tables


78. Using averages and counts in pivot tables


79. Calculating percentages in pivot tables


80. Using slicers


81. Creating a pivot chart


82. Tracing totals back to data


83. Refreshing a pivot table


84. Inserting a table


85. Adding totals to a table


86. Formatting your table


87. Setting (and clearing) a Print Area


88. Printing to fit the page or across multiple pages


89. Adding a page header


90. Adding a watermark


91. Choosing cells to leave unlocked


92. Hiding formulas in cells


93. Password protecting ranges


94. Protecting the worksheet


95. Protecting the workbook


96. Protecting Excel files


97. Data entry shortcuts


98. Formatting shortcuts


99. Navigation shortcuts


100. Selection shortcuts