AL RI MA D TE You can also make Excel more efficient by using the mouse wheel to zoom, automatically inserting decimal points, pinning Excel to the Windows 7 taskbar, creating binary workbooks, applying formatting across multiple worksheets, and using dialog box controls to input worksheet data. GH If you find yourself spending a major part of your day working with Excel, you can make those chores go faster — and so make your overall work life more productive — by making Excel as efficient as possible.
Customize the Quick Access Toolbar .................................................................................. 4 Customize the Ribbon............................................................................................................ 6 Export Ribbon Customizations to a File ............................................................................ 10 Configure Excel to Use the Mouse Wheel for Zooming .................................................
Customize the Quick Access Toolbar You can make Excel easier to use by customizing the Quick Access Toolbar to include the Excel commands you use most often. Because you launch Quick Access Toolbar buttons with a single click, adding your favorite commands to the toolbar saves you time. By default, the Quick Access Toolbar contains three buttons — Save, Undo, and Redo — but you can add any of hundreds of Excel commands.
Chapter 1: Making Excel More Efficient 5 Click the command you want to add. 6 Click Add. ● Excel adds the command. ● To remove a command, 5 you can click it and then click Remove. 6 7 Click OK. 7 ● Excel adds a button for the command to the Quick Access Toolbar. More Options! You can increase the space available to the Quick Access Toolbar by moving it below the Ribbon. This gives the toolbar the full width of the Excel window, so you can add many more buttons.
Customize the Ribbon You can improve your Excel productivity by customizing the Ribbon with extra commands that you use frequently. Keep in mind that you cannot modify any of the default tabs and groups in Excel, other than hiding tabs you do not use. Instead, you customize the Ribbon by adding a new group Display the Customize Ribbon Tab 1 Right-click any part of the Ribbon. 2 Click Customize the Ribbon. The Excel Options dialog box appears. ● Excel automatically displays the Customize Ribbon tab.
Chapter 1: Making Excel More Efficient Add a New Group 1 Click the tab you want to customize. 2 Click New Group. 1 ● Excel adds the group. 3 Click Rename. 2 3 The Rename dialog box appears. 4 Type a name for the group. 4 5 Click OK. Excel adds the new group to the tab. 5 More Options! You can get more space on the Ribbon and reduce clutter by removing any tabs you do not use.
Customize the Ribbon (continued) Although you will mostly prefer to add one or more custom groups to the default Excel tabs, this is not always convenient because it reduces the amount of space available to the other groups in the tab. This can cause the buttons to appear cluttered, making it harder to find the button you need. In such cases, a better customization method is to create your own tabs and populate them with custom groups and commands.
Chapter 1: Making Excel More Efficient Add a Command 1 Click the Choose Commands From dropdown arrow. 1 2 2 Click the command category you want to use. 3 Click the command you 4 5 want to add. 4 Click the custom group you want to use. 5 Click Add. ● Excel adds the command. 3 ● To remove a custom 6 command, click it and then click Remove. 6 Click OK. ● Excel adds the new tab or group, as well as the new command, to the Ribbon.
Export Ribbon Customizations to a File You can make it easy to apply Ribbon and Quick Access Toolbar customizations on another computer by exporting your own customizations to a file. Customizing the Ribbon or the Quick Access Toolbar is not a difficult process, but it can be time-consuming, particularly if you want to make a substantial number of changes. If you use Excel 2010 on another computer, it is likely that you will want to have the same 1 Right-click any part of the Ribbon.
Chapter 1: Making Excel More Efficient The File Save dialog box appears. 5 5 Choose a location for the customization file. 6 Type a name for the file. 7 Click Save. 6 7 Excel saves the customizations to the file. 8 Click OK. 8 Apply It! To apply the Ribbon and Quick Access Toolbar customizations on another computer running Excel 2010, you need to import the customization file that you exported by following the steps in this section.
Configure Excel to Use the Mouse Wheel for Zooming If you frequently zoom in or out of a worksheet, you can save time by configuring Excel to enable you to zoom using the wheel on your mouse. Zooming a worksheet is a useful technique. For example, you might want to zoom out of a large worksheet to get a sense of the overall structure of the worksheet data. Similarly, zooming in on a section of a worksheet enables you to focus on just that section. 1 Click the File tab button. 2 Click Options.
Chapter 1: Making Excel More Efficient 4 Click to select the Zoom on Roll with IntelliMouse option. Note: Although the option name specifies the Microsoft IntelliMouse, this option works with any mouse that comes with a standard scroll wheel. 4 5 Click OK. You can now zoom in and out of your Excel spreadsheets by turning the mouse wheel.
Move in a Different Direction When You Press Enter In certain cases, you can make your Excel data-entry chores more efficient by changing the direction that Excel moves the selection when you press Enter after you finish editing a cell. Generally, you enter the data vertically in a column of cells. Excel allows you to do this by automatically moving the selection down to the next cell when you press Enter.
Chapter 1: Making Excel More Efficient 4 Make sure that the After 4 Pressing Enter, Move Selection check box is selected. 5 5 Click the Direction drop-down arrow, and select the direction that you want Excel to move the selection after you press Enter. 6 Click OK. Excel now moves the selection in the direction you specified when you press Enter to confirm a cell entry.
Automatically Insert a Decimal Point itself, and then the digits to the right of the decimal point. In a long list of values, the extra step required to type the decimal point is a repetitive action that just slows you down. To speed up this kind of data entry, you can configure Excel to add the decimal point for you automatically. For example, if you tell Excel to automatically add two decimal places, then when you type a number such as 123456, Excel adds the value to the cell as 1234.56.
Chapter 1: Making Excel More Efficient 4 Click to select the Automatically Insert a Decimal Point check box. 4 5 Use the Places spin box 5 to specify the number of decimal places you want Excel to add automatically. 6 Click OK. 6 Excel now automatically inserts the number of decimal places you specified when you enter a numeric value into a cell. ● Excel displays Fixed Decimal in the status bar to remind you that it will automatically insert the decimal point.
Configure When Excel Warns You About Long Operations To avoid wasting time waiting for a long workbook recalculation to finish, you can configure Excel to warn you when an operation might take an excessively long time. In a typical worksheet with only a few formulas, the number of operations required to recalculate the worksheet might run into the dozens or hundreds, which Excel can handle instantly.
Chapter 1: Making Excel More Efficient 4 Make sure that the Alert the User When a Potentially Time Consuming Operation Occurs check box is selected. 5 Use the When This 4 5 Number of Cells (In Thousands) is Affected spin box to specify the threshold at which Excel displays the long operation warning. Note: The number in the spin box is shown in thousands. So, for example, if you enter 1,000 into the spin box, then the threshold is one million cells. 6 Click OK.
Pin Excel to the Windows 7 Taskbar You can quickly and easily launch Excel by pinning the Excel icon to the Windows 7 taskbar. If you use Excel every day, Windows offers some methods for starting the program that are easier than going through the menus. For example, you can pin the Excel icon to the Start menu so that the program is just two mouse clicks away. You do this by right-clicking the Excel icon and then clicking Pin to Start Menu.
Chapter 1: Making Excel More Efficient Pin a Program Using Your Mouse 1 Click Start. Note: If you see the Excel icon on the main Start menu, skip to step 4. 2 Click All Programs. Note: After you click All Programs, the name changes to Back. 3 Click Microsoft Office. 4 Click and drag the 3 Microsoft Excel 2010 icon to any empty section of the taskbar. 5 When you see the Pin to Taskbar banner, drop the icon. 2 1 4 5 ● Windows 7 adds the Excel icon to the taskbar.
Make a Workbook Faster by Saving it as Binary If you have a large or complex Excel workbook, you can make it open and save faster by converting it to the Excel binary file format. The standard file formats in Excel — Excel Workbook and Excel Macro-Enabled Workbook — are based on the OpenOffice XML Standard, where XML is short for eXtensible Markup Language. XML files are really just complex text files that Excel reads line-by-line when you open the file, and writes line-by-line when you save the file.
Chapter 1: Making Excel More Efficient 7 Click Excel Binary 7 Workbook. 8 Click Save. Excel saves the new file using the Excel Binary Workbook file format. 8 Did You Know? The Excel Binary Workbook file format is compatible with Excel 2010 and Excel 2007. If you want to improve file performance while maintaining compatibility with earlier versions of Excel, save your workbook using the Excel 97-2003 Workbook file format.
Open a New Window for a Workbook You can make a large spreadsheet easier to manage by creating a second window for the workbook. When you are building a spreadsheet, you often have to refer to existing sheet data. For example, when you construct a formula, you may need to refer to specific cells. Similarly, once your spreadsheet is working, you often need to monitor a cell value.
Chapter 1: Making Excel More Efficient The Arrange Windows dialog box appears. 5 6 5 Click to select the Horizontal option. ● If your worksheet has just 7 a few columns, you can click to select the Vertical option, instead. 8 6 Click to select the Windows of Active Workbook option. 7 Click OK. Excel arranges the workbook’s windows. 8 When you are done with the second window, click its Close button to return to using just the original workbook window.
Allow Only Certain Values in a Cell You can make Excel data entry more efficient by setting up data entry cells to accept only certain values. When you build a spreadsheet, you may find that some cells can only take a particular range of values. For example, an interest rate cell should take a decimal value between 0 and 1 (or a whole number between 0 and 100 if you have formatted the cell with the Percent number format).
Chapter 1: Making Excel More Efficient 8 Click the Input Message tab. 9 Make sure the Show Input 9 8 0 Message When Cell Is Selected check box is activated ( ). ! 0 Type a message title. ! Type the message you want to display. @ @ Click OK. ● When the cell is selected, the input message appears. More Options! It is often a good idea to also configure an error message that displays when the user tries to enter data outside of the range you have specified.
Apply Text or Formatting to Multiple Worksheets You can speed up the creation of spreadsheet models by applying text and formatting to multiple worksheets at once. In most workbooks, the worksheets are related in some way, but they generally have significantly different structures. However, in certain cases each worksheet uses an identical structure. For example, each worksheet might have the same overall title.
Chapter 1: Making Excel More Efficient 6 Add the text and other data you want to display on the grouped worksheets. 7 7 Apply the formatting that you want to use on the grouped worksheets. 6 8 Click the tab of a worksheet in the group. ● The data and formatting you added to the original worksheet also appear in the other worksheets in the group.
Quickly Display the Office Clipboard You can make the Office Clipboard easier to use and more efficient by configuring Office to display the Clipboard quickly. A clipboard is a memory location that is used to store data temporarily. Windows comes with a clipboard that stores data that you either cut or copy, and you can then paste the data to a document. The Windows Clipboard can only store one item at a time, which is not always convenient or useful.
Chapter 1: Making Excel More Efficient Display the Office Clipboard Using the Keyboard 1 Click the Home tab. 1 2 2 In the Clipboard group, click the dialog box launcher icon. The Office Clipboard task pane appears. 3 Click Options. 4 Click Show Office 4 3 Clipboard When Ctrl+C Pressed Twice. Excel now displays the Office Clipboard automatically whenever you press Ctrl+C twice in a row. More Options! By default, the Office Clipboard icon appears in the notification area of the Windows taskbar.
Use Dialog Box Controls to Input Data You can make worksheet data entry easier and more accurate by using dialog box controls such as check boxes, option buttons, lists, and spin boxes. If you are building a worksheet for data entry, your main concerns should be speed and accuracy. That is, you want users to be able to input data as quickly as possible, while still making the entered data as accurate as possible.
Chapter 1: Making Excel More Efficient ● Excel adds the control to 6 the worksheet. 6 If the control comes with 7 a text label, right-click the control. 7 Click Edit Text. Note: You can also double-click the text. Excel opens the label text for editing. 8 Type the name you want to use for the control. 8 9 Click outside the control. 9 Excel removes the selection handles from the control. Note: To select the control later on, hold down Ctrl and click the control.
Use Dialog Box Controls to Input Data (continued) Adding a form control to a worksheet does not do very much by itself. To make the control useful, you must link it to a worksheet cell. That way, when the user changes the state or value of the control, the resulting change is reflected in the linked cell. The value you see in the linked worksheet cell depends on the type of control. A check box inserts the value TRUE when it is checked, and FALSE when it is unchecked.
Chapter 1: Making Excel More Efficient 1 4 2 Populate a List Control with Values 1 Add the list items in a vertical or horizontal range on the worksheet. 2 Right-click the list box or combo box control. 3 Click Format Control. 3 6 5 The Format Object dialog box appears with the Control tab displayed. 4 Click inside the Input Range box. 5 Select the range that includes the list values. ● Excel inserts the range address in the Input Range box. 6 Click OK.
Check for Accessibility Problems If you have a workbook that will be used by people with disabilities, you should check that workbook for accessibility problems that could make it harder for the disabled to read and navigate the document. Spreadsheets that seem ordinary to most people can pose special challenges to people with disabilities. For example, a person with a visual impairment might have trouble seeing images, charts, form controls, and other non-text elements.
Chapter 1: Making Excel More Efficient 6 ● Excel displays the Accessibility Checker task pane. 6 Click an item in the Inspection Results section. ● Excel uses the Additional Information section to tell you why you should fix the problem and the steps required to fix it. Important! If you know your worksheet will be used by people with disabilities, you should build a new sheet with accessibility as your goal.