Embed or link this publication
Popular Pages
p. 2
excel 2007 beyond the manual copyright © 2007 by helen dixon all rights reserved no part of this work may be reproduced or transmitted in any form or by any means electronic or mechanical including photocopying recording or by any information storage or retrieval system without the prior written permission of the copyright owner and the publisher isbn-13 pbk 978-1-59059-798-9 isbn-10 pbk 1-59059-798-2 printed and bound in the united states of america 9 8 7 6 5 4 3 2 1 trademarked names may appear in this book rather than use a trademark symbol with every occurrence of a trademarked name we use the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark lead editor jonathan hassell technical reviewer judith myerson editorial board steve anglin ewan buckingham gary cornell jason gilmore jonathan gennick jonathan hassell james huddleston chris mills matthew moodie jeff pepper paul sarknas dominic shakeshaft jim sumser matt wade project manager richard dal porto copy edit manager nicole flores copy editor liz welch assistant production director kari brooks-copony production editor laura esterman compositor dina quan proofreaders elizabeth berry and lori bring indexer valerie perry artist april milne cover designer kurt krames manufacturing director tom debolski distributed to the book trade worldwide by springer-verlag new york inc 233 spring street 6th floor new york ny 10013 phone 1-800-springer fax 201-348-4505 e-mail orders-ny@springer-sbm.com or visit http www.springeronline.com for information on translations please contact apress directly at 2560 ninth street suite 219 berkeley ca 94710 phone 510-549-5930 fax 510-549-5939 e-mail info@apress.com or visit http www.apress.com the information in this book is distributed on an as is basis without warranty although every precaution has been taken in the preparation of this work neither the authors nor apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work the source code for this book is available to readers at http www.apress.com in the source code download section.[close]
p. 4
contents at a glance about the author xix about the technical reviewer xxi acknowledgments xxiii introduction xxv chapter 1 chapter 2 chapter 3 chapter 4 chapter 5 chapter 6 what s new in excel 2007 1 customizing excel 25 excel essentials 39 styles and formatting 59 sorting and filtering 89 analyzing data using subtotals consolidations and tables 105 creating pivottables and pivotcharts 131 editing pivottables and pivotcharts 153 what-if analysis 171 formulas and functions 187 charts 231 adding graphics to a worksheet 251 preventing and correcting errors 277 protection and security 299 getting data from external sources 325 sharing reviewing and distributing data 341 sharepoint and excel services 363 location of popular excel 2003 commands in excel 2007 379 selecting data in a pivottable report 385 chapter 7 chapter 8 chapter 9 chapter 10 chapter 11 chapter 12 chapter 13 chapter 14 chapter 15 chapter 16 chapter 17 appendix a appendix b index 387 vii[close]
p. 5
contents about the author xix about the technical reviewer xxi acknowledgments xxiii introduction xxv chapter 1 what s new in excel 2007 from excel to xxl 1 1 excel gets a makeover 4 the ribbon 5 the microsoft office button 6 the quick access toolbar 7 access keys 9 enhanced screentips 11 page layout view 12 better-looking documents 12 themes and styles 12 more templates 13 smartart 14 easier analysis 15 rich conditional formatting 16 excel tables 17 pivottables and pivotcharts 17 sorting and filtering changes to charts 18 18 simplified formula writing 19 more functions 20 ix[close]
p. 6
x contents finishing touches 21 external connections 21 new file formats 22 new ways to share data 23 chapter 2 customizing excel 25 excel options 26 customizing the quick access toolbar 28 customizing the status bar 31 custom views 32 customizing your excel windows 34 freezing panes 34 splitting panes 35 36 working with multiple windows comparing windows side by side 38 chapter 3 excel essentials 39 entering and editing data 40 custom lists 42 creating a series 44 working with paste 45 paste special 46 paste options 48 defining names 50 defining a name 51 managing names 53 creating a name from a selection 53 creating a dynamic range 54 working with multiple worksheets 54 inserting worksheets 55 renaming worksheets 55 deleting worksheets 56 moving and copying worksheets 56 entering and formatting data in multiple worksheets simultaneously 57[close]
p. 7
contents xi chapter 4 styles and formatting document themes 59 60 applying a document theme 60 customizing a document theme 61 cell styles 63 applying a cell style 64 modifying a cell style 64 creating a custom cell style 65 table styles 66 applying a table style 66 changing or removing a table style 68 creating a custom table style 68 manually formatting worksheets 69 formatting numbers 70 custom number and date formats 73 creating custom number formats 74 creating custom date and time formats 78 deleting a custom number or date format 79 conditional formatting 80 highlight cells rules 80 top/bottom rules 82 data bars 83 color scales and icon sets 83 creating custom rules 84 removing conditional formats 85 managing conditional formatting rules 86 chapter 5 sorting and filtering 89 sorting data 89 filtering data 91 using filter 92 advanced filtering 98[close]
p. 8
xii contents chapter 6 analyzing data using subtotals consolidations and tables inserting subtotals and outlining data calculating subtotals 105 106 106 removing subtotals 108 outlining data 109 consolidating data 114 consolidating by formula 115 editing a data consolidation 116 excel tables 116 creating a table 117 adding and removing table rows and columns 120 using calculated columns in a table 124 using structured references in a table 126 removing an excel table chapter 7 130 creating pivottables and pivotcharts 131 pivoting 131 what are pivottables 134 what type of data can be summarized in pivottables 134 creating a pivottable report 135 adding and removing fields 138 switching between automatic and manual updating 138 selecting data in a pivottable 139 grouping and ungrouping fields 140 expanding and collapsing fields 141 formatting a pivottable 141 pivottable styles 142 creating a custom pivottable style 142 changing the layout and cell display 144 adding and removing subtotals and grand totals 146 creating a pivotchart report 147[close]
p. 9
contents xiii chapter 8 editing pivottables and pivotcharts 153 sorting a pivottable or pivotchart field 153 filtering data in a pivottable or pivotchart 157 using the report filter area 159 changing field settings 161 refreshing a pivottable report or pivotchart report 162 changing the data source for a pivottable 163 changing the location of a pivottable or pivotchart 164 creating formulas using calculated fields or calculated items inserting a calculated item 164 inserting a calculated field 165 166 pivottable options 168 deleting a pivottable or a pivotchart report 169 chapter 9 what-if analysis 171 data tables 172 creating a one-variable data table 172 creating a two-variable data table 174 scenarios 176 displaying a scenario and creating a summary report 178 editing and deleting a scenario 179 merging scenarios 180 goal seek 180 solver 182 chapter 10 formulas and functions 187 constructing formulas 188 error values in formulas 191 formula autocomplete 192 using relative and absolute referencing 193 formulas and defined names 195 referring to cells in other worksheets and workbooks 195 array formulas 197 controlling when formulas are calculated 199[close]
p. 10
xiv contents using functions 200 206 financial functions 201 logical functions text functions 209 date and time functions 212 lookup and reference functions 216 math and trigonometry functions 219 statistical functions 223 information functions 227 database functions 228 chapter 11 charts 231 creating charts 231 chart types in excel 232 creating a chart from data in a worksheet 236 charting data from a different worksheet 237 editing charts 238 changing the location of a chart 238 changing the type of a chart or data series 239 changing the data source for a chart 239 adding a secondary axis 242 analysis features for charts 243 formatting charts 246 selecting a chart layout and style 246 manually changing a chart s layout 247 manually changing a chart element s style 248 saving a chart as a template 249 deleting a chart or a chart element 250 chapter 12 adding graphics to a worksheet 251 inserting pictures and clip art 251 inserting a picture from a file 252 inserting clip art 252 formatting pictures 254 adjusting pictures 255 changing the style of pictures 257[close]
p. 11
contents xv inserting shapes 257 formatting shapes 260 changing the style of shapes 260 displaying text or cell contents in shapes 261 resizing pictures and shapes 262 cropping pictures 264 rotating or flipping pictures or shapes 265 filling a shape or chart element with a picture 265 creating and editing smartart 266 changing the structure and layout of smartart 268 displaying cell contents in smartart 269 formatting smartart 270 resizing smartart 270 changing the style of smartart 271 using wordart 271 changing the stacking order of objects 273 aligning and distributing objects 275 chapter 13 preventing and correcting errors 277 data validation 278 allowing numbers in a range 278 allowing values from a list 279 allowing dates or times in a range 281 allowing text of a particular length 282 creating custom validation criteria 283 displaying an input message and an error alert 283 changing or removing data validation 286 removing duplicate values 286 formula auditing 286 identifying errors in formulas 287 error checking 289 dealing with circular references 291 viewing and hiding formulas 292 viewing the relationships between cells 293 evaluating formulas 295 watching formulas 296[close]
p. 12
xvi contents chapter 14 protection and security 299 protecting worksheets and workbooks 300 protecting the contents of a workbook 300 protecting the contents of a worksheet 302 protecting the structure of a workbook 308 protecting a shared workbook 309 digital signatures 310 using a signature line 311 viewing a digital signature 313 the trust center 314 adding viewing and removing trusted publishers managing application add-ins 315 adding changing and removing trusted locations 316 318 changing settings for activex controls 319 changing settings for macros 320 turning the message bar on and off 321 changing settings for external content 321 changing privacy options 322 chapter 15 getting data from external sources 325 connecting to external data sources 325 importing data from an existing connection 326 importing data from a microsoft access database 328 importing data from the web 332 importing a text file 333 importing data from other sources 336 managing data connections 337 refreshing external data 337 editing workbook connections 339 changing the format and layout of an external data range 340[close]
p. 13
contents xvii chapter 16 sharing reviewing and distributing data sharing and reviewing workbooks 341 342 creating a shared workbook 342 editing a shared workbook 344 tracking changes 345 using comments 347 adding a comment to a cell 348 viewing comments 348 printing comments 349 349 editing and deleting comments preparing a worksheet for printing 350 using headers and footers 351 changing the layout for printing 352 using print preview 355 preparing a document for distribution 356 changing the document properties 357 inspecting a document 357 protecting and finalizing a document 359 compatibility issues and saving in other formats 361 chapter 17 sharepoint and excel services excel services 363 microsoft office sharepoint server 2007 364 365 366 371 excel services architecture publishing a workbook to a server 367 displaying an excel workbook in excel services supported and unsupported features in excel services 374 interacting with spreadsheets generated by excel services 376 editing a workbook published in excel services 376 removing a workbook published to excel services 377[close]
p. 14
xviii contents appendix a location of popular excel 2003 commands in excel 2007 appendix b 379 selecting data in a pivottable report 385 index 387 1cf89c68be7952065b426ef882b98939[close]
p. 15
about the author helen dixon has been working as an it trainer for almost ten years specializing in microsoft excel she currently works for queen s university in belfast training staff and students in it and providing a consultancy service for staff her qualifications include a bachelor s with honors in business studies and a master s in computing and information systems and various it qualifications including mos excel 2002 expert and ecdl advanced spreadsheets she is currently studying for a phd in electronic commerce at the university of ulster xix[close]
Comments
no comments yet