Excel 2007 Pocket Guide, Second Edition

By Curtis Frye
Book Price: $9.99 USD
£5.99 GBP
PDF Price: $6.99

Cover | Table of Contents

---

Index

[ A ], [ B ], [ C ], [ D ], [ E ], [ F ], [ G ], [ H ], [ I ], [ K ], [ L ], [ M ], [ N ], [ O ], [ P ], [ Q ], [ R ], [ S ], [ T ], [ U ], [ V ], [ W ], [ X ], [ Y ],

A[ Top ]
above- or below-average values, formatting conditionally, 66
absolute cell references, 77
Alignment tab (Format Cells dialog), 21
AutoComplete, 29
      enabling, 48
      turning off, 48
      using, 58
AutoCorrect, 28
      using, 57
AutoExpansion, turning off for tables, 53
AutoFill options button, 50
AutoFilter, creating or removing, 84
autofitting columns and rows to contents, 48
AutoFormat as You Type tab, 29
AutoRecover, 13
      changing frequency and location of information saving, 35
AVERAGE function, 128
AVERAGEIF function, 128
AVERAGEIFS function, 128

B[ Top ]
back drawing layer (workbook file), 15
banding in Excel tables, turning on/off, 54
below- or above-average values, formatting conditionally, 66
black and white printing, worksheets, 39
books on Excel, 153
borders, formatting, 68
bottom or top list values, conditional formatting, 65
buttons, adding to Quick Access Toolbar, 7

C[ Top ]
Calculator command, 139
carriage return, entering in a cell, 49
case-sensitive searches, 42
cell references
      absolute, 77
      relative, 77
      streamlining with named ranges, 79
cells
      clearing contents, 61
      deleting, 46
      editing characters in a cell, 23
      editing contents, 51
      formatting, 21, 61-64
            borders, 68
            filling cells, 69
            styles, 23
      formulas, adding or editing, 76-78
      identifying in a worksheet, 16
      inserting into a worksheet, 45
      moving, 46
      selected, printing from active worksheet, 38
      selecting, 59
            keyboard shortcuts, 146
changes, tracking, 95
characters, editing in a cell, 23
charts, 99-101
      keyboard shortcuts for working with, 150
      PivotCharts, 104
CHOOSE function, 121
Clipboard, using, 60
Close All command, 140
Close button, 9
collaboration
      comments, controlling display of, 94
      data analysis with PivotTables, PivotCharts, 101-104
      sharing workbooks, 97
      summarizing data in charts, 99-101
      tracking changes, 95
      web documents, 98
color scales, conditional cell formatting with, 66
color scheme, selecting for Excel, 92
columns and rows
      adding, deleting, and moving, 45
      autofitting to contents, 48
      changing column width, 47
      changing row height, 47
      deleting, 46
            in tables, 53
      formatting, 20
      hiding or unhiding, 47
      identification, 16
      inserting, 45
      moving, 46
      repeating columns at left of printed page, 39
      repeating rows at top of printed page, 39
      right-clicking, 26
COMBIN function, 106
command-line switches, Excel startup, 141
commands, 139
      useful commands not in Ribbon list, 139
comments
      controlling display of, 94
      printing worksheet comments, 39
Compatibility Mode, 11
CONCATENATE function, 127
conditional formatting of cell content, 65-68
Constrain Numeric command, 139
context menus, 25
contextual tabs (user interface), 4
copying
      data, 59
      worksheets, 44
COUNT function, 129
COUNTA function, 130
COUNTBLANK function, 130
COUNTIF function, 131
COUNTIFS function, 132
current date and time, 115
custom properties, setting for a workbook, 33
cutting data, 59
Cycle Font Color command, 139

D[ Top ]
data bars, summarizing data conditionally, 66
data entry
      entering data efficiently, 48
      keyboard shortcuts, 147
data layer (workbook file), 15
data sets (alternative)
      defining, 80
      deleting, 82
      editing, 81
data validation, 51
dates and times
      formulas, 114-116
      using fill series with dates, 49
decimal places, number of, 65
detail level, 86
Developer tab, 92
dialog expanders, 5
dictionaries (spellcheck)
      adding terms to, 89
      creating or editing custom dictionaries, 91
      language, selecting, 90
disabled items in safe mode start, enabling, 35
draft quality for printing, 39
drawing objects, right-clicking, 27

E[ Top ]
editing data, keyboard shortcuts, 148
Enter key, changing default movement, 41
entering data (see data entry)
error codes, printing, 40
error messages (custom), for data validation, 52
Excel 2007
      new features, 1
      user interface, 2-14
Excel Help button, 8
Excel Most Valued Professional (MVP) Site, 153
Excel program window (see program window)
Excel resources, 152-155
      books, 153
      utilities, 154

F[ Top ]
FACT function, 107
file format (Excel 2007), 10
File menu items (Office Button), 6
file types (Excel 2007), 11
files, 14-17
      common tasks in Excel, 32-35
      default location, 34, 143
      native file formats, 140
      sheet tabs, 16
      workbook, 15
      worksheet, 16
fill series, 49
filling cells, 69
filtering
      PivotCharts, 104
      PivotTables, 103
      wildcards in filters, 142
      worksheet data, 84
financial formulas, 116-121
finding items in worksheets (see searches)
fitting printout with Scale to fit, 38
folders, default locations, 143
FORECAST function, 133
Format Cells dialog box, 21
formatting, 17-24
      cell borders, 68
      cell contents, 61-64
      cells, 21
      charts, 100
      columns and rows, 20
      finding and replacing, 43
      keyboard shortcuts to format data, 149
      numbers and dates, 64
      styles, 23
      tables, 54
      workbooks, 70
      worksheets, 17
formula bar, 8
      editing cell contents, 23
formulas, 105-137
      absolute references, 77
      adding to cells, 76-78
      auditing, 78
      date and time, 114-116
      financial, 116-121
      keyboard shortcuts, 148
      logical, 125
      lookup and reference, 121-125
      math, 106-113
      named ranges, using, 80
      new in Excel 2007, 105
      relative references, 77
      statistical, 128-137
      text, 126
      viewing in R1C1 reference format, 16
freezing panes, 19
FV (future value) function, 116

G[ Top ]
Go To dialog box, controlling items displayed in, 41
gridlines, printing, 39
groups, adding to Quick Access Toolbar, 7

H[ Top ]
headers and footers
      adding to a worksheet, 17
      creating, editing, and adding images, 72-75
height, changing for rows, 20, 47
hiding or unhiding
      columns and rows, 21, 47
      worksheets, 19
HLOOKUP function, 123
HOUR function, 114
hyperlinks
      creating, editing, and following, 70
      network path conversion to, stopping, 58

I[ Top ]
icon sets, conditional formatting, 67
IF function, 125
IFERROR function, 126
images
      adding or editing in headers and footers, 75
      back drawing layer of workbooks, 15
      background image for worksheets, 4
Insert Options button, 46
inserting or deleting
      cells, 22
      columns and rows, 20
INT function, 107
Internet sites for Excel resources, 152
IRR (internal rate of return) function, 117

K[ Top ]
keyboard shortcuts, 143-151
      charts, creating and manipulating, 150
      data entry, 147
      editing data, 148
      formatting data, 149
      formulas, working with, 148
      general program shortcuts, 144
      macros, working with, 150
      navigating a worksheet, 145
      PivotTables, displaying and hiding items, 151
      selecting data and cells, 146
      shortcut menus, manipulating, 150
      Smart Tags, 151
keyboard, using for worksheet navigation, 41

L[ Top ]
LEFT function, 127
Lighting command, 139
line styles (cell borders), 69
Live Preview, 91
logical formulas, 125
lookup formulas, 121-125

M[ Top ]
macro-enabled templates (.xltm), 13
macro-enabled workbook (.xlsm) files, 12
macros, keyboard shortcuts, 150
margins, changing for headers and footers, 75
margins of a workbook, changing, 39
mathematical formulas, 106-113
MAX function, 134
Maximize button, 9
MEDIAN function, 135
merging cells, 63
Microsoft Office Fluent interface design (see Ribbon user interface)
Microsoft TechNet web site, 152
Microsoft Template Gallery web site, 153
Microsoft, Official Excel Site, 152
MID function, 127
MIN function, 135
Mini Toolbar, displaying, 92
Mini Toolbar (shortcut menus), 27
Minimize button, 9
MINUTE function, 115
MODE function, 135
MONTH function, 115
moving worksheets, 44

N[ Top ]
name box, 9
named ranges, using, 79
native file formats, 140
navigation, keyboard shortcuts, 145
navigation in workbooks or worksheets, 40-43
network paths, conversion to hyperlinks
      stopping, 58
New command, 140
newsgroups, Excel, 153
NOW function, 115
NPV (net present value) function, 119
number or worksheets in a workbook, changing, 45
numbers
      filtering in worksheet data, 85
      formatting, 65

O[ Top ]
Office Button, 6
      AutoComplete, turning on, 29
      AutoCorrect options, 28
      AutoRecover feature, 13
      Smart Tags, turning on, 29
Office Clipboard, using, 60
Office Themes
      applying or modifying, 70
      applying to workbooks, 19
Office Update web site, 152
Office Zealot blogs, 152
order of worksheets, changing, 44
orientation of printed page, 38

P[ Top ]
page breaks
      inserting, 58
      previewing before printing, 37
page order, changing for printing, 40
page range, printing from a worksheet, 37
passwords, protecting workbook data, 87
Paste Options button, 60
pasting data, 59
PERMUT function, 107
personal information, protecting, 34
pictures (see images)
PivotCharts, 104
PivotTables, 101-104
      keyboard shortcuts to display and hide items, 151
pixels, column width and row height, 20
preview picture, saving with a workbook, 33
print area of worksheets, 36
print previews, 36
privacy, protecting for personal information, 34
PRODUCT function, 135
program window, 7
      controls, 9
      Select All button, 9
      status bar, 10
      tab bar, 10
      workbook window controls, 9
properties (custom), setting for a workbook, 33
protecting data in workbooks, 87
Publish As Web Page command, 139
PV (present value) function, 120

Q[ Top ]
Quick Access Toolbar, 7
      adding/removing buttons, 27
      customizing, 93
      data entry forms, 55
      moving below the Ribbon, 93

R[ Top ]
R1C1 reference format (columns and rows), 16
RAND function, 107
RANDBETWEEN function, 108
range of pages, printing from a worksheet, 37
ranges of cells
      converting table to cell range, 53
      named ranges, using, 79
      repeating a value across, 49
recently used files, changing number on File menu, 34
records
      editing, 56
      navigating among, 56
recovering a workbook after Excel crash, 35
Redo/Repeat button, 7
reference formulas, 121-125
relative cell references, 77
renaming worksheets, 44
reordering worksheets, 44
replacing items in worksheets, 42
resizing tables, 53
Restore button, 9
Ribbon user interface, 3
      adding button or group to Quick Access Toolbar, 7
      contextual tabs, 4
      dialog expanders, 5
      hiding/showing the Ribbon, 93
RIGHT function, 127
right-click menus, 25
ROUND function, 108
ROUNDOWN function, 109
ROUNDUP function, 109
rows (see columns and rows)

S[ Top ]
safe mode, starting Excel in, 35
Save AutoRecover info every x minutes, 13
Save button, 7
scale of printing, 38
Scenario command, 139
scenarios
      defining, 80
      deleting, 82
      editing, 81
ScreenTips, showing or hiding, 92
scroll bars, hiding, 40
searches, 41-43
      wildcards, 142
SECOND function, 115
security
      macros, 12
Select All button, 9
Select Visible Cells command, 139
selecting
      data and cells, keyboard shortcuts, 146
      text and data, 59
series, filling automatically, 49
sheet tabs, 16
      changing color of, 45
      right-clicking, 27
shortcut menus, 25
      keyboard shortcuts, 150
      Mini Toolbar, 27
shortcuts, keyboard (see keyboard shortcuts)
Shrink to fit checkbox, 21
Smart Tags, 29
      keyboard shortcuts, 151
sorting worksheet data, 82
special characters, inserting, 58
spelling
      automatic correction, 28
      checking in worksheets, 88-91
Spreadsheet Solutions, templates, 14
starting Excel in safe mode, 35
startup command-line switches, 141
statistical formulas, 128-137
status bar, 10
      right-clicking, 27
STDEV function, 136
STDEVP function, 136
styles, 23
      cell, 63
      line styles for cell borders, 69
      PivotTable, 103
      table styles, 54
SUBTOTAL summarizes, 109
subtotals, 86
SUM function, 111
SUMIF function, 111
SUMIFS function, 113
summary information, changing for a workbook, 33
switches, startup, 141
symbols, inserting, 58

T[ Top ]
tab bar, 10
      sheet tabs, 16
tables
      creating, managing, and formatting, 52, ??-54
      PivotTables, 101-104
      references to, in a formula, 78
TechNet web site, 152
template (.xltx) files, 12
templates
      Microsoft Template Gallery web site, 153
      tips on using, 13
text
      finding and replacing, 42
      finding in a worksheet, 41
      formatting in cell contents, 61
      formulas to manipulate text values, 126
      inserting, 58
      selecting, 59
title bar (program window), 7
top drawing layer (workbook file), 15
top or bottom list values, conditional formatting, 65
Total row, 54
trend lines in charts, 101

U[ Top ]
Undo button, 7
user interface (Excel 2007), 2-14
      contextual tabs, 4
      dialog expanders, 5
      file types, 11
      new file format, 10
      Office Button, 6
      program window, 7
      Quick Access Toolbar, 7
      Ribbon, 3
      templates, tips on using, 13
utilities, Excel, 154

V[ Top ]
validation of data, 51
VAR function, 136
VARP function, 137
views, creating custom views, 87
VLOOKUP formula, 121

W[ Top ]
web documents
      saving workbook as, 33
      saving worksheet or workbook as, 98
      updating based on workbook updates, 99
Web Page Preview command, 139
WEEKDAY function, 115
width
      autofitting column to its contents, 48
      changing for columns, 20, 47
wildcards in searches and filters, 142
Windows, printing workbooks from, 38
Woody's Watch web site, 152
workbook files (.xlsx), 11
workbook files, macro-enabled (.xlsm), 12
workbook window controls, 9
workbooks
      creating from templates, 14
      formatting, 70
      protecting data, 87
      saving as Web page, 98
      sharing, 97
      structure of workbook file, 15
worksheets
      creating template for, 14
      custom views, creating, 87
      filtering data, 84
      formatting, 17
      navigation
            keyboard shortcuts, 145
      organizing with sheet tabs, 16
      saving as web page, 98
      sorting data, 82
      structure of worksheet file, 16
      subtotals, using, 86
workspaces
      saving all open workbooks as, 33
      workspace (.xlw) files, 13
Wrap text checkbox, 21

X[ Top ]
.xlsm (macro-enabled workbook) files, 12
.xlsx (workbook) files, 11
.xltm (macro-enabled template) files, 13
.xltx (template) files, 12
.xlw (workspace) files, 13

Y[ Top ]
YEAR function, 116

Return to Excel 2007 Pocket Guide