Wiley Excel 2010 Visual Quick Tips
Contents
1. Excel opens the label text for editing Type the name you want i ala Jc J 9 J J E T to use for the control EE 8 Click outside the control Excel removes the E selection handles from E the control E Note To select the control later on hold down Ctrl and click the control Wde Sheer Sheet g ahaaa A AO ila E aog m Important Did You Know To use the worksheet form controls you When you are dragging the control on the must customize the Excel Ribbon to worksheet you can make the control s display the Developer tab Right click border snap to the worksheet s cells by any part of the Ribbon and then click holding down the Alt key as you drag If Customize the Ribbon The Excel Options you want the control to be a perfect square dialog box appears with the Customize hold down Shift as you drag If you want Ribbon tab displayed In the Customize the control to be centered on the spot the Ribbon list box click to select the where you start dragging hold down Ctrl as Developer option and then click OK you drag Use Dialog Box Controls to Input Data continued Adding a form control to a worksheet does not option the first option returns 1 the second do very much by itself To make the control option returns 2 and so on Scroll bars and useful you must link it to a worksheet cell spin boxes return a value from a range of That way when the user changes t2. s s ssssssssssessessesssessesssssessssssssassecsecssenssesateseeseesneess 36 Customize the Quick Access Toolbar You can make Excel easier to use by customizing the Quick Access Toolbar to include the Excel commands you use most often Because you launch Quick Access Toolbar buttons with a single click adding your favorite commands to the toolbar saves you time By default the Quick Access Toolbar contains three buttons Save Undo and Redo but you can add any of hundreds of Excel commands In a default Excel configuration the Quick Access Toolbar appears above the Ribbon as part of the Excel title bar However this position only allows you to add a few buttons as there is only so much space in the title bar To get much more space to add buttons you should move the Quick Access Toolbar below the Ribbon You can also export your Quick Access Toolbar customizations to a file so that other people can import the same customizations For more information see the section Export Ribbon Customizations to a File Click the Customize Quick Access Toolbar button Ifyou see the command you want you can click it Excel adds the button for that command to the Quick Access Toolbar and you can skip the rest of the steps in this section Click More Commands 1 Har Sheets Shee ra 0 Fe Hore Cusiumise Quek Area Toue h pa Rey Wew Deelge amp ES coe Sook Micro
3. Customize the Quick Access Toolbar cccccccccsscccsescscscscscscscecscececacacscacacecacacacacscececscaceees 4 GUStOMZEHTERIDDO ee nate eee ene ae 6 Export Ribbon Customizations to a File sscssssssssssssssssesssssssssssssecssessessaesaceseeseesseess 10 Configure Excel to Use the Mouse Wheel for ZOOMING s ssssssssssessesssesesseessesses 12 Move in a Different Direction When You Press Ente sscsssssssssessesesssessesssesseesees 14 Automatically Insert a Decimal POINK sssssssssssessssssessessesssssesssssessssseseeseeescessesens 16 Configure When Excel Warns You About Long Operations sssssssssesseesesseeseeees 18 Pin Excel to the Windows 7 Taskbal sss sssssssssssssssssssssesssssssssssssessesssesassssesassssesaeesss 20 Make a Workbook Faster by Saving it as BINAry sscsssssessssssssssssessessssseesesseeseeseesseens 22 Open a New Window for a Workbook sscsssssessesssessessessesssssssssssscsssssesssssseeseeseesseess 24 Allow Only Certain Values in a Cell ccsssssssssssssssssssssssessessessssssssssssessssssssessesessessesess 26 Apply Text or Formatting to Multiple Worksheets s sssssscssessessessssssssesssssseseesees 28 Quickly Display the Office Clipboard ssssssssssessessssssessessessssssessessessseseesesseeseeseesseess 30 Use Dialog Box Controls to Input Data eee ee ee rinEE ee EEan 32 Check for Accessibility Problems
4. J 513 695 74 55 359 76 S7 34259 815 025 1 Aksios nimate EE 4 512 686 71 5 436 09 57 262 62 821 2592 yry Fim 14 5 512 656 79 5 515 60 87181 10 26 774 Aftemate text helps reeder undersiand itanmetion EA 512 656 71 35 556 24 57 056 98 eae a7S7 preserted im picbures and other clyects i 7 512 656 44 55 682 31 S701440 4535 0661 one ir a 512 656 74 5 767 55 56 929 17 543 823 To add attemate test to an abject 13 F 512 656 74 35 364 06 B5 94265 S49 67 7 7 1 Right click on the object anc select the Format 13 10 512 695 74 55 041 87 35 784 84 565 618 commana For tres object type 20 11 12 606 71 6031 00 fS5 605 72 e1 a aae a deanna ur e obpect into the text bak 21 12 512 696 74 36 121 45 55T525 SETTT1A annie EA a 512 695 71 GENI PAAA p73 ee daeoni ij 14 12 686 77 36 306 45 __ 88 390 25 _ 60 291 aoe frog mone obout making socunents ooosable 4 H Dynamic Amortization schedule cant 4 os Ready Fal Hf Ga aco ial F Important If you know your worksheet will be used by people with disabilities you should build a new sheet with accessibility as your goal Here are a few pointers for making a worksheet more accessible Make extensive use of text headings to annotate the worksheet and make it easier to understand the structure of the sheet In particular every row and column should have a unique heading
5. Leer Interface opha T Shire bni Towbar orn selecliz ae ahte liar Brewis chens Oefautt Soeentp bia Show deeture ceenpiecns in Soreenhps x im aealg tHe weak bouki Upe tnis aak Fort sie Body Fani n a Hemal Yisa Default yew for see heet L Inriude the mary sheets k Penuninaler pou copy od Miert Hra Dra name Faul Chapter 1 Making Excel More Efficient Click to select the Automatically Insert a Decimal Point check box Use the Places spin box to specify the number of decimal places you want Excel to add automatically Click OK Gener r Advanced options tor working with Excel Prohing Eating optons 7 eter prevung Enter more setioan Cerection Dowen ee Customize Ribbor 4 Eneite fl handle anid cell drag and drop Slert estore mereming ol F Slow acting direct in calls Add in F Cetend dyta range foreats and formulas F Ense sutomastic peren enby F Ensita Ainol ormplete tor cell values Hukk Anass lectern Inet Center Zoor on roll vaih InteliMouse A Ael lhe uia when a polectially lire coming operelion occur When Gh member ol cells fn thousand im atlectect 33554 oo x Use sprten aparahors Dermal cepacstor T 6 ok cane Excel now automatically inserts the number of decimal places you specified when you enter a numeric value into a cell Excel displays Fixed Decimal in the status bar to remind you that it will aut
6. and then follow the steps in this section to customize it Remove It Right click any part of the Ribbon and then click Customize the Ribbon the Excel Options dialog box appears with the Customize Ribbon tab displayed To restore a tab click the tab click Restore Defaults and then click Restore Only Selected Ribbon Tab To remove all customizations click Restore Defaults and then click Restore All Ribbon Tabs and Quick Access Toolbar Customizations E Export Ribbon Customizations to a File You can make it easy to apply Ribbon and customizations on the other computer so that Quick Access Toolbar customizations on you are dealing with a consistent interface no another computer by exporting your own matter where you do your spreadsheet work customizations to a file Rather than wasting valuable time repeating Customizing the Ribbon or the Quick Access the same customization steps on the other Toolbar is not a difficult process but it can be computer you can export your customizations time consuming particularly if you want to to a file You can then import that file on the make a substantial number of changes If you other computer and Excel automatically use Excel 2010 on another computer it is applies the customizations for you likely that you will want to have the same Right click any part of the acp ile ae arar FPPP Ribbon z 7 m p guslum PEEN faethe ae Pir i e E Finary 1 rie ere afrow
7. you often for the workbook and then arrange those need to monitor a cell value For example if windows side by side vertically or you change the data in one part of the sheet horizontally This enables you to display what you might want to see how that change affects you are currently working on in one window the result of a formula elsewhere in the sheet and what you need to reference or monitor in the second window Create a New Workbook Window OCRE T D ied dome Insert Page Laroui Fornulas Oaks Revise Winw ah 6 m po FS Open the workbook you J Deere aaua rants Q A By a 5 J Custom Views E arrange Al T Hide ZH ag want to work with H aridin it Heading Taom 100 Fooanmtn KLTL hvebch Marms Grid nes pared Selection Ge Freeze Panes E Unhid GO woriepae Winds iwib ogi wies Shw Ivon Aind Macros Pane i Layout E Full ereen i i r k STVI D 5 A11 855 SB 5 2 Click the View tab mr S erwsoss a1 855 5654 856 E F E D E E E E Click New Window mk j 2 Nominal Rate APR E00 Deposit Frequency ams 1 Annually 3 Term Years 10 Deposits Per Year 1 Semi Annually 4 Initial Deposit 5100 000 Compounding Frequency Seritnramiy 2 Quarterly 5 Periodic Deposit 55 000 Compounds Per Year 2 Monthly E Deposit Type O Effective Rate Per Perlod 6 09 Weekly Total Periods 10 Daily cl gt Investment Schedule Interest Cumulative Total 13 Period Earned Cumulative Interest Deposits In
8. Do not overuse white space such as blank rows and columns White space usually helps make a worksheet look less cluttered but a sheet that has little or no white space is much easier for the disabled to navigate Use Excel formatting such as row heights and column widths to create space within the worksheet Use named ranges whenever possible as named ranges are relatively easy to navigate using the Go To command in Excel To name a range select it click the Formulas tab and then click Define Name EJ
9. In t e 1 i r 5 Drop Doan 3 Dynami Ammtiralion Schedule Tetai Lean Costs 1 595 58 6 106 95 46 36 a ce a A Inspection Results k z oir ee is By 3 Drop Doan Mortgage Paydown Snails 9 B 4524842 4491 3 ower section 10 S 249 16 S4417 Sh cu n 10 149 91 543 42 i EAS manit Anonicstion Schedule 7 12 6o 192 37 0 96 59 Tej Additional nfo matian ka 14 Setari and fir soch mae Ushed abawe to mahe Dals docusert 1 accesi for people with disabiiihes Reog mare oboat mating secured oooh if 13 13 21 a H a WL falon ioan g nieret Costs Pring AOO OOO Ready Fal leaf AL B D ET accessibtty Checker Excel uses the Additional Loan Data m irepertion Bessits 7 Intarect Rata Annual 600S Tima Basis ame Information section to tell FA S S500 ns S Te A isingit tet h h Id fi Balloon Payment 0 Total Perlode E Z POO OR S LK Payment Type i So ye T 1 the problem and the i Amortization Schedule Orap Down Dynamic Amortization Schedule steps required to fix it Drop Down 3 Mortgage Paydown onal Drop Doan 3 Mortgage Paydown Analysis 9 Period Pa J Warnings q targad Celh 10 1 512 695 74 5196 71 S7 600 00 85 1963 z x a k eh f dination i x Ti 7 51 686 74 5974 GE eraran eo ard ey Ae Ormani Amoritation Scheduley
10. Quek Access kaakaa arame eani Fanart E yen Fabled Wark onk Click Customize the B oe E ip Ribbon 4 shett Sheet g sheaa Po Reedy E The Excel Options dialog mo M O O H box appears saii i Cuetumize the Hikbun Excel automatically ei Famur sommare z beme z displays the Customize A pn 3 m Ribbon tab aaner ig kee S m Sec ac uae 3 Click Import Export Quick Anus Toobar a aa pea Add Ira S Peny cele H F Oaks Click Export All Ribbon rn cre B male Insert Sheet Colurens E E Developer and Quick Access Toolbar mian ut Ram baem u m Customizations p al Fane Spec oa Fiture a eae EB quick pict Di Recs Baresi Save ii aa l hew Tab kew Group Rename 43 eet Dascending Cusloandaliaori Basora Duranta eporttport Inport custeminban file Chapter 1 Making Excel More Efficient E File Save im The File Save dialog box Me es Libraries Docurnerts b 0O Search Documents fa appears Crganige Hew folder x ies E oan Choose a location for the aa E ee Arrange iy Folder customization file Wr Favorites ipsa eit aana a Type a name for the file m Porskak ke Outlook Files MAAA 4 65 P File Foldet E irea iL Sragh Catalog BELG 1219 PM File Tolder A Click Save Sq Libraries Dorunmerte a Mise i Pictures Videos Say E n F Filename Excel Customizations 9 Save ac type Exported Office Ul file Hide Folders Cancel Exelptons HES
11. The standard file formats in Excel Excel Workbook and Excel Macro Enabled Workbook are based on the OpenOffice XML Standard where XML is short for eXtensible Markup Language XML files are really just complex text files that Excel reads line by line when you open the file and writes line by line when you save the file Excel has been optimized to read and write XML code extremely quickly and so the standard Excel file formats are fine for most worksheets However if you have a worksheet that is very large for example several thousand rows or more or several hundred columns or more or is very complex then the standard file formats may take a while to open and save To improve the performance of such files you can convert them to the Excel Binary Workbook file format This is identical to the Excel Macro Enabled Workbook format except that it uses binary code which Excel can read and write much faster instead of XML code Open the workbook you want to convert Click the File tab Click Save As The Save As dialog box appears Select a location for the new workbook Type a name for the new workbook Note Because the new workbook will have a different file extension xlsh you do not need to change the filename if you do not want fo Click the Save As Type drop down arrow Ey Leers Microsoft oe l m j Fage Lapu Farmiuias Daia Raika Wew r T at
12. fe Sane g a Fp Ir ation about Loans Sane As 5 Talka Paul Documerts l oerseks E Open er i Clow so are Permissions a Anyore can open copy and change any partot this Fraperties workbosk Protect Sra WIREH Recent Workbook Title Tags Bis r i Prepare for Sharing o Prirt 7 Ealore sharing thie file be avare Pat i contaire paiia ae 2 bra Comenent Last Motes L520 207 PA Tare amp Serd EE A Qreumert praperties prinber path author s rame and Created SO L156 AM related dates Last Frinted VSADE SDM Help Invinible ubjecta Conbent thai gtople with disabilities oe unable bo read Felsted Feople G Oeti author Paul MeFedries Ed Ext Versions i l 1 La ModHled By Paul M Fedres ral D Yesterday TIL AM iwhan I dosed without saving 5 Manaye Belaled Cocurmemts B Seve ds i ety pace Pelee foder F a fey Microsott Office Ey Autom Paul Md eds c Hide Folders E o Lisrerigs amp Documents 0 Documents library p B ataman A Horegrup i Cet 1 Fig mana Loans Tawe as Typ Excel Workbook r Save Thurid Email Indudes 2 bocekions Ur Favorites Name ste modii lype E Desktes E Deweicads b Dimibok ies egs ih Recent Places J Enagk Catalog iS 2007 Egat E Libraries E 2007 Seles Fa E 2007 2008 Budget a Music GE 2009 2000 Budget E Pictures id Analyzing Finercisl Data q Aaa isi Chart Eeamples E Cercitienal Formalia Tage Add atag tools c
13. file format is compatible with Excel 2010 and Excel 2007 If you want to improve file performance while maintaining compatibility with earlier versions of Excel save your workbook using the Excel 97 2003 Workbook file format This is a binary format that is compatible with Excel 97 and all later versions of the program Click Excel Binary Workbook Click Save Excel saves the new file using the Excel Binary Workbook file format Did You Know Other than improved performance when opening and saving a file there is no difference between the Excel Binary Workbook file format and the Excel Macro Enabled Workbook file format Both formats support the same features create files of approximately the same size and have the same performance once the files are loaded into Excel Open a New Window for a Workbook You can make a large spreadsheet easier to This is easy with a small spreadsheet where you manage by creating a second window for the can see everything on the screen However workbook larger spreadsheets do not fit into a single When you are building a spreadsheet you screen so the data you need to reference or often have to refer to existing sheet data For monitor might not be visible requiring that example when you construct a formula you you scroll through the sheet to see it may need to refer to specific cells Similarly A better solution is to create a second window once your spreadsheet is working
14. message title Type the message you want to display Click OK ee Oo When the cell is selected the input message appears Remove It If you no longer need to use data validation on a cell you should clear the settings Follow steps 1 to 3 to display the Data Validation dialog box and then click the Clear All button Excel removes all the validation criteria as well as the input message and the error alert Click OK Apply Text or Formatting to Multiple Worksheets You can speed up the creation of spreadsheet models by applying text and formatting to multiple worksheets at once In most workbooks the worksheets are related in some way but they generally have significantly different structures However in certain cases each worksheet uses an identical structure For example each worksheet might have the same overall title Similarly in a budget workbook each worksheet might have not only the same title but also the same headings Income Expenses and so on If you just have a small number of worksheets you can also complete one worksheet s structure copy the range and then paste it into the other sheets For a large number of sheets however Excel offers a much faster method You can collect all the worksheets into a group where Excel treats the collection of sheets as a single worksheet This means that any data you enter into one sheet is automatically entered on the same spot in every o
15. Chiaboari kon on Taskbar contains two or more aise l items MAr H sheeti Sheet2 Sheets i A IE a0 reaw a may 100 _ 3 10 11 12 13 44 15 i 4 A H Sheeta Sheet Sheeta oy IE i Chapter 1 Making Excel More Efficient Z bed ir o Bool Microsoft Excel Display the Office File Home e ayaki Formulas Data ETETEA Vig Co 7 F Z a 4 x calibri rio General Fig Conditional Formatting Ge tnet E Ay j Clipboard Using the Jae BOF We x ES r oo Fema a Tabi 3 Datate E bi oe Keyboard K F a7 oe TE i a Cell Styler 7 fy Femat 7 a TOR T soot Se AE eee pene see Click the Home tab launcher icon s So T In the Clipboard group click the dialog box F f Ready T l 3 100 lt 0 ch Clipboard Tik an ilen bo paste Show Office Clipboard When Cirk Pressed Twice Collect tiithout Showing Office Dipbosed S T DT The Office Clipboard task pane appears Click Options Click Show Office Clipboard When Ctrl C Pressed Twice Excel now displays the Office Clipboard automatically whenever you press Ctrl C twice in sheets 0 roo E d i 30 Da m G a row More Options By default the Office Clipboard icon appears in the notification area of the Windows taskbar When you have the Office Clipboard displayed and you cut or copy a
16. Excel saves the Ganera Ep Foni he customizations to the file n Ehoose commends fron Customize the Ripbor Click OK eas Popular Commands basin Tabs zme ih All Chart Typis a Main Tabs ne f calculate How H af Home Advanced EL Calculate Shee H E My Tab Custom FH Custom so My Tab Group Muxia customize upon Ze Delete Cells Macros Muslar QP Delete Sheet Columns fa wt Insert Quick Access Toolbar Deele Sheel Rows FA at Page Layout E j E mail FA st Formulas pans A Formed Cells E Lt Deka Trust Center H Hypelir l Ea Ferie 3 Insert Cells fH Lt View T Insert Sheet Colurns ml _ Developer Sra insert Sheet ows CA Lt Add ins _ b Macros i F Background Removal F New Bll d E Open aa Remove a Open Recent File Py Paste Specal i Picture 3 PiwotTabee Print Previews i Quick Print Redo iI Repeat fed iwe Set Print 4rea SS A J fort Ascending NewTab Hew Group _ Rename A Sort Descending Customizetions Restore Defaults Spelling Di Tete x Cancel Apply It To apply the Ribbon and Quick Access Toolbar customizations on another computer running Excel 2010 you need to import the customization file that you exported by following the steps in this section Note however that importing a customization file replaces any existing customizations that you have created On the computer you are customizing right click any part of the Ri
17. Interest Raia j nnuai Amortization fen 2 lerm Fear u Frinc pal i Parmer Type a Amartization Schedule Tarm intaraet Dumalab Cormelatin Aameining 4 amortization Year Perle Rais MEER Payment Principe Imeem Principal in ereen Principal REDENN Laar Pupa 2iuayee Fhorbais Arlalen hehehe Furl yap Hapdi 4 reat E mn m ee e E Cala vulization e oad Iree iui Moog or Aol Apg Gee octane bo eer ie vith Pe cere tiroe Gea al Lox cma Chapter 1 Making Excel More Efficient A LE i Initial Mortgage Data 2 Interest Rete Annuli Anvortization ieai l larm Years Principal Payment Type e Amortization Sche 0 7 Te rm Anmworiizadon Yaar Perlod Hate WPER Payment Principal Interest Teo Laan Prncgal Anayss Mortgage Amortization Schedule Mortgage Paydori Cumulative Cunvulative Remaining Principal interest Primcipal 4 More Options It is often a good idea to also configure an error message that displays when the user tries to enter data outside of the range you have specified Follow steps 1 to 3 to open the Data Validation dialog box and then click the Error Alert tab Make sure the Show Error Alert After Invalid Data is Entered check box is selected and then specify the Style Title and Error Message Click the Input Message tab Make sure the Show Input Message When Cell Is Selected check box is activated WI Type a
18. R tg Cushumize the Quick Access Toolbar Shaokt command Tome Cupit Dick Agak Tomar tice Menu rc For all d ume nis defect ia F Scare ag E mail as POF atiachnent oe ey Eral as XFS Srachenent _ d Freep Dramen Redo ae T excel M I Worebock d He fg Exel Bina Wrarkeaok ia Ewe Maco Eneblsd Warkoogk Ej oee Workooak wK E a And adini ia viha lie bareli G inpet orent Ej Iemet Fax Manage Credermisk F Mark as Fral 3 Open B Open fect Mle ily OpenQacure rt torsade Uj Opler EF Prepare A Pree and Frit Pent Lh Pent prense OO Pepe amp Publsh a 3 D3 Dehish ar DT ar YET r Cusen Sartana Detant Ihya Quick pen Toolsar elo khs Albian TeporbExport Page Layout Forua Dts Rew ay we Dereli a i a a p 2 H a i mu a Fi z Ly 1 i oa Cereal qu c sme a s i 1 ia it wm Font E Alenen E abr 4PM Shetti Sheed Shosta Fa FE cansi More Options If the command you want to add appears on the Ribbon you can add a button for the command directly from the Ribbon Click the Ribbon tab that contains the command right click the command and then click Add to Quick Access Toolbar Excel inserts a button for the command on Click the command you want to add Click Add Excel adds the command To remove a command you can click it and then click Remove Click OK Excel adds a button for the command t
19. W Page Layout E Formulas m Data W Review E View Developer W Add Ins Background Removal T Se ee eH Customizations Restore Defaults Y Import Export ox canw More Options You can get more space on the Ribbon and reduce clutter by removing any tabs you do not use For example if you do not use the Excel reviewing tools then you might prefer to hide the Review tab to reduce the number of tabs you see on screen In the list of tabs that appears below the Customize the Ribbon drop down list deselect the check box beside any tab you want to hide Chapter 1 Making Excel More Efficient Add a New Group Click the tab you want to customize Click New Group Excel adds the group Click Rename The Rename dialog box appears Type a name for the group Click OK Excel adds the new group to the tab Try This You can change the order in which the tabs appear in the Ribbon For example if you use the tools in the Data tab more often than those in the Home tab then you can move the Data tab to be the first tab in the Ribbon Use the up and down arrow buttons that appear to the right of the tab list to modify the order You can also use these buttons to modify the order of the groups within any tab Customize the Ribbon continued Although you will mostly prefer to add one or more custom groups to the
20. anced tab Trut Center Infanmation about Book Bookl Microsoft Excel Fugelopcut Famurs Cya Ree VIEW Permissions EN Anyone can cpen copy and changa any part of tha workbook Pra Warkhngk Pricerties Sine Tilla z Tags Proper fer Sharing care Der Bofure slimy this Tig be weve Halil conker meses Lhark he paneer Pabo Jules hna latt finish be arad Todas Si Ah Variend AJ There are ne previous versione of ein file Fialahed Saocle Ysu F J a A a 2 Opten fe Beales Paul a Ey User Interface options ea aos piri Too bar on selechon F a lier Frawley Oefauk teccilig siyi bfe abue des onplioris in SoOcenhips When cresting new workbooks Lha thn tart Esdr Fort Fort ge ti Detauit gew for mea steels harraal Vigwe z ImAus thit mam herie z Beinn youi copy H Mkrasoti Oike Lhar pama aul Excel Options i Let P i 3 Advanced cations for working wh Excel tornula Pranling G ng options zae T After pressing Enter pove sHedion Dir clion Dows bol angus re I T automatically rest a decimal point Customize Ribbor T trable fel handle snd cell drag and drop w Slat before cvervriting cele Quick Access Toolba zl F Allow celiting dicti in coils 4ddtra lt Estered cists range Formats and teens T Enable autometic percent entry Trust Ca Enable gutaOcenplete for cell values 7 Fean an ih lao at Alert the user vihen a potentolly bme cormsuming op
21. anes Chapter 1 Making Excel More Efficient Te p pp coe Workbook Excel Macro Enabled Workbook Organize Me Excel 97 2003 Workbook AMIL Data fe Microsoft Office Single File Web Page Web Page Excel Template Ur Favorites Excel Macro Enabled Template Excel 97 2005 Ternplete Boek ed Tab dinih eb Downloads Unicode Test i Recent Places ML Spreadsheet 2003 c Microsoft Excel 5 095 Workbook Cy Comma delimited Libraries Formatted Text Space delimited F ioe Text Macintosh Decuments Text M5 D05 a husic CSV Macintesh Be Pictures An es DIF Dats Interchange Format Videos SYLKE Symbolic Link Excel 4dd In i Excel 97 2003 Add In a Homegroup a XFS Document File name fenDocument Soreadsheet Authors Paul WePedries Tags Add a tag Save Thurnbna l Hide Folders fe Save As E L T e Libraries Documents E NE Organize Mew fobder ims Microsoft Office g Documents library Arrange by Folder Includes 2 locations Tr Favorites Mame Date modified Type m Desktop Lg Downloads LL Outleck Files 5 5 2009 Wold PM File folded Sel Recent Places m Snaght Catalog BST 2009 1219 PMI File folded So Libraries Decurnents a klusic b Pictures Ej Videos a Horegroup IR File name Loans Save as type Excel Binary Workbook Authors Paul MePedries Tags Add a tag Save Thurnbevaal Hide Folders Concel Did You Know The Excel Binary Workbook
22. bbon and then click Customize the Ribbon to open the Excel Options dialog box with the Customize Ribbon tab displayed Click the Import Export drop down arrow and then click Import Customization File In the File Open dialog box locate and then click the customization file and then click Open When Excel asks you to confirm that all of your existing customizations will be replaced click Yes and then click OK Excel applies the Ribbon and Quick Access Toolbar customizations Configure Excel to Use the Mouse Wheel for Zooming If you frequently zoom in or out of a worksheet you can save time by configuring Excel to enable you to zoom using the wheel on your mouse Zooming a worksheet is a useful technique For example you might want to zoom out of a large worksheet to get a sense of the overall structure of the worksheet data Similarly zooming in on a section of a worksheet enables you to focus on just that section You normally zoom either by using the controls in the View tab s Zoom group or by using the Zoom slider that appears in the bottom right corner of the Excel window These techniques are fine if you only zoom occasionally However if you use the zoom feature frequently it is a good idea to configure Excel to zoom using the mouse wheel Click the File tab button Click Options ia ire A dye 4 hice Brin Save amp Sond Help EJ a The Excel Options dialog box appears Click the Adv
23. crease Future Value 6 000 00 5 000 00 11 000 00 111 090 00 an Are An A A a Banaran FA Arr an Arrange the OCEA a Ak Aone insert Page Layou Pow aval Daty Plewi ze Kiri Dewtloner a jo PO Workbook s Windows A LJ Page Eresk Predew f F Fa ji E Hewi Eq Spik Tg Custom Views pea C anpi o Excel creates a second His E Full Sereen A Giidines F Hesaings lly Sl Tria FE Freese Panes C2 Unhide RA Pa aan pami y F window for the workbook TARI O C 4 T and appends 2 to the name of the new 2 Nominal Rate APR 6 00 Deposit Frequency aruz Annual win dow Term Years 14 Deposits Per Year 1 Semi Annually initial Deposit 100 000 Compounding Frequency severus e Quarterly 111 Periodic Deposit 55 000 Compounds Per Year Monthly Note Excel also appends 1 to the Deposit Type O Effective Rate Per Period 6 09 Weekly ce an ae name of the original window Total Periods 10 Daily Click Arrange All Cumulative Interest 56 090 00 5 000 00 511 090 00 111 090 00 JP ok Bikes Ts E ly Rs ons ST eT IE 1 Arrange Windows EA Split im fy J m Hid tal ca tare Saaitch Marre Chapter 1 Making Excel More Efficient The Arrange Windows dialog box appears Click to select the Horizontal option If your worksheet has just a few columns you can click to select the Vertical option instead Click to select th
24. d Remove It If you have a fast computer with a lot of memory then Excel should be able to handle almost all real world calculations relatively quickly so you do not need Excel to warn you In that case deactivate the warning by following steps 1 to 3 and then clicking to select the Alert the User When a Potentially Time Consuming Operation Occurs check box cy Pin Excel to the Windows 7 Taskbar You can quickly and easily launch Excel by As with previous versions of Windows the pinning the Excel icon to the Windows 7 Windows 7 taskbar displays an icon for each taskbar running program However one of the new If you use Excel every day Windows offers features with the revamped Windows 7 taskbar some methods for starting the program that is the capability of storing program icons are easier than going through the menus For much like the Quick Launch Toolbar in example you can pin the Excel icon to the previous versions of Windows Once you have Start menu so that the program is just two Excel pinned to the taskbar you can then mouse clicks away You do this by right clicking launch the program by clicking the icon the Excel icon and then clicking Pin to Start You can pin Excel to the taskbar either by Menu However if you use Excel frequently running the Pin to Taskbar command or by you might prefer to have it just a single mouse clicking and dragging the program icon to the click away You can achieve this b
25. default Excel tabs this is not always convenient because it reduces the amount of space available to the other groups in the tab This can cause the buttons to appear cluttered making it harder to find the button you need In such cases a better customization method is to create your own tabs and populate them with custom groups and commands You can also export your Ribbon customizations to a file so that other people can import the same customizations For more information see the section Export Ribbon Customizations to a File Add a New Tab In the Customize Ribbon tab of the Excel Options dialog box click New Tab Excel adds the tab Excel adds a new group within the tab Click the new tab Click Rename The Rename dialog box appears Type the name you want to use Click OK Repeat steps 3 to 5 to rename the new group Ls Customize the Ribbon Main Tabs Main Tabs 7 Home H Clipboard H Font E Alignment H Number W Styles H Cells H Editing My Group Custom BE New Tab Custom New Group Custom W Insert Page Layout 7 Formulas FF Data of Review View 7 Developer 7 Add Ins W Background Removal HAR Ree eS lew T New Group Rename Customizations Restore Defaults Import Export Chapter 1 Making Excel More Efficient i
26. e Windows of Active C Unhide Workspace Windows Workbook option a amp Click OK E Excel arranges the 1 Annually workbook s windows emi Annual i gt See When you are done with Monthly the second window click a its Close button to return 3 to using just the original eT P workbook window E F z 1 Annually Semi Annually 2 Quarterly Monthly Weekly Daily E ae y z Try This Did You Know If you are using the new window to monitor If you are using the new window to either the first few rows or columns in the monitor a particular cell value in workbook then you might find it easier to another part of the workbook Excel split the worksheet into panes instead of offers another method for doing this creating a new window In the View tab click the Watch Window You use this the Split button and then click and drag the window to monitor the current value of pane borders to define the area you want to one or more cells To learn how to use monitor The areas inside each pane scroll this window see Chapter 4 independently so you can keep the data in the other area in view at all times Allow Only Certain Values in a Cell You can make Excel data entry more efficient To ensure that the proper values are entered by setting up data entry cells to accept only you can set up a cell with data validation certain values criteria that specify the allowed value or values When
27. e Control tab of the Format Control dialog box use the Minimum Value and Maximum Value spin boxes to specify the range Use the Incremental Change spin box to specify how much the control value changes when the user clicks a scroll or spin arrow Click OK Important When you click an item in a list control the item s position in the list appears in the linked worksheet cell To get the actual item you need to add the following formula to a cell INDEX input_range cell_link Replace input_range with the address of the range that holds the list values and replace cell_link with the address of the control s linked cell Check for Accessibility Problems If you have a workbook that will be used by people with disabilities you should check that workbook for accessibility problems that could make it harder for the disabled to read and navigate the document Spreadsheets that seem ordinary to most people can pose special challenges to people with disabilities For example a person with a visual impairment might have trouble seeing images charts form controls and other non text elements Similarly a person with physical disabilities might have trouble navigating a worksheet Fortunately such problems are often easily fixed For example adding a text description called alt text to a chart or other non text element helps the visually impaired understand what the element does avoiding non standard worksheet structu
28. eretion occurs When this nymber of cele in thowsands ts affected 33 554 a Live sen separators Decimal separator Thousands sepecston Tuno myene Logical D yius C t copy and paste a Shows Paste Opbers barbers Stow Insert Options buitons Cut copy and port Inserted gbjects with thelr parent cells Image Size and Quality fix Sook Excel Options Danera B 3 Advanced cations for working wh Excel tornula Pronting Cokking options ase T After prendra Enter pove selection Dir clion Dawn orl anguage a 79 I T Automatically rest a decimal point Customize Ribbon T irable ti handle and cell drag and drop of lert before overwriting cele Qubek Access Toolba ixl amp z 7 Allys webbing dreth in coils Add ira a Estered ists range Formats and teers F Er Dei par EE Enable aubomatic percent entry Enable gutoOcenplete for cell values EJ Glert the user when a potently pme corsuning opecetion occurs When this nyamber of cele ln thowands be affected 25 554 F Use splen separates Decimal separate Thrus peor Tunar myene Logical C haul Cul copy and paste a Shove Paste Opbors barbers Stow Insert Options buttons Cub copy and sort Inserted gbjects with thelr parent cells Try It When you activate the Zoom on Roll with IntelliMouse check box rolling the mouse wheel forward causes Excel to zoom in on the worksheet by 15 percent with each scroll of the wheel rol
29. fo ae TR ee Information about Loans O Click Check for Issues ae eee er eee s sg sge iL Click Check Accessibility ali EY Peeminsions os aaa open copy ard change any pert of this fanatic Protect io WIEKE Recent Workbook DE pr o Taps dda Neve Categones id a Prepare for Sharing Print 4 Before sharing this file be aware that it contains Relaked Datis Corniinmaniks Lait Modified LS 258 257 Save amp Send Oecumert properties pirte path authors name Created JAE 11255 ang releteci cates Lait Privitod TADE 9 00 HEP E en EA dor haiden properes 1 GJ Op ipanama infeereatian alte pantek Melated Prople meee athar T Spanne Feul Mer edries aaa desma Last Moditied fy Feul Medies Check foo features nol wppoded r earlier wersion of Eecel TEPNY adaout saving Related Documents p Open File lecaliar re 2 Properties E F Loan Payment lee ae a E F I Accessibility Checker Period Principal Interest 1 Chapter 1 Making Excel More Efficient reper Besulis Excel displays the Accessibility Checker gt imterest Rate Annual 600 i 049 99 50 00 i Vo Eror 3 Periods Years 5 2 ao 549 28 4 5 eessng att ten task pane 4 Principal 10 000 3 Sire 4856 5 Thet i Charli Pi 7 5 Menthly Payment 5193 33 4 145 49 47 84 5 Chak 1 Kit Cli k h EJ 5 isa4e22 47 44 1 Drop Down 2 Dynamic Amortization Schedule ICK an item
30. ginal Editing _ A worksheet also appear in the other worksheets in the group More Options If you have a workbook with a large number of worksheets and you want to include most or all of those sheets in your group do not click each worksheet tab individually To group every sheet right click any tab and then click Select All Sheets alternatively click the first tab you want to include in the group hold down Shift and then click the last tab you want to include Remove It To exclude a worksheet from the group hold down Ctrl and click the worksheet s tab To collapse the entire group either click any tab that is not part of the group or right click a grouped tab and then click Ungroup Sheets Ey Quickly Display the Office Clipboard You can make the Office Clipboard easier to use and more efficient by configuring Office to display the Clipboard quickly A clipboard is a memory location that is used to store data temporarily Windows comes with a clipboard that stores data that you either cut or copy and you can then paste the data to a document The Windows Clipboard can only store one item at a time which is not always convenient or useful However Office 2010 comes with its own memory storage area called the Office Clipboard that can store up to 24 cut or copied items You can paste the most recently cut or copied item using the Paste command but to paste an older item yo
31. ha Pumnialun your copy ul Moe Olle disei name Poul Chapter 1 Making Excel More Efficient E O G Make sure that the After si S Advanced options far working wih Excel Pressing Enter Move iun a Selection check box is e T Atter pressing Enter pave selection z selected Dred Drem S 5 1 Automatica Dren fedima polnt Click the Direction drop down arrow and select the direction that you want Excel to move the selection after you press Enter Mares ees F enable nme 9 bell grag and crop T Alwi before evening cols E sow aditting directy in cells E Extend data range formats and formulas F Enable automatic parent entry Custounize Abbe Quik Access Toolbar E Enable AutoComolebs tor cell values E Zoom on roll wEh IntellMours F Alert the user when a potentially time corsuming operation occurs When the number of cells pn thoverand is attectess MISH F Use sstem separators romal pecmor Thousands riparitan Cunhor meement Logical i Visual Cert copy and poe F Show Peete GQotions butter E Show Insert Ophons banter bf Cut cope and sort Inserted polects with thelr parent cells Image Se and Quality a Gopkd Click OK a S iai Excel now moves the aia cutting options selection in the direction sare C geee 7 you specified when you sstamstaly Inserta deal point pre
32. han having the user memorize a cell s possible inputs you can provide a list of the allowable values To use worksheet form controls you must first customize the Ribbon to display the Developer tab as described in the first Tip Add a Control to a imi Worksheet ai Page Layout Beet Micresedl Eecal Formule Daa Farii ous D oo Fra Sp Properties i i SRA es a H a ap Properti ER meor al Click the Developer tab isisa kem i oe ST EPEa ade Ins CCE ere Couie ae ae lec Basle ay Macro Secutily uid Ins f 4 Run Dialog Panel Cade amm Contro pat M Hadiiy Click Insert a i Q oe 4 E Click the control you H a want to add z ao Ag 2 In this case you need to click a control from the Form Controls section of g the Insert Controls gallery 4 13 14 15 16 i i Ha FA Shoot Shoots Shoots Fal 4 Ready E3 Click and drag on the worksheet at the spot where you want the control to appear As you drag Excel 8 displays the border of 2 the control ra When the control is the T size and shape you want i release the mouse Aab H Sheett Shaata Sheets Pa Drag In dagunent ia creste a checkbox conbrol Chapter 1 Making Excel More Efficient Excel adds the control to the worksheet If the control comes with a text label right click the control Click Edit Text Note You can also double click the text
33. he state or values that you specify List boxes and combo value of the control the resulting change is boxes get their items from a worksheet range reflected in the linked cell and they return the position of the selected The value you see in the linked worksheet cell item in the list where the first item in the list depends on the type of control A check box returns 1 the second item returns 2 and so inserts the value TRUE when it is checked on To get the actual list value you must use and FALSE when it is unchecked Option the INDEX worksheet function as described buttons return a number based on the selected 1n the second Tip Link a Control to a Worksheet Cell Right click the control Click Format Control The Format Control dialog box appears with the Control tab displayed Click inside the Cell Link box Click the cell you want to use to store the control s value Excel inserts the cell address in the Cell Link box Click OK When the user changes the value of the control the new value appears in the linked cell Mie H Shosti lt Sheat Sheets Ee CR Ki ad 2 Rest Miciect Excel ee Daweloper ar Fra l Hame Insert Fage Leyout Formulas Dats Review Weve So Properties ee Cats Fomet Conano coierendumes sane Peotecton Prozertes alt Tex
34. ling the mouse wheel backward causes Excel to zoom out of the worksheet by 15 percent with each scroll Chapter 1 Making Excel More Efficient T Click to select the Zoom on Roll with IntelliMouse option Note Although the option name specifies the Microsoft IntelliMouse this option works with any mouse that comes with a standard scroll wheel E es Click OK You can now zoom in and out of your Excel spreadsheets by turning the mouse wheel Image Size and Quality ix 5pokl fe A Q Or Did You Know When the Zoom on Roll with IntelliMouse check box is deactivated rolling the mouse wheel causes Excel to scroll the worksheet roll the wheel back to scroll down and roll the wheel forward to scroll up This is a useful technique and you can still use it even when the Zoom on Roll with IntelliMouse check box is activated In that case hold down the Ctrl key and roll the mouse wheel to scroll the worksheet Move in a Different Direction When You Press Enter In certain cases you can make your Excel from left to right or from right to left or in a data entry chores more efficient by changing the column from top to bottom Although you can direction that Excel moves the selection when use the arrow keys to force the selection to you press Enter after you finish editing a cell move in the direction you want the Enter key Generally you enter the data vertically in a is larger than the arro
35. n item in any Office application the icon displays a notification that says X of 24 Clipboard where X is the number of items on the clipboard If you find these notifications distracting and not very useful you can turn them off Click the Home tab and then click the Clipboard group s dialog box launcher icon to open the Office Clipboard Click the Options button and then click to deactivate the Show Status Near Taskbar When Copying command To disable the actual icon click to deactivate the Show Office Clipboard Icon on Taskbar command Use Dialog Box Controls to Input Data You can make worksheet data entry easier and more accurate by using dialog box controls such as check boxes option buttons lists and spin boxes If you are building a worksheet for data entry your main concerns should be speed and accuracy That is you want users to be able to input data as quickly as possible while still making the entered data as accurate as possible The easiest way to achieve both goals in Excel is to add dialog box controls also called form controls to your worksheet These are controls such as check boxes and lists that you are familiar with from dialog boxes The benefit to using form controls is that they reduce the amount of typing required by the user entering data For example rather than having the person type Yes or No in a cell they can activate or deactivate a check box instead Similarly rather t
36. o the Quick Access Toolbar the Quick Access Toolbar EJ Customize the Ribbon You can improve your Excel productivity by to an existing tab and then adding one or customizing the Ribbon with extra commands more commands to the new group that you use frequently Alternatively you can add a new tab to the Keep in mind that you cannot modify any of Ribbon add your own groups to that tab and the default tabs and groups in Excel other then add your commands than hiding tabs you do not use Instead you customize the Ribbon by adding a new group Display the Customize AA eT AT oo i Ribbon Tab a i wi ae a oo Right click any part of the Fs IT green Brees BOIS Ribbon Le nem d oo o o mm x Click Customize the Ribbon The Excel Options dialog box appears fy Customs the Ribbon Excel automatically Ca E tantas z displays the Customize Jp emnon Jj wae Ribbon tab Ea crese ee Tona s eee il Haer Use these lists to choose on ee cate the commands you want ne Es to add nea nee aun Bemus These lists show the Gan f Lesena AF pesopn m existing tabs and groups aes aii eo To display a tab s groups you can click the tab s a i ia plus sign a Set Descending Spelling Customize the Ribbon Main Tabs Main Tabs DE A v Home W Clipboard H Font E Alignment E Number H Styles H Cells H Editing New Group Custom lt 9 El Insert
37. oint Many Excel data entry tasks require you to type a long list of values that use the same number of decimal places The most common example is a list of currency amounts which always have two decimal places When you are entering such values you type the digits to the left of the decimal point the decimal point itself and then the digits to the right of the decimal point In a long list of values the extra step required to type the decimal point is a repetitive action that just slows you down To speed up this kind of data entry you can configure Excel to add the decimal point for you automatically For example if you tell Excel to automatically add two decimal places then when you type a number such as 123456 Excel adds the value to the cell as 1234 56 Click the File tab Click Options Ecokl Microsoft Emel Vy Page zyrt Fon driz zais iea i Information about Booki Peamlsshons iy Arone Tan Ben copy ard change smy par ahin ee nk ne Protezi i Workbook Properties 7 zas THe l Prepare for Sharing eee I 2 Saher chaning hie Hie bene teak contains a Check tar a Eardere Esis weal Mediti pa di Tojap Gola Abt 2 med Vers lone HI Thos one no pimus wasae of this file Aidala Pespk Eye Auer Poul Wers bonne last ode Ep iam ll Po opertien Esil Otros The Excel Options dialog box appears Click the Advanced tab prt Gereral apligns for ew king shih Cercel
38. omatically insert the decimal point Did You Know Try This Even in Fixed Decimal mode Excel still If you choose a number other than 2 in the drops trailing zeroes from your cell Places spin box and you always want Excel entries For example if you choose 2 in to display that number of decimal places the Places spin box and you then enter you must format the cells with a custom 12340 in a cell Excel displays the entry numeric format In the Home tab click the as 123 4 If you always want to see two dialog box launcher in the Number group In decimal places that is 123 40 the Category list click Custom and then in then you must format the cells using a the Type text box type 0 a decimal point two decimal numeric format such as and then a O for each decimal place that you Number or Currency want displayed For example the format 0 000 always displays three decimal places EJ Configure When Excel Warns You About Long Operations To avoid wasting time waiting for a long workbook recalculation to finish you can configure Excel to warn you when an operation might take an excessively long time In a typical worksheet with only a few formulas the number of operations required to recalculate the worksheet might run into the dozens or hundreds which Excel can handle instantly A mid size worksheet might require a few thousand or even a few tens of thousands of operations to recalculate bu
39. res Accessories Music Games Microsoft Access 2010 Microsoft Excel 2010 Computer Control Panel Devices and Printers Default Programs Help and Support di Microsoft Office 2010 Back Change It As you drop program icons onto the taskbar Windows 7 displays the icons from left to right in the order you added them If you prefer the Excel icon to be in a different place on the taskbar click and drag the icon to the left or right and then drop it in the new position Chapter 1 Making Excel More Efficient Pin a Program Using Your Mouse Click Start Note If you see the Excel icon on the main Start menu skip to step 4 Click All Programs Note After you click All Programs the name changes to Back Click Microsoft Office Click and drag the Microsoft Excel 2010 icon to any empty section of the taskbar When you see the Pin to Taskbar banner drop the icon Windows 7 adds the Excel icon to the taskbar Remove It If you decide you no longer require Excel to be pinned to the taskbar you should remove it to reduce taskbar clutter and provide more space for other taskbar icons To remove the pinned Excel icon right click the icon and then click Unpin this Program from Taskbar EJ Make a Workbook Faster by Saving it as Binary If you have a large or complex Excel workbook you can make it open and save faster by converting it to the Excel binary file format
40. res such as merged cells helps the physically disabled navigate a worksheet You can use the Accessibility Checker task pane to look for these and other accessibility problems and learn how to fix them Open the workbook you want to check Loans Microsoft Eare 0 BE el File Hon Insert Page Layout Frome Data Finic es Developer 2go r E P B Calin i Cunency Fy Conditional Formatting geimi x Ar g ee Har Br UY Sa H 8 Porat as Tele F Delete Be all aa g Ce dA PE g AA Imer Brommt a gt Pies Seeds Clipboard ia Font Alignment Humber Hyles Calls Editing BS z PMIT B2 f 12 B3 12 B bed A D E F G H l J K 1 Loan Payment Analysis E 2 interest Rate Annual 6 00 1 5143 53 50 00 5193 33 3 Parlada Years 5 2 S10 519 28 193 33 4 Principal 10 000 3 8444 76 M856 8195 55 le Monthly Payment 5193 33 4 5145 29 51784 195 35 5 Total Loan Casts 51 599 55 5 10514622 47 11 193 33 T 6 8446 95 46 98 195 55 z 7 8447 68 515 65 195 35 k amp Siteazp 44 91 193 33 10 3 0344016 44 17 195 45 11 10 5149 91 543 42 8193 53 E 12 60 192 37 0 36 193 33 13 14 15 ak A Baloon Loan Interest Costs Ponopal and Interest Cunmalatae 4 L F I Ready Pa Wy o y 2 Click the File tab i al f gt 2 Loans hdicrosodt Excel la File Fags Layout Fomeslas Data Febe Yen oho FE Click In
41. s Customize the Aban hoot command tom Commance Med in the abbor Fepula Conmands l Commands Wage ce Olea Menu al Tabe Male Tabs Tool Tabs Cuan labi and Giuss Sane w Ar Posture kL i sulori bar 2 AutoFormat i pnk Kotlon Eordar 0 Bright Se Enpi ex Buleh and Murkbennq Bution Check tne Upstate hien Tih piia mierenia Bo Ciar Dalomnusans Rerin Deaubh Y q sesi i Irearttaat Add a Command Click the Choose Commands From drop down arrow Click the command category you want to use Click the command you want to add Click the custom group you want to use Click Add Excel adds the command e e0 O 0 To remove a custom command click it and then click Remove Q ETE El d 7 Donel Mirncsr t Boze jet a Page Layout Pormrulas Dats Ferje T Click OK C Excel adds the new tab or group as well as the new command to the Ribbon Dawe oper Wo H Sheetl Sheta Sheet fo Ready EJ Try This You can also customize the tabs that appear only when you select an Excel object Excel calls these tool tabs and you can add custom groups and commands to any tool tab Right click any part of the Ribbon and then click Customize the Ribbon to display the Excel Options dialog box with the Customize Ribbon tab displayed Click the Customize the Ribbon list and then click Tool Tabs Click the tab you want to add
42. s Toolbar Add Ins F Extend cats range formats and feemulas F Enable aulomatic perce entry 7 Enable AshoComplate for cell values Trust Center Zoom on poll vaith IntelliMouse F Alert the user whan a potentially time consuming operation occurs When this nuenber of calls fin ioukandsj is alfected 10 000 T Uie athens daparators mal Seaerabor Decal seperate Did You Know Although you re unlikely to ever come across such an operation the maximum value that you can specify in the When This Number of Cells In Thousands is Affected spin box is 999 999 999 Note too that although you can enter a value as small as 1 in the spin box low values are not recommend because they generate excessive warnings Unless you have a very slow computer do not go under ten million operations 10 000 in the spin box m Make sure that the Alert the User When a Potentially Time Consuming Operation Occurs check box is selected Use the When This Number of Cells In Thousands is Affected spin box to specify the threshold at which Excel displays the long operation warning Note The number in the spin box is shown in thousands So for example if you enter 1 000 into the spin box then the threshold is one million cells Click OK Excel now warns you about time consuming operations when the number of cells affected will be equal to or greater than the number you specifie
43. soft Looe E a Ga pp Ef ee Cerul Ey Ccndricnal Formating 7 Insert Er H p PAN m aki L p 1 o w J A Fora an Tabie oae gf A ja x Some FAnd Jy Cel Siphu aJF na Ake Selec Shea Lelki Edimy Shrew elo the Ribbon Ready Ed The Excel Options dialog box appears Erase Sonmdinids ian Popular Commards oh h eal i f ia Coclumize the Quick 2i Tubar Cuibanize Quick Acces Toelbar Pee al cecuments deieh Popular Lommards Command kst inthe Ribbon lal Suse All Corran H Undo Excel automatically Toolbar tab Macros m ei Click the Choose drop down arrow omatab Click the command anak Sora thir Tozer Fens Tah Chert Tools Degn Tab Smale PaoiTabie Toos Options Tab Poullabis Tow wign Tab as F E amam onne x displays the Quick Access Commands From pane inaut category you want to use soc as i Chart Tools Lapoul Tab Picture Tons Format Tab Header fi Poincar Teale Denign Tas Show Quick Acces Toolbar below the ebbon q0 Ssaki Wicrasit Dore g i F B f W Be J dr F E E m a More Options You can increase the space available to the Quick Access Toolbar by moving it below the Ribbon This gives the toolbar the full width of the Excel window so you can add many more buttons Click the Customize Quick Access Toolbar button and then click Show Below the Ribbon Chapter 1 Making Excel More Efficient di S
44. ss Enter to confirm a cell entry if Enable fill hardie ard cell deeg arid crop Fo Alei belare evenviling cols E Slow adtting directy in cells E Extend data renge format and formulas 7 Enable automatic percent entry Custoenize Ribbon Quik Access Tookar E Enable AutoComplebe tor cell values E Zoom on roll whh IntelliMouse vf Abert the user when a potentially time comsuming operation occurs When the ymber of cells pn thousands is stfectest Fd F Use sstem separators Drona spamon Thousands paraban Cursor meenen Logical T Yisual Oat copy and pre F Show Paie Qotions butter E Show Insert Ophors barter E Con cope and sort Inserted pbJects with thelr parent ces Image Sue and quality Wf Gopkd Did You Know If you have only a few data items to enter you can force Excel to move the selection in the direction of the next cell entry by using the arrow keys For example suppose you are entering data in a row from left to right When you finish editing a cell press the right arrow key which moves the selection to the next cell on the right Similarly you can press the left arrow key to move the selection to the left or you can press the up arrow key to move the selection up 15 Automatically Insert a Decimal Point You can make certain Excel data entry tasks more efficient by configuring Excel to automatically insert a decimal p
45. t coria Wes Checked A Boe Calin aig lt 9 _ a ere 16 Ha b H Sheet Sheet Shact Fo Paint J Chapter 1 Making Excel More Efficient Fammi Cbjert e jel Populate a List Control Sine Protachem Thecmr Lera AL Twat with Values Feino i A a ee Add the list items in a Bi o m a vertical or horizontal 3 chang j range on the worksheet 4 Chartreuse verte b cu ae i i Rr oa ba eae Right click the list box or eer Sapos combo box control 3 laughing Lumberjack Lager Order 9 Quthack Lager Click Format Control The Format Object dialog box appears with the Control tab displayed i0 Rh nbr u Klosterbler ii Sasquatch Ale 1 Steeleye Stout 13 14 15 C O O heat Shoat Shaats tI Click inside the Input Range box Select the range that includes the list values Chal Chang Chartreuse werte Cote de Blaye Ipoh Coffee Lakkalikoiri Laughing Lumberjack Lager Excel inserts the range Cuthack Lager Rh nhriu Elesterbler address In the Input Sasquatch Als Steeleye Stout Range box Click OK The values from the worksheet range appear as items in the list control More Options If you add a scroll bar or spin box control to the worksheet you must configure the control to return a value from a specified range Right click the control and then click Format Control In th
46. t even such larger sheets typically complete recalculation in a second or two In a massive worksheet that contains many linked formulas or one or more large data tables the number of operations required to recalculate the sheet can run into the millions If that number exceeds 33 554 000 operations Excel warns you that the recalculation might take some time You can configure that threshold to a lower or higher number If a large data table is causing slow workbook recalculations you can configure Excel to bypass data tables when it recalculates workbooks For more information see Chapter 4 Click the File tab Jas Ori Booki Merosot Excel a Fage Layout Parmala Dra Reiew View gt o a amp Click Options l l Information about Bookl H baza ia E Open Permissions a Anyone can oon copy wel Change any pari af this G pin a workhnonk Workieank M perliis MTE Recent The addatti E Prepare for Sharing on aii Mew r Batore sharre this Mle be aevare thet it contaire ANERER Frint heck for a Pelahed Oster biui Last biogHled Save Be Send Cresta Tadap G00 ARM ant Pointed Help Versions E r Ei ten AI There sre ro previas veriore of this file Related Feaple Sunhor Warid The Excel Options dialog Bod Opts box appears Click the Advanced tab ace Customize Aibbon Quick Access Toolbar When creating mew warbooks Use this tape Body Fort Foni sere rh
47. the text and other Page Layout Formulas Data Review 2o da To Er Gememl Fycorditianal Formatting gr Incrt E AF j data you want to display r A a E er J Format at Table FF Doite las an bata on the grouped pricier z ELA CHI jar H ber r ge mena worksheets EEE e Apply the formatting that a Budget Worksheets 1 Income 3 Royalties 4j Advances 5 Fees E interest 5 Expenses 9 Subscriptions 20 Salaries 11 Supplies 12 Services 14 Telephone 15 TOTAL HAEN Sheet cheall cheeks F Sheet Fe irl Reach Ld Bock Microsoft Excel Fite H eet Page Layout Friis Data Pointe x Calibri u Sr Geel FaconditionalFormating gemat E A B 7 U Asa EI oe J Format at Table 7 F Goleta H7 TF uda A FE ws F com styles clipboard i Font a Alignment Mumbar Styles Ala z ie a c D E F G H l 1 1 Budget Worksheets a incame 2 Royalties aj Advances 5 Fees i Interest T TOTAL E Expenses lt _9 4 Subscriptions 10 Salaries EER Supplies l Services 1a Telephone 15 TOTAL ce FW Sheet Sheek Sheet Sheed ar Jal I Ready 20o Pg A rAd hiii Sot amp Find amp BH Format 7 cf Fiker Select you want to use on the grouped worksheets TE Click the tab of a worksheet in the group The data and formatting you added to the ori
48. ther sheet in the group similarly any formatting applied to one sheet is also applied to the entire group Click the tab of the first worksheet you want to include in the group Press and hold Ctrl Insert Caliber 7 ii MarH Sheet Ready 7 Sheet Click the tab of the next worksheet you want to include in the group Insert Calibn ri Bi e Mate te ah Excel displays Group in the title bar to remind you that your worksheets are currently grouped Note If you select a tab accidentally click the tab again to remove the worksheet from the group Repeat step 3 for each worksheet you want to include in the group Release the Ctrl key MarH Sheet1 Ready 75 Sheet Ey Sheet Page Layout Bookl Microsoft Excel lal Fage Layout Formulas Data Review YIEE Ceveloper a o g a o General gt F Conditional Formetting g insert E Ay a SS a a f Ai l i j figs Format as Table FF Delete a Sort fe Find amp Ee 2 4 2 E cell Styks fEgrormat ca fitere Select J Alignment Humber A Styles Tells Editing w 5 eI D E F G H l J KS F Sheet Pa IE i i Ema s 7 i Bookl Group Mikrosoft Excel Dats Review view ginet E Ak FF Delete fae a bie i t gt mi Format g E Cell Styks 5 Editing Styles Tells Chapter 1 Making Excel More Efficient Bookl Microsoft Excel Add
49. u must display the Office Clipboard and then double click the item you want to paste Unfortunately displaying the Office Clipboard takes a few steps so if you use this tool frequently you might prefer a faster method You can configure Office to display the Office Clipboard automatically either as soon as it contains at least two items or whenever you press Ctrl C twice in succession Display the Office a ine oie Forala Daka Desig a o A A Clipboard Genaral Ei ConditionalFemattings guint E A F j Automatically be v s 7 memes iey fee Format as Table a Delete 7 l TA ae jas d r IF EF Re mon E Coll Styles fa Fomat ca gos det i D Click the Home tab Clipboard amr si Alignment Humi i i 2 s jes i s Al ih w In the Clipboard group an z z r E click the dialog box i 2 launcher icon i JA 5 z ry a g 10 E 11 12 13 14 15 W AE H Sheetd Sheet Sheets y ya l T Ready J Ho 2 is L iF The Office Clipboard task En pra x A D E F g A pane appears Cik an ilen eae 2 Click Options a Coop or auf fo colec fan Click Show Office re F Clipboard Automatically Excel now displays the Shaw fice Cigbaard Automatically D s Show Office Clipboard When Cti Pressed Twice Office Clipboard Collect Without owing rice Cipaeard automatically whenever it EE Show Otice
50. w keys and is thus faster column of cells Excel allows you to do this by to use and less prone to error Therefore you automatically moving the selection down to can configure Excel to move the selection in the next cell when you press Enter the direction you prefer when you press Enter However in some cases you might need to enter a large amount of data in a row either Click the File tab Click Options The Excel Options dialog box appears Click the Advanced tab Bookl Microsoft Emel e Page Lartit Fork Iriz zais iei D n Information about Booki nee Pamlissions E Aryone can open copy ard change smy par ahin we nk Probeci Workbook Properties EEL THe Prepare for Sharing b Pet Saher chaning chic Hie be masr teak E ariane iia Clrenk bow Suthers cane Balstad oe O ADEO O tisi Meditied cnet cre Tijan Go Ahi wat Frimied Feirer VYeorslons WI Theses ane no pimus waaa of this fle Adabed Mespk eu Panes aiher Poul Yehann r ustModPedes H iam ll P cpariar Excel Options a i a General options for working with Excel Liner Inbertace options FW Shine Mini Tanlzar on wlahinn i Save gel crake Live aiei Language chants Ustaut r Arve a ni _ The ste Showfesture desc plons in Screentip fusiumew Ribbon When pesing ney mockbondes Quick Access Toolbar Use the tock Drip ton Furl sige i Defsuitaley for neertheets Horin slew dnckeda Unie manp ah
51. x Default slew for new sheets Mormal Whew Include this mary gherb 3 Panemalme yuu copy of Mikeosoll Offre EB General options for working with Excel isre im rrlar Shire biri Toolbar ee selection T fable Line Prewtews i E srheme rit a meente shie Shaw Testure descriptions in SereenTier Pe aphan l Cancel Chapter 1 Making Excel More Efficient Excel ptons i Advanced options for working with Excel Editing options 7 Atter pressing Enter myro telaction Diedianm Down l Automatically insect a decima point Pares F Enable til handle and cell drag and drop F Slert betore overamting cells Z Aiow sting directly in cells Customize Ribbon Quick Access Toolbar 4dd Ins 7 Extend cats range formats amd feemulas 7 Enable aukomatic percer entry Lf Enable AutoComplete for cell values Zoom on poll vaith lballibeuse F Alert the user whan a potentially time consuming operation occurs 5 When this number of calls fin thoupeerds is alerted 33 554 lt F Uie dyther separators Becmal separator Excel Options i Advanced options tor working with Excel Formulas Proofing Eding options Sae F Aiter pressing Enter mowo robaction Direction Down e l Automatically insect a decimal point Plaines F Enable fll handle and cell drag and drop J Slert betore oweramting cells F Alw acting directly im cells Customize Ribbon Quick Acces
52. y pinning taskbar Excel to the Windows 7 taskbar Pin a Program Using a Command Click the Start button Note If you see the Excel icon on the main Start menu skip to step 4 eee Troubleshoot compatibility Click All Programs i Windows Home Server Ci Pin to Taskbar Note After you click All Programs ZJ Windows Live ID Pin to Start Menu the name changes to Back Windows Media Center C c PN Windows Media Center aa Click Microsoft Office Windows Mesia Player EA RT z Right click Microsoft Excel pe este qal XPS Viewer 2010 Accessories Click Pin to Taskbar J Games 3 Maintenance Ji Microsoft Office A Microsoft Access 2010 Microsoft Excel 2010 Le i Microsoft InfoPath Designer 2010 Microsoft InfoPath Filler 2010 N Microsoft OneNote 2010 Microsoft Outlook 2010 Microsoft PowerPoint 2010 Default Programs Microsoft Publisher 2010 Microsoft SharePoint Workspace 20 Help and Support Wi Microsoft Word 2010 i Microsoft Office 2010 Tools Back O vr F A Control Panel Devices and Printers Windows 7 adds the Excel icon to the taskbar Note If Excel is already running you can also right click the taskbar icon and then click Pin This Program to Taskbar Ey Windows Home Server Console Windows Live ID 2 Windows Media Center Connector Paul Windows Media Center Windows Media Player Documents EF Windows Update lt lt XPS Viewer Pictu
53. you build a spreadsheet you may find You can work with numbers dates times or that some cells can only take a particular range even text length and you can set up criteria of values For example an interest rate cell that are between two values equal to a specific should take a decimal value between 0 and 1 or Value greater than a value and so on Excel a whole number between 0 and 100 if you have also lets you tell the user what to enter by formatted the cell with the Percent number adding an input message that appears when format Similarly a cell designed to hold a the user selects the cell mortgage amortization term should probably take whole number values between 15 and 35 Click the cell you want to restrict Click the Data tab Click Data Validation The Data Validation dialog box appears Click the Settings tab In the Allow drop down list click the type of data you want to allow in the cell In the Data drop down list click the operator you want to use to define the allowable data Specify the validation criteria such as the Maximum and Minimum allowable values as shown here Note The criteria boxes you see depend on the operator you chose in step 6 Ey oh 1 EoHirg Faimh El Tel PET eo Ted be STW ps A J Ahes al Seus Cemadhre Al gt af hail Couar Duplicates 22 YAWA asabini H EREE pimal Daha Conrat Sort amp Mh Date Teac Ceca gt Initial Mortgage Data 2
Download Pdf Manuals
Related Search