These directions can also be found on Dat on COP1/training/excel97.doc
These are general major topics. Use the Edit-Find choice from the menu bar to search for something specific. If the topic isn't covered and you would like something added to this page please contact webmaster@cop.ufl.edu
I. Formulas
II. Formatting
III. Sorting Data
IV. Graphs/Charts
Creating
Formatting
background colors
axis
text
colors
Error Bars
VI Protecting Worksheets (so formulas don't get destroyed)
Note: there is a specific order that excel follows when making calculations!
1. exponentials
2. multiplication and division
3. additon and subtraction
=3*7+5 yields 26
whereas =3*(7+5) yields 36
order can be forced by parenthesis.
here the parenthesis force the addition of 7+5 which is then
multiplied by 3
1. Formulas need to be preceded by an equal "=" sign.
2. To calculate the sum of a column:
In the cell where you want the results to show:
3. Type =SUM then type the left parenthesis, hold the left mouse button down and drag over the columns (row) to be totaled and then type the right parenthesis. (Or type the whole deal as shown below)
=SUM(A2:A10) this will give the sum of column A rows 2 through 10, the formula would put into Cell A11 (or where-ever you wanted the result)
=SUM(A2:F2) will give the sum of row 2 columns A through F.
=AVERAGE(A2:A12) will give the average score
CAPITAL LETTERS ARE NOT NECESSARY but make it easier to see the formula
=sum(a2:a10) will work.
=MAX(A2:A10) will give maximum score.
=MIN(A2:A19) will give the lowest score.
You can use Ctrl-Enter to place
the same formula
in several cells.
1. Select the range of cells
2. With the range still selected type the formula (or value) into the active cell
3. Press Ctrl-Enter (not Enter) and the formula will be entered into all highlighted cells. This is handy when you are adding up grades on an exam. You don't need to enter the formula 109 times or do Copy Paste.
The other way to enter the same formula is to:
1. Type the formula into the first cell and press enter
2. Highlight the cell
3. Place the cursor over the cell at the bottom right side (over the small black box) so that it turns into a thick + sign. Now press and hold down the left mouse button and drag the cursor down over the cells where you want the formula to be place.
Viewing Formulas Vs Viewing Results
1. Pressing Ctrl and ` toggle (switch) between viewing the formula and the result of the formula (value generated). This is handy for finding errors in the formulas.
Absolute
Relative
Relative references: the cell references in a formula automatically adjust after you copy the formula to another cell or range. That means: copying formula =SUM(B3:B9) from cell B10 to cell C10 the new formula will automatically adjust to read =SUM(C3:C9)
Absolute references: the cell references in a formula stay the same regardless of where you copy it to. This is controlled by a $ sign before the column letter. if there is a formula in B4 that you want to copy to D5 then place a $ infront of B and 4: $B$4. Then when you copy the file the reference will always be the same cell B4.
While
entering the formula or after you have entered the formula you
can toggle between absolute and relative by placing the cursor
before the column heading by pressing F4 which
will toggle through the options of absolute and relative.
If you have a worksheet that has a lot of formulas and something is wrong (a cell has an error message) - you can use the tracer lines option to track the formula back through the worksheet to the referenced cells.
1. Click on the cell that has the error message. From the menu bar choose Tools, Auditing, Trace Precedents (or Show Auditing Toolbar and then choose trace precendents)
2. The source of data is marked and there is an arror which leads to the formula.
1. Once values are entered if the column is not wide enough you can use the mouse to double click on the column edge or grab the edge of the column by the letter heading and manually drag the desired width. Or you can double click the left mouse button on the column edge and it will automatically increase to the size to fit the largest entry.
1. To increase the decimal point value, highlight the cells or columns you want to change, then from the menu choose Format - Cells - Number and choose how many decimal points you want to show. Or from the tool bar you can click on the icon for the decimal points.
1. Sorting of data: i.e. a roster to post outside after an exam so that the grades are sorted by ss# and the names are hidden.
Note: always save your work before starting any change so that when something goes wrong (and it will) and the file is messed up you can close without saving and start over (this takes less time than trying to undo).
From the menu bar choose Data - Sort Decide if you have a header (the first row of your table contains labels for the column (ie Name/SS# etc.) Then choose Sort By (press the down arrow so that the name of your column shows, ie SS# and decide if you want ascending or descending). Then choose ok.
Once sorted by SS# drag the column width so that only5 numbers show. Then highlight the column and choose right align so that only the last 5 numbers show. Place your cursor in the column of names, and from the menu bar choose Format - Column - Hide this will hide the names, then select the print area and print.
Numbers can be copied from one sheet within a workbook to another sheet (page) in the same workbook and pasted as either values (permanent - won't change or be updated) or paste special - link - (where the values on the second sheet are linked to the values on the first page. Changing the first page will automatically update (change) the second sheet.
Highlight the area to be copied. choose Edit - Copy go to the place where you want the numbers to be copied and choose Edit Paste or Edit Paste Special-Link
If there are formulas: the formula will be copied to the new location. (note: it will reference the relative cells (if your formula sums numbers in a1:b1, then the copied formula will reference the two preceeding cells; if you copy the formula into cell j1 it will sum h1:i1) so be sure this is what you want. If you copy the formula in the same column then the reference will be correct. If the cell has a formula generating a number and you want to copy that number then choose Edit Paste Special and Choose Values. This will put in numbers that are not relative or linked to anything.
Copying Worksheets to another Workbook or same Workbook
1. Open worksheet to be copied
2. From the menu bar choose Edit Move-Copy-Worksheet
3. If it is to be copied to a new workbook type in the new name
4. Choose where in the workbook you want the sheet to be placed
5. Click on make a copy
6. Click on OK
A new copied will be made and the formating will be retained.
1. To insert a vertical page break click on the column to the RIGHT of where you want the page break.
2. From the menu bar choose Insert - Page Break (a dashed line will appear where the page break is located)
3. To insert a horizontal page break click on the row BELOW where you want the page break.
4. From the menu bar choose Insert - Page Break (a dashed line will appear where the page break is located)
5. To get both a horizontal and vertical page break put the cursor in the cell just to the right and below where you want the break and and choose Insert -Page Break
6. To remove a page break place the cursor in the cell next to the page break and from the menu bar choose Insert - Remove Page Break.
Note: There are two parts of excel’s chart (the big outline of the graph and the smaller inside area which is the actual graph (chart) itself. Clicking on different parts of the overall picture will produce different results. Watch which areas that have a box around them to see where you are working.
1. Open excel.
2. Enter data with what you expect as your x-axis in column A, your data information in the next columns. Use separate columns at the end for your error bars. The will not be included in the origination of your graph.
3. Once all data has been entered, hold the left mouse button down and drag it over your data set creating a box around it to select the data, omit the error bar columns at this time.
5. From the menu bar choose Insert Chart This Page it will show the area that you highlighted
6. On your spreadsheet hold the left mouse button down and draw a box where you want the chart (graph) to be placed.
7. Choose next and pick the type of plot you want to create (ie lines, bar, lines with points) click next after each choice.
8. This will show step 4 of 5 and a graph of your data, make sure you have chosen columns for data series and then pick Use 1 column for category (X) axis labels.
9. Choose next and you will have the chance to put in legends, axis labels and a title for your graph.
10. The choose finish and it will draw a chart (graph) in the box you created previously. This will probably not be big enough but it will have a box around it with little black "handles". Grab a corner of the box and drag to the size you want.
Background colors - attributes of graph
11. The graph (chart) will probably have a gray background. To change the format of the graph double click with the left mouse button in the center of your graph. This will bring up a patterns dialog box where you can change the background color and whether or not there is a box around the graph.
12. To change the attributes of the x-axis, double click the left mouse button on the axis line and several options will open up to change. The y-axis can be modified the same way.
13. To change the width of the line of the x-axis or y-axis. Double click the left mouse button on the axis. Choose the Patterns tab and pick the thickness (weight) of the line. This is recommended if you are going to use in word/powerpoint document.
14. To add text to the graph on the top menu bar where it says axis type in the text you want to add. Press return and the text will pop down onto your graph, probably not where you want it to be. It will have a box around it so you can place the cursor over the box part of the text and move it to where you want it to be.
15. Each series of data will automatically have a different color. To change this attribute double click the left mouse button on any data point in that series and a dialog box will appear to be able to change the color, name and value (this is the legend) and error bar format.
16. To change the legend typing: Double click the left mouse button on the specific data set. Choose Name and Value tab and type in a new legend to go with the data instead of series 1…
17. To add Error Bars. Double click the left mouse button on the specific data set or you can click on the data set once and make sure each point is highlighted then click the right mouse button. A choice of options will pop up including error bars. Put the cursor in the box which says custom +. Then on the spreadsheet highlight the cells which contain the values for the error bars. Decide whether you want the error bars to go up and down or just one direction, copy the value in the + section to the - section for both directions of error bars. Choose ok.
1. Enter formulas into your spreadsheet.
2. Highlight the cells where data is going to be entered/changed
3. From the menu bar choose Format - Cells - Protection choose unlock.
4. Click on ok
5. From the menu bar choose Tools - Protection - Protect Worksheet
6. Enter a password (make sure you write this down somewhere because it can't be removed unless you have the password!).
7. Now data can be entered but the formulas can not be changed.
Cells in the same row/column or even different rows/columns can be merged. Colors and shading and alignment within the cell can be added through the Format Cells option
1. Highlight the cells to be merged. (a1:b1)
2. On the menu bar choose Format Cells Alignment click on merge cells. This creates on cell going over two columns (A and B)
3. To merge two different rows. Highlight the cells (A1:A2).
4. From the menu bar Choose Format Cells Alignment click on merge cells. This creates on cell going over two row (# 1 and #2).
5. Then you can format within the cell to be centered horizontally or vertically or even at an angle.
6. There is an icon on the toolbar which accomplishes the same thing. (It looks like a letter a within a box.
Images/WordArt/Graphics
1. Objects can be added to the worksheet by clicking on the Drawing icon at the top of the toolbar (looks like the letter A with a cylinder and box) This will put a tool bar at the bottom of the screen to choose objects.
2. Once the objects are added to the spreadsheet they can be filled with color and/or rotated or moved accordingly.
3. To add a shadow to a box first create your box. Then click on the button on the bottom tool bar for the type of shadow style you want. To change the color of an object that has a shadow -click on the object. Then click on the bottom tool-bar the icon second from the right (shows shadows). A small box should open that gives options for moving the shadow placement around and one of the options at the far right has a down arrow. Click on the down arrow and different color options will show. Choose the color.
4. Note because these programs are inter-related the steps used in excel should work the same in word and powerpoint.
To repeat the row or column titles for multiple page printouts:
1. From the menu bar choose File, Page, Setup
2. Click on the Sheet tab
3. In the Print Title area select either the Rows to Repeat at Top or the Columns to Repeat at Left
4. In the worksheet select the row(s) or column(s) of titles you want to appear on each printed page.
1. With the sheet open, from the menu bar choose File Page Setup and the Sheet tab.
2. In the Print Area box type in the print range A1:G23, j2:j3, with areas separated by a comma, this will print on separate sheets. The areas can be highlighted by moving this box out of the way and with the left mouse button held down draw a box around the print area, type a comma and then draw around the next area. Check print preview to see the separate pages.
3. To print several areas on one sheet hide the columns or rows that you don't want printed and set the print area around the remaining columns. If it is a little too big for one page then choose "fit to page" option from the File Page Setup options.
Excel automatically places a generic header (sheet1 or the name of the sheet if you have one) and footer (page1) on your printouts. In order to customize:
1. From the menu bar choose File - Page Setup
2. Click on the tab for Header/Footer
3. In the header box click on the down arrow to choose from the list or to type in your own header click on the custom button. This will also allow you to format the typing as you wish and place it on the left side, center or right side
4. For the footer click on the down arrow and choose from the list or as above choose custom footer and make necessary changes.
Last modified October 26, 1998
Return to Tutorial
page