This isn't Excel it's Magic! - Bob Umlas

This isn't E.xcel, it's Magic!

NOTE: All the tips in this book will work in Excel 2000,2002.2003, and

2007. Most will also work with Excei 1 997.AII the screen shots are from Excel 2007, and the detailed descriptions of how to access many of them are via Excel 2007's new Ribbon. There are also descriptions for accessing the same tips using versions earlier than Excel 2007, and these all begin with the italicized text,"[Exce1 2003: . . . ]" Tips 27, 28, 68, 69, and 90 have no equivalent in versions other than Excel 2007.

This isn,t Excel , it,s Magic! Tips and Tricks for getting

the most out of Microsoft® Excel

•

Bob Umlas

Microsoft® Excel MVP

SECOND EDITION

.4'��

•••• �.. .,,

IIL PUBLISHING

Copyright© 2007, by International Institute for Learning, Inc. All rights reserved. No portion of this book may be reproduced, stored in a re trieval system, or transmitted in any form or by any means-electronic, mechanical, photocopying, recording, scanning, including the right of reproduction in whole or in part in any form, except for brief quotations in critical reviews or articles, without the prior written permission of the publisher. For information, contact the publisher. The information in this book is distributed without warranty. Although every pre caution has been taken in the preparation of this book, neither the author nor the publisher shall have any liability with respect to any loss or damage caused in any manner by the information contained in this book. Ill Publishing, New York titles may be purchased in bulk at a discount for educa tional, business, fund-raising, or sales promotional use. For information, please email [email protected] or call 212-515-5144.

Microsoft® Excel is a registered trademark of the Microsoft Corporation. Published by Ill Publishing, New York a division of International Institute for Learn

ing, Inc., 110 East 59'h Street, 31" Fl., New York, NY 10022 www.iil.com ISBN 978-0-9792153-2-2

Library of Congress Control Number: 2007936376 Second edition Designed by Tony Meisel Publisher Judy Umlas Printed in Canada

I'd like to thank my wife, Judy, for her continued support while I pursue my Excel "studies," including trips to Redmond for the MVP Summits, as well as spending time answering people s questions on the newsgroups . I also want to thank Will Tom pkins my fellow Excel MVPs, my co-work ers, and the many other contributors to the online newsgroups-David Hager, John Walkenbach (who has put many of my excel "oddities" discover ies on his website), Bill Manville,Jim Rech,Jan Karel Pieterse,Tom Chester, Reed Jacobson, Eric Wells, Bob Greenblatt,Tim Aurthur, Monika Weber, Bill Jelen {"Mr. Excel"), Jim Janssen, and Joe Sorrenti for their contributions to my knowledge. I want to acknowledge my best friend. Jerry Goldin, Ph.D., for his ongoing support of everything I do and of my entire family. I want to thank my sister, Marilyn Umlas Wachtel, for being there when I have most needed her. My wonderful children, Stefanie and Jared Um las, have put up with me as I remained at the computer, writing this book, instead of watching Alias or The Simpsons with them. They have been terrific! I would especially like to thank E. LaVerne Johnson, President and CEO of International Institute for Learning (IlL), for believing that I am one of the top three Excel gurus in the world, no matter what I tell her. I also thank her for supporting my love of teaching by offering the Mastering Microsoft® Excel course through IlL all these years. '

,

Bob

Umlas, Microsoft®

Excel

MVP

International Institute for Learning, Inc. (Ill)

I

ntemational Institute for Learning, Inc. (Ill), headquartered in New York City and with eleven operating companies in major cities around the world, is a global

leader in Project, Program and Portfolio Management, Microsoft® Project, Six Sigma, and Business Analysis corporate solutions.

Using its Many Methods of Learning TM, Ill combines the theory and practice

that professionals need in order to manage today's business complexities with maximum flexibility. Ill offers traditional classroom training as well as on-demand learning, "virtual" instructor-led courses, and hands-on leadership simulation classes. Ill recently released Project Management elibrary, a state-of-the art, animated and engaging program that is rich in education and exercises. TRADITIONAL CLASSROOM LEARNING Each year Ill schedules hundreds of live, instructor-led courses and workshops in major cities around the world.This offers a convenient and effective way for profes sionals to participate in classes that expose them to diverse industry perspectives and trainers' real workplace experiences. VIRTUAL ELEARNING Utilizing the latest online technologies, Ill brings professionals a broad curriculum of live, instructor-led courses, including Mastering Microsoft® Excel, via the Internet. This environment allows students to interact live with the instructor and fellow classmates, while avoiding the costs and inconvenience of travel. ON-DEMAND LEARNING Ill offers a wide variety of courses that provide extraordinary content along with the convenience of learning at a student's own pace-whether that's at home or work, day or night. In addition to traditional video, texts and workbooks, Ill offers content-rich, multimedia training programs. COMPUTER -AIDED SIMULATION LEARNING A growing number of Ill's courses use computer-aided simulation.This allows par ticipants to learn b y making official decisions in a realistic and safe "virtual" business setting. ONSITE LEARNING AND CORPORATE SOLUTIONS Ill works closely with clients to develop learning solutions that are tailored to meet their precise business objectives and cultural needs. For more information, please contact: Lori Milhaven, Executive Vice President International Institute for Learning, Inc. Phone:1-212-51S-5121 or1-800-325-1533 e-mail: [email protected] www.iil.com

Also published by Ill Publishing, New York The Zen Approach"' to Project Management by George Pitagorsky, PMP The Power of Acknowledgment by Judith W. Umlas Project Portfolio Management Tools and Techniques by Parviz F. Rad, PhD and Ginger Levin, PhD

Forthcoming: Project Management Poetry, Puzzles and Prose Positive Leadership in Project Management: Practical Leadership for the Practitioner by Frank P. Saladis, PMP

Praise for THIS ISWT EXCEL, IT'S MAGIC!

"I was astonished reading this book. The tips are excellent and well worth knowing-you won't want to miss even one of them! Although Eng lish is my second language, I got through it effortlessly. Bob Umlas is a magi cian-thanks for sharing this immense knowledge with us!'' - Monika Weber, Excel MVP Project Manager, Corporate IT AMCOR Rentsch Ltd. "I carry This isn't Excel, It's Magic! in my car and read tips while stopped at traffic lights. The book is already full of post-it flags fo r all the tips I didn't know. I think I've flagged all but the title page." - Melanie Beck, technical training specialist, Grant County Public Utility District "I thought I knew a lot about Excel; but I now realize how little I did know. There are so many great head spinning, jaw dropping tips and tricks. It makes unlocking the power of Excel an amazing experience. A must have for (Excel) users everywhere. Mr. Umlas (is) a true magician." -Aiesa Lambert, corporate accountant, CM Foods "I love this book and highly recommend it to all Excel users. Whether you are looking for some simple techniques to make you more productive in Excel, or if you are ready fo r some sophisticated how-to's, you will learn valuable information from This isn't Excel, it's Magic!" - Bob Schuster, partner of ExceiMagic and RWS Information Systems is) a simple, but amazingly useful book.Very clever! Bob has produced a compact, but brilliantly useful book of shortcuts, for mula secrets and tricks. One thing I loved about this book is it tackles 'real world' problems.This is not a book for Excel beginners. However, if you use Excel professionally o r for fun then you absolutely should consider grabbing a copy of this highly useful and informative book. It will save you many hours of work and delight you at the same time." - Darryl Collins, Excei!VBA developer "(This isn't Excel, it's Magic/

"Nobody willing to use Excel efficiently should be without a copy." - Miguel Fischman, systems analyst programmer "What a handy little book chock fill of hints, tips and techniques. I have been studying Excel for longer than I care to admit yet I consider my knowledge of the subject miniscule compared to the author's breadth of skill." - Chris Curtis, desktop p ublisher, bellaonline.com "Bob Umlas is a Microsoft® MostValuable Professional (MYP) who put his nearly 20 years of Excel experience into a book called: This isn't Excel, it's Magic! Bob shares many excellent tips for the experienced Excel user that will save time and energy when working with spreadsheets. A beginner may be a little daunted by some tricks, but will appreciate them once they get to know Excel. The book is well illustrated and narrated." - Rick Ca.stellini, host of Castellini on Computers "One of the biggest perks of attending the MYP conference in Redmond was the opportunity to chat with folks from all over the world. I had the unique opportunity to chat with Bob Umlas, an expert in Microsoft® Excel. If you have questions about how to use Microsoft® Excel then Bob probably has the answers." - David Ciccone, founder, mobilitytoday.com "The first time I read it was in the middle of the night during a blackout. I had to use a flashlight under the covers.Thanks again for your help." - Debi Morrison, B2B Support Coordinator, "the Excel Queen" "Despite the large selection of accounting software programs for businesses, Microsoft® Excel is the tried-and-true spreadsheet option for most, espe cially for small businesses.A new book by Bob Umlas,a Microsoft® Excel MYP and author of 300 articles on Excel, shares tips on how to get the most out of the software." -Accounting Smartpros.com "You just have to buy this book!" -Jack lmsdahl, co-host, On Computers radio show with AlaskaJoe

FEATURES

1 . Using Advanced Filter to another sheet 2. Select all O's: or Select all specific keyword 3. Cut/Copy, Insert Paste by dragging borders! 4. Use Fill-handle to insert or delete rows 5. Shift/find finds backwards 6. Combining cells with Paste Special/Skip Blanks 7. Right-click drag the Fill Handle for several options 8. Defining names for large non-contiguous ranges 9.What is that :1, :2 ... I see in the work book title? 10. Double-clicking tools 1 1 .AutoSum Tool features 12. Change dates like 20050923 to one Excel can "understand" 1 3.AIIow only unique entries in a column 14. Right-mouse/drag the border of a range for several new options 15. Shift/click inner VCR controls 16. Using Pictographs 1 7.Wish there were a "No to All" when closing many files? 18.Quick return from FS(Goto) 19. Split Bars 20.Window/Arrange Tiled (except this one!) 21. Adjusting Row Heights for Merged cells 22. More Scrolling 23. Drag/Scrolling 24. Using the Justify command 25. Understanding the order ofWindow/Arrange 26. Creating names 27. Long Names 28. Long Formulas

14 16 21 22 23 25 27 29 31 33 34 36 38 40 41 42 46 47 48 51 53 54 56 57 60 62 64 65

FORMATTING

29. Formatting comments 30. Format #VALUE! or any errors away 3 1 . Using (h] format for hours>=24 32.Advanced number formatting 33. Formatting text 34. Using Fills 35. Indenting or distributing text

66 70 72 73 74 75 76

FORMULAS

36. Easily create custom list of a-z,A-Z 37. Inner Series 38. Using Apply Names 10

This isn't Excel, it's Mogicl

78 80 81

83 86 90 92 95 96 97 98 100 101 102 103 104 105 107 108 110 1 11 113 115 1 18 119 124 125 128 132 135 138 140 141 143

39. Selecting a random sample of data 40. How to change row height when a key field changes 4 1 . Define "global/local" name 42. XLM-Names (not XML) 43. Getting number of unique values 44. Named formulas are array-entered 45. Getting End of Month 46. How many Wednesdays between 1/1/05 and now? 47. Looking up 2 (or more) values 48. Named ranges which define themselves 49. Using REPT for visual effects SO. Using the Space as a n operator 51. Extracting the last pan of a string 52. Using SUM PRODUCT 53. Using the TEXT function 54. Use wildcards in MATCH function 55. See all characters in font set 56. Double-Spacing data 57. Fill "=A 1 " right, have reference become =A2, =A3, 58. Fill "=C3" down, have reference become =D3, =E3, etc. 59. Using Define Names & scrolling 60. Show a picture by typing its name? 61. Protecting ranges from insertion of rows/columns 62. NumberingQuestions tip 63. Mailing Label tricks - 1 64. Mailing Label tricks - 2 65. Bulk formula change 66. Fun with relatively defined names 67. Using notes inside formulas via the N-function 68. New Table features 69. New Excel Functions KEYBOARD SHORTCUTS

70. Ctri/Shift/-,1 ,2,3,4,5,6 for quick formatting 71. Borders from keyboard 72. Inserting Date & Ti me shortcuts 73. Click the AutoSum button from the keyboard 74. Bringing the selection into view 75. Ctrl/[ more powerful than its "equivalent" GoTo Precedents 76. Show Corners of Selection Easily 77. Anchoringthe active cell 78. Finding after the Find dialog is closed 79. A few miscellaneous Shortcuts This isn't Excel, it's

145 146 147 148 149 150 151 152 153 154 Magid

11

MISCELLANEOUS

80. Sorting columns 81. Making exact copies of ranges with relative or mixed references 82. Filling Holes 83.Aiigning objects 84. Leaving cursor in the cell after pressing enter. 85. Shortcut 3D formula 86. Pulling cells together 87.An oddity with the Camera Tool 88.Quicker data entry 89. Miscellaneous - Changing cell reference 90. More miscellaneous Info

155 157 161 163 165 166 167 169 171 172 173

PRINTING

91. Copy Page Setups to other sheets 92. Copy Page Setups across workbooks 93. Print many worksheets at once 94. Print remote areas on same page 95. Reset all page breaks caution

175 178 179 180 183

VBA

96. Getting to your Visual Basic routine by FS (go to) from the worksheet 97. Can't find the old Excel 2003 commands! Use this feature! 98. Aligning Userform objects 99. Put pictures in Userforms from the clipboard 100. Make your own tools for forms 101. Using invisible objects 102.1nitialize Listboxes, Comboboxes with Months via GetCustomListContents 103. Using MicrosoftVisual Basic bookmarks 104.VBAArray 105. Ensuring users don't open your workbook with the shift key down to prevent your macros from kicking in! 106. Bulk Removal of leading apostrophe 107. Multi-select offset 108.VBA Boolean test 109.VBA Shortcut to fill ranges 1 1 0. Some VBA Shortcuts 1 1 1.A few VBA Keyboard Shortcuts Index

12

This isn't Excel, it's Magic/

184 185 186 188 189 193 194 195 196 197 200 201 203 204 206 208 209

I actually learned Microsoft Excel on the Macintosh in 1 986 or so, using version 0.99! When the real version (1.001) shipped, I read the manual (yes, Excel came with manuals then) from cover to cover. Six times. Especially in order to learn Data Tables. I just didn't get it. (Okay, so I'm a geek. Actually, I once heard that you're a geek if you double-click the TV remote)! Around 1 993 I received the nickname "Excel Trickster" from one Will Tompkins, a real Excel Guru. I got that because during a seminar he was hosting in Washington, I believe, for what he called the Excel SWAT team, he was showing his then famous Tompkins methodology - a macro structure using Excel4 style macros (beforeVBA existed in Excel) which was quite sophisticated. It may have been Excel 3, I really don't remember! In any case, he showed a line of code which used a range name, a label, and he wanted to show us the code at that label. So he used Edit/Goto, and we saw literally many hundreds of defined names which he had to labori ously scroll through to get to the one he was looking for. After he had done this about 4-5 times, I raised my hand and said, "You know, you can get to that label directly.Just press Ctrl/[." He tried it and was flabbergasted! So he tried it again. Then he remarked chat I just saved him about 2 hours every day scrolling through his defined names! When I also told him you could return to the location you were just at by using Ctrl/], he called me the Excel Trickster, and that nickname has stuck with me ever since. So thank you,Will. I've been the contributing editor to Excellence and The Expert maga zines, monthly 1 6-page publications for about 8 years (they no longer publish), and I've led a few sessions at Microsoft's Tech-Ed on tips & tricks and on array formulas. I've also been an Excel MVP for Microsoft since chat program's inception -this is an acknowledgement for people who contrib ute answers on the online newsgroups, and I currently lead an online Master Class in Excel which is twelve 3-hour sessions (details can be found at http:// www.iil.com and clicking on "Live Elearning"). So, over the years, I've gathered many tricks and I decided to put them together in this book.And in no particular order! I hope you enjoy them!

This isn't Excel, it's Magic!

•

13

1. Using Advanced Filter to another sheet Advanced filter can only filter to the active sheet. The active sheet can pull information from another sheet, but it cannot "push" information to another sheet.You'd get this message: "

"

l8J

---

Microsoft Excel

�

You can only copy filtered data to the active sheet.

But by using the Advanced Filter on the Data tab: [Excel 2003: Data iFilteriAdvanced Filter]

rrp

Home

·rnaI

�

Insert

Page Layout

(llil Connections �Properties

Refresh All•

�-:J Ed1t lmks

Formulas

lillJ 7

X�

Filter

Sort

Connections

r

Data

l

� Cl.:ar

� �Advanced� '{<> Reapply

Sort & Filter

you can pull information to the active sheet:

1

2 3 4 5 6 7 8 9 10 11 12 13 14

A

I

B

c

D

��

-

1\dvance
Action

0 Eilter the list, in-pla.ce 0 CQPY to another location

b_ist range:

Criteria range: Copy to:

I=Range2

ISheet l !$A$ll

� Unique tecords only


OK

]I

Revit

t�I 1�1

Cancel

]

E

Range2 is a range defined on another sheet.The sheet we're filtering to is currently empty. After clicking OK (notice the "Unique records only" is checked, above): A

!_]Jared

I

B

2 1Fred 3 Stefanie

Judy

4 Bob 5

6 Alice 7 Jared

-

8

q

Kate

This isn't Excel, it's

Magid

15

2. Select all O's or Select all specific keyword Suppose you had a worksheet which looked something like the following, and you wanted to select all the cells containing the word "amounts":

u

A

2

B

Description

_

____

m35-c summary input --

-

3

m35-c gl �coun�-

5

m35-c amounts

7

m35-d summary input

4

m35-c gl description

m35-d description

6

8

m35-d g_l acount#

9

10

11 12

13 --

14

15

16 17

i81

19 20

'

21 22

23

24

m35_.:
_

<:... ::..:. .:.:._: .:..._

m80 ye balance

[ I [ !_ I I I I I

-·

m80 gl acount#

lm80 gl description

I

•

_

m80 amounts paid after

m8�g� count� ---• _ --m81gl description

m81 ye balance

!

m81 amounts paid after

m 82 g�count#-··---•_ m82 gl description I

m82 ye balance

•

m82 amounts paid after m83 gl �count#

-·---: m83 gl description

I

·-

You can't do it with FS/(Goto) Special and use features there, but you can use Excel 2007's Find All, with a twist...

16


If you use Home tab, Find and Select from the Editing section, then Find (or, simply press Ctri/F): [Excel 2003:Edit!Find) I:

..

[il· a·

.. ,

..

=I �

I

= e�e �

-

..

-V �

Sort & Fihd F il ter y Select ..

&

Find...

,Beplace..

.

Go To ...

Go To Special... Form ulas

-

Comments

Conditional Formatting Constants

Data Validation

�

Select Objects

�I

Selection Pane...

type in "amounts" (without the quotes), then click the Find All button you'll see: ,

.

'

Fmd and Replac e

Fin!! ] R•l!l��
Book

S heet

hydr adienton;;Ja�zermap2 .xl$

hydradlentorganirermap2.xis hydradiefltorganirermap2.xis hydradiemorQanirermap2.xls hydradlentorganizermap2.xis

hydradJentorganirermap2.xis hydradientorganizermap2.xis hydr•diemorQ•niu!rm•p2.xls

hydradiemorganirermap2.xls hydradiefltorganirermap2.xis

hydr•diemorQaniwmap2.xis hydradientorganizermap2.xk .... •

.... A:.-.,... �...........v ....--. ... .....,

j'iicell(s) found

......

clior.t org•nl•.,. to ewp client organizer to ewp client organizer to ewp eli� organizer to ewp dient organizer to ewp ctiert organizer to ewp diest organizer to ewp cliett organiw to ewp client organizer to ewp client organizer to ewp client organizer to ewp cli ent organizer to ewp

I I Eind Next I $9$10 $8$14 $6$18 $6$22 $8$26 $8$30 $6$34 $6$38 $9$42 $9$46 $9$50 $9$51

.f.O.t-C'O

Close

m3S- d •mounts

mBO amounts paid after

m81 amounts paid after m82 amounts paid after

mB3 amounts paid after m81 amounts paid after

m8S amounts paid after

mB6 •mounts paid •fter m87 amounts paid after m88 amounts paid after

m89 •mounts paid after

� �� .,..'


17

Here's the twist: instead of selecting the items in the found list, you can use ctri!A (or shift click the last item in order to select from the already selected item through the item you're now selecting, or even ctrllclick to select specific items), and all items selected in the list will also be selected in the worksheet: "

c

O..:.C.iptiel'l mlS.cw....•'Y . ft�,ut MlS..c:tl �C.YM. nOS.c ,, 6Kaf,tio-n lft3:S-cIMOUntl nOS·-4111 c1Htf1p* nOS-41 �Uttt.nUI)' ln�n m3S• n1JS4

D

F

G

H

J

"" ,_, �� --�--------------------------3,·

IC'IOU..

gl glltW l ip•ion

m)S,4 UIIOV1'111

n.O sl Ue ll ntl Jl dHUiplion n1180 �· h•J•not m80 lm ountt ptld'"" ni81 tl �e.unt.f nt81 tl dtsO"fpllon n180

nl81 ye b t .s a.n e. n.S t emountfpeldel\er nt82 gl �eount *

n8 l 2 gl dowlptlo*' nl82 yo h.tlinet ru82 •mounts paid tftet m8lgl•eountk m83 gl dewlrtlon m83 ye b 6 1 anc t nl8) trnountt poht after n.&• gl•eount.f _,, -· ..............

Now, when you close the dialog box, the cells will still be selected! Now, suppose your worksheet looks something like the following, and you want to change all the yellow cells to be bold (and still yellow): A 1 Form Name FORMS471

2

3 FORM5471

4 FORMS471 s

FORMS471

6 FORMS471 7 FORMS471

8

FORM547l

9 FORMS471 10 FORMS471 11 FORMS471 12 FORM5471 13 FORMS471 14 FORMS471

15 FORM5471

16 FORMS471

1

17 FORM 5471 18 FORM5471 19 FORM5471 20 FORMS471 21 FORMS471

18


Selecting by text now won't do, but there's another feature of Excel's Find command: Find by Format. Note the Format button at the top, (you may need to click the Options button):

Fin!;!

Find what:

Witbin:

R.egjace

I�:-I

;== By Rows :::;;

�ook in:

Set

I '[

f;�.rm.a_t,.,

.

Formulas

When you click this, you'll see:

�im�.rm!!! fjiU«,;��:JI Category:

G eneral

Number

Currency

II

-' "

Accounting

FOnl

[

-:��

·

·

Alignment

)

-:

0 �1ati::h £ase 0 Match entire cell contents

Sheet

Search:

� I No Format

� �

� �

-----

I Border l !

Fill

l

IProtection

Sample

G eneral form at cells h ave no s pecifte number form at.

oate Time

l

Percent�ge Fractton

Scient(fic Text

Special Custom

�

I

Choos e Format From

Cell .

..

I

[

OK

-···

I

'II I

.

Clear Cancel

l

I


•

19

I n this dialog, you can enter all the properties of the formatting you're go ing to want to find, but there's also an easier way: click the bottom button, "Choose Format From Cell. . . " When you do this, you'll see a special cursor which looks like the regular cell-selection cursor but with an eye-dropper next to it! When you click on the cell containing the format you want to find, you'll then see a preview of the formatting in the dialog box, and you can combine this with text to find as well! Find and Replace Find

J Re12.lace j

FiQd what<

Within: Searcli:

:::l L:::f.:gioi�C::.::J P_r_e_v_ie_w_"' d

c Ef b

---=

=---

jsheet

J.sy Rows

Look i(i: ]Formulas

•

r rvlatch �ase

3 2J . a

r

!VlatGh entire cell·contents '

Options<:< F[nd All

E.ind Next

J

Close

Here's the result: A

Form Name 2 FORM5471 l

FORM5471

FORM5471 FORM5471 FORM5471 FORM5471

B

c

0

H

��

Fil!! Fi�d what:

FORM5471

IFORM5471

FORM5471

19 FORM5471

21 FORM5471 20

G

find and Replace

'-----'-"v.ll I

v

:====='=:; v _l

vi

17 18

F

-

FORM5471

16

E


Preview�

j[

Format ..

·J

0 �tch £ase

0 Match entire cell C!lntents

[Opljons I «

Fr>d AU

I !1

e;nd Next

Close

3. Cut/Copy, Insert Paste by dragging borders! In order for this tip to work, you need to have the setting checked for Enable fill handle and cell drag-and-drop turned on (which is the default setting anyway). Office button, Excel Options, Advanced section, as shown here: [Excel 2003:Toolsl0ptions1Edit!AIIow cell drog and drop] -----

------- ------Ex cel

.--

Oplio n s

------,

-

I][J

Popular

Formulas

0 Afttf prenino Enter. move selection OJrectlcm:

e� _j

Customize

Trust Center Resource s

IDown vj

0 Autom1tle�lly i nsert• .stedmal point

Advanc

Add · lns

options for working with Excel.

Editino options

Proofing

'

Advanced

.,0

/.0

It]

0

r1

E.nob lt fill handle a n d cell grag.and-drop

0 alert before overwriting cells Allow 1dlting directly in cells

Enabfe autom atic percent entry

Extend data tange fo1mats and formu!as

0 Enable &utoComplete for cell values 0 60om on roll wtth IntelliMo use

0 Alert the user when a potentially time

consuming operation ocrun

When this nymbtr of cells (in thounnds) is affeded:

133554 :I

To cut and paste a range, select it and drag the border (the cursor will change to a cursor with a 4-headed arrow). To cut and insert paste a range, drag a range by its border and the shift key held down.You will see an indication of where the range will be inserted:

1 2 3

A

B

L 1/1/2003 c

36

45

40

50 2/1/2003

44

4

48

5

52

6

56

•

55 3/1/2003 60

�

4/1/2003t ' 5/1/2003 6/1/2003

The grey line indicates where A1 :C2 will be cut and insert-pasted.The intel lisense shows that the new location will be at AS:C6. If you hold the ctrl key down with either of the two action s above, the cut becomes a copy. This isn't Excel, it's Magic!

21

4. Use Fill-handle to insert or delete rows Did you know th at holding the shift down while using the fill handle will shift cells? Well, now you do! Let's take a look.

1 . Hold shift key while dragging Fill Ha ndle.You will see a new shape to the fill handle, a split vertical 2-headed arrow.

2. If drag down you will insert rows 3. If drag up you will delete rows 4. If drag right you will insert columns 5. If drag left you will delete columns

22


5. Shift/find finds backwards This is great for finding the last department in a list, for example: A

1

Department

2

Acctg

3

Acctg

5

Acctg

4 Acctg 6 Acctg

7

Acctg

8 Acctg

9

Acctg

'10 Acctg ..

•

,11 Acctg

-12 Acctg

-

.

:0:13> Acctg -

·14 Acctg

lS' Acctg

B

.. v��..,

0_J. . __ I _:e::...jl... ....J F .--...: _i_: I c --L---=:. � __ ,_ _

=

_ _•niOI'

_

� -2!�!1,!!-,�P.,� £!51.. �

1ReQiace1 A c-t� gj FiQdwhat: .-lFins[

11

---� .=

Previe·:., ..

I

[

C::}} 0 ch !sheet MatcH tire_cell cQ.ntents 0 _ � : � , "-i � ;, � ==== S,earch: := �Rows W _ r.;: !,ook in: (Formulas � !;,ase

i : Witb.n

l

F(nd All

]1

��Next

_

j

-

16 Acctg

How can you tell where the last Acctg department is? Use Find & Select from

the Home Tab (or ctri/F), and before clicking Find Next, hold the shift key, and you're there in one click!

This isn't Excel, it's Magid

23

A

B

Fing

c

E

ReQiace

Fi!ld what:

�... IAc_. c_tgj�------------

271

272

F[nd All

Department . . .1:-- 1""\-.-.

........... _ ._...

1-

--

J ( __Eind N�xt J

---- ·-

I

r

You can use a wildcard to search for anything backwards, so starting from cell A1 , you can find the last row or column!

� £!!t �.!!!J.d Rep�£!! .. ,�,.,�� F"''! L ReQ!occ} From cell AI. finding an� backwards and

,-

iQ<:I F v.t.at:

wt!!On:

�rch:

�ooi
24

selecting "By Rows'' will select the lost row

•

(Excel's ctrVend is often too far downl)

-

Then. reselecting cell AI and changing the dropdown to "by columns" will find the lost

Shett

.

��_J

used column!

I

Ffld AI


I I fi>d -

Closej

6. Combining cells with Paste Special/Skip Blanks You've seen the Skip Blanks feature in the Paste Special dialog, but do you know how to use it? It refers to the range being copied-blank cells in the source won't erase cells in the receiving range. Exam ple: A1 :A7 has a,c,e,g and B1 :B7 has b,d,f,h: Copy B1 :B7, select A2, Right/Click, Paste Special

1

A

a

:b

�

3 c

-

J'

__ _

.

2

�

c

I

'

D

I

'

Cut

Lt!l f:;opy

4

5

1_ __2l

e

I

6

7 g

�� faste

I

Paste �pecial...

Insert Copied C_tlls...

8

Delete...

9

Clear Contents

. ,..

with Skip blanks: A

....;. .- .;... 1 .... a :b2

3 4

L.. c

---< 7 g

� 8 9

10

11

12 13

14 15

16

17

-

18

1,!d

___

5 e 6

:'

•

'

:t

�--:

B

-

:

:

' '

• '

....... ..................

c

0

F

E

G

H

------

Paste Special Paste

0!!1 OE«.-.Jas

0:talues 0Fetmats 0 !:_omments 0 ValldatiOQ

�I:RJ

0 AI u.ing Sou"ce tl!eme 0 AIe11cept borders 0Colo.mn !f!idhs 0 Fotrr..Jias and r.Jmber formots 0 Vaii!E's and nl.lllber formats

Operation

0NQne 0P4d 0 S.Ubtract

0Mu�iply 0Divide 0 Transpos!l_

[

OK

][

Cancel


25

Here's the resu It:

i 1'!j.. t_:!\;::..-.. . k...____(j .c�� •

' ' -· '

2 b

3 c

d

4 d 5 e 6 f

f

7 g 8 h

h

9_

1('\

26


--

__

---

•

--

--

' ' · ' t , '

--

__

:

'

--

.........J

__ _

7. Right-click drag the Fill Handle for several options If you use the fill handle and drag with the right-mouse button down, you

will be presented with a dialog containing several options when you let go:

±I

A

36

45

40

50 2/1/2003

1/1/2003

3

I Fill

-

4 5 6 7 s 9 10 n 12 13

E

.

- ---- -

tI

F

Cop� Cells

·.

-

. . ..

�eries

Fill formatting Only

I

Fiii1Nith.Qut Formatting Fill Days

Fill �eekdays Fill Months

-

•

I

1

I I

I

I

Fill Years .blnear rrend !;iro.,.Jth Treno S�ries...


27

What the original selection contains will determine what options are available in the dialog:

1 2 3 4

A

B

36 40

c 45 1/1/2003 so 2/1/2003

D

�opy Cells Fill �eries

5

6

Fill formatting Only

7 8

Fill WithQut Formatting

F111 0,; ·s

9 10 11

Fill W.:ekday: F1ll Months F111 Years

12

13 14

1inear Trend

Growth Trend

15 --

S�ries...

Notice that in the first example, dates were selected, so the middle sec tion of the dialog (filling dates) is available, but the second example has the middle section disabled because dates weren't selected. The Fill Weekdays option with dates selected can be quite useful:

1

2

A

36 40

B

45

50

I 1/6/2005 c

1/7/2005

1/10/2005 4

1/11/2005

5

1/12/2005

6

1/13/2005

7

1/14/ 2005

8

9

1/17/ 2005

1/18/2005

Cell C1 's fill handle was dragged down to cell C9 with the right mouse used, and Fill Weekdays was chosen. Notice 1/8,1/9, 1/1S,and 1/16 are missing, because they're weekends.

28


8. Defining names for large non-contiguous ranges If you need to name a multi-area range, you're limited to about 255 charac ters in the definition. This can cause problems as seen here. In this worksheet we want to give a name to all the cells containing values so that it's easy to clear them or add them up, or refer to them in some way.

1 2 3 4 5

6 7

8 9 10

11 12 13 14 15

A

Data:

8

2

D

c

Data:

,....- •

Data:

12 14 Data:

F

33

G

Data:

•

•

Data: Data:

E

21 Data:

25 Data:

Data:

Data: 50 Data:

Data:

88

>�] This tab has a

27

89

63 Data: 70

Data:

30

H

8

•

1

105

Also notice the tab's name is quite long. Okay, we want to name the cells, so we select them first (it's easiest using F5, Special, and making this selection): ------

�

Go To Special

Select 0 �orrrnents 0cqnstants OEonnulas 01'1\Lml>ers ·1 o--·· fig�, 0Log_icals 0�rrors 0 Blan!!,s 0 Current region 0Current �ray 0 Ot!jects

0 Row differences 0 Colum differences 0 erecedents 0 Dependents C•ecl nl 'lle-�ls 0 lait cell 0 Visible cells onl:,: 0 Condi{ional formats 0 Data yahdation AU

Same

II OK J I

Cancel

J This isn't Excel, it's Magic!

29

That is, we're selecting constants which are num bers (not text). Then we use Define Name from the Formulas tab. [Excel 2003:/nsertiNameiDe(tne]. But you'll find that if the resulting name was too long. then Excel won't even bring up the dialog! You can name these cells most easily, though, by using VBA! Press Alt/F1 1 . getting you to the VBE (Visual Basic Environment), then Ctri/G (the immediate pan e) then type this exactly, and press enter: Selection.Name = "MyData" Then Alt!Q will bring you back to Excel. Now the name MyData will accu rately refer to the desired cells. ,

30


9. What is that : 1, :2 ... I see in the workbook title? The New Window button from the Window section of the View tab creates another "view" of the same workbook: [Excel 2003:WindowlNew Window] Developer

View

Add-Ins

q [] � Zoom to Zoom 100%

Zoom

� New WindowI S

9 Arrange All Selection � Freeze Panes •

.JJ

.d!

""""

-··-··

L1 3!1

El EQ

89

Save Switch Workspace Windows •

Window

You can use it to: View separate sheets of the same workbook at the same time: •

Al

BooloS:l

A 1 Cool!

•

f,. =Sheetl!Al

B

C_l

2

0

® 800�'6:2

B_ -1.::. E_.!_II'-+--= A-.J-� L__:::._ C _!_ __;_ D _:: Cool! 1-=---p;: ::::.:.-.

3

3

4

4

5

5

6

:

6

9

9

1 0

ul " •

• >t1

,-� = """"::::-:" """'"--,; 0

Sheetl/ Sheet2""' She etJ

:1

1 0

i Jl 1 '-=� "'-';; · ;;; ·� ..,. ; ,.e, � LJ � sn� e� e u = � ; � � s h !!§ ee � t � 2� Lj = stl � !! !j � !J !:=: :=


31

• View the

same sheet but remote areas simultaneously:

Al

fi< Cool!

•

@ Book6:1 A I 1 cool! I 2

!

c

B

•

D

99998

u

�

� 9.: 99 :: 9:..: :

3

100000

4

v Cool!

5 6 7 8 9

• View

formulas and values simultaneously: Al

@ Book6:1

'

A

1 !cool! 2 -

-

...

I I •

( .,

B

/x =Sheetl!Al - 1::1 X Book6:2 t;E A D c 1 1 lrr1 =Sheetl!Al

3 4

----1 5 6

.....

= =

'--

3 4 5 6

7

____,

8

I � 4 � �mSheet2/ ShelT rH..,.J

2

-

7

9

I

8

... 9

1tdl .-

I� �. � ��;3 Sheeq.Lsheet3

To rejoin the windows, close the window by the "x" in the upper right, not by Office button/Close, [Excel 2003:FileiC/ose] because the separate windows would save and reopen that way.You can use New Window many times and get as many windows as you wish of the workbook. Think of it as if you were looking out your physical window and seeing parked cars. Now move to another window and look at the same cars. You have a slightly different perspective of the same object.That's kind of what's going on here.

32


10. Double-clicking tools Double-click the format painter keeps it "alive" to click on several cells without having to click it again. You can no longer double-click Shape tools - But if you right-click a tool, you'll see an option to "Lock Drawing Mode", then you can draw several of the same shape without revisiting the tool [Excel 2003:Doublec/ick the shape from the drawing tao/bar] (From the Shape button on the Insert tab):

Insert \ Page layout

Formulas

... Data

I � �� t, b� Clip Art

s.hapes sma�rt •

...

,

Review

�0.1�:�rifi. ��._!,!,• •

.

Ba

'

.s

l

""- '\, '\. L_ L. i... ( 4 � �''- G � Rectangles_ _________ OODOQQQQQ

Basic Sha.!:..pes=----

� 0 6 � 0Q O G Q 0 0 ® ® 6C> O D IF CS � OO I3 Bl D @@ tru Q ©
Press the esc key to stop that feature or click the tool again!


33

1 1. AutoSum Tool features Suppose you have a block of cells like in this illustration, containing numbers which you want to enter the totals both on the right and below; that is, in E1 :E7 and A8:E8.You'd probably do it something like this: A

1

8

c

•w ------. -

65

D

G

30

2

84

85

87

3

14

69

78

97

42

71

6

4

88

5

5

32

71

62

6

57

47

65

2

7

50

5

63

92

8

Click in E1 and click the Sum tool (on the Editing section of the Home tab) [Excel 2003:Sum too/bar button on Standard Too/bar]

� u � [� Q.

y

Sort & Find &

Filter y Select

y

Editing

1 . and press enter, or double-click the Sum tool. 2. Click the fill handle in E1 and drag to E7, or double click the fill handle in E1. 3. Select cell AS, click the sum tool, enter the formula, select the fill handle, drag to E8. Hold on to your hats. It can be done in one click: Make this selection as shown in the illustration:

i

A 1

65

c 54

D

E

85

30

46

87

74

14

69

78

97

4

88

42

71

6

5

5

2 3

6 7

84

57

so

8 34

B


32

71

62

5

63

92

47

'

65

2

'

•

Select an extra blank column and row. Click AutoSum Tool once and you're done! I know . . . you have to try it! Next, if you have a multi-cell selection, as in this illustration: 1

A

B

60

54

2 3 4 5 6 7 8

60

66 28

58 57

16 14 91

9 34 70 62

29 16

10

70

7

11 12 13 14

66

68

76

13

46 32

81

9

95

15 16

77

70

18

I

A single click of the sum tool will put the correct totals in!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

16 17

A

B

60

54

J

60

66 28

58 57 9

16 14

238

46

91 29 16 320 7 68 77 13 81

32

70

34 70 62 70 66 95

76

551

.

I

18

41

33


35

12. Change dates like 20050923 to one Excel can "understand" Suppose you are looking at a worksheet which contains dates which can't be formatted as "real" dates because of their structure:

h l l

A

1

20050923

3

20051107

2

4

20050930

Select the date(s), and use Data tab, Text-To-Columns, [Excel 2003:Data!Text to Columns] Data V..

l Review

Clear

'"{v Reapply

.�Advanced Iter

View

��

Developer

Add-Ins

1�1 Data Valida�ion � Ef:c Consolidate Text to Remove . s"' lysi Ana at-If Wh � tes lica , Dup !!ms ,Sol� ffi-:.I

"'

Data Toots

which brings up the Text-to-Columns wizard:

C!J.fll(ert Text .to ColiJmns Wizard. - Step1

.of 3.

The Text Wizard has determined that yoor data is Delimited. If this is correct, choose Next, or choose the data type that best deS
0Jii'J

Original data type Choose the file type that best describes your data:

0[ § iiiT,i��1

0 Fixed \:)!idth

-Characters such as commas or tabs separate each field. -

Fields are aligned in columns with spaces between each field.

Preview of selected data:

tl-�0050923 � ��0051107 ri � 0050930

[ 36


Cancel

II

< B<;d<.

ll

f!!ext >

J! [

finish

I

Even though the dates are fixed width, in this case you can simply click "Next" twice. In step 3 of wizard Select Date,YMD: .Convert Text to Columns Wizard · Step 3 of 3

��

Tt-is screen lets y(ltJ select each cobnn and set the Data Format.

data fOI'mat O�al Orext

Cobnn

'Genetil converts runenc vakles to runbers, date v.....,. to dates, and al remaining vakles to text.

1Advanced... 1

O£stnob01'1: MYO DVM Data erev'L.!YD-"'M"'-----1

t�I

Cancel

I[

<

!l_ack

Next >

If you click Finish now, the result will replace the dates.You can select an other destination cell: 3

@�

-

-

Column dMa format

0 l:ieneral

Orext 0Qate: v 0 Oo not �t cobm

�

Here's the result: A

B

I

20050923 9/23/2005' 2 20050930 9/30/2005· 3 20051107 11/7/2005' 1

.

4

c;

This isn't Excel, it's Magic! 37

13. Allow only unique entries in a column Use Data Validation from the Data tab: [Excel 2003:DatajValidation]

�� ..J (d �� . ""'

•

"

�

Hom�

sll

-:J•

tnstn

Gtl e
All·

Formul.ls

� Coontchons �

�

clltlrnts

Connections 81

A

. \.

8

�I

1m ')[ A

�I Son

I

fllttt

Data \

Ot��

V R t\ 1 t\

.:.:..:.:.; · � ; ,;,;;;,;.� )tl !'!i_�

• ,. 'ttpp.,

'!.1Ac!Yan
Sort & F lttt t

I• Cln contain only unique values

V 1 tv 1

OtvtlOPtt

Add-Ins

�Data Vall�atoon � : tr [to Consoli�••• TtJ
•

•

Oata Tools

D

c

8ool:6 • Mlcrosort Excel

F

G

H

with a custom formula like =CO UNTIF(8:8,8 1 )= 1 : 81

/x

1

2

3

4

Can cont a in

values

E

F

G

Data Validation --- --�� setltn!l>

InPut Me ssaQe

V.tlid.!Jtion ateria

lL:= Cust = ""' =-

Error

Al ert

-' "1 ��·!llrt -

-

1-<.B...-u( :B,BJ)=JI

17

Remember the COUNTIF function-its syntax is =COUNTIF(Range,criter ia).With all the cells in column 8 selected, this formula returns TRUE only if the COUNTIF for all of column 8 (the range) contains only 1 value of"Can contain only unique values." If you were to select 82 at this point and reex· amine the data validation formula, you'd see =COUNTIF(8:8,82)=1. Let's enter 1 in cell 87 and see what happens: 38


0

This value has a!re.ady been entered I

· : ·''R �t; ;···j "

...

[

Cancel

I [

tiel�

I

The message appears because the data validation for cell B7 is =COUNTIF(B:B,B7), which returns 2.The message comes from the error alert tab of the Data Validation dialog:

J�)�

. -.-� ·-- -

P.�!.�X�!i��t.!.�;

·

Settings

··

Input flilessage

.· ..

·

·-

:··················Alert ... . ' i Error .. ....

�

; ,.............................

0 Show error alert after invalid data is entered

When user enters invalid data, show this error alert: St:!le :

!

Sl

stop

0 [

Clear All

I

T tle:

i

Error. message:

This value has already been entered!

""

-

v·'

-

I

OK

I[

Cancel

I

This isn't Excel, it's Magid 39

14. Right-mouse/drag the border of a range for several new options If you right-mouse drag & drop the border of a range to a new location, then when you let go of the right-click you're presented with a dialog containing many options: 1

I

2 3 4 5

II

6

7.

8

9

10

·:.

11

12 ·

13· 14 15 16

B

A

J

1

0 ,

36

45

40

50 2/1/2003

lI I

I

17

.E

1/1/2003

.�

1 !-

•

I

I

0

_J_; veHere

p y Here

E" ��opy

H.�rt� .. .Yall.l � S.:QJ.lly

Copy Here as tormats Only link Here

I_ -

]

Create !::!Yp erlink Here �hift Down and Copy Shift Right and Copy Shift Down and Move Shift Right and Move C�ncel

If you drag a range this way and before letting go you drag it right back to the original location. you will still be presented with the above dialog, and this way you can copy/paste special values in place with the m ouse.

40


15. Shift/click inner VCR controls I n the tab VCR-type scrolling controls, 23

24

-

25

�

26

I I I l

i l ! l -

I _

I

� Sheetl..(shee.�fj?Sheets.,.tb:/ ��1 Ready

Num Loci<

e:J-f

If you hold the shift key down while clicking one of the inner two controls, you will scroll a "page" of tabs (all the tabs that are viewable) at a time instead of one sheet at a time.


41

16. Using Pictographs Anything in the clipboard can be pasted onto a chart 3.5

3

2.5 2

1.5 1

0.5

0

1

2

3

Clicking on the coins, copy (ctrl/c), click on a series, Home/paste (or ctrl/v): 3.5 .-----3 --,-

-+-2.5 .J--.!..,L -I--� --.,.� 2 -JI.t. 1.5 +-.L.(!l...:.�A-..-1 t�-=------Lm,v----�..4--1

42


2

3

1. Seriesl

Right-dick on the series, use Format Data Series: s.s 3 2.S 2

1.5 1

0.5 0

..--

t-------.·t----···t.� � . -+-.-!!}-G: -

---�·r·

t--.-- ---�-1':(!fr.===ililbt:=:.___, +--

Q�ld�

__, -1--

�rl R

to M! t ch Styl �

ill Chang� S�rie1 Chart T� e r------l !1l} Stt•a Oata_. I

2

•..

-

4
,

Oati Lall:�H

Ad d Trtn dli n t

f o rmillt Oat-a 5-tri es •. •

•.•

Click these options: [Excel

2003:Patterns tablfi/1 Effects]

� -

J � ��1: Fill · I FJ seriK Op � � nt

0 S,elld M 0 G-od.,.tNI 0 e,t11Jrur texb$e ffl

ector Border SWAct

0 C10 ftl

Onrd�

0 Ay:IOflliiiC

Sh.'ldoW

0 lrlw:•tifnt:'];Jii'IC! 0 't.•-v cdf.Msbypoir.t

J.O fiOfti\Jt

Tcxlyrt:

�

IM c tt ! cm�

I

!Je...

W" d I I Q¢o I I Clip Att... I

0 ...1<11 0 Sl>
I I

Ir�ttCY:

l.:l ,.

D

lv.": '()le ts ISI!

' l. !0% �� !

a.. �

I

This isn't Excel, it's Magid 43

There are 3 format options: 1 . Stretch (default) 2. Stack 3. Stack and scale to If we select Stack, we see: 3.5 3

2.5

2 1.5

,

1

'

0.5

0 1

2

3

If we select Stack and scale we can enter a number into the Unit/Picture box. Again, this is something you can experiment with. ,

You also may have noticed these optio ns in this dialog: Gradient fill Picture or texture fill And others Here's the gradi ent option selected and the Preset colors dropdown: • • •

Fill

0 ttofl 0 :;oldtil 0 �-fil 0 ew:ture ot teXVe II 0 Apma11< D x•rcolnbr...,. 0 flwuta""9"""'

l'$<:

(!<<
Gridentstops

(stot:�

�or:

44


1

�;;:, ,.--.,

The new chart: �-

3.5 3

2.5 2

1.5 1

0.5 0

1

2

3

On the picture tab there was a button to select a picture-you can use any picture you have on your computer! You can experiment with the other tabs as well.


1 7. Wish there were a "No to Air' when closing many files? There is! Hold the Shift key when clicking the No button and you won't be prompted to save changes on any workbook! Microsoft: EKcel

& I

Do you want to save the changes you made to 'Book3'?

yes

H H · · · ·

......... .

............. . . . ................

46

,


Yes To All

No

I

Cancel

18. Quick return from FS(Goto) . . . The "return" address of the last range you visited via another FS/Goto is stored in the Goto dialog, so to return quickly, use the FS key and press Enter. Actually, the last 4 addresses you've gone to are kept.

This isn't Excel, it's Magid 4 7

.19. Split Bars Did you know there are split bars in Excel? They're here:

�Book2 1

I

A

-� l 5 6

1

I

3

4

La

•

-

c

1:1

X

•

�

7

8 9

10

11

,.

You can click and drag them into the workbook or you can double click either one to split the window in half. Here's what it looks like when both split bars were double clicked: @sook2

Ci:i 21 3

A

�

1

B

c

0

E

F

G

.

4

-

c

'

X

I"=

-'Iii

s 6

7

8

1 '

•

•

I

..::

I

�

9 10 11 12

.

13

1�

, .�· • •••I Sheeul sheet!r(rw.

-

'

i

.

I

.•.

J "'

�

Here's what it looks like when the intersection of the 2 split bars are dragged up and to the left (before letting go of the mouse button! ):

48


\!1 Book2

1 1::=! 2

B I

0

c

E

I

I

F

I

G

-

0

I

X

1': iii

3

4

s

� 1

.::

-

8 9 10

�•

11 12 13 14

..>.< 14 4 �

"'

Sheet!� Sheetl!,cl

ua.-1

�

ij Ll.

1-r•�

+

\--

� �

Of course, you can also do this from the Split button in the Window group of the View tab:

� New Window 13 jJ El i3 Arrange All '::: ldt EQ Save Switch (f) Freeze Panes L:] Workspace \h.lindows Window �

T

If the cursor is anywhere other than column A or row 1 , the splitting will occur to the left or above where the cursor is. lf you double-click the inter section of both split bars if they're both present, they will both disappear. All of the above you may have known. But did you know that the VBE also has split bars?

I(Generof)

iJ (<

)

Declarations

•


Here's an excerpt from a very long routine and where the split bar was used to help align the If statement with the Else statement since there was a lot of nesting of statements:

n:

�:

3 jeattA1b

Ht·.Find(Ht•f & "." lookot::xiPoJ·t).Row ,

If n : 0 Then 'no ".xxxx"

'On

A1

_J

n : Ht·.Find(Ht·f. lookot::xiWhole, Aftet·::Hr.Offset(Hr.Cells.count - 1).1

I f n : 0 Then GoTo 1

'··················end loop----------------····· Else 'yes .xxx With Sheets("Copysh
You can see that the split bar makes the align m ent code to align the statements!) easier to see!

50


(since this author indents

• 20. Window/Arrange Tiled (except this one!)

Here's the result of using View/Window/Arrange All/Tiled with 3 open work books: [Excel 2003:Window!Arrange/Tiled]

�

�

Arrange Windows Arrange

0[!��1 0 HQrizontal 0 Vertical O �ascade 0 Windows of active workbook o_ K Cancel ]

� [

_

\!)Boolol

Ll 1I � 2

A

-

I

I

•

8

c

D

1

_;] [ 1:1

X

Book3

E

c

D

E

3

4 5

:�I 9

�I u

�

A

B

c

D

E

2

1

17 18 li • � H Sheetl

But suppose you didn't want to include Book3 in the arrangement? Do you have to close it or hide it? No, you can minimize it (click the leftmost tool in the upper-right corner of the workbook: and it won't be r:� X included in the arrangement:

]


51

@sook2

A

8

J

c

I

•

4 5 6 7 8

�

-

"'

X

Book4 1 2

3 4 5 6

9 0 1 11 12 R u dy

Num lock

�

Notice Book3 is at the bottom left, not included!

52


D

E

• 2 1. Adjusting Row Heights for Merged cells

Excel has a bug when you merge cells and try to adjust the row height by double-clicking the row separator. To see this, merge cells A1 :C1, and also mark it as Word wrap. Now enter lots of text, then double-click the row border between rows 1 and 2. Nothing happens. The solution is to make some remote area as wide as A1 :C1 is, say cell AA1 , and in there, give it the formula =A1,and also mark this is wrap-text (merge cells isn't necessary because it's only one cell!). Now, when you double-click the border, the cell AA1 is being used as the one to make the adjustment, and it looks like you've got it working!


53

• 22. More Scrolling You probably know that scrolling is ''localized". If your entire worksheet goes from A1 :C1 0, the scrollbar looks like this:

� Book5 A

1 2 3 4 5 6 7

Is r9

l1o r11 1 12 ru 1 14 rlS

16 17

�

I

8

-

c

1

.

D �..:.

-- - 1

] I

l eetl Sh--

------

jz.,:.

I

!

_ _

! I

I

l_, JI

I

_i,

__

.


,

_l_·- lll!

_!,

__

__

I

1

· �-

--i---''

.......

But if your last cell was XX200000, it'd look like this:

54

X·

� Books

JJ �I

A

I

I

B.

•

4

5 6 7

� 8 9 10

rli 11

Notice the difference in the sizes of both the vertical and horizontal scroll bars between the two diagrams. It gives you a hint as to the size of the worksheet (a cute tip in itself, if you didn't know it). If you simply scroll, you're somewhat restricted to the range where there is data. This makes sense. However, if you do want to scroll faster, and outside the range of the data, simply hold the shift key down as you scroll.You'll get to row 1048576 or column XFD pretty quickly!


55

23. Drag/Scrolling Did you know that if, in a large worksheet, you drag the cursor-that is, you click in cell A20, for example, hold the mouse button down and then drag the cursor beyond the bottom of the worksheet, into the area of the tabs or over the scrollbars-the worksheet scrolls. But did you know that the farther away the cursor is from the window the faster is scrolls?

I Ay_toShapes

•

"'-

...._ _ _

If you resize the Excel application window (here you see part of my desktop) and drag totally outside the window, Excel's worksheet will scroll very fast. Did you also know that Excel slows down the scrolling (when dragging the mouse) as you approach the end of the used range and pauses momentarily when it reaches the end?

56


• 24. Using the Justify command What is this command, anyway? It's a way of reflowing text.You can get it from the Editing section of the Home tab: [Excel 2003: Edit/Fi/1/justift): I: •

A 'Ef

...

�· ""'"'

@: � Qp @ J.eft

•

�eries...

Hl:sheets ..

Justify

O r from the Vertical section of the Alignment tab:

Number

A6gnment

Text &gnment

Gnt

11 ProtectionI

FUI

Border

Orientation

tfOrizontal:

v

G e neral

nj�·

•

� ��

� � & t k _ ai _ -: ---� L o Bolt om

T e X t

__

Text

Ll·

-•

•

•

lo Suppose you have text like this...

A l B

E

F

G

H

J

K

L

M

� 1 1.( -N o"" wIS,..; th i time! forall &ood men to oomt to the aid oftheir party. Now Isthe tim� tot allgoodmen to come tothe aid ofthei rparty. 2

C

0

... and you want to fit it all from columns A:E, not A:K. Make the selection as this: ..

1

2

B

c

0

II

F

G

H

J

K

L

I

Now is tht ume for all good rt1en to come to the aleortheir pany. Now is the time forall good men to col'ne tothe aid oftheir party. o

e

M

3 4


57

purposely selecting a few extra rows, then issuing the Justify command:

rl:sheets

�tiltS...

•

Iustify

and the resu It is:

1

Now

is th time for all good men to come to the aid

i

2 of their p rty. Now is the time for all good men to

3 come to tllle aid of their party. • 4 I

just what you wanted. If you selected A1 :C2 first, you would see an excel warnmg message: A

1

2

B

c

D

E

F

G

Now is the time for all good men to come to the aicof their party. Now is the •

----

3

Microsoft Office Excel

4

�

5 6 7 8 9

�

Textwl extendbelowselectedrange.

i oitJ I Cancel J

which means all the text won't fit insideA1:C2.1f that's o k with you, then you'd see:

D Ais th

B

c

time for all good 2 men to ccme to the aid of 3 their part. . Now is the time 4 for all gocd men to come to 5 the aid of. heir party. 1 Now

58

6

I


You can also use Justify to flow the text into a longer string, rather than shorter strings. If you select A1 :DS from the above and use the Justify command, then you'd see the text refl owed to:

L�

A

=

.

I

B

II

� · � · c� --

--

�

D � � -·� · · -� -

--

1 Now isth time for all good men to

come t o t e a1 d of their party. Now is th�

2

3

time for a I good men to come to the aid

4 of theirp; rty.

-

I

s

---r-•--

•

1---

·-----

And if you selected A1 :M 3, it would reflow back to the original: A

-:�, l'r.: Now ,.,.::,"' ls" thi' 2J

e

c

o

e

LF

G

L

H

J

K

]

L

M

tlmr. forall good tnPn toCOIYU! to the tid ofthf"lr party. NQ\v li the tim�Of' f all good men to come to the aid l?f their par 1y.

.

'

]

Just to be complete, if instead of A1 :M3 being just selected you tried A1 :M2, (leaving out row 3):

A:=il: li:JF � No w Is - .

B

:Ji:

the timefor all god o

2 to• nli! t'othet aid ofthttlr

l

c

1

¢

1'1'1en to

< 111

•

h ·G '

No w I s th8

time for a ll good men to (omo lo t h e a id

b · • H:: I;: :::; il: I I

4 of theirpirty.

LJI 1;, IK

I

I I

<

h

Then the justify would leave rows 3 and 4 out of the result: A

�

8

I

c

f

0

I

E

•

F

'

G

.

H

l"'ii_ N_ o_ w_ _ is-: th-li time for all good men to come the aid of their party. Now is the 2' 3 time for all good men to come to the aid 4 of their party. ----.I to

-

-

5

probably not what you want!


59

• 25. Understanding the order of Window/Arrange

Suppose you have S workbooks open and you use View tab/Arrange All from the Window group [Excel 2003: Window/Arrange): View

Zoom

Developer

100%

Zoom to Selection

Zoom � Boola 1

A

-

B

Cl

X

2 3

6

7

Add-Ins

� New Window i·-3

� Ar ange Alii @ Freeze Panes.,.

l

r

::1 u

.

Boo�S 1

A

Book4

B

2

1

8

1_ .:!

Wir

B

2

� �<

Book!

A

_

A

B

•

etl_LsheeR ..c • >II She

Book3

L.!

B

(

The top/left window will always be the active window. But what is the order of the rest of them1 Probably seemed sort of random, didn't it1 Well, this author recently learned the "secret" of how to determine the sequence. If I wanted to see it in this order: Book1

Book2

Book4

Book3

BookS

all I need do is click the workbooks in the reverse order, from bottom right (bookS) and up (book4, then book3, then book2, then book1 ). Yes, just click! The result after clicking Arrange All:

60


� Bookl 1 A 1 2

3 4

---st

-

•

8

1:1

X

800�2

1 2

,1

6

7 8

A

Ls

1

>�1 Sheen/Sheet2

•

A

8

A I

B

2 I< • • >I

Sheetl

Sheet2

-- --��A�----=8--��

� � --

--

__

1

Book4

2

9.

So, it turns out, that Excel's order always was the LIFO (Last in, first out) sequence you activated the windows. Go figure!


61

• 26. Creating names Suppose you have a worksheet which looks like this: A +1cars

J

B

c

0

Data...

2 Toyota

613

204

3 Oldsmobile

734

4 Honda

961

5

Ford

876

6

lincoln

797

n

340

480

±JBMW

676

G

I

846

680

780

202

411

436

749

461

589

857

171

262

847

53

7 Mercury

F

E

616

254

801

410

728

405

981

919

63

922

568

933

420

720

172

326

139

120

837

77

232

Mustang

241

17

722

889

989

Volkswagen

516

534

908

917

498

Chevrolet

�Aif• Romoo

and you wanted to name each of the ranges in columns C:G.That is, you want C2:G2 to be named "Toyota", C3:G3 to be named "Oldsmobile", etc. You can't simply select A2:G12 and use the Create names feature because Toyota would be B2:G2, not C2:G2, etc. How about using an intermediate step? Select A2:A12, use the fill handle to drag to B12: !

A

1 cars 2 Toyota 3

4

Oldsmobile

Honda

5 Ford 6 lincoln

-

_!_Mercury 8

BMW

9 Chevrolet

B

j

Oat<

Toyota

Oldsmobi Honda Ford lincoln Mercury BMW

Chevrole

10 Alfa Romeo Alta Rom ...!.!_ Mustang

Mustang •.,......,

12 Volkswagen Volkswae

62


then selecting B2:G12 and using Create from Selection command in the Defined Names group of the Formulas tab (or Ctri/Shift/F3): [Excel 2003: lnsert!Name!Create] A

B

)X-; f9 Recently - Used .

Insert

Function

�

E

--

/? Usei n Formula

f9 Math & Trig ·

• � Text •

I[Er Date & Time • fiE! More Functions •

Financia l •

·

Name Managerj!lli' Create from Selection

B2

A

H Data ... 204

Toyota

613

846

680

780

202

411

436

le Oldsmobi

734

72

Honda

961

847

749

461

589

876

340

857

171

797

480

616

254

262 919

53

676 .

801

568

410

728

405

981

720 .

172

326

Ford

Lincoln Mercury

BMW

63

922

Chevrolet

933

420

AIfa Rome

139

120

837

n

241

17

889

989

516

534

722 .

917

498

Mustang

kswagen Volkswago

908

232

Create names from values in the:

Dfoilrow ! . .,......."''''''"'

0 !,eft column 0 !!ottom row 0 !iight column

''[__ o_ K -"j [

Cancel

Then simply clear B2:B12! Olds m obile .. A 1 Cars 2 Toyota

\B

.,. "
fx c Data

734

D

F

E

...

204

613

3 Oldsmobile

4

Honda

961

847

5

Ford

876

6

Lincoln

797

734

72

846

G 680

780

202

411

749

461

589

340

857

171

2.62

480

616

254

919

This isn't Excel, it's

Magic! 63

• 2 7. Long Names

If you create a very long name for a range:

�� Name: IThislsAVerylongNameAndNormallyWontDispla·l Scope: jworl
New Name

------

CQmment :

B,efers to:

I=Sheet2!$A$1

[I

OK

[�J I [ Cancel J

"'

then it doesn't display the entire thing in the name box: >-hislsAVerylongNa... I

!toooooooool•

1

2

3

4

A

f,c

•

B

�

I

c

1000000000

I

D

I

E

J

New to 2007 is the ability to widen the name box by simply dragging the little circle indicator as shown (double-clicking restores it to its default position): ThislsAVerylongNameAndNormallyWontDisplayWell A

64

1

B


c

l

0 __L _ =._ E

•

F -l- l'- I---''---

7

!10000000

fx

G

H

• 28. Long Formulas If you create a very long formula, p revious versions of Excel often made it

hard to see both the formula and the cell. Here's a screenshot from Excel 2003. Notice cell F1 is selected, but you can't see the cell: rI

.1,

•

e

f.

J � �·, .,..,.. �' , , ,.,'lbl<"'·;�,'11..•'/.'.blot',·J 1: �·· ;J{, '...,_.1"'� : · � .,)( ok � � 4'!'".·��.:.fi'.CIH - .-�"'1-:1�(, 'N11'(1'1',''·••r,"fl.:.y';.,.,..','J / f.'...,_.,.,.·;Sff!•-e:.tr,"0::.:."":.:..-·:.-..,.;·!"«," "OK�;.J.wJMr·,"ft�,N ';..,.._.,._. ''.!¥(,.,.,.', 'l 'th :v, • •. 'A.If..'\�• 'ktt...'Oct:W'" .:.,·. .?-......,_..( "''dl---4f":J ..,..., ;: .""�•·.·;.ur.'May',·...,.·.$ :,);,�(.·�.q,.nc·."Sttt�·· ·...t'k# 'Oct�. , _... ."O.(ot'llbt.f'.'Jtl'loN'f'•..,·�)'-. "N � • ' -r-•..,...tit'--�-:-.uy.-�-.�w.a¥;'()c«..ft'.":�+t .... .."': .. A; "'«.tw..t>tl"" '#loli'J'"f -er ;w .1¥) ,f,·�·;J,'!·;��roT«(';'Ort:�.� II!"C t : ,J-. ' 11' ( ,,_ ,' ', • • ..,"0.:..-&.,..,'J»t.m(', t C'

•,f,'J..ht','Mr'.'�·.·s.�t-....' .,' O(�a.t',�t«'-."Crt.. �·.·�.''�-��.·�·-·,. ,.�)l}"'.�fii','A:J('

' *'( 'N•", Jt.l'( 'AI.;�II',"Sf'CI.... t � . ; ..,-; '1.\K ¢(:�·. ' .¢ti .; "Mfl' )., ..t ' , '.:V·1VJ'·r�',""'-W' 1-{ H'J ',' ' '

,

Here's the same thing in Excel 2007: ./ •

•

c

:· � ;'"" �o.JM."';; � .._�,� �- ; ' S.;�.: embt,...;O e � ' owr "': ( ,1! T ', :. � n.,. M • , , . H

k'f"': " '�" "" �l e � ,q •t ·:"'M.Wd'i':"A;1ll tt·:' M

0

t

,

i.l �

1 ,H

l:�l.I*Y

..

.

But you might wonder where's the rest of the formula? You can drag the formula bar down, or double click it to see both: 11

""-

•

• .....L..._,c,._j

f•1•l"

J
•Oc<(•flibtt':'"u-r..u,.y'"t'' cbtu;�r,..:· MMO!'':•Aprfl-.:· ,_...:' ..,.-· J.-rA:•:..r!lt)·":'.\u:-g.\i$t�.:'SeptMbet':'O;·to1xH-";" :;Q\�Mb!.·•·:

'tl«A!tn�f'':""� .t. � Aot'W' ' � "(.,t:n.JJr(':'

...J.

o

-�

r

1 <>

'''"" ;;;I

,_��•:•t\p!ll':' M¥(:'J�r..t�:"'h...,.,-;''..'4"')1\ISI'";'"$otpt�-r.t:,t1' ;'()(tOIX-t"!"' N O"' "-Mbt : 1"

t. W d ' i; •J • SNri•:• Mtr( J�r�'�·tr:itf':'�U��"�'$tp1\".'l'l�l":'0<10�,..�·NOO!�ftt�I": ·J I .I\_! L .I M I " r...J ,

..

'0\.'<1!M'!t�r·;"t�AuMY"'t'fCbfl1oi'J":

J_,.i;,j

• ;

.L.
As you can see, when the formula bar is expanded the worksheet moves down to accommodate it. You can use Ctri/Shift/U to open the formula bar to fit the active cell. And press it again to restore it to one line.

This isn't

Excel, it's Magid

65

29. Formatting comments When most people create a comment (Review Tab, New Comment, or Shift/F2), {Excel 2003: lnsertiComment), they accept the shape and color and just enter the information they want. So most comments look something like this:

/Bob Umlas:

This is a typical comment

-

I

But how about a comment like this?

l '

Bob Umlas:

Or even this: (It's me!)

Bob

Here's how you can do it. When you first create a comment, the cursor is right after whatever you typed.You need to select the comment itself, not the text inside. So, select the border of the comment:

66


....

Comment3

A

12r--

I

· -

c

B

D

13 14 -·

F

E is a typical comment

1 5I -'

161

17J

___...

You need the "Change Shape" tool. This can be placed on the Quick Access Toolbar: [Excel 2003:View 1Too/borsl DrowingiDrow Menu !Chonge Autoshape) .

. .

'� !l,9P. .!i i!. � .S .. � Customize the Quick

Popular

Formulas

l;hoost commands from:(!)

Add-Ins

Trust Ctnttr

Ruoura\

.

.

[

All Commands

Save

�Cu\torn1u

Ac.ces�'\T. ,'3 16�r. '' 9

J

Proofing

Advanctd

-rr, -..

3

Changt Layout

Chart Background Chart Data lablt Chart Eltmtnts Chart Floor Chart Gridlints Chart Layouts Chart Namt Chart Ptoptrtles

Chart Quick Sill«

""-' ''

l

-

�

j �

>

p

=..> •

•

,.=:,..

r

� ...,

.:!J •

[

Add >>

"

- me

I

I

When the comment is selected by the border, click the new Change Shape tool.

This isn't

Excel, it's Magid

67

@liD IY �·w��

r8 ' ISiti>� J) ;;

BO<

J Home tnsert PaJJe oBasi o0 D. � o ooo � h T!lhomo fJBliOQ@@@A
•

P�sle

I

•

Olpboard r;

Com ment3 12

13

A

•

-

Font

·(•

8 I

...

81 0<1< A rr oW>

¢ ¢ 'if .O. (:;) i.t + e f" � ca c1J � � \\.9 O't> •1.> ¢ D » [l;; g ·:0 � c - o-v� flowth ort

DO<>OO[jGJ(;'k>OCJ\J o o O t?®Ee & � 6 \7 G D QEJOlO

14 15

Cal lOUIS

16

Q 009-DAJ,-o
17

" I

18

Starsand 8a _nners

lg

J!.�

�

-

00 � -A- ® ® ® $ 1121 !tll fRl !l:SJ .triP��

20

� �

..

..

From that, you can select basic shapes, block arrows, flowchart, stars and banners, or callouts, each of which has yet another set of shapes to choose from. Shown here is the Cloud callout: cauouts

o o og...o ...a rGl @ -

.. . . .J...... ..J /:,.... ., / i [1� ...: r:,....J. c.....;. JO '0

ri,.., ' L::J

Once you have the new shape, (cloud, here), right-click the border once again and choose Format comment, as shown, if you wish to format this comment even further:

!

g. 1 m;� � i: ;:: · : • ·· N: · >·· ·:· ··�···:· :>- t·t·t·�·!·!�·t·· � :�

,.J i'''=

I I l I I I

J,

�op•

� faste ..J 5) Edi tTe� iii'� '� � -=-

-··

68

Cu!


-

-

Grouping

>

Order

•

1

A�sign Ma
�� Format Comm.ent·.-. .

1

� !jyperhnlc..

.·

I.

That will bring up yet another dialog, from which you can choose Fill Effects: ------

Format Comment Font

I

Ft1l

I

II

Colors and l in es

�olor:

tv"I

NoFtll

Iransparency: ne l

------ - - -

··-

• •· •· •· •· •· •· ·• •·

C�:

�shed:

• •••• • • • o• • •• •

Connector: MOWS

aegin style: Begin s[ze:

[ I

luooou•co

••oo• ••o • • [1(- • • • • Mor e Color s

. .•

Eil E ffects . . .

Fill

..

I

Io "'o

Automatic

0

II

Web

Si2e

�I

!o.7s pt

I

� I[

�

�I

I

Cancel

I

From this dialog box you can select the Picture tab and import any picture (I chose me!). rill flfor.ls

------

rEl

Setect Pictur e. .. sample:

0 Lock Q.icturt .spec.t ratio

0 �ol• t• fill elfe
as you saw before. This isn't Excel, it's Magic!

69

30. Format #VALUE.! or any errors away Probably the easiest way to not display errors like #VALUE! or #DIV/0! in an already existing worksheet is to use Conditional Formatting. Here's how: 1 . Select all the cells you want to hide these error values in. 2. Use Conditional Formatting from the Home tab and select New Rule (there are other ways to get to the new rule, but this is the most direct) ,

[Excel 2003: FormotjConditionol Formatting):

r � �- � !! ' �� l!lghllght F F 1m

e �

for matting " as Table .; t Syl e s

Cond1t1onal Format

"

,;

C�ll

•

Cells Rules >

�� I ngr:;l ® ® li!l

Iop/Bottom Rules

•

Qata Bars

•

Colo r S«otes

•

(con Sets

•

�

-

·

!j ew Ru l e ...

�Itar RuiH

Mana g t Buies...

•

3. Select the Rule Type "Format only cells that contain," then pick the Errors rule from the dropdown: � Mt

-· ·-

��l. !''.'!.'!_l!l!�.e.�,!l}�- r@� �lect A Rule Type : 1>

Fonnat al eels based on their values 1> Fonnatonly celo thatcontain 1> Format only top or bottom ranked vakles 1> Format only values that are above or below average 1> Forrnat only unique or duplicate values

1> Use a formula to

det2rmine whi ch cells to

forrnat

!:;dtt the Rule Description:

fQrmat only cells with:

�iii..H.rc · ·' " ' ",'= "� i�l !.. ..;...,.,g:. ,-...,-... i_ '"' oo> i

Prevlew:

70

I


No Format Set

IL

OK

'

I

[

II I

fo r mat. ..

.

Cancel

I I

4. Click the Format button, the font tab, and assign a white font!

'

i

'.It'

«! �) 8
R..P., nw. Sold Sold ltak

�

�:

IEff ects

Size:

Fgrt style;

051<\elt' ' r LJ .'(A,:,('

8 9 13 I! I�

"

"

-

�--

For Cond•lJOMI Fo:rnotlilo you con sel

v

14

-

• Automat•c Them4!Colors

FoUII fxlore Colors...

OK

II

Cancel

This isn't

Excel, it's Magid

71

3 1. Using [h} format for hours>=24 When you add times, anything over 23:59:59 will start over at O. lf you use a format of [h]:mm:ss then you will get the "true" number of hours:

17

C6

I

21I

1

•

A

3•

jl

�

(

.

'

B

�x:I =SUM(Bl:85}

--·

3:15

4:15

'

c�

Q

5:15

6 15

:

7:15

.

2: 151 26:151

•

The formulas in 86 and C6 are identical. However, 86 is formatted as h:mm:ss which will not permit the "h" to be above 23, and the format for cell C6 is (h]:mm:ss.

72


32. Advanced number formatting You can specify formatting by value ranges.This format: (Red] [1 20]"TOO HIGH";$#,##0.00 says: if the value in the cell is <80, make it red and show the text "TOO LOW." If the value in the cell is >120, make it blue and display the text "TOO HIGH." Otherwise, format it as currency. Aside from [red], (blue], [cyan], [magenta], [white], (black], [blue], and [green], you can use [color1] thru (color56]. The rule is to place the color inside square brackets, followed by another set of square brackets for the condition, then the format if it meets that condi tion. You're limited to 2 conditions plus an "all other."

This isn't

Excel, it's Magid

73

33. Formatting text You can have parts of text constants in one cell be different sizes, fonts, color, by selecting the text and using the tools from the formatting features in the Home tab [Excel 2003: Formatting too/bar]. '

Text cal refarn:tted Of d'a'actErS in a rell (oot fam.ias) Here,

� @

•

Home

Insert

Calibri

Page Layout

Formu las

Data

Revi

• 11

Al B

2

c

can be formatted by ch

3 4 5 6

II I I

� ardCo l ors

� = \;)

•• • ••

,More Colors...

the word "Text" is selected, and the red font color is being applied. Only the word "Text" will be red. You can also access the font by right-clicking once the word is selected.

74


34. Using Fills A number format which includes * will fill the cell with the nex.t character. A format of General*. will fill the cell with periods. Enter 1 2 and you'll see 1 2 . . . . . .. . . . . . .. as wide as the column is: Al

1

?

u......

... ,

A

u

fx

0 ••••••••• 00 • • •0 • • • • • • • •0.

•

B

Use a format of $**#,##0 to get asterisk-fill for currency. The first * is the code indicating to use the next character to fill the cell. That next character is *, so now 1 2 looks like this: Al 1

2_

... (

A

12

fx

$*************** 12.00

E

•


75

• 35. Indenting or distributing text Suppose you have a worksheet which looks like this: A 1 Main section 2 Sub Sections 3 Isub Secti o ns 4 ; Sub Sections 5 Sub Secti o ns

B

6 I Sub Sections 7 Sub Sections

Main section 9 Sub Sections 10 Sub Sections 11 Sub Sections 12 Sub Sections 13 Sub Sections 8

14 Main section

15 S ub Sections

16 Sub Sections 17 18 19

Sub Sections Sub Sections Sub Sections

and you want to indent each Sub section a few cells. In this example, you could replace the word Sub with" Sub", but in reality, each of these cells would contain other text.What many people would do is laboriously click in each cell and type a few leading spaces! But what about this: Two clicks of the indent button on the alignment section of the Home tab: [Excel 2003: Format!Cel lsjA/ignmentllndent up-arrow twice)

Alignment

works just fine. It's a command also found in the format cells dialog (ctrl/1, alignment tab or the bottom right arrow of the above illustration):

76 This isn't Excel, it's Magic/

"'J, • p y_ -;,.,.·-

��gmt_a�.��l�; -

Number

Alignment

Font

,

Border

Text alignment

I (Indent) e : IBottom

j[

Fill

vi Indent: g vi

Horizontal:

Left

V rtical

Distributing text is found in the same Format cells dialog. Here, cells A1 and A2 contain the text "This is a wide cell," but cell A2 was formatted as distributed:

is is a wide cell is

F�;mat

is

a

wide

cell

Font

Border

_ _ , .-. · � � • r • " ' ' ' r • • • " .,_ _lfm Ce l ls . . 4 'U �, � '!' � � � ...d

5

6

7 8 9 10 11 12 13 14

Number

Alignment

Fill

Protection

Orientation

Text a�gnment

•

ttorizontal:

(Indent)

lndent:

lo

T e X t

�1

•

•

• •

Text

•

This isn't

•

-

•

•

•

•

•

Excel, it's Magid

77

36. Easily create custom list of a-z, A-Z Instead of typing this all out and then importing to custom lists, you can enter: =CHAR(ROW()+64) in row 1 and fill down to row 26 for A-Z.

I

Al

A

_!_., �:; ;lA_�! 2 B

fxI =CHAR(ROW(}+64)

B

c

I

D

31c

4 .o ,

-

5 IE

� ��

------- ------

:

1'---

=CHAR(65) is the letter "A", thru =CHAR(90) is the letter "Z" Copy the list and paste special Values (or you won't be able to import them into the Custom Lists). Then select the range and use Office Button, Excel Op t ions Edit Custom Lists button: [Excel 2003:Toolsl0ptions!Custom Lists] ,

� D �. .!lJ.C.�IOp,lio�.·.��' � Hom � ,)1 -� Pa�t· "

£!1pboir
-� -=� 1 A A

_3_ 8 3 c -

..1_0 5 6

E F

ILPopula• Formulas Pfoofino Savt

ACS.Jn
u1tomirt

Change the most popular options in Ex c el. . ..

,Mani Toolbar on nltdion"J:

S Enabl• LiY< Prev;ew"

8 Show Q�eloptrtab in the Ribbon
Add-Ins

t;olor sd1eme:

Tnnt Center

S<{l."tnTip 't y( t:


�

� Show

8 Alway\ u'e Out!Jpe

Ruourcu

=

Top options for wo king with Ex c el r

C

and click Import:

78

r

�

��'= Blu '= •:::! i:J :L

-:1

__ __ __ __ __ __ _ _ _

Sho w feature deu rfptions i n S a eenTi ps

Crute lnts for use in sorts and fill s�quen
v

IEdit Cust,gm Lisu...J

� ·-·-- �-IM"i)rv;) Custom lists,.lj. - l!£!:J� ,_, . ,.� · -. u . ·,., ,, _"

·

-

-

Custom ll�t s Cus tom

list �ntries:

NEW LIST Sun, Mon, Tue, Wed, Thu, Fri, � SUnday, Monday, Tuesday, Wee Jan, Feb, Mar, Apr, .lul, J

[ &dd I �--. [ I :6·

A B c

Qefete

0 E F G H I J I(

Pre�s Enter to separate nst entrie s·

tmpoct bst from cells:

l

===::. [::J��C:�;

-, ,. == .-� $A:!. $:.: !!: :$ A � = $2 = 6-- � ::.: � ::l '< -

l L.:!

�J

OK

I[

Carocel

Now, you can enter any letter (upper or lower case), and use the fill handle. I f you entered upper case, it will fill with upper case letters. Lower case will fill with lower case letters.

This isn't

Excel, it's Magid

79

37. Inner Series Some fu nctions take a parameter which changes, like =IPMT(rate,per,nper, pv,fv,type) The "per" argument usually differs from one row to the next, making it difficult to fill down. Most people enter the series 1 ;2;3;. . . down a colu mn for the sole purpose of being able to reference it in the formula! Instead, use ROW(A 1 ) as the "per" argument: •

86 A 1 Rate

Ji..__ B

6

7

Lc

I

I S1,75o.ooI

•

$1, 4 .

$1,745 7.98 $1,743.95 99

10

$1,74 7 1. 2 $1,739.88

11 12 13

I E

F

$400,000

8 � 9

o

0:00

3 PV 5

:IPMT($B$1/12,ROW(A1),$B$2"'l2,-$B$3}

5.25%

2 ' Nper 4

f,.

i�

j

$1,737.8 92 $1,735.76

-�-- ---

·

1-

---!

-

When the formula in cell 86 is filled down, the part containing ROW(A1) becomes ROW (A2), etc. This is, of course, 1 in 86, 2 in 87, etc, giving the in ner senes. You can also use COLUMN(A1 ), of course, if the series needs to be filled rg i ht!

80


38. Using Apply Names Immediately after naming ranges, you can easily apply these names to exist ing references. For example, if you have =BS+B6 in a cell

.r

82

A

c

B .

1 3

D

nl

I

2

1 =B5+86

tx

•

4 4

s

First

6

Second

7

7

and then name BS "First" and B6 "Second." 2

11

3

4 s

6

7 8 9 10 11

F irst

Second

Create Names from Selection -

4

Create names from values in the:

,owJ 0t!i1:;:..;;:e· .......... 0 �eft column

7

@�

· -

....

.

Oaottomrow

0 fii<;lht column

OK

JI

cancel

then after naming them you can use Apply Names from the Define Name dropdown in the Formulas tab, [Excel 2003: lnsertiName!Apply) and these will already be selected in the dialog: Formulas

Data

�

Revi�w

� lookup & R�f�rence • til Math & Trig • • � Mor� Functions •

I

Name

Manager

View

Developer

�� Define Name '4

I

A

Define Name... -=

A:B

�pply Names -

.•.

Defined Names

After you apply the names you will see =First+Second:


81

..,... ... !:��-§;;

"'("

��P.! X. !'-1.�

��

v

0 !gnore Relative/Absclute

[Qptions J ] [ Cancel

0 Yse row and column names

[

B2 1 2 3 4 5 6 7

82

...

J;>.

OK

C:t

(..

-• ; .

.c

Iii'�

11

First d


>>

4 7

'

=First+Second

-.0<

-

-

39. Selecting a random sample of data If you have a database with many records and you want to take a random sample of that data, here are a few techniques you can use. One way to get a random sample is to use a computed criteria and advanced filter. Suppose you want to take a random 10% of the data. Enter the formula as shown in C2 (keep C1 blank). By entering the formula =RAND()<0.1, every time this worksheet calculates, the =RAND() will return another random number. So RAND()<0.1 will return TRUE, about 10% of the time. (Rand() returns a random value between 0 and 1, not including 1 ). C2 1

2 3

f,. =RAND()
•

A

Database of 100 items

8

I

lteml ltem2

c

TRUE

I

•

4 ltem3 5

6

ltem4

7

ltem6

8

lte m7

9

ItemS

10

Items

ltem9

Using the Advanced button from the Data tab: [Excel 2003: DateiFilteriAd vanced Filter]

Formulas

T Filter

Data

o( Clear

Review

:{� Reapply

I � , LV A dva n c.,� I

Sort & Filter

c


83

you can filter like this:

�

-

Advanced Filter Action

0 filter the list, in-place

0 CQPy to another location

!.ist range: �riteria range: -"11� .)

'

I$C$1 : $C$2 � :::: == : :.: :: == � L I _:·�::.:.J-:1 _ _ _ _ _ _ _ _

0 Unique records only

[

OK

I[

Cancel

I

and that will create a random selection: roc

A

1 Database of 100 items 10 ltem9

-

13 ltem12

16 ltem15 27 ltem26

62 ltem61 n ltem76

74 ltem73

-

86

-��

ltem85

11-

This will be different each time.You may notice that there are only 9 items shown, not 10, and that's because the values RAND returns are random! It's best to use this on larger databases!

84


A second way to select a random 10% of your data is to still use the RAND function, but not use filltering. Look at this: A

r-

B

.,

1

Database of 100 items

2

ltem1

3

ltem2

4

ltem3

0.161792

ltem4

0.236491

6

ItemS

0.255978

7

ltem6

0.786952

8

ltem7

0.810523

9

ItemS

0.227744

10 ltem9

0.2444

5

I

-

!

0 .472988

0.754415

11 ltem10

0.170091

12 ltemll

0.506724

13 ltem12

0.073749

14 ltem13

0.186336

15 ltem14

0.498387

Cells B2 thru B101 contain =RAND().AII you need do is select A2:B101 and sort by column B! Take just the first 1 0 items, and you have your random 1 0% of the database!

This isn't

Excel, it's Magic! 85

40. How to change row height when a key field changes Suppose you had a worksheet something like this (but more complicated!) A

I

- �- .Joeeartment!

B

2

IAccounting 3 Accounting 4 Accounting 5 Accounting 6 Accounting 7 Accounting 8 Accounting 9 Accounting 10 Personnel 11 Personnel 12 Personnel 13 Personnel 14 Personnel

15 IS

16 IS 17 IS 18 IS

If A1 :A3000 contains departments (sorted) and you want a visual break between departments you can change row heights on the first row of each new one. How can you select the changed department? In the above example, in cell B2, e.g., enter a formula such as =IF(A1 =A2,1,NAO). Or you could enter =IF(A1 =A2,TRUE,"X").

86


The choices I'm usi ng are any two of Number,Text, Logical, Error because of this:

�

-

Go To Special

Select O co ents o c�nstants 0·Eorm�lasl

0 Row differences

mm

0 CokJmn differences 0 Erecedents 0 Dependents

�N!lmbers

D ecc orI

�Te�t

�logicals

:.l .evel>

0

La�t cell

0 Blan!ss

0 Visible cells onl:(

0 Current tegion

0 Cond�onal formats

0 Current �rray

0 Data yalidation AI

0 O!ljects

!,

Same

OK

1) I

Cancel

l

Assumi ng we choose the first formula, then the NA()'s will show on every new department: B2

1

I

5

6 7

8 9

10 11 12 13 14

..

(

A Department Accounting Accounting Accounting Accounting Accounting Accounting Accounting Accounting Personnel Personnel Personnel Personnel Personnel

15 IS 16 IS

#N/A

1

1 1 1 1 1

#N/A

1 1 1 1

#N/A

1 1

17 IS

1

18 IS

1


87

Select column B. Home tab, Find & Select, Goto Special (or simply press FS): [Excel 2003: EditiGo ToiSpecial] rt •

I:

liD

�te "" nat

at&

M

·

a·

•

s

•

3 " 3C

q

��

Sort &

Find &

Filter • Select·

find...

Replace... Go To...

l

Go To �pedal... Formylas Comments Conditional

Formatting

Constants

Data Validation

�

ln.

Select Objects

� ...t- ....:-- n...--

select Formulas + Errors (you actually have to deselect Numbers, Text, and Logicals, leaving just Errors, or just deselect Numbers because you know the result is only numbers and errors): -

Go To

[8]

Special

Select

O�omments 0 CQ:nstants @Eor!Was

0 N11.mbers 0 Te;![t 0 . ll.��!�i

0 R� diferences 0 Colu(!11 differences

O�ecedents 0 Dependents 0

.... ._....._..,

0 '-rrors 0 Blan!ss 0 Current region 0 Current �rr�y 0 O!lJects

u �


. ! <; '..· . .

0La� cell

0 Visible cells onll( 0 Conditional formats 0 D�ta y_alidation

0� 88

ct or'

All Same Cancel

J[

Now only the #N/A cells are selected, so you can use Format Row Height from the cells group of the Home tab [Excel 2003: Format/Row/Height]:

:

�o Insert • � Delete • Sort & i!) Format • (2· Filter • Cell S"12e

:c : ++

n

Find & Select •

'

Row Height...

0

AutoFit Row Height Column Width... AutoFit Column Width .Qefault Width...

Visibility

And change the row height to 30 to give this effect: A

1 _Department

B

I

Accounting

c:J

4 Accounting

1

5 Accounting

1

2 Accounting 3

1

6 _Accounting

1

7 Accounting

1

Accounting

1

9 Accounting

1

8

10 Personnel

#N/A

11 Personnel

1

12 Personnel

1

Personnel

1

Personnel

1

13

14

15 IS 16 IS

1 7 I<;

(

#N/A 1 1

And finally you can clear column B.


89

4 1. Define "global/local'' name If you define a name to be the cell above, like from D2 you define "above" to be =D1, Excel puts the sheet name in front of it. Using Define Name from the Formulas Tab[fxce/ 2003: Insert/Name/Define]: Formulas

.ayout cal

Data

lookup & Reference

! & T1me

6 Math & Tr�g •

' More Functions

�

Review

Name Manager

•

Library

View

Ac

Developer

...U Define Name

JP Use in Formula •

� Create from Selection Defined Names

D

F

E

G

H

I

•

-

!

L. l

_ _

New Name

�ame:

�cope: CQmment:

&efers to:

��

IA bove � --__J I ======; � vI Workbo ok

l=dll [�J ��--== , = �= ===� jr [=c== aoc� el�j ;

-

/.

you get "above" to be =SheetS !D1: D

lL.--1

E --

H

I

J

K

��

-- -

Nd/JlC Manager

[

G

F

Mew···

Ed

Delete

I

filtw

]

So any time I use =Above to reference the cell above the active cell, it will always pick the cell from SheetS! That is, if on Sheet5, cell A1 I have "Bob," then if I enter =Above from Sheet1 cell A2, 1'd see "Bob." 90


Interesting side note: if the active cell is in row 1 and you use Edit/G o To and type Above, the active cell will be in row 1 048576! To get "above" to be the cell above on any sheet, do one of the following: 1 . Leave leading "!".That is, define "above" to be =!D1: 1

2

A

B

3

--

5

�me:

4

c

E

D

I

I._

!

_ _

Edit Name

6 7

C2mment:

8 9

lAbove

...

rill� I " -

10 11 12

Befer> to:

13

14

1�101

II

OK

t�I l I Cancel l/

2. Define "above" to be =INDIRECT("r[-1 ]c",FALSE)

I

!

-·

�t'2] I

New Name !!lame:

�ope:

C�:

&efers to:

[Above lworl
vi

-

[&indirect("r[-1)<",false� I OK l I

.

1�1 Cantel l

The difference between the 2 choices is that the latter will calculate auto matically, and the former won't (without forcing a calculation with the F9 key). As a side note, you probably shouldn't use this technique if you're also using VBA because there's a bug that when VBA recalculates, all cells with that name refer to the active worksheet! This isn't Excel, it's Magic!

91

42. XLM-Names (not XML) Old style Excel 4 macros ("XLM"=eXceL Macros) are still around and useful. They must be used as a defined name, not as a worksheet function. If you try to use these as worksheet functions Excel will give you an error. (Please note that prior to version 2003, copying a cell which uses this name to an other sheet will crash Excel!) For example, the LINKS function will return a list of the links in the work book (and in a way that you can see the entire path!) =LINKS() This will return all the file names in the current directory. =FILES() This will return the names in the workbook =NAMES() There are many more, and if you're interested, you can download them from http://support.m icrosoft.com/kb/q1 281 85/ Exampl e: Define lk as =LINKS(). Then use =INDEX(Ik,1) to get first link, or =INDEX(Ik,row()) in row 1 & fill down to get all the links:

[ZJ[?SJ

�ew Name

�ame:

2cope:

CQmment:

Refers to:

lk

� ==== � IWor== kbook== l;' I ----------� :========!....__ ----------.,

lL=_ lin .. ks_, O..: I,_--;=

A1

92

This

isn't Excel, it's Magic!

� ] [ Cancel J :;-;: = ::::! := (�:;J =

= = =

II_

As you can see, cell A1 contains the formula as indicated, and it returns the entire link path to the first link document. The workbook is linked to 3 workbooks, named del1.xls, del2.xls, and del3.xls. But the Edit Links dialog (from the Connections section of the Data tab [Excel 2003: EditjUnks]) Insert

Home

al

If�

Mi} Connections

u H

Refresh All• Connections

Data

formulas

Page layout

lill1 1

.,A Oear

A

� Reap1

filter

Sort

�Advar

Sort � filter

doesn't show the whole path: Edit Links Sour ce dell -y j $ del2.xls del3.xls

.

Location: Item: Updote:

-

I Typ e WN I s he et '

Wo�ksheet Wor·ksheet

Updote A A A

� [ �evalues ] [Chaoge s-ee.. I

Stotus Unl niJwn Unknown Unknown

.

C:\Oo
0 &utomotl<

[�tMtuP Prompt... I

Similarly, here's the file/open dialog: -

------

Open �,.:

�-

(j o..stop

e�. !l ""' Ccnpltcr

lo

:1-2000

... ..

��vt:�!] I!Joz �•"."" i!) O>-OQORl.>i< !)�-thTI\O.JdJ

i!)OKLOSEA\IG.xb i;!)os-5'{EO.x�

ONN:>lR£CTZ.>I< �..,Network '!!J 1!.)07-IN:>IAE CT.>CI< Pious

i!!)oe.F1Nlt11"H.>
�

"!)09-15 1 K 1 m . xls

10<-IAXOATt.x� 11·1fX1SOAT.>k

• 12·A'.rne.xls

'e!.)13-SORTP'I'!0.>4s

·�-

v

-

•z�<�� <9 1<8 38 1<8

IS 1<8

35 1<8

Z'ISIC8 221:8

Dote-lid

ty pe

t'icro$0lt<:ifK.c Extd

•••

Mcrosott cttlc:cElCCeJ... Mcro$01t�fiai £xcel

Mcn>$011: ctfi
Mcrosoft ctfite Encl.•. I�CIOlo/t Off�e Exte\. ..

Mcro«ltt ctfiee Excel...

21 K8 t-1crosolt cttke Excel i2 1
•••

261<8

30K8

•••

MCIO$oft Offkc Exte1.,,

161:8 Mcrosoft otfke Ex
IHB IIJcrosoft ().
E

3/1?/2000 IMZm 3/1212000 10:57m 3{12{2000 tO:>
3/l
9:35""

3/12/2000 10:571'1'1 3/12/2000 10:31FN 3/12/2000 10:57 FN 3/12/2000 10:26 FN I0:II I'N 3/12/2000 10:57 I'M 3112/2000 10:57 I'M

3/l2f2000


93

By defining a name like FL, for example, to be =FILESQ, you can get this directly into your worksheet:

I =INDEX(FL,ROW()} c A _ ..., I _:...:. ...._ -,\1. ____B_.: =---I 1 � 01-ARRAYl .xls ! Al

•

_

1

fx

(

2 02-SUMIF.xls 3

03-0KSORT.xls

04-MatchTwo.xls J 5 05-CLOSEAVE.xls 4

6 06-SPEED.xls 7 ,07-INDIRECT.xls 8

07-INDIRECT2.xls

9

08-FINONTH.xls

10 09-ISINCELL.xls

11 10-MAXOATE.xls 12 11-TEXTSORT.xls

13 12-Prlme.xls 14 13-SORTPTNO.xls

Another nice feature of this is that you can fi Iter on the files. For exampie, if you wanted to return only those files which have an "0" in them, you can use =FILES("*O*") where the asterisks are wildcards: A1 A

�

(

!_103-0KSORT.xls

_

f !

04-MatchTwo.xls

2 3

"'I:INOEX(fi,ROW()) B l c 0 Ndlllc

05-0.0SEAVG.xls

__,

4 11-TEXTSORT.xls

5 1 3 -S ORTPTNO.xls __, 6 14-WilCOX.xls

7

94

E

-


Manager

fjew... fl

II

�..... �

...

}

II

F

�e

G

Refers To

=FllES (" ' O '")

H

1

Scope WoM.ook

43. Getting number of unique values For this example, assume Rg is defined as A1 :A10 and contains 4;5;5;4;5;5;4;5;5;5 Array-enter (ctrl+shift+enter) =SUM(1/COUNTIF(Rg,Rg)) How it works: First, a brief discussion of the COUNTIF formula.The syntax is =COUNTIF {Range,Criteria). lt counts the number of items in the range which meet the criteria. So, in our example, =COUNTIF(Rg,4) would return 3, because there are 3 4's in the range. Similarly, =COUNTIF{Rg,5) would return 7 because there are 7 5's. It turns out that if you use the range itself a.s the criteria, then each item in the range is used, one at a time, as the criteria. So, COUNTIF(Rg,Rg) counts the number if 4's, then counts the # of 5 s, then counts the # of 5's, etc. This returns ={3;7;7;3;7;7;3;7;7;7}. However, you will only see the first value, 3, in the cell. If you clicked in the formula bar and pressed the F9 key (calculate), you'd see all the values. (If you did this, don't forget to either press esc to cancel the calculation or click the X near the formula bar). '

So now, 1/COUNTIF(Rg,Rg) returns ={1/3,1/7,1/7,1/3,1/7,1/7,1/3,1/7,1/7,1/7}. There are 3 "1/3", and 7 "1/7", each totaling 1 . So the SUM returns the correct result:There are 2 u nique values.

This needs co be ctrl/shiftlentered or it will calculate as if that first 3 were the only value, and the answer would be 1/3, or . 3 33333333.

This isn't

Excel, it's Magid

95

44. Named formulas are array-entered If the formula in the preceding tip were entered without ctrl+shift, the cell would contain 33333 as mentioned. If a name (say "unique") is defined to be =SUM(1/COUNTIF(Rg.Rg)) and you enter =unique, without pressing ctrl+shift, the cell would contain 2! .

,

.tL.��!l"iS&£�

[

New

••

Name

.

Cl unique

lI

�dit...

)[

Value

{ . .} .

tlelete

)

Refers To

-S....-(1/CO�Tlf(Rg,Rg)).

I

Scope

Filter •

Convnen t

I

Workbook

�.!; Refers

to:

jX)f./1 l•sur�(I/COUNTIF(Ro,Ro))j

So it seems all named formulas are treated the same as a n array-formula.

'1'!)

t.>-11

!�I I Close l if you

•

entered it as

However, here's an oddity. If you enter =A1 :A2 in cell A3, you'd see #VALUE!. If you array-enter it, you see whatever is in cell A1 . 1f, while the cursor is in cell A3, you define a name to be =A1 :A2, then entering that name in A3 still produces #VALUE! If you array-enter that name (like {=test}-the braces supplied by Excel) then it returns A1 's value. So these techniques seem to contradict one another! Just be aware that your named formulas may be different from what you expect only because it's likely interpreted as array-entered!

96


45. Getting End of Month You don't need the Analysis Toolpak (an add-in which ships with Excel). If the date is in cell A1 , then to get the date of the last day of that month, use =DATE(YEAR(A1 ),MONTH (A1 )+1 ,0). The DATE function takes 3 parameters: year, month, and day. The Year is the same year as cell A1's year, so we simply use YEAR(A1 ) MONTH(A1 )+1 is one month past the month in cell A1. The first day of next month is =DATE(YEAR(A1),MONTH{A1)+1,1 ), so the day before that is the last day of this month. Therefore, instead of using 1 as the day parameter, Excel has no problem with using 0. The Month parameter for the DATE function doesn't need to be 1-12, and the Day parameter doesn't need to be 1-31! Of course, you can also use =DATE(YEAR{A1 ),MONTH(A1 )+1 ,1 )-1 if you don't like using 0 for the day parameter! .

This isn't

Excel, it's Magid

97

46. How many Wednesdays between 11 1105 and now? {This is obviously not limited to Wednesdays!) Use the INDIRECT function to turn the date into a range! (Hang in there!) IfA1 has 1/1/05 and A2 has =TODAYQ (and let's assume for this example that today is 3/5/05) =ROW(INDIRECT(A1&":"&A2)) is the same as =ROW(I NDIRECT("38353:3841 6")) which is the same as {38353;38354 . . . 38415;38416}. These numbers are "serial" numbers, Excel's way of numbering days since 1/1/1 900. The INDIRECT function changes text to a range, where possible. The WEEKDAY function takes a date and returns the day of the week, where 1 =Sunday. 2=Monday, etc. So, =WEEKDAY(ROW(IN DIRECT(A 1 &":"& A2))) would return {7;1 ;2;3 } because 1/1/05 is a Saturday and Saturday is 7,so it starts with 7. . . .

,

Put another way, =WEEKDAY(38353), where 38353 is the serial number for 1/1/05, is 7. Comparing this sequence of numbers-{7;1 ;2;3}-to a 4 changes these numbers to a series ofTrue/False values:

A3

(

•

A J

....!.... 1/1/2005 2

3

B

fxj{:WEEKDAY(ROW(INDIRECT(Al&":"&A2)))=4}

I

c

3/5/2005

FALSE

•

4

expanding the formula bar shows:

98


I

D

l

E�

F

G

C U BEMEM. ..

...

( t. 'X ./ ,

..

={FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE; FALSE; FALSE;FALSE; FALSE;TRUE;FALSE;FALSE; FALSE;FALSE; FALSE;FALSE;TRUE; FALSE;FALSE; FALSE; FALSE;FALSE; FALSE;TRUE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;

� I

FALSE 8

A 1 II

2

1/1/2005

3/5/2005

3 .. ;FALSE} •

.!

l

c

D

l

r

E

I . .

F

I

G

•

. . . the TRUE values correspond to Wednesdays. The N function changes False to 0,True to 1 . Array-entering =SU M(N (WEEKDAY(ROW(IN DIRECT(A1 &":"&A2)))=4)) gives 9, meaning there are 9 Wednesdays between 1/1/05 and 3/5/05.

This isn't

Excel, it's Magid

99

47. Looking up 2 (or more) values Suppose you have a table of values looking something like this:

I

B

A

C

l

_!_� �------�M�� a �� e r�� l i� �-----1 2 IFi rst Last Amount 3 1 Bob

4

Fred

S IBob Bob

6

7 John

8 Bob

Smith

23

Williams Devon Williams

78 45

Smith

89

Clinton

67

34

Jones

9 Bob

10 Bob

12

McCarthy

56

and elsewhere in your workbook you have a first and last name for which you'd like to find the amount value.That is, you have Bob Williams in F2: G2 and you need to pick up the value from cell C6.You can't use any of the LOOKUP functions, and you can't directly use the MATCH function either. What to do? Here, an array formula comes to the rescue: H2

fx (:INOEX(C:C,MATCH{F2&G2,$A$1:$A$21&$B$1:$B$2 1,0))}

•

A

+f�

lrst Bob 4 Fred s Bob 6 Bob 7 John

8 Bob 9 Bob 10 Bob ,

B c Master List Last Amount Smith 23 Williams Devon Williams

0

E

F

Bob Fred B ob

78

45

G Find

Williams I Williams Clinton

H

34• 1

78

67

34 89 67

Smith Clinton Jones

u 56

McCarthy

F2&G2 becomes the string "BobWilliams," and this is being matched against A1:A21&B1:B21 which, when selected in the formula bar and the F9 key is pressed, expands to:

i NDEX(C:C. MATCH(F2& G2.("Maslerl.Jsl";1'irSI I.0$1";"8obSrrilh". "Fred'IViliams":"Bob0evon";"BobWilliams";"JohnSmilh";"8obCiu11on· i• '" '" '" BobJone$·:"'Bot>McCanhy"; ":··: ":•••·-:·-;·;··:•·:•·:• },0))

..

.

.

.

.

-

.

·-·····

.

.

.. .

•

.

and you can see that the combination BobWilliams is the 6th item in the list, so this formula reduces to =INDEX(C:C,6), which is C6,so the result 34 is returned.

100


48. Named ranges which define themselves Currently you have Database defined as $A$1 :$E$25.

�ame:

2cope:

cQlllent l!l :

B.efers

to:

IDatabase I ========�--� Worlcbook I� VJI�--� �========== " -

�Sheeti!$A$1 :$E$2S I [� J ��=====;r== �=; Cancel 1 l 1 OK

L

You add a record to row 26, and then need to redefine the range to include the new row. Assuming there are no "holes" in the first column of the database, change the definition to be =OFFSET($A$1,0,0,COUNTA($A:$A),5). This means starting with cell A1 , 0 rows down and 0 columns over, in COUNTA(A:A) rows by 5 columns. (COUNTA returns the number of non-blank cells). This automatically will include new rows added because the COUNTA re sult will change. lf it were now A1 : E25 then the COUNTA function returns 25.When you add a new record, the COUNTA returns 26, so the new definition is =OFFSET (A1 ,0,0,26,5), or A1 :E26. .


101

49. Using REPT for visual effects You can easily use a variation on =REPT("/\",500) for a nice effect. Shown below, you can see the effect of the formula entered in cell A1 . Al A

s·

•

8

txl =REPT("/\",500) C

0

E

F

G

H

=-t-..__ II\/\/\JV :. _.:_ tv .____ \l vwVV\1\MiVVVV 2 -

--"--' 3

-----

l·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll·ll

4�0OOOoOOOCOo<�>OOOO<>C 'i

Cell A2 contains =REPT("=",500). Cell A3 contains =REPT("(-)",500). Cell A4 contains =REPT("<>",500). Go be creative!

102


50. Using the Space as an operator The space is as valid an operator as +,-,/, or *. =West Gizmos will pick up the intersection of the 2 ranges, West and Gizmos: C 9 1

A

�

·� L

I =West Gizmos ! c J D fx

8

North

South

West

3

1Thlngs Gizmos

37

85

4

Objects

53

74

2

-

24

51

5

7

s 9

1n

I

I I

East

92

91

39

98

58

8

·--

-

I

6

E.

I

ssl• I

-

1 I

Here. West is defined as C2:C4, and Gizmos as B3:E3. They intersect in C3, which is the result you see in cell C9.The space operator is an intersection operation.


103

5 1. Extracting the last part of a string Suppose you have this in a cell: C:\MSOFFICE\EXCEL\LIBRARY\MSQUERY\ MyFile.xls and you'd like to pick out the last piece, MyFile.xls. Here's how to do it. The technique basically changes the last slash to some unique character, then finds where that character is, and returns from that position + 1 to the end. First, there are a few things you need to know.There's a LEN function which returns the number of characters in a string, or cell. So if A1 contains ABCA. =LEN(A1 ) would return 4. The SUBSTITUTE function substitutes one value for another in a cell. So =SUBSTITUTE(A1 ,"C","Q") would return ABQA in the above example. Also, =SU BSTITUTE(A1 ,"A","Q") would return QBCQ, because it changes both A's. We're going to take advantage of another option in the SUBSTI TUTE function, where we can change a particular occurrence. =SUBSTITUTE(A1,"A","Q",1 ) would give QBCA, and =SUBSTITUTE(A1,"A","Q",2) would give ABCQ. The 1 and 2 tells which one to change. Okay, preliminaries are done. Let's go back to where A1 contains C:\MSOF FICE\EXCEL\LIBRARY\MSQUERY\MyFile.xls. 1 . =LEN(A1} is 44. 2. =SUBSTITUTE(A1 , "\'"', "") is C:MSOFFICEEXCELLIBRARYMSQUERYMy File.xls-the same string without the backslashes. 3.That length (LEN(SUBSTITUTE(A1, "\"","") is 39. 4. =LEN(A1 )-LEN(SUBSTITUTE(A1,"\","")) is S, telling us there are S backs lashes. If we use that as the 4th parameter in the SUBSTITUTE function, we will only change the 5th slash. S. =SUBSTITUTE(A1,"\",CHAR(222),the value from step 4) changes the last backslash to a CHAR(222). (CHAR(222) is kind of arbitrary strange charac ter_ . . hang in there!) 6. =FIND(CHAR(222), the value from step S) finds what position that char acter is in-that is, where the last slash is! 7. =MID(A1 ,the value from step 6+1 ,255) gives just MyFile.xls. Putting it a II together: =MID(A 1 ,SEARCH (CHAR(222),SUBSTITUTE(A 1 ,"\",CHAR(222),LEN(A1 ) LEN(SUBSTITUTE(A1 ,"\",""))))+1 ,255) will return MyFile.xls.

1<2

2 1

104

•

J•

...

1> =MID(A1.SEARCH(CHAR(222).SUBSTITUTE(A1,'\".CHAR(222). -...........,.

, ..________...,.,.

lEN(Al)·l.EN(SU9SllME(A1,'\',"))J)•I .Z$)

C:\MSOFFICEIEXCEL\LIBRARY\MSOUERY\M .li) le.xls

Tllis isn't Excel, it's Magic!

M yFile.xls

,, .n

_

52. Using SUMPRODUCT Instead of trying to make SUM I F or COUNTIF work on multiple conditions (you can't), use SUM PRODUCT. Or you can use the new functions SUMIFS or COUNTIFS, discussed in tip 69. Look at the following worksheet:

�

.2... 3

.

B

A '

Fred

4

Fred

5

Fred

;&

7

-

__!.. 9

Fred

...2004ifred ' .., .. ..... ,

. .

20031 Bob

.

; treo

,10 11

c

Ill

F

l 3

4'

5

6 8

G

I

�< II

m1

I

I

l

.

I

.

UCT((1''·�11

1l1lJ.

.I

i

UcT((I' ? · "11

�

H

I

•

2

9

Fred

E

111

I

-"D.-1)") ) I

I

{A 2 : A l1= 20 04) --(B2:Bll="Bob") C2:Cll} 10;. :SUMPRODUCT( ' --· . -·- ·---

,

,

.

Cell D4, which contains the formula shown in DS, returns 1 1 .This is the sum of the amounts in column C which correspond to the year being 2004 and the name being Bob. That occurs only in rows 3 and 1 0. The formula works like this: The part of the formula which is A2:A11 =2004 evaluates to (what you'd see if you selected that part of the formula and pressed F9): {FALSE;TRUE;FALSE;FALSE;TRUE;TRU E;FALSE;FALSE;TRUE;FALSE} where the TRUE's correspond to the 2004's. B2:B11 ="Bob" evaluates to: {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}. Now these are being multiplied together. Only TRUE *TRUE is 1 , any other combination is 0. (You can try it in a cell-enter =FALSE*TRUE, for ex ample). So this produces {0,1 ,O,O,O,O,O,O, 1 ,0}, where the 1 's correspond to a pair ofTRUE's. Now this in turn is multiplied by the range in C2:C 1 1 , or {1 ;2;3;4;5;6; 7;8;9;1 0}.This multiplication yields {0;2;0;0;0;0;0;0;9;0}, and this is added up to produce the 1 1 . Cell D7 (formula shown in DS) only returns the {0;1 ;0;0;0;0;0;0;1 ;0} part of the multiplication, and this is added up to produce the 2-which represents a COUNT of the number of records for which the year is 2004 and the name is Bob. You may have seen another variation of the formula in cell D4. Cell D1 0 contains the formula shown in D 1 1 . What is the"-" before each set of parentheses? If you enter =-TRUE in a cell, you'll see -1, =-FALSE yields 0. So --TRUE yields 1 , and --FALSE still yields 0. The This isn't Excel, it's Magic!

•

105

-- coerces the set ofTRUE/FALSEs shown above to 1's & O's (not - 1 's & O's). so the formu la really reduces to

=SUMPROD

UCT({0;1 ;0;0;1 ;1 ;0;0;1 ;0} ,{0;1 ;0;0;0;0;1 ;0;1 ;0},{1 ;2;3;4;5;6;7;8;9;1 0})

which again becomes SUMPRODU CT({0;2;0;0;0;0;0;0;9;0}), or 1 1 .

A side note: you can also use the new function in Excel 2007, SUMIFS.The formula in cell 04 would be =SUMIFS(C2:C1 1 ,A2:A1 1 ,2004,B2:B1 1 ,"Bob"). The syntax for this function is =SUMIFS(sum_range,criteria_range,criteria,...)

1 06


53. Using the TEXT function Date won't fit in the column/ fx =TODAY()

I

c I

o

F

E

�...J.:H.:...._�_:..__c__

G

------m

-

formdl Cells

�

General .....

Wednesday, 11/1/2006

Cunency

Ac
o.to Time

m/d/ Y'/Y'I

Penent•90

Fra
--

Cell D2 contains =TODAY() and is formatted as shown: dddd, m/d/yyyy. Well, it won't fit. If you're restricted from changing the column width, what can you do/ The solution is the TEXT function. Cell D1 contains this formula: =TEXT(TODAY(),"dddd, m/d/yyyy") Since this returns text, it will flow into the next column if there's not enough room to display in its own column! By the way, you can bring up the Format Cells dialog with either Ctrl/1, or by the little arrow at the bottom right of the Number section of the Home tab. This arrow will bring up the same dialog (in the Home tab), in the Font, Alignment, or Number section, but with a different tab or the Format Cells dialog showing initially. Home

X

� 'd

:J

r)l

tnsert

Cahbri

fB

I

u

Page Layout

• 11

•

·JI�±� ·JI�

Formulas

[A· A·1 1= ·

Data

Review

View

�

er De velo p

-

A

&

·I I� •

e;JI�·j :trJ[iF t�l

�

�·

General

s • %

-

•

•

11�•8 +�81

Wherever you see this little arrow, it means you will be presented with a dialog, very similar to what you saw in previous versions of Excel. This isn't Excel, it's Magid

107

54. Use wildcards in MATCH function Look at this worksheet:

I

C2

=

A

....;__.oept �Deptl 3 Depn 4 Depll 5 Depn

•

'•

6 Depn Total 7 Dept2 a oept2 9 Dept2 10 Dept2 1l Dept2

�

Amt

f �j(:B2/INDEX{B: B,MATCH{"'Totai",OFFSET{A2,0,0,1000,l) ,O)+ROW{)·1)} " ofTot al

ani

30% 13"

2919 340

100% 10% 23%

794

762 426 367 207 122

459, 3247 671

E

f

H i

G

30%1

86 9 384

564

16 Dept3

c j p

II

27%

13% 11% 6%

4%

17%1 14%1 100%

I

4%

The formula in C2 is filled down as far as necessary (past C16) and yet it returns the percent each number in column B is to the total value for that department. It's based on the fact that the total is indicated in column A by text ending in the word "Total." Part of the formula uses an asterisk inside the MATCH function: =MATCH("*Total will look for anything ending in the word Total (case insensitive). So, cell C2 is dividing B2 by that number in column B which cor responds to the next Total, or in this example, it's B2 divided by B6. Let's examine the formula more closely. =B2/INDEX(B:B,MATCH("*Total". OFFSET(A2,0,0,1000,1 ),0)+ROW()-1 ) in this case winds up being =B2/ INDEX(B:B,6), which is B2/B6.The part we're interested in here is how the 6 is derived. And more specifically, how this same formula in C3 through C6 returns 6 and how in cells C7:C1 5 it returns 15. Okay, back to cell C3. =MATCH ("*Totai",OFFSET(A2,0,0,1 000,1 ).0) is the same as =MATCH("*Totai",A2:A1001,0) because the OFFSET function as used here says to start with A2. go 0 rows down and 0 columns over, and use a shape of 1 000 rows x 1 column, or A2:A 1001. This part returns 5 because the MATCH function never returns 0-it's a value from 1 and up, or #N/A if not found. An adjustment needs to be made. We need 6, so we can just add 1 ! But that seems like cheating. And in C4, the MATCH would return 4 and adding 1 wouldn't be right; we'd need to add 2! In other words, 1 08


we need to add one less than the row the formula is contained in: in C2 add 1; in C3 add 2, in C4 add 3. So in cell Cn, add n-1. Now look at the formula. We're adding ROW()-1. I've added a column in this next illustration which is simply the part of the formula from the MATCH on: 07

A

8

Amt

1 'Dept 2

3

0eptl 0eptl

4 0eptl 5

0eptl

7

0ept2

6

0eptl

-.!l�

Oept2

9 oept2 10 Oept2 11 0ept2 12

13 -

Total

l I

r

.

14 Oept2

15 0ept2

Total

=MATCH('"'Total",OFFSET(A7,0,0,1000,l},O}+ROW(}-1 C

% ofTotal 30% 872 869 30% 13% 384 794 27% 100% 2919 340 10% I 23%I 762' 426 r- 13% 367[ 11% 6%1 2071 4% 1221� '

I

0ept2 0ept2

/.<

I

'

I

'

5641

459 3247

I

I

17%'

14% 100%

I

0

J

E

F

G

6

6

6

---

6

6

151• 151 15

15

�1.

15

15

151

--

·

-

1 i

.

I 1 i r I r --

· -

What makes this formula interesting is that it can be filled down as far as necessary without knowing what row the total is in.


109

55. See all characters in font set 1 . Enter =CHAR(ROW()) in row 1 a. =ROW() returns the row you're in. =ROW() entered in cell G23 returns 23. b. =CHAR(97) returns the 97th character in the character set for that font, usually a lower-case "a" (picture fonts like Wingdings or Webdings return something else). 2. Fill down co row 255. 3. Easy co see th ings like •, ¢. £: 147

148

149

150

151

"

A

" •

-

-

152 ...

-

153 TM 154 s 155 ) 156 � 157 0 158 z 159 y 160 161 .I

162 ¢ 163 £

4. When you see that is in row 1 49, you can then know that holdingAit while typing 0149 on the numeric keypad will create chis character, as soon as you let go of the AIt key! And this holds true for all of Office, not just Excel! You can copy/paste special values for the character(s) you want. and you can then copy the re sulting character from the formula bar. "•"

110

This isn't Excel, it's Magicl

56. Double-Spacing data Here's a technique to double-space your data which is faster than any macro could do it (unless the macro utilizes this technique also!) Imagine being able to double-space thousands of rows in a spl it second. The secret? Sort them! 1. Number the rows. ln this example the data is in A1 :09.We used column E for the row numbering: I

A

c

B

1 1 Data

Data

2

Data

3

4 s

6

7

Data

Data

Data

Data Data

-

F

E

D

-

Data

Data

1

Data

Data

Data

3

Data

Data

Data

Data

Da ta Data

Data

Data Data

.Data

, Data 'Data

Data

Data

:Data

2

Data

4

Data

Data

Data

5

Data

6

#the rows- en t er 1 in E1, 2 in E2. Select El:E2, double-click the fill handle

7

Data

'Data

•

1

•

81

91

'

:oata

2. Copy and paste these nu mbers so there are 2 sets A

Data Data iData Dataa 8 a

1

2 3

Data

4

5

6

Data

7

D ta

9

10

1l

12

..!.�

14

Dat

Data

B

'

c

Da DData Data tData Data Data Data ata

D ta

ata

Da a

'

Data

IData

Data Data

Data

Data

Data

Data

'

·-.·..-s'-

D

ll

Data

21

_j

Data

Data Data

Dat a

Data

3

.

4 5 6

Dat a

7

Da ta

8

Data

1

9

2

3

4

5

15

6-

17

7 8

16

-

18

9•


111

3. Sort the data by this column.This sorts the blank rows into their correct place. A 1 Data

B Data

c Data

D

Data

2

E 1 1

3 Data

Data

Data

Data

4

2 2

5 Data 6

Data

Data

Data

3 3

7 Data

Data

Data

Data

4

9

Data

Data

Data

5

8

Data

10

11 Data -

Data

Data

Data

12 •13

Data

5 Data

•16 17 Data -

6

6 Data

Data

14

15 Data

4

7 7

Data

Data

18

Data

Data

Data

8

Data

8 9

9

4. Clear the column, and you're done! You can use this technique to triplespace by pasting another set of row numbers before the sort!

112


57. Fill "=A 1" right, have reference become =A2 , =A3,

• • •

Yo u've probably wanted to be able to fill a formula horizontally and have the resulting formula reference a vertical range. That is, you may have entered =A1 , and wanted to fill that to the right but have the result become =A2, =A3, etc. Of course, Excel gives you =B1, =C1, etc. This is not a substitute for either the Transpose function or copy/Paste Special and che cking the Transpose checkbox. We want a dynamic and simple formula reference as a result. Here's a te chnique that shows you how to do this. 1.

Enter the first formula without"=". Here, B2 was entered in cell B12. The B2 is used because that's where the data starts.

'"I! 1 2 3

4 s_

6 7_ 8

9 10

i

�-+� B..-+ ···""-

J

I )Here's I lth-e r: inf�rmation1 'to

see

left :...._

to right

,

_ _ __ _

11

�l

82


113

2. Fill

right with the fill handle (You'll have

I'1 I A [ 2

Here's

3

the

4

c

.B

i

5

·to

6

see

7

left

8

to

9

right

-

0

E

I

information --

B3, B4, BS,. . . )

10 11

ls2

-

12

B3

B4

I

B5

· �

3. Replace "B" with "=B":

Fi!!dwt>ot:

R�lace wi t h:

�[= s ============="' f :g]

L-----------------------�[o;= p�== M=>>�I •8

1 v,

(Replace !II ) ( B.epla
f[nd All

I ( Eind

Next

I [

J

Close

4. Here's the result: A

1 2 3

I

B

c

I

0

I

Here's the

4

informati on

5

to

6

see

7

left

8

to

9

right

10 11 12

114

IHere's


the

lnformatlc to

E

I

58. Fill "=C3" down, have reference become =03, =E3, etc. This is similar to the preceding tip but the orientation is the reverse. However, this is a lot trickier. If we enter C3 (without the equal sign) and fill down, we'd get C4, CS, etc, the same as if the equal sign were present. We really want the C to become D. E. etc. Here's how: 1. Enter first formula without"=" but in R1 C1 notation so instead of =C3, use r3c3, which means row 3, column 3. c

I o I

-:- .

Here's

fhe

E

1 info

1

Ir3c3 l

T '

G

F to

I

- �ee

I

L

I

H

to

left

J

right

-

1-

-

2. Fill down, giving r3c4, r3c5, etc.

I

c

I

f

0

Here's - the

-

r3c3 r3c4 r3c5 r3c6 r3c7

E

-

-

Info -

to

F

G see

H left

I I •

J

I I to

I I

right

I •


115

3. Switch to R 1 C1

notation via Office button/Excel Options, Formulas: [Excel

2003:ToolsiOptionsiGeneraiiR 1 C1] J)J:cel Optioi'\S

� Change options related to

Popular

11, F'o1mu1as

�

Proofing

formula calculation.

Calculation options

Workbook Calculation())

Save

0 Automatic

Advanced

0 Automatic except for data tables 0 Manual 0 Recalculate w-or�bool:b�•·. ,oving

Customize Add-Ins

Working with f

Trust Center

0

-

Bf

a reference style

0 0 ,Eormula AutoComplete0 0 Use table names in formulas 0 Use Get£ivotData functions for PivotTable references

Resources

4.This step is necessary, because you can't replace r with =r in the above because Excel will give you an error message:

c Here's

D

ov�� ..... ......

F

H

G

I

J

K -

"

� J I An!l

.........

......... .

E

ReQiace

Fi�what:

111-

R�e with:

Jr

·vJ] v] I()p!ions » I

J•r

. � -

Mi-c rocsoft Excel •

-

••

The fornda you typed contains an error,

For lnformotion about fixing common formula probl.,.,, click Help. To get a$slstance in enter'ng a fuoction, dickOK, then dickFunction on the Insert meoo. If you are not ti)'Wlg to enter a formula, avoid using an equal sign (=)or minus sign (-) or precede it with quotation motk ('). • •

•

,

OK

116


II

Help

5. Replace "r" with "=r" (after switching to R 1 C1 notation as indicated above).

I

3

Here's Here's

4

5

6

7

8

9

1

10

ntJ �e� l======================� • R�

FQIV!Nt:

r

v

�== == == == == == == == == � -------------� v

� ' �e�: �

---

ll

[�» Close

�

Excel has completed Its search and has made 5 replacements.

01(

6. Switch back to A1 notation-uncheck the R1 C1 notation box.


117

59. Using Define Names & scrolling

While the Formulas Tab, Define Name dialog is showing [Excel 2003: lnsertiNameiDefine] you can't scroll unless the cursor is in the refers-to box. Just thought you'd like to know!

118


60. Show a picture by typing its name? First, let me demo what we're about to accomplish: I type the word "dog" in cell A 1 and see: A

_!__Idog

I

I

8

c

0

8

c

0

•

2

3 4 5 6 7 8 9 10 cat

dog

11

12 baby •

-

I

I type cat and see:

l

1

I

2

lcat

A

I

I

•

3 4 5 6 7

8 9

�

�

10 cat 11 dog 12 baby 1�


119

I type baby and see:

I

A

1

_:� l J,.::. Ib::.:; ab._ y_J

+f

B

D

c

3

4 5 6 7 8 9

10 cat 11

dog

12

baby

1 '1

The really neat part is that this is done without macros of any sort, just formulas! Imagine having a parts inventory and being able to type in the part number and get a picture! Here's how it's done. The pictures need to already exist in the worksheet and be equally spaced (and out of sight)-here's a 50% view of the work sheet from cell U1:

I

Picture 6 A

·C

1 baby 2 3 4 5 6 7 8 9 10 cat

dog 12 baby 11

13 _10 -1 ,,

120


B

I

/:c =$U$1:$W$10 c

D

E

The dog starts in U 1 , the cat starts in U 1 1 , and the baby picture in U21. That is, all the pictures here are 1 0 rows apart. Next, you need a camera-tool type object whose reference is a defined name, like =pic. See how to get the camera tool into the Quick Access Toolbar in the tip about printing remote areas on the same page earlier in this book. Then, select a 1 0 row x 3 column set of cells near column U-like U 1 :W1 0, then click the camera tool. Then click again near where you want the picture to show up. In our example, that was around cell B2.You should see some thing like this: Picture 6 A

1

...

B

fx =$U$1:$W$10 c

D

E

baby

2 3 4 5 6 7 8 9 10 cat 11 12

dog baby

13 ,. "

Notice the formula bar has a reference to the range originally selected when clicking the camera tool. By the way: this works much better with grid lines turned off, otherwise the grid lines are part of the picture.You also need to format the picture to have no border:


121

Select the

the

picture, use Format picture from

Format button in the

section of the Home tab: [Excel 2003: FormatiPicture]

g albn �m Styles

Conditional format atTab�

.f:!

•

i

J

C:�ll

·

St)'I
K

l

�Jorma::A t C el l Size P.o

![

-

a·

·S
f: l!u - se r.

�el9n;...

� ute:F �

Ro w

He; g�t

C�V:t'rJ .:.::_i�-

�..

n

Auto.=!! Cot..Jmn WC'th

Defa.;.z:t lidth...

Visibility

I II

I

-

J I

Benamt Sheet

or CopySheet... !ab Color Prot ection

I I J I I I

�I

>

Organize Sheets

l

1

I

I H1de &. !Jnhidt �ove

•

-.-. . .-;

frolect Sheet...

)a 18

,

@] �� F.ormat P.icture.,,-• �o
This shows: · · · · · · · >Y ...,. __... ..

1'M'JF.o " " " ' " � " "u v = . � · • .o. �

��!!.��.t?ID and lines I ffPropertiesn I Pltture •. Colors

Size

Fill

�clot:

J.ransp.vency:

IJle

<

I "';'

I

> ·:2

["·N'fL\9k'lld

Con_nector:

122

II


Web

Protection

J

,.

�yle:

Weight:

lo%

�I

cells

Now define Pic as =OFFSET(Sheet1 !$U$1,which-1,0,30,1 5).The U1 is the top left cell for all the pictures;"which" we will define in a moment, the 0 means 0 columns away from column U, and the 30, 15 is the shape of the picture: 30 rows x 1 5 columns. "pix" is the list of legitimate picture names-in this example,A6:A8. "which" is defined as =MATCH (Sheet1 !$A$1 ,pix,0)*30-29.An example will help. To see the dog, we type dog in A1 .The MATCH statement matches A1 against pix and that part of the formula returns 1 since dog is the first item in the range A6:A8. So, 1 *30-29 is 1 : (30-29). So, "which" is 1 , which-1 is 0, and that makes Pic be OFFSET(U1 ,0,0,30,1 5), or the original range U 1 :AI30. So where are we using pic? Remember seeing $U$1 :$AI$30 once we clicked the camera tool? Select that in the formula bar and type =pic:

1

2

I

A

[dog

3

I I •

B

I

c

D

4 5

6 7 8 9

10

]

cat

11 dog 12 baby

One more example and we're done. If we type baby in A1 , then the MATCH statement matches "baby" against pix and that part returns 3 since baby is the third item in the range A6:A8. So 3*30-29 is 61: (90-29). So, "which" is 61, and wh ich-1 is 60.That makes Pic be OFFSET(U 1 ,60,0,30,1 5), or the range U61 :AI90. That exactly covers the picture of the baby, so the picture switches! This is involved, but the impact is great.


123

6 1. Protecting ranges from insertion of rows/columns You can put an array formula to the left or on top of ranges to be protected from inadvertent insertions. Suppose you have a table of values in N33:R70 which isn't always in view, so you (or someone else) might accidentally insert a row at A44 and destroy the integrity of the table (if it were being used in a lookup formula, the blank row in it would likely create errors). In M33:M70, a parallel 1 -column range, array-enter =0 (that's hold ctrl+shift, when entering =0):

M33

LF 32 33 34 35 36 37 38

T

L

(

fxj{=O}

'

[Mj

N

I

0

(2krote_cted_r_
.0

1

0

Pre Pre Protected range Protected range Pre Protected range ,Protected ran$e Pre

..

You'll see {=0}, indicating the range is array-entered. If you try to insert rows you get the "you cannot change part of an array" message:

·

-

�

·

Mi_�;ro�oft �xc�t

_n

You cannot change part of an array.

r· Oii·-'1 ' ttt !t! d " " stt e " "

Notice that the protected range is out of sight and we're trying to insert a row at row 37 which would put a blank into that remote range.

124

This

isn't Excel, it's Magic!

• 62. Numbering Questions tip Take a look at the following: A2

...

A B c 1 Number Tip _?__I'-H---�1! ere's some text

I

fie =MAX($A$1:A1)+1 0

I

E

I

F

3

4

2 Here's some more text

5 3

Perhaps this could be a question which takes more than one line to pose

9

4

And another question

10 11

5 And yet another

6 7 8

12 . ..

You can see that the formula looks at all numbers from A1 (as an absolute reference) to the cell above (as a relative reference), and adds one to the largest number on that range. I n this case, cell A4 has a formula which examines A1 :A3 and takes the largest number (1) and adds 1 to it. So far no big deal.This formula is not being filled down, but is copy/pasted to the first line of each "paragraph." The nice feature with this formula, instead of simply entering the numbers wanted, is that if you decide to delete a question or add a new one, all the other numbers are re-numbered! Let's see what happens when rows 6:8 are deleted, using the Delete Cells command from the Cells section of the Home tab: [Excel 2003: EditjDe/ete]

g

d"" Insert • I: � Delete j:l � • � Qelete Cells... � Delete Sheet ,Bows 'JJ' Delete Sheet �olumm lim Delete �heet "'"

d

1 J


125

The result is:

l

(

A6 A 1

1 Number Tlp 2 3 4

5

6

Br= · r

I =MAX($A$l:A5)+1

fx

•

C(

1

Here's some text

2

Here's some more text

I

0

E

I

F.

�-----3 And another question

7 8

4 Andyet another

9

Notice that cell A6 has the same formula as it did before, but what were questions 4 and S are now 3 and 4! Suppose I wanted to insert a new ques tion before question 4 (let's also assume there are 100 questions!) First select rows 7:9 and use the Insert Cells from the Cells section of the Home tab: [Excel 2003: lnsertiCe/ls)

I a"" ;.a .> .J

�

[31

I

I: .. Insert (£liS

Ia"" Insert �

...

Insert Sheet Rows

Insert St1eet ,\;;olumns In1ert Sheet

Now we have: A7 A 1

B

.

txl

0

E

NumberTip 1 Here's some text 2

Here's some more text

1-1�-6 ��----�3�A�n�d�a�n�o��er�u�e�s�ti�o�n--� 7 8

9. �

-------�

10

4 And yet another

11 12 13

126

This isn't Excel, it s Magic/ '

I type in the new statement and copy/paste any of the formulas in column A to cell AS:

i

AS

A

1 Number

2

j

Tip

8

fx

•

(

c

1 Here's some text

3

D

F.

F

Here s some more text

4

2

6

3 And another

5

I

I:MAX($A$1:A7)+ 1

'

question

87 ,_ ....,4 •The new statement _

9

10 11 12

siAnd yet another

l


12 7

• 63. Mailing Label tricks

-

1

Suppose you have a list of names and addresses arranged like this and you want to rearrange it like the textbox indicates: 1

Address

A

City...

2 Bob's Address ---<

Company

Bob's City, State, Zip

3 Judy's Address

Judy's Company

Jane's City, State, Zip

Herman's Address Herman's City, State, Zip

_L Laird's Address

From this, we want 7 Curtis's Address Bob's Company Jared's Address Bob's Address 9 Stefanie's Addre' Bob's City, State Zip 10 Peter's Address

12 Joan's Address

13 Reid's Address

14 LaVerne's Addre· 15 Eli's Address

16 Oon's Address 17

en ::.

A

! 2J Bob's Address 1 Address

3 Judy's Address

4 Jane's Address

Peter's Company

Alice's Company Joan's Company

\.on:y,-;:,"\.
Oon's City, State, Zip

lcity...

Laird's Company

lp stefanie's Company

�P

If you tried to enter formu las like these in

Herman's Company

Jared's Company

Judy's Company Judy's Address Judy's Oty, State Zip etc

Jane's Company

Curtis's Company

8

11 Alice's Address

c

Bob's Company

Judy's City, State, Zip

4 Jane's Address 5

B

Reid's Company

LaVerne's Company Eli's Company

Don's Company

D2:D4

(showing formulas): c

B

Company Bob's Company

•

Bob's City,State, Zip

Judy's City, state, Zip

I

Judy's Company

Jane's City, state, Zip

Jane's Company

.2_tHerman's Address Herman's City, state, Zip Herman's Company 6

Laird's Address

From this, we want Curtis's Address ..2._t Bob's Company Jared's Address Bob's Address Stefanle's Addre; Bob's City, State Zip Peter's Address Judy's Company 11 Alice's Address 12 Joan's Address Judy'sAddress Judy's Oty, State Zip 13 Reid's Address

Laird's Company

Curtis's Company

.£! 8

14

1 5

LaVerne's Addre! l etc Ell's Address c.n::. "''lY1 .;)ld(�, U'f.J

16 Oon's Address

128

lp Stefanie's Company Peter's Company Alice's Company

J

Don's City, State, Zip

This isn't Excel, it s Magic/ '

Jared's Company

Joan's Company

Reid's Company

p LaVerne's Company Eli's Company

Don's Company

=C2

=A2

=B2

0

which results in:

-+i

A

c

B

D

City...

Company

Bob's Address


Bob's Company

Bob's Company

Judy's Address


Judy's Company

Bob's Address

4 Jane's Address


Jane's Company


1 Address 3

5

_any Herman's Address Herman's City, State, Zip Herman's Comp

6 Laird's Address

7 �

Curtis's Address l;uoti'c: 6tirli'OC<

laird's Company

laird's City, State, Zip

CUrtis's City, State, Zip l:arorl'< ritv <:t�to

CUrtis's Company l::�rorl'c; rnmn::.nv

7in

which seems like a good start, filling this down (starting with selecting D2: DS, including a blank cell) produces a disaster: 06

·� A

1 Address

Bob's Address

t.J=C6 l

c

B

City...

0

Company


Judy's Address


Jane's Address


Bob's Company

Judy's Company

Bob's Address

Jane's Company


Herman's Address Herman's City, State, Zip Herman's Company laird's Address

Laird's City, State, Zip

Laird's Compan'!'

Curtis's Address

Curtis's City, State, Zip

Curtis's Company

8 Jared's Address

Jared's City, State, Zip

Jared's Company

6

7

-

Bob's Company

I

!Laird's Company La i rd'sAddress

I

•

Laird's City, State, Zip

9 Stefanle's Address Stefanle's City, State, Zip Stefanle's company Peter's City, State, Zip 11

Allee's Address

... .. -

. ..

•

•

•

Peter's Comp!ny

Allee's City, State, Zip

Alice's Company

Joan's City, State, Zi p - . .. ... .. .. .

Joan's co�pany .... . .. ...

-·

Peter's Company Peter's Address Peter's City, State, Zip

-what happened to the intermediate companies like in row 3, 4, and 5? Oudy's company...)-notice the formula bar for the active cell has =C6 in it, not =C3. Here are the formulas for the next few rows: 1 Company

c

2 Bob's Company

D =C2

3 Judy's Company

=A2

Jane's Company

=82

6 Laird's Company

=C6

4

5 Herman's Company

7

Curtis's Company

8 Jared's Company 9 Stefanie's Company 10 Peter's Company

A6 =

=86 =C10

11 Allee's Company

=A10

12 Joan's Company . .. - . .. .

=B10


129

You begin to get the picture. The solution? Watch: Enter these values (not formulas!): fir

·C

02

iAddress A

I

I xc2

B

c

D

City Company Bob's Address Bob's City, State, Zip Bob's Company xa Judy's Address Judy's City, State, Zip xa2 Judy's Company 4 Jane's Address Jane's City, State, Zip Jane's Company xb2 5 Herman's Address Herman's City, State, Zip Herman's Company 6 Laird's Address Laird's City, State, Zip Laird's Compa ny 7 Curtis's Address Curtis s Company Curtis's City, State, Zip ...

l

I

'

8 Jared's Address

Jared's City, State, Zip

Jared's Company

10 Peter's Address

Peter's City, State, Zip

Peter s Compa ny

Stefanie's Address Stefanie's City, State, Zip Stefanie's Company

9

� u...-•... ,.. ..-............

••

A lr.,....l,. ,..1••• ,. . •,.•.,.

"'tl-

'

",,_,_ ....--·-·· ..

Now, when you select 02:05 and double-click the fill handle, you get this: A

B

c

D

Address

City

Company

Bob's Address Judy's Address


Bob's Company

xc 2

Judy's City, State, Zip Jane's City, State, Zip

Judy's Company

xa2

...

Jane's Address

Jane's Company

Herman's Address Herman's City, State, Zip Herman's Company Laird s Address Laird's City, State, Zip Laird's Company Curtis's Address Curtis's City, State, Zip Curtis's Company Jared's Address Jared's City, state, Zip Jared's Company Stefanle's Address Stefanle's City, State, Zip Stefanie's C�mpany '

xb2

xc3 xa3 xb3

Peter's City, State, Zip

Peter's Company

Xc4

Allee's City, State, Zip Joan's City, State, Zip

Alice's Company

xa 4

Joan's Company Reid's Company

Reid's City, State, Zip

xb4

Now you can change x to =: c

B

A 1

Address

City

2

Bob's Address


3

J udy's Address


4

Jane's Address


5

H er

6

Laird

7

CurtI

8

Ja re

9 Stet

10 Pete

...

Joan

13

Reid

14 1 "v..

130

xc2

xa2 xb2

-

find dod Replace Fins!

rReQiace I

-

��

fi!ld wh&:

X

v

R�iace woth:

-

v

[ Repi�ce e,ll I [


B.epiace

I I

I I [I Next I I Close I [ Options

11 Aile 12

Company Bob's Company Judy's Company Jane's Company

F!(ld All

Find

»

This gives (formulas are showing) 0

:.,...,.

:�

':.: d ::. : d:,: re :.: s ::= s . ··. '- City,:c + Co :.:n l, :.: p;my '-::c -! ,---::--= ,---,.. ..., . . = ,.,... ,

-

Bob's Ci

lob's Add r ess

udy's Address

-

S t ate, Z i p

-

Judy's C ity, Sta te, Z ip

-

Bob's Company

C2

Judy's Company

=A2

�� -�� -- 1=B2 . � . " �L � terman Find and , Replace [1Jl[J ane's

.

.aird's

Find

:urtis's

FiQd what:

ared's

A.§.Piace with:

:tefani 'eter's .lice's oan's

R ej!lace

"

'

.

'

'

'

� == == == == == == � �= ·= === == == == ==== === =i= ;ijv L· ------------�;===��v [ Op�on s I »

F[nd I I Eind Nex t � ��. �

=C3 =A3

=B3 =C4 =A4 =B4

� � !; ;; � ��!!�� -1

teid's

. a� Ve � rn

iOI I B.ep loce I f��ei.�.i:i.�

a-

AI

=C5

State, Zip Compa ny:li's Address Eli's City, �� - � � � � �� - �Eli's � � � � � - =A5 =B5 >on's Address Don's City, State, Zip Don's Company

And without formulas showing, we have: 1 3

6 7

A

D

Address

City...

Company

Bob's Address


Bob's company

Bob's Company

Ju dy s Address


Judy's Company

Bob's Address


Jane's Company

Bob's City, state, Zip

'

Herman's Address Herman's City, State, Zip Herman's Company

Zip

-,-r --

Laird's Address

Laird's City, State,

Curtis's Address

Curtis's C ty, State, Zip

8 Jared's Address

9

c

B

i

�red's City, sta_t�,Zip.

Laird's Company

Judy s Compa·ny '

Curtis's Company

Judy's Address

J". red's Co �pany


Stefanie's Address Stefanie's City, State, Zip Stefanie's Company

Peter'� <:ity, State, Zip

Peter's COmpany

Alice's City, State, Zip

Alice's Company

Jane's Company ' _ Jane s Address

Joan's City, State, Zip

Joan's company


Sneaky, eh? But this was touted as mailing labels.The format is okay, but how can you make these fit on address label paper? Well, you can tweak the row height of the blank cells. First, you need to make column D be the print area, from the top label to the last line of the bottom label. Check how it would look now, using Print Preview. Make sure there are no labels which are split across pages.You can also adjust the top & bottom margins. If it needs adjustment, select column D, use FS(Goto), Special, select Blanks, and click OK. Now only the blanks are selected. Now use Home Tab, Cells Group, Format com mand, Row Height and make your adjustment, taller or smaller and print preview again. [Excel 2003: FormatiRowiHeight]


131

• 64. Mailing Label tricks

-

2

OK, what about this set of addresses? A 1 Jones, Jim 2 addrt 3 Cltyl Statel, 2ipl 4 S Smith, Bill 6IAddr2 7 Addr2 part 2 8 Clty2, State2 Zlp2 9 e mailaddr 10 11 Johnson, Alice 12 addr3 13 Clty3, State3 Zlp3 14 emalll 151emall2 I 16 POBox 33333 17 I 18 Zedwlch, Paul 19 addr4 I 20 Clty4, State4 Zlp4 I 21 I 22 Bergman, Sylvia 23 addr5 24 Clty5, States Z.lp5

c

B

D

How can you

,

possibly sortthese by name?

i

I_ _

I

I

I

I

Il

I

I lI

T I

I

It's already "arranged:' but what if you need to sort them by name? Not only does this seem impossible, but the num ber of lines in each address is not the same, making it more difficult! Or so it would seem! First, we need to insert a row at the top: A

1

B

c

'

2 Jones, Jim

3 addr1

4

Cityl, Statel, Zipl

6

Smith, Bill

s

1 possiblysort

How can you

by name?

7 Addr2

8 Addr2 part 2

9

Clty2, State2 Zip_2

10 emalladdr

••

132


f I I

r I

these

,

This is so we can enter a very simple formula in B2:

"'I=IF(Al="",A2,Bl} B l I c !Jones, Jim I

B2

i

1

A

Jones, Jim

2

•

3 addrl 4

Cityl, Statel, Zipl

s 6

Smith, Bill

7 Addr2

8 Addr2part 2

Clty2, State2 Zip2

9

10 emailaddr 11

The formula takes advantage of the blank between address-sets, hence the need to insert a row. It says that if the row above is empty, use the name (from A2), otherwise use the row above. Watch what happens when we fill this down (important-include one extra blank row at the bottom to keep the separation between addresses when we sort) B2

.

"

A

__!__ � Jones, Jim

I

3 addrl

4 S

Cltyl, statel, Zipl

..2_Smith, Bill

7 Addr2 _ 8 Addr2 part 2

"'I =IF(Al="",A2,Bl}

c

B

Jones, Jim Jones, Jim

Jones, Jim

Jones, Jim

Smith, Bill Smith, Bill Smith, Bill

9 Clty2, State2Zip2 Smith, Bill 1q_ emalladdr

Smith, Bill

11

Smith, Bill

.Johnson, Alice Johnson, Alice 13 addr3 1� Clty3, state3 Zip3 Johnson, Alice Johnson, Alice 1S emalll 12

Johnson, Alice


133

Why, now we have a field to sort on! __;;-_A

�s 2� Jone , Jim

:::J

8

---,

0

E

F

G

H

J

K

----Jones, JIm

addrl

l

Jones, Jim

tyl. StateI, Zlpl Joo�s. Jim

-------

Jones, Jim Smltl\. Bill

r'lpar12

c.o....

Smith, Bill

_11'-'·tv2. St•te2.Zlpl Smith. Btll

,

11 12 ohnson, 14

oladdr

..,,by

Smith, Bill

13 itddr3

� J rK

s..t On

a...

.,. � :l.! � ·.,., :;;;:; r � v, �::,;; :...,.� .� � � � � �� vl "1•::.::;; .,,--, � � .:;

v

·

Smith, Bill Alice

--

Sorl

Smith, Bill

__

c

Johnson, Ahc Johnson, AUc

State3, 2Jp3 Johnson, Aile ty3, ·

15 emaill

email2

17 POBox 16

33333

Johnson, Aile John$0n. Aile

'"' I I

Johnson, Aile '....

..

. ''

•

resulting in: B

A 1 2

Johnson, Alice

3 addr3 4

S

6

7 8

email1

Johnson, Alice

email2

Johnson, Alice

POBox 33333

Johnson, Alice Johnson, Allee

12

13 Smith, Bill

1S

Addr2 Addr2 part2

Jones, Jim Jones, Jim Jones, Jim Jones, Jim Smith, Bill Smith, Bill Smith,

Bill

16 Clty2, State2, Zlp2

Smith, Bill

17 email addr 18 19 Zedwlck, Paul

Smith, Bill

20 addr4

Zedwick, Paul

All there is left to do is clear column B!

134

Johnson, Alice

Clty3, State3, Zlp3 Johnson, Allee

9 Jones, Jim 10 addr1 11 dtyl, Statel, Zipl 14

Johnson, Alice


Smith, Bill Zedwlck, Paul

cance�

• 65. Bulk formula change Suppose you have a worksheet with references to many sheets like this: I

_

!._[

'

�

=Sheet21$1:?J.048576

2

I ..£ I !I-'! I

fx '=Shee\2!$1:$1043576

(

•

A1

=Shee\3 1$1:$�048576 '

3 ,Sheet4 1 $1: � $ 048576

4

=She e tS!$1: 1 $ 048576

5

=Shee \6!$1:$1048576

6

=Shee tl!$1:$1043576

7

=Shee \8!$1:$1043576

8

�

=Shee \9 ! $1 : $1043576

=She et!0!$1:$1043576

10 =Sheettl!$1:$1043576 �1 =Shee\12!$1:$1043576 12 =Shee t BI$1:$10435 76

- --

13 =Shee 4 !$1:$1 04357 6 \1

.

14 =Shee \15!$1:$1 04357 6

And you want to put in column B these formulas corresponding to what's in column A: =COUNTA(INDIRECT("Sheet2!1 :1 048576")) in B1 through =COUNTA(INDIRECT("Sheet16!1 :1 048576")) in B16. It could be done with a VBA routine, but here's a way to do it with formulas: Look at the formulas in B here (The cells are all selected from the result of a Fill/Down): i

I

fx ="=COUNTA( INDIRECT("""&MIO(Al,2,255)&"""))"

B1 A

(

(

B

II

1 �heet2!$1:$1048576

=COUNTA IN01RECT "Sheet2!$1:$1048576"

2

�heet3!$1:$1048576

=COUNTA(INOIRECT("Sheet3 !$1:$1048576"))

3

�heet4!$1:$1048576

=COUNTA(INDIRECT("Sheet4!$1:$1048576"))

4 �heets!$1:$1048576

=COUNTA(INDIRECT("Sheets!$1:$1048576"))

�heet6!$1:$1048576

=COUNTA(INDIRECT("Sheet6!$1:$1048576"))

5

6 �heet7!$1:$1048576

=COUNTA()NOIRECT("Sheet7!$1:$1048576"tJ

�heetS!$1:$1048576

=COUNTA(INOIRECT("Sheet8!$1:$1048576"))

8 �heet9!$1:$1048576

=COUNTA(INOIRECT("Sheet9!?1:$1048576"))

7

-

•

9 �heetl0!$1:$1048576 =COUNTA(INDIRECT("Sheetl0!$1:$1048576"))

10 �heetl1!$1:$1048576 -

=COUNTA(INDIRECT("Sheetll!$1:$1048576"})

11 �heetl2!$1:$1048576 =COUNTA(INDIRECT("Sheetl2!$1:$1048576")) . ...

-t"t-......�ot .. "\ tf"o1 .c'1 t\_.tH:"7C

_ ,...1"'\1 I "I'TA I I"I r"\ 1 n ,.�lli t"I.. .... ,....a.-

1 ...

I

cA-1 , t'-1 n ..

nc-,cII'\

The formula in B1 is ="=COUNTA(INDI RECT("""&MI D(A 1 ,2,255)&"""))" This is understood by Excel to simply be a bunch of text made to look like a formula, but it does not act like one. Since it looks like the right one, let's copy/paste special values (by the trick of dragging the border away & back to the same spot which gives a dropdown to choose values):


135

I ="=COUNTA( INDIRECT("""&MIO(Al,2,255)&"""))" B

c

I

0

E

I

= COUNTA(INOIRECT("Sheet2!$1:$1048576")) � ------------�--��------�--�-+� �--�� - =COUNTA(INOIRECT("Sheet3!$1:$1048576")) =COUNTA(INOIRECT("Sheet4!$1:$1048576"))

Move Here

=COUNTA(INOIRECT("Sheets!$1:$1048576"))

.!;opy Here


Copy Here as formats


1 ink Here

=COUNTA(INOIRECT("Sheetll!$1:$1048576"))

�tuft Ot> n ancJ Cop_


=COUNTA(INOIRECT("SheetS!$1:$1048576"))

=COUNTA(INOIRECT("Sheetl0!$1:$1048576"))

Copy Here as �alues Only Only

Create!jype�ink Here

So now it looks like the formula we want, even in the formula bar:

Bl

fx =COUNTA(INOIRECT("Sheet2!$1:$10485.

•

A

B

1 =Sheetl!$1:$1048576

=COUNTA(INDIRECT("Sheetl!$1:$10485 71

2 =Sheet3!$1:$1048576

=COUNTA{INDIRECT("Sheet3!$1:$1048571

4

=Sheet4!$1:$1048576

=COUNTA(INDIRECT("Sheet4!$1:$1048571

=SheetS!$1:$1048576

=COUNTA{INDIRECT("SheetS!$1:$1048571

5

=Sheet6!$1:$1048576

=COUNTA(INDIRECT{"Sheet6!$1:$1048571

6 =Sheet7!$1:$1048576

=COUNTA(INDIRECT("Sheet7!$1:$104857•

3

But it's not. We once again need to Replace"=" with"=": L -

-

•

!ID-

¥

(2 ·

ot •

=� =

rg

Sort & Find & Filter · Select •

find

...

-

•

Replace

q

�o To...

�ac

btl

...

Go To Special...

Formylas Comments Conditional Formatting

Constants Data Validation

�

� 136


Select Qbjects Selection fane . ..

And the final result is: 81 1 2 3 4 5

6 7 8

9

10 11 1?

�(

fx =COUNTA(INDIRECT("Sheetl!$1:$1048576"))

A =Sheet2!$1:$1048576 =Sheet3!$1:$1048576 =Sheet41$1:$1048576 =SheetS!$1:$1048576 =Sheet6!$1:$1048576 =Sheet7!$1:$1048576 =SheetS!$1:$1048576 =Sheet
:<;h<><>t1 ':II �1·�11\411<;7/i

8

45

c

I

D

0 1 1 1 1 1 1 1 1 1 1


13 7

• 66. Fun with relatively defined names If, while on cell A 1, you define a name, z, to be

=!A1 :Z1 00

��

like shown:

, .,. � · · � · � ..,..� N ew N am e� � .... ;._ '!:. _,. ... .. � ---'"""' , � �:

z

�ope:

C�:

aefers to:

lwort
ltAI:ZIOO

�I

I

,.. -

-

[

-

�I

OK

[�� I

Canc el

then you can use this name to scroll the active cell to the top left of the worksheet! For example, suppose you have a worksheet which looks like this:

-�t , �0 .1 ;•,l

� t�
) d .J:;:!

J

1d.)UI

-

• 'CftiUI .S -(u:.a

-�d�t�

·�

-

-

,...

,.

C�1.J

..... cf)1<1

4.1�.1 on.-

1 4.1"...,

6J14

• 4.1t.1

C..1•

c

d•:•

;.i IS>�

.L

0

dat::�

dt.lo

chta

dJlA

(t,n;t

dJtf

�.,.

d.t!lt

d..at•

d.1Uo

�1..l

clA!•

dlol
doll..

.,,,.

.....

data

.,... d•t•

d&tl

4.1t<1!

Cb!.l

...,.

d.){,\

u
c t..a b

l.S �tl

16 �Jt"' "

d•t.t

..... .

d>t•

cUI>

C'.Jt.1

.....

d .) I ,\

...,.

d1U

cs..:.a ....

.,.,. dm

... , .

4-W.li

....

CI.U:t

d..lt•

I»til d.l !.-

d.!•

...,.

.....

tht,\

...,.

"'"

.,... d.i�• d.at• 4.la

.,.,.

11 -· "

�t.l "''"' �:..

......

""''

"

21

N

�t:;tl

d>l>l

�t�

4.1�2

c!JtU

d.Jt..2

d..�.l

dJit.l1

chtll

&Ul

<1>1> 6>1.> ..... .... d>U

....

�'!�

d.ttf

c:..-:�2

ct :.:.1 .. �:..:

dl�d -

:o.s

....

.,.,.

d.tt..1

-2l

l

dill�

d4.. :z 4-f�.-2

n

�

di!.�

dt!.-t

;o

I -

J

"'"'

cu•

u I;!J �

M -

d �.. ••

......

0 d)tla

1

dau

d�t.li

c!Jll

(1..;11:1

'

Cl•l.l

.,.,,

u � ; .,

d.ltil

l

d)!ll

.,.,.

"""

(

dUI

' .,.,.

.:0 CI;U

I

d�!.\1

tJ�l cla:.-2 1!��1:

C..St-11

t\1:...1 .:.Jt-�2 d.lW . e� .t ..a

��: ..

IC.lt.f)

d.1ta2 d.-t.l)

..w

d.-�J

�:.a

d;):.C

d.at.l.l

d.J•:.u """'t

d>u> cb.al

d.ii\#1

d.at�

d .. tt 1 CU '::OIJ

dJu:

d.t t.tl 4Jt�-J

......,

d.-:#l.

"' "'

d.l:42

t.:.�.,1

..... .

dnll

d.1l.t7

4.1-�.u

d.U.U'

d>t>l

and you'd like to see more of the data starting in cell 115. Certainly you can scroll both horizontally and vertically, but you can also take advantage of this newly defined name, z. Click on cell l15, then use FS and Goto z.The result will be this:

138


115

J

·� I

I

fx

data2

l

K

data2

data2

M

N

data2

data2

data

I

15 data2

data2

16 data2

data2

data2

data2

data2

data2

data

17 data2

data2

data2

data2

data2

data2

data

18 data2

data2

data2

data2

data2

data2

data

data2

data2

data2

data2

data2

data

20 data2

data2

data2

data2

data2

data2

data

21 data2

data2

data2

data2

data2

data2

data

23 data2

data2

data2

data2

data2

data2

data

data2

data2

data2

data2

data2

data

24 data2

data2

data2

data2

data2

data2

data

25 data2

data2

data2

data2

data2

data2

data

26 data2

data2

data2

data2

data2

data2

data

27 data2

data2

data2

data2

data2

data2

data

28 data2

data2

data2

data2

data2

data2

data

'q rl�t<>7

rl�t�')

rl�t::t?

ri::lt::t'

ri ::l t::l.,

-

19 data2 -

22 data2

-

rl�t�7

rl�t<>

Notice that the cell has been scrolled to the top left. That's because, from cell 115, the definition of z is =!I1 S:AH 1 1 4 as in this illustration: tjew...

I[

t
··

I[

Veluo

Qelt
Refe.-s TO

fi l t e r .. ComMent

/

- a relative reference to the active cell resized by 100 rows and 26 col umns. In order for Excel to show as much as it can of this range, it scrolls the worksheet! Z is defined with a leading exclamation mark so that the sheet name is not appended to the name, and you can use z to be a 1 00x26 shape in any sheet!


139

• 6 7. Using notes inside formulas via the N-function

The N function, shown here:

� � "' = •

l!J�,I!fl_,t�nc,ti�!l=�

��

�arch for a functcon:

Or sele
IL_ An

��

[

I

_ _ _ _ _ _ _ _ _

MIRR MMIULT

N(value) Converts non-number value to ·a number,.- dates to.serrai numbers,· TRUE to I, anything else to 0 (zero).

converts non-number to a number, dates to serial numbers,True to 1 , anything else to 0. Well, you can take advantage of this knowledge inside a formula. For example:

, -'"',] •VLOOK UP ( B22,x yztable, 3,FALSE)+ N(" T his will search the table for the z·factor")

- c l o l e

F ! G ' H :

: J 1 �

Io.otamJ This formula now explains that the VLOOKUP is searching xyztable's third column for the occurrence of cell B22's value for the z-factor (whatever that is). The point being that the part of the formula, N ("This will search the table for the z-factor") will be zero. not effecting the result. So you can use the N-function inside formulas like this to document or comment on a part of the formula by effectively adding 0.

140


• 68. New Table features If you have entered a table (new to 2007), it is automatically assigned a name, like Table1, etc. You can reference parts of the table in formulas. Here is a list of some of the new features. If you enter the beginning of a formula for a table and include the opening bracket, like =table1 � Excel provides an intellisense tool tip: 8

5

•

0

£

f

G

I

(

J

,K_ .l

l

t

l

M

l

4

� 1

c

...,. ·liN;�· •:ot.ak

"n.,.II.Oort

The small text says: "Returns the entire contents of the table, or specified table columns includ ing column headers, data and total rows" It provides a reference to the entire table, so you can sum it, for example, without knowing the cell addresses. =SUM(Table1 [#All]) might include a total row, if present, so the above illustration would give 20 in this example. Using =SUM(Table1 [#Data]) would yield 10.


141

To get the headers, you could ctrl/shift/enter =table1 [#Headers] if there were more than one column in the table, or simply enter the formula (with out ctrl+shift) if there was only one column:

IAI Illata

,

ITotals

.tllisRow 01

B I data2 a 4 1 2 3 3 2 4 1,

1 2 3 4 5

c

'

fxlt=Table1(#Headers]} 0

loata

E data2

I

!

F

Here are the last 2:

Data #All #Data #Headers

[Returns the total rows for the table or specified table columnsI

03 2 3 4 5

142

1 2 3 4


•

5 1 2 3,

c

,.

I

E

0 3!

F

• 69. New Excel functions Excel 2007 comes with 5 new built-in functions (excluding the new CUBE functions): SUMIFS,AVERAGEIFS, COUNTI FS,AVERAGEIF, and IFERROR. Let's take a brief look at them in action: E3 1 2

3 4 5

6 7

11 12 13 14 15 16 17 18

A Region East North East West South West East

fx =SUMIFS(Amount,Region,''South",Oate,"<4/06")

�

8 Amount

I

c

0

Date 558 29·May·05 43 14-Apr-07 826 28-0ct-04 765 3·Apr·09 271 14-Feb-10 415 17-Jun-05 402 16-Mar-07 633 15-Nov-05 16 9-Aug-05 198 20-Jan-06 741 26-0ct-10 559 5-0eo-08 263 30-Apr-08 809 27·NOV·08 884 23-Aug-07 894 10-0ec-08 942 13·Mar·ll

East West East East North South West North

I

I

E

F

G

H

1&51

I

11

•

508.4090909

481.2837838 22

Not Found Not Found

Here, we see the SUMIFS function. Its syntax is SUMIFS (Sum_range,criteria_ range1,criteria1, ... ) which basically states which range to sum based on pairs of criteria ranges and their criteria. This example uses Amount (column B) as the range to sum, and there are 2 pairs of criteria: the region (column A) must be "South," and the Date (Column C) must be prior to April, 2006. The next function we take a brief look at is the AVERAGEIFS, which is basi cally the same as the SUMIFS, but the Average is taken instead of the Sum: ,

ES

A 1 Region East

North

8

fx =AVERAGEIFS(Amount,Region,"South",Oate,"<4/06")

Amount

c

Date 558 29-May-05 43 14-Apr-07 826 28-0ct-04 765 3-Apr-09 271 14-Feb-10

0

E

F

G

H

111&5

Isos.4o909o9!

The AVE RAGEIF function is like the AVERAG EIFS function, except there's only one pair of criteria:


143

A

1

"' =AVERAGElF(Region,"East",Amount}

•

E7

Region

2 East

I

B

c

Amount

558

Date

29-May-05

4 East

826

28-0ct-04

6 South

765

3-Apr-09

West

F

j

508-4090909

1481-2837838!

415 17-Jun-05

F�
I

E

ll185

271 14-Feb-10

Jlwest R

I

43 14-Apr-07

3 North s

D

I

40, 11\-M�r-07

The COUNTIFS fun ction is like the SUMIFS and AVERAGEIFS functions, except the range to sum (or average) is not included. It's still pairs of criteria, but they're merely counted: E9

A

Region

•

B

c

Amount

East

14-Apr-07

11185

3-Apr-09

508.4090909

826 28-0ct-04 765

West

6 South

271 14-Feb-10

7

415

West

17-Jun-05

481.2837838

402 16-Mar-07

East

9 South 10 South

F

Date 43

4 East

8

E

D

558 29-May-05

North 5

"' =COUNTIFS(Region,"South",Date,"
16

221

I

633 15-Nov-05

9-Aue-05

•

You can calculate that 1 1 185 I 22 is 508.4090909, verifying that the SUM I COUNT is the AVERAGE! The last new function is the IFERROR function, which basically shortens something like this: I=tf(ISERROR(Vl0UP{ 0K "Herman",A2:CS0,2,FAISE)),"Not Found",VlOOKUP("Herman",A2:CS0,2,FAISE))

to this:

o l E 1 INotFound !

I

F

1

G

H

K

J

t 1 M

=IFERROR(VLOOKUP("Herman",A2:C50,2,FALSE},"Not Found"} D

E

F

G

H

I

INot Found ! The syntax isjiFERROR(Val.;e,value_if_worJ which means that if it is NOT an error, it returns the value of the calculation, as in the first longer example above. In Engli sh it's "If the VLOOKUP returns an error, show "Not Found." Other wise, do the VLOOKUP." But this requires you to code the sameVLOOKUP formula twice in the same function. The IFERROR avoids that! ,

144


70. Ctri/Shiftl-, 1,2,3,4,5,6 for quick formatting These simple keyboard shortcuts can help with number formatting (here, the value 1.5 was entered in the cell): Ctrl+sh ift+:

Example:

1

Resulting Format: General 2 decimals

2

Time format

1 2:00 PM

Date format

1 -Jan-00

C u rrency

$1.50

Percent

150%

-

3

4

5

6

Sc ientific

1.5 1.50

1 .50E+OO


•

145

7 1. Borders from keyboard Ctri/Sh ift/7 creates outline border Ctri/Shiftl- removes border

146


72. 1nserting Date & Time shortcuts Ctri/Semicolon (Ctrl/;) will insert the date in m/d/yyyy format; Ctri/Colon (Ctrl/:) will insert the time in h:mm AM/PM format.


14 7

73. Click the AutoSum button from the keyboard

Altl= is the same as clicking the AutoSum tool. Altl= twice (quickly) will also enter the result.

148


74. Bringing the selection into view It's possible you've selected some area of cells and then scrolled away so you can't see it any more. Ctri/Backspace brings that selection into view, and shift/Backspace brings selection into view as well but reduces the selection to the active cell. So, if this is the before picture: I 1

2 3 4 5 6

A

I I

B

I

c

I •

then shift/backspace will simply have cell B3 selected.


149

75. Ctrll[ more powerful than ,, its "equivalent Go To Precedents If a cell has =SUM (B2:E4), then with that cell selected, use FS (Goto) I OSpecial and select Precedents. The result is that cells B2:E4 will be selected. Ctrll[ does the same thing. However, if a cell has a link to a cell in a closed workbook (the entire path would be shown in the cell), then using FS (Goto)ISpeciai/Precedents would give an error message: -

Microsoft

�

Excel

No cells were found.

_G

�."..........".............1

i

'

OK

i

whereas ctrll[ would open the file, switch to the appropriate sheet, and select the cell! The FS (Goto) I Special dialog also has an "All levels" option button when you select Precedents (or Dependents). This means that if, in the first exam ple above, cell C3 had a formula which referenced H12, the all-levels option would also select cell H 1 2 (in addition to B2:E4). -

G.o To Special

C8J

Select

0 !:;_ome m nts

0 Ro�differences

0 Eotmulas

0 erecedents

0 C2f'St4ntS

0 Column differences 0 Dependents O D!redonly 0iAif!eveiSl. ·-·-···-··..--

0 La�t cell Oal�n�

0 Visible cells onl�::

0 Current (B!jion

0 Conditional formats

0 Current �rray

0 Data �alidation All

Oo�Vects .--� OK

Same

J [

Cancel

The equivalent keyboard shortcut is ctrl/shift/[.

150


]

76. Show Corners of Selection Easily Ctrllperiod successively selects corners of the selection is large, as in A1 :DD799.

selection, even if the entire


151

77. Anchoring the active cell Holding the shift key down while you click a cell, or double-click a border, or even in the GoTo dialog (FS) will keep that cell as the active cell. If cell C4 is active, as shown, and you use the GoTo (FS) dialog and enter e9 as the cell to go to:

I c

D

H F I G � -

q�T!J

��.

!iO to:

I

I

E I

.

....

•

�

. •

I_

vi

!l.eference:

1•91

I SPecial.

• .

l It

OK .. .

JI

Ca ncel .

. . .

I. . l

then if you hold the Shift key down when you click the OK button, this will be your result: A 1 2 3

4

-

5

8

I

c

I

----1 6 7

11 10

I

In other words, the original active cell will still be the active cell. If you shift click cell A2, now, the selection will be A2:C4, with C4 being the active cell. Since Ctrl/down arrow (or right/left/up arrow) takes you to the end of a block, shift/ctrl/arrow will also take you to the end of the block, but the active cell won't change!

152


• 78. Finding after the Find dialog is closed

Shift/F4 repeats the Find command after a find was done; Shift/Ctri/F4 repeats find backwards! That is, if you did a normal Find command to find something: �

fi.t��t !'. !'��.._�P}�_e _, .� � �

IOpl;io,">S »II then if you close the find co mmand and use Shift/F4, it will still find the same text, and Sh ift/Ctri/F4 will find the same text backwards.


153

• 79. A few miscellaneous Shortcuts

Ctrl/' (single apostrophe)-this copies the cell above exactly. So, f i cell A1 has =SUM($B2:C7), then pressing Ctrl/' from cell A2 will put =SUM($B2:C7) in cell A2. Ctrll" (quote)-this copies the value from the cell above. So f i cell A 1 has =SUM(E1 :E1 0) and its value is 350, then pressing Ctrl/" from cell A2 will put the plain number 350 in cell A2. Ctrl/; will put in today's date. Ctrl/: will put in the time. If you press Ctrl/; followed by a space then Ctrl/:, you ll have the date and the time. Sh ift/F2 will insert a Comment. F9 will calculate the open workbooks; Shift/F9 will calculate the active workbook. • Ct ri/T or Ctri/L will insert a Table. • Alt/T/0 will get you to 2003's equivalent ofTools/Options. Alt/T/1 will get you to 2003's equivalent ofTools/Add-ins. Alt!V/U is Full Screen Alt/F/<1-n> is File/Open . •

•

•

•

•

'

•

•

•

•

•

154


80. Sorting columns This is useful for rearranging columns and is often easier that cut/insert paste to move the columns around. You may have to use a "dummy" row to enter the sort sequence. In this worksheet, a dummy row was inserted (row 1 ) to enter the sort sequence. We want Name, then Address. then City, State, and Zip: A

1

Address

2

I

21

B

1

Name

•

3 123 Main Name1 4 124 Main Name2 5 125 Main Name3 6 126 Main Name4 7 127 Main NameS 8 128 Main Name6

4

State

E

0

c

3

City

Orlando

Fl

NV

Albany

TX

Houston

CA

San Diego

WA

Red

Fl

Zip

5

12345 12346

12347 12348 12349 12350

mond

Tallahassee

Use Home tab, Editing section, Sort & Filter, and Custom Sort [Excel 2003: Data/Sort]:

�: u �

•

0..

�� X� inJ

Sort & Find &

Filt er • Select •

•

largest

�ort Smallest to

S2rt largest to Smallest

Custom Sort...

Y=

filter

1�

f:e•ppty

Click the "Options" button, click Sort Left-to-Right,

I.)!1Add

][X !!dote

lev el

Coi.Jmn

Sortby · ,-

-

��

,__ _ _ _ -

J I-b �Y

LOYd

II

level

,)

•

IQptions...I

0 MydatahastJoaclors

ar d..

,

.. to z

., 0 ,••• ..sitiv. Orie:nUaion t

0 Sort \Of> t4 bottom ®1' . • t��u��-�.!!2�J ....

_.......,_,•..•.,_,1

..

OK

II

Cancel OK

cnce a l


155

click OK, --

Sort

'Row

SOrt by

•

SOr t On

l�

0

IQptions.·•I

Order

t

then pick the row containing the desired column sequence (Row case), and finally click OK: A

IName 1•1

1 -

2 3

7

c

B

Address

2

0 3

City

E 4

State

5 Zip

Name1

123 Main Orlando

Fl

12345

Name2

124 Main Albany

NV

12346

Name3

125 Main Redmond WA

12347

Name4

126 Main Houston

12348

NameS

127 Main San Diego

8 Name6

156

I

TX

CA

128 Main Tallahasse FL


12349 12350

1

in this

81. Making exact copies of ranges with relative or mixed references This technique is useful if you have many cells to copy-otherwise, it's pretty simple to copy the formula directly from the formula bar. Usually, if you copy a range which has relative or mixed references to a new range, the referenc es will adjust. That is, if A1 contains =C7 and you copy A1:82 to C2:D3, then C2 will contain =ES. Here's how you can have C2 still contain =C7. We'll use the above references as an example. To copy A1:82 to C2:D3 and not change any references in the resulting range: Here are the formulas we're starting with, using relative, mixed, and absolute references: B

A 1

=D$7 =$0$8

=C7

2 =$C8 :� I

•

And here's the worksheet displayed normally:

1

2

.--.:..:. A��

labc ! def ghi

c

8

D

jkl

3 4

s 6 7

abc

def

8

ghi

jld

9


15 7

1 . Use a 2nd worksheet in group edit mode. To do that. Ctrl/click a second (unused) sheet tab:

Book1 [Group] Micrc c6;;\ [) Q � • J £3 .2iJ J> . -� HomejInsertIPage layou-1formulas I DataIReview Vi!w�vel , ;

�

_j

Paste •

I

� '-cl

J

Clipboard r.

A1

jabc

1 -

2

ghi

3

A

• 11

Cahbri

-

%

•

I B I u ·I K .£1 � ��·I IEB · I �· A ·

•fldef

Font

B

•

I

.fx

!

r.

c

D

I

--.

-

jkl

•

Numbu Sl) r.

Alignment

=C7

1

E

I

F

4 5 6

-

7 8

abc ghl

9

10 ••

•

�

Ready

def ]kl

27Sheet 37tl7 �11Sheetl/Sheet Num lock

�

lil41 l�!m [Q]J!!!JLlOOj

(Notice both sheets 1 and 2 are selected, and there's another indication you're in group mode-at the very top of the screen you can see "[Group]."

158


2. Select A1 :B2, use Across Worksheets in the Editing section of the Home tab (this is dimmed when you're not in group edit mode) [Excel 2003: Edirl Fill/Across Worksheets]: Home

Sort &

•

Folter

Edotm

Find &

Select ·

tiJ

=

Down

11! BighE

=

I

IQI llP � ),eft

�cross Worksheets...

�eries...

!usttfy

3.You're presented with this dialog

Fill Across Workshe. . . Fill

0:...... A !�.....IH · :

. ,

0 Contents 0 Formats

I[

OK

[

I

Cancel

4. Now, Sheet2 contains the exact same references in A1 :B2 as Sheet1 contains. S. Activate the 2nd worksheet (not in group edit)-you do this by shift/click ing on Sheet2's tab. Since we didn't copy C7:D8 as well, you only see O's in Sheet2, but that's fine. 6. In Sheet2, Cut A1 :B2 and paste to C2:

[ I �f A

I =C7

fx

Al

B

I

1

0

0

2

0

0

c

•

3 This isn't Excel, it's Magic/

159

You can see in the formula bar that cell C2 still contains the reference to C7. This is because the references don't change in a cut/paste, only in copy/paste. 7. Back to group edit (with Sheet2 active, ctrl/click Sheet1 ) select C2:D 3, use Across Worksheets from the Editing section of the Home tab, [Excel 2003: ,

EditiFilljAcross Worksheets]

Down

[QI

� .Bight

tm

Up

.!,eft

�

l

l!cross Workshee!s.,.=-o: �eries...

·l

lu�tlfy

filling the 2nd sheet back to first. Shift/click Sheet1 and you'll see: C2

•

A rr!l _abc

l

�

-

2 ghi 3

def

B

jkl

t�

= C7

jl;� c· .li

def

I

abc

jkl

ghi

4 5 6

I

0

.

.

G.

I

I

H

I

I

•

jkl

ghi

8

I

E

def

abc

7

"o-1

9

10 the formulas are...

��:

C2

j:== 14 1 =C7 ;.. 15 2 =$C8 1 3

�

16

3

18

5

17 4

19

6

�

8

20

7

•( A

t.J =C7 -

=0$7 =$0$8

r I

B

.

�

1:�8

G

0 =0$7

=$0$8

I [ abc

j gh .::_ I

-

I

•

I

J�. ::::.:. ef ...

_Jijkl

_

_ _ _ _

Another way (maybe easier) would be to replace the"=" in the formulas with something like "x=" (turning formulas to text), then copy/paste to the new location, then replace "x=" with "=" in both ranges!

160


82. Filling Holes Suppose you have a worksheet something like this: A1 A

�

1

ate

2 PA 3

·(

f.< 8

JRegion North

J

State c

Amount

300

04 1

4

1 9 5

s 6

749

SOuth

7

700 348

508

8

935

9

582

10

11 WA

North

13

south

12

14

377

936 831 2 4 3

344

15

16 NY

North

18

South

97

120

17

577 955

19

And you'd like to sort it by state! Yikes.You KNOW it would be quite messed up because of the holes (empty cells). ln this example it wouldn't take too long to fill PA down from A2:A 10, then WA from A1 1 to A1 S, etc. and then it'd be easy to sort, but if this extended to row 6000, forget it! Well, there's a pretty easy way: 1. Select cells, use FS key, click Special, select Blanks:

I

83 A

1 State 2 PA 3

·{

8

Region North

4

I

6

South

s

7

J.
c

Am oun t

300

104

159 49 7

700

348 508

8

935

9

582

10

11 WA

North

377

13

south

831

12 14

16 N Y

North

18

south

17

••

243

344

5 1

19

936

97

120

577

955


161

2. Type "=," press the up-arrow, then press Ctri/Enter: 83

�Stat:

1

2

PA

3 PA 4 PA s PA

6 PA

.c�

1-

I=sz

.r.

s_] c

Region

North

l!::!orth North North

Amount

300 104 1S9 749

7 PA

SOUth

700

south

348

8 PA

9 PA

South

508

10 PA

South

93S

11 WA

South

582

North

3n

12 WA

North

936

13 WA

South

831

1 WA 4

South

2 43

15 WA 16 NY

17 NY

18 NY 19 NY '"

south

344

North

97

North

120

South

577

South

95S

3. What? It can't be that easy! What happened? Look at cell B3: It says =B2, or the cell above. Ctrl-enter says "fill the selected cells with the formula," so every blank cell references the cell above. So A1 2 says =A 1 1 , etc. Now the sort is trivial. And if you want to clear the formulas after the sort, simply select 1 cell, use the FS key, click Special, select Formulas & OK, then press the delete key to produce the sorted result.

==: '

= A

1 State 2 NY 3 4

_, 5

6 PA

I

a

= •

Reglon

h Nort

I

Sout h

North

11

South

18

19

on

162


300

749

700 348

93S

14

17

sn

508

13

16

120

1S9

12

5 WA 1

97

104

8 10

Amount

955

7

9

c .J'--�

5 82

North

3 17

936 south

831 243 344

83. Aligning objects When a drawing object is selected, you will see the Drawing Tools context sensitive menu show up. If you've inserted buttons or option buttons, check boxes and the like, then this isn't a "drawing object" so the context sensitive menu is not present, but you can still get the desired commands in the Page Layout tab, the Arrange group. In the Arrange group, is the Align button: �9�t.J0Ut

IS

f:otnlulas

16 Ci

OntWtiOn

Sao

RnltW

D•t•

,L..J1

"""' lfu"-1 a.ru·

agt Sttup P

Yi
= •

� ..:1

&actoround Print

T•Utl

'"

......

Orrtfop�r

..... • ., .

.

Wtdth: AUt o• mc -

U } Ht1ghl: aJ Sal"

Gridlin.t:J

� Vl.w

Automlti< • """ '

1iudin9s

.J View

4Srif\0tofront � AMon ·I - !ljGroup• �S.endt

Pnnt

SUi t to fll

t!: 'S h f.tt Op

"

�

•

tKtion Pane

Atrangt

• o'•tt

(by the way, if one object is selected, you can select all objects by ctrl/sh ift/ spacebar-if an object is not selected when you press ctrl/shift/spacebar, you will first select the current region, and if you do it again, you will select all cells): [Excel 2003:View1Too/bars1Drawing ToolbarlDraw menu ArrowlAiign or Distribute] Drawing Tools 1-Ins

t\

_r tyles

J

Format

A · 't;h Bring to Front � - � Send to Back • <:\ � Selection Pane -

•

•

•

r,

K

L

M

•

I�

Align !,eft

�I

Align Right

oJ\}

Align Middle

.$.

Align £enter

'iii

Align!op

-

wdl

Align §ottom

D!}c

Distribute Horizontally

g

1:t

Distribute Vertically Sna.2 to Grid .S.nap to Shape View Gridlines


163

·

For example, if you use Distribute Vertically on the left worksheet with the objects selected... you'II see them as on the right: c t1• ··1.-.t: ! J tt � 'i: ! � : i t ! Ol, i J

'

0 ® Option Button I

! o· ·�o �

@Option Button I

0 Option Button 2

0Option Button 3

0Option Button 4

0 Option Button 5

164


84. Leaving cursor in the cell after pressing enter. If the cursor normally moves out of the cell when you enter a value and you temporarily want to keep it in the same cell, simply press ctrl/enter instead of enter! This assumes you have only 1 cell selected, else all the cells will be filled!


165

85. Shortcut 3-D Formula If you wanted to add up all the sheets' ceiiA1,you could enter this formula: =SUM (Sheet1 :Sheet6!A1 ) But there's a shortcut where you can use a wild card to do it and Excel will change what you type into the correct reference. One caveat: this shortcut excludes the sheet you're entering it on in the reference. Here's an Example. Enter =SUM('*'!A1 ) and when you press enter it will change: .

CUBEMEM. . .

...

� Book3

IF:ll 1

:X ./ fx =sum('*'!al)

(

.

I

A

I

B

=sum('*'!al} •

c

0

I

2 -

fx �UM(Sheet2:Sheet6!Al}

...

Al

� Book3

o

A

1

B

0

c

I

•

2

0

-r

E

!

But there's another advantage to the shortcut. If you enter it in Sheet3, for example, you'd see =SUM(Sheet1 :Sheet2!A1 ,Sheet4:Sheet6!A1 ) in the for mula bar after entering =SUM ('*'!A1 ) .

Another variety of this tip is to use parts of sheet names. For example, if your sheet tabs are named Sheet1, Bob1, Sheet2, MyBobDatabase, Bonbon, Sheet3, Bonfire, and on sheet2 you enter the formula =SUM( *bo* !A1 ), Excel will expand it to include all the sheets containing "bo" somewhere in the name: '

C5

•

� Book3

A

1 2

.fx

�

I

B

I

'

=SUM('Bobl'!Al,MyBobDatabase:Bonbon!Al,Bonfire!Al)

c

I

D

1

E

I

F

I

G

I

H

T

I

3 4 5

I

6

I�

4�

166

ol

•

�11-_Sheetl/Bobll Sheet2Ll�yBobDatabase /BonbonL"Sheet3LBonfireL�./1 This isn't Excel, it's Magic!

86. Pulling cells together If you have data separated by many rows that you want to put in a contigu ous range of cells (see below), this will show you how you can do it easily.

l1

1

2 3

A want

I

I

B

•

these cells

4 5 6 7

to

8 9

all be

10 11

12

.

13 together.

-

• •

1. Select

the column containing the data 2. Use FS (go to)/Special, selecting formulas or constants .--- A

-

c I. s _, I Go To Special

_ -

_! jwant 2

3

4

these cells

16 .

...

�

0Eormulas

to

8 9 10 all be 11 12 1� together. 14 15

Oconvnents �

6

7

Seled

®'Constants'

5

D

F

E

• @X

---------

0Nvmbers 0Te;!lt 0Logicals 0�rrors Oelan�

0 Current tegion 0 Current �rray

0RO!! differences 0 Column differences 0 e_recedents 0 Qependents D"ec t or y

A l-eve.

Ola�cell

0 Visible cells onl� 0 Conditional fonnats 0 Data j!artdation

OoQ.lects

All Same

OK

]j [

Ca n cel


16 7

3. Copy 'l

A

•

·

1 hwant 2

I

B

3 these cells 4 5 6 7 to

8 9 10 11 12 ' 13 14

·-

all

be

r

together.

I

4. Click in new location and paste

J

�

2

A

. ._ .. -·...,�

-· ---

• .... ... ... ... ... .. ... ... .

I want

... ... ... ... ... ... ... ... ..

3 these cells: ------

-

'\6

· � � :w=

I want

these .cell -

· to

"""'-�

all.;. be ;

-.,:.._....

-

·

- ,..

5

-

B

-

·-

to2ether:

·-

-------- '

7 ��-------: 8 9 - ----10 lall be : 11 12 - 131_lto"ether. : �;> 14 --

-

... ... ...

..

---

.. _

... ... ... ... ...

- - - ..

_ _ _ _ __

---

.-

I

i-----

j�--

The results will always be constants, not formulas, even if they were origi nally formulas.

168


• 87.An oddity with the Camera Tool

In the following illustration, the grid lines were turned off, range A1 :C12 was

selected, the camera tool (See Print remote areas on same page, earlier in the book). was clicked, and the picture was "developed" in range E1. P icture 1 A

,_-·

info -+i info 1

3

-4 5

-6 7

8 9

10

11 12

info info info info info Info info info info Info

·�

..

-B

info

(

I =$A$1:$C$u

tx c

info

info Info info

info

info Info Info Info Info Info Info

info Info Info Info Info Info Info Info

•

0

info info info info

Info info

Info

info

E

info 111f 0 info info info info info

>.

F

"

info

info info info info info info info info info info info

If you were to format this picture and give it no line and

no

info info info info

G

•

info info Info info info info info info

fill, it would look

like this:

'-'

E

Info info info info Info

Info info info info

0

Info

Info

info

0

Info

F

"'

info info info Info

info info info info Info info info

0

G

info i nfo info info Info info

info info info

0

Info

info info

0


169

• 88. Quicker data entry

If you need to enter large numbers, like 1 billion, it's pretty easy to enter the wrong number of zeroes. Instead of slowly typing the string of 9 zeroes while mentally counting, you can simply type

Al

A

1

1 e9.That would go in as: fx

I

B

[l.OOE�9�

ltooooooo

c

D

E

2

because excel sees this as scientific notation (which it is): ......__,.

J

Jnwt

Home

� J(, n LJ� Pa�te �

B

I

Clipbo a rd r.

Al

="'

A

Formulas

• 11

Jl

•

•][Ell •][ &

e :....: e � ...:

A·1 p�- ...u � ·l .6. •]IC: ��!� ·��

rA

;;;:

•

Developer

View Scientific

• 0/o

A

J[�o8 +�8]

•

t..l loooooo��/ �====r====r===;=== A- I F G H

8 J. c

1 I 1.00E+09l

--

Number

Font

I

R vi w

Data

----'-

-

Ollibrl

l

Page Layout

-

D

but a simple reformatting of the cell will fix that.

Al

A

110000000

f,c

I

_!_[looooooo!

� B

�� c

__ --

� o

�

--

��E--

--

2

This isn't

Excel, it's Magic/

171

• 89. Miscellaneous,

Changing cell reference

If you have a reference such as =SUM (A500:A525) and need to change it to =SUM(A525:A535), you would most likely edit the formula and change the 500 to 525 and the 525 to 535. And of course, that would work. Did you know you can just change the 500 to 535 an d get the same result? That is, you would see this before pressing enter:

,.. X .J fx =sum(.A535:A525)

I

8

..

C·

I

0

but as soon as you entered the formula, you'd see:

8

k! =SUM(A525:A535)

I 1

- Excel would change it for you!

1 72

This isn't Excel, it's Magicl

ol,

----!i-

l

• 90. More miscellaneous info

You can double-click a tab name to hide the ribbon:

Double click it again and it's back. Lastly, you should press

F1 for help on all the new features of Excel 2007

There are so many! �Excel Help

-

1:1

X

A\ � � .. @@('l:) � l l(i)J --�-------------------�1 •

P Starth

•

Excel

c

He l� Md HOV·tO =-= I

l tlelp £xce se llro -w --

�;;j;'(sjj�;;j

Getting help

tnsll!llin9 FR ccnversaon and �tility

l'lorl:boolt ��

Worl
Formula and name basics

Accessi>ility

'il Excel Help

-

o

X

� �--------------------� ��© � � � -A\ � •1 •

P Starch

•

What's new 4items @ 1'/hafs new In MicrosoftOffice E
E
@ Demo: Up to speed with Excd 2007

This isn't

Excel, it's Magic/

173

And don't forget to watch the demo!

�Excel Help ;.:,

-+

-

IX) I"� �

P S•arch

•

Demo: Up to speed with Excel --- . . . . . . :: .. . ...

'

.

- -..

---

1 74

-- . -.

·-

(You may experience a delaywhile lhe demo is loading.}

-"'t=:;""= t :-=�. ·=:=

·-·· -

2007

" Play Demo {9

-

-

.:::.: .":''"

.;:.-·::e..-:-_ �:..:: ::..-:..·-· -·

o;;·;-:..o::�:.::-:;-:n··;;;-�::! -

X

!.(,)-AA�-------------------� til � • •

•

1:1


-·---- -·

Microsoft Office Excel2007 has a new design that makes your work

9 1. Copy Page Setups to other sheets If Sheet1 has the setup you want and Sheet2 is to receive the same settings: Here's what the print preview looks like for sheet1 :

I

Next

Jl-"1 J

Zoom

][.e
11812005

ny· Cen.t:er Heade:r:

I

I

A B c This contains page setup for

� 2 iliQe'Seti.,i . d' iiiW " T

...

___ ._,..

And sheet2:

72

1/112003

63

2/112'003

70 17

"'

4/112003

••

.�/112003

81

104

S/1/200 3

98

112

7/112003

105

120

8/-t/200 3

112

128

3/1/2'003

sa ..

So, you can see that Sheet2 is portrait and has no left/center header, while sheet1 has landscape, has an italicized center head er, a date for the left header, and the data is shown with gridlines and row & column headings. We'd like sheet2's settings to be the same as sheet1 's settings. Here's how to do it: 1. Put both sheets in group edit (with Sheet1 active, ctrl/click Sheet2-you can also shift/click, but if you're using sheets which aren't next to each other, ctrl/click will only put the 2nd sheet in group mode, whereas shift/click will put all the sheets in-between in group mode). 2. Use dialog arrow in the Page Setup section of the Page Layout Tab to bring up the Page Setup dialog: [Excel Pa);e·La�ovt

2003: Fi/e[Page Setup] F ormUlaS• -

[JJ �< !D

tYi��:Qin( Orl��ati:Of{'· �·�� • "

,.

'

. Dat a· .e.J F-1 ... . . Bteaks .�

'

r

R eV i eW'

Vftw

� .

"

Ba.<.MJ round .-

.

. .

. .

.

, ,•.., . .. .

.


•

175

... which dis plays: �-

-

Page Setup

ra�.J[;.Orientation

THeader/FooterTSheet-1

or>s

Scalonv 0 Ad� to: O�to:

�!fg]

lt= � oo =�:l..:.;% normal size It

�;lpage(s)wodeby �.:. 1'_ ---.=.J :!tal

Iletter, Quolty: �-.M Ied_ ,u _ m_ Fi(.st pbQe number: [utoJ Paper sile:

�� =
8.5xll n.

Pt1nt

_______________

[

£!rint...

I [Print

I [ Qptions

Previe<;!

OK . -·

3. Click

OK

11 [

.••

Cancel

I

(you don't even have to look at the settings!)

4. Get out of group edit (shift click a sheet tab), or right-click a sheet tab and select Ungroup Sheets S.That's it. Really! Here's Sheet2 now:

l1Y center Header

� 8

H

You can do this with >1 sheet at a time, or even the entire workbook.To do it for more than 1 sheet, simply put all the sheets in group mode and have the sheet with the settings you want to copy be the active sheet. Then Page setup/OK/get out of group mode!

1 76


To do the entire workbook, right-click a sheet tab, select "Select All Sheets," and repeat the process. Jnsert... Qelete Bename Move or Copy...

� dJD

�ewCode Protect Sheet...

Iab Color

�

Hide !:,!nhide...

=;(!)

lelect All S heets-·'""" ===

mC

:

o

am p w

= =

= > o = w

Don't forget to get out of group mode!


177

,

'

92. Copy Page Setups across workbooks You can temporarily copy in the sheet from the desired settings to the sec ond workbook and proceed as in previous tip. But this method brings in names, styles, number formats, and links as well. Here's another approach which avoids that problem. UsingVBA, run this command: Application.Dialogs(xiDialogWorkgroup). Show. This puts all open work books and worksheets in group edit mode. With workbooks Book1 and Book2 open, that produces this dialog: �---

@liD

G�oup [dit Select !!roup:

[�!'.9.k.!.ls.b.�.e.t.?.:_., _, . . .....

[Book!]Sheet3• [Book2)StieetI [Book2)SheetZ Booi,Z Sheet3

•.

.

.

._[

'

... . .... .

K 0

-····· ··· ··

.

.. ...... .

----'J1l [

�

'

, .. .

. .

.

.

.

Cancel

I

Selecting the sheets which contain the page setups to be copied, you can then simply issue the File/Page Setup command as before. Once again, make sure that the sheet with the proper Page Setup is the active sheet, because selecting any other sheet will take you out of group mode. And, once again, don't forget to get out of group edit mode!

178


93. Print many worksheets at once To do this, just put them in group mode:

Right-click sheet tab & choose "Select All Sheets:" Jnsert... Delete Rename Move or Copy...

cy �

YlewCode frotect Sheet... Iab Color

>

t!ide

O r ctrl/click specific sheets to print. Then Print! Don't forget to take the sheets out of Group Mode, which you can do by shift/clicking any sheet.


179

94. Print remote areas on same page To do this, use the camera tool.You can get

it

by putting it into the Quick

Access toolbar. C lick the Office button, then Excel Options: [Excel

2003: ViewiToolbarsiCustomizeiCommands tabiTools from CategoriesiCamera (-213 the way down) in Commands))

D

Re
t!
..,.

L../

Qp•n

ltd

1�V<

�

Save e,s

�p

� :bJ

• •

Prepare

l For Oan.xls

-(:::1

� Atlanllc City l.xls

-(:::1

i Webinar2.xl�

-� t=l

PJ.lblish

-\'::1

§ Qui:.xls z

Cool fml.xl.s

�

!! l!

FlowChart.XI!

-(;:1

4-PivoU.xls

-!):l

Classm'atfs.xlf

�

6-P!VCALCS.XLS

-D=I "

• •

Stn.sl

LJ

-t:l

i Movie Jeopardy3.xl�

erlnt

�

1 oddityJ
•

Tabl•s and Pivot Tablfs.xls

-!):!1

l065·EOSO.xls

-1):;1

l·FlLTER.XLS

-i):;J

9·Cond Fmtg and Data Val.xls

-1):;1

3-Userl'orm.xls

-(;:1

Homtwork.xls

..j):a

Org.xls

-(;:1

Pivot1.xhx

-!:=

P4voU.xh

�

�los•

which brings up the following dialog.You will have to select the Customize option, then select All

Com mands from the "choose commands from" drop

down, then scroll down until you see Camera (they're alphabetical):

��

CU1tornl::t tht Qu•cl; A
Q'loo'•(OI!Im<�n(h froM:1

....

[All

Cunomiu .Quid:A«-eufoolb.or::!J" FQI�� (IQ(\IIIIffiU(0��11(.11

Comnu.tl(lt

I

--

a

II(W

U'J

Un
('..t

A!do

Q

•.

Tr�.ul Cen111

( tfllfl CIUI\Ot Ql,ut 1�1)( ..

.

'"'•"9" Colo" Clung-tO•t• $ouru..,

180

This isn't

v _.J

_

Excel, it's Magic!

•

S�wr I• •

Qpcn

L.J I•

Next, select the remote cells, and click camera tool. A 1 Areal 2 Ateat 3 Ateat • Areal 5 Anal 6 Neal 7

Areal

8 Arlil ' Art11

Areal u Areal 12 Areal

10

8

Areal Areal Areal Areal Areal Areal Areal Areal Arell Areal Aftll Areal

c

Areal Areal AJill Arell Ar1tal Areal Areal Artll

Arul Neal Atell Areal

G

E

0

H

K

L

Areal Aflll Areal Areal Areal Areal Aletl Areal Areal Areal Antal Areal Jueal Areal Areal Areal Areal Aftll Areal Areal Areal Attll ArHl ... ,...

M

N

0

p

a2 lAtea2 Atea2 Ant2 Area2 Areal ea2 !Areal Areal Area2 ea2 jArea2 Ana2 Area2 ea2 jArea2 Areal Areal rea2 ;Area2 An�l2 Areal

Areal iArea2

13 14 15 15 17

"Develop" the picture near the original range by clicking where you want the picture to go. Picture 2

'I A 1 Areal

·(

B

I I

/.-c =$M$11:$Q$16

l

c

D

I

E

Areal Areal Areal Areal

I

F

2 Areal Areal Areal Areal Areal _ 3 Areal Areal Areal Areal Areal

4

Areal Areal Areal Areal Areal

5 Areal Areal Areal Areal Areal 6 Areal Areal Areal Areal Areal

7 Areal Areal Areal Areal Areal 8 Areal Areal Areal

Areal Areal

Areal

Areal Areal

9 Areal Areal

10 Areal Areal Areal Areal Areal 11 Areal Areal Areal Areal Areal

12 13 14

Areal

Areal

eal

Ar

Areal

Areal

Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2 Area2_.

�{

Area2 Area2 Area2 Area2 Area2

18

Area2 Area2 Area2

Area2

Area2


1Q

If you hold the AIt key, the picture will exactly line up with the closest cell grid. For best results, you want to turn off the gridlines and removing the border after the picture is shown. (Note the address in the formula bar).


181

j

-

Prlnt Area

(

A I I I 1 Areal Areal Areal B

I Areal I I

fx

•

c

0

E

Areal Areal

2 Areal Areal Areal Areal Areal

3 4

5

Areal Areal Areal Areal Areal Areal Areal Areal Areal Areal

Areal

Areal Areal Areal Areal

6 Areal Areal Areal Areal Areal 7 8 9

-

Areal A real Areal

Areal Areal Areal Areal Areal Areal Areal Areal Areal Areal Areal Areal

10 Areal Areal Areal Areal Areal

A

11 real Areal Areal Areal Areal 12 Areal Areal Areal Areal Areal _ 13 Area2 Area2 Area2 Area2 Area2

14

15



16 Area2 Area2 Area2 Area2 Area2 !Z. Area2 Area2 Area2 Area2 Area2 18 Area2 Area2 Area2 Area2 !Area2 1Q

Now select the cells to print, including the cells "behind" the picture and set the print area, then print! It will print on one page instead of one page for each remote area!

182


95. Reset all page breaks caution If you have a Page Setup which includes a setting like Adjust to 80% normal size, this also gets reset to 100% by the Reset All Page Breaks command found in the Page setup section of the Page Layo ut tab:

cellsllnsert!Reset All Page Breaks] Page Layout

-

I ~ l] ns Orientation

Size

•

•

B

Print Area

El I

c

Not nice!

I

.....

D

Breaks Bac.kground -

•

-

E

C3L

I"= ~ &!]

Page Setup

fx

View

Review

Data

Formulas

[Excel 2003: Select all

Print Titles

j: ;.

t

0 •

f

Insert Page Break

I

gemove Page Break Reset All Page Breaks -

This isn't

Excel, it's Magic/

183

96. Getting to your Visual Basic routine by FS (go to) from the worksheet If you know the name of your Visual Basic subroutine you can get to it quickly just as if it were a range name! This assumes, of course, that the VBA project is not protected! Edit!Goto (FS) and enter the name of the proced ure! This is also true if you type the name in the Name box.

�[!;;\ l&l"l . �

'

..

.

"-n v..w

1-iOIII• Cllutt htf 1 foun., Oilotl

0.,.... Ud-1

i;

-

�

X

�icrosofl 14

filt

YlsUoJI&sic Bookl - [Modu"1 ·

Edit

� Jnsttt

�mat

•

1

2

Areal

Ate

An�al

Are

3 Area1 Ate 4 Areal

Are

C

0

E

F

-

G

:;Go�to ,.' �u Pr,...J eo

-

--.

7 Areal

Areal Are

9

A1e11

Are

11 Areal Are

12 Ata:J2 Ar�· 13A
.

..

....,

_

184

a1

::::::. -.,... ,,.. -, ao1L-.-�� ..,. -,�-..-:::.._ ---

�== = =;"'·I-··· I

10 Ateat Ate

W) '

, ,,__6,_,. .It-t

Are

8

� Sheet2 (S'Ie 5tle

Atet'l Ate

-

�

<

Oi' l boo l;

&1. V8AProj.ect(8tnFaces.wla.� )

-

__,

___

·-


.J�� ·;; 'i � •

0 tion E x l icit

.\! V8Aho;cd(Book2)

� e:JMierosdtfxttiOb:ie
------

S Areal Are 6

Pt.R�ALXl.S{PCR SOf"fAL ;. •l.f6 KASOI'IAL.Xl.S (Pt:;RSClr'IA� .:l

•

• ...,. :::J 1..

� - �==�� " ·6:��--�=-�----==�==� �· m� � L�� " ·==�� F � H• -!_ 8

Q
: !:l.dl)

! l!il iii . Q b

A

(Code))

-

End Sub

..

·

97. Can't find the old Excel 2003 commands? Use this feature! Press F1 (Help).Then click What's New. Then click "Reference: Locations of Ex c el 2003 commands in Excel 2007." Then scroll to the very bottom and click "Interactive: Excel 2003 to Excel 2007 command reference guide." Then click Start the Guide (requires Adobe Flash Player 7.0 or later). Then click Start. You'll see Excel 2003 commands. When you try one, you'll see how/ where it's done in Excel 2007.


185

98. Aiigning Userform objects Selecting more than one object will show one with white handles, others with black handles. �

�

u.�erform3

• •

•

•

•

•

•

•

•

•

•

•

Notice the 2nd one has white handles. This one is the one used for aligning: Add-Ins

Iools

lml

·§i

J:e:i Ifi

�ign

�

�ak.e Same Size

�

Size to Fit

.g,

�I

Size to Grig

!,efts

t@

Centers

k

Help

�:.. ::: _:-

Rights

-

!ops

.IIJT

Horizontal SpacinQ

�

l!erbcal SpaCing

-o(} Middles

•

Jll&

�ttoms

!:;enter in Form

�

:fll:

to §rid

Arrange Buttons

�

-

•

!ifoup

•

b!ngroup

•

•

Order

'I -

186

l:f>

Window


�

•

I

•

This becomes: �

�s�rform3

�

•

•

•

•

•

•

•

This isn't

Excel, it's Magic/

187

99. Put pictures in Userforms from the clipboard Copy any picture into the clipboard. Access the Userform, and click in the Picture property. Even though the picture you want is in the clipboard, the Paste command in the Edit menu is dim. But you can paste into the picture property with ctrl/v! Pr opert ies - U s erform

luserform3 UserForm

Userform3 "

"

.

.

"

Alph�betlc CateQorlzed

•

.

•

•

.

•

.

•

.

.

•

•

.

.

.

•

0

.

•

. . . ... . . . ....

•

.

.

•

•

.

•

.

•

. •

. . . .

. . . . . .

. . .

..

•

•

. •

•

•

•

•

•

•

•

'

. . .

'

•

•

•

•

•

•

•

.

0

•

•

•

•

•

•

0

0

•

. . . . .

•

•

•

•

•

•

•

•

•

•

.

•

. •

. •

.

•

•

.

•

•

.

•

•

. . ..

..

.

..

. .. . .

,

,

.

..

.

,


•

•

0

•

•

•

0

. .. ,

,_, . '

"

188

. .

•

•

•

•

•

•

•

•

•

•

You can remove the picture via ctrl/x.

.

•

. .,

.

.

"' .

. ·

.

.

.

.

.

. ·

.

.

, _. .

. .

•

•

. . .

......

2·

.

•

•

. . . . . . . . .

(None)

.

.

•

.

fmMcJUSeP'oint ll

•

•

....

&None) 0

. . . .

•

•

.

'

•

. •

100. Make your own tools for forms You're always creating and

OK and Cancel pair of buttons, right? Well, you

can save them! Select them both and drag directly onto the toolbox! --

Toolbol<

Controls

� A

{8)

I

. . . . . . . •

•

0

•

abl � IHll P" r< j=!LJ....J ..!..l..:J;�� .- ___;L.L��......., rnl L...,,...-•

•

•

•

•

•

•

•

•

•

0

•

•

•

•

•

•

•

•

•

•

0

•

•

•

.

. .

. . . . . . .. . . . . . . - . . . . . . . - . ... . . . . . - . . • • • • • - • 0 •

•

•

•

.

•

•

•

•

•

•

•

•

•

0

•

•

•

•

•

•

•

•

•

•

•

•

•

0

0

•

•

•

•

•

0

•

•

0

•

•

•

•

0

•

•

•

0

•

•

•

0

•

•

•

•

•

0

•

•

•

•

•

0

•

•

•

0

•

. . . . . . . . . . . . . . . •

•

•

•

•

. . . . . . .

•

•

•

•

•

•

•

•

•

•

•

•

&dditlonal Controls... Delete New Group

erForm4 •cI

. ..

. . . . .

Customize New Group

I�

A bl!!ril ...J p r. ,... CJ_j

�

•

d ,..

�00 �

��

You can then use this new tool to drag both

OK & Cancel buttons onto any

new Userform. If you right-mouse click this new tool, you get

Controls

� A

I

abl !ili1 IHll P" r< r!LJ....J ..!.J .... • I.J AI ��

��fi!.L-: ::-----L.L....:�<�<-'-; zJ

rn

&dditional Controls...

e rro rm� erForm4 • Ic

Delete New Group Customize New Group

This isn't

Excel, it's Magic/

189

and by selecting "Customize New Group," you will see these options: --

Customize Control

�

Tool Tip TeKt !New Group

Previewl I iiD I

I !.,oad Picture... I �dK Picture...

which you can edit (Edit Picture) to become:

If"•

Colors

1101 Ill•• •••ll!l •••ll

' • I

Controls C� t u:e l �l e ar

�A

[�

] abl

rv

r.

...J r

...!..J

�

,., �

rn

OK Ctll

�

�

LJ _J .!lfcll

You can save any combinations for easy future development.And they're available in all future sessions of Excel to drag out of the toolbox onto your form. Only the design is kept, not any code behind the objects.

190


You can also place these on your own Controls "page"-if you right-click

the Controls tab you 'II see thi s:

New Page

Delete Page

Rename

...

Move

.•.

�mport Page

...

Export Page

.•.

Selecting New Page and right-clicking this tab, you'll see:

Controls NewPage Delete Page Rename ... �ave... !mport Page

.••

!;_xport Page

..•

UserForm3

which yields: �

�

Rename Caption:

IM.v Cool Control�

Control !ip Text:

OK

I

Cancel


191

or: I I

I I

Controls

My Cool Controls

j

OK

And now you can see you can store a whole library of controls:

I

Controls

My Cool Controls

IOption ButtonsI

UsefForm3

'1Mnn11IP.< I

v •

just drag them from the control toolbox onto your userform!

192


10 1. Using invisible objects Suppose you have this simple form:

'.J•P ·��--· . �!!.:_t: k � � ��

r Done II

Change Aspect Ratio

I

Wouldn't it be cool to show text simply by hovering (no click necessary) the mouse over an object/ We'll show how you can have "Here's the explana tion you wanted ... blah blah blah" when the cursor moves over the "Change Aspect Ratio" button:

� t1V �.J� � ! � � �---��

r0o�;;11

Change Aspect Ratio

I

Here's the explanation you wanted blah blah blah •.•

Here's the "magic" code to do that. First, the label has its "visible" property set to False, so it doesn't show up when you first show the form. Here's the code for the MouseMove event over the Change Aspect Ratio button: Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByYal Shift As Integer, ByVal

X As Single, ByVal Y As Single)

Me.Labei1.Visible =True End Sub The "meat" of this command is simply to make the label ("Label1") visible. So whenever the mouse hovers over the button you see it. But you also need to make the label invisible when the mouse is no longer hovering over the button. There's no MouseNoLongerMove event! So, you need to place another mouse move event over the form itself: Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) Me.Labei1.Visible = False End Sub Now, when the mouse is over the form the label disappears, and when it's over the button it shows up! This isn't Excel, it's Magic!

193

102. lnitialize Listboxes, Comboboxes with Months via GetCustomListContents Much more efficient than setting up some range containing the list of months, you can put this code into the initialize event of the form: Private Sub UserForm_lnitializeQ Me.ListBox1 .List = Application.GetCustomlistContents(4) End Sub � -----

--

P�Curos.;..-_,� -· ,�, Mlteh

:O:tl ·t �)' )./'< ' > AI.I)H. � .ett bel O««
IL

Jl

e+:

Application.GetCustomlistContents(4) refers to the 4th item in the Cus tom Lists (accessed from Office Button/Excel Options/Popular/Edit Custom Lists)

[Excel 2003: ToolsiOptionsiCustom UstsJ

· · 9J!�Lons.

;;,;�-� r@�

Color

Save

J

Sewrity

Error Checb-.g

Generat

Transition

Chart

List tnboes: ,

-

Fet.tu..t•arct, ,;.pr

l'•t Ju; e JUly

"'

f I

I

]

Ad;; o�:e

I

tu'1JSt S�te-.bet -

If you want to show days of the week, you'd use Application .GetCustomlist Contents(2)!

194


103. Using Microsoft Visual Basic bookmarks Microsoft Visual Basic has an Edit Tool bar which has, among other things, the ability to put bookmarks into your code, temporarily (they won't save).

"6

. r-=====�===

•

Par�er Info

Ctti+SNft+l Ctri+Space •

0 &H800()()()0j

;r.xuilQM..,;,

I· �

A. �e'mtr-(;o."'"'

F�

Cit:!I A Soc.:..r1u1r.$

Done

It makes getting to a location in code very easy to access. It shows up as a blue flag in the margin of the code:

Pr·ivatc Sub Co

andButton2_MouseMove(ByVal Buti

A1�.11isible : Tr·uc

vatc Sub Uscr•Form_MouseMove(ByVal Button

Me.Labell.Visible : False

As:

End Sub

You can mark code even across projects, and as you click the Next or Previ ous Bookmark button, it will immediately take you to that section of code. You can even bookmark the immediate window(!), but you won't see the indicator because the immediate window has no margin. But you'll see the cursor blinking in the immediate window!

This isn't

Excel, it's Magic/

195

• 104. VBA Array

Suppose you needed to access a strange sequence of numbers, like 4,1 3,1 9,20,28,44,123. Perhaps these are rows you need to work on You can .

get to them in a loop with this technique:

Sub AccessRowsO For· i = 0 To 6

j = ArTay(4.

13, 19. 20, 28. 44, 123)(i)

RowsG).Font.Bold

=

Tr·ue 'for• example

1\lext End Sub The statement Array(4, 1 3 , 19,20,28,44,123) has 7 elements in bered 0 through

it,

num

6 (unless Option Base 1 is supplied at the top of the

module). So the first element has a subscript of 0 and can be referenced by Array(4, 1 3, 1 9,20,28,44, 1 23)(0), which would be a 4. The 123 is Array(4, 1 3, 1 9,20,28,44, 1 23)(6). So, using i as a subscript, j takes on the succes sive values i n the array list. Here's another way to do the same thing. And since loops are s o fast, you might sim ply prefer this way:

Sub AccessRowsO For· i = 4 To

123

Select Cose i Cose 4,

13, 19, 20, 28. 44. 123

Rows(i).Font.Bold =True End Select Next End Sub

196


• 105. Ensuring users don't open your work book with the shift key down to prevent your macros from kicking in!

Here's a simple technique to ensure they allow your macros to run. Save the workbook with only one sheet showing, and that sheet may look like this: ....

You may not use this workbook without enabling macros! Reopen and don't hold the shift key! Prevent them from unhiding any other sheets by protecting the workbook's structure with a password. Use Protect Workbook/Protect Structure and Windows from the Changes section of the Review tab [Excel 2003:Tools/Protection/Protect Workbook]:

81

m �

Protect

Prote ct

Sheet Workbook

•

ti}iJ Protect and Share Workbook � Allow Users to Edit Ranges Sha re · Track Changes· Workbook �P -

Restrict Editing Protect Structure and Windows I

0

M

Restrict Permission

Jj

I

N

Unrestricted Access ,Bestricted Access Manage Credentials

Then supply a password:

Protect worl
0 S,tructure O�n i dows

eassword (optional):

I···

OK

J] [

Cancel

]


19 7

Your Workbook_Open event procedure or Auto_Open procedure has the code to hide the message sheet and unhide the other sheets by unprotect ing the workbook and supplying the password:

lworkhook Pl'ivote Sub Wor·kbook_OpenQ ThisWor·kbook.Unpr·otect "abc" For· i : 2 To Sheets.Count Sheets{i).Visible: Tr·ue

l

1\lext

Sheets{l).Visible: False End Sub Pr·ivate Sub Wor·kbook_Befol'eCiose{Concel As Boolean) Sheets{l).Visible: Tr·ue For· i: 2 To Sheets.Count Sheets(i).Visible: False Next ThisWor·kbook.Pr·otect "abc" sh·uctut·e::Tr·ue '

End Sub

Notice also that before the workbook is closed, it also resets the sheets back for the next time, hiding all sheets except the message sheet. It's turned to visible first because you can't hide all the sheets in a workbook! Lastly, you can protect access to the VBA code by using this in the VBE: �ebug

I

�

B,un IJ

Iools

a

c

0 � ,.. &:1

IT [workb• �

I

Pri

I Add-Ins


Help , Cc

B,eferences...

�ltronal Controls...

I==

Macros...

1-

Qptions...

I

VBAProject Pro perties.. .

Digital Signature...

..-. � �� .

198

Window

In(

�n11n .

"<

And then:

!��P.!.� i£!;.L:J> .!�i !:,<;1J'J:2 P �Qi_e�--* General Proteclion I

.;.�

.....-

�.

Lock project � Lock ptO)eCt for VIewing

I Password to view project properties--------, Password Confirm password

"'I '"'" ,

...,.

OK

I '

H elp . '

So now, if the user opens the workbook with the shift key down, it's a fairly useless workbook!


199

106. Bulk removal of leading apostrophe When a cell's contents contains a leading apostrophe, it doesn't show up in the cell, only in the formula bar. This has the effe ct of making the cell be treated as pure text. That is, if you enter '=1 0/2, you would see =1 0/2, not 5. Getting rid of the' by Find and Replace doesn't work because Excel treats the apostrophe as if it weren't there and would say

it

wasn't found! So re

moving it is a matter of editing the cell. But what if you have many cells with the leading apostrophe? Each of the cells in A1 :A10 contain a leading apostrophe:

I '=Sheet2!A1 I D I

fx c

E

The way to remove them is via text-to-columns! Use this feature on the

Data tab, the Data Tools section [Excel 2003: Data

Review

;., Clear ��Reapply

.":V Advanced Iter

View

Developer

1:=-.:I

!��

Text to

Remove

�

DatalText to Columns]: Add-Ins

� = -

--=

Columns Duplicates

1�1 Data Validation �o Consolidate � What-If Analy i

Data Tools

When you get the Text to Columns wizard, just click Finish!

200


s s

•

•

• 107. Multi-select offset

Did you ever have an odd selection and wanted to have the same selection

(

2 columns over or 1 or a few rows down, etc) to repeat some action? Here's an oddly shaped selection, all filled with 123:

814 1 2 3 4

A

5 6

·� 123

123

123

123 123 123 123

8 9

11 12 13 14 - �

123 123 123

B

c

123 123

7

10 --

1123

/x

I

123

1231

Now you want the same shape beginning in D 1 . The easiest way to do it is using the VBE's immediate window to execute a simple one-liner command:

Immediate

selection.offset(O,3).select

This isn't

Excel, it's Magic/

201

The result: A

B

l

123

2

123

4

123

8

123

�

123

10

123

11

123

12

123

13

123

. l

123


I

I

I I

123

7

o�tJ-=-t�]�F �•

I

123

15

202

I

123

6

�

·-

123

3

5

c

I

r i

i

J

l I

I

·;

I

.,

I

-

• 108. VBA Boolean test

Instead of using this to turn a Boolean variab le to True/False:

Sub BoolcanTestQ

Dim MyBool As Boolean

If Range("A12").Voluc MyBool

=

True

=

False

>

12 Then

Else MyBool

End If End Sub

You can use this t o d o the same thing:

Sub BooleanTestO

Dim MyBool As Boolean

MyBool : (Range("A12").Vo.lue

>

12)

End Sub The expression (Range(''A 12").Value

>

12) will be evaluated and if it is great

er than 1 2, My BooI will be set to True, else it will be set to False, the same as in the first example! Quite a bit sh orter!


203

• 109. VBA Shortcut to fill ranges

Suppose you need to fill cells A 1 :D1 (using VBA) so it looks like this:

l

B c A ___::l_�:.N:.:;a;.;.;m;.;.;e;..._.Aceount # Date 2

D Amount

Most folk'sVBA would look something like this:

Sub FiiiRangeQ Range("A1").Value ="Name"

Range("B1").Value = "Account #" Range("C1").Value

=

"Date"

Range("D1").Value ="Amount" End Sub ...and that would work fine. But look at this version:

Sub Fi iiRan geQ Range("Al:Dl").Value : Ar·r·ay("Name", "Account#", "Date", "Amoun t") End Sub Not only is this shorter, but if the code needed t o fill range A1 :Z1, there'd

be

a lot less typing!

What if you wanted to fill A1 :A4 with the same information? You might now be inclined to use this:

Sub FiiiRongeQ Range{" At: Dl").Value : Ar·r·ay("Narne", "Account #", "Date", "Amount") End Sub

204


But that would surprisingly result in:

�B=.�..=2c��=o: 1_::: �Name-r

I

A

1Name jName -11Name 2 3

4

I

<--F .

" I •

...not what you expect.You need to use this code to accomplish the vertical arrangement of the data: Sub FiiiRangoQ Range(" A1:A4").Value : Appl ieation.Tr•ar�
That is, you need to transpose the information. Since Transpose is a work sheet function, you need to use any of these combinations:

Appl ication.Tr·anspose Appl ication. Wor·ksheetFunction.Tr·anspose Wor• ksheet Fu11ction.Tr·anspose The latter two will give the intellisense dropdown:

Wo r· kshe�t Function

�:.. ---

--·····--·-

""�!Acos

....... ........:a, �

:� · :-----· ··� · · · ·- · ·· -

�

.:. · � Acosh ""� And

..

®J Application ,,� Asc

'"� Asin "'� Asinh

The reason

I chose the first one (without the intellisense) is that it's the

shortest amount of typing!


205

• 1 10. Some VBA Shortcuts

Suppose you have a variable defined as this:

Dim savStr·i ngFor·Holding TheDescr·iption As Stt'ing ...and you wanted to use typing

it, then

It,

obviously, later m your code, you can simply start

press ctri/Spacebar, which will give you this:

Dim so.vSh•ingFoi·HoldingTheDescl·iption As St1·ing

vi

sa ��

-�� Savesening

r!§t savStringForHoldingTheDescr1ptlon "'� Second

·�� Seek

rf!JJ Selection '"� SendKeys

... and you can use the down arrow to select it, then the tab key (or enter key) to put in into your code: sav <·� SavePicture

,,� SaveSetting

rtf} "� Second

.�

"·� SeeK

rtf} Selection .:� SencJKeys

If what you typed is unique enough so there's really n o choice (like if you typed savs then pressed ctrl/spacebar), Excel will simply put the entire vari able right into the module. As a matter of fact, it's not just for variables. How often do you type out Ap plication? Simply typing Appl and pressing ctrl/spacebar will put Application right in the code!

206


"App" isn't quite enough for uniqueness:

oppl l!ii' ApplicatiOn .,� Asc .o� AscB ...�AscW r5J Assistant

v

"-� Aln

And, if you have nothing on the line and press ctrl/spacebar, you will still get the beginning of the list:

:.-..�;Ails..

..

..

..

!�

..

.. ---....- ·--·· · ·.. . . - .. .. . . . ....-.. --·-··· . · ·- · -·-···-·

..

..

---·-�·-M·----·-···-····-·-·- .. .. - ...

� AcliVeCell � ActiveChart ll1ii' AcbvePrinter � ActiveSheet � AcliveWlndow � ActiveWorkbook

,

.

,

_ _ _ ., _ ,1 _

_ , _ __ ___ .. _ __ _ , ,

-


20 7

1 1 1. A few VBA Keyboard Shortcuts AltlQ will bring you from the VBE to Excel. However, Alt/F11 is a toggle back and forth from/to VBE and Excel, so I prefer that one! Ctri/G will bring up the Immediate Pane. If the cursor is IN the immediate window, then Ctri/A and then backspace will dear it. If you right-dick on a variable, you will see a dropdown where you can re quest the definition:

savStr·I .

End Sub

.-

.

'' I I•

d. CUt q_ Copy !h. P.aste ..!. ti].

;J

U;t Properties/Metb.o.ds .

�

Parameter'lnfo

J,

Complete �ord

I�

.

'T' I ."

•

Li�t Constants '

.

Quick Info

lO�JQie

•

t:1 Qbject Browser

....

I

Add Watch...

Qefinition

Last Position !:!ide

208


I

ption

3D formulas, 1 6 6 Aligning 0 bjects, 163 Apostrophe Bulk removal, 200 Apply Names, lli Autosum Enter from keyboard, 148 Auto-Sum Features, �

Fill down

Alternate references, 11.5.

Fill Handle

Insert/Delete rows, 22

Right-click drag, ].]_ Fill right Alternate references, llJ. Filling Holes, 1 6 1 Filter Advanced, H

Borders

Shortcut, 146

Find after Find dialog gone, 153 Formatting

Bringing cells together, 1 6 7

#VALU E,70

Camera Tool

Comments, li

Oddity, 169 Characters All in Font, 110. Closing workbooks, � Comment Inside formula, 140

Comments, Formatting, 6A Copy formulas

With mixed references, 1 5 7

Cursor Movement Remaining Stationary, 165

Fill, Z5. I ndenting, l.A

Number, Advanced, I.1 Text, H

Time, Hours over ll [h), D. Formula Bulk change, 1 3 5 Change cell reference, 172

Extract part of string, 1JM. Inner Series, 80.

Long, !U.

Custom Lists, lB.

MATCH, using wildcard, 1QB.

Cut, Copy

Named, �

Via Borders, 21

Note inside, 140 Numbering Questions, 125

Data entry quicker, 1 7 1

Date & Time

Shortcut, 147 Dates

As range. 2B.

REPT.1 02 Show picture, 11i SUMPRODUCT,1 05 TEXT, 1QZ Functions New to 2007, 143

Change those like 20050923, 3..6. End of month, 21. Double space, 1il End of Month, 21. Excel 2003 commands

Hiding ribbon, 173 Indenting Text, 1.A Justify, ll

How to find, 185


209

List

Range protection, 124 Custom, za

Lookup More than

1 value, 1Q.Q

REPT.1 02

Ribbon (Hiding), 1 7 3 Right-Mouse Border. �

Mailing labels

Row Height, 8..6.

Trick1 , 128 Trick2.132

MATCH Formula,lQB.

Merged Cells

Row Height, S1

Multiple selection Selecting offset, 201

Scrolling, � Drag, 56. Selection All Specific Text, 1.6. Anchoring active cell, 1 5 2 Bring into view, 149 Select successive corners, 1 5 1

Names

Creating, U

Shift key While opening workbook, 197

Defining. Scrolling, 118

Shift-Click, 4i

Defining Large

Shortcut

areas, 2..2. Global vs Local, .2.0.

3D formula, 1 6 6

Long. !d.

Autosum, 148

Relative definition, 138

Borders. 146

Self-D efining. l0 1

X LM,92 Page Breaks

Reset Caution, 183 Page Setup Copy to other sheets, 175 Copy to other workbooks, 178 Paste Special Bring cells together, 167

Skip Blanks, .25. PictoGraphs. il Picture

Show by formula, l.l.i Printing

M any sheets at once, 179

Remote areas on same page, 180 Reset page breaks, Caution, 183 Protection Range , 1 24

Date

& Time, 147

Formatting, 145

Miscellaneous, 154

Select Precedents, 1 5 0

So rting

,

Columns, 1 5 5 Space character

Intersection, 103 Split screens (In VBA, too), 18. SUM PRODUCT,105

Table New features, 1 4 1

TEXT, 1QI Tools

Double-clicking, ll Unique

Entries, 3B.

Values, .2..5. Userform

Aligning objects, 186 Random sampling, 8.1 210


Initializing listboxes, etc., 194

Invisible objects, 1 9 3

Get to routine 1 84

Make your own tools, 189

Shortcuts, Part 1,2 0 6

,

Shortcuts, Part Using pictures, 188 Visual Basic

Arrays, 196 Boolean test, 203

Filling ranges, 204

2,2 0 8

Using bookmarks, 195 Window

Arrange, Except this one, 51 Arrange, Order, 60.

Title, 1 · ,2 · , 11.


211

ISBN-1 0: 0-9792153-2-3 ISBN-13: 978-0-9792153-2-2 5 1 4

This isn't Excel it's Magic! - Bob Umlas

Recommend Documents