100 Top Tips - Microsoft Excel

by Sean McManus

Overview

100 Top Tips – Microsoft Excel will help you boost your productivity and get more value from your spreadsheets with this powerful book of tips. It showcases:

time-saving features (including data entry shortcuts)

powerful functions for processing text and numbers

insightful data analysis features, including pivot tables and what-if analysis.

You’ll also discover how to use graphs, debug formulas, and print everything out cleanly. Whether you’ve been using Excel for years and never gone beyond the basics, or are just entering the world of spreadsheets, this book will quickly supercharge your skills.

As you would expect from the In Easy Steps brand the book is written in an approachable style with step by step instructions provided for many of the tips, and full colour screenshots guiding you through the processes.

Product Details

ISBN-13:	9781840788792
Publisher:	In Easy Steps Limited
Publication date:	02/25/2020
Series:	100 Top Tips - In Easy Steps
Edition description:	4th ed.
Pages:	108
Product dimensions:	3.06(w) x 7.10(h) x (d)

About the Author

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.

Table of Contents

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