Docstoc

Anatomy of Excel formatting

Document Sample
Anatomy of Excel formatting Powered By Docstoc
					Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Version 1.0 September 27, 2005

By Scott Lowe
Takeaway
Sometimes we take for granted what users know. While <b>Microsoft Excel</b> may be familiar to you, if you are on a <b>help desk</b> you will have to explain how the application works to users that fall within a vast range of skill level and knowledge. This series of Excel tutorials can make that training much simpler. Properly formatting a spreadsheet can bring a project home.

Table of Contents
EXCEL FORMATTING ............................................................................................................................................................2 THE EXCEL FORMATTING SERIES ............................................................................................................................................2 BORDERS .................................................................................................................................................................................2 The Borders button ...........................................................................................................................................................3 Draw the borders yourself (The Draw Borders method) .............................................................................................6 Border from the Format Cells window............................................................................................................................8 RESIZING ROWS AND COLUMNS ............................................................................................................................................10 Change width and height with the boundary controls................................................................................................10 Change width and height with the menu options........................................................................................................13 GETTING BETTER ...................................................................................................................................................................14 ADDITIONAL RESOURCES ................................................................................................................................................15 Version history.................................................................................................................................................................15 Tell us what you think .....................................................................................................................................................15

Page 1 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Excel formatting
In the last part in this Excel training series, you learned how to format the text in your Excel spreadsheet. For example, you learned how to boldface text, apply formatting such as dollar signs, and learned how to shade your cells with different colors. In this part of this series, you will learn how to manipulate other aspects of your Excel spreadsheet, including borders (lines), the size of the individual cells, and much more.

The Excel formatting series
Part 1 • Boldface, italicize and underline cell content • Change the size and font of your text • Apply a default Excel style (i.e. dollar, percent, etc) to cells • Use date and time formatting in your spreadsheet • Apply shading Part 2 • Apply borders • Resize rows • Resize columns Part 3 • Text formatting • Justify cell contents • Change the direction of the text in your spreadsheet • Word wrap text Part 4 • Automatically format cells based on their contents • Change the margins for your printed page • Add a header and footer to your printer spreadsheet

Borders
Excel is very commonly used to deal with tables of information, including budget reports, inventory information— anything, really, that is most useful formatted in a table with rows and columns defining the information layout. In most reports of this type, you need some way to be able to visually break up the information. If you have 100 lines of numbers on a sheet, even if they're in neat rows and columns, you'll end up killing your readers with eyestrain…and that's not good, particularly if you're using the spreadsheet information to back up a funding request! You can help your readers keep their eyes sharp, and make your spreadsheet much more attractive, through the use of borders. In Excel, the term "borders" doesn't mean a box around the outside of your sheet. It refers to any line you place on any side of any cell – the border for that cell. Note that borders can be on top of or below a cell range, or to the left- or right-hand side of a cell range. You can even run borders diagonally through cells.

Page 2 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Figure A

The area highlighted in green is a cell range for which I told Excel to add lines for all borders – or around each individual cell in the selection. There are a number of ways you can add a border to a cell, or to a range of cells. For each method (with the exception of the "Draw Borders" method), you first need to select the cell or range of cells to which you would like to apply a border.

The Borders button
The quickest way to add borders is to use the Borders button on the Formatting Toolbar. Figure B

Look before you click! The Borders button remembers your last border choice. The Borders button is broken up into two parts: the button part, and then the little down arrow immediately to the right of the button. If you just click the button, whatever is showing on the button will be applied to your cell selection. Look very closely at Figure B above and notice that the button part has a black line across the bottom and the rest of the grid is made up of dotted line. If you select cells and click this button, your selection will get a solid line across the bottom. If you want more variety in your selection—perhaps you'd like a line across the top and bottom—click the down arrow to the right. You get the options shown below in Figure C.

Page 3 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Figure C

Use one of these twelve options to apply a border to your cell range. If you pick a button with multiple borders on it (for example, if you select the button with all of the borders solid), your selection will get all of the corresponding borders. If you want to remove all borders, choose the button at the top left. Note that some buttons include double-underlines (useful for you accountants out there), and some include thicker borders, including the one at the bottom right, which would place a thick black line completely around your selection. Figure D shows you some border examples created using the Borders button's drop down arrow.

Excel has twelve standard border options.

Page 4 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Figure D

The areas highlighted in green are the selection area for the chosen borders type. I added red boxes to help you actually see the lines created by the Borders button. I did not add this to the last example, lest it become a sea of red.

Page 5 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Draw the borders yourself (The Draw Borders method)
Break out a marker and draw the borders right on your screen—okay, maybe not. But Excel really does provide you with a tool that you can use to draw your own borders with your mouse. If you need to put a whole lot of different borders on your spreadsheet, this method is a heck of a lot easier than individually selecting each cell range and then applying the right border type. You've already seen how to get to the Draw Borders feature. Click the down arrow on the Borders button and choose the Draw Borders option at the bottom of the resulting window. Another toolbar—the Draw Borders toolbar—appears on your spreadsheet, with four options with which you should become familiar. By default, as soon as you see this toolbar appear, you can start drawing lines. To do so, use your mouse and hold down the left mouse button while you drag a line across your spreadsheet. When your line is done, release the mouse button. You can draw a box, too. You can even draw a diagonal line through an individual cell in your spreadsheet. Experiment a little and you'll quickly get used to this tool; but back to the Draw Borders toolbar. The first button on the Draw Borders toolbar (Figure E) provides a way for you to switch between drawing just outside borders around your selection and drawing borders that are inclusive of the interior cell borders in your selection. This "Draw Border Grid" option draws both a border and the interior grid, and is akin to choose the "All Borders" option from the Borders drop down menu. If you've made a mistake while you're drawing your borders the next button, the eraser, is your best friend. As implied, rather than drawing lines, the erase removes lines. The eraser has no other options. The next option on the Draw Borders toolbar provides a way for you to change the type of line you want to draw. The default line isn't always what you need. Perhaps you need a double line, a thicker line, or a dotted line, for example. To get these options, click the down arrow next to the line type box and choose the type you want. Finally, the last button—the line color button. As its name implies, this button provides a way for you to change the color of the lines you're drawing. Click the button to get Excel's standard color box. All of these Draw Borders toolbar options are shown below in Figure E.

Page 6 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Figure E

The Draw Border toolbar provides an easy way to change your line type.

Page 7 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Border from the Format Cells window
Along with a multitude of other formatting options, the Format Cells window also provides you with a place to manage borders in your spreadsheet. Figure F gives you a look at the border formatting options available in this dialog box, accessible from Format | Cells | Border in Excel. Make sure you've selected the range of cells to which you want to apply borders before you go here. Figure F

The Border tab in the Format Cells dialog box. This box can be a little confusing, even for veterans. I'll go over each major section of the window in detail in the order you should make your selection.

Page 8 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

1. Line: From this section, you can make two decisions: What style your line will use and what color the line will be. The line style is very visible. To change the color, though, you need to click the down arrow under the Color heading. I highly recommend that you make selection in this section first. It means that you won't have to redo your line location selections later on. 2. Presets: These three buttons—None, Outline, Inside—provide you with a quick way to apply (or remove) borders to and from specific portions of your selection. If you want to just get rid of the borders, click the None button. To apply an outside border, click the Outline button. To add the interior grid, click the Inside button. When you're done, click OK. You don't have to use the presets, though. In some cases, they won't meet your needs. In these cases, use the Border section. 3. Border: The Border section consists of eight buttons and a preview box. From this section, select the line style you'd like to add to your spreadsheet selection. Each button is labeled making it easy for you to pick the border you want. You can also click directly in the preview box in the specific location you'd like a line, but this can sometimes require deft mousing ability. It's usually easier to just use the buttons. Note that you don't have to limit yourself to a single line style and color. In fact, you can apply a variety of line styles and colors to your selection with a single trip to Format | Cells | Borders. You see, as you apply your selections to the Borders section, they stick. For example, if you apply a black solid line as the top border, you can then go and choose a completely different style and color and then apply that choice as the bottom border. An example is shown below in Figure G. Figure G

Smorgasbord of colors
Page 9 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

In real life, you probably wouldn't choose this smorgasbord of colors, but it works for an example to show you that you can customize borders to your heart's content. That's it for borders.

Resizing rows and columns
What do you think of the spreadsheet snippet shown in Figure H? Figure H

A spreadsheet snippet with an automobile inventory Personally, I think that this spreadsheet is pretty hard to read. You can use some of the techniques you've learned thus far to clean it up (for example, borders and shading), but one thing is clear: The fact that you can't read all of the text in column E makes this spreadsheet pretty useless. The solution: Make columns wider so that the text fits and is readable. Likewise, you can make your rows taller in cases where you need more space. For instance, if you have a larger font in one of your rows, you might need that row to be a little taller. Or, if you have multiple lines of text in a single row, a little more height to that row might be in order. As with most things in Excel, there are a couple ways you can go about making columns wider and rows taller.

Change width and height with the boundary controls
The quickest way to change the width or height of a cell is to use that column's or row's boundary control, which is another name for the line separating columns and rows. Take a look at your Excel sheet and look between columns A and B. The line between is there for two reasons: (1) as a visual separator between columns; and (2) to provide you with a quick way to make adjustments to the cell size. If you want to make a column wider, click and hold on the separator right after that column's identifier (i.e. the letter 'A') and drag it to the right. As you hold down your mouse button on the separator, Excel gives you an indicator telling you how wide the column is. If you want to make a row taller, use the separator immediately below the row number. Examples of both are shown in Figure I.
Page 10 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Figure I

The row and column boundaries let you make width and height adjustments to portions of your spreadsheet.

Page 11 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Excel also provides you with a really quick way to make a column wide enough to fit all of its text. Go to the column separator immediately after the column heading and double-click the separator. Excel will automatically look at your column and make it wide enough to fit the widest text. The same is true for row height as well. Figure J

In this example, I double-clicked the column separators for columns A, D, and E.

Page 12 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Change width and height with the menu options
Everything in Excel can be somehow controlled from the menu and column width and row heights are included in this. To change the height of a column, go to Format | Column | Width. Likewise, to change row height, go to Format | Row | Height. A default Excel workbook column is 8.43 wide and a default row is 12.75 high. You might wonder why a width of 8.43 is much larger Figure K than a height of 12.75. Answer: The width is specified by counting letters while the height is measured in 1/72ths of a inch. You can specify a column width of 0 to 255, which represents the number of characters that can be displayed in a cell that is formatted with the standard workbook font. If you specify a width of 0, the column is hidden from view. Row height can be in the range of 0 to 409, representing the height measurement in points (1 point is about 1/72 inch, and is the common method by which font size is measured). If you specify a row height of 0, the row is hidden from view. Personally, I find this method of changing width and height to be cumbersome. I don't necessarily want to have to figure out how tall a row needs to be. I normally use the mouse and the separators to change width. Only in cases where I definitely need to specify a specific width or height do I use the menu.

Enter new figures for the desired column width and row height.

Page 13 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Getting better
Between the tips in this article and in the previous article in this series, you can begin creating truly readable, attractive spreadsheets that convey information and not just data. As an example, using the described tools, your vehicle inventory can begin to take shape, as shown in this example (Figure L). Figure L

It's not quite art yet, but it's better than something completely plain!

Page 14 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

Anatomy of Excel formatting: Make better spreadsheet presentations – part 2

Additional resources
• • • • • • • Subscribe to TechRepublic's Downloads RSS Feed Sign up for TechRepublic's Downloads Weekly Update newsletter Sign up for TechRepublic's Microsoft Office Suite newsletter Check out all of TechRepublic's free newsletters Anatomy of an Excel Workbook: Build a foundation for success by mastering user fundamentals Anatomy of an Excel formula: Use built-in functions to calculate answers Anatomy of Excel formatting: Better spreadsheet presentation – part 1

Version history
Version: 1.0 Published: September 27, 2005

Tell us what you think
TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible. Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback. Please take a minute to drop us a line and tell us how well this download worked for you and offer your suggestions for improvement. Thanks! —The TechRepublic Downloads Team

Page 15 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html


				
john kimingi john kimingi ceo www.kimingi85.blogspot.com
About just a whizz kenyan boy