100 Top Tips: Microsoft Excel

By Sean McManus

This pocket-sized and budget-priced book, 100 Top Tips: Microsoft Excel, helps you in three ways:

• Learn more from your data: Discover features such as pivot tables, rankings, conditional formatting and What-If? analysis, none of which require programming or VBA skills. You'll see how to create your own formulas, including conditional sums, subtotals and averages.
• Boost your productivity: Learn about time-saving shortcuts for common activities, including data entry, analysis, and managing large spreadsheets. The book also provides a handy reference for common formulas and processes, and an easy-to-read list of keyboard shortcuts that can save you time for your most common processes.
• Improve your spreadsheet accuracy: See how to enforce consistency using data validation and Excel tables, and how to protect your spreadsheet from accidental changes.

Tips are usually covered in a single page, although a few of the tips stretch across two pages. I've squeezed some bonus tips in along the way too, so you're getting at least 100 tips to supercharge your Microsoft Excel skills! Take a look at the table of contents below to get an idea of what you'll learn.

You don't need to do any VBA (Visual Basic for Applications) programming or use extensions: these tips all use the normal Microsoft Excel interface. I've assumed you're using Microsoft Excel 2010 or later (for example, Microsoft Office 365, Microsoft Excel 2013, Microsoft Excel 2016, or Microsoft Excel 2019), but some tips will also work in earlier versions. A few tips relate to the latest versions of Excel. I have mentioned any compatibility issues I am aware of.

For anyone who uses Excel regularly, this book is indispensable. Given the budget price of this book and the wealth of productivity boosting tips inside, I believe it will quickly pay for itself in saved time.

Buy the book here

---

Preview the book!

See the full Table of Contents below (with links to some sample tutorials) or download the free sample PDF to see the first 10 tips. The free PDF sampler of 100 Top Tips: Microsoft Excel shows you how to use Auto Fill and Flash Fill to enter data more quickly; how to manage large spreadsheets; and how to validate the data in your cells. You'll also see how to add a drop-down menu to improve data consistency and speed up data entry.

You can also read these articles in your browser by clicking them in the table of contents below.

---

My favourite Microsoft Excel time savers

Here are some quick but powerful ideas you can start using today:

---

Table of Contents

The book is organised as set of tips, which you can read in any order. I've included cross-references where appropriate to help you to find your way around. I have organised the book, though, so ideas follow logically. You might want to read it once through, and then keep it handy as a reference.

Here's a list of the tips in the book. These section headings don't appear in the book, but I've added them here so you can more quickly get an overview of what's included.

Data entry in 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 in Excel

• Entering formulas
• Understanding formula symbols

Analysing data in Excel

• 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

Creating formulas in Excel

• 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

Using named ranges in Excel

• Naming ranges
• Using the Name Manager

Using lookups in Excel

• Using VLOOKUP
• Using HLOOKUP
• Finding data with MATCH
• Using the INDEX function
• Using the CHOOSE function

Rounding data in Excel

• Rounding data values
• Using advanced rounding functions

Date calculations and formats in Excel

• Calculating with dates
• Making dates and breaking them down
• Creating custom date formats

Manipulating text (strings) in Excel

• 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 and analysing formulas in Excel

• Debugging: Tracing precedents
• Debugging: Tracing dependents
• Debugging: Evaluating formulas
• Using the Watch Window

Visualising data in Excel

• Adding simple conditional formatting
• Adding advanced conditional formatting
• Creating new rules for conditional formatting
• Managing conditional formatting
• Visualizing your data
• Using Quick Analysis

Performing What-If? analysis in Excel

• Using What-If? scenarios
• Using data tables
• Modeling two formulas in a data table
• Using two-dimensional data tables
• Using Goal Seek

Using pivot tables in Excel

• 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

Using tables in Excel

• Inserting a table
• Adding totals to a table
• Formatting your table

Printing spreadsheets successfully in Excel

• Setting (and clearing) a Print Area
• Printing to fit the page or across multiple pages
• Adding a page header
• Adding a watermark

Using the security features in Excel

• Choosing cells to leave unlocked
• Hiding formulas in cells
• Password protecting ranges
• Protecting the worksheet
• Protecting the workbook
• Protecting Excel files

Supercharging your speed with shortcuts in Excel

• Data entry shortcuts
• Formatting shortcuts
• Navigation shortcuts
• Selection shortcuts

---

Bonus content

• Top 10 Surprising and Fun Facts about Microsoft Excel - Discover how rock bands, artists, and poets have used Excel, and see videos of the long lost Easter eggs Microsoft hid in older versions of the software.

Buy the book here