Formatting Worksheets in Microsoft Excel
Cell Merging and AutoFill in Excel
In this section we will create a title for our worksheet. A title should be centered at the top of the page and this is easily accomplished using the cell merge operation.
Begin by opening Excel with a new blank workbook and select cell A1. Key ABC Employee Time Sheet into the selected cell and press the enter key. If the formatting bar is not visible in the toolbar section simply click view, toolbars, formatting so that toolbar will be visible.
Next, select cells A1:J1 (A1 through J1) and then locate the Merge and Center button located on the formatting toolbar. This button contains the letter “a” with an arrow on each side. Click this button and notice that ABC Employee Time Sheet is now centered across the top and those cells have been merged into one cell, A1.
Working with a series and adding labels in Excel
Our time sheet will assume that each of your employees works with different clients on different days.
Select cell B3 and key the word Monday into that cell. Click the green checkmark in your formula bar. Using the checkmark will keep the active cell in place, whereas hitting the enter key would jump to cell B4.
We will use AutoFill to finish the days of the week. Excel uses the AutoFill function to enter a series of data like numbers, days, and months. You will notice that since you used the checkmark function in the formula bar, the cell B3 is still selected with a dark border. AutoFill works a bit like drag and drop using a fill handle.
Position your mouse pointer by the lower right corner of the border. When it turns into a thin black cross simply click and hold your left mouse button. Drag the fill handle to cell F3. A screen tip will appear as you drag the fill handle over to cell F3 that displays the values that will be entered into the cells.
You can also use the fill series option, which is a little different than the AutoFill function.
Select cell B4 under the heading of Monday. Key the number 1 into the cell and then click the green checkmark. This time you will right-drag to cell F4 by holding the right mouse button down instead of the left mouse button. When you reach cell F4 a shortcut menu will appear. Click on fill series in the shortcut menu and the numbers 1 though 5 will appear in the corresponding cells. If you use the left mouse button to drag across the cells then it will just repeat the number 1 in each cell instead of using the series of numbers 1 to 5.
Now we will move to cell G3 and key in the word Total and then proceed to cell A3 and key in the word Client. Now we will list the clients that our company has in cells A5:A12.
Select cell A5 and enter Jazz Band, then hit the enter key to jump to cell A6 and continue through the list of clients;
Rustlers Restaurant
Show Dog
Extreme Realty
Lucky Leaf
Shorty’s
Jump Start
Nightingale
Enter the word Total in cell A13 and then save your work.
Modifying the size of rows and columns in Excel
You will notice that the clients you just entered will be overlapping into the adjacent cells. Excel is set to display eight characters by default in each cell.
One way to change the size of your cells is to use the AutoFit command. The AutoFit command will automatically resize the width of your column to the width of the widest cell needed. In this case, Rustlers Restaurant contains the most characters so the whole column would adjust itself according to that cell data.
Move your mouse pointer into the column heading section between columns A and B it will change into a horizontal resize pointer. Double-click the divider between columns A and B. The column widens to accommodate the longest entry, Rustlers Restaurant.
You can also use an exact width to change the size of a column. The number of characters a column will hold before it spills into the next column determines column width in Excel. These are called units. By default, Excel’s columns can contain 8 characters or 8.43 units. Select column heading B and right drag your mouse to heading G, release the mouse button and right-click on the selected columns to bring up the short cut menu. Select Column Width from the choices and key the number 12 into the option box and click OK.
Deselect the columns by selecting cell A1. Hover your mouse between rows 3 and 4 until it changes into the vertical resize pointer. Click and drag the row divider down until the screen tip displays 24.00. This will widen the row that contains the days of the week to make it visually appealing.
Fonts and Font Styles in Excel
Now that we’ve changed our row and column sizes we can change our fonts and font styles to make our worksheet more aesthetic. All text formatting can be done just like in Microsoft Word and other programs. Select your title in cell A1, change the font to Impact in the font pulldown selection box, change the size to 20, click on the red font color in the font color menu, and also select bold on the formatting toolbar.
Next we will select the cells B3:G3 and change those to a color of your choice like blue or purple. Do the same to cells A5:A12 for more practice.
Remember to only use three or four different font styles or colors so your work looks professional.
Changing the Alignment of Cell Contents in Excel
We can also align the contents of our cells to be horizontal or vertical. The default for text within a cell is left aligned and dates, time, and numbers are left aligned. In this section we want to align the contents of cells B3:G4 in the center so we will select those cells and then click the center alignment button in the formatting toolbar. Our day and date labels are now centered in their cells.
Sometimes you may want to rotate your text and give it a different look. We will select cells B3:G3, and then press the CTRL + 1 keys to open the format cells dialog box. Click on the Alignment tab and notice there is a spinner control box. We want to change the degree of our text to “60” so we would key that into the degrees box and click OK.
We now notice that our rows aren’t quite the size we need so we will resize those using the vertical resize method. Simply place your mouse pointer between row headings 3 and 4 and double click to automatically adjust the size.
Next we want to change the column width. Select columns B:G on the column headings, right-click the selected cells, then choose Column Width from the shortcut menu and key 7 into the box, and click OK. Deselect the cells and notice that our columns and rows look much neater and more precise.
Number Formats in Excel
Excel automatically attempts to identify numeric data as it is entered and chooses the best-suited format. If you were to enter 01/01/01, Excel would apply the default date setting and change it to 1/1/2001. If you prefer a different date format you would change this in the Format Cells dialog box. When the Format Cells dialog box appears choose the number tab at the top. You are then given several categories to choose from such as General, Number, Currency, Accounting, Date, Time, Percentage, etc. Select the appropriate format and click OK.
The other tabs in the Format Cells dialog box are Alignment, Font, Border, Patterns and Protection.
Cell Borders and Shading in Excel
Adding cell borders and shading to your spreadsheet application are used as visual effects to enhance your worksheets. Borders are used to separate columns and rows containing data while shading is used to highlight important data in cells. As you are working with a worksheet your screen will show the gridlines of the cells but when you print your worksheet the gridlines do not print by default. You can select all the cells to have borders or just some of them. You can select to have borders on the top and bottom of selected cells and none on the sides or vice versa. You may also choose to have a border completely incasing the cells. All of these options are available in the Format Cells dialog box under the Border tab.
Excel offers many formatting guidelines to enhance your work, provide visual breaks and give impact to your spreadsheet applications.
