Excel2010

Questions

1.Which of the following options is not located in the Page Setup dialog box?

a.Page Orientation

b.Margins

c.Header / Footer

d.Page Break Preview

2.How do you set a Print Area, so that Excel prints only that part of the worksheet?

a.Select the area then select the Office buttonPrint Quick Print.

b.Select the area then click Print Area Set Print Area button on the Page Layout Tab.

c.Select the area you want to print then click the Print button on the Quick Access Toolbar.

d.There is not a way of doing this.

4.How can you view and/or add a page header to a worksheet? Choose all that apply.

a.Click the Header/Footer button on Office button Prepare.

b.Open the Page Setup Dialog box and click the Header/Footer tab

c.Select Header&Footer from the Text group in the Insert Tab

d.Click the Page Layout button on the status bar then click on “Click to add header” text

5. .................... reduces or enlarges or fits the worksheet to a specific number of pages.

a. Orientation	b. Scaling
c. Paper size	d. Print quality

3.The page break is not at the place you want. How can you fix this?

a.Select the cell where you want to add a page break and then click the Page Break button on the Insert Tab.

b.Click the cell where you want to add a page break and select View tab Freeze Panes.

c.Click the Print Preview button and click the Fit to Print button on the toolbar.

d.Select View Page Break Preview and drag the page break indicator line to where you want.

6. .................. is the number of the dots per inch (dpi) that appears on the printed page.

a. Scaling	b. Margins
c. Resolution	d. Orientation

7. ..................... are the printing limits of the

paper.
a. Scaling	b. Print Quality
c. Measurement	d. Margins

Page Setup and Printing

61

8. ..................... settings should be smaller than the top and bottom margin settings and larger than or equal to the minimum printer margins.

a.Header and footer margin

b.Print area

c.Date button

d.Chart tab

9.If you select ..................... you will expand the chart to fit the full width and height of the page margins.

a. Use full page	b. Scale to fit page
c. Draft quality	d. Printing quality

10.If you have multiple pages to be printed

..................... help you to move through the pages and see them in the Print Preview window.

a.Active sheet(s)

b.Entire workbook

c.Next and previous buttons

d.Zoom button

62	Microsoft Excel

Word Search Puzzle

H P T R A H C D P X D F

E U L A V I J F O G H O

X X L L V Z O U R R E O

K T N I G R A M T I A T

A N D D M M D D R D D E

F E P A C S D N A L E R

E M T T J P N G I I R F

Y M I I P R I N T N C B

O O F O R K U L A E Y N

Y C T M E R G E Z S G X

Words	Clues
LANDSCAPE	Page orientation in which the page width exceeds the page length.
	A function that allows you to add the numbers in multiple cells.
	To be the right size or shape.
	It is used to display series of numeric data in a graphical format.
	One or more lines of text that appear at the bottom of every page of a document.
	To take out a copy of your document on a paper
	The horizontal and vertical lines on the spreadsheet.
	Text that appears at the top of every page of a document when it is printed.
	Area between the edge of a page and the written or printed text.
	The orientation of a page in which the longer dimension is vertical.
	Comparing data with known information (patterns, ranges, check digits) to verify that
	the data is correct.

Page Setup and Printing

63

Project

1.Make a nice looking table which has the names and surnames of your classmates with their grades in Informatics lesson. Then show a print out to your teacher.

2.Find and write down the results of a General Assessment Test in your school in an Excel worksheet.

a.Print the table sorting by classin ascending order and then by averagein descending order.

b.Assuming you have multiple pages, on the top of each page you must have the same title. Solve this problem in this project.

64	Microsoft Excel

FUNCTIONS AND FORMULAS

5.1 Understanding Functions and Formulas

Using formulas is the essential part of Excel. Microsoft Excel is best when you have lots of numbers and different calculations with these numbers. Each box (cell) is like a different address and can be used with its referring address or name.

	To Refer to	Use
	The cell in column A and row 10	A10
	The range of cells in row 1 and columns	A1:E1
	A through E (5 cells)
	The range of cells in columns A through E	A1:E5
	and rows 1 through 5 (5 × 5 = 25 cells)
	All cells in row 5 (A5:XFD5)	5:5
	All cells between rows 5 through 10	5:10
	All cells in column H (H1:H1048576)	H:H
	All cells in columns H through J	H:J

Figure 5.1: A1 Reference Style

The A1 reference style

There are two different reference styles: A1 and R1C1. By default, Excel uses A1 reference style. This refers to columns with letters (A, B, C... XFC, XFD a total of 16384 columns) and rows with numbers (1 through 1.048.576).

These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number.

For example, D2 refers to the cell at the intersection of column D and row 2.

In R1C1 style R and C represent Rows and columns. The number written after R is the Row number and the same for the column. The D2 cell in A1 Reference style means R2C4 in this style.

5.2Linking Worksheets

You can think that “Instead of gathering all the data in a single workbook, why do I need to link worksheets, or workbooks?” There can be many reasons:

To keep well organized workbooks

To have less workbook sizes

To have different security and access levels, etc.

In the ancient times, when there were no computers, we used papers to collaborate with people. Because, we have better network environments now, we don’t have to print hundreds of pages for a project and distribute it to others and wait for all to work and return the pages. Now, we can let all people work on their copies and we have links to appropriate locations, in order to have dynamic and up-to-date information.

The general syntax for a link

=[WorkbookName]SheetName!CellAddress

But, if linking from the same workbook and/or worksheet, you can skip the default parts and write only the cell address after the equal sign. So, if you want to have a link to A1 cell from the same worksheet, in the destination cell, you simply write:

=A1

66	Microsoft Excel

If you want to have a link to the A1 cell in Sheet1 from sheet2, you first write the sheet name followed by an exclamation mark and write the cell address in the destination cell:

=Sheet1!A1

If you want to have a link to the A1 cell in Sheet1 in the ‘Plans.xlsx’ workbook from another workbook, you

first write the workbook name in square brackets,

then, sheet name followed by an exclamation mark and

write the cell address in the end: =[Plans.xlsx]Sheet1!A1

5.2.1 Creating a link formula by pointing

Because, when entering external reference formulas manually, you can easily make errors, usually it’s not the suggested method. Instead, have Excel build the formula for you:

1.Open the source workbook.

2.Select the cell in the dependent workbook that will hold the formula.

3.If you’re simply creating a link and not using the external reference as part of a formula, just enter an equal sign (=) and then select the cell and press Enter. If you are entering a formula, when you get to the part that requires the external reference, activate the source workbook and select the cell or range and press Enter.

4.After you press Enter, you return to the dependent workbook, where you can finish the formula.

Because the references that you create with this method are always absolute references, if you plan to copy it to create additional link formulas, you need to pay attention when using them as absolute or relative references.

As long as the source workbook remains open, the external reference doesn’t include the path to the workbook. If you close the source workbook, however, the external reference formulas change to include the full path.

5.3Writing Your First Formula

To write a formula, you must start with an equal sign “=”. Then, using references, numeric values and arithmetic operators, you can write your own formulas. (You can see the cell reference name in Name Box on the left of Formula Bar).

When the workbook file name has spaces in it, we use single quotation.

='[Daily Plans.xlsx]Sheet1'!A1

Functions and Formulas

67

Example 5.1:

You have a worksheet that lists exams for students. Write a formula for your teacher that calculates each student’s average.

	Name Box			Formula Bar			Active Cell

Figure 5.2: Math exam results

	Operator	Meaning	Example	Result
	+	Addition	=3+2	5
	–	Subtraction	=3-2	1
	/	Division	=3/2	1.5
	*	Multiplication	=3*2	6
		Percentage
	%	(Divides number	=50%	0.5
		by 100)
	^	To the power	=3^2	9
	Figure 5.3.a: Arithmetic Operators
	Operator	Meaning	Example	Result
	=	Equal sign	=3=2	FALSE
	>	Greater than	=3>2	TRUE
	<	Less than	=3<2	FALSE
	>=	Greater than or	=3>=2	TRUE
		equal to
	<=	Less than or	=3<2	FALSE
		equal to
	<>	Is not equal to	=3<>2	TRUE

Figure 5.3.b: Logical Operators

Solution: In order to calculate a student's average, you need to calculate the sum of Exam1, Exam2 and Exam3 and then divide the result by 3.

For the first student, according to Figure 5.2, the cell containing

Exam1 mark is D4;

Exam2 is E4; and

Exam3 is F4.

And you should use these referring addresses in your formulas.

Now, you know all the details and you are ready to write your formula. First, you select the cell where the result will appear (G4) and then write the formula below in the formula bar:

= (D4+E4+F4)/3

After you press the ENTER key, it will display the average of the first student. Now, you can copy this formula to other students. When you copy and paste formulas, Excel will automatically make the necessary changes in formulas for every row and column references. For the second student the Exams are stored in the 5th row and the formula will be = (D5+E5+F5)/3

5.3.1 Operators in Excel Formulas

There are three groups of operators in Excel.

Operator	Meaning	Example	Result
:	Range	A2:C7	All the cells from A2
			to C7
	Union (to define		The cells from B1 to
,	more than one	A1,B1:B6
	reference)		B6 and A1
space	Intersection	=B7:D7	produces reference
		C6:C8	to common cells to
			the two references

Figure 5.3.c: Other Operators

68	Microsoft Excel

Example 5.2

Your math teacher needs some help. He wants you to prepare an Excel sheet in which he will write the students marks. He has two written exams and a final exam. When calculating the semester average, written exams weigh 25 % each and the final exam weighs 50%. Help him prepare an Excel sheet like the following one.

Figure 5.4: Average with Final Exam

Analysis and Solution:

Since you want to calculate the final as 50% of the semester average, you will multiply the Final Exam (E2) by 50 and the other exams by 25. Then the sum of all will be divided by 100 to get the student’s average. In cell F2 write the formula

= (E2*50+C2*25+D2*25)/100

to get the average of the first student. Copy the formula for the other students in the list.

5.3.2 Absolute and Relative Reference

A relative reference is an address that keeps the relative difference from the source to the destination cells. When you copy this formula to another location, Microsoft Excel automatically adjusts the new addresses relatively, according to the new location.

Sometimes, you want an address not to change when you copy it to another location. In such conditions, you add the ‘$’ sign to the front of the column or row reference. Being able to place the ‘$’ sign to the front of a row or a column separately provides flexibility.

Example 5.3

Computing the cumulative sum (year to date) is one of the problems that you might face.

Solution:

As you see in the figure next, we have monthly amounts in column B. And, in column C, we have the year to date amount. In C2 cell, we can use the formula

=SUM($B$2:B2)

In this formula the starting address is an absolute reference, but, final address is a relative one. So, when you copy this formula to other cells in the same row, it’ll keep the start cell the same, but, the final cell will be the next one.

A1 Relative column, Relative row

$A1 Absolute column, Relative row A$1 Relative column, Absolute row $A$1 Absolute column, Absolute row

Figure 5.5: Computing cummulative sum

Because our formula is located in column C, and when copying, we’ll copy again to the same column, we can simply use B$2.

Functions and Formulas

69

Example 5.4

In an Excel worksheet, you have exchange rates and your expenses. Because you are a foreign company in this country, you pay in Euro but all of your budget is built on USD. So, for every payment you convert from Euro to USD. You place exchange rates at the top of the page and you write your formula to convert the payment into USD.

Solution:

You will study more complicated conversions later. For now, we will only explain converting Euro to USD. In this case the USD conversion cell has to be an absolute

Figure 5.6: Using absolute reference reference and will not change from one payment to another: $B$8.

The payment cell has to be a relative address, because, every conversion will take its left cell as a payment. D11 contains the payment for the first operation, D12 for the second payment, and D13 for the third one.

Then the formula for the first payment in column E becomes

=D11 * $B$8

Now your formula is ready.

When you copy this formula to other payments, the USD exchange rate cell will be an absolute reference but the payment cell will be relative and change automatically for every copy.

The formula for the second payment will be come	=D12 * $B$8
The formula for the third payment	=D13 * $B$8
The formula for the fourth payment	=D14 * $B$8

5.4Simple Functions:

Sum, Average, Count, Max and Min

Excel provides a wide range of predefined functions. You will study in this section some basic functions and their uses, and the way to access these functions.

If you click on the small arrow next to the Insert Function button in the Home Tab and then select More functions (Or, if you select Insert Function from the Formulas Tab. Or, press <Shift+F3>), you will see the Insert Function dialog

box.

Figure 5.7: Insert Function Button

70	Microsoft Excel