- Shopping Bag ( 0 items )
Ships from: JACKSONVILLE, FL
Usually ships in 1-2 business days
Ships from: Los Angeles, CA
Usually ships in 1-2 business days
- What This Book Covers
- Why I Wrote This Book
- Read the Whole Thing?
- Watch out for the nerdy stuff
- How This Book Is Organized
- Part I: First Things First
- Part II: Basic Stuff That You Must Know
- Part III: How to Impress the Easily Impressed
- Part IV: Faking It
- Part V: The Part of Tens
- Part VI: Appendixes
- Dumbmie Assumptions
- Conventions -- Typographical and Otherwise
- Of mice and keyboards
- I think icon, I think icon
- Now What?
Part I: First Things First
- Chapter 1: What Is This Thing Called 1-2-3 for Windows?
- OK, So What the Heck Is 1-2-3 for Windows?
- What Can This Puppy Do?
- Crunch some numbers
- Make killer graphs dude!
- Show data on a map
- Manage your lists
- Manage that data zzzz...
- Access other data Bill's paycheck?
- Automate actions with macros
- And into the great beyond
- What Can't It Do?
- Chapter 2: Jump Starting, Breaking, and Stopping
- Starting 1-2-3 for Windows
- Starting 1-2-3 for Windows from the Windows Program Manager
- Starting 1-2-3 for Windows from DOS
- Get Me Out of Here At Least for a Moment
- Setting Yourself Free
- The right way
- The wrong way
- Chapter 3: Jumping Right In
- What You'll Be Doing
- First Steps
- Adding column headings
- Making the headings bold
- Adjusting the column widths
- Entering the Data
- And Now, a Formula
- Inserting new rows
- Sticking in the formula
- Ready for another formula?
- Adding a Title
- Saving Your Masterpiece
- Working with the File
- Saving It Again, Sam
- Quitting 1-2-3 for Windows
- Retrieving Your Work
- Still Afraid?
- Other Stuff for Overachievers
Part II: Basic Stuff That You Must Know
- Chapter 4: Entering Numbers, Words, and Other Things
- Sorry Folks, But You Just Gotta Know This
- The screen tour it's the law
- Some useless terminology
- Rows, columns, cells, and sheets
- Gimme your address or your life
- The cell pointer
- Move it along, doggies
- What to Do with a Blank Worksheet
- Worksheet modes
- What goes in a cell?
- More about what goes in a cell
- Typing in a number
- Making numbers look right
- Entering labels
- Aligning labels and numbers
- When Enter isn't good enough
- Changing Things You've Done
- Overwriting a cell
- Editing a cell
- Nuking a cell completely
- If You Are Salivating for More
- Changing the defaults
- Chapter 5: Let's Have a Look at the Menu and Have a Dialog
- 1-2-3 for Windows: At Your Command
- Two Types of Menu Systems
- The main menu
- Quick menus
- Using Menus
- With a mouse
- With the keyboard
- More about Menus
- What the Menus Are Good For
- The File menu
- The Edit menu
- The View menu
- The Style menu
- The Tools menu
- The Range menu
- The Chart menu
- The Query menu
- The Window menu
- The Help menu
- A Meaningful Dialog
- The anatomy of a dialog box
- Dialog box controls
- Mousing through dialog boxes
- If you prefer the keyboard
- Chapter 6: How to Keep from Losing Your Work
- Protecting Your Work
- Using Files with 1-2-3 for Windows
- A new worksheet
- Saving files
- A Visit to the File Menu
- Files and windows
- The file-related File menu commands
- Nonfile-related File menu commands
- Doing Things With a File -- Step by Step
- Making Backups
- Backing up from 1-2-3 for Windows
- Backing up from the Windows File Manager program
- Backing up from DOS
- Other ways to back up your work
- Chapter 7: The Secret of Formulas
- Formulas: The Definition
- Hello, Operator?
- Using parentheses
- How to Enter Formulas
- Just How Complex Can They Be?
- I created a monster
- Relative and Absolute References
- It's all relative
- Absolutely absolute
- Why use absolute references?
- Even More about Formulas
- Controlling recalculation
- Using built-in functions
- Other types of formulas
- Chapter 8: Making Formulas More Functional
- Getting Functional
- OK, what is an @function?
- A functional example
- Cells and ranges
- Adding sheets to cell references
- Entering @Functions
- The direct approach
- The pointing method
- Let's have an argument
- Editing @functions
- Insert @function here, Insert @function here!
- @Function Categories
- Some Useful Numerical @Functions
- @Functions for Dates and Times
- Combined @Functions in Formulas
- The nesting instinct
- Testing conditions
- Naming Cells and Ranges
- An example
- Another example
- Naming your cells and ranges
- Chapter 9: Cutting and Changing without Screaming and Crying
- Types of Changes You Can Make
- Cutting and Pasting -- Scissors and Glue Not Required
- How cutting and pasting works
- Why cut and paste?
- Cutting and pasting a cell
- Cutting and pasting a range
- Cutting and pasting formulas
- The E-Z way to move things
- Copying Things Things Things
- How copying works
- Why copy?
- Copying a cell to a cell
- Copying a cell to a range
- Copying a range to a range
- Copying formulas
- Drag-and-drop copying
- Adjusting Column Widths
- Why widen columns?
- Adjusting column widths using the menu
- E-Z ways to adjust column widths
- Changing Row Heights
- Erasing Cells and Ranges
- Adding New Rows and Columns
- Why do it?
- Adding new rows
- Adding new columns
- Getting Rid of Rows and Columns
- Why do it?
- Eliminating rows
- Deleting columns
- Transposing Rows and Columns
- Why do it?
- How to do it
- Finding and Replacing -- Wherefore Art Thou, Text String?
- Sorting Ranges of Cells -- Head 'Em Up and Move 'Em Out
- Why do it?
- How to do it
- Chapter 10: Getting It on Paper
- The Seedy Side of Printing
- Printing 101
- Getting a Sneak Preview
- Printing 202
- Printing part of your worksheet
- Printing specific pages
- Printing multiple copies
- Changing the print orientation
- Adjusting paper sizes
- Adjusting margins
- Adding headers and footers
- Sizing your printout
- Suppressing and printing certain elements
- Printing title rows or columns on every page
- Quick Reference for Printing
- Printing Charts, Drawings, and Other Neat Stuff
- Printers du Jour
- Laser printers
- Inkjet printers
- Dot-matrix printers
- Daisywheel printers
- Chapter 11: In Another Dimension
- First, Some Terminology
- Why Use 3-D Worksheets?
- Ideas for Using 3-D Worksheets, No Weird Glasses Needed
- Things You Should Know about 3-D Worksheets That Your Mother Never Told You
- Fundamental Stuff
- Adding sheets
- Removing sheets
- Naming sheets
- Color-coding your tabs
- Displaying three sheets at once
- Printing multiple sheets
- Navigating in the Third Dimension Without the Help of Captain Picard
- Activating other sheets
- Selecting 3-D ranges
- Preselecting 3-D ranges
- Group Mode
- A Final Note
- Chapter 12: Making 1-2-3 Work Like Your Indentured Servant
- Having It Your Way
- It's Not Default of the Worksheet
- User Setup Including Autosave
- Splitting Windows Not with Your Baseball
- Freezing Rows and Columns Even in the Dead of Summer
- Zooming Windows
- Chekking Your Speling
Part III: How to Impress the Easily Impressed
- Chapter 13: Fancy Formatting Footwork
- What Is Stylistic Formatting?
- Why Bother?
- General Principles of Formatting
- Screen or printer?
- General formatting how-tos
- Dealing with Fonts
- Types of fonts
- Changing fonts
- Borders, Lines, and Frames
- Adding lines or borders
- Adding designer frames
- Color, Color, Everywhere
- Alignment and Misalignment
- Word wrap
- Centering across cells
- Changing orientation
- And Now, the E-Z Way to Format Stuff
- Some Final Words
- Chapter 14: Impressing Your Boss with Championship Charts
- The Chart's Parts
- Charts Is Charts
- How to Create a Chart
- Making a Chart Be Your Eternal Slave
- Changing the chart type
- Moving and resizing a chart
- Chart annihilation techniques
- Printing charts
- Rearranging Your Chart's Furniture
- Changing chart titles and labels
- Changing chart colors
- Hungry for more chart power?
- Charming Chart Choices
- A 3-D bar chart
- A horizontal bar chart
- A chart that knows how to accessorize
- A mixed chart
- Stacked bars
- An XY chart
- A stock market chart
- A 3-D line chart
- Another XY chart
- Chapter 15: Mapmaker, Mapmaker, Make Me a Map
- Why a Map?
- Types of Maps Available
- Map-Making Overview
- Creating a Map
- Setting up a map
- Creating your mapsterpiece
- More about mapping
- Modifying a Map
- Moving and resizing a map
- Changing the title
- Changing the bins, colors, and legend text
- Zooming your map
- Advanced Mapping
- Chapter 16: A Very Expensive Doodle Pad
- The Draw Layer -- Like Your Guardian Angel
- The Drawing Tools
- Tooling with the Drawing Tools
- Moooving objects
- Resizing objects
- Redecorating your picture so it looks maavelous
- Restacking objects
- Combining objects -- you're the match maker
- Adding Your Van Gogh as Clip Art
- Importing clip art
- Reading .CGM files
- Serious Uses for the Draw Layer
- Calling attention to cells
- Improving your charts
- Creating diagrams
- Brown nosing your boss
- Chapter 17: Showing Off with Shortcuts and SmartIcons
- Why SmartIcons?
- Smart Facts about SmartIcons
- But What Do They Do?
- So Many Palettes, So Little Time
- Changing palettes with the special SmartIcon
- Changing palettes with the status bar
- Moving the Palette and Changing the Size of the Icons
- What Makes SmartIcons So Smart?
- Shortcut Keys
- Menu shortcuts
- Using the keyboard for menus
- Function keys
- Stylistic shortcuts
Part IV: Faking It
- Chapter 18: The Lowdown on Databases and Lists
- Lists and Databases
- Dancing with Databases
- Creating a Database
- Querying a Database
- Changing the query
- Summing query results
- Chapter 19: Cool Formulas You Can Steal
- Read Me First
- Common, Everyday Formulas
- Calculating a sum
- Computing subtotals and grand totals
- Computing an average
- Calculating a percentage change
- Finding the minimum and maximum in a range
- Calculating a loan payment
- Mathematical Formulas
- Finding a square root
- Finding a cube or other root
- Checking for even or odd values
- Generating a random number
- Rounding numbers
- Label Formulas
- Adding labels
- Working with labels and values
- Date Formulas
- Finding out what day it is
- Determining the day of the week
- Determining the last day of the month
- Miscellaneous Formulas
- Looking up a corresponding value
- Looking up an exact corresponding value
- Chapter 20: Just Enough about Macros to Survive
- A Macro by Any Other Name . . .
- Generals about Macros
- Your First Macro Don't Spoil It
- Creating the macro
- Running the macro
- Modifying the macro
- Some macro rules
- The Macro Recorder Not Just Your Greatest Hits
- Recording a Macro A Doo Wop Wop
- Getting More Advanced Figaro, Figaro
- Recording a formatting macro
- Using Other People's Macros
Part V: The Part of Tens
- Chapter 21: Ten Good Habits You Should Acquire
- Chapter 22: Top Ten Concepts Every 1-2-3 for Windows User Should Understand
- Chapter 23: The Ten Commandments of 1-2-3 for Windows
- I: Thou Shalt Always Maketh Backup Copies
- II: Thou Shalt Check Thy Work Carefully
- III: Remember Thy Right Mouse Button
- IV: Thou Shalt Not Take the Name of Bill Gates in Vain
- V: Honor Thy SmartIcons and Shortcut Keys
- VI: Thou Shalt Not Copyeth the Program from Others
- VII: Save Thy File Beforeth Taking Drastic Measures
- VIII: Thou Shalt Enable Undo and Use It Daily
- IX: Thou Shalt Consult Thy Local Guru with Matters of Importance
- X: Thou Shalt Feareth Not to Experiment
Part VI: Appendixes
- Appendix A: If You Gotta Install It Yourself
- For Do-It-Yourself Types
- Preflight Checkout
- Installing 1-2-3 for Windows
- Appendix B: Glossary
Reader Response Card
"Something hidden. Go and find it. Go and look behind the Ranges."
Rudyard Kipling, 1865 1936
In This Chapter
You may already know that 1-2-3 for Windows, like all other Windows spreadsheets, enables you to store as many as 256 sheets in a single worksheet file. Each of these sheets has 8,192 rows and 256 columns. If you do your arithmetic, you see that the total works out to more than a half billion cells at your disposal. This chapter explains how to use this powerful feature for fun and profit well, at least for profit -- maybe.
Before you get too far into this chapter, you need to understand some terminology. For example, you may be tempted to call each separate sheet in a work-sheet file a page -- after all, it's easy to picture a 3-D worksheet as a huge notebook with lots of pages in it. For the record, here is a list of the standard terminology for 1-2-3 for Windows:
But if you want to substitute the term pages for sheets, be my guest. I call them pages, too, when I'm not writing books about them.
Jeepers, a half billion cells! Don't get too excited, however, because you can never use all these cells. You'd run out of memory long before you even got close. The benefit of a 3-D worksheet is not in the number of cells you can access. The benefit, rather, is in the great way you can organize your work and break it up into more manageable units.
Many spreadsheet projects can be broken down into distinct chunks. For example, you may have a chunk that holds your assumptions, several different chunks that hold tables of values, a chunk to store data for graphs, and so on. Before the days of 3-D worksheets, one of the most difficult aspects of dealing with large spreadsheets was figuring out where to put all the various chunks. With a 3-D worksheet, however, it's simple: put each chunk on a separate sheet.
Figure 11-1 shows an example of several sheets in a 3-D worksheet. Notice that the name of each department appears on the tab; therefore, you can jump to a department's budget just by clicking the tab.
Before you get into the meat of 3-D worksheets, let me whet your appetite with a few ideas on how to put this 3-D worksheet business to use. You may be able to use some of these ideas, or -- better yet -- maybe you can come up with some new ideas on your own. If so, let me know. I'm always looking for good ideas for future books!
If managing different scenarios is your bag, a better approach is to use the Version Manager feature in 1-2-3 for Windows.
Those extra worksheets have a great deal of other uses, too, but first you need to learn a few basic concepts.
Keep in mind the following concepts when you're working with more than one sheet in a 1-2-3 for Windows worksheet file:
This section explains how to do some basic operations involving 3-D worksheets, including adding, removing, and naming sheets, displaying three sheets at once, and other basic concepts.
When you create a new 1-2-3 for Windows worksheet, it has only one sheet sheet A. If you want to use additional sheets, you have to insert them yourself. Inserting additional sheets is actually pretty easy to do. If you have a mouse, simply click the New Sheet button at the upper right of the worksheet window, as you can see in Figure 11-2. The 1-2-3 for Windows program sticks a new sheet directly after the one you're currently working on and makes the new sheet the active one. The active sheet is the one that you're working on and contains the cell pointer.
Our rodentially challenged friends need to use the menu to add a new sheet. The Edit-->Insert command does the trick and displays the dialog box shown in Figure 11-3. You select the Sheet option, specify whether you want the sheets inserted before or after the current sheet, and then select the number of sheets you want to add.
If you want to get rid of an entire sheet including everything that's on it, of course, activate the sheet that you want to zap and then select the Edit-->Delete command. The dialog box in Figure 11-4 appears. Select the Sheet option and then choose OK.
By the way, if you try to delete the only sheet in a worksheet, 1-2-3 for Windows objects and displays a message telling you that you can't delete all sheets.
A quicker way to delete the current sheet is to right-click the sheet's tab to display a shortcut menu. After you choose Delete from the menu, the current sheet disappears in a flash.
Deleting an entire sheet is a pretty drastic measure, because everything on the sheet is gone in an instant. If any of your formulas refer to cells on a deleted sheet, they'll return
ERR for error. So before you nuke a sheet, make sure that's what you really want to do.
You should give a meaningful name to every sheet that you use. By naming the sheets, you make it easy for you or anybody else who may inherit the file to identify what's on each sheet. After all, it's easier to remember that your boss's sales projections are on a sheet named BOSS than on a sheet named R. You also can use sheet names in formulas, which can make the formulas more understandable. Calculating a ratio with a formula such as +BOSS:A1/BOSS:A2 makes more sense than using +R:A1/R:A2, no?
Naming a sheet requires a mouse. Oddly enough, 1-2-3 for Windows does not have a menu command to perform this operation. So if you're working on a new file on a mouseless laptop for example, you have to live without named sheets for a while. Sorry.
To give a sheet a name, just double-click the sheet's tab and then type in a name. Sheet names can be up to 15 characters long 1-2-3 for Windows does not distinguish between upper- and lowercase letters in names. Avoid using spaces, commas, semicolons, periods, or any other nonletter or nonnumber characters. And finally, don't create sheet names that look like cell addresses such as AB12; 1-2-3 for Windows may get confused.
You can continue to use the original sheet letter, even if you give the sheet a name. For example, if you name the first sheet IntroScreen, you can enter either a formula such as @SUMINTROSCREEN:A1..A6 or a formula such as @SUMA:A1..A6. The program recognizes both formulas as the same request for information, one using the sheet name and the other using the sheet letter. However, 1-2-3 for Windows always replaces a sheet letter reference that you enter with its name if it has one.
If you want to change a sheet name, just double-click the tab. The program highlights the current name. Just type in a new name to replace the old one. Any formulas that have references to the old name are changed automatically to the new name.
There's no need to settle for drab tabs. You can make your sheet tabs any color you like. Besides adding some pizzazz to your screen, using different colors for your sheet tabs makes it easier to identify the sheets. Here's how to do it. Just right-click a sheet tab and choose Worksheet Defaults from the shortcut menu. A rather imposing dialog box appears. Check out the bottom-right part of the dialog box and look for the pull-down list labeled Worksheet tab. Click the list and you can choose from 255 glorious colors.
Normally, you can see only one sheet at a time. But if you want to see or work with three consecutive sheets from a 3-D worksheet, you can get a perspective view, as shown in Figure 11-5.
To get into the perspective view, select the View-->Split command and then select the Perspective option in the dialog box that appears. To get back into the normal viewing mode, choose the View-->Clear Split command. You'll learn how to navigate through these sheets later in the chapter.
If you read Chapter 10, you already know how to print a worksheet. But what about printing a worksheet that has more than one sheet? Easy. Issue the File-->Print command and then select the All worksheets option. That's all there is to printing more than one sheet.
If you want each sheet to start on a new piece of paper, insert manual page breaks at the top of each sheet. To insert manual page breaks, move the cell pointer to the upper left cell in the second sheet. Select the Style-->Page Break command and then select the Row option. The program inserts a page break before it prints that sheet. Repeat this process for each sheet in the worksheet.
You already know how to navigate through a 2-D worksheet by using the arrow keys, mouse, scroll bars, and so on. Moving around in a 3-D worksheet requires a bit more effort, however, because you have another dimension to be concerned about. But again, learning to move around a 3-D worksheet is fairly logical once you get the hang of it. It's kind of like driving on the Los Angeles freeway, only infinitely safer and with much less gridlock.
Before you can scroll around on a sheet, you must activate, or display, it on-screen. The easiest way to activate a specific sheet in a 3-D worksheet is to click the tab with your mouse. If you have lots of sheets or sheets with long names, the tab you want may not appear. You can click the little arrows to the left of the New Sheet button, as shown in Figure 11-6, to scroll the tabs to the left or right until the one you want appears.
If you're in perspective view with three sheets showing, you simply can click any of the visible sheets to activate one.
Sometimes, using the keyboard to activate a different sheet may be more efficient. Table 11-1 lists the keyboard combinations necessary to activate a sheet.
Table 11-1 Keyboard Combinations to Activate Sheets
|Key Combination||What It Does|
|Ctrl+PgDn||Activates the preceding sheet, unless you're on the first sheet. Then it has no effect.|
|Ctrl+PgUp||Activates the next sheet, unless you're on the last sheet. Then it has no effect.|
|Ctrl+Home||Activates the first sheet and moves the cell pointer to the upper left cell.|
|End, Ctrl+Home||Moves to the last cell that contains data on the last sheet that contains data.|
A fast way to activate a far-off sheet is to press F5, the Goto key. 1-2-3 for Windows asks you what address you want to go to. Enter a sheet letter followed by a colon, and you're there in a jif. For example, if you want to activate sheet M, press F5, type M:, and then press Enter. If the sheet has a name, you can enter the name followed by a colon. Wouldn't life be wonderful if we had Goto buttons on our cars?
When you're building a formula that references information on more than one sheet, you can either enter the cell references manually, or you can use pointing techniques similar to those used in a single sheet.
For example, assume that you are building a formula in cell A:A1 that adds up the figures in range B:A1 through F:A1, a common formula to consolidate the numbers in six sheets. You can either type @SUMB:A1..F:A1, or you can point to the argument and let 1-2-3 for Windows create the range reference for you. Do so by performing the following steps:
Watch the formula being built in the edit line.
1-2-3 for Windows brings you back to the cell that holds the formula.
Rather than press Ctrl+PgUp to activate other sheets while pointing, you can hold down the Shift key and click a sheet tab.
When you're formatting ranges that extend across different sheets, you may prefer to select the range before you issue the formatting commands. But as you'll see later, Group mode can simplify formatting all the sheets.
When you're dealing with a single sheet, you simply drag the mouse across the range to select it or you can press Shift+arrow keys to preselect the range. Preselecting across several sheets is very similar: just hold down the Shift key while you activate another sheet either by clicking a tab or by pressing Ctrl+PgUp or Ctrl+PgDn.
Before putting this chapter to bed, I want to discuss one more topic that's relevant and actually pretty useful at times -- Group mode. Group mode enables you to format all the sheets in a 3-D worksheet file at once. After you're in Group mode, any changes you make on one sheet using the Style menu or its equivalent SmartIcons affect all other sheets in the file. For example, if you're in Group mode and you change the font in cell A1 on sheet A, the program also changes the font in cell A1 on all the sheets in the file.
To get into Group mode, do the following:
As a reminder that your formatting applies to all sheets in the file, 1-2-3 for Windows displays the word
Group in the status bar at the bottom of the screen.
Group mode can be very handy if you want all the sheets in your file to look the same. For example, after you change column widths or the formatting of cells in one sheet in a group, the other sheets all follow suit. Obviously, using Group mode can be a real time-saver -- and it also makes your work look more consistent.
If you find that dealing with the third dimension is rather confusing, don't despair. It's all very logical, and you'll get the hang of it after you start playing around with 3-D worksheets. Once you master it, you'll wonder how you ever got along without out it. If you find yourself getting really frustrated, however, just stick with single-sheet worksheets. After all, people have been using only one sheet for more than a decade -- and they got along just fine.