Don't Fear the Spreadsheet: A Beginner's Guide to Overcoming Excel's Frustrations - Softcover

Nash, Tyler; Jones, Kevin; Urtis, Tom; Jelen, Bill

Softcover

ISBN 10: 1615470034 ISBN 13: 9781615470037

Verlag: Holy Macro! Books, 2012

Inhaltsangabe

Written in a question-and-answer format, this lowest-level beginner book covers the extreme basics of using spreadsheets in Excel. Instead of delving into advanced topics that scare most Excel novices away, the guide starts at a much more basic level, quickly providing a passable knowledge of the program and allowing users to overcome their fears and frustrations. It answers hundreds of common questions, including Can I delete data from a spreadsheet without changing the formatting? How can I merge two cells, columns, or rows? How do I use text-wrapping? How do I create custom functions? and What is a Macro and how do I go about creating it? Intended for the roughly 40 percent of Excel users who have never even entered a formula, this book will demystify the problems and confusion that prevent them from using the program to its potential.

Über diesen Titel

Über die Autorin bzw. den Autor

Tyler Nash is a college student who turned to Bill Jelen for spreadsheet help. She convinced him to compile their question-and-answer sessions into a comprehensive guide for fellow novice Excel users. She lives in St. Augustine, Florida. Bill Jelen is an Excel expert at www.mrexcel.com and the author of numerous books, including Excel Gurus Gone Wild, Pivot Table Data Crunching, and several titles in the Excel for Professionals series. He lives in Akron, Ohio. 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. Kevin is a Microsoft MVP in Excel.
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. Tom is a Microsoft MVP in Excel.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Don't Fear the Spreadsheet

By Tyler Nash, Bill Jelen

Holy Macro! Books

Copyright © 2012 Holy Macro! Books
All rights reserved.

ISBN: 978-1-61547-003-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...

Details zur Ausgabe

• Verlag: Holy Macro! Books
• Erscheinungsdatum: 2012
• Sprache: Englisch
• ISBN 10: 1615470034
• ISBN 13: 9781615470037
• Einband: Taschenbuch
• Anzahl der Seiten: 210
• Kontakt zum Hersteller: Nicht verfügbar
• Verantwortliche Person: Nicht verfügbar