Sometimes you may want to add graphics, for example a corporate logo, to a worksheet. The good news is that images, ClipArt and WordArt are available in Excel, along with a host of call-out shapes that you can use to label your charts. You’ll find them all on the Insert ribbon.
Data manipulation
The features mentioned in this section are most relevant when you’re working with a large data set - perhaps several hundred, or even thousand records - and it isn’t practical to scroll through the entire worksheet each time you want to find a particular record.
To use data functions effectively, each column of your worksheet should contain the same data type, apart from the column heading. Ideally, row 1 should contain the column headings, with the data rows immediately below; this structure is referred to as a data table. If you have blank rows in your data set, then you’ll need to manually select the data to be manipulated, which you don’t really want to do.
Sort
The sort function does exactly what it says: it sorts your data records based on the criteria that you specify. You can sort numbers, text or dates, in either ascending (default) or descending order. Blank cells are always placed last in a sort.
If you want to sort an entire data table:
1. Click anywhere in the column that you want to sort by.
2. On the Home ribbon, select Sort & Filter.
3. Choose either Ascending (Sort A to Z) or Descending (Sort Z to A)
order.
4. Your data will be sorted based on the value in the column that you
initially clicked on.
If you want to sort on two or more criteria (columns), or if you want to sort a range of cells, then you need to do a custom sort:
1. Click in the data table, or select the cells to be sorted.
2. 
On the Home ribbon, select Sort & Filter, and choose Custom Sort. The Sort Window will open.
3. In the Sort By field, use the drop-down arrows to select the column that you want to
sort by and the order (ascending or descending) to be used.
4. If you want to add another sort criterion, then click the Add Level button, and a second details row will appear in the window. Again, choose the sort column and sort order.
5. Add more levels (or delete levels) as required.
6. When you click the OK button at the bottom of the window, your data will be sorted.
Note that the Sort function is also available from the Data ribbon.
| Remember that you can rely on the Undo button if you don’t like the result that you get! |
Filter
The filter function lets you view just the records that you want to see! The other records in your data table will still be there, but hidden. To use this amazing function:
1. On the Home ribbon, select Sort & Filter, and select the Filter option. 
2. In the first row of your data table, a drop-down arrow will appear on the right of each
column heading. When you click on a drop-down arrow, you’ll see a list of all the
values occurring in that column. Press [ESC] to close the filter list.
3. If you want to view records with a particular value only, click to uncheck the Select All option, and then check one or more values that you want to view. Click the OK button. (The example above has already been filtered on Product, Delivery month and Customer Type, and is about to be filtered on Discount as well.)
4. All rows that do not contain the value(s) you checked, will be hidden from view. A column that has been filtered will show a funnel icon next to the drop-down arrow on the heading.
5. Repeat the filtering process for as many columns as you need. You can remove a column filter by checking its Select All option.
To clear your previous filter settings, select Sort & Filter, and then Clear. 
To turn off filtering, select Sort & Filter, and then Filter (the same option that you originally used to turn it on).
Note that the Sort function is also available from the Data ribbon.
EXCEL EXERCISE #1: Grade Sheet
1. Enter the information in the spreadsheet below. Be sure that the information is entered in the
same cells as given, or the formulas below will not work.
You will notice that when you enter the information in the first column, the text runs over into the next cell. To adjust the size of the column, once all the information is entered for the first column, click on the column heading (that is the letter A). Then open the FORMAT menu, select the COLUMN options, and then select the AUTOFIT SELECTION command.
2. Enter the formula below into cell G5 and copy it into cells G6 to G8. This demonstrates the
use of a "relative reference" (e.g., C5) that points to the contents of a cell
G5: =c5*.3+d5*.3+e5*.3+f5*.1
Now copy this formula to cells G6, G7, and G8. To do this click on cell G5 to make it the active cell. Then open the EDIT menu and choose the COPY command (a flashing border should now appear around the cell G5). Now click on cell G6 and drag the pointer so the range of cells from G6 to G8 are now highlighted. At this point you need to open the EDIT menu again, but this time selected the PASTE option.. Notice that when you copy this formula into other cells the row numbers for the cells change according to the row into which the formula has been copied.
3. Enter the information below in the cell indicated.
B10: Averages
4. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data.
C10: =(c5+c6+c7+c8)/4
D10: =sum(d5:d8)/4
E10: =average(e5:e8)
5. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average.
B12: Weights
C12: .3
D12: .3
E12: .3
F12: .1
6. Enter the formula below into cell G5 and then copy it into cells G6 to G8. This demonstrates
the use of an "absolute reference" (e.g., $C$12) that points to a specific cell in a spreadsheet.
Notice that when the formula is copied into other cells, the absolute reference remains the same
whereas the relative references change according to the location into which the formula is copied.
G5: =$c$12*c5+$d$12*d5+$e$12*e5+$f$12*f5
7. Make the changes to the cell contents indicated below and notice how the final averages
change.
a. D5: 74
b. C7: 98
c. C12: .25 and E12: .35
d. E12: .30 and F12: .15
8. Just when you thought you were finished calculating final grades, you realize that you forgot
someone. You know, that quiet student that always sits in the back of the room. Anyhow, you
can start all over or simply insert a new row for the forgotten student.
a. Move the cursor to row 6 and click once (on any cell in this row or the row heading).
Open the INSERT menu, select ROWS option. Notice how a new row is inserted after
row 6. Also, check the formulas entered into cells D11, E11, G5, G7, G8, and G9 have
now all changed to accomodate the newly insert row.
b. Now that an additional student has been added to your grade book, the formulas used
to calculate the averages for Exams #1 and #2 are incorrect (this is because these formulas
still assume only four grades are to be averaged. To correct this, copy the formula in cell
E11 to cells C11 and D11.
c. Enter the information below in the identified cells.
A6: Linder, Barry B6: 993-14-9283 C6: 81 D6: 73 E6: 83 F6: 65
d. Notice that the exam averages change when the new student's grades are entered but a
final average is not automatically calculated for him. This is because the formula was not
copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll
is completed.
Your final spreadsheet should look like the one below.
9. IF statements can be used to automatically assign letter grades to each student.
a. Enter the following formula.
H5: =if(g5>89,”A”,if(g5>79,”B”,if(g5>69,”C”,if(g5>59,”D”,”F”))))
b. Copy the formula in cell H5 to cells H6 through H9.
c. The IF command evaluates the first logical test (i.e., G5>89). If the statement is true an
“A” is assigned. If the statement is false, the next logical statement is evaluated (i.e.,
G5>79). To place an IF statement inside another IF statement is referred to as “nesting.”
Excel allows up you to nest up to seven IF statements.
10. To save the grade sheet onto your diskette, open the FILE menu, select the SAVE AS
command, and enter the following name at the blinking cursor in the shaded box:
A:GRADESHT.XLS
QUESTIONS
