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


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:

Excel Tip from 100 Top Tips: Microsoft Excel


The book in brief

Book Title 100 Top Tips: Microsoft Excel
Book Author Sean McManus
Publisher In Easy Steps
ISBN 978-1840788792
Pages 108
Size 10cm x 18cm (take it anywhere!)

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 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

Buy the book here

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

Coding Compendium

Coding Compendium

A free 100-page ebook collecting my projects and tutorials for Raspberry Pi, micro:bit, Scratch and Python. Simply join my newsletter to download it.

Web Design in Easy Steps

Web Design IES

Web Design in Easy Steps, now in its 7th Edition, shows you how to make effective websites that work on any device.

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!

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