|
|
|
|
Formatting your spreadsheet |
After you have input your data into your spreadsheet, some fine tuning
to your spreadsheet will most likely need to be done. This could consist of small adjustments or
big changes to the way the data looks.
At times a cell will end up filled with number signs after having a number transferred into it or
after calculating a formula. What does this mean? Your column is not wide enough for the number that
is to be displayed. Text will spill over into the next empty column, however, numbers will show up as
repeated number signs to alert you to widen the column.
WHY don’t numbers spill over the same as text? Because even text will only spill over
until it comes to a column with data in it and then it cuts the text off. If numbers were treated
the same way, numeral data would be cut off, displaying WRONG information. Instead of $1,234,090,321
being displayed, $1,234,090 could be all that is shown because it wasn’t able to spill over.
This display would, of course, be incorrect. If the creator of the spreadsheet wasn’t careful
to notice that the number wasn’t displaying correctly and didn’t widen the column, some
serious problems could result.
To Change Column Width
One way to adjust the width of columns is seen below. If you prefer to change column widths
using the column width dialog box, access the Format “Column” “Width”
menu and type the desired width in the text box for the selected columns.
Another method of changing your column width is by clicking and dragging on the right border
of the column indicator corresponding to the chosen column. Here’s how (assume we are
changing the width of column B):
Position your mouse pointer until it changes to a double arrow with vertical line in the middle
and place this mouse pointer on the right side of the “B” column indicator. See below.
Click and drag the mouse to the desired column width. When the mouse is clicked, dotted lines appear
to show the left and right borders of the column to make it easier in adjusting the width. Also when
you click, the actual column width is displayed above the mouse pointer. This display is shown while
you drag in case you are dragging to a particular column width. Of course, the column can be widened,
as well as, narrowed.
CHANGING ROW HEIGHT
A row’s height can also be changed. The process is exactly the same as changing the column
width, except you use the bottom border of the row indicator to use for clicking and dragging.
ADDING AND DELETING ROWS AND COLUMNS
Adding
The most important thing to know about adding rows or columns is where to position your cursor
for your addition to be in the correct place. When adding columns, click anywhere in the column
that would follow your addition. As seen in the below example, in order to add a column for
March’s figures, you begin the process to add a column by clicking anywhere in column D.
If a row needs to be added in the example between “Supplies” and “Durable
Equipment,” click the cursor anywhere in the “Durable Equipment” row since
you want this row to follow your addition.
After clicking either “Columns” or “Rows,” blank space is added to
your spreadsheet immediately proceeding where your cursor was positioned.
Deleting
Just as with adding columns and rows, it is equally as critical to have your cursor positioned
correctly when removing columns and/or rows from your spreadsheet.
To delete a column or row, click on any cell located in the column/row you want to remove.
Click on the Edit menu and click on “Delete.”
An additional (and quicker) way to delete a column or row is to click on its corresponding
indicator (see above) to highlight the row/column. Click into the Edit menu and click
on “Delete.” The row/column is automatically deleted without having to click through
any dialog boxes.
CENTERING TITLES ACROSS COLUMNS
In the below example, the title was typed in cell A1 and allowed to spill over into cell B1.
It is possible to have this title centered over a chosen number of columns or all of the columns
in the spreadsheet. Here’s how:
Make sure the title has been typed in the very first cell of the row in which you want it centered.
Highlight that cell and continue highlighting across all of the chosen columns.
 Click on the
“merge and center” button on the toolbar.
Your spreadsheet’s title is now centered across the columns you highlighted.
FORMATTING CELLS
The Format Cells box is another dialog box with different categories within it. The below
spreadsheet will be referred to as we learn about formatting cells.
Formatting Numbers
The “Number” tab is how dollar signs ($), commas (,), decimal points (.),
percentage signs (%), and zeros (0.00), among other choices, are added automatically to
numbers when they are entered. These are characters that are added without having to actually
type them in. The “Number” tab is also how numbers can be set as dates in various
formats, text, scientific figures and other formats.
In our example, we want cell range C5..C10 to be formatted for currency with a comma separator
and we don’t want any cents to be shown. Here are the steps:
After setting the category to currency with 0 decimal places and using the $ symbol,
your spreadsheet will now look like the example below. (The default for the display of
negative numbers was left in place.)
Now let’s format our data in column B in order for the numbers to have comma separators.
Access the “Format Cells” dialog box (Format menu/”Cells”).
Choose “Number.”
The spreadsheet now appears as below.
In order to become familiar with each of the number categories, click each one to select it
and read the description included. Also examine all the various options associated with the
category.
NOTE: When using the percentage category, input figures in decimal format.
Example: In order to have 97% display in a cell, input it as .97
Data Alignment
Accessing the “Alignment” tab of “Format Cells” can modify how text
appears in the cells of the worksheet. Alignment settings are used to create an attractive
worksheet, with easily read and followed data.
The following changes will be made to the spreadsheet using the “Alignment”
portion of the “Format Cells” dialog box.
Text wrapping
1. Before setting the cells to wrap text, first set the column width according
to the data within the column.
Example: set it according to the width of the entries in Column B.
2. Click and drag to highlight the cells that contain the text you want to wrap.
In this example, cells B5 and C5 should be highlighted.
3. Access the “Format Cells” dialog box and click the “Alignment” tab.
4. Under the terms “Text control” click to enter a “
ü
” by “Wrap text.”
5. If no additional alignment options or formatting options are desired, click the
“OK” button to close the dialog box.
6. In order for the titles to show properly you may need to resize the column further.
Centering within the cell
Horizontal
1. In order to have text horizontally centered (from side to side) within the cell’s
boundaries, highlight the cells you wish to have the text centered.
2. Access the “Format Cells” dialog box and click the “Alignment” tab.
3. Under the terms “Text alignment” and “Horizontal” click the dropdown
arrow to expose the list of options for horizontal placement.
4. Choose a format for horizontal centering by clicking on your choice.
5. Click “OK” to accept the change and to exit the dialog box if finished.
Vertical
In order to set vertical centering (centered from top to bottom) within the cell, highlight the
cells you wish to be affected if they are not already highlighted.
1. Access the “Format Cells” dialog box and click the “Alignment” tab.
2. Under the terms “Text alignment” and “Vertical:” click the dropdown
arrow to expose the list of options for vertical placement.
3. Choose a format for vertical centering by clicking on your choice.
4. Click “OK” to accept the change and exit the dialog box if finished.
Below is how the spreadsheet’s column headers appear assuming horizontal and verbal
text centering.
Shrinking characters
In the same way that “Wrap text” shows multiple lines of text in a cell, “Shrink
to fit” automatically reduces the font size until all of the cell’s data is displayed
without having to adjust the column width. This feature should only be used when a small amount of
text is not visible.
Merging cells:
In order to use merged cells, the cells must be merge BEFORE text is inserted. Otherwise,
text will be lost.
Here are the steps to give our spreadsheet a sub-heading over two columns:
Changing text orientation
Changing the orientation of text has the following effect on your spreadsheet:
Steps to changing text orientation:
1. Highlight the desired cell(s).
2. Access the “Format Cells” box.
3. Click either the preset orientation of up to down
OR
Choose a degree of orientation by clicking and dragging on the dial or by typing in the desired
degree of orientation.
4. Click “OK.”
Font Changes
Changing fonts in Excel is done exactly the same as in all Windows software. The only
difference in Excel is you will highlight the cell(s) you wish to change the font of.
However, you can also click onto the command line and highlight certain words or characters
to change the font of, bold, make italics, etc.
The following choices can be made after first highlighting the desired
cells you wish to be changed.
Adding Borders
A border can be added to an individual cell or a range of cells. As with most formatting,
you must first select the cell or cell range that is to have a border.
Customize the border by using the choices under the term “Border” or by
clicking in the preview box where you wish a borderline to be on a cell or cell range.
Clicking will automatically draw the borderline. Each of the preset choices can be used
alone or along with any of the other border choices. A sample of the combination of presets
will be shown in the preview box.
5. Should you want your border to be in color, click into the drop-down list under
“Color:” and click the desired color for your lines.
6. Click “OK.”
Adding Patterns and Getting Fancy with Color
In addition to having your font characters in color, color can also be added to the cell.
The color appears as background shading in the spreadsheet.
Steps to adding cell shading and/or patterns:
1. Highlight the cells that you wish to be in the same color with the same pattern, if you
choose to have one.
2. Access the “Format Cells” box, and click on “Patterns.”
3. For cell effects you have several choices:
a) Select only a color from the palette shown OR
b) Click the drop-down list where the word “Pattern” appears. From this box
(seen right) you can chose a cell shading color with a pattern OR a pattern by itself.
4. After choosing a color and/or pattern, click the “OK” button.
Protecting Your Spreadsheet
There will be times when you want to be sure that your spreadsheet be protected from
having a formula accidentally erased or changed or anything else altered about it. Should
this be the case, you want to “Lock” your spreadsheet.
From the “Format Cells” box, click on the “Protection” tab. If you
want your spreadsheet to be locked from alterations, click on “Locked.” Be sure
to notice the directions below the boxes. In order for the spreadsheet to be actually protected,
access the Tools menu and choose “Protection”.
Decide whether you will be protecting the worksheet or entire workbook.
The next decision Excel will ask is what is to be protected within the spreadsheet (see left).
A password can also be added to the spreadsheet. If a password is chosen, be sure that you record
it somewhere safe. Once a sheet is password protected, only the password will unlock it.
Choosing “Hidden” from the “Protection” box will hide formulas from being
shown on the command line. This helps ensure that formulas will not be accidentally tampered with
and will also ensure confidentiality. Be sure to select the cells with formulas that are to be
hidden BEFORE going through the steps to turn on hiding and protecting.
COPYING FORMATTING
It is possible to copy all of the formatting that you have done to a particular cell or cell
range, to another cell or cell range. Once you have all the formatting in place that is
appropriate, click on the “Format Painter” button from the button bar. As you
move the mouse pointer over the spreadsheet, a paintbrush now accompanies the mouse pointer.
Click and drag to highlight the cells that are to have the same formatting as the original cell.
Keep in mind that all formatting will be copied using the format painter. You cannot pick and
choose which formats to copy from one cell or cell range, to another. |
|
