MS-EXCEL
MS-EXCEL
First test: pages 1-11
Second test: pages 12-24
Final test 1-30
: Cell reference
In Excel, a cell reference identifies the location a cell or group of cells in the spreadsheet. Sometimes referred to as a cell address, a cell reference consists of the column letter and row number that intersect at the cell's location. Note that when listing a cell reference, the column letter is always listed first.
Cell references are used in formulas, functions, charts, and other Excel commands.
Such as: D47, R87, E45:E77
Relative cell reference:
In Excel and other spreadsheets, a relative cell reference identifies the location of a cell or group of cells.
Cell references are used in formulas, functions, charts , and other Excel commands.
By default, a spreadsheet cell reference is relative. What this means is that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the function's new location.
In contrast, an absolute cell reference does not change when it a formula is copied and pasted to other cells.
A relative cell reference consists of the column letter and row number that intersect at the cell's location.
5An example of a relative cell reference would be C
454, G15, or Z
Absolute cell reference
In Excel and other spreadsheets, an absolute cell reference identifies the location a cell or group of cells.
Cell references are used in formulas, functions, charts , and other Excel commands.
An absolute cell reference consists of the column letter and row number surrounded by dollar signs ( $ ).
An example of an absolute cell reference would be $C$4, $G$15, or $A$345.
Note: An easy way to add the dollar signs to a cell reference is to click on a cell reference and then press the F4 key on the keyboard.
An absolute cell reference is used when you want a cell reference to stay fixed on a specific cell.
This means that as a formula or function is copied and pasted to other cells, the cell references in the formula or function do not change.
By contrast, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells.
FUNCTION WIZARD PROJECT
In this section you’ll learn how to activate the FUNCTION WIZARD, use the FUNCTION WIZARD to find Excel’s inbuilt functions, and how they can help you.
You can avoid having to remember the complex syntax for each function, and to find out more about functions you come across in spreadsheets.
FUNCTION WIZARD is Excel’s ways of helping developers and users understand and use functions.
To activate
❖ you can either click on this fx button or
❖ you can say Insert
❖ Function,
❖ when you click on that a menu similar to this will appear, depending on the version of Excel you have,
❖ now what you’ll have here is a couple of categories, and all the functions that sit in there.
❖ So for example if you click on Look up &Reference all the functions that exist in that section will pop up and you can look through this and actually by looking at the description see if it meets what you need,
I highly recommend you have a look at these every now and again, or look at our website and get some training material on some of the more useful functions.
How this helps as well is, if you have a spreadsheet with a function in it that you don’t fully understand, you can actually backtrack into the FUNCTION WIZARD to find out what is actually happening. So for example
❖ if we click on this cell, we’ll see the function is a simple IF function,
❖ if you click on the FUNCTION WIZARD in there, up pops up the IF function arguments and it explains that that is the logical tests happening,
❖ if its true that’s what’s happening
❖ and if it false that’s what’s happens and it’ll explain what the If function does,
Now lets say you understand the If function, but you don’t know what this ABS does.
❖ Click in the ABS itself
❖ click on the FUNCTION WIZARD
❖ and up will pop that function argument.
So there’s the cells being used, but it tells you that ABS returns the absolute value of a number - that is a number without its sign. So you can quickly understand what is happening and look into it.
Now different versions of Excel handle that slightly differently. What you might find is that on your version the way it’ll do it is
❖ once you’ve clicked in it
❖ you’ll be able to click over there
❖ and there’ll be a little fx next door to it ,and you can click it again, all depends on the version
But this allows you understand any function that is used in a spreadsheet, and so you don’t have to worry about running back to the developer to learn what functions they have actually used
For saving your file from file select save as:**
Save: for save your file then select location your file on the hard disk then press saves.
You can change type of your file from the save as type on the drop down list at the same screen(save as)
**header and footer:
To add header or footer for any excel sheet:
From file select page setup, then from the sub menu for page setup select header or footer. then press custom header button then screen will be appear which allow you to insert any thing like text, image, page number, date, time, path and file name.
You can do the same thing if you want to add a footer.
**to select type of your page portrait or landscape:
From main menu then file then page setup then page then portrait/landscape
**to define margins
Main menu then file then page setup then margins
**insert chart:
Main menu then insert then chart then select type of your chart then next then select data range then finish
-now you can move the chart to any spreadsheet by cut and then paste
**insert picture:
Main menu then insert picture from file then go to location, the picture on the hard disk then open
**format cells:
Highlight the cells right click then format cells or direct from formatting toolbar.
**page break:
Main menu then insert then page break
**change cell name:
Highlight the cell then main menu then insert then name then define then write new name then press OK
**hyperlink:
Highlight the text then insert then hyperlink then write the address then ok
**protection for sheets or book:
Main menu then tools then protection then protect then sheet or book then type your password.
**insert diagram:
Insert then diagram then select diagram type then ok
**hide (cells, rows, columns, sheets):
From format then select one of them then hide, but you have to highlight it first.
Verify your data entry
*validation rules in excel:
1. Highlight cells which you want to add rules for them
2. Select the data menu then validation …this will open the data validation window.
3. The rule is set in the setting tab
4. Use drop down menu in the allow: how to select the type of data to allow. for example: if you are going to enter numbers between 0.8 and 2.5 select the decimal from this menu
5. Select between from data: drop down menu if it is not already visible.
6. Set the minimum value to 0.8 by typing this value
7. Set the maximum value to 2.5 by typing this value
8. If you wish to give the user information about the data to be entered in this cell, use the input message.
9. To enter an error alert, if the user enters the wrong data, select the error alert tab.
10.
Enter stop as a validation style this will prevent invalid data being entered into these cells.
11. Eneter the title for this error message.
12. Enter the contents of the error message
13. Click on ok to enter these setting for validation
Spreadsheet - Excel
Functions In MS-Excel:
1. Sum function: finds the total for a numeric range of cells.
Data in these cells should be numeric. EX:- =sum(A1:A10)
2. Average function:- finds the average for a numeric range of cells. Data should be numeric. EX:- =average(A1:A10)
3. Maximum function: finds the maximum value between a numeric range of cells. EX:- =MAX(A1:A10)
4. Minimum function: finds the minimum value between a numeric range of cells. EX:- =MIN(A1:A10)
5. Count function: counts a numeric range of cells.
Suppose we want to count the number of numeric entries in the range C3 to C30. we can use =COUNT(C3:C30). Any blank lines or text entries in the range are not counted.
6. Counta function: to count a number of items or names of people
(not numeric entries as the count function) we need to be able to count text entries. To do this we can use =COUNTA(C3:C30) you need to make sure that heading are not included in the range so that they are not counted as well. Again blank lines are not included.
7. Countif function:
Counts the number of cells within a range that meet the given criteria. COUNTIF(range,criteria) Range is the range of cells from which you want to count cells.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted.
For example, criteria can be expressed as 32, "32", ">32", "apples".
[pic]
[pic] [pic]
[pic][pic] [pic]
❖ [pic] [pic]
Function Wizard
In excel there is a help tool for functions called the function wizard.
There are two ways to get the function wizard. If you look at the standard toolbar, the function wizard icon looks like the icon on the right.
The other way to get to the function wizard is to go to the menu INSERT -- down to FUNCTION.
How to Use Excel Filtering to Find Data Fast:
Learn how to use Excel’s AutoFilter and free yourself up for other tasks.
Many of us find Microsoft Office Excel spreadsheets very useful for compiling information about customers, products, sales revenues and other types of data. But when the volume of data in a single worksheet grows to fill dozens of columns or rows, sorting through it can be a challenge. If you want to isolate, for example, your top 10 customers in a particular region over the last six months, you might spend a long time reviewing your data entries.
Fortunately, Excel includes an easy-to-use AutoFilter to show just what you want to see and hide the rest. Filtering doesn't change your data in any way. As soon as you remove the filter, all your data reappears, exactly the same as it was before.
Here's how to use the AutoFilter tool in Excel.
1. Make Sure the Data Type Is the Same in Each Column
In your worksheet, the top row of each column should have a heading that describes the contents of the column, such as "Product Number" or "Customer."
[pic]
The data in each column should all be the same type. For instance, do not mix text in a column with numbers, or numbers in a column with dates.
2. Activate the AutoFilter
Now click inside any data cell and activate the AutoFilter by doing the following:
|• |In Office Excel 2003, click the Data menu, point to Filter, and then click AutoFilter. |
|• |In Office Excel 2007, click the Data tab and in the Sort & Filter area click Filter. |
The AutoFilter arrows now appear to the right of each column heading.
[pic]
Note: If you select an entire column instead of a single cell before clicking the AutoFilter command, an AutoFilter arrow will appear only on the selected column, not on all columns of the data.
3. Start Filtering Data
Suppose your worksheet contains customer sales data. Each customer entry includes information about the customer's location, products they purchase, purchase dates, and revenues and profits from each purchase. Perhaps you want to view sales activity only for those customers located in the West region. Excel can help you do this.
To view only the sales activity from customers in the West region, you click the AutoFilter arrow in the column with the Region heading. When you click an AutoFilter arrow, a list is displayed. The list contains each of the items in the column, in alphabetical or numeric order, so that you can quickly find the item you want. In this instance, you scroll to West, and click it.
[pic]
When you click West, Excel hides all the rows on the worksheet except for those that contain that text in this column.
[pic]
4. Apply Additional Filters
If you want to focus on even more specific information, you can filter again on another column, and then again on another column, and so on. You can click the arrow next to any heading in any column to apply a filter.
After filtering by Region, for example, you can click the AutoFilter arrow on the Product Number column and filter that column to see only the West region customers who purchased product number 12-100.
[pic]
You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.
5. Use Advanced Filtering Techniques
Excel also enables you to perform more intricate types of filtering. Two particularly useful types are the Top 10 filter and custom filtering.
Finding the Top (or Bottom) 10 in a column
You can use the Top 10 filter on columns of numbers or dates. With Top 10 you can find either the top items or the bottom items (the smallest or largest numbers or dates). And you're not limited to finding the top 10 items or the bottom 10 items. You can choose how many items you want to see: only 1 or as many as 500. You can use Top 10 to find the highest-priced or lowest-priced products, to identify employees with the most recent hire dates, or to see the top or bottom student grades.
To use Top 10 on a column of data in Excel 2003, click on a data cell in the column and then click the column's AutoFilter arrow.
|• |In Excel 2003, click (Top 10…) near the top of the drop down list. |
|• |In Excel 2007, click Numbered Filters and then select Top 10. |
The Top 10 AutoFilter dialog box opens. In the dialog box, select either Top or Bottom. Then select a number. Finally, select either Items or Percent.
You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.
Using custom filters
When you filter by choosing from the AutoFilter drop-down list, you hide everything except your single choice. If you want to see more than one selection in a column, you can create custom filters.
To create a custom filter,
|• |In Excel 2003, click (Custom…) near the top of the drop down list. |
|• |In Excel 2007, click Numbered Filters and then click Custom Filter. |
The Custom AutoFilter dialog box opens. You can now enter two filtering requirements for the column of data. For example, you could see customers who purchased product numbers 12-100 and 12-500.
Note: Be sure to select the Or button or you will see no results at all.
[pic]
6. Turn Off Filtering
How you remove filters depends on how many filters you have applied, and from how many columns you wish to remove filters.
|• |To remove a filter from one column, click the AutoFilter arrow next to that column, and then click All. That command will |
| |display the rows hidden by that filter. |
|• |To remove all filters at once, point to Filter on the Data menu, and then click Show All. That command will display all |
| |rows hidden by all filters on the worksheet but leave AutoFilter turned on. |
|• |To turn off AutoFilter, point to Filter on the Data menu, and then click AutoFilter. |
|• |To remove the filters from your spreadsheet, simply deselect AutoFilter (Excel 2003) or Filter (Excel 2007) on the Excel |
| |menu. All the data in the spreadsheet is revealed again. |
How to create charts:
1. To create a chart, you first need to select the cells you wish to be contained within that chart. To do this, left-click and hold the mouse button, then drag the cursor over the groups of cells you wish to include. Then let go of the mouse button, and the cells will remain highlighted.
2. Step 2
Access the chart tool. Scroll to the “Insert” tab on the command bar and select “Charts.”
3. Step 3
Select the chart for your spreadsheet. A Charts Properties menu will open, in which you can select the style of the chart you want from a drop-down menu. Select the desired chart and click “Next” to continue.
4. Step 4
In the next menu, you can specify the specific columns and rows to include in the chart. If you have already highlighted the cells and columns from Step 1, you can click “Next” to continue; otherwise, type in the series of charts and rows by entering their corresponding column letters and row numbers.
5. Step 5
You can label the chart, name the fields and adjust the categories, gridlines and table information simply by selecting the corresponding tab and entering the required information into the fields. Click the “Next” button to continue after making the desired entries.
6. Step 6
Select where to place the chart. Choose by selecting the corresponding radial button to place the chart in a new sheet or inside of the current sheet; after making your selection, click on the “Finish” button to implement your completed chart.
Data ⋄ Text to Columns
We've learned how to put two names together. However, it seems that even more than doing that, people need to split names up. Perhaps so they can do a mail merge to Dear John instead of to Dear John Smith. Here's how we separate them, which works in many, but not all cases. If nothing else, it can make a really long manual chore take a whole lot less time.
Place your cursor on the H and click to select the entire column H. Go to the menu and choose Data⋄Text to Columns.
[pic]
Choose Delimited and hit Next. Then tick the Space checkbox. This tells Excel that spaces are what separates the data. Note the data preview. Hit Finish.
[pic]
Warning: If you have data in the column(s) to the right when you use this feature, you could wipe it out.
See the result.
[pic]
Save the file.
[pic]Learning VLOOKUP in Excel
One of the popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task. (Includes Excel VLOOKUP Example file)
A recent case involved some voter registration data I needed to analyze. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called "Pcode" and not the political party. This coding wasn't intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.
[pic]
One way to solve this problem is to create a worksheet with the Pcode and translation and have Excel use the VLOOKUP function for the party name. You might think of VLOOKUP as an Excel translator. I could then add a column called “Political Party” to my original worksheet to show the information from a lookup table.
Creating a Lookup Table
A lookup table includes the values you wish to "lookup" such as our Pcode and the translation such as political party. You can place this table on the same worksheet, but for this Excel tutorial I'll add a worksheet called "Political Party".
How to Create a Lookup Table,
1. Right-click your spreadsheet’s tab and select Insert…
2. On the Insert dialog, double-click Worksheet. This will be on the General tab.
3. Rename this new worksheet tab with a descriptive name such as “Party Codes”
4. In Column A, enter the unique values that exist on your main worksheet. In my example, these were the codes that showed in the Pcode column in the thumbnail. These values should be in ascending order.
5. In Column B, enter the translated value. You can have more values in column A than appear on your main spreadsheet. For example, I have an entry for “Citizen Party” even though I didn’t show a registered voter with that affiliation.
[pic]
Using the VLOOKUP Function
Excel’s VLOOKUP function uses 4 pieces of information. The function panel may seem intimidating with the terms, but it’s simpler than it looks.
To lookup a value using VLOOKUP,
1. Add your new column on your original worksheet that will display the info pulled from the Lookup table. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.
[pic]
2. Place your cursor in the first blank cell in that column. In my example, this is cell D2.
3. From the Insert menu, select Function…. The Insert Function dialog will appear.
[pic]
4. In the Search for a function: text box, type “vlookup” and click Go.
5. Highlight VLOOKUP and click OK.
Defining the VLOOKUP Values
After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values. You’ll see that your starting cell and the formula bar show the beginning part of the function =VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().
For illustration purposes, I have overlaid the Party Codes worksheet on top to show the relationships.
[pic]
1. Lookup_value – Think of this field as your starting point. In my example, I’ll click cell C2 so the value is filled in the dialog. I'm requesting Excel take the value of C2, which displays as the Pcode of “A”, and find the matching political party on my lookup table on the Party Codes worksheet.
2. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as ‘Party Codes’.
Rules & Caveats
There are several rules to remember about this table array.
Rule 1 - The left column must contain the values being referenced. In other words, I couldn’t have our first column be Political Party.
Rule 2 - You can’t have duplicate values in the leftmost column of the lookup range. I couldn’t have two entries with the value “A” with one being “Democratic” party and another “A” for the “Humanist” party. Excel would complain.
Rule 3 - When referencing a lookup table, you don’t want your cell references to change when you drag and fill to populate the other cells with the VLOOKUP function. As example, if I want to use the same function in cells D3 through D7, I don’t want my lookup cell references to shift each time I move down to the next cell. I need the cell references to be the same. After you define your range, you need to press F4 which will cycle through absolute and relative references. You want to select the option that includes a $ before your Column and Row. ( 'Party Codes'!$A$2:$B$45. ) You can get around this if you know how to use Excel name ranges.
Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Party Codes worksheet which has the name of the political party.
Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we’ll use “FALSE”.
After clicking various cells, my dialog looks like this:
[pic]
You can see in the circled formula bar above, I now have more information based on my entries in the Function Arguments dialog box.
The other item of interest is that when you build these functions, Excel displays the result in the Formula result = text line. This is great feedback which can show if your function is on target. In our example, we can see Excel looked up the Pcode of “A” and returned the Political Party “Democratic”.
Copying the VLOOKUP Function to Other Cells
It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, I want to copy the function to other cells in the same column.
To copy VLOOKUP to other column cells,
1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.
2. Grab the cell handle that displays in the lower right corner.
3. Left-click and drag down the cell handle to cover your column range.
Note: If I hadn’t changed to absolute reference as mentioned in Rule 3, I would’ve seen my table array entry shift by one cell as we dragged down through the other cells.
VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your Excel formulas.
How to protect parts of the spreadsheet:
1. Highlight this part (cells)
2. Select the format menu then cells …this will open the format cells window
3. Select protection tab.
4. Tick the locked check box then press ok
**How to protect worksheet
Select the tools menu followed by protection then protect sheet…then tick the check box to add any votes.
Printing in Excel 2003 - Overview
[pic]
Excel Printing - Overview
Printing in Excel is a bit different than printing in some other programs, such as a word processor. One of the main differences is that Excel has five locations in the program that contain print-related options.
The first, the Print button, is located on the standard toolbar. The other four of these are listed under the File menu:
❖ Print Preview
❖ Page Setup
❖ Print Area
❖ Print
[pic]Excel Printing using the Print button
If you want to quickly print out the worksheet that is open in the Excel screen, you can do so by using the Print button on the standard toolbar. Clicking on this button sends the active worksheet directly to the printer, using the current print settings.
Use this button when you want to print a single copy of a workbook. Just realize that, unless you have previously set a Print Area, you are going to get all the sheets in the workbook that contains data, not just the sheet that is on the screen
[pic]
Excel Printing Using Print Preview
Before printing all or part or an Excel spreadsheet, it might be a good idea to have a look at what is going to get printed – just in case it’s not what you expect – or really want. Print Preview displays the current worksheet in the preview window. It shows you how the worksheet will look when it is printed.
Print preview toolbar
Some of the more commonly used options on this toolbar are:
❖ Next and Previous buttons – Show you all the pages that will be printed.
❖ Zoom – Allows you to zoom in on specific parts of the worksheet.
❖ Setup – Opens the Page Setup dialog box,
❖ Print – Opens the Print dialog box.
Excel Page Setup Dialog Box – Page T
[pic]
Printing in Excel - Using the Page Setup Dialog Box
The page tab in the Page Setup Dialog Box has three areas of printing options.
1. Orientation – Allows you to print sheets sideways (Landscape view). Very useful for spreadsheets that are just a bit too wide to print using the default portrait view.
2. Scaling – Allows you to adjust the size of the worksheet you are printing. Most often used for shrinking an Excel worksheet to fit on fewer sheets or magnifying a small worksheet to make it easier to read.
3. Paper Size and Print Quality
➢ Paper size – is adjusted most often to accommodate larger worksheets such as changing from the default letter size (8 ½ X 11 inches) to legal size (8 ½ X 14 inches).
➢ Print quality – has to do with the number of dots per inch (dpi) of ink that are used in printing a page. The higher the dpi number, the higher the quality the print job will be.
Excel Page Setup Dialog Box – Sheet Tab
[pic]
Excel Printing - Using the Page Setup Dialog Box (con't)
The Sheet Tab of the Page Setup Dialog Box has four areas of printing options.
1. Print Area – Select a range of cells on the spreadsheet to print. Very useful if you are interested only in printing a small section of the worksheet.
2. Print Titles – Used for printing certain rows and columns on every page - usually headings or titles.
3. Print- The most commonly used options are -
➢ Draft quality – Prints a quick, low quality draft copy.
➢ Gridlines – For printing the worksheet gridlines – making it easier to read data on larger worksheets.
➢ Row and column headings – Prints the row numbers and the column letters down the side and across the top of a each worksheet.
1. Page order – Changes the order for printing pages on a multiple page spreadsheet. Normally Excel prints down the worksheet. If you change the option, it will print across
Setting and Clearing a Print Area
[pic]Excel Printing - Setting and Clearing a Print Area
By default, Excel prints the entire worksheet. One way to stop this is to set a print area, which specifies the exact range of cells to print.
To set a print area –
1. Drag select the group of cells you want included in the print range.
2. Choose File > Print Area > Set Print Area from the menu.
3. Choose File > Print Preview from the menu to check that the print area is set correctly.
To remove a print area -
1. Choose File > Print Area > Clear Print Area from the menu.
2. Choose File > Print Preview from the menu to check that the print area has been cleared.
The Print Dialog Box
[pic]
Excel Printing - The Print dialog box
The four main option areas in the Print dialog box are:
1. Printer – Allows you to choose which printer to print from. To change printers, click on the down arrow at the end of the Name box and chose from the printers listed in the drop down menu.
2. Print range
➢ All – The default setting – refers only to pages in the workbook containing data.
➢ Pages – List the start and end page numbers for those pages to be printed.
1. Print what?
➢ Active Sheet – The default setting - prints the worksheet page that was on screen when the Print dialog box was opened.
➢ Selection – Prints a selected a range on the active page.
➢ Workbook – Prints pages in the workbook containing data.
2. Copies
➢ Number of copies – Set the number of copies to be printed.
➢ Collate – If printing more than one copy of a multi page workbook, you can choose to print copies in sequential order.
How to create Form with MS excel:
1. Show form tool bar. (Go to view then toolbars then Form).
2. Click on what you want from form elements then draw rectangle on working area.
The End
[pic][pic][pic][pic][pic][pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.