Home

Wiley John Walkenbach's Favorite Excel 2007 Tips and Tricks

1. 2 California West 58 283 800 3 Jan Washington West 35 507 200 4 Jan Oregon West 39 226 700 Es West Total 13207700 6 Jan NewYork East 25 107 600 _7 Jan New Jersey East 47 391 600 is East Total 72 499 200 _ 8 Jan Total 204 1 516 900 10 Feb California West 44 558 400 11 Feb Washington West 74 411 800 Marl Sheet 23 sales reportxlsx 2 2 Jan California West 58 283800 3 Jan Washington West 35 507200 _4 Jan Oregon West 39 226700 5 West Total SUBTOTAL 9 D2 D4 SUBTOTAL 9 2 4 6 Jan New York East 25 107600 7 Jan New Jersey East 47 391600 8 East Total SUBTOTAL 9 D6 D7 SUBTOTAL 9 6 E7 3 Jan Total SUBTOTAL 9 D2 D7 SUBTOTAL 9 E2 E7 10 Feb California West 44 558400 11 Feb Washington West 74 411800 meh Sheets Ca Ready E3 Figure 9 1 Displaying two windows for a workbook lets you view the cells and their formulas Tip 10 Customizing Your Quick Access Toolbar Customizing the Quick Access Toolbar In previous versions of Excel you can fairly easily modify the user interface You can cre ate custom toolbars that contain frequently used commands and you can even remove menu items that you never use In earlier versions of Excel you can also display any num ber of toolbars and move them wherever you like For users who have moved up to Excel 2007 those days are over With the introduction of the new Ribbon based user interface in Office 2007 user cus tomization is severely
2. Hed Tip 15 Hiding User Interface Elements Hiding User Interface Elements Excel has various options that enable you to hide quite a few elements in order to cus tomize your workspace In some cases you can change the display options in more than one place For each of the elements listed in this section I show you the easiest Hide method possible From the Ribbon Change the following settings by using controls on the Ribbon Ruler When your worksheet is in Page Layout view use View Show Hide Ruler to toggle the display of the rulers at the top and left sides of the window Turning off the rulers gives you slightly more screen real estate Gridlines When View gt Show Hide Gridlines is not checked cell gridlines are not displayed If you format ranges by using cell borders turning off the gridlines makes the borders more prominent Message Bar The message bar appears directly above the formula bar and as far as I can tell is present only if you open a workbook that triggers a security warning Turn off the message bar display by choosing View gt Show Hide Message Bar Formula Bar The formula bar located just below the Ribbon displays the contents of the selected cell To see more of your worksheet turn it off by choosing View gt Show Hide gt Formula Bar Row amp Column Headers If this setting is turned off by choosing View gt Show Hide Heading you don t see the row nu
3. Tip 25 Making a Worksheet Very Hidden Making a Worksheet Very Hidden You probably already know how to hide a worksheet Just right click the sheet tab and choose Hide Sheet from the shortcut menu And of course it s just as easy to unhide a sheet Right click any sheet tab and choose Unhide from the shortcut menu You see a list of all hidden sheets To make it more difficult for the casual user to unhide a hidden sheet make the worksheet very hidden Here s how to do it 1 First make sure that the Developer tab of the Ribbon is displayed By default this tab is not displayed To turn on the Developer tab open the Excel Options dialog box choose Office gt Excel Options click the Popular tab and select the Show Developer Tab in the Ribbon check box 2 Activate the sheet you want to hide 3 Click Developer Controls Properties to display the Properties window see Figure 25 1 Properties B Sheeti Worksheet X Alphabetic Categorized DisplayPageBreaks False DisplayRightToLeft False EnableAutoFilter False EnableCalculation True EnableFormatConditionsCalculation True False False Name Sheet1 StandardWidth Visible 0 xINoRestrictions Sheet2 8 43 2 1 xlSheetVisible 0 xiSheetHidden Figure 25 1 Use the Properties window to make a worksheet very hidden 4 In the Properties window click the Visible property to display a down arro
4. Excel doesn t provide a direct way to hide the contents of cells without hiding entire rows and columns but you can fake it in a few ways e Use a special custom number format Select the cell or cells to be hidden press Ctrl 1 and click the Number tab in the Format Cells dialog box Select Custom from the Category list and then in the Type field enter three semicolons e Make the font color the same as the background color e Add a shape to your worksheet and position it over the cell or cells to be hidden You should make the shape the same color as the cell background and probably remove the borders All these methods have problems The cell s contents are still displayed on the formula bar when the cell is selected If you don t want to see the cell contents on the formula bar after you use one of those methods you can either hide the formula bar or perform these addi tional steps 1 Select the cells 2 Press Ctrl 1 and then click the Protection tab in the Format Cells dialog box 3 Select the Hidden check box and click OK 4 Choose Review Protect Sheet 5 In the Protect Sheet dialog box add a password if desired and click OK Keep in mind that when a sheet is protected you can t change any cells unless they are not locked By default all cells are locked You change the locked status of a cell by using the Protection tab in the Format Cells dialog box Tip 18 Taking Pictures of Ranges Takin
5. New Comment button or by right clicking the cell and choosing Insert Comment from the shortcut menu Use Review gt Comments Show All Comments to toggle the display of comments The Excel Options dialog box has additional comment viewing options found in the Display section of the Advanced tab A user can choose to hide all comments and comment indicators which means that your comments will never be seen This tip describes how to use Excel s Data Validation feature to display a pop up message whenever a cell is activated It s a good way to ensure that your comment will always be seen regardless of the user s comment viewing setting Follow these steps to add a message to a cell Activate the cell that you want to display the pop up message Select Data gt Data Tools gt Data Validation to display the Data Validation dialog box In the Data Validation dialog box click the Input Message tab Optional In the Title field enter a title for your message Enter the message itself in the Input Message box Click OK to close the Data Validation dialog box ow A WN After you perform these steps the message appears whenever the cell is activated see Figure 23 1 for an example You can also click and drag the message to a different loca tion if it s in your way A B G D E pI 1 Name Shirley Boberly 2 Department Customer Relations 3 User Code R 983 IMPORTANT 2 Please make sure
6. in D4 aed a layout Formulas Paste z P 123 3 in D3 mimm A J iu Shape Style Insert Shape N 3 Paste LX f A Drag and Drop D E F 22 Bold 23 24 25 26 Typing 544 34 in C3 Typing SUM C3 C4 in C5 Typing 892 12 in C4 27 Undo 6 Actions 28 29 30 31 32 Figure 5 1 The Undo button displays a list of recent actions that can be undone Tip 5 Undoing Redoing and Repeating Note g Keep in mind that the effect of executing a macro cannot be undone In fact running a macro wipes out all the Undo information This serious weakness in Excel isn t present in Microsoft Word However at least Excel 2007 fixed another Undo related problem In previous versions saving your workbook destroyed the Undo information Fortunately that s no longer the case Redoing The Redo button located to the right of the Undo button on the QAT essentially undoes the Undo command If you undo too much you can click the Redo button or press Ctrl Y or F4 to repeat commands that have been undone Repeating You can repeat a command by pressing Ctrl Y or F4 This command simply repeats the last action unless the last operation was an Undo operation In this case the Undo is undone as described in the previous section Repeating a command can be a great timesaver Here s an example of how useful the Repeat command can be You may apply lots of formatting for example font size bold for matting background
7. s much easier to click the arrow on the Format button and click Choose Format from Cell Then click on a cell that already has the formatting you want to replace Click the lower Format button the one beside the Replace With field to display the Find Format dialog box again You can use the Choose Format from Cell option and specify a cell that contains the replacement formatting Or use the tabs in the Find Format dialog box to specify the desired formatting In this example click the Font tab and select Cambria size 16 bold style and white color On the Patterns tab choose black as the cell shading color At this point the Find And Replace dialog box should resemble Figure 20 1 In the Find and Replace dialog box click the Replace All button Find and Replace Jeg Find Replace Find what v Preview Format Replace with SS Ey Within Sheet v Match case Match entire cell contents Search By Rows v Lookin Formulas v Replace all Replace Find All Eind Next Close Figure 20 1 The Replace tab in the Find and Replace dialog box Tip 20 Replacing Formatting NOTE If you use the Choose Format from Cell option in Step 4 you may find that not all occur rences of the formatting are replaced usually because one or more aspects of the for matting do not match For example if you click on a cell that has General number for
8. 00 290 500 00 _1 150 000 00 5 Travel 40 500 00 42 525 00 44 651 25 46 883 81 174 560 06 6 Supplies 59 500 00 62 475 00 65 598 75 68 878 69 256 452 44 7 Facility 144 000 00 144 000 00 144 000 00 144 000 00 576 000 00 f 8 Total 530 500 00 535 500 00 540 750 00 550 262 50 2 157 012 50 7 15 16 v7 4 Totals Marketing Operations Manufacturing ey NI m Figure 3 3 Each worksheet in this workbook is laid out identically Assume that you want to apply the same formatting to all sheets for example you want to make the column headings bold with background shading Selecting a multisheet range is the best approach When the ranges are selected the formatting is applied to all sheets In general selecting a multisheet range is a simple two step process 1 Select the range in one sheet 2 Select the worksheets to include in the range Tip 3 Selecting Cells Efficiently NOTE 7 To select a group of contiguous worksheets press Shift and click the sheet tab of the last worksheet that you want to include in the selection To select individual worksheets press Ctrl and click the sheet tab of each worksheet that you want to select When you make the selection the sheet tabs of the selected sheets appear with a white back ground and Excel displays Group on the title bar When you finish working with the multisheet range click any sheet tab to leave Group mode Hed Tip 4 Making Special Range Selec
9. 1697 RANDBETWEEN 1000 5000 Km 58 cell s found Figure 20 2 The Find and Replace dialog box expands to display a list of all matching cells When these cells are selected you can then format them any way you like Note that you can widen the columns in the list of found cells by dragging a column border and you can also sort the list by clicking a column header NOTE 7 Conspicuously absent from the Find and Replace dialog box is the ability to search for cells by their styles Despite the fact that Excel 2007 places increased emphasis on cell styles it s not possible to find all cells that use a particular style and apply a different style to those cells You can find and replace the formatting but the cell style does not change Tip 21 Changing the Excel Color Scheme Changing the Excel Color Scheme One of the new features in Office 2007 is the ability to change the color scheme of the applications You do this in the Excel Options dialog box Choose Office gt Excel Options and then click the Popular tab Use the Color Scheme drop down list to select your color choice Blue Sliver or Black see Figure 21 1 When you change the color scheme your choice affects all other Microsoft Office 2007 applications Excel Options Axi pean z c the most popular options in Excel U han m r i in S ange the most popular options Xcel Qa Formulas ai Proofing Top options for working with Excel Save 7 Show Mi
10. Excel Training Getting help amp Workbook management Developer Reference Accessbilty Worksheet and Excel table basics 5 eee Content from this computer ity Workbook management Forma and name basics v Excel Help E Q Function reference ics Formula and name basics Developer Reference rRe sortna and condtonaly d TRESE PeETENC Fitering sorting and conditionally formatting GBSummerizing consoldating and outlining dat a data pVoidating data Summarizing consoldating and outining data Validating data Bimporting data PivotTable reports and PivotChart reports Importing data PivotTable reports and PivotChart reports QWhatif analyss What4f analysis Working with XML poeta Metin t Charts Security and privacy gchets secuity and privacy Macros Excel and the Web yeeros Automation and programmability Activating Excel j Wi Excel andthe web Customizing Working in a different language Automation and programmability Aciivating Excel Add ins Working with graphics amp Customizng Publishing to SharePoint Server Excel Services Collaboration eal x BrceHetp Rotine Figure 24 2 Specifying where to search for help When you re connected to Office Online you can identify the URL for the Help topic and send it to someone else To do so right click the current Help topic and choose Properties The Properties dialog box displays the URL of the Help topic Select the URL text with your mouse and press Ctrl C to copy it The URL can be opened
11. Range by Using the Shift and Arrow Keys The simplest way to select a range is to press and hold Shift and then use the arrow keys to highlight the cells For larger selections you can use PgDn or PgUp while pressing Shift to move in larger increments You can also use the End key to quickly extend a selection to the last non empty cell in a row or column To select the range B3 B8 see Figure 3 1 by using the keyboard move the cell pointer to B3 and then press the Shift key while you press End followed by the down arrow key Similarly to select B3 D3 press the Shift key while you press End followed by the right arrow key A B G D E F G H I 1 2 3 A 2 3 4 4 5 6 5 7 8 9 6 10 11 12 7 13 14 15 8 16 17 18 9 10 11 ay N 13 14 44 gt gt Sheeti 2 Figure 3 1 A range of cells Selecting the Current Region Often you need to select a large rectangular selection of cells the current region To select the entire block of cells move the cell pointer anywhere within the range and press Ctrl Shift 8 New If the active cell is within an Excel 2007 table created by choosing Insert Tables Table you can press Ctrl A to select the cells but not the header row or total row within the table Press Ctrl A again to select the entire table including the header row and total row Press Ctrl A a third time to select the entire worksheet Tip 3 Selecting Cells Efficiently Sele
12. all you need to do is customize a blank work book exactly as you like it Here s a list of some of the items you can change e Number of sheets Add or delete sheets as you like You can also change their names Styles Use the Style Gallery Home Styles to customize styles By default all cells use the Normal style so if you want to change the default font in any way including fill color number format font and borders modify the Normal style To change the Normal style right click its name in the Style Gallery and choose Modify Then make the changes in the Style dialog box Print settings Use the commands on the Page Layout tab to specify print related set tings For example you can include header or footer information or adjust the margins e Column widths If you don t like the default column widths change them Graphics You can even insert a graphical object or two for example your company logo or a picture of your cat When the new default workbook is set up to your specifications choose Office Save As In the Save As dialog box follow these steps 1 Select Template xltx in the Save As Type drop down list If your template contains any VBA macros select Excel Macro Enabled Template xltm 2 Name the file book x1tx or book x1tm if it has macros 3 Make sure that the file is saved to your XLStart folder Excel proposes that you save the file in your Templates folder but it must
13. be saved in your XLStart folder Tip 12 Customizing the Default Workbook NOTE 7 If you re using Windows XP the XLStart folder may be located in either of these directories C Documents and Settings lt username gt Application Data Microsoft Excel XLStart C Program Files Microsoft Office Officel2 XLStart If you re using Windows Vista the directory is C Users lt username gt AppData Roaming Microsoft Excel xXLStart After you save the file you can close it Now every time you start Excel the blank work book that s displayed will be based on the template you created In addition when you press Ctrl N the new workbook will be created from your template Hed If you ever need to bypass your new default workbook and start with one of the normal Excel default workbooks choose Office gt New and choose the Blank Workbook item Tip 13 Using Document Themes Using Document Themes Over the years I ve seen hundreds of Excel workbooks that were created by others A sig nificant percentage of these workbooks have one thing in common They are ugly In an effort to help users create more professional looking documents the Office 2007 designers incorporated the concept of Office document themes Using themes is an easy and almost foolproof way to specify the colors and fonts and a variety of graphical effects in a document Best of all changing the entire look of your document is a breeze A few mouse cl
14. cell for each row is the cell in the same column as the active cell If one column is selected cells that are different from the active cell If more than one column is selected the same comparison is done but the comparison cell for each column is the cell in the same rows as the active cell Cells that are referred to in the formulas in the active cell or selection limited to the active sheet You can select either direct precedents or precedents at any level Cells with formulas that refer to the active cell or selection limited to the active sheet You can select either direct dependents or dependents at any level The lower right cell in the worksheet that contains data or formatting Only visible cells in the selection This option is useful when dealing with outlines or a filtered list Cells that have a conditional format applied by using the Home Styles Conditional Formatting command Cells that are set up for data entry validation by using the Data Data Tools Data Validation command The All option selects all cells of this type The Same option selects only the cells that have the same validation rules as the active cell Tip 4 Making Special Range Selections NoTE When you select an option in the Go To Special dialog box be sure to note which sub options become available For example when you select the Constants option the Formulas suboptions become available to help you further refine t
15. color and borders to a cell by using the Format Cells dialog box After you close the dialog box it s a snap to apply that same formatting to other cells or ranges by pressing Ctrl Y Or you may need to insert blank rows at certain locations in your worksheet Issue the Home Cells gt Insert gt Insert Sheet Rows command one time Then move the cell pointer to the next row to be inserted and press Ctrl Y to repeat the row insertion command Excel also has a Repeat button but it s not normally available You can however add this button to your QAT 1 Right click the QAT and choose Customize Quick Access Toolbar to display the Customize tab of the Excel Options dialog box 2 In the Excel Options dialog box select Popular Commands from the drop down list on the right 3 In the list of commands select Repeat 4 Click Add to add the selected command to the QAT 5 Click OK to close the Excel Options dialog box Why add the Repeat button to your QAT when pressing Ctrl Y is so easy One reason is that you can hover your mouse pointer over the button and Excel displays a description of what will be repeated see Figure 5 2 Another reason is that the Repeat button is dis abled if the most recent command cannot be repeated a visual cue that may prevent you from trying to repeat something that can t be repeated Tip 5 Undoing Redoing and Repeating Ca H92 Ny y fo il Home Ins Repea
16. curtailed Although it s possible to modify the Ribbon it s a rather complicated process and it s not something a casual user would do In Office 2007 user interface customization is limited to the Quick Access Toolbar or QAT and that s the topic of this tip If you find that you continually need to switch Ribbon tabs because a frequently used com mand never seems to be on the Ribbon that s displayed this tip is for you About the QAT The QAT is always visible regardless of which Ribbon tab is selected After you customize the QAT your frequently used commands are always one click away NOTE S3 The only situation in which the QAT is not visible is in Full Screen mode which is enabled by choosing View Workbook Views Full Screen To cancel Full Screen mode and restore the Ribbon and QAT right click any cell and choose Close Full Screen or press Escape By default the QAT is located on the left side of the Excel title bar and it includes the fol lowing three tools e Save Saves the active workbook Undo Reverses the effect of the last action e Redo Reverses the effect of the last undo If you prefer you can move the QAT below the Ribbon Right click the QAT and choose Show Quick Access Toolbar Below The Ribbon Moving the QAT below the Ribbon eats up some additional vertical space on your screen In other words you see one or two fewer rows of your worksheet if you move the QAT from its default l
17. found quite a few enhancements including a completely new VBA editor UserForms class modules and more This version known as Excel 2000 could use HTML as a native file format and for the first time supported COM add ins It also featured a self repair capability an enhanced Clipboard and pivot charts VBA programmers could use modeless UserForms and several new VBA functions were introduced Tip 1 Understanding Excel Versions Version Released Comments 10 2001 Known as Excel 2002 or Excel XP this version is part of Office XP It has a long list of new features but most of them are of little value to the majority of users Perhaps this version s most significant feature is its ability to recover your work when Excel crashes 11 2003 Officially known as Microsoft Office Excel 2003 this version s new features are improved support for XML a new list range feature Smart Tag enhancements and corrected statistical functions 12 2007 As write this book the current version is officially known as Microsoft Office Excel 2007 This version has many new features including new file formats a revamped user interface support for much larger worksheets a handy page layout view document themes new conditional formatting options much better looking charts a new table feature a few new worksheet functions and much more Hed Excel 6 doesn t exist Beginning with Excel 7 the version numbering was changed so
18. is selected the case of the text must match exactly For example searching for smith does not locate Smith Match Entire Cell Contents If this check box is selected a match occurs if the cell contains only the search string and nothing else For example searching for Excel doesn t locate a cell that contains Microsoft Excel Look In This drop down list has three options Values Formulas and Comments If for example Values is selected searching for 900 doesn t find a cell that contains 900 if that value is generated by a formula Remember that searching operates on the selected range of cells If you want to search the entire worksheet select only one cell Also remember that searches do not include numeric formatting For example if you have a value that uses currency formatting so that it appears as 54 00 searching for 5 doesn t locate that value Working with dates can be a bit tricky because Excel offers many ways to format dates If you search for a date by using the default date format Excel locates the dates even if they re formatted differently For example if your system uses the m d y date format the search string 10 2005 finds all dates in October 2005 regardless of how the dates are formatted You can also use an empty Replace With field For example to quickly delete all asterisks from your worksheet enter in the Find What field and leave the Replace With field blank When you click the
19. the Format Cells dialog box 3 In the Format Cells dialog box click the Protection tab and remove the check mark from the Locked check box 4 Choose Review Protect Sheet to display the Protect Sheet dialog box 5 In the Protect Sheet dialog box remove the check mark from the Select Locked Cells check box see Figure 22 3 pa Protect Sheet Protect worksheet and contents of locked cells Password to unprotect sheet Allow all users of this worksheet to _ Select locked cells unlocked lt Format columns Format rows Insert columns Insert rows Insert hyperlinks Delete columns Delete rows x C Cee Figure 22 3 Use the Protect Sheet dialog box to prevent the user from selecting locked cells 6 If desired specify a password that will be required in order to unprotect the sheet and then click OK Tip 22 Limiting the Usable Area in a Worksheet After you perform these steps only the unlocked cells those you selected in Step 1 are accessible VAN CAUTION Worksheet passwords are not at all secure In fact it s a trivial matter to crack such a password Therefore worksheet protection is more of a convenience feature than a secu rity feature Hed Tip 23 Using an Alternative to Cell Comments Using an Alternative to Cell Comments As you probably know you can attach a comment to any cell by using the Review gt Comments gt
20. the entire background of the sheet tab is displayed in that color Changing the Sheet Tab Text Size If you find that the text displayed in your sheet tabs is too small or too large you can change the size of the text but you have to make a system wide change The text size on the Excel sheets tabs is determined by a Windows systemwide setting To change it open the Windows Display Properties dialog box The easiest way to do this is to right click on your desktop and choose Properties from the shortcut menu In the Display Properties dialog box that appears follow these steps which assume that you re using Windows XP 1 Click the Appearance tab in the Display Properties dialog box 2 Click the Advanced button to display the Advanced Appearance dialog box see Figure 14 1 3 Choose Scrollbar from the Item drop down list 4 Adjust the Size setting and then click OK Be aware that this setting affects the size of the scroll bars in all your applications Tip 14 Changing the Sheet Tab Appearance EA Appearance Inactive Window Active Window Normal Selected Window Text Message Box Message Te ok If you select a windows and buttons setting other than Windows Classic it will override the following settings except in some older programs Item Size Scrollbar j 17 OK Cancel Figure 14 1 Use the Advanced Appearance dialog box to change the text size in the Excel sheet tabs
21. using the data in the selected range Display the Go To dialog box Display the keytips for the Ribbon commands so that you can access the commands by using letters on the keyboard Edit the comment in the active cell or insert a comment if the cell doesn t have one Display the shortcut menu for the selected item Activate the next window Activate the previous sheet in the workbook Activate the next sheet in the workbook Display the Paste Special dialog box Perform the equivalent of clicking the AutoSum button Make the selected cells bold Copy the selected cells Fill down Display the Find dialog box Display the Replace dialog box Make the selected cells italic Create a new default workbook Fill to the right Save the active workbook Tip 6 Discovering Some Useful Shortcut Keys Shortcut What You Can Do with It Ctrl V Paste a copied or cut item in the selected cell Ctrl X Cut the selected cells Ctrl Y Repeat the last repeatable command Ctrl Z Undo the last action Hed Tip 7 Navigating Sheets in a Workbook Navigating Sheets in a Workbook As you know a single workbook can contain multiple worksheets The sheet tabs at the bottom of the Excel window identify the worksheets All Excel users know that they can activate a different sheet by clicking its sheet tab If the tab for the sheet you want isn t visible you can use the tab scroll controls to the left of the first sheet tab to scroll the tabs left
22. with any Web browser It took me a long time to figure this one out You can determine which buttons appear on the toolbar in the Help window Click the tiny icon on the right side of the toolbar and select Add or Remove buttons You see the list shown in Figure 24 3 Context sensitive help is spotty For example if an Excel dialog box is displayed press ing F1 or clicking the question mark icon on the dialog box title bar displays the Help window In some cases the Help information relates to what you re doing for example the Name Manager dialog box In other cases you re presented with the opening screen of the Excel Help system for example when the Sort dialog box is displayed In the latter case you can use the Search box to find the information that should have been displayed for you automatically You have no way to display the Office Help window if an Office application is not run ning In previous versions of Office you could double click a CHM help file and the help file would be displayed That s no longer possible in Office 2007 because of the new Help system technology that s used Ticket 70 Tip 24 Understanding the Excel Help System Exce Help x oO KY S J gt P Search Add or Remove Buttons Standard gt v Back a Forward ipa amp stop s Refresh Beowee ioe Lice GS Home What s new Getting help amp Print X Irstaling A Change F
23. Creating a Live Image of a Range To create an image that s linked to the original range of cells select the cells and press Ctrl C to copy the range to the Clipboard Then choose Home Clipboard Paste gt As Picture Paste Picture Link Excel pastes a picture of the original range and the pic ture is linked if you make changes to the original those changes are shown in the linked picture Notice that when you select the linked picture the formula bar displays the address of the original range You can edit this range reference to change the cells that are displayed in the picture To de link the picture just delete the formula on the formula bar You can also cut and paste this picture to a different worksheet if you like That makes it easy to refer to information on a different sheet Unfortunately Excel doesn t allow much formatting of this picture You can change its size by dragging a corner but none of the standard picture formatting commands is available Figure 18 2 shows a linked picture of a range placed on top of a shape which has lots of interesting formatting capabilities Placing a linked picture on top of a shape is a good way to make a particular range stand out 3 4 5 6 7 8 9 Closing Price Per Share ul January 14 125 February 15 375 z March 16 250 April 14 188 Hl May 11 250 H June 10 000 July 12 125 if August 13 375 a September 15 313 October 15 375 20 Nov
24. J KE 1 The Sprocket And Widget Report 2 3 Monthly Sales 4 Sprockets E Widgets 5 500 6 gt 450 8 400 9 350 y 10 300 ie 1l 250 A 12 Month Ma Widgets MSprockets amp 300 13 Jan 125 212 S 14 Feb 145 224 150 4 J 15 Mar 132 190 100 16 Apr 154 245 50 17 May 164 289 i 18 Jun 178 275 i Jan Feb Mar Apr May Jun 20 MarH Sheeti Sa Doi M EEE Figure 13 2 The worksheet after applying a different theme Applying a Theme Figure 13 3 shows the theme choices that appear when you choose Page gt Layout gt Themes This display is a live preview display As you move your mouse over the theme choices the active worksheet displays the theme When you see a theme you like click it to apply the theme to all worksheets in the workbook A theme applies to the entire workbook You cannot use different themes on different work sheets within a workbook When you specify a particular theme you find that the gallery choices for various elements reflect the new theme For example the chart styles that you can choose from vary depending on which theme is active Because themes use different fonts and font sizes changing to a different theme can affect the layout of your worksheet For example after you apply a new theme a worksheet that printed on a single page may spill over to a second page Therefore you may need to make some adjustments after you apply a new theme Customizing a Theme Notice that
25. Part Q Basic Excel Usage n this part you ll find tips and tricks covering some of the fundamental uses of Excel from selecting cells and navigating sheets in a workbook to hiding rows and columns as well as working with the Quick Access Toolbar and changing Excel s color scheme Tips and Where to Find Them Tip 1 Tip 2 Tip 3 Tip 4 Tip 5 Tip 6 Tip 7 Tip 8 Tip 9 Tip 10 Tip 11 Tip 12 Understanding Excel Versions Maximizing Ribbon Efficiency Selecting Cells Efficiently Making Special Range Selections Undoing Redoing and Repeating Discovering Some Useful Shortcut Keys Navigating Sheets in a Workbook Resetting the Used Area of a Worksheet Understanding Workbooks versus Windows Customizing Your Quick Access Toolbar Accessing the Ribbon with the Keyboard Customizing the Default Workbook 9 12 14 18 21 24 26 28 29 31 35 36 Tip 13 Tip 14 Tip 15 Tip 16 Tip 17 Tip 18 Tip 19 Tip 20 Tip 21 Tip 22 Tip 23 Tip 24 Tip 25 Tip 26 Using Document Themes Changing the Sheet Tab Appearance Hiding User Interface Elements Hiding Columns or Rows Hiding Cell Contents Taking Pictures of Ranges Performing Inexact Searches Replacing Formatting Changing the Excel Color Scheme Limiting the Usable Area in a Worksheet Using an Alternative to Cell Comments Understanding the Excel Help System Making a Worksheet Very H
26. Replace All button Excel finds all the asterisks and replaces them with nothing Tip 20 Replacing Formatting Replacing Formatting A useful but often overlooked Excel feature is the ability to search for and replace cell formatting For example if you have cells that use the 14 point Calibri font it s a simple matter to change the formatting in all those cells to something else The process isn t as intuitive as it could be so I walk you through the steps Assume that your worksheet contains many cells that are formatted with a yellow background and in 14 point Calibri in bold Furthermore assume that these cells are scattered throughout the workbook The goal is to change all those cells so that they re displayed with 16 point Cambria in bold with white text on a black background To change the formatting by searching and replacing follow these steps Hed 1 Click on any single cell and choose Home Editing Find amp Select Replace or press Ctrl H to display the Find and Replace dialog box If you want to limit the searching to a particular range select the range rather than a single cell 2 In the Find and Replace dialog box make sure that the Find What and Replace With fields are blank 3 Click the upper Format button the one beside the Find What field to display the Find Format dialog box You can use the Find Format dialog box to specify the formatting you re looking for but it
27. Ribbon From Your Keyboard Accessing the Ribbon from Your Keyboard At first glance you may think that the Ribbon is completely mouse centric After all none of the commands has the traditional underlined letter to indicate the Alt keystrokes In fact the Ribbon is very keyboard friendly The trick is to press the Alt key or slash key to display the pop up Aeytips Each Ribbon control has a letter or series of letters that you type to issue the command By the way you don t have to hold down the Alt key as you type the keytip letters Figure 11 1 shows how the Home tab looks after I press the Alt key to display the keytips If you press one of the keytips the screen then displays more keytips For example to use the keyboard to align the cell contents to the left press Alt followed by H for Home and then AL for Align Left If you re a keyboard fan like I am after just a few times you memorize the keystrokes required for common commands that you use frequently Home Insert Page Layout Formulas Date Review View Developer H N P M A Ww LL h Calibri j1 Wo y a i E kS 1 wl iii M Paste p3 B Z u i J amp A E E a Yor ae lh Conditional Format _ Cell s a Formatting as Table Styles Clipboard fa Figure 11 1 Pressing Alt displays the keytips After you press Alt you can also use the left and right arrow keys to scroll through the tabs When you reach the proper tab pres
28. abs Design Layout and Format Notice that these new tabs contain a description on the Excel title bar when a chart is selected the description reads Chart Tools Selecting any of the following elements also results in the display of contextual tabs a table a pivot table a drawing a shape or WordArt a picture a photo or clip art a header or footer in Page Layout view or a SmartArt diagram You can of course continue to use all the other tabs when a contextual tab is displayed Tip 2 Maximizing Ribbon Efficiency Aan ae 3y a RE Home Insert 4 F t Revie e dop Y A ib u Aa sw t eneral y a Insert 43 s J Delete Paste y B z u B gt A E E H Mergea cent a sahis lipt For 7 Il r ad 9 3 8 Home 1 Page Layout Formi Data Ri D 9 x Caib n ana s E Ay A s i z 3J s a Delete a Says By E Be r A a oe ital raes I 1 6 zzv a 3 tyl und Format 47 gt Filter Select Ea id g ua ay Insert Page Layout Formulas Date R D e is 3 amp Celib u E A W a B z ula 3 A Paste Alignment Numb I B A 5 2 Figure 2 1 The look of the Ribbon varies depending on the width of the Excel window e Right click just about anything on screen to get a context sensitive shortcut menu It s one element of the old user interface that s still in place And if you re trying to decrease your mouse dependence you can also press Shift F10 to display the shortcut menu for the
29. ar at the bottom of the Excel window displays a variety of information In pre vious versions of Excel the user could turn the status bar display on or off Excel 2007 requires a VBA macro to toggle the status bar display Sub ToggleStatusBar With Application DisplayStatusBar Not DisplayStatusBar End With End Sub Hed Tip 16 Hiding Columns or Rows Hiding Columns or Rows If you have data in a column or row that you don t want to see you can hide the column or row Doing this is often useful if you have formulas that provide intermediate calculations and you don t want them to appear in a report Or you may just want to hide unused rows and columns so that you can focus only on the used area of the sheet NOTE 7 Formulas that refer to data in hidden rows or columns continue to function normally An exception is the SUBTOTAL function If the first argument for SUBTOTAL is greater than 100 the SUBTOTAL function ignores the data in the hidden rows or columns Refer to the Help system for information about the arguments for SUBTOTAL It does lots more than just sum Hiding To hide one or more columns use any of these techniques e Select a cell in the column or columns to be hidden Then choose Home Cells gt Format gt Hide amp Unhide Hide Columns e Select entire columns and then right click and choose Hide from the shortcut menu e Select a cell in the column or columns to hide and press Ct
30. ber along the left of the worksheet Or select any cell in the row and press Shift spacebar To select multiple adjacent rows click and drag in the row number area Or select any cell in the first or last row press Shift spacebar and use the arrow keys to extend the selec tion down or up To select multiple nonadjacent rows press Ctrl while you click the row numbers for the rows you want to include Hed Tip 3 Selecting Cells Efficiently Selecting Entire Columns To select a single column click a column letter along the top of the worksheet Or select any cell in the column and press Ctrl spacebar To select multiple adjacent columns click and drag in the column letter section Or select any cell in the first or last column press Ctrl spacebar and use the arrow keys to extend the selection to the right or left To select multiple nonadjacent columns press Ctrl while you click the column letters for the columns you want to include Selecting Multisheet Ranges In addition to two dimensional ranges on a single worksheet ranges can extend across multiple worksheets to be three dimensional ranges Figure 3 3 shows a simple example of a multisheet workbook The workbook has four sheets named Totals Marketing Operations and Manufacturing The sheets are laid out identically I budgetxlsx Arx A 8 c D E F G E 1 Budget Summary 2 3 laz a3 aa Year Total 4 Salaries 286 500 00 286 500 00 286 500
31. click 20 hyperlinks you see the message 20 times You even see the message if the hyperlink points to an Excel workbook that s in a trusted location And there is no direct way to turn it off a Microsoft Office Excel Security Notice Microsoft Office has identified a potential security concern This location may be unsafe C Documents and Settings jw My Documents Excel action xis Hyperlinks can be harmful to your computer and data To protect your computer click only those hyperlinks from trusted sources Do you want to continue Yes Figure 26 1 Clicking a hyperlink may display this message You can avoid seeing this annoying message but you need to make a modification to the Windows Registry 1 Close Excel 2 Click the Windows Start button and then choose Run 3 In the Open dialog box type regedit and then click OK This command launches the Registry Editor 4 In the Registry Editor locate the following Registry subkey and click it HKEY_CURRENT_USER Software Microsoft 0ffice 12 0 Common Choose Edit gt New Key Type Security and then press Enter to name the key With the Security key selected choose Edit gt New gt DWORD Value Type DisableHyperlinkWarning and then press Enter to name the entry Double click the DisableHyperlinkWarning entry to display the Edit DWORD Value dialog box ON OO Tip 26 Disabling Hyperlink Warnings 10 In th
32. cting a Range by Shift Clicking When yov re selecting a very large range using the mouse may be the most efficient method but dragging is not required Select the upper left cell in the range Then scroll to the lower right corner of the range press Shift and click the lower right cell Selecting Noncontiguous Ranges Most of the time your range selections are probably simple rectangular ranges In some cases you may need to make a multiple selection a selection that includes nonadjacent cells or ranges For example you may want to apply formatting to cells in different areas of your worksheet If you make a multiple selection you can apply the formatting in one step to all selected ranges Figure 3 2 shows an example of a multiple selection A B G D E F G H E A 2 2 3 2 3 4 4 5 6 5 8 6 10 11 12 7 13 14 15 8 16 17 18 9 10 11 12 13 14 z 4 gt H Sheeti lt 3 EN J Figure 3 2 A multiple selection that consists of noncontiguous ranges You can select a noncontiguous range by using either the mouse or the keyboard Press Ctrl as you click and drag the mouse to highlight individual cells or ranges From the keyboard select a range as described previously by using the Shift key Then press Shift F8 to select another range without canceling the previous range selection Repeat this action as many times as needed Selecting Entire Rows To select a single row click a row num
33. e 5 Save your workbook and Excel resets the last cell After performing these steps pressing Ctrl End takes you to the real last cell Tip 9 Understanding Workbooks versus Windows Understanding Workbooks versus Windows One of the most common questions asked in the Excel newsgroups is Why is Excel dis playing two copies of my workbook Then the Excel users go on to describe the symp toms The filename is followed by a colon and a number for example budget xlsx 2 Normally a workbook is displayed in a single window within Excel However you can cre ate multiple windows for a single workbook by using the View gt Window gt New Window command Most people who ask this question in the newsgroups have probably issued the New Window command accidentally What they re really asking for is a way to close the addi tional window or windows That s an easy problem to solve Just click the X on the title bar of the unwanted windows Or press Ctrl F4 to close the window After you close all extra windows the title bar no longer displays the window number By the way the multiple window configuration is saved with the workbook so when you open the file later the multiple windows are still displayed Although many people are confused about multiple windows there are at least two good reasons why you might want your workbook to display in two or more windows e You can view two worksheets in the same workbook si
34. e Edit DWORD Value dialog box click the Decimal option and type 1 in the Value data field 11 Click OK to enter the value Figure 26 2 shows the Registry Editor after I made the change Ei Registry Editor Woe File Edit View Favorites Help s a 12 0 A Name Type Data Access ab pefsut REG_SZ value not set Clip Organizer Rig oisabletyperinkwarning REG_DWORD 0x00000001 1 CLView Common E charting DataServices a CrawAlerts a FixedFormat EJ ceneral e HelpViewer fa Internet a LerquageResources gt POLE Hed CJ Licensing 2 Maisetings F gO Migration GJ Onobjectcontrol 3 E Open Find 4 gt gt My Computer HKEY_CURRENT_USER Software Microsoft Office 12 0 Common Security Figure 26 2 The Registry Editor with a new setting that disables the hyperlink warning message After performing these steps restart Excel The new setting takes effect By the way this change affects all Microsoft Office 2007 applications Even after making this change you may still get a warning when you click a hyperlink for a certain type of file For example after I made this modification I still saw a warning when I clicked a hyperlink for an MP3 music file I had to make another change for this particular file type Double click the My Computer icon to open an Explorer window Choose Tools Folder Options Click the File Types tab Inthe Register File Types list sel
35. ect the extension for the type of file that s generating a hyperlink warning for example MP3 5 Click Advanced to display the Edit File Type dialog box A N Tip 26 Disabling Hyperlink Warnings 6 Remove the check mark from the Confirm Open After Download option and click OK 7 Click Close to close the Folder Options dialog box You may need to repeat these steps for other file types Eventually Excel will be an envi ronment free of hyperlink warnings
36. ember 18 875 December 20 750 22 Damma 26 27 4 gt Sheeti J 4 mamis Figure 18 2 A linked picture of a range placed on top of a shape Tip 19 Performing Inexact Searches 51 Performing Inexact Searches If you have a large worksheet with lots of data locating what you re looking for can be dif ficult The Excel Find and Replace dialog box is a useful tool for locating information and it has a few features that many users overlook Access the Find and Replace dialog box by choosing Home Editing gt Find amp Select gt Find or pressing Ctrl F If you re replacing information you can use Home Editing gt Find amp Select gt Replace or Ctrl H The only difference is which of the two tabs is dis played in the dialog box Figure 19 1 shows the Find and Replace dialog box after clicking the Options button which expands the dialog box to show additional options ge w Find and Replace JEI a Find Replace Find what M No Format Set Format x Within Sheet v Match case Match entire cell contents Search By Rows v Lookin Formulas v Find All Eind Next Close Figure 19 1 The Find and Replace dialog box with the Find tab selected In many cases you want to locate approximate text For example you may be trying to find data for a customer named Stephen R Rosencrantz You can of course search for the exact te
37. er right cell in the worksheet is an empty cell not the real last cell In other words Excel sometimes loses track of the used area of your worksheet For example you may find that a workbook s file size seems much too large It s possible that Excel has misidentified the used area and is storing a large number of empty cells First try saving the workbook After the workbook is saved Excel may correctly identify the last cell If Excel still doesn t identify the last cell it s probably because you deleted data but left the formatting in the cells To force Excel to identify the real used area you need to delete the columns to the right of your data and then delete the rows below your data For example assume that the real last cell in your workbook is G25 but pressing Ctrl End takes you to some other cell M50 for example To delete those formatted cells follow these steps 1 Select all columns to the right of column G To do this activate any cell in column H and then press Ctrl spacebar followed by Shift End and then the right arrow key 2 Select Home Cells gt Delete gt Delete Sheet Columns Or right click any column header and choose Delete 3 Select all rows below row 25 To do this activate any cell in row 26 and then press Shift spacebar followed by Ctrl End and then the down arrow key 4 Select Home Cells gt Delete gt Delete Sheet Row Or right click any column header and choose Delet
38. fy the two fonts you prefer see Figure 13 4 When you use the Home Fonts Font control the two fonts from the current theme are listed first in the drop down list Sample Heading Body text body text body text Body text body text Figure 13 4 Use this dialog box to specify two fonts for a theme Tip 13 Using Document Themes 41 Use the Page Layout gt Themes Colors control to select a different set of colors And if you re so inclined you can even create a custom set of colors by choosing Page Layout gt Themes Colors gt Create Theme Colors This command displays the dialog box shown in Figure 13 5 Note that each theme consists of 12 colors Four of the colors are for text and backgrounds six are for accents and two are for hyperlinks As you specify different col ors the Preview panel in the dialog box is updated Greate New Theme Colors Theme colors Sample Text Background Dark 1 v Text Text a Text Background Light 1 Oz zi Text Background Dark2 v o i a 4 Text Background Light 2 v aii ak Accent 1 2a Hyperlink Accent 2 v Hyperlink Accent 3 v Accent 4 v Accent 5 x Accent 6 X Hyperlink lia Eollowed Hyperlink E Name Custom 2 Save Cancel Figure 13 5 If you re feeling creative you can specify a set of custom colors for a theme Theme effects operate on graphical elements
39. g Pictures of Ranges Excel 2007 makes it easy to convert a range of cells into a picture The picture can either be a dead image it doesn t change if the original range changes or a live picture which reflects changes in the original range The range can even contain objects such as charts Creating a Static Image of a Range To create a snapshot of a range start by selecting a range of cells and then press Ctrl C to copy the range to the Clipboard Then choose Home Clipboard Paste gt As Picture Paste As Picture The result is a graphical image of the original range When you select this image Excel displays its Picture Tools context menu which means that you can apply some additional formatting to the picture Hed Figure 18 1 shows a range of cells B2 E9 along with a picture of the range after I applied one of the built in styles from the Picture Tools gt Format Picture Styles gallery NIN IN BiB Rie Ie le RB BIR poa NIBP SO WOOD WB WIN IR Oo 23 Medebobl _Sheet1 I January June Sales by Region E u E Figure 18 1 A picture of a range after applying some picture formatting A B c D E F z 1 3 _ Region1 Region2 Total 4 January 98 832 67 989 166 821 5 February 94 302 74 094 168 396 8 March 101 305 88 406 189 711 7 April 99 334 75 321 174 655 8 May 114 565 65 923 180 488 9 June 108 323 60 332 168 655 LL Tip 18 Taking Pictures of Ranges
40. he results Likewise the Dependents suboptions also apply to Precedents and the Data Validation subop tions also apply to Conditional formats CROSS REFERENCE For information about selecting cells based on their content see Tip 19 Tip 5 Undoing Redoing and Repeating 21 Undoing Redoing and Repeating This tip describes three procedures that every Excel user needs to understand These pro cedures help you recover from mistakes and improve your editing efficiency Undoing Just about every command in Excel can be reversed by using the Undo command In Excel 2007 the Undo command is located on the Quick Access Toolbar QAT which is normally displayed on the left side of the Excel title bar You can also press Ctrl Z to undo your actions Hed Choose Undo after issuing a command in error and it s as though you never issued the com mand You can reverse the effects of the last 100 commands that you executed by selecting Undo multiple times Some actions however can t be reversed Anything that you do by using the Office button for example saving a workbook is not undoable he 2007 Undoing 100 actions is new to Excel 2007 In previous versions the Undo list was limited to 16 actions If you click the arrow on the right side of the Undo button you see a description of the recent commands that can be reversed see Figure 5 1 Qa I x Jx Format Cells Typing 332 65 in D5 Typing 344 9
41. icks is all it takes to apply a different theme and change the look of your workbook Importantly the concept of themes is incorporated into other Office 2007 applications Therefore a company can now easily create a standard look for all its documents Elements within Excel that are controlled by themes are e Cells and ranges that use theme colors as opposed to standard colors e Tables e Charts e Pivot tables e Shapes e SmartArt e WordArt e Sheet tab colors see Tip 14 Figure 13 1 shows a worksheet that contains a SmartArt diagram a table a chart anda range formatted using the Heading 1 named style These items all use the default format ting which is known as Office Theme A B c D E F G H 1 J K L Ej The Sprocket And Widget Report Monthly Sales W Sprockets Widgets 500 450 400 350 10 300 g 11 250 12 200 13 150 14 Feb 145 224 100 4 15 Mar 132 190 50 16 Aor 154 245 6 i jan Feb Mar Apr May Jun 17 May 164 289 18 jun 178 275 19 m MAH Sheeti 23 m Figure 13 1 The elements in this worksheet use default formatting en au slwe Tip 13 Using Document Themes Figure 13 2 shows the same worksheet after applying a different document theme The dif ferent theme changes the fonts colors which may not be apparent in the figure and graphical effects for the SmartArt diagram A B C D E F G H
42. idden Disabling Hyperlink Warnings 49 51 53 55 56 60 61 64 Tip 1 Understanding Excel Versions Understanding Excel Versions If you re reading this book you should be using Excel 2007 which is radically different from all other Excel versions I ve found that most users don t even know which version of Excel they use Here s how to find out your Excel version plus additional information If Excel has a menu titled Help click it and then choose About If Excel does not have a menu titled Help then you re using Excel 2007 Follow these steps to find out more 1 Choose Office gt Excel Options Hed 2 In the Excel Options dialog box click the Resources tab 3 On the Resources tab click the About button Figure 1 1 shows that I m using version 12 0 also known officially as Microsoft Office Excel 2007 Most people just call it Excel 2007 Notice the decimal places after the version number That represents the build of the product In my case I m using build 4518 I don t know what the 1014 means About Microsoft Office Excel Microsoft Office Excel 2007 12 0 4518 1014 MSO 12 0 4518 1014 Part of Microsoft Office Professional 2007 2006 Microsoft Corporation All rights reserved Portions of International CorrectSpell spelling correction system 1993 by Lernout amp Hauspie Speech Products N V All rights reserved The American Heritage Dictionary of
43. ile formats she may not even be able to open the file Ticket 70 NOTE Microsoft provides the free add on Office 2007 Compatibility Pack This download gives previous versions of Office support for the new Office 2007 file formats Therefore you can share Excel 2007 files with users of Excel 2000 Excel 2002 and Excel 2003 Note however that this add on doesn t endow the older versions with any new features It just allows the software to open and save the files For the record the following table shows the major version numbers of Excel Version 7 Released 1985 1987 1990 1992 1993 1995 1997 1999 Comments The first version of Excel was for the Apple Macintosh The first Windows version was labeled 2 to correspond to the Macintosh version Because Windows was not widely used this version included a runtime version of Windows This version included toolbars drawing capabilities outlining add in support 3D charts and many more new features The first popular version of Excel It included quite a few usability features Excel 5 a major upgrade included multisheet workbooks and support for VBA This version known as Excel 95 was the first major 32 bit version of Excel Feature wise it was similar to Excel 5 This version known as Excel 97 was the first version to support conditional formatting and data validation It also incorporated new menus and toolbars VBA programmers
44. matting it doesn t replace cells that have Date number formatting The solution is to click the Format button to display the Find Format dialog box and then click the Clear button in each dialog box tab in which the formatting is not relevant In some cases you may prefer to simply select the cells with a particular format To do so perform Steps 1 through 4 in the preceding step list Then click the Find All button The dialog box expands to display information about the qualifying cells see Figure 20 2 Click on the bottom part of the dialog box and then press Ctrl A to select all qualifying cells P Find and Replace Find Replace Replace with No Format Set Format v within sheet x E Match case x C Match entire cell contents Search By Rows Lookin Formulas Replace All Replace Find All Eind Next Close Book Sheet Name Cell Value Formula a Book2 Sheet2 c 4 1476 RANDBETWEEN 1000 5000 3 Book2 Sheet2 D 4 3214 RANDBETWEEN 1000 5000 j Book2 Sheet2 SES4 2630 RANDBETWEEN 1000 5000 Book2 Sheet2 c 5 4924 RANDBETWEEN 1000 5000 Book2 Sheet2 0 5 4529 RANDBETWEEN 1000 5000 Book2 Sheet2 SESS 1881 RANDBETWEEN 1000 5000 Book2 Sheet2 C 6 1205 RANDBETWEEN 1000 5000 Book2 Sheet2 sD 6 2326 RANDBETWEEN 1000 5000 Book2 Sheet2 SES6 3160 RANDBETWEEN 1000 5000 Book2 Sheet2 c 7 2319 RANDBETWEEN 1000 5000 Book2 Sheet2 0 7 4943 RANDBETWEEN 1000 5000 Book2 Sheet2 E 7
45. mbers and column letters headers Comments If you use cell comments you can choose to display a comment indicator or not or display the comment and indicator I use the second option Comment Indicator Only These choices are on the Review gt Comments tab From the Keyboard Modify the items below by using the keyboard The Ribbon Press Ctrl F1 to turn the Ribbon display off or on In previous versions of Excel Ctrl F1 toggles the Task pane Outline symbols This option is relevant only if you added a worksheet outline by using the Data Outline gt Group command If you don t like seeing the Outline symbols you can turn them off Or you can press Ctrl 8 to toggle the display of the Outline symbols Tip 15 Hiding User Interface Elements From the Excel Options Dialog Box You change the following settings from the Advanced tab in the Excel Options dialog box To display this dialog box choose Office Excel Options Then click the Advanced tab e Windows in Taskbar This option is in the Display section of the Advanced tab in the Excel Options dialog box When the Show All Windows in the Taskbar option is turned on each workbook appears as a separate icon on the Windows taskbar It s too much clutter for my taste so I keep it turned off e Objects This setting refers to graphical objects embedded in your sheets including charts You may want to hide these objects if you have many of them and your system pe
46. multaneously For example you can display Sheet1 in the first window and Sheet2 in the second window and then tile the two windows so that both are visible The View gt Window Arrange All command is useful for tiling windows This statement also applies to chart sheets If you have a chart on a chart sheet you can arrange the windows so that you can see the chart along with its data e You can view cells and their formulas at the same time Create a second window and then press Ctrl to display the formulas Tile the two windows so that you can view the formulas and their results side by side see Figure 9 1 NOTE S3 When you need to compare information in two worksheets or the same worksheet in two windows try the View Window View Side by Side command It makes it easy to compare two worksheets because Excel automatically scrolls the second window to keep them synchronized To use this feature with a single workbook first create a sec ond window by choosing the View gt Window New Window command Hed Tip 9 Understanding Workbooks versus Windows Ve 5 4 2 G Microsoft Excel reac ee Formulas Data Review View Developer Esar Gli E a we Custom Vi SJ Arange T 2p Page a ne Show Hide Zoom 100 Zoomto a Save Switch Macros Layout E Full Screen Selection i Freeze Panes Z 13 Workspace Windows Workbook Views Zoom Window Macros
47. n display the Compatibility Checker dialog box at any time by choosing Office gt Prepare Run Compatibility Checker Tip 2 Maximizing Ribbon Efficiency Maximizing Ribbon Efficiency When you first fired up Excel 2007 you probably noticed that the commands at the top of the window are different very different from other versions Since the beginning of time all Windows programs have had a similar user interface that consists of menus and toolbars The Office 2007 designers went out on a limb and came up with a radically differ ent user interface the Ribbon The words along the top Home Insert and Page Layout for example are known as tabs Click a tab and the Ribbon changes to display a new set of commands arranged in groups For example the Home tab has groups labeled Clipboard Font Alignment and more Using the Ribbon is straightforward enough Somewhat ironically new users will adapt much more quickly to the Ribbon than experienced users will Long time Excel users will spend a considerable amount of time scratching their heads and trying to figure out where their favorite commands now live The following tips help you get the most out of the new Ribbon user interface e Don t be afraid to click on the stuff you find on the Ribbon This advice applies espe cially if you re an experienced user who is trying to adapt to the new user interface Everything you do can be undone by clicking Undo located on the Q
48. ni Toolbar on selection i 7 Enable Live Preview Advanced Z Show Developer tab in the Ribbon Customize v Always use ClearType Colorscheme Blue v Add Ins j ScreenTip style Blue ure descriptions in ScreenTips Trust Center N Silver n Create lists for usdgiadk nd fill sequences Edit Custom Lists Resources R When creating new workbooks Use this font Body Font v Font size lly Default yew for new sheets Normal View v Include this many sheets 1 Personalize your copy of Microsoft Office Username John Walkenbach Choose the languages you went to use with Microsoft Office Language Settings lt gt OK Cancel Figure 21 1 Selecting a new color scheme for Excel You may find that a particular color scheme is easier on the eyes Or you may just want an occasional change of scenery Unfortunately the three choices are all there is what you see is what you get You cannot customize the color schemes in any way and as far as I know third party developers cannot even create new color schemes CROSS REFERENCE Don t confuse color schemes with document themes another new Office 2007 feature The document theme feature is an entirely different concept and these two features are not related See Tip 13 for more about document themes Tip 22 Limiting the Usable Area in a Worksheet Limiting the Usable Area in a Worksheet Have you ever wanted to restrict access to a cer
49. ntire active area of the worksheet Otherwise the selection is based on the selected range Table 4 1 summarizes the options available in the Go To Special dialog box Tip 4 Making Special Range Selections TABLE 4 1 GOTO SPECIAL OPTIONS Option Comments Constants Formulas Blanks Current Region Current Array Objects Row Differences Column Differences Precedents Dependents Last Cell Visible Cells Only Conditional Formats Data Validation What It Selects Only the cells that contain cell comments All non empty cells that don t contain formulas This option is useful if you have a model set up and you want to clear out all input cells and enter new values The formulas remain intact Use the check boxes under the Formulas option to choose which cells to include Cells that contain formulas Qualify this choice by selecting the check box for the type of result Numbers Text Logicals the logical values TRUE or FALSE or Errors Hed All empty cells A rectangular range of cells around the active cell This range is determined by surrounding blank rows and columns You can also use the Ctrl Shift 8 shortcut key combination The entire array used for multicell array formulas All graphical objects on the worksheet If one row is selected cells that are different from the active cell If more than one row is selected the same comparison is done but the comparison
50. ocation Commands on the QAT always appear as small icons with no text When you hover your mouse pointer over an icon you see the name of the command and a brief description Hed Tip 10 Customizing Your Quick Access Toolbar As far as I can tell there is no limit to the number of commands that you can add to your QAT But regardless of the number of icons the QAT always displays a single line of icons If the number of icons exceeds the Excel window width it displays an additional icon at the end More Controls Click the More Controls icon and the hidden QAT icons appear in a pop up window Adding New Commands to the QAT You can add a new command to the QAT in three ways e Click the QAT drop down control which is located on the right side of the QAT see Figure 10 1 The list contains several commonly used commands Select a command from the list and Excel adds it to your QAT e Right click any control on the Ribbon and choose Add To Quick Access Toolbar The control is added to your QAT after the last control e Use the Customize tab of the Excel Options dialog box A quick way to access this dialog box is to right click any Ribbon control and choose Customize Quick Access Toolbar m ld u 6 Book2 Microsoft E 23 pa m ca i Howe Insert Customize Quick Access Toolbar f Review Vi ner F General X wee 4 gt eGo KO Paste g Bo to DeliLv save aT 0 gt 0 Calibri Cli
51. olbar below the Ribbon Figure 10 2 Use the Customization tab in the Excel Options dialog box to customize the OAT Some of the items in the drop down control are described in this list e Popular Commands Displays commands that Excel users commonly use e Commands Not In The Ribbon Displays a list of commands that you cannot access from the Ribbon e All Commands Displays a complete list of Excel commands e Macros Displays a list of all available macros e Office Menu Displays the commands available on the Office menu e Home Tab Displays all commands that are available when the Home tab is active In addition the drop down list contains an item for every other tab To add an item to your QAT select it from the list on the right and click Add Notice that some commands in the list on the left display icons The icon tells you what form the com mand will take a drop down list a split button an edit control or a Ribbon group If the command doesn t display an icon it s a button control If you add a macro to your QAT you can click the Modify button to change the text and choose a different icon for the macro The only time you ever need to use the Customize tab of the Excel Options dialog box is when you want to add a command that s not on the Ribbon or add a command that executes a macro In all other situations it s much easier to locate the command on the Ribbon right click the command and choose Add To Q
52. ont Size File conversion and compatibility k management Show Table of Contents Worksheet and Excel table basics Formula and name basics Not On Top BENEA rites ing sorting and conditionally formatting Reset Toolbar Summarizing consoldating and outlining data Validating data y Importing data PivotTable reports and PivotChart reports mi What f analysis Working with XML Charts Security and privacy Macros Excel and the Web Automation and programmabitty Activating Excel Customizing Working in a different la Add ns Worleng with graphics Publishing to SharePoint Server Excel Services Collaboration v Excel Help D Offline Figure 24 3 Configuring the Help window toolbar If you re a VBA programmer you can write a macro to display a particular help topic To do so you need to know the topic ID Locate the Help topic that you want to display and then right click and choose Copy Topic ID For example the topic ID for the topic Overview of Formulas is HP10081865 The following VBA procedure when executed displays that Help topic Sub ShowFormulaOverview Application Assistance ShowHelp HP10081865 End Sub NOTE Unfortunately displaying a Help topic by using VBA works only if the user s Help system is in Offline mode If the user s Help system is connected to Office Online attempting to display a Help topic shows only the opening screen Unfortunately VBA has no way to determine which mode is being used
53. or right see Figure 7 1 M 4 Sheetl Sheet2 Sheet3 Sheet4 Sheet5 R Figure 7 1 Use these controls to scroll the sheet tabs to the left or right You can also reduce the width of the horizontal scroll bar to reveal more sheet tabs Just click the vertical bar on the left side of the scroll bar and drag it to the right Another way to activate a sheet is to right click the tab scroll controls This action displays a pop up list of sheet names see Figure 7 2 Just click a name and you re there If not all the sheet names are listed click the More Sheets item at the bottom and a dialog box that lists the sheet names appears Just double click a sheet name to activate that sheet Figure 7 2 Right clicking the tab scroll controls displays a list of sheet names Tip 7 Navigating Sheets in a Workbook In many cases the most efficient way to activate a different sheet is to avoid the mouse and use the keyboard Surprisingly many users don t know about two useful keyboard commands e Ctrl PgDn Activates the next sheet e Ctrl PgUp Activates the previous sheet I use these keys 90 percent of the time but then again I avoid using a mouse whenever I can Hed Tip 8 Resetting the Used Area of a Worksheet Resetting the Used Area of a Worksheet When you press Ctrl End Excel activates the lower right cell in the used area of the work sheet In some cases you find that the low
54. ou must enter the range address manually Tip 22 Limiting the Usable Area in a Worksheet c D E a G H l J K L Mi 1 T 2 Properties tJ Sheett Worksheet z 4 Alphabetic Categorized Name 6 January June Sales by Region DisplayPageBreaks DisplayRightToLeft 7 __ Region1 Region2 Total Enabeautariter 8 January 98 832 67 989 166 821 EnableCalculaton 9 February 94 302 74 094 168 396 EnableFormatConditionsCalculation Tr EnableQutiining s 10 March 101 305 88 406 189 711 SS 11 April 99 334 75 321 174 655 EnabieSelecton 0 xNoRestrictions 12 May 114 565 65 923 180 488 Name Sheet 13 June 108 323 60 332 168 655 a we 1 xSheetiisible 15 V 15 s pi 18 19 20 21 22 i 4 gt gt Sheeti Sheet Sheet3 3 u 2 Figure 22 2 Use the Properties window to control some properties of the worksheet After performing these steps you find that you cannot activate any cell outside the speci fied range Also some commands no longer work For example you cannot select entire rows and columns Note that the scroll area is limited to a single contiguous range of cells There s a problem The ScrollArea property isn t persistent In other words if you save your file close it and then open it again the ScrollArea is reset and you re free to select any cell you like One solution is to write a simple VBA macro that is executed when the workbook is opened To add this type of mac
55. pboard a Forj E mail Pa Number Al Quick Print Print Preview ls gt o O Spelling l vV Undo vV Redo Sort Ascending Sort Descending More Commands WwwaN DW WN Re Show Below the Ribbon Minimize the Ribbon Figure 10 1 The QAT drop down menu is one way to add a new command to the OAT Figure 10 2 shows the Customize tab of the Excel Options dialog box The left side of the dialog box displays a list of Excel commands and the right side shows the commands that are now on the QAT Above the command list on the left is a drop down control that lets you filter the list Select an item for the drop down list and the list displays only the com mands for that item Tip 10 Customizing Your Quick Access Toolbar Excel Options Axi Popular a tal Customize the Quick Access Toolbar Formulas m Choose commands from G Customize Quick Access Toolbar Roofing Popular Commands v For ll documents default ave Advenced E lt Separator gt ld Save i Calculate Now Undo d Customize TJ Calculate Sheet Redo gt 3 Bh Create Chart UO Repeat Add ins 3H Custom Sort Trust t FT Datasheet Formatting Delete Cells Add gt gt saan W Delete Sheet Columns y Delete Sheet Rows w G2 E mail a a Insert Cells Insert Hyperlink La Insert Picture from File GA Insert PivotTable T Insert Sheet Columns Za Insert Sheet Rows v Reset C Show Quick Access To
56. rformance is suffering To hide all objects in the workbook use the For Objects Show options located in the Display Options for This Workbook section of the Advanced tab in the Excel Options dialog box e Page Breaks After you print or preview a document Excel displays dotted line page breaks in the worksheet You can turn them off if you like Use the Show Page Breaks check box in the Display Options for This Worksheet section of the Advanced tab in the Excel Options dialog box e Zero Values Sometimes you may prefer to hide all zero cells and show a blank cell instead Control this option by using the Show a Zero in Cells That Have Zero Value check box in the Display Options For This Worksheet section of the Advanced tab in the Excel Options dialog box e Scroll Bars You can hide the horizontal and vertical scroll bars for a workbook Go to the Display Options for This Workbook section on the Advanced tab of the Excel Options dialog box and use the two check boxes labeled Show Horizontal Scroll Bar and Show Vertical Scroll Bar Sheet Tabs To hide the sheet tab go to the Display Options for This Workbook section on the Advanced tab of the Excel Options dialog box and remove the check mark from the Show Sheet Tabs check box With no sheet tabs you can use Ctrl PgUp and Ctrl PgDn to activate a different sheet A Setting That Requires a Macro Turning off the display of one user interface element now requires a macro The status b
57. rl 0 that s a zero To hide one or more rows use any of these methods e Select a cell in the row or rows to be hidden Then choose Home Cells Format gt Hide amp Unhide Hide Rows e Select entire rows and then right click and choose Hide from the shortcut menu e Select a cell in the row or rows to be hidden and press Ctrl 9 Unhiding That which is hidden also needs to be unhidden To unhide one or more hidden columns use any of these techniques e Select a range that consists of cells to the left and to the right of the hidden columns Then choose Home Cells gt Format gt Hide amp Unhide Unhide Columns e Select entire columns to the left and to the right of the hidden columns and then right click and choose Unhide from the shortcut menu Tip 16 Hiding Columns or Rows e Select a range that consists of cells to the left and to the right of the hidden columns and press Ctrl Shift 0 that s a zero To unhide one or more hidden rows use any of these methods e Select a range that consists of cells above and below the hidden rows Then choose Home Cells gt Format gt Hide amp Unhide gt Unhide Rows e Select entire rows above and below the hidden rows and then right click and choose Unhide from the shortcut menu e Select a range that consists of cells above and below the hidden rows and press Ctrl Shift 9 Hed Tip 17 Hiding Cell Contents Hiding Cell Contents
58. ro follow these instructions Make sure that the workbook window is not maximized One way to do this is to press Ctrl F5 Right click the workbook s title bar and choose View Code from the shortcut menu This action displays the ThisWorkbook code module for the workbook Enter the following VBA code in the ThisWorkbook code module Private Sub Workbook_Open Worksheets Sheet1 ScrollArea C6 F13 End Sub Press Alt F11 to return to Excel Save the workbook close it and reopen it If your workbook has an XLSX extension you need to save it as a macro enabled workbook with an XLSM extension When the workbook is opened the Workbook_Open procedure is executed automatically and the ScrollArea property is set Tip 22 Limiting the Usable Area in a Worksheet CAUTION This method is by no means a foolproof way to prevent users from accessing parts of a workbook Nothing can prevent a savvy user from using the Properties window to delete the contents of the ScrollArea field Or when the workbook is open the user can choose to disable macros for the workbook Another way to bypass the Workbook_Open macro is to press Shift while the file opens Using Worksheet Protection The second method of limiting the usable area of a worksheet relies on unlocking cells and protecting the workbook 1 Select all cells that you want to be accessible They can be single cells or any number of ranges 2 Press Ctrl 1 to display
59. s the down arrow key to enter the Ribbon Then use the left and right arrow keys to scroll through the Ribbon commands When you reach the command you need press Enter to execute it This method isn t as efficient as using keytips but it s an easy mouse free way to take a quick look at the available commands Hed Tip 12 Customizing the Default Workbook Customizing the Default Workbook When you create a new Excel workbook you get a standard default workbook What if you don t like that workbook For example you may prefer a workbook with only one work sheet Or maybe you don t like the default font or font size Perhaps you prefer to have the gridlines hidden in your worksheets Or maybe you have a standard header that you always use on printed pages As it turns out Excel gives you quite a bit of control in this area It s relatively simple to create an entirely different default workbook The trick is creating a custom template file named book x1tx and then saving that file to the proper location on your hard drive NOTE 7 If all you care about is changing the number of worksheets in a new workbook that change is very easy to make and a template is not required Choose Office Excel Options and click the Popular tab Then change the setting for the option labeled Include This Many Sheets always keep it set to 1 After all can easily add more sheets if needed To create a new default workbook template
60. selected item cell range or chart element for example e Right click certain items in Excel to take advantage of the mini Toolbar displayed above the shortcut menu see Figure 2 2 This toolbar contains formatting tools that may save you a trip to the Ribbon When I started using Excel 2007 I just ignored the mini Toolbar but I soon realized that it s a very handy tool e Don t ignore the Quick Access Toolbar QAT This part of the new user interface is the only interface element that you the user can customize Check out Tip 10 to find out how easy it is to customize it e Finally keep in mind that Excel has additional commands that aren t even on the Ribbon If you come up empty handed after performing an exhaustive Ribbon search for the old text to speech commands for example don t despair You need to add those commands yourself by customizing the QAT see Tip 10 Calibri Ju F e Bl amp H A Aa 30 B amp Cut Ga Copy Insert Delete Figure 2 2 The mini Toolbar is easy to ignore but it can save lots of trips to the Ribbon Tip 3 Selecting Cells Efficiently Selecting Cells Efficiently Many Excel users think that the only way to select a range of cells is to drag over the cells with the mouse Although selecting cells with a mouse works it s rarely the most efficient way to accomplish the task The answer of course is to use your keyboard to select ranges Selecting a
61. such as SmartArt shapes and charts You can t customize theme effects If you customize a theme by using different fonts or colors you can save the new theme by choosing Page Layout gt Themes Save Current Theme Your customized themes appear in the theme list in the Custom category Other Office 2007 applications such as Word and PowerPoint can use these theme files Tip 14 Changing the Sheet Tab Appearance Changing the Sheet Tab Appearance Many users don t realize it but they can change the appearance of the sheet tabs displayed in a workbook This tip describes how to change the size of the text and the color of the sheet tabs Changing the Sheet Tab Color If your workbook has many sheets you may find it helpful to color code the sheet tabs For example you may use red tabs for sheets that need to be checked and use green tabs for sheets that have already been checked To change the color of a sheet tab right click the tab and then select Tab Color from the shortcut menu You can then pick a background color from the palette To change the color of multiple sheet tabs at one time press Ctrl while you click the sheet tabs Then right click and change the color If you select a color from the Theme Colors section of the palette the tab color changes if you apply a different document theme see Tip 13 When the colored tab sheet is active the text appears underlined with that color When the sheet is not active
62. t Format Cells Ctrl pulas eee Calibri 11 ia 4a Copy Paste 5 ajer J Format Painter B Z U BE aA A Clipboard 4 Font E38 m faj A B C D E a 22 23 V 25 T Figure 5 2 After you add the Repeat button to the QAT a ToolTip describes the task if any that will be repeated NoTE Unfortunately repeating an operation is unreliable in Excel 2007 at least in the initial release In fact it s seriously broken In some situations you may find that pressing Ctrl Y has no effect when it should have an effect At other times you may find that pressing Ctrl Y repeats the command that you issued before the previous command Tip 6 Discovering Some Useful Shortcut Keys Discovering Some Useful Shortcut Keys Excel has no shortage of shortcut keys Your productivity is sure to increase if you take the time to learn the shortcut keys for commands that you use frequently In the following table I list some of the most useful shortcut keys This list is certainly not an exhaustive one it describes just the commands that I find most useful Shortcut F11 Alt F1 F5 Alt Shift F2 Shift F10 Ctrl F6 Ctrl PgUp Ctrl PgDn Alt Ctrl V Alt Ctrl B Ctrl C Ctrl D Ctrl F Ctrl H Ctrl I Ctrl N Ctrl R Ctrl S What You Can Do with It Create a new chart on a separate chart sheet of the default chart type by using data in the selected range Create a new embedded chart of the default chart type by
63. tain range within a worksheet For exam ple you may want to set up a worksheet so that only cells in a particular range can be acti vated or modified This tip describes two ways to accomplish this task by using the ScrollArea property and by using worksheet protection Setting the ScrollArea Property A worksheet s ScrollArea property determines which range is visible Figure 22 1 shows a worksheet The instructions that follow restrict the usable area of the worksheet to the range C6 F13 A B c D E F G H T 1 2 3 4 5 7 _ Region1 Region2 Total 4 8 January 98 832 67 989 166 821 9 February 94 302 74 094 168 396 10 March 101 305 88 406 189 711 11 April 99 334 75 321 174 655 12 May 114 565 65 923 180 488 13 June 108 323 60 332 168 655 14 15 16 17 44 gt Sheet1 Sheet2 Sheet3 1 A m Figure 22 1 You can restrict the usable area of a worksheet to a particular range Here s how to do it 1 First make sure that the Developer tab of the Ribbon is displayed By default this tab is not displayed To turn on the Developer tab access the Excel Options dialog box choose Office gt Excel Options click the Popular tab and select the Show Developer Tab in the Ribbon check box 2 Choose Developer Controls Properties to display the Properties window see Figure 22 2 3 In the Properties window enter C6 F13 in the ScrollArea field and press Enter You can t point to the range y
64. ter you specify Press F1 for more help Figure 24 1 A pop up Supertip describes each Ribbon command But the real Help content is found in the Help Viewer window The following notes help familiarize you with the Excel 2007 Help system The Excel 2007 Help system has a single entry point no more Help menu with various options To display Excel Help press F1 or click the round question mark icon below the Excel title bar e The Help content resides in two places on your hard drive and on a Microsoft Web server The lower right corner of the Help window displays the status in a button either Offline shows content only from your computer or Connected to Office Online shows content from the Web You can change the status by clicking the button and making your choice In some cases using the Online option gives you more up to date information e Connecting to Office Online displays additional content that s not available when you choose the Offline option Specifically you can search for templates or training materi als Figure 24 2 shows the option in the drop down Search button list Tip 24 Understanding the Excel Help System 2 Excel Help Es 22S BX Os B 2 Search Table of Contents Content from office Online z Sj hars new All Excel N 3 b Getting help g Excel Help rrstaling Arcessbiity Excel Templates Fie conversion and compatibility
65. that you enter a valid User 6 Code as defined in 7 the 13 May 07 revision 8 of the TPS Report 9 10 11 44 gt gt Sheet1 lt Mm 1 Figure 23 1 This pop up message was created by using Excel s Data Validation feature Tip 24 Understanding the Excel Help System 61 Understanding the Excel Help System With every new release of Office it seems that Microsoft revamps the Help system Office 2007 is no exception The new Help system officially known as the Help Viewer is radi cally different from the previous one and in fact uses completely different technology A new aspect of Office 2007 is its Supertip feature When you hover your mouse over a command on the Ribbon you see a pop up description of what that command does Figure 24 1 shows an example of a Supertip B Mi ft Exce eS esc Py View Developer O x A y asa y ka i s ao HE 4 E E Jya S El Ge me Ge Wy aad Filter ae Text to N Remove Data Consolidate What If Group Ungroup Subtotal 7 7 Advanced Columns Duplicates Validation Analysis 7 7 rt amp Filter Data Tools Outline i j Text to Columns i F H l J Separate the contents of one Excel N o P Q cell into separate columns For example you can separate a column of full names into separate first and last name columns In Word use this feature to convert the selected text into a table splitting the text into columns at each comma period or other charac
66. that all Microsoft Office applications had the same version number If you must share a workbook with someone using a version before Excel 2007 save the file as an XLS file by choosing the Excel 97 2003 Workbook option in the Save As dialog box Pay attention to the results of the Compatibility Checker which appears automatically when ever you save your file in this older format This useful dialog box shown in Figure 1 2 iden tifies potential problems when your workbook is used by someone who has an Excel version earlier than Excel 2007 Microsoft Office Excel Compatibility Checker The following features in this workbook are not supported by earlier versions of Excel These features may be lost or degraded when you save this workbook in an earlier file format Summary Number of occurrences Some formulas contain references to tables that are not 1 amp supported in the selected file format These references will p Find Hel be converted to cell references find Help Location Sheet1 This object will no longer be editable 1 Location Sheet1 Shapes Find Hel One or more functions in this workbook are not available in 1 earlier versions of Excel When recalculated in earlier Find Hel versions these functions will return a NAME error instead 2 TEP fy C Check compatibility when saving this workbook Figure 1 2 The Compatibility Checker helps identify potential compatibility problems You ca
67. the English Language Third Edition Copyright 1992 Houghton Mifflin Company Electronic version licensed from Lernout amp Hauspie Speech Products N V All rights reserved PDF Reference fourth edition Adobe Portable Document Format version 1 5 1985 2003 Adobe Systems Incorporated All nights reserved This product is licensed to John Walkenbach Product ID 81605 861 0024503 65243 View the Microsoft Software License Terms Warning This computer program is protected by copyright law and international treaties Unauthorized reproduction or distribution of this program or any portion of it may result in severe civil and criminal penalties and will be prosecuted to the maximum extent possible under the law System Info Tech Support Figure 1 1 This dialog box displays the Excel version So who cares which version of Excel you use Most of the time nobody cares As long as your version does what you want it to do the version makes no difference But if you share your workbooks with other users the version may be very important Tip 1 Understanding Excel Versions Suppose that you use Excel 2007 and you give a co worker who uses Excel 2000 a copy of a workbook If you happened to use a feature that was introduced in Excel 2002 Excel 2003 or Excel 2007 your co worker may not be able to work with your file in the way you intended In fact if you saved the file in one of the new Excel 2007 f
68. the Page Layout gt Themes group contains three other controls Colors Fonts and Effects You can use these controls to change just one of the three components of a theme For example if you like the Urban theme but prefer different fonts apply the Urban theme and then specify your preferred font set by using the Page Layout gt Themes Font control Tip 13 Using Document Themes Da pida ra arag ca E Home Insert Page Layout Formulas Data Revi c o a A Fonts DO Margins Orientation Size Print Breaks Background O effects 5 lee Aer re Built In i Aa Aa Aa manana Soa manm Beo mon Office Apex Aspect Civic 4 Aa Aa Aa Aa ee SB n gt m Sees a ma Concourse Equity Flow Foundry J Aa Aa Aa 5 mm mm umnu ici i Bae ma i Median Metro Module Opulent 4 Aa Aa Aa Aa sen u a mu j En nnmnnn j Oriel Origin Paper Solstice Aa Aa Aa Aa mmnnna oe Se5588 BEREE Technic Trek Urban Verve vf More Themes on Microsoft Office Online G3 Browse for Themes 7 bey Save Current Theme Figure 13 3 The built in Excel theme choices Each theme uses two fonts one for headers and one for the body and in some cases these two fonts are the same If none of the theme choices is suitable choose Page Layout gt Themes Font gt Create New Theme Fonts to speci
69. tions Making Special Range Selections As you use Excel you ll probably wonder how you can locate specific types of cells in your worksheets For example wouldn t it be handy to be able to locate every cell that contains a formula or perhaps all cells whose values depend on the current cell Excel provides an easy way to locate these and many other special types of cells The key to many types of special selections is the Go To Special dialog box Choose Home Find amp Select gt Go To Special to display the Go To Special dialog box as shown in Figure 4 1 Another way to open the Go To Special dialog box is to press F5 and then click the Special button in the Go To dialog box m Go To Special Select Comments O Row differences Constants Column differences O Formulas O Precedents O Dependents O Last cell O Blanks O Visible cells only O Current region O Conditional formats Current array O Data validation O Objects j Cancel Figure 4 1 Use the Go To Special dialog box to select specific types of cells After you make your choice in the Go To Special dialog box Excel selects the qualifying subset of cells in the current selection Usually this results in a multiple selection If no cells qualify Excel lets you know with the message No cells were found NOTE By If you bring up the Go To Special dialog box when only one cell is selected Excel bases its selection on the e
70. uick Access Toolbar Tip 10 Customizing Your Quick Access Toolbar Only you can decide which commands to put on your QAT In general if you find that you use a particular command frequently it should probably be on your QAT For example when I work on a chart I like to use the Chart Elements control to make it easy to select chart elements That control is located in the Chart Tools gt Format tab and the Chart Tools Layout tab but not in the Chart Tools Design tab Putting a copy of this control on my QAT saves me many keystrokes on every chart I work on Performing Other QAT Actions Here are some other things you can do with your QAT e Rearrange the QAT icons If you want to change the order of your QAT icons you can do so on the Customization tab of the Excel Options dialog box Select the command and then use the up and down arrow buttons on the right to move the icon e Remove QAT icons The easiest way to remove an icon from your QAT is to right click the icon and choose Remove from Quick Access Toolbar You can also use the Customization tab of the Excel Options dialog box Just select the command in the list on the right and click the Remove button e Reset the QAT If you want to return the QAT to its default state display the Customization tab in the Excel Options dialog box and click the Reset button All your customizations disappear and the QAT then displays its three default commands Tip 11 Accessing the
71. uick Access Toolbar which is on the left side of the Excel title bar by default e To get more screen real estate enter Hide the Ribbon mode You can hide the Ribbon by double clicking any of the tabs When you need to access a command just click the tab and the Ribbon comes back to life The Ribbon disappears again when you finish To leave this mode and return to normal just double click a tab You can also enable and disable Hide the Ribbon mode by pressing Ctrl F1 e Access the Ribbon from the keyboard Although the Ribbon appears to be mouse centric it s quite functional from the keyboard See Tip 11 for some keyboard access tips e Note that the appearance of the commands on the Ribbon varies depending on the width of the Excel window When the window is too narrow to display the entire Ribbon the commands adapt and may seem to be missing But the commands are still available Figure 2 1 shows the Home tab of the Ribbon when the Excel window is at three differ ent widths In the first view all controls are fully visible When the Excel window is made narrower some descriptive text disappears but the icons remain When the win dow is made very narrow some groups display a single icon However if you click the icon all the group commands are available to you e Take advantage of contextual tabs on the Ribbon In some cases the Ribbon displays new contextual tabs For example when you select a chart you see three new t
72. w button click the button to display a drop down list and select 2 xlSheetVeryHidden After performing these steps you find that you cannot unhide the sheet by using any Excel command In fact you cannot even unhide the sheet by using the Properties window The Properties window shows the properties of the active sheet When a sheet is hidden it s never the active sheet Tip 25 Making a Worksheet Very Hidden 65 Is the sheet is hidden forever Nope To make the very hidden sheet visible again you use a simple VBA macro The macro listed here unhides Sheet2 of the active workbook change the sheet name as appropriate Sub UnhideSheet Worksheets Sheet2 Visible True End Sub M CAUTION Making a worksheet very hidden is not a security feature Anyone who really wants to know what resides on a very hidden sheet can easily find out by using the UnhideSheet macro Hed Tip 26 Disabling Hyperlink Warnings Disabling Hyperlink Warnings As you probably know Excel lets you insert a hyperlink into a cell A hyperlink can acti vate a different sheet or a different workbook or it can execute an external program For example if a cell contains a hyperlink to an MP3 music file clicking the hyperlink plays the song on the default music player Hyperlinks are great until you start using them Unless the hyperlink goes to a location in the same workbook you see the warning message shown in Figure 26 1 If you
73. xt Stephen R Rosencrantz However there s a reasonably good chance that the search will fail The name may have been entered differently as Steve Rosencrantz or S R Rosencrantz for example It may have even been misspelled as Rosentcrantz The most efficient search for this name is to use a wildcard character and search for st rosen and then click the Find All button In addition to reducing the amount of text that you enter this search is practically guaranteed to locate the customer if the record is in your worksheet The search may also find some records that you aren t looking for but that s better than not finding anything The Find and Replace dialog box supports two wildcard characters e matches any single character e matches any number of characters Wildcard characters also work with values For example searching for 3 locates all cells that contain a value that begins with 3 Searching for 179 locates all three digit entries that begin with 1 and end with 9 Tip 19 Performing Inexact Searches NOTE To search for a question mark or an asterisk precede the character with a tilde character For example the following search string finds the text NONE NONE If you need to search for the tilde character use two tildes If your searches don t seem to be working correctly double check these three options which sometimes have a way of changing on their own e Match Case If this check box

Wiley John Walkenbach's Favorite Excel 2007 Tips and Tricks

Contents

Download Pdf Manuals

Related Search

Related Contents