Microsoft Excel Quick Start
S tarting the Program
Microsoft Excel can be started by either clicking on the Excel icon on the desktop or by going to Start / Programs / Microsoft Excel.
The Excel Interface
T he names of the various parts of the Excel interface are shown above.
Standard Toolbar
The standard toolbar contains links to commonly used Excel tasks. If you wish, you can customize the buttons on the toolbar to better fit the tasks you normally use. Pictured is the Excel 2000 toolbar. Excel 97 is similar.
Formatting Toolbar
The Formatting Toolbar has many of the same font, border, and shading capacities as Microsoft Word, and they work in similar ways. These buttons allow you to change font size, color, and style, alignment of the information in a cell, as well as other formatting operations.
Formula Bar
The Formula Bar is where you enter both cell information and/or values, and create formulas that will manipulate the data contained in other cells. The cell listed in the Cell Name Box always lists the active cell. When you enter something in a cell (or the formula bar) to put into a cell, three symbols appear; a red ‘X’, a green check, and an equal sign.
Clicking the red ‘X’ deletes the changes you have made to the cell, the green check applies the changes to the cell, and the equal sign allows you to edit the cell (usually used in editing formulas).
Cell Naming
In Excel and most other modern spreadsheets, the cell naming convention is a letter followed by a number, creating a unique location on the spreadsheet. The letters go across the top of the spreadsheet and indicate the column, with the numbers down the left side, indicating the rows. This means that the top-most cell on the left side is cell A1. Cell C2 is located at the intersection of the third column and the second row.
There are 65,536 numbered rows in an Excel worksheet. The columns begin at A and continue through Z. The first column after Z is column AA, followed by AB, and so on. The are a total of 256 columns available, ending in column IV.
Finding the Active Cell
The active cell always has a black border around it and the cell name is contained in the Cell Name Box. Any text, numbers, or formulas that are entered into an Excel worksheet are entered into the active cell.
Fill Handle
The Fill Handle allows you to enter data or text information quickly. For instance, if you want to create a column that contains cells numbered from 1 to 10. Enter ‘1’ in cell A1 and ‘2’ in column A2. Left click on the fill handle and drag down the column eight more cells. When you release the mouse button, Excel will have completed the series. This will also work for other number patterns (2, 4, 6…. or 3, 6, 9…) and for months of the year.
Sheet Tabs
T he tabs at the bottom of an Excel workbook show all of the worksheets. By right-clicking on a tab, you can rename the worksheet, copy it, or move it to another location. The active sheet is the on that has a white background, as shown here.
Workbook? Worksheet?
Each sheet in Excel is called a worksheet. There can be many (Ina a practical sense, the number is only limited by the amount of system memory you that is available and the time you have available to wait for the worksheet to calculate) worksheets or chart sheets (which have graphs stored in them) in a workbook.
Navigating in a Worksheet
There are several ways to move the active cell in a worksheet. The one I use most is to use the scroll bars to get into the correct range of cells, then clicking on the cell I want to make it active. The up, down, left, and right arrows will move you one cell at a time in the chosen direction. Pressing the Ctrl key and Home key together will take you to cell A1. The Ctrl key and up arrow will take you to the top of a column, while the Ctrl and down arrow will take you to the last data entry in a column. The Ctrl key and left arrow will take you to the beginning of a row and the Ctrl key and right arrow will take you to the last data entry in a row.
You can also move around a worksheet by using the Go To command, found on the Edit menu or made active by pressing the Ctrl key and ‘g’ key.
Oops!
Occasionally, we all make a major mistake. Importing an unwanted worksheet or deleting data can really create havoc. Excel has a built in rescue function. When you make a mistake, go to Edit and click on the Undo command. The Undo function has saved me on more than one occasion.
In order for this to work, you should use the Undo command as soon as you recognize the mistake. If you wait until the file is saved and closed, it is too late. In the worst case, if you have made a real mistake and Undo function won’t fix it, if you haven’t saved the workbook, you can close it without saving the changes. You’ll loose everything that you have done in the current session, but it may be better than trying correcting the mistake.
Moving and Copying Data
Many times it is necessary in Excel to move data from one cell to another. The two main methods of moving data are the clipboard (a temporary storage area) and by using Drag and Drop. They work like this:
Action
|
Key Combination / Menu Command
|
Cut
|
Performed by selecting the cell and pressing the Ctrl key and ‘x’ key together. Alternative: select cell, go to Edit and click on Cut. Puts the cell information on the Clipboard. Deletes the cell information from the source cell.
|
Copy
|
Performed by selecting the cell and pressing the Ctrl key and ‘c’ key together. Alternative: select cell, go to Edit and click on Copy. Puts the cell information on the Clipboard. Leaves the cell information in the source cell.
|
Paste
|
Performed by selecting the cell where you want the information on the clipboard from the Cut or Copy function placed, and pressing the Ctrl key and ‘v’ key together. Alternative: select cell, go to Edit and click on Paste. Puts the cell information from the Clipboard into the selected cell. Deletes the current cell contents.
|
Drag and Drop
|
Good for moving data short distances in a worksheet. Move the mouse to the left side of the cell until a left pointing arrow appears. Left click and hold, while moving to the cell where you want the information. Release the mouse key to drop the information.
|
H elp!
Excel actually contains a great deal of information about how the program works, including how to enter formulas, in the Help menu. Go get help, press the F1 key, or click on Help, then click on Microsoft Excel Help. I usually use the Index and type in keywords to find information.
Also available on the Help menu is Office on the Web, which will take you to the Microsoft Office web site where you can perform a search for the information you are interested in.
Formulas and Functions
The heart of a spreadsheet is the ability to enter formulas or Functions that will do something useful with the data you have entered. The following briefly describes creating and entering simple formulas and functions.
Formulas
T o enter a formula, click on the cell where you want the result to be entered, which makes that cell active. I enter all formulas I write in the formula bar, although you can enter simple formulas directly into a cell. Here is a simple worksheet that will calculate the total budget for several classrooms, the average amount available per classroom, and the percentage expended. None of the formulas are entered.
The first formula we will enter is the Total Budgeted. This is done by using the AutoSum icon.
This button creates a sum of all of the cells above or to the left of the selected cell. The formula takes the form =SUM( ) (In Excel, all formulas and functions begin with the ‘=’ sign). To determine the Total Budgeted, click in cell H2, then click on the AutoSum icon.
Notice that Excel looks at the cells to the left and includes all of the cells in the range that have data entered in the calculation. If you wanted to manually enter this data, you would select cell H2, then type in =SUM(B2:G2). This tells Excel to add up (SUM) the cells B2 to G2 – with the colon in the formula meaning “everything between”. This sum includes all cells, whether they have values entered or not.
Next, we need to determine the Average Budget amount. Since an average (mean) is the sum of the numbers divided by how many numbers there are, we already have determined part of the information needed – the SUM we did in H2. Really all we need to do now is to divide the result of H2 by 6, the number of rooms. The formula is: =SUM(H2/6) . To enter this formula, click on cell I2, and enter the formula into the formula bar, then click on the green check icon. (Note: Excel also has an “AVERAGE” function that will allow you to do the same calculation without having to count the cells.) Our worksheet should now look like this:
 
Next, we need to create the % Expended calculation. The % Expended is the total of what is spent each school month, divided by the total budget for the room. The calculation entered into cell B13 will look like:
=SUM(B3:B12)/B2 . This takes the all of the cells between cell B2 (September for Rm 1) and cell B12 (June for Rm 1) and adds them together. The total is then divided by the budgeted amount that was entered in cell B2.
Although it is possible to enter the same formula in subsequent cells, Excel will do the entry automatically. The fill handle is used to do this. To fill the formulas for the remaining cells, click fill handle for cell B13, then drag the fill handle over to cell G13. When you click on cell C13, you will see that Excel has changed the formula to be =SUM(C3:C12)/C2 – the correct formula for cell C13. Now, when we enter the funds spent each month by room, the worksheet will calculate the percentage of the budget that remains.
Functions
A  s you might expect, there are hundreds other functions available besides AutoSum. To access these functions, click on the cell where you want the result, then click on the function button.
The following screen will appear:
N early all functions in Excel include a brief explanation of what the function does and a wizard that walks you through creating the formula.
Formatting Worksheets
Formatting Data
As mentioned earlier, Excel has a Formatting Toolbar that helps you in changing the appearance of a worksheet. By highlighting a cell or group of cells, you can change the horizontal alignment, the font and font size, as well as make the cells Italic or Bold. These functions work essentially the same as they do in Word.
Changing How Data is Displayed
A s you can see from our example, the cells in the % Expended row of our worksheet are shown as a percentage, not as a decimal. This conversion was done with cell formatting.
Click on cell B13, then drag the highlight over to cell G13. Right click anywhere on the highlighted area to bring up the Format Cells window. Left click on Format Cells. (Note: You can also get to this command by clicking on Format then Cells)
To change the format of the numbers, click on the Number tab of the Format Cells window. Click on Percentage and select the number of decimal places you want, then click OK.
As you can see, there are also tabs for changing cell Alignment, Font, Border, Patterns, and Protection. We’ll cover some of these next.
Cell Alignment
T his screen lets you change the vertical and horizontal cell alignment, the direction of the text in the cell and will allow you to Wrap Text ( Keeps the information in a cell from ‘running over’ in to the next cell), and Merge Cells, which allows you to create a single cell from several small ones.
Highlight the cells you want to change, then click on the appropriate selections in the window shown.
Borders and Patterns
P utting a border around cells make them stand out and will help clarify for the user what the worksheet is showing. To put a border around a group of cells, highlight the cells, then right click on the highlighted area, bringing up the Format Cells command. Left click on Format Cells, then click on the Border tab.
T his controls the top border of the top highlighted cell
T his controls the bottom border of the bottom highlighted cell
T    his controls the top and bottom borders of all highlighted cells between
Left side border of the leftmost highlighted cell
Right side border of the rightmost highlighted cell
All left and right borders of highlighted cells between
By highlighting the cells, adding light and dark borders, and adding a gray pattern in the top row, it changes the appearance significantly.
T  he gray cell pattern is added by highlighting the cells you want the pattern in, and right clicking, then left clicking on Format Cells. Click on the Patterns tab, then click on the pattern you want for the cells. Generally, light patterns are easier to read (and print better) than dark patterns.
To remove the pattern from a cell, select the cell(s) and then click on the No Color button.
Creating Formulas for Multiple Worksheets
Often, the worksheets in an Excel workbook are all identical, such as in the case of a workbook that contains teacher budget sheets. It is possible to create formulas that refer to multiple worksheets at the same time. This is done by beginning the formula in the desired cell up to the first parenthesis: =SUM(
After this, click on the tab of the first sheet that is involved, then click on the cell(s) that the data you want to work with is located. Next, hold down the Shift key and click on the tab of the last sheet that is involved. Finally, close the parenthesis and click on the green check or press Enter.
The key to doing this is that all the worksheets have to have the same layout. If they do not, the results can be unpredictable.
Absolute or Relative
There are times when having Excel change references for you works well. As you fill a formula down, it will automatically increment the row reference and as you fill across, it will automatically increment the column reference; these cell references are called Relative Cell References. Although it is great to have Excel work for you, sometimes you just want Excel to stop helping. A good example is when you want to refer to a single cell in a formula - for instance, when using a formula to calculate tax on sales. If you put the tax rate in the formula, the entire workbook needs to be modified if the tax rate changes.
Fortunately, Excel has a way for you to tell it to stop helping you. You can tell Excel to refer to a single row, a single column (or both) by using Absolute Cell References. By putting a $ sign in front of the cell reference, you can turn off the automatic incrementing. If you want to always point to cell A1 in a formula, the cell reference would look like $A$1 in the formula. If you wanted to have the column reference stay fixed but the row reference increment, it would look like $A1. Finally, if you wanted to have the row reference stay fixed but the column reference increment, it would look like A$1. You can toggle through the alternatives by holding down the F2 button and pressing the F4 button.
Getting Data From Elsewhere
Excel can also use data entries in a worksheet formula that reside outside the current workbook that you have access to (If you want to have formulas update data in the external workbook, you must have read / write rights to the external file) This is done through the same process as referring to another worksheet in a formula. Open both workbooks, select the cell where you want the formula, and begin the formula. If you wanted to create a sum, you would start the formula: =SUM( After this, go to Window and select the external workbook. When the external workbook is open, select the cell(s) you want to get the data from. When they are selected, go to the formula bar and close the parenthesis.
Excel can also be used to get data from the web for you to use locally. To do this, click on the cell where you want the data inserted, then go to Data / Get External Data / New Web Query. When the window opens, you can select how much of the data you want imported and how much of the formatting you want preserved. After you make your selections, click on Browse Web. This will open your web browser and allow you to navigate the page that contains the data. Make your Excel workbook active again by clicking on Excel on the toolbar, then click OK to import the data.
|
Yüklə 366.5 Kb.