Don't Fear the Spreadsheet E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 288

Veröffentlichungsjahr: 2024

Beschreibung

This book is written in an easy-to-follow question-and-answer format, specifically designed for complete Excel beginners. Focusing on the extreme basics of using spreadsheets, it avoids overwhelming readers with advanced topics and instead builds a foundational understanding. Readers will quickly gain a passable knowledge of the program, addressing common fears and frustrations through clear explanations and practical examples.
The guide answers hundreds of everyday questions, such as "Can I delete data without changing formatting?" and "How do I use text-wrapping?" as well as slightly more advanced queries like "What is a Macro, and how do I create one?" It empowers users by breaking down intimidating concepts into manageable steps, making Excel approachable and useful for even the most inexperienced users. The focus is on helping readers become comfortable with essential tasks, from merging cells and formatting text to understanding formulas and navigating the interface.
Aimed at the 40 percent of Excel users who have never entered a formula, this book demystifies the program's tools and functions, transforming confusion into confidence. By the end, readers will feel equipped to use Excel effectively for personal and professional tasks, overcoming barriers to productivity.

Leseprobe

by

Tyler Nash

Kevin Jones, Tom Urtis, Bill Jelen

Holy Macro! Books

PO Box 82

Uniontown, OH 44685

Don’t Fear the Spreadsheet

© 2012 by Holy Macro! Books

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms used in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books.

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Printed in USA by Hess Printing

First Printing: May 2012

Author: Tyler Nash, Bill Jelen, Tom Urtis, Kevin Jones

CopyEditor: Ash Glentry

Proofreader: Smit Rout

Layout: Nellie J. Liwam

Cover Design: Shannon Mattiza, 6Ft4 Productions

Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685

Distributed by: Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-003-7 (Print) 978-1-61547-106-5 (Mobi) 978-1-61547-326-7 (ePub)

Library of Congress Control Number: 2012939999

About the Authors

Bill Jelen is the host of MrExcel.com. You can find his daily Learn Excel podcasts on the bjele123 channel at YouTube. He has written 30+ books but says his Learn Excel 2007-2010 From MrExcel should be your next book purchase after you finish this book. When he isn’t writing about Excel, you will find him in a kayak on the Indian River near Merritt Island Florida or at his home near Akron, Ohio.

Tyler Nash is currently pursuing her Master’s Degree in English and Philosophy. She is the Copy Editor for Holy Macro! Books. Outside of a love for learning, Tyler also enjoys playing the Didgeridoo and road trips.

Tom Urtis is owner of Atlas Programming Management (www.atlaspm.com), a Microsoft Office business solutions company specializing in Excel project development and training. Tom also created the Excel Aptitude Test (called XAT, xat.atlaspm.com) to measure Excel skills and know-how. When Tom’s not at the computer, he enjoys the outdoor life that California offers, and the diverse cultures of the San Francisco Bay Area where he lives.

Kevin Jones has been building applications in too many languages for too many years starting with IBM mainframe Basic Assembly Language. He is known for his ability to use Visual Basic to get Excel to do the most unusual and unexpected things. As “zorvek” he has answered close to 10,000 questions about Excel and Visual Basic on various online forums. NASA wouldn’t take him so he spends his free evenings with his daughter Emily at the theater watching 3D superhero films.

Dedication

Tom: To my brother, Michael Urtis.

Bill: To Nate Oliver.

Kevin: To Nate, a good man and a friend. You are missed.

Tyler: To Jane Dermody.

Acknowledgments

Bill wishes to thank Tyler Nash for pitching the idea for this book. He is grateful to Tom Urtis and Kevin Jones for jumping in to keep the book on schedule and into your hands. Various people have contributed stories or anecdotes for the book: Larisa Sadarangani, Mark Doughty, Jane Shiels, Mark Spencer, Patricia Jones, Bryan K, Kevin Marcote and David Rosenthal. Thanks to Scott Pierson for his creative promotions for this book.

Tom wishes to thank Bill Jelen for inviting him as a coauthor. Big thanks to Tyler Nash for conceptualizing the idea for this book, reminding us of the daunting starting point we all felt when opening our first Excel workbook. Thanks to Steve and Nancy Martin for contributions, and to Scott Pierson for the book’s early public shout-outs. Special thanks to people using Excel at every level including you, the reader. Your shared questions and solutions have been the Excel community’s best teacher.

Tyler wishes to thank Scott Pierson and Bill Jelen for welcoming her to the wild world of Excel. She also thanks Professor of Sociology, Steven Stoll, for driving her to move outside of her Excel comfort zone. Further, she extends her appreciation to all who supported her along the way: Sara Peterson, Nash, Peggy and Jackie Nash, Maureen Harding, Maura Farrell, Sarah Weckerly, Mary Ellen Jelen and Dr. Robert Colson.

Kevin wishes to thank Bill for giving him the opportunity to collaborate on this book and to Tom for taking pictures of the fun.

Foreword

Never in my life could I imagine that I would need to use Microsoft Excel for any reason. However, life had different ideas about that and I found myself in the presence of the world’s greatest Excel guru and his cohorts. Yes, Bill Jelen, MrExcel himself and his Excel team of any fluctuating number of people suddenly became a fairly consistent part of my life. Interestingly, so did the need for Excel. It’s difficult to constantly be surrounded by people using terms like: “Concatenate, Pivot Table, VLOOKUP, Spreadsheet, Cell” and other various Excel terms and not begin to wonder what all this Excel business is about. People get very excited over Excel, which naturally spiked my curiosity. That curiosity is just what gave birth to Don’t Fear the Spreadsheet. In May of 2010 I found myself siting in a conference room in Ohio with Bill Jelen and the rest of the MrExcel crew. A few hours into it and several daydreams later it dawned on me that these people had no clue what it was to be ‘unfamiliar’ with Excel. Needless to say I brought this to their attention as it was becoming increasingly difficult to decode the Excel jargon. “Excuse me,” I interrupted. “You are aware that there are people out there who haven’t a clue what to do after opening Excel, right?” Crickets…. Followed by a table of people staring at me…. Silent, both astonished and horribly disappointed. After what seemed an eternity of awkward and terrifying silence, Bill Jelen turned to me and said, “I’m not sure what you mean.” I repeated my question. It was at that moment that I realized that the group of Excel geniuses that surrounded me had been so far removed from their first days of Excel, that they couldn’t fathom such a statement.

The group of Excel geniuses that surrounded me had been so far removed from their first days of Excel

I expanded on my question and explained to them how terrifying Excel can be, especially for those of us who need to use it for work and don’t even know what the fx bar is. I reminded them of what it was like to be an Excel ‘newbie.’ I watched the lights behind their eyes flicker with confusion, fear for remembering when Excel was foreign to them and the realization that some of us just don’t know how to use Excel, let alone program it to talk. Baffled, they began inquiring about this ‘clueless’ population of people and it became evident that certainly there was something they could do to help people like myself. After going back and forth, Bill asked me to come up with one hundred basic Excel questions that he and his crew of Excel gurus could address. The following week I opened Excel for the third time in my life, played around, made hundreds of mistakes and compiled a list of questions—Don’t Fear the Spreadsheetwas born. Bill Jelen, Kevin Jones and Tom Urtis came together to tackle my questions and provide the worlds Excel ‘newbies’ with the most in depth and learner friendly answers. The result: perfection. No longer do we have to spend hours on end searching for answers to Excel’s simplest questions, suffering the trials and errors of misinformation or feel foolish asking our fellow employees how to accomplish the simplest of tasks. Today, I can confidently state that I don’t fear the spreadsheet.

– Tyler Nash

Introduction

Microsoft Excel is installed on 750 million desktops worldwide. Assuming some people have Excel both at work and at home, you still have 400 million people ‘using’ Excel. However, the level of use varies dramatically.

This figure divides the world of people using Excel into groups of 80 million people each:

Figure 1 40% of people using Excel have never entered a formula outside of using the AutoSum icon.

That means 80 million people ‘use Excel’ to open a workbook that they get in their e-mail and print it.

Another 80 million people get some data, do some formatting, add titles, maybe a total row and print.

For these groups of people, Excel can be a confusing and daunting product to use. Excel has many strange behaviors that Excel gurus take for granted. When you ask the department guru why Excel does X, he or she will scoff, wave it off, and say, “That’s just the way it happens.”

Excel gurus forget when they were in your position, faced with the same challenges that you are facing. Most Excel books out there, even Excel for Dummies, are written for people who are already in Level 3. Those books are trying to take someone from Level 3 to Level 4. Not many Excel books focus on people who are currently in Level 1 or Level 2. This book is designed for you.

Tyler Nash is a complete Excel rookie and had the job of keeping the three Excel gurus grounded. Tyler was able to think like an Excel rookie because she IS an Excel rookie. The questions here are designed to be helpful to the person who is completely new to Excel. That isn’t to say that sometimes Bill or Kevin or Tom will try to pull you into Level 4 or even the beginning stages of Level 5 in this book. What might seem like a really easy question requires a bit more than you might think. Or, it might be that the Level 2 approach takes an hour instead of the two minutes required by the Level 3 approach. In these cases, we will show you both ways to solve the problem. It is up to you if you try the intimidating way or the slower way.

As you gain confidence with Excel, I hope you go back and try the other ways to solve the problem. There is a repeated story of people in a Microsoft focus group. They ask how to do X in Excel. The leader of the focus group says that it can easily be solved in six clicks using a “Pivot Table”. The person invariably says, “Oh – no… no… I don’t want to do that. I don’t need a solution that involves a Pivot Table.” This is a frustrating position for me as an Excel guru. I can imagine you struggling through the non-Pivot Table method and having it take four hours, but I could teach you the Pivot Table way and the whole thing can be done in two minutes. Chapter 9 is designed to give you a quick introduction to the most powerful tools that Excel has to offer. Don’t start there. But that chapter is a jumping off point to move on to another book that will make you a pro at all that Excel has to offer.

There are two longer case studies in the book. One is designed for Level 2 and one for Level 3. These longer case studies show you how to build a small model from start to finish.

Chapter 8 shows every way to accomplish several common tasks in Excel. Often, a manager will want you to do something his way. This will teach you every way to do these common tasks.

Chapter 9 is particularly useful if you are heading off to a job interview this afternoon

Chapter 9 is particularly useful if you are heading off to a job interview this afternoon. Skimming that chapter will give you familiarity with topics that are bound to come up if the job requires knowledge of Excel.

Throughout the book, you will encounter a special section we call “You Did What?” These are true stories of people who went through painful gyrations in Excel when there was a one-minute solution to solve the problem. I did not include these to make fun of the people who went through the long way. Every person using Excel has gone through the long way at some point. These “You Did What?” sections are there to remind you that there might be a faster way. (But, if you actually find yourself doing any of these twelve tasks day after day, please read how to do it faster.)

The screenshots and shortcut keys in the book are from the Windows version of Excel 2010. A few features won’t be in Excel for the Mac or in Excel 2007 or earlier on Windows.

Other elements in the book:

The red boxes highlight important quotes along the way.

Tips offer tricks to speed you along your way.

Notes offer asides. Things related to the topic that might help explain why something happens.

Cautions warn you when you should be careful. Not following the instructions exactly will lead to bad things.

The book will use Ctrl+A with a capital A, even though you would actually type Ctrl and the lower case a. Since Ctrl+1 and Ctrl+l look the same, I will remind you that it is Ctrl+1 (the number one).You will not see Ctrl+L used anymore in Excel. When the Ctrl or Alt key might seem ambiguous (such as Ctrl+; or Alt+=), the book will clarify that you should press Alt and the equals sign.

Chapter 1 - Excel Basics

Why Do We Need Excel?

What is it for? What does it do? When should I use it?

To understand what Excel is good at, it might help to go back to 1978. At that time, if you had to keep track of any numbers, you had a toolkit with the following items:

A pad of green ledger paperA mechanical pencilA Pink Pearl eraserAn Xacto knifeAn electronic adding machine

Say that you were keeping track of time for a project so you could submit your invoice at the end of the week. You would use the mechanical pencil to log hours on the green ledger paper. You would use the adding machine to keep a column of running total hours and a running total of the money you had earned.

You were erasing a lot, and re-doing the same calculations

The eraser was used any time that you discovered a mistake in the earlier numbers. You would erase that number and enter the correct number. However, changing that one early number meant that you had to change all of the calculations that came after that number. This would mean that you would be erasing a lot, and doing the calculations all over again.

Sometimes, if you were working on a project that changed frequently such as an annual budget, you would erase a number over and over and over. Eventually, you would erase a hole in the paper! You would then use the Xacto knife to cut a fresh bit of paper from the last page in the tablet and glue it over the hole in your spreadsheet so you could keep using the spreadsheet.

Back in 1978, Dan Bricklin was a college student. For his business classes, he noticed that he was doing the same paper spreadsheets over and over. A case study might have five scenarios, each with a different interest rate. All of the calculations that came after the interest rate entry were identical, but he still had to do them by hand, over and over and over. One of Dan’s ideas was to create a calculator with a trackball in the bottom. The ball would let you scroll back through your calculations to the interest rate entry, change the number, and then roll forward to see all of the calculations performed again using the new interest rate. Working with his friend Bob Frankston, they invented a Visible Calculator on the Apple IIe computer. In the fall of 1979, Dan and Bob started selling VisiCalc and sales of VisiCalc and the personal computer skyrocketed. Over the years, many companies sold spreadsheets. VisiCalc, Lotus 1-2-3, Multiplan, Quattro Pro, and Excel became popular. Today, Excel is the leading spreadsheet program, in use on 750 million Windows computers (and 5 million Macs).

Excel is good at doing calculations, particularly when the numbers used in the calculation might change frequently. Change one number early in the spreadsheet and you get to see all of the calculations reflect the new number.Excel is good for creating charts and graphs from numbers.Excel is good for holding a lot of rows of data. You can sort that data to find the largest sales, the smallest sales, the earliest sales. You can filter the data to find only sales of red cars to people over the age of 65. You can also use a feature called a Pivot Table to summarize thousands of rows of data down to one page to spot trends in the data.And, because it is easy to change the widths of the columns and the height of the rows, it is easy to use Excel any time that you need to do something like a table in Microsoft Word. The big difference… Excel can hold a bigger table than you can create in Word. Even if you need 20 columns, Excel can do it. Even if you need 16,384 columns and 1.1 million rows, Excel can do it.

If you have never used Excel, take an hour and walk through the case studies in the book (the first one is in "Is There a Way to Make a Sheet with Only a Few Cells and Columns?"). You will gain confidence and learn what Excel can do for you.

What Practical Uses Does Excel Have?

And how do I do ANY of it? Ex: calendar, managing personal finances, address book, and the like?

Excel can be used to do anything. The possibilities are limitless. If you have Excel 2007, you can browse a whole bunch of finished workbooks that you can use.

Open Excel. Go to File. From the left navigation of the File menu, choose New. Excel will show you a whole bunch of files available. In the image below, you can see Agendas, Budgets, Invoices, Labels, Schedules and Time Sheets.

Figure 2 The templates on this opening screen are the tip of an iceberg.

There are free templates available. Use the Search Office Online box. Type: Personal Finance. You have these free choices available:

Figure 3 Personal finance? How about check registers, monthly budgets, tax planning, all for free.

Try typing anything in the box. I’ve found NCAA Brackets. I tried Menu and found a variety of grocery planners, dinner party planners, and more.

Figure 4 Menu planners, grocery lists, dinner parties.

What Is the Intersection of a Row and Column Referred to As?

I need to learn the lingo. What do you call the box at the intersection of row 10 and column C?

That box is called a “Cell”. There are 17 billion cells on a worksheet. By convention, the name of a cell is the column name followed by the row number.

The cell at the intersection of column C and row 10 is called C10.

Figure 5 Column name followed by row number.

Note that if you select a cell, the name of the cell appears in the Name Box to the left of the Formula Bar.

If you have a lot of columns, your data might extend past column Z. Excel starts over with column names of AA, AB, and so on. If you actually have a worksheet with more than 701 columns of data, you will get to the point where Excel goes past ZZ and starts over again with AAA. The three character column letters continue all the way out to XFD – a total of 16,384 columns.

Note: Why 16,384? It is 2^14. Similarly, the last row – 1,048,576 is 2^20.

The last cell in the worksheet is called XFD1048576. It is really unlikely that you would ever reach this cell. You could write the name of every living person on earth and only fill up 40% of Sheet 1.

Figure 6 To reach XFD1048576, press Ctrl+Right Arrow then Ctrl+Down Arrow.

Caution: Don’t try this trick with all-time world population. According to noted demographer Jean Bourgeois-Pichat, there have been 81 billion people alive on earth since 600,000 BC to 1988. To enter the names of all of those people in Excel, you would have to use Sheet 1 through Sheet 5.

When you have a contiguous collection of cells, those are known as a Range. The name of a range is the name of the cell in the top left corner, a colon, and the name of the cell in the lower right corner. The figure below is B2:D6.

Figure 7 This range runs from B2 to D6.

Note: If you try to refer to B6:D2 in a formula, Excel will automatically rewrite the reference as B2:D6.

What is a Workbook?

Workbook, worksheet, no… workbook. Wait, what’s a workbook?

A workbook is a collection of worksheets saved in a single file. Each worksheet is identified by a tab across the bottom of the Excel window. While workbooks often have boring names like Sheet1, Sheet2, Sheet3, you can change the names to be more meaningful.

Figure 8 This workbook has two worksheets.

How Many Worksheets Come in a Workbook?

Three. But you can change this. Typically, a new workbook opens with Sheet1, Sheet2, Sheet3.

Figure 9 By Default, you will get three worksheets in a workbook.

If you are creating a simple one-page worksheet, you don’t really need the blank Sheet2 and Sheet3 hanging around back there. Right-click the sheet tab and choose Delete.

Why do they start with three worksheets?

Back in Excel 93, a workbook only contained one worksheet. When Microsoft introduced the ability to include multiple worksheets in a workbook, they decided to make this obvious by including 16 worksheets in every new workbook. I guess they figured that no one would think to use Insert, Worksheet to add new worksheets. This created a lot of silly workbooks with 15 blank worksheets. After that version, Excel changed the setting so that you start with three blank worksheets. They are still doing this because they think that you cannot figure out that the “*” worksheet is how you insert a new worksheet. After reading this book, you will know how to insert worksheets, so there is really no reason to have Excel put three worksheets in every new workbook. To change the setting, go to File, Options. The first panel of the Options dialog is called General in Excel 2010 and Popular in Excel 2007. Use the setting shown here to change the number of new sheets back to 1.

Figure 10 Choose that each new workbook should have one worksheet.

How Do I Insert New Worksheets into a Workbook?

Excel, with all its crafty short cuts must provide a way to quickly insert a new worksheet…Right?

The last worksheet tab in a workbook is a blank worksheet icon with an orange asterisk. This is the New Worksheet icon. Click this sheet tab to add a new blank worksheet as the last worksheet in the workbook.

Figure 11 Click the * sheet tab to add a new sheet to the end.

What if you don’t to move this new worksheet between two other existing worksheets? Click on the worksheet tab and drag it to a new location. A tiny blank triangle will indicate where you are about to drop the new worksheet.

Do you always have to insert the new worksheet at the end and then move it to the new location?

No! There is another way to insert a worksheet exactly where you want it. Say that you want to insert a Feb worksheet between Jan and Mar in this figure. Select the Mar worksheet. From the Home tab, select Insert, Insert Sheet. Excel will insert a new worksheet to the left of the active sheet.

Figure 12 To insert a new worksheet before the current worksheet, use Alt+I+W or this command.

What is with the Masking Tape?

Passing by a coworker’s cubicle at the office, I noticed a piece of masking tape stuck onto her computer monitor’s screen, near the lower left corner. The word “March” had been neatly hand-printed on the piece of tape. It’s odd to see anything stuck onto a monitor screen, but I figured she ran out of post-it notes and maybe was reminding herself of a marching-related exercise routine for after work.

The following month I saw “April” written onto a fresh piece of masking tape, stuck onto the monitor screen in the same location as the March piece of tape had been. I asked her: “What’s up with the masking tape and month names stuck onto your screen?”

She opened a budget workbook for the company and showed me how the tape covered over the Sheet3 worksheet tab. She told me that part of her job is to type the income and expense numbers into that worksheet for the prior month. Each month she needs to remind herself which month is being entered, and since there is no month named “Sheet3”, this was the best way to do it.

Masking tape mystery solved: my coworker did not realize that Excel’s worksheet tab names can be changed. There are two easy and fast ways to accomplish this task. In the first pair of figures, notice that the first step would be to either double-click the worksheet tab whose name you want to change, or right-click that worksheet tab and left-click to select Rename from the popup menu.

Figure 13 To change a worksheet tab name, start by either double-clicking the tab, or right-click the tab and select Rename.

You will see that the existing worksheet name becomes highlighted, as shown in the left-most figure below. Now all you need to do is simply type the new name for that worksheet tab, such as the month name of April, and press the Enter key. In the figure, the worksheet tab that used to be named Sheet3 is now named April.

Figure 14 With the existing tab name selected, type in your new name for that worksheet and press the Enter key.

Tip: A few FYI’s about naming worksheet tabs. The names can be up to 31 characters in length. The proposed new worksheet name cannot be the same as another worksheet name in that workbook (that is, duplicate worksheet names in the same workbook are not allowed). Finally, a worksheet tab name cannot be blank, and it cannot contain the characters *, :, [, ], ?, /, or \.

Oddity: A worksheet name can contain some symbols that cannot be typed on a standard keyboard. This can create some odd-looking worksheet names like in this figure:

Figure 15 Using a macro, you can create sheet names that can’t be typed.

If you want to freak out a co-worker, open the sample files that came with this book. Copy one of these strange sheet names from RenameWorksheet.xlsm to your own book.

How Can I QUICKLY Erase an Entire Sheet?

How can I erase an entire sheet, formatting and all?

First select all cells by clicking the icon above and left of cell A1.

Figure 16 Use this icon to Select All.

On the right side of the Home tab, the Clear dropdown is usually represented by an Eraser icon. Open this dropdown and choose Clear All. Using Clear All will clear cell contents and all formatting. If you want to delete cell contents but leave the existing cell formatting, you can simply press the Delete key.

Figure 17 Use the Clear All icon to erase cell contents and formatting.

How Can I QUICKLY Delete an Entire Sheet from the Workbook?

How do I go about deleting an entire sheet from my workbook?

Right-click the sheet tab and choose Delete from the menu that appears.

Figure 18 Right-click the sheet tab that you want to delete.

If the worksheet has always been blank, Excel will delete it without any further interaction. If the worksheet has data, or if the worksheet once held data and that data has been deleted, Excel will show a message warning that the sheet contains data and the deletion can’t be undone. You can choose to continue deleting or click Cancel to go take another look at the worksheet.

Figure 19 Excel warns you before deleting a worksheet.

Scan through the column numbers to see if one column is missing. This could indicate a hidden column. The hidden column might still have data. Similarly, there might be a hidden row. To unhide all columns and rows, select all cells in the worksheet. Then, select Home, Format, Hide & Unhide. If either Unhide Rows or Unhide Columns is not greyed-out, select those commands.

It is possible that important data is still on a worksheet that appears blank. Perhaps the data is there and someone changed the font color to white. Or, someone used a custom number formatting code of “;;;” to hide the data. After pressing Ctrl+End, press Ctrl+Shift+Home. Excel will select from the bottom right corner back to cell A1. Look in the Quick Sum area to see if any statistics appear. If you haven’t done so already, right-click the Quick Sum area and choose all six statistics: Average, Count, Numerical Count, Min, Max, and Sum. The Count statistic will count cells that contain text.

Caution: The Undo command is not available to bring back a worksheet after it has been deleted. If you accidentally deleted the worksheet, you need to go back to the last saved or AutoSaved version of the worksheet.

How Would I Go about Deleting Several Cells at the Same Time?

I have to delete several cells. Right now, I select one cell, then use the Home, Clear, Clear All, over and over and over. Is there a faster way?

Figure 20 Opening this command over and over is tedious.

There are a lot of tips to make this process easier.

Press Delete to clear a cell

First, selecting a cell or range of cells and pressing the Delete key on the keyboard will clear the cell. Pressing Delete is easier than opening the Clear flyout menu over and over.

Second, there are a few different ways to select all of the cells to delete.

If the cells are in a rectangle, you can use the mouse to click in the top left cell and drag down to the bottom cell. This will select an entire range.

When the cells are not contiguous, you can follow one of these two methods:

Select the first cell. Hold down the Ctrl key while clicking on all the other cells. As long as you hold down Ctrl, Excel will add the newly selected cells to the selection.If using Ctrl seems unnatural, you can press and release Shift+F8. The status bar will indicate that you can Add to Selection. As you click additional cells or ranges, they will be added to the selection.

Figure 21 Shift+F8 toggles you into Add Selection mode.

After you’ve selected multiple cells using Shift+F8, press the Delete key. Then, use Shift+F8 to toggle back to normal selection mode.

Can I Reset Some Cells in a Worksheet While Keeping Others?

I built this loan payment calculator. I would like to be able to reset the input cells without clearing all of the cells.

Figure 22 Clear only the input cells but keep everything else intact.

This one sounds really simple, but it will require some work to make it happen.

First thought – do you want to clear all of the numeric cells that are non-numeric in a certain range? It is likely that the input cells are non-formula cells that contain a number or date. If that is true, you can use the Go To Special dialog as described below in Method 1.

Second thought – would you mind changing the fill color of all cells to be cleared to be similar? If this is the case, you can use the Find All Format as described in Method 2.

Third thought – if neither of the above would work, you could use the named range method as described in Method 3.

Method 1: Go To Special.

1. Select the range A3:G6. This range encompasses all of your input cells but also includes other cells.

2. Press F5 or Ctrl+G to display the Go To dialog.

3. In the lower left corner of the Go To Dialog, click Special to display the Go To Special dialog.

4. In the Go To Special dialog, choose Constants. Uncheck the boxes for Text, Logicals, and Errors, leaving only Numbers checked. Click OK. Only cells C3, C5, F3, and F5 will be selected.

5. Press the Delete key.

Figure 23 Go To Special selects all of the numeric constants within the selected range.

Tip: In Excel 2007 or newer, you can use Home, Find & Select, Go To Special instead of steps 2 & 3 above.

Method 2: Find All Format. This method requires that you have filled all of the input cells with the same fill color and that no other cells have this fill color. To clear the cells with that color, follow these steps:

1. Select one cell in the worksheet.

2. Ctrl+F to display the Find dialog box.

3. Leave the Find What box empty.

4. Click the Format… button.

5. In the Find Format dialog, go to the Fill tab. Choose the color of your input cells. Click OK.

6. Back in the Find and Replace dialog, click Find All. A list of all matching cells will appear in the bottom of the dialog.

7. Click Ctrl+A to select all of the cells.

8. Use the red X to close Find and Replace dialog.

9. Press the Delete key.

Figure 24 Find All in conjunction with the Format feature.

Method 3: Use a Named Range.

This method requires a little advanced planning. You will have to select all of the input cells one time. Select those cells and click in the Name Box that is located to the left of the Formula Bar. Type a one-word name such as InputCells and press Enter. Because you have multiple cells selected, the name will immediately disappear from the Name Box, but that is OK.

Later, you can easily re-select all of the input cells by opening the dropdown at the right edge of the Name Box and selecting the Input Cells from the list. This will select all of the cells. You can then press Delete.

Figure 25 Using a single name for all of the non-contiguous input cells makes it easy to re-select them all later.

What Does ‘Freeze Panes’ Do?

What are panes and why do I want to make them cold?

Freeze Panes should be called “Keep the Headings Visible”.