Don't Fear the Spreadsheet: A Beginner's Guide to Overcoming Excel's Frustrations
200
Don't Fear the Spreadsheet: A Beginner's Guide to Overcoming Excel's Frustrations
200eBook
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473267 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 07/01/2012 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 200 |
| File size: | 14 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Don't Fear the Spreadsheet
By Tyler Nash, Bill Jelen
Holy Macro! Books
Copyright © 2012 Holy Macro! BooksAll rights reserved.
ISBN: 978-1-61547-326-7
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:
• pad of green ledger paper
• mechanical pencil
• Pink Pearl eraser
• An Xacto knife
• An 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.
There are free templates available. Use the Search Office Online box. Type: Personal Finance. You have these free choices available:
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.
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.
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.
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.
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.
How Many Worksheets Come in a Workbook?
Three. But you can change this. Typically, a new workbook opens with Sheet1, Sheet2, Sheet3.
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.
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.
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.
You Did What?
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 cchange, or right-click that worksheet tab and left-click to select Rename from the popup menu.
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.
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:
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.
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.
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.
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.
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?
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.
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.
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.
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.
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.
(Continues...)
Excerpted from Don't Fear the Spreadsheet by Tyler Nash, Bill Jelen. Copyright © 2012 Holy Macro! Books. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.