Shepherd University logo Information Technology
Home | About | Undergraduate | Graduate | Prospective | Current | Athletics | Alumni | Faculty/Staff   

I. T. Services

For Students

For Faculty/Staff

I. T. Services Policies and Procedures

Frequently Asked Questions

Projects and their current status

About Us and Contacts

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.

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.

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.

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.

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.

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
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.

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
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.

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.

Shepherd University | I. T. Services | P.O. Box 5000 | Shepherdstown, WV | 25443-5000
User Support Desk 304-876-5457 | Main Number 304-876-5245 | email: