100 Top Tips – Microsoft Excel

Sean McManus

• Publisher: In Easy Steps Limited
• Year: 2020
• Print ISBN: 9781840788792
• eBook ISBN: 9781840789119
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

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.
• How to use graphs
• Debugging formulas
• Printing clearly

And much more!

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.

Entering data with Auto Fill

If you’re typing in data that has a pattern to it, Excel can complete the pattern for you. You can use this for number sequences, times, and dates.

Enter the start of the sequence into two cells. If it’s a number sequence, you’ll need to enter at least two numbers. For times and dates, Excel can use just one. For alternate months (January, March, etc.), you’d enter the first two to show the pattern.

Highlight the cells containing your data.

Click the Fill handle. It’s a tiny dot in the bottom right of the selected area. Your cursor becomes a small black cross when you hover over it.

Drag the mouse to cover the area you want to fill. In the example shown here, you’d drag down. As you drag, you’ll see the value that’s being added shown next to your cursor.

Click the Auto Fill Options menu that appears beside your cursor to find options for the fill, including Copy, which fills each cell with the data you’ve selected. You can also fill weekdays, days, months, or years from dates. Try putting 31-Jan in the first box, then autofilling months to get the last day of each month.

Even quicker: If you just want a sequence of numbers going up by 1, enter the first number, select the cell and hold down Ctrl while you drag the Fill handle.

Filling to match the neighboring column

There’s an even quicker way to autofill a range so it matches the size of the neighboring columns.

Enter the first two data items in the sequence you want to use. For example, if you want to add years starting at 2025 and want to increase by 1 each time, enter 2025 and 2026 into two cells.

Highlight both cells containing the data items you just entered.

Double-click the Fill handle in the bottom right of the selected area.

Your data column is automatically filled to match the size of the neighboring columns. In my example, the autofill extends to where the data ends in the third column, even though the nearest column is shorter.

Using Flash Fill

Introduced in Excel 2013, Flash Fill recognizes patterns in data based on neighboring cells. It’s much more sophisticated than Auto Fill. You can use it, for example, to reformat text and split or combine data. Here’s an example.

You might have a list of names, wi...

Table of contents

• Cover
• Title
• Copyright
• Contents
• 1 Entering data with Auto Fill
• 19 Finding an item by its ranking using LARGE
• 41 Finding data with MATCH
• 61 Debugging: Tracing dependents
• 81 Creating a pivot chart
• BackCover