UNIT II: Format Worksheets and Workbooks
1. Change Worksheet Tab Color:
1. Right-click on the sheet tab at the bottom of the workbook.
2. Select Tab Color.
3. Choose a color from the palette.
4. The tab color will change, though it appears as a small underline if the
sheet is active.
2. Rename a Worksheet:
1. Double-click on the sheet tab you want to rename.
2. Type the new name.
3. Press Enter to confirm the change.
4. Alternatively, right-click the tab and select Rename.
3. Change Worksheet Order:
1. Click and hold the sheet tab you want to move.
2. Drag it to the desired position among the other sheet tabs.
3. Release the mouse to place the sheet in the new order.
4. Insert and Delete Columns or Rows:
• Insert:
1. Select the column letter or row number next to where you want to
insert.
2. Right-click and choose Insert.
3. A new column or row will appear.
• Delete:
1. Select the column or row you wish to remove.
2. Right-click and choose Delete.
5. Change Workbook Themes:
1. Go to the Page Layout tab.
2. In the Themes group, click Themes.
3. Select a theme from the list.
4. The theme will apply new fonts, colors, and effects to your workbook.
6. Adjust Row Height and Column Width:
• Row Height:
1. Select the row(s) you want to resize.
2. Right-click and select Row Height.
3. Enter the desired height and click OK.
• Column Width:
1. Select the column(s) you want to resize.
2. Right-click and choose Column Width.
3. Enter the width and press OK.
• AutoFit:
o Double-click the border between column or row headers to
automatically adjust size.
7. Insert Headers and Footers:
1. Go to the Insert tab.
2. Click Text > Header & Footer.
3. The worksheet will switch to Page Layout View.
4. Type in the header or footer sections.
5. Click anywhere outside to save changes.
8. Hide or Unhide Worksheets:
• Hide a Worksheet:
1. Right-click the sheet tab.
2. Select Hide.
• Unhide a Worksheet:
1. Right-click any sheet tab.
2. Select Unhide.
3. Choose the sheet you want to show and click OK.
9. Hide or Unhide Columns and Rows:
• Hide:
1. Select the column(s) or row(s) you want to hide.
2. Right-click and choose Hide.
• Unhide:
1. Highlight the columns or rows adjacent to the hidden ones.
2. Right-click and choose Unhide.
10. Customize the Quick Access Toolbar:
1. Click the small drop-down arrow on the Quick Access Toolbar (top-left
of the window).
2. Select the commands you want to add (e.g., Save, Undo, Redo).
3. For more options, choose More Commands to open Excel Options.
4. Select your desired commands and click Add.
5. Click OK to save the changes.
Practice Activities:
Step 1: Creating a Workbook
1. Open Microsoft Excel.
2. Click File > New.
3. Select Blank Workbook to create a new workbook.
4. Save the workbook by clicking File > Save As, then choose a location
and file name.
Step 2: Adding a Worksheet to an Existing Workbook
1. Open the workbook where you want to add a worksheet.
2. Click the + icon next to the sheet tabs at the bottom to add a new
worksheet.
3. Alternatively, right-click on an existing sheet tab and select Insert >
Worksheet.
Step 3: Copying and Moving a Worksheet
To Copy a Worksheet:
1. Right-click on the sheet tab you want to copy.
2. Select Move or Copy.
3. Check the box for Create a Copy.
4. Choose where to place the copied sheet and click OK.
To Move a Worksheet:
1. Click and hold the sheet tab you want to move.
2. Drag it to the desired position.
Step 4: Creating a Basic Worksheet
1. Open a new or existing workbook.
2. In the worksheet area, click on a cell (e.g., A1) to select it.
3. Enter data (e.g., text, numbers, or dates) into the selected cell and press
Enter.
4. Use Tab to move to the next cell in the same row or Enter to move to the
next row.
Tips:
• Use the Home tab to format the text, numbers, and alignment of your
data.
• Adjust column width and row height by dragging the edges of the
column/row headers.
Step 5: Performing Calculations in an Excel Worksheet
1. Entering Basic Formulas:
o Click on the cell where you want the result to appear.
o Type the = sign to begin the formula.
o Enter the formula (e.g., =A1+B1 to add values in cells A1 and B1).
o Press Enter to display the result.
2. Using Functions:
o Common functions include:
▪ SUM: Adds a range of cells (e.g., =SUM(A1:A10)).
▪ AVERAGE: Calculates the average of a range (e.g.,
=AVERAGE(A1:A10)).
▪ IF: Performs logical tests (e.g., =IF(A1>10, "Yes",
"No")).
o Type the formula manually or use the Formulas tab to insert
functions.
3. AutoFill:
o Enter a formula in a cell.
o Drag the small square in the bottom-right corner of the cell (fill
handle) to copy the formula to adjacent cells.
4. Cell References:
o Relative Reference: Changes based on the cell’s position (e.g.,
A1).
o Absolute Reference: Remains constant using the $ symbol (e.g.,
$A$1).
o Mixed Reference: Combines relative and absolute (e.g., $A1 or
A$1).
Practice Exercises
Exercise 1: Basic Arithmetic Operations
1. Create a new worksheet.
2. In cells A1 to A5, enter the following numbers: 10, 20, 30, 40, 50.
3. In cells B1 to B5, enter: 5, 4, 3, 2, 1.
4. Perform the following calculations:
o Sum: In C1, type =A1+B1 and press Enter. Drag to fill C1 to C5.
o Subtraction: In D1, type =A1-B1 and press Enter. Drag to fill D1
to D5.
o Multiplication: In E1, type =A1*B1 and press Enter. Drag to fill
E1 to E5.
o Division: In F1, type =A1/B1 and press Enter. Drag to fill F1 to
F5.
5. Save the workbook.
Exercise 2: Formatting Worksheets and Workbooks
1. Add a new worksheet and rename it Practice Sheet.
2. Change the tab color to Blue.
3. Insert 2 new rows above row 1.
4. Adjust the column width of column A to 20.
5. Insert a header with the text Excel Practice.
6. Hide column B.
7. Unhide column B.
8. Customize the Quick Access Toolbar to include Save As and Print
Preview.
9. Save the workbook.
