AL Chapter 1 TE RI Spreadsheet Basics MA In This Chapter D ◆ Learning things about spreadsheets you don’t typically find in manuals and classes ◆ Fixing up odd behavior in your spreadsheets TE ◆ Setting Excel macro security GH ◆ Getting the most out of an Excel menu ◆ Understanding Excel templates RI ◆ Taming your Excel toolbars PY ◆ Alleviating window “pains” in Excel CO A Good Place to Begin Have you ever wanted to individually format different words in a sentence or phrase appearing in a
4 Part I: Escape in Under 30 Seconds Figure 1-1: Spreadsheet cells with individually formatted words Here is how you do it. Select the spreadsheet cell containing the text you want to format. In the Excel Formula Bar (see Figure 1-2), select the word(s) you want to change. Figure 1-2: Edit individually selected words in the Excel Formula Bar. With the text selected in the Formula Bar, click Format➪Cells and you will be presented with a dialog box like that shown in Figure 1-3.
Chapter 1: Spreadsheet Basics 5 Part I Figure 1-3: Format the selected word as you would regularly format a spreadsheet cell. My Spreadsheets Have Gone Haywire — Help!! Every now and then, it looks as though something with Excel is totally messed up. These “haywire” moments are typically caused by Excel having the wrong settings. In this section, I outline a few issues relating to settings and show some easy fixes. “When I enter the value 12, Excel turns it into 0.12, but when I enter 12.
6 Part I: Escape in Under 30 Seconds Figure 1-4: Disable (uncheck) the “Fixed decimal” setting. Figure 1-5: Uncheck the “R1C1 reference style” to get back the A1 style. NOTE There is a spreadsheet on your CD-ROM called ch01_02SwitchTool.xls. When you open this spreadsheet you will see two buttons labeled R1C1 and A1. Clicking the appropriate button will allow you to switch back and forth between the two reference styles without having to go through the Excel menus.
Chapter 1: Spreadsheet Basics 7 Understanding the R1C1 reference style I want to explain some features of using the R1C1 style. Several things are worth mentioning. • Absolute cell references don’t require any “$” symbols. They are just the row number and column number. For instance, $D$10 is the same thing as R10C4. • Relative cell references are shown with brackets around the respective row or column number offset.
8 Part I: Escape in Under 30 Seconds Figure 1-6: This spreadsheet is reversed (column labels all go in the wrong order and rows appear on the right instead of the left side). So, how does this situation come about and why would Excel allow you to do this? It’s very simple (see Figure 1-7). Excel is used in many countries and many languages. In some languages, the order of text flows from right to left, in contrast to English.
Chapter 1: Spreadsheet Basics 9 Part I Figure 1-7: You can adjust settings to go from right to left or from left to right. Excel allows you to set options that tell the software how it should behave at any time. To be able to set your options (such as the International setting), you need to have a spreadsheet already open. It doesn’t matter which one, and the settings you choose don’t apply to any specific spreadsheet.
10 Part I: Escape in Under 30 Seconds Figure 1-9: Options for New Workbook After you have a spreadsheet or workbook open, you should have no problem changing the options. Try it. If you are new to Excel, some of the terminology may be confusing. The sidebar “Excel workbooks, worksheets, and templates,” later in this chapter, may clarify a few points. “My Formula Bar disappeared.” I once downloaded a very interesting spreadsheet from the Department of Energy.
Chapter 1: Spreadsheet Basics 11 Macro Security Figure 1-11 shows the kind of warning error you will get if you open a spreadsheet containing a macro and your security settings in Excel are set to High. Figure 1-11: Warning message presented by Excel that tells you it disabled the spreadsheet macros NOTE Unlike the rest of Excel, which contains numbers and calculation formulas, macros contain programming code. Macros can extend the capability of a spreadsheet to do more than straight computations.
12 Part I: Escape in Under 30 Seconds Figure 1-12: Adjusting your Security Level setting to Medium Here is what this accomplishes. Anytime you open a spreadsheet that contains macro code, Excel will ask whether you want to enable or disable macros. This setting gives you maximum flexibility. Instead of unilaterally disabling macros, it allows you to decide whether to enable the macros at the time you open the spreadsheet file.
Chapter 1: Spreadsheet Basics 13 Part I Figure 1-13: Abbreviated menu with double arrow Here’s how to fix it: 1. Click the Customize feature from the Tools menu. The Customize window with its three tabs appears. 2. Click the tab labeled Options. 3. The second checkbox, Always Show Full Menus, is not selected. Click this checkbox to make sure that full menus are enabled. 4. Click the Close button to accept the changes you made.
14 Part I: Escape in Under 30 Seconds continued Excel templates are well suited for finely tuned spreadsheets that you will use repeatedly. A good example is a time sheet that could be distributed to a group of people. Perhaps, instead of using a template, you could commandeer an already populated spreadsheet and clean out the data.
Chapter 1: Spreadsheet Basics 15 Thankfully, you don’t have to search for the directory in which to save the templates. Excel takes care of that for you automatically. Standard location of Excel Templates you create Templates located in the General tab Your template will open as a regular .xls file, but when you save it, Excel will try to save it in the Templates folder and not the usual location where you keep your regular spreadsheet files.
16 Part I: Escape in Under 30 Seconds Toolbars and Toolbar Icons “My toolbar is missing some icons that it used to have! What happened?” Just underneath your Excel menu is a series of icons placed on a set of toolbars. Clicking each of these icons causes Excel to perform a particular task. There are benefits of using toolbar icons: They are very accessible (except when hidden!) and are often quicker than trying to do the equivalent through the Excel menu. Here is the problem and what you can do about it.
Chapter 1: Spreadsheet Basics 17 “A toolbar that I don’t need is on my screen. How do I make it go away?” The answer is easy if your toolbar happens to be free floating. Simply click the X at the top-right of your toolbar. However, toolbars that are nestled directly under the Excel menu do not have that X. For these, click Tools➪Customize and click the Toolbars tab. You will see a list of toolbars. The ones that are visible have a checkmark next to them. Uncheck the ones you no longer wish to see.
18 Part I: Escape in Under 30 Seconds Figure 1-17: Give your custom toolbar a name. The name can be any descriptive name of your choosing. For now, you can call it Group1. I use the Group1 toolbar that’s set up here throughout the book. You are free to create additional groups. You can also mix and match icons among the different groups. Click the Commands tab. You will notice a variety of categories, including File, Edit, View, and so forth. Click the Edit category.
Chapter 1: Spreadsheet Basics 19 sign (+) in it. While you have your mouse button held down, drag the icon onto your empty Group1 toolbar. When you drag the icon onto the Group1 toolbar, two things happen: 2. A vertical insertion point indicates where the icon will be positioned. When you release the mouse button inside the Group1 toolbar, you see the icon deposited there. For the Edit Category, add the toolbar commands for Paste Values and Clear Formatting.
20 Part I: Escape in Under 30 Seconds Sometimes you will want specific toolbar icons even though you can access the feature through one of the space-saving icons. The Paste Values and Paste Formatting are such icons. Being able to paste pure values that are devoid of formulas and formatting information is an important feature to have. Likewise, being able to paste formats will facilitate your ability to manage spreadsheet information.
Chapter 1: Spreadsheet Basics 21 Part I Figure 1-21: After you click the Freeze Pane icon, the confusing split-pane is gone and the spreadsheet scrolls naturally with a split screen. Text to Column is a feature that is particularly handy if you’re going to be working with data files that are provided from third-party sources, such as government-published information pulled off the Internet. Here’s the last bit of configuration and I’ll be done with toolbars.
22 Part I: Escape in Under 30 Seconds two rows. They can be shoved onto the second row, but there is not quite enough space to simultaneously display all of them on a straight horizontal line. I don’t know about you, but I don’t particularly like the idea of using second-class icons. If they’re out of sight, they’re out of mind.
