100
Index | 379
restore workspace, 5
right-aligned numbers, 3
rows and columns, hiding, 27
Save As, preventing in
workbooks, 11
Save Workspace, 5
saving workbooks, before deleting
data permanently, 41
scroll area, limiting to used range on
worksheets, 30
ScrollArea, 27
sheet events, 9
Sheet Name tab, 9
Sheet object, 9
sheets, repairing corrupted
sheets, 44
Show Developer tab in the
Ribbon, 12
Spreadsheet viewer, 46
structural tips for setting up and
laying out spreadsheets, 2
template tab, 21
templates, 21
templates, grouping, 21
ThisWorkbook, 12
tiled view, 5
Today( ), 17
toolbars, customizing, 36
Trust Center, 11
Trust Center Settings, 11
Ungroup Sheets, 8
Unhide, 19
Unhide under Window options, 17
updated values, 4
users, bypassing limited access, 11
users, limiting privileges, 11
users, preventing from inserting
worksheets, 14
VBA code for grouping worksheets
automatically, 9
versus PivotTables, 125
Vertical option places workbooks
side by side, 5
View Code, 9
View Microsoft Excel tool, 10
Visible property, 19
Visual Basic code, running
automatically, 11
Visual Basic Editor (VBE), 13
volatile functions, 17
warning prompts, stopping, 18
Workbook bloat, 42
workbook customization, 5
workbooks, extracting data from
corrupt workbooks, 45
workbooks, opening more than one
at a time, 5
workbooks, prevent printing of, 14
workbooks, reducing the size of, 42
workbooks, repairing corrupted
workbooks, 44
workbooks, saved as read-only, 11
workbooks showing muliple ones
simultaneously, 5
worksheet protection,
auto-toggling, 33
worksheet_Activate, 27
worksheets, grouping
automatically, 9
worksheets, grouping manually, 8
worksheets, keeping hidden
worksheets unseen from
users, 19
worksheets, limiting scrolling
range, 26
worksheets, specify a valid range, 27
worksheets, ungrouping, 8
workspace, 5
XLSTART folder, 23
xlVeryHidden, 19
.xlw extension for saving
workspace, 5
SQL (Structured Query Language), 332
square brackets ([ ]), 81
stacked chart, displaying totals of, 190
standard toolbar for pre-Excel 2007
versions, 61
Stars and Banners, 71
static recordset, 332
static value, determining, 54
Store macro in: box (Record
Macro), 274, 276
stored values, permanently changing
with Precision as Displayed
option, 80
STrConv function, 323
structural tips for setting up and laying
out spreadsheets, 2
Structured Query Language (SQL), 332
98
380 | Index
Styles merging from other
workbooks, 61
SUBSTITUTE function, 216, 235
subtotals
boldface, using for identification, 85
identifying in worksheets, 85
SubTotal Button, 85, 88
SUBTOTAL function, 212
SUBTOTAL function, making it
dynamic, 212
Subtotals, 85
Subtotals, identifying Grand Total
from, 87
Subtotals pre-Excel 2007
versions, 85
SUM cells, 273
SUM function, 57
SUM function, adding time beyond 24
hours, 94
SUMIF function, 56
Summarize value field by, 133
summing
based on fill color, 273
calculating a person’s exact age, 255
calculating sliding tax scales, 246
calculations, avoiding error
values, 203
counting cells with multiple
criteria, 243
custom functions, for sliding tax
scale calculations, 249–251
DATEDIF function, 255
DSUMfunction, adding numbers in a
column of a list, 240
DSUM function, 209
DSUM function, error values, 203
finding the nth occurrence of a
value, 210
IF functions, for sliding tax scale
calculations, 246
SUBTOTAL function, 212
SUM and IF, slowing down
recalculations, 242
sum cells, based on multiple
criteria, 239
sum every nth cell or row, 208
SUM function, error values, 203
SUM function, simplify summing
with use of, 108
SUM functions, for sliding tax scale
calculations, 246
Sum of Amount field, 135
sum or counting cells while avoiding
error values, 4
sum totals, and error values, 203
SUMIF function, 239
SUMPRODUCT function, 209, 242,
245
SUMPRODUCT, overusing, 246
SUMPRODUCT, slows down
recalculations, 242
VLOOKUP function/formula, for
sliding tax scale
calculations, 247
showing a weekday as a
number, 256
showing a weekday as a weekday
name, 257
SUMPRODUCT function, 209
Super Code, 286
Super Code menu items, 286
T
Table Style, 62
tables
3-D effects, 61
blank cells, avoid within data, 127
Camera icon, 130
columns, 127
comparison operators, use
interchangeably, 231–233
Count of Product, 133
database functions for setting up
lists, 126
dynamic named range based
PivotTable, 138
dynamic named range, to decrease
refresh time, 137
Existing Worksheet, 128
extract statistical information from
raw data, 124
First Name field, 132
generate and extract data while
conserving memory, 126
GETPIVOTDATA function, 135
Grand Total, moving to top of the
table, 135
Grand Totals, 135–136
99
Index | 381
guidelines for creating tables or
lists, 126
headings, 127
Hide, 138
high row numbers, avoid using, 138
Insert tab, 128
invalid dates and PivotTables, 274
LARGE functions, to extract figures
from PivotTables, 136
Layout button, 133
leave at least three blank rows above
in spreadsheets, 3
list, creating, 126
lookup functions for setting up
lists, 126
macro, creating PivotTables
with, 133
Names column, use dynamic range
names, 229
New Worksheet, 132
Next button, 133
#NUM! error, returned with two or
more identical names in name
column, 229
pivot data for immediate results, 125
pivot data from another
workbook, 137
PivotChart Wizard for pre-Excel
2007 versions, 128
PivotCharts, 126
PivotCharts, unavailable for
Macintosh in Excel, 126
PivotTable, automate creation
of, 131–133
PivotTable creating, 128
PivotTable for pre-Excel 2007
versions, 128
PivotTables, classic table formats
required, 124
PivotTables, formatting and color
coding, 129
PivotTables online tutorial, 124
PivotTables, restricting shared
data, 129
PivotTables, to produce unique
names in lists, 207
PivotTables versus spreadsheets, 125
preventing blank entries and missing
fields in, 100
producing summary information
from a table, 125
range selected automatically, 128
raw data, 124
recordset, 332
Refresh option, 126
retrieve tables from databases, 332
rows, 127
SMALL functions, to extract figures
from PivotTables, 136
sorting, 127
static picture of PivotTables, 129
Summarize value field by, 133
tables, creating, 126
Values area, 132
View code, 134
VLOOKUP function, using across
multiple tables, 219–221
“What kind of report do you want to
create?” for Windows
PCs, 133
TakeFocusOnClick property, 277
Target.Interior.ColorIndex, 282
tax scales, calculating, 246
Temperature Data Series, 157
template tab, 21
templates, 21
templates, grouping, 21
text
changing to upper- or proper
case, 322–324
extract a specified word from a text
string, 234
extracting from a cell, 73
first word from a text string,
returning, 234
forcing to upper- or proper
case, 324–327
last word in a string of,
returning, 233
LEFT function, to return first
character or characters in a
text string, 234
left-aligned numbers, avoid
changing, 81
LEN function, to return the number
of characters in a text
string, 235
list of text codes, 82
How to C#: Rotate Image according to Specified angle VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel, VB.NET PowerPoint, VB.NET Tiff, VB Steps to Rotate image. Save the rotated image to an image file on the disk
pdf rotate single page and save; pdf reverse page order
139
382 | Index
text (continued)
number entries and, extracting
numeric portions of cells, 70
numbers and, mixing in the same
cells, 70
STrConv function, 323
text equations, evaluation of, 258
TEXT function, 218, 257
text numbers, converting to real
numbers, 68
Text Size, 167
text string, extracting specified words
from, 233
text string, extracting the numeric
portion, 70
showing the weekday as weekday
text, 257
TEXT function, 69
Text Size, 167
Text Values, 79
TextBox, 277, 327
thermometer chart, 157–158
ThisWorkbook, 12
add-ins, 284
macro ease of use and, 285
pre-2007 Excel, 99
private module, 264, 283
Workbook_Open event, 264, 283
Tick mark labels, 182
ticking cells, 272
Tile button, 5
tiled view of spreadsheet, 5
TODAY function, 17, 205
toolbars
attaching to a workbook, 317
coding Restore, 317
coding Show, 317
creating a custom toolbar, 317
custom toolbars, 37
customizing, 36
displaying, 317
hiding and restoring, 316
native toolbars, removing, 317
pre-Excel 2007 versions and, 36
Tools
➝
Add-Ins, 253
installing add-ins, 286
viewing add-ins, 284
Tools
➝
Macro
➝
Macros
➝
FillBlanks
➝
Run, 199
Tools
➝
Macro
➝
Macros
(Alt/Option-F8), 263
adding Calendar Control, 276
password protecting
worksheets, 278
running macros, 271
Tools
➝
Macro
➝
Record New
Macro, 274, 275, 276
Tools
➝
Macro
➝
Visual Basic
Editor, 13, 19, 37, 134, 217,
223
Tools
➝
Macro
➝
Visual Basic Editor
(Alt/Option-F11), 266
assigning buttons to macros, 268
retrieving workbook name/path, 279
splash screens, 269
summing cells by fill color, 273
Tools
➝
Options
➝
Calculation, 218
Tools
➝
Protection
➝
Protect
Sheet, 276
Tools
➝
Protection
➝
Protect
Workbook, 15
Tools
➝
References…, 332
Tools
➝
VBAProject Properties
➝
Protection, 285
tornado chart (population
pyramid), 184–186
Track Changes, 293
tracking changes, track, report, and
overcome limits to, 293
TRIM function, 216, 236
True or False, using to compare
ranges, 195
TRUE value, 50
true value for calculations, 80
Trust Center, 11
Trust Center Settings, 11
Type
➝
Change Chart Type, 191
Type options, 141
U
Ucase function, 328
UDF (user defined functions), 249
UDF (user-defined functions), 54
Underline, 88
underscore (_), 110
How to C#: Basic SDK Concept of XDoc.PDF for .NET example, you may easily create, load, combine, and split PDF file(s), and add, create, insert, delete, re-order, copy, paste, cut, rotate, and save PDF page(s
rotate single page in pdf file; how to rotate page in pdf and save
106
Index | 383
Undo
adding up to 100 mistakes to the
Undo feature, 84
Undo History, 84
Undo Stack, 84
unexplode pies, 141
Ungroup Sheets, 8
ungrouping worksheets, 8
Unhide, 19
Unhide under Window options, 17
Unique Records Only, 66, 78
unprotecting worksheets, 282
unwanted characters, removing, 68
updated values in spreadsheets, 4
uppercase text, 322, 324–327
URLs, avoid using as a hyperlink
base, 25
“Use 1904 date system” checkbox, 218
Use a formula to determine which cells
to format option, 50, 54, 55,
58, 88
UserDefined, 55
UserForm (VBE), 301
splash screens, 269
UserInterfaceOnly argument, 98, 282,
283
UserInterfaceOnly option, 320
users
bypassing limited access, 11
limiting privileges, 11
prevent printing of workbooks, 14
preventing from inserting
worksheets, 14
restoring settings, 316
user-defined functions (UDF), 54,
249
user-friendly validation (pick) list, 67
worksheets, keeping hidden
worksheets unseen from
users, 19
V
valid recipient list, 349
validation
validating data, 48
Validation, 48
validation list, decreasing list of
options, 101
validation lists, adding data
automatically to, 91–93
validation lists, changing based on
another selected list, 66
.Value = Now, 298
value derived from a formula,
determining, 54
#VALUE!, returned if cells contain
text, 209
Value (y) axis crosses at maximum
category, 183
Values area, 132
values, finding the nth occurrence of a
value, 210
VBA (Visual Basic for Applications)
auto-generate indexes, 24
code for grouping worksheets
automatically, 9
code performance, 270
data, sending to multiple places, 8
Excel, incompatibility with, 47
OpenOffice.org, incompatibility
with, 47
ticking cells upon selection, 272
VBA code, unrecoverable due to
incompatibility, 47
VBA Projects, HTML and HTM
formats, 46
worksheet index numbers and, 266
VBE (Visual Basic Editor)
CodeNames and, 266
hidden workbooks, 283
splash screens, 269
vbFromUnicode, 324
vbHiragana, 324
vbKatakana, 324
vbNarrow, 324
vbProperCase, 323
vbUnicode, 324
vbUpperCase, 323
vbWide, 323
Vertical option places workbooks side
by side, 5
View
➝
Code, 12
View
➝
Code (F7), 276, 278
View
➝
Macros, 29
View
➝
Project Explorer, 19
View
➝
Project Explorer (Ctrl-R), 266
124
384 | Index
View
➝
Properties (F4)
adding Calendar Control, 275
password protecting
worksheets, 277
splash screens, 269
View
➝
Properties Window, 19, 269
View
➝
Toolbars
➝
Customize…., 130
View
➝
Toolbars
➝
Forms, 149, 213,
267
View
➝
Toolbox, 269
View
➝
Unhide, 17, 40
View Code
code for creating customized
toolbars, 37
code to specifying a specific
worksheet in a custom
toolbar, 37
conditional formatting, 281
grouping worksheets
automatically, 9
index, auto-generating, 24
pre-Excel 2007 versions, 99
protected worksheets, 283
ranges, activating only the used
ranges, 30
running macros, 264
Save As, preventing in a
workbook, 12
ticking cells upon selection, 272
valid ranges, establishing boundaries
to, 27
worksheet protection, auto-toggle
for, 33
View Microsoft Excel tool, 10
Visible property, 19
Visual Basic code, running
automatically, 11
Visual Basic Editor (VBE)
code for customizing toolbars, 36
code to prevent users from printing a
workbook, 14
menus, quick access to, 13
pre-Excel 2007 versions, 55
prompts, preventing unnecessary, 16
worksheet protection, 19
VLOOKUP function, 210–212
VLOOKUP function, using across
multiple tables, 219–221
volatile functions
COLUMN function, 201
nonvolatile functions, nested withing
volatile functions, 205
Now(), 17
personal macro workbook, avoiding
storing in, 17
RAND function, 74
RAND function, major flaw in, 75
recalculating, 17
recalulations, reducing when using
volatile functions, 205
ROW( ) function, 60
spreadsheets, 17
Today(), 17
Use a formula to determine which
cells to format, 54
W
Walkenbach, John
downloadable ChartTools foradding
labels to charts, 166
web site for downloadable Chart
Tools, 163
warnings
array formulas, overusing, 246
error values, 203
SUMPRODUCT, overusing, 246
warning prompts, stopping, 18
WEEKDAY function, 256, 257
“What kind of report do you want to
create?” for Windows
PCs, 133
Window
➝
Unhide
Personal.xls file, 274, 276
viewing hidden workbooks, 283
Window
➝
View
➝
Arrange, 5
Windows
➝
Save Workspace, 5
Windows
➝
View
➝
Unhide, 276
Windows of active workbook, 5
Windows XP, retrieve data from closed
workbooks, 341–344
word-based reports, 344
Workbook Open, 98
Workbook_AddinInstall event, 285,
287
Workbook_AddinUnInstall, 285
Workbook_AddinUninstall( ), 287
101
Index | 385
Workbook_AddinUnInstall event, 285,
287
Workbook_BeforeClose event, 265
Workbook_Open event, 264, 283
workbooks
ActiveWorkbook, 284
All Open Workbooks, 18
attaching a toolbar to, 317
blank default workbook,
restoring, 23
Calendar Control, 274
Cascade option, layering workbooks
on top of each other, 5
code, never use to unprotect user’s
workbook, 284
CodeName and, 266
creating a list of hyperlinks, 298–300
customization of, 5
customizing default workbooks, 23
data extraction from corrupt
workbooks, 45
Display options for this
Workbook, 164
Excel Calendar Control, 274
hiding, 283
Horizontal for viewing workbooks in
a single stack, 5
macro workbooks, personalizing, 23
retrieving name and path of, 279
naming with the text in a cell, 315
opening more than one at a time, 5
personal macro workbook, 17
prevent printing of, 14
Protect Workbook, 15
reducing the size of, 42
repairing corrupted workbooks, 44
reproduce another sheet in another
workbook, 194
retrieve data from closed
workbooks, 336–344
retrieving names/paths, 279
Save As, preventing in
workbooks, 11
saved as read-only, 11
saving workbooks, before deleting
data permanently, 41
showing muliple workbooks
simultaneously, 5
splash screen, 268
ThisWorkbook, 12
track changes on all worksheets in a
given workbook, 295
tracking changes in a specific
workbook, 293–295
Vertical option places workbooks
side by side, 5
Windows of active workbook, 5
Workbook bloat, 42
Workbook_AddinUninstall( ), 287
Workbook_Open event, 283
workbooks names for pre-Excel 2007
versions, 40
worksheets
Alt/Control-~, shows actual
formulas on the
worksheet, 164
AutoFilter, 289–291
avoid selecting entire worksheet, 55
code for worksheet protection, 33
Ctrl key for grouping worksheets
manually, 8
data duplicated in multiple
worksheets, 8
data, spreading over numerous
worksheets unnecessarily, 2
deleting rows with specified criteria
or conditions, 289–293
Existing Worksheet and tables, 128
grouping manually, 8
grouping worksheets
automatically, 9
index numbers and, 266
keeping hidden worksheets unseen
from users, 19
limiting scrolling range, 26
macro recorders and, 266
named ranges, identification of, 121
password protection for, 276
password-protect and unprotect with
one application, 276–279
Private module, 324
protect from viewing without a
password, 320
Protect Worksheet, 32
referencing via CodeName, 266
retrieving names, 279
rows, deleting in worksheets without
AutoFilter, 291–293
running procedures on protected
worksheets, 282
43
386 | Index
worksheets (continued)
scroll area, limiting to used range on
worksheets, 30
sorting, 319
specify a valid range, 27
track changes on all worksheets in a
given workbook, 295
tracking changes in a specific
workbook, 293–295
ungrouping, 8
unprotecting, 282
UserInterfaceOnly, 282
users, preventing from inserting
worksheets, 14
VBA code for grouping worksheets
automatically, 9
Worksheet and tables, 128
worksheet name, using in a cell, 236
worksheet names, creating a list
of, 236
worksheet names, extracting, 237
worksheet_Activate, 27
worksheets protection with
auto-toggling, 33
xlSheetVisible, 295
xlVeryHidden, 295
worksheets, referencing via
CodeName, 266
workspace, 5
workspace, restoring, 5
X
X-axis category labels, highlight with
color, 188–190
.xla extension, 283, 285
xlSheetVisible, 295
XLSTART folder, 23
.xlsx, 337
xlVeryHidden, 19, 295
.xlw extension for saving workspace, 5
XY scatter chart, 160
Z
Zero Values, 79
8
Colophon
The tool on the cover of Excel Hacks is a trowel. The trowel shown is the
type that is generally used in everyday gardening tasks such as removing
stones from dirt, planting, and removing weeds.
The cover image is from the Stockbyte Work Tools CD. The cover font is
Adobe ITC Garamond. The text font is Linotype Birka; the heading font is
Adobe Helvetica Neue Condensed; and the code font is LucasFont’s
TheSans Mono Condensed.
Documents you may be interested
Documents you may be interested
