Home > Books > 100 Top Tips: Microsoft Excel

100 Top Tips: Microsoft Excel

By Sean McManus

Book cover: 100 Top Tips: Microsoft ExcelThis 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


The book in brief

Book Title 100 Top Tips: Microsoft Excel
Book Author Sean McManus
Publisher In Easy Steps
ISBN 978-1840788792
Pages 108

Free PDF sampler of 100 Top Tips: Microsoft Excel

Free PDF Sampler

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

Credits

© Sean McManus. All rights reserved.

Visit www.sean.co.uk for free chapters from Sean's coding books (including Mission Python, Scratch Programming in Easy Steps and Coder Academy) and more!

Discover my latest books and music

100 Top Tips: Microsoft Excel

100 Top Tips: Microsoft Excel

Power up your Microsoft Excel skills with this powerful pocket-sized book of tips that will save you time and help you learn more from your spreadsheets.

Scratch Programming in Easy Steps

Scratch Programming IES

This book, now fully updated for Scratch 3, will take you from the basics of the Scratch language into the depths of its more advanced features. A great way to start programming.

Mission Python book

Mission Python

Code a space adventure game in this Python programming book published by No Starch Press.

Cool Scratch Projects in Easy Steps book

Cool Scratch Projects in Easy Steps

Discover how to make 3D games, create mazes, build a drum machine, make a game with cartoon animals and more!

Coder Academy book

Coder Academy

Learn to make games and other programs in Scratch, and make a web page in HTML. Highly interactive book for 7-10 year olds.

Artificial: album cover shows a steampunk heart

Artificial

What happens beyond AI when the machines become emotional? Discover my new album of electronic music here.

Walking astronaut from Mission Python book Top | Search | Help | Privacy | Access Keys | Contact me
Home | Newsletter | Blog | Copywriting Services | Books | Free book chapters | Articles | Music | Photos | Games | Shop | About