Excel
 Assignment 2 - Editing and Formatting Worksheets

Microsoft Excel provides tools that give you great flexibility in changing the appearance of your data and the structure of your worksheets. With a little effort, you can adjust spacing, alignment, and the look of type to make a worksheet easier to view, follow, and update; they’re easier for others to use, too.

You can change the way numbers are displayed so that their appearance corresponds with the type of numbers you are using.
You can also change the width of columns so that the data in the column fits appropriately, and you can increase the height of a particular row to call attention to the data in the row. As you work with worksheets, you’ll often find that you must move or copy data to other locations—a feature that is easy to perform in Excel. In fact, Excel provides numerous techniques you can use to copy or move data to a different location in a worksheet or even to a different worksheet.

Formatting Numbers
Most of the data that you use in Excel is numeric. This data includes financial figures, dates, fractions, percentages, and other information that usually appears with a mix of numerals and symbols. To be more meaningful, most numeric data needs some special touches—a dollar sign, a certain number of decimal places, or a percent sign, for example.
At a resort hotel, for example, the bookkeeper tracks room sales collected per week. She wants the sales amounts to look like monetary amounts; for example, $53.00, not just 53. In entering the sales amounts, she could type a dollar sign, followed by the number of dollars, followed by a decimal point, followed by the number of cents. But she knows that it’s much easier to enter the raw sales amounts and let Excel add these currency formats.
Start Excel, the standard workbook opens.  Now type 1234 in cell A1 and hit enter
Now select the cell you entered 1234 in and click on "Format" in the menu.  Then choose "Cells". The Format Cells Dialog opens listing the different was you data can be formatted  Choose something other than General in the Category list box.  You will notice the the sample box to the right changes as you choose different formats.

Opening an existing file.
Download the following document and save it to your pc.  Percent Sales Increase
Once it is saved go to the folder containing the document and open it with Excel
Format several numeric entries in a worksheet
In this exercise, you apply formats to raw numeric data that’s already been entered.
Make sure that cell A1 is currently selected, and on the Format menu, click Cells. The Format Cells dialog box appears.
Click the Number tab, if necessary, and in the Category list, click Date.  The Number tab is displayed, and the Type list is filled with options for formatting dates.
In the Type list, click 3/14/01, and click OK. The date in cell A1 changes to match the date format that you selected.
Select the range B3:F8. On the Format menu, click Cells, and then click Currency in the Category list. The Format Cells dialog box appears with formatting options for Currency (monetary values) shown. Notice that the default format for currency includes the dollar sign ($), a thousands comma separator, and two decimal places
Double-click in the Decimal places box, type 0, and press Enter. The selected cells are now in currency format, with no decimal places.
Select the range C10:F10. On the Format menu, click Cells, and then click Percentage in the Category list. The Format Cells dialog box appears with Percentage selected, and the dialog box shows the sample format for the first cell in the selected range. The only option you can change for the percentage format is the number of decimal places. You may have noticed the selected cells contain a formula for calculating a percentage increase in sales over the previous years. We will learn how to create formulas in a later exercise.
Click OK. The selected cells appear in percentage format, with two decimal places.
Click any blank cell in the worksheet. Your workbook should like much better now making it easier to read.
On the File menu, click Save.
Close Percent Sales Increase, and leave Excel open for the next exercise.
Opening an existing file.
Download the following document and save it to your pc.  Five Year Sales02
Once it is saved go to the folder containing the document and open it with Excel
Although a cell entry can include up to 32,000 characters, the default column width is only 8.43 characters. For some number formats, when you enter a value that won’t fit within the default column width, the number “spills over” into the next column. For other number formats, a number that won’t fit within a column is displayed as a series of pound signs (######), indicating that the number is too long for the current column width.
Resize columns and rows
In this exercise, you resize columns and rows. For this exercise, the width of columns in the practice file has been preset to 15 characters.
Select the range B4:F4. On the Format menu, point to Column, and then click Width. The Column Width dialog box appears, showing the current column width.
Type 13 in the Column Width text box, and click OK. The width of columns B through F decreases from 15 characters to 13 characters.
Click any cell. The range B4:F4 is no longer selected. Point to the bottom of the row selector for row 2. The mouse pointer changes to a double-headed arrow—the resize pointer.
Drag the row selector down until the row has a height of about 20.25 points (27 pixels, or screen picture elements). Use the ScreenTip to achieve the exact height. The height of row 2 increases
Point to the right edge of the column selector for column D. The mouse pointer changes to a double-headed arrow.
Double-click the right edge of the column selector for column D. The width of column D decreases to better fit the column contents.
Align cell contents horizontally and vertically
In this exercise, you set the vertical and horizontal alignment of cell contents. You also use the Undo and Redo buttons to see how changes can be undone and reapplied as desired.
Select the range B3:B6.
On the Format menu, click Cells. The Format Cells dialog box appears.
Click the Alignment tab. The Alignment tab appears.
Click the Horizontal down arrow, and click Left (Indent) in the list.
Click the Vertical down arrow, and view the list choices. Choices on the Vertical list let you align the data up and down inside the cell. Vertical alignment becomes more apparent when your rows are significantly taller than the data they contain.
Click the Vertical down arrow again to close the list without changing the vertical alignment.

 Click OK.
The contents of the selected cells are aligned to the left. On the Standard toolbar, click the Undo button. Excel returns the cells to their previous formatting.
On the Standard toolbar, click the Redo button. Excel reapplies the cell formatting.
On the Standard toolbar, click the Undo button. Excel undoes the cell formatting again.
Click the Save button on the Standard toolbar.
Close Five Year Sales02. Leave Excel open for the next part.
Opening an existing file.
Download the following document and save it to your pc.  Rentals
Once it is saved go to the folder containing the document and open it with Excel
Find text
In this exercise, you find a word in a worksheet.
On the Edit menu, click Find. The Find And Replace dialog box appears.
Click the Options button, if necessary, to expand the dialog box.
In the Find What text box, type Ski
Click the Search down arrow, and, if necessary, click By Rows. Excel will search across successive rows rather than down successive columns.
Click the Look In down arrow, and click Values. Excel will search cells for values rather than formulas.
Click Find Next. Excel selects the cell that contains the first occurrence of Ski.
Click Find Next. Excel selects the cell that contains the next occurrence of Ski.
Leave the Find And Replace dialog box open for the next part of this exercise.
Replace data in a worksheet
In this exercise, you find and replace the first occurrence of a search string and then replace every occurrence of a string in the worksheet with a different string.
In the Find And Replace dialog box, click the Replace tab.
In the Find What text box, replace Ski with .95, and press Tab.
The search string that you want to locate is entered, and the insertion point is positioned in the Replace With text box.
In the Replace With text box, type .99. The contents of the Replace With text box will be used to replace occurrences of the specified search string.
Click the Search down arrow, and click By Columns. Excel will search down successive columns rather than across successive rows.
Click Find Next. Excel locates the first occurrence of the search string .95.
Click Replace. Excel replaces the first occurrence of .95 with .99 and locates the next occurrence of the search string.
Click Replace All. Excel replaces all occurrences of .95 with .99—the values in the Price per Rental column.
Click OK to close the message box that tells you how many replacements were made.
Click Close. The Find And Replace dialog box is closed.
On the File menu, click Save As, type Rentals 2, and click Save.  The workbook is saved with the new name.
Keep this file open for the next exercise.
Inserting and Deleting Cells, Rows, and Columns
After setting up a worksheet, you might find that you need to insert a blank cell, column, or row to create space for entering additional information. For instance, if the sales manager wants to add new rental items in the Rentals worksheet, he’ll need to insert a new row for each new rental item. Alternatively, he might want to delete an existing cell, column, or row to eliminate unnecessary information. In the Rentals worksheet, column C is used to indicate the total number of rentals for each item in the year. The sales manager finds that this column is unnecessary and wants to delete it.
Insert and delete rows, columns, and cells
Click on Cell C#; C3 is now the active cell
On the Edit menu place the cursor over Clear, and then click on Contents.
On the Edit menu, click Delete. The Delete dialog box appears.
Click the Entire Column option, and click OK. The Year column, along with all of its contents, is deleted.
Select C1:F1. Four cells are selected.
On the Insert menu, click Cells. The Insert dialog box appears
Click the Shift Cells Right option, and click OK. Excel inserts four new cells and shifts the contents of existing cells (in the same row) to the right.
Select cells A10:A12. Three rows are selected.
On the Insert menu, click Rows.
Excel inserts three rows above what was row 10 (now row 13). You now have room to add three new rental items.
Click the Undo button twice. Excel removes the inserted rows and cells.
On the Standard toolbar, click the Save button. The workbook is saved with the current name.
Close Rentals 2. Keep Excel open for the next exercise.
Download the following document and save it to your pc.  Monthly Sales
Once it is saved go to the folder containing the document and open it with Excel
Copy, move, and clear data
In this exercise, you move and copy data to a new location, clear the formatting in a selected range, and clear the contents in a selected range.
Click cell A7, and on the Standard toolbar, click the Cut button. The contents of cell A7 are copied to the Windows Clipboard, and a flashing marquee appears around cell A7. The marquee indicates the contents that will be cut.
Click cell A8, and on the Standard toolbar, click the Paste button. The contents of the Windows Clipboard (from cell A7) are pasted in cell A8, and the marquee no longer appears around cell A7.
Select B3:D6, and on the Standard toolbar, click the Copy button. The contents of the selected cells are copied to the Windows Clipboard, and a flashing marquee appears around the selected cells, indicating what has been copied to the Windows Clipboard.
Click the Q2 sheet tab near the bottom of the Excel window. Excel displays the Q2 worksheet.
Click cell B4, and on the Standard toolbar, click the Paste button. The contents of the Windows Clipboard are copied to the Q2 worksheet, starting at the location of the active cell (B4).
On the Edit menu, point to Clear, and click Formats. The currency formatting is removed from the selected cells
On the Standard toolbar, click the Undo button. The currency formatting is reapplied to the selected cells.
On the Edit menu, point to Clear, and click Contents. The contents are removed from the selected cells, but the formats are still present and will be applied to any data entered in those cells.
Type 3444, and press Enter. Excel converts your entry to currency format.
On the File menu, click Save As, type Monthly Sales 2, and click Save. The workbook is saved with the new name.
Email Monthly Sales 2, Rentals 2, Percent Sales Increase, and Five Year Sales02 to me.

Back to the main page