Introduction to Microsoft Excel

WHAT IS A SPREADSHEET

A spreadsheet is used to carry out everything from simple addition to complex financial and statistical analyses.  Spreadsheet programs can also help you create charts and graphs based on the data you’ve entered.

 

Brand Names of Spreadsheet Software

• Microsoft Excel
• Microsoft Works Spreadsheet
• Lotus 1-2-3

BASIC EXCEL SKILLS

Starting Up Microsoft Excel

1. Click on START, choose PROGRAMS and click on Microsoft Excel
2. You will see a white grid in front of you with rows of menus and icons at the top of the screen.

The Key Components of a Spreadsheet

1. Title Bar: The bar that displays the name of the spreadsheet you currently have open.
2. Name Box: The box that displays the address of the cell that you are presently on.
3. Formula Box: The bar where the contents of the cell are displayed.
4. Worksheet Tabs: These tabs allow you to switch from one worksheet to another. The standard spreadsheet contains three worksheets.

 

Name Box

A Cell

Entering Data into a Spreadsheet

1. To enter data into a cell, simply click into the cell and type.
2. ACTIVITY: 
1. Start in cell A1, and type “Day of the Week”.  Press Enter, or click your mouse into cell A2 and type “Monday”.  Continue down Column A until you’ve typed Monday through Sunday.
2. Click on cell B1 and type “Customers”.  Proceed to cells B2-B8 begin typing in the numbers: 20, 10, 30, 50, 60, 100, 70.

Entering a Formula into a Spreadsheet

1.  What is a formula?  It is a command that instructs Excel to carry out a calculation.  Addition, subtraction and averaging are all examples.

3. ACTIVITY:
1. In cell A9, type “Total”, then,
2. Move to cell B9 and type “=SUM(B2:B8)” and then press ENTER.  You should see a total appear in cell B9.

Calculating Four Basic Math Operations

Addition:

1.  Use the SUM command, which is    =SUM(beginning cell address:ending cell address) and press ENTER.  OR,

2. If the numbers are not adjacent to each other, you can use the “+” operator as follows:

      = first cell to be added + second cell to be added + third cell to be added, and so on.  Then press ENTER.

    

Subtraction

1.  Use the – operator     = first cell – second cell and then press ENTER

Multiplication

1.  Use the * operator      = first cell * second cell and then press ENTER

Division

1.  Use the / operator      = first cell / second cell and then press ENTER.

Making Changes to the Contents of a Spreadsheet Cell

ACTIVITY:  We made a mistake calculating Tuesday’s customers.  There were actually 16 customers, not 10!

1. Click on the cell whose contents you would like to change
2. See the present contents of the cell appear in the FORMULA Bar and change the data.
3. Notice that the TOTAL was automatically recalculated (It now reads 346 instead of 340!)

Saving Your Spreadsheet

1. Click on FILE and choose SAVE AS if you are saving the spreadsheet for the first time, otherwise, choose SAVE.
2. Choose what location you would like to save your spreadsheet in and click ok.

Printing a Spreadsheet

1. Click on File and choose PRINT. 
2. If you’d like to only print certain pages then use the “Print Range” section of the Print dialog box and choose which pages you would like to print. 

Fitting a Spreadsheet onto One Printed Page

1. If you want your spreadsheet printed on one page instead of spanning two pages, follow these steps:
2. Highlight all the cells of your spreadsheet then click on FILE and choose PRINT AREA. (in Excel 2007, you would select PAGE LAYOUT from the main menu, then select PRINT AREA)
3. Click on SET PRINT AREA and see that a dotted line appears around your spreadsheet cells.
4. Click on FILE and PRINT PREVIEW.
5. Click on SETUP and click on the radio button beside (under the heading SCALING) FIT TO 1 PAGE WIDE BY 1 PAGE TALL. (in Excel 2007, click the small arrow in the bottom right corner of the SCALE TO FIT shortcut box to access this option)

6.      Click on OK, Click on PRINT and Click OK.

INTERMEDIATE EXCEL SKILLS:  EDITING A SPREADSHEET

Inserting Columns

ACTIVITY:  The regional manager says we need to start tracking the number of visitors to our story each day.  She wants us to insert a new “Visitors” column BETWEEN the current “Days of the Week” column and the “Customers” column in order to track this data. 

1. Click into any cell within the column that will become the first column to the right of the new column.  Which column is that for us??
2. Now click on INSERT and choose COLUMN.  You will see a new column appear. (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT SHEET COLUMNS)

ACTIVITY:  Starting in B2, type the following number of visitors for each day:  30, 32, 45, 90, 65, 200, 130.

Inserting Rows

1. Click into any cell within the row that will become the first row below the new row.
2. Click on INSERT and choose ROW.  (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT SHEET ROWS)
3. You will see a new row appear.

Inserting Cells

1. Click into the cell where a new cell needs to be inserted. 
2. Click on INSERT and click on CELLS (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT CELLS)
3. Choose one of the four choices that appear:
1. SHIFT CELLS RIGHT means that when you insert the new cell, the existing cell will be shifted to the right.
2. SHIFT CELLS DOWN means that when you insert the new cell, the existing cell will be shifted down.
3. The other two choices, ENTIRE ROW, and ENTIRE column are alternative ways to inserting rows and columns.

Copy and Pasting

In Excel, you use the Copy and Paste feature when you want to duplicate a cell or block of cells in another location within the spreadsheet.  The original cell will remain in the original location, but a second copy is found at the new location.

ACTIVITY:  The regional manager wants a total number of visitors for the week.  We can copy the formula from C9 into box B9.

1. Click on C9 (the source cell). 
2. Click on EDIT and choose COPY.
3. Click on cell B9 (the destination cell) and click on EDIT and choose PASTE. 
4. See the total appear.
5. *NOTE* Excel converts the formula’s column “C” references into column “B” references.  To prevent this automatic adjustment, which is called Relative Cell Referencing, place $ signs in front of the column and/or row reference that you don’t want Excel to adjust when that cell is copied to another location.  For example, if C9 contained “=SUM($C$2:$C$8)”, when it is copied to B9, it will still read “=SUM($C$2:$C$8)”.  When Excel’s relative cell referencing is blocked by $ signs, it is known as Absolute Cell Referencing. 
6. With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.

Cutting and Pasting

Use this function when you want to move a cell or block of cells from one location to another.  No trace of the cell(s) will remain in the original location.

1. Click on the cell(s) you would like to cut (the source cell).
2. Click on EDIT and choose CUT.
3. Click on the cell where you want this cell to reappear (the destination cell) and click on EDIT and PASTE. 
4. See the cut-cell reappear in the new location.
5. With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.

INTERMEDIATE EXCEL SKILLS:  FORMATTING A SPREADSHEET

Formatting a Cell

1.      You must highlight the cell or block of cells you want to format.

2.      After you highlight the cells, choose FORMAT from the menu and select CELLS to see your choices.

1. Number:  Allows you to select the format for the type of data within the cells.  For example, choose CURRENCY if you are using dollar values, and Excel will add a $ sign in front of the cells.
2. Alignment:  Allows you to control the positioning of the text or number in your cell.  If you have a lot of text in one cell, and you don’t want to expand the cell’s width, you can choose WRAP TEXT. 
3. Font: You can change the style and size of the highlighted text or numbers, including making them bold, underlined or italic.  This is the same as the Microsoft Word formatting. 
4. Border:  Allows you to add vertical or horizontal lines to different parts of the worksheet. You can choose where you’d like the grid lines to print out, the line style, and even the line color. 
5. Patterns: Allows you to control the background color of the highlighted cells and to add patterns.  Use this to visually isolate data.
6. Protection: Allows you to lock cells and hide formulae from careless or unauthorized users.  Remember, if you password protect your spreadsheet, make sure you remember the password!
7. In Excel 2007, you can access most of these features from the quick menus under the HOME tab. To access the features for border, pattern, and protection, click the small arrow from the FONT, ALIGNMENT, or NUMBER quick menus to access these and other formatting features.

ACTIVITY:  Give row A the following characteristics:  bold, underlined and 16 font. 

Format the Height of a Row

1.      Click on any cell in that row and click on FORMAT and choose ROW.

2.      Click on HEIGHT and type in a new number for the Row Height.

3.      In Excel 2007, select a row and right click to access the quick menu where you will find the row height feature or under the CELLS quick menu click the word FORMAT

      Note: The standard setting is 12.75.

Format the Width of a Column

1.      Click on any cell in that column and click on FORMAT and choose COLUMN.

2.      Click on WIDTH and type a new number for the Column Width. 

3.      In Excel 2007, select a row and right click to access the quick menu where you will find the column

width feature or under the CELLS quick menu click the word FORMAT

Note: The standard setting is 8.43.

ACTIVITY: Adjust the column width so that cell A1’s “Days of the Week” label is entirely viewable.  If you don’t want to guess at the appropriate Column Width, then select AUTOFIT SELECTION instead and Excel will decide for you. 

Format A Sheet

Formatting a sheet lets you do two things: 

1.      Change the name of the sheet:

a.       Click on the tab you would like to rename. 

b.      Click FORMAT select SHEET (in and click on RENAME.

c.       You will see the name of the sheet darken.  Type in a new name and press Enter.

d.      In Excel 2007, click FORMAT under the CELLS quick menu, then select RENAME SHEET

2.      Change the Background Image

a.       Click on the tab whose background you would like to change.

b.      Click FORMAT, select SHEET and click on BACKGROUND.

c.       Locate the image file and click INSERT.

d.      In Excel 207, select the PAGE LAYOUT tab, then click on the BACKGROUND option to access this feature

INTERMEDIATE EXCEL SKILLS:  CHARTS

ACTIVITY:  We want to impress our regional manager with graphically displayed data.

1.      Highlight your spreadsheet, including the column headers. (Days of the Week, Visitors, etc.)

2.      Click on INSERT and choose CHART. (In Excel 2007, click on the INSERT tab, and then select the type of chart you would like to create from the options provided – the rest of the instructions below are ONLY for Excel 2000 and 2003) – For Excel 2007, to make various chart edits it is easiest to right click on the portion of the chart you would like to change and right click

3.      Click on the Type of Chart you would like and click NEXT.

4.      You’ll be presented with a draft of what your new chart will look like.  Click on Next. 

5.      The dialog box that appears will allow you to add some text labels to your Chart, such as a Title, and labels for the X and Y axes.  Then click NEXT

6.      This dialog box asks you where you would like your chart to appear—in a new worksheet, or as an object in your current sheet. 

7.      Click on FINISH.

ADVANCED EXCEL SKILLS

Functions

Excel can perform a very large number of functions, which are formulas that perform a specific calculation beyond standard arithmetic.  If you go to INSERT and choose FUNCTIONS, you will see a large menu of functions. 

1. If you want to use a certain function, first click in the cell on your spreadsheet you want that function performed in.  Then go to INSERT and choose FUNCTION and select the function you want to perform. 
2. Specify the cell or block of cells that you want the function to consider.
3. Click OK.
4. To insert functions in Excel 2007, you would choose the FORMULAS tab, then select the function you would like to perform from the function library and follow steps 2 and 3.

ACTIVITY:  The AVERAGE function is useful.

1. Click into cell A10 and type, “Daily Average”
2. Click into cell B10 and click on INSERT and then FUNCTION
3. Click on ALL, click on the AVERAGE function, and then click OK.
4. In the “Number1” field, type in the cells you would like to average

*NOTE* If the dialog box is blocking your view of the spreadsheet and you can’t remember the location of the cells you’d like to average, click on the symbol at the end of the “Number1” field.  Your spreadsheet will pop up and you’ll get a chance to highlight the block of cells you’d like to average.  Then press ENTER.

5.  Click on OK.

ACTIVITY:  To practice functions, try to apply the MAX function to have Excel determine the highest daily Customer total. 

Adding and Deleting Comments to the Spreadsheet

1. Click on the cell that you would like to add a comment to.  Click on INSERT and then COMMENT
2. In Excel 2007, click on the cell where you want to add the comment and right click for the quick menu, then select INSERT COMMENT.
3. Type your comment in the box that appears and then click in a cell outside the comment.
4. A small red tag appears at the top right corner of the cell that contains the comment.

ACTIVITY:  Add a comment to cell B7 that says, “This was the Saturday before Christmas so sales were particularly strong that day”

5. To delete a comment, click EDIT, the select CLEAR, and then select COMMENT.
6. In Excel 2007, to perform these functions right click the cell again for the quick menu and access the option this way.

Sorting Data

ACTIVITY:  The regional manager would like us to sort our data in a way that ranks the “Days of the Week” from the busiest day to the least busy day based on the number of “Visitors”.

1. Highlight the data, including the titles, but excluding the “Total” and “Daily Averages” rows.
2. Click on DATA and then SORT.
3. Click on the drop-down arrow below the “Sort By” header and select the criteria by which you want to re-sort your data.
4. Click either “Ascending” or “Descending”.
5. Click OK.
6. For Excel 2007, click the DATA tab, and use the SORT quick menu options to perform various sort types

*NOTE* re-sorting the data by “Days of the Week” in ascending order will not return the data to a Monday – Friday order.  It will re-sort the data based on the alphabetical order!! To get it back to Monday-Sunday, click on EDIT and UNDO SORT.

To Keep Row and Column Labels Visible As You Scroll

1. To freeze the tope horizontal pane, select the row below where you want the split to appear.
2. To freeze the left vertical pane, select the column to the right of where you want the split to appear.
3. Click on WINDOWS menu and choose FREEZE PANES.
4. To UNDO, return to the WINDOWS menu and choose U NFREEZE PANES.
5. For Excel 2007, to freeze panes you would choose the VIEW tab, and then click on word FREEZE PANES.

Filtering Out Data

ACTIVITY: The regional manager wants just the data for Saturdays.

1. Click anywhere within your data.
2. Click on DATA FILTER and then AUTOFILTER
3. Click on one of the drop-down arrows and select the criteria you would like to filter the data through.
4. Excel pulls out just the data you requested.
5. To get rid of the filter,
1. Click on DATA, click on FILTER and choose AUTOFILTER.
6. With Excel 2007, the easiest way to perform filtering is by selecting the data to be filtered, clicking on the DATA tab, then click FILTER under the SORT and FILTER option

Read More

Common Preventative Maintenance Techniques Used for Networks

Network Maintenance

There are common preventive maintenance techniques that should

 continually be performed for a network to operate properly. 

In an organization, if one computer is malfunctioning, generally only that user is affected.

 But if the network is malfunctioning, many or all users are unable to work.

Preventive maintenance is just as important for the network as it is

 for the computers on a network. You must check the condition of

 cables, network devices, servers, and computers to make sure that

 they are kept clean and are in good working order. One of the biggest

 problems with network devices, especially in the server room, is heat.

 Network devices do not perform well when overheated. When dust gathers

 in and on network devices, it impedes the proper flow of cool air and 

 sometimes even clogs the fans. It is important to keep network rooms clean and change air filters

 often. It is also a good idea to have replacement filters available for prompt maintenance.

 You should develop a plan to perform scheduled 

maintenance and cleaning at regular intervals. A maintenance program

 helps prevent network downtime and equipment failures.

As part of a regularly scheduled maintenance program, inspect all cabling. Make sure that cables

 are labeled correctly and labels are not coming off. Replace worn or unreadable labels.

 Always follow the company’s cable labeling guidelines. Check that cable supports are properly 

installed and no attachment points are coming loose. Cabling can become damaged and worn.

 Keep the cabling in good repair to maintain good network performance. Refer to wiring diagrams 

if needed.

Check cables at workstations and printers. Cables are often moved or kicked when they are

 underneath desks. These conditions can result in loss of bandwidth or connectivity.

As a technician, you may notice that equipment is failing, damaged, or making unusual sounds.

 Inform the network administrator if you notice any of these issues to prevent unnecessary network

 downtime. You should also be proactive in the education of network users. Demonstrate to 

network users how to properly connect and disconnect cables, as well as how to move them, 

if necessary.

Read More

Network Cards

A NIC is required to connect to the network. The NIC may come preinstalled on a computer, or you might have to purchase one on your own. You must be able to upgrade, install, and configure components when a customer asks for increased speed or new functionality to be added to a network. If your customer is adding additional computers or wireless functionality, you should be able to recommend equipment based on their needs, such as wireless access points and wireless network cards. The equipment that you suggest must work with the existing equipment and cabling, or the existing infrastructure must be upgraded. In rare cases, you might need to update the driver. You can use the driver disc that comes with the motherboard or adapter card, or you can supply a driver that you downloaded from the manufacturer.
There are many types of network interfaces, as shown in the figure:

• Most network interfaces for desktop computers are either integrated into the motherboard or are an expansion card that fits into an expansion slot.

• Most laptop network interfaces are either integrated into the motherboard or fit into a PC Card or ExpressBus expansion slot.

• USB network adapters plug into a USB port and can be used with both desktops and laptops.

Before purchasing a NIC, research the card’s speed, form factor, and capabilities. Also check the speed and capabilities of the hub or switch connected to the computer.
Ethernet NICs will auto-negotiate the fastest speed that is common between the NIC and the other device. For instance, if you have a 10/100 Mb/s NIC and a hub that is only 10 Mb/s, the NIC operates at 10 Mb/s. If you have a 10/100/1000 Mb/s NIC and a switch that is only operating at 100 Mb/s, the NIC operates at 100 Mb/s.
If you have a gigabit switch, you most likely need to purchase a gigabit NIC to match speeds. If there are plans to upgrade the network in the future to Gigabit Ethernet, make sure to purchase NICs that can support the speed. Costs can vary greatly, so select NICs that match the needs of your customer.
To connect to a wireless network, the computer must have a wireless adapter. A wireless adapter communicates with other wireless devices, such as computers, printers, or wireless access points. Before purchasing a wireless adapter, make sure that it is compatible with the other wireless equipment that is already installed on the network. Verify that the wireless adapter is the correct form factor for the customer's computer. You can use a wireless USB adapter with any desktop or laptop computer that has a USB port.
Wireless NICs are available in different formats and capabilities. Select a wireless NIC based on the type of wireless network that is installed:

• 802.11b NICs can be used on 802.11g networks.

• 802.11a can be used only on a network that supports 802.11a.

• 802.11a dual-band, 802.11b, and 802.11g NICs can be used on 802.11n networks.

Read More

Network Installation Steps

Having a clear understanding of all the steps required to physically building a network improves the success of a project. You may need to install network cards, wireless and wired network devices, and configure network equipment.
When installing a wireless network, you can use a wireless access point or a multipurpose device. The Linksys E2500 is a multipurpose device that provides both router and access point capabilities. You have to decide where you want to install access points to provide the maximum range of connectivity.
After you have determined the location of all the network devices, you are ready to install the network cables. If you are installing the cable yourself, make sure that all the necessary materials are available at the site, as well as a blueprint of the network’s physical topology.
To physically create a network, follow these steps:

Step 1. Make sure all Ethernet wall port locations are properly marked and meet the current and future requirements for the customer. To install the cable in ceilings and behind walls, you perform a cable pull: One person pulls the cable, and another feeds the cable through the walls. Make sure to label the ends of every cable. Follow a labeling scheme that is already in place, or follow the guidelines outlined in TIA/EIA 606-A.

Step 2. After the cables are terminated at both ends, use a cable tester to make sure that there are no shorts or interference.

Step 3. Use a floor plan to find the locations for access points that allow maximum coverage. The best place for a wireless access point is at the center of the area you are covering, with line of sight between the wireless devices and the access point.

Step 4. Connect the access point to the existing network.

Step 5. Make sure that the network interfaces are properly installed in the desktops, laptops, and network printers. After the network interfaces are installed, configure the client software and the IP address information on all the devices.

Step 6. Make sure to install switches and routers in a secured, centralized location. All LAN connections terminate in this area. In a home network, you might need to install these devices in separate locations, or you might have only one device.

Step 7. Install an Ethernet patch cable from the wall connection to each network device. Check whether you have a link light on all network interfaces and on each network device port that connects to a device.

Step 8. When all devices are connected and all link lights are functioning, test the network for connectivity. Use the ipconfig /all command to view the IP configuration on each workstation. Use the ping command to test basic connectivity. You should be able to ping other computers on the network, including the default gateway and remote computers. After confirming basic connectivity, configure and test the network applications, such as email and the web browser.

Read More

OSI and TCP/IP Data Models

Reference Models

An architectural model is a common frame of reference for explaining Internet communications and developing communication protocols. It separates the functions of protocols into manageable layers. Each layer performs a specific function in the process of communicating over a network.

The TCP/IP model was created by researchers in the U.S. Department of Defense (DoD). The TCP/IP suite of protocols is the dominant standard for transporting data across networks and the Internet. It consists of layers that perform functions necessary to prepare data for transmission over a network. The chart shows the four layers of the TCP/IP model.

A message begins at the top application layer and moves down the TCP/IP layers to the bottom network access layer. Header information is added to the message as it moves down through each layer and is then transmitted. After reaching the destination, the message travels back up through each layer. The header information that was added to the message is stripped away as the message moves up through the layers toward its destination.

Application Layer Protocols
Application layer protocols provide network services to user applications, such as web browsers and email programs. Common protocols that operate at the application layer include HTTP, Telnet, FTP, SMTP, DNS, and HTML.

Transport Layer Protocols
Transport layer protocols provide end-to-end management of the data. One of the functions of these protocols is to divide the data into manageable segments for easier transport across the network. Common protocols that operate at the transport layer include TCP and UDP.

Internet Layer Protocols
Internet layer protocols provide connectivity between hosts in the network. Common protocols that operate at the Internet layer include IP and ICMP.

Network Access Layer Protocols
Network access layer protocols describe the standards that hosts use to access the physical media. The IEEE 802.3 Ethernet standards and technologies, such as CSMA/CD and 10BASE-T, are defined in this layer.

Read More

Install the Motherboard

The CPU, heat sink and fan assembly might be installed on the motherboard before the motherboard is placed in the computer case. This allows for extra room to see and maneuver components during installation.
CPU
Figure 1 shows a close-up of the CPU and the motherboard. The CPU and motherboard are sensitive to electrostatic discharge. When handling a CPU and motherboard, make sure that you place them on a grounded antistatic mat. Wear an antistatic wrist strap while working with these components.
CAUTION: When handling a CPU, do not touch the CPU contacts at any time.
The CPU is secured to the socket on the motherboard with a locking assembly. Before installing a CPU into the socket on the motherboard, make sure that you are familiar with the locking assembly.
Thermal compound helps to conduct heat away from the CPU. When you are installing a used CPU, clean the top of the CPU and the base of the heat sink with isopropyl alcohol and a lint free cloth. A paper coffee filter works well as a lint free cloth. This removes the old thermal compound. The surfaces are now ready for a new layer of thermal compound. Follow the manufacturer recommendations about applying the thermal compound.
Figure 2 shows thermal compound being applied to the CPU. In most cases, apply a very small amount of thermal compound to the CPU. The compound spreads out evenly under the weight and pressure of the heat sink and fan assembly.
Heat Sink and Fan Assembly
Figure 3 shows the heat sink and fan assembly. It is a two-part cooling device. The heat sink draws heat away from the CPU. The fan moves the heat away from the heat sink. The assembly usually has a 3-pin power connector.
Figure 4 shows the cable and the motherboard connector for the heat sink and fan assembly.
To install a CPU and heat sink and fan assembly, follow these steps:
Step 1. Align the CPU so that the Connection 1 indicator is lined up with Pin 1 on the CPU socket. This ensures that the orientation notches on the CPU are aligned with the orientation keys on the CPU socket.
Step 2. Place the CPU gently into the socket.
Step 3. Close the CPU load plate. Secure it in place by closing the load lever and moving it under the load lever retention tab.
Step 4. Apply a small amount of thermal compound to the CPU. Follow the application instructions provided by the manufacturer of the thermal compound.
Step 5. Align the heat sink and fan assembly retainers with the holes on the motherboard.
Step 6. Place the assembly onto the CPU socket, being careful not to pinch the CPU fan wires.
Step 7. Tighten the assembly retainers to secure the assembly in place.
Step 8. Connect the assembly power cable to the CPU fan connector on the motherboard.

Read More