Printing the worksheet

PRINTING THE WORKSHEET

Printing the spreadsheet is one of the most important tasks that you do in Excel, probably after saving the worksheet. Fortunately, Excel makes it easy to produce professional-looking reports from your worksheets.

In previous versions of Excel, it is difficult to know how many printed pages it would take to print a spreadsheet unless you switch to Print Preview. It is only when you use Print Preview that you discover that the last column of your worksheet will print on the next page. Also, you cannot create or even see your worksheet's headers and footers in Normal View,

Excel 2007 adds a new view called Page Layout, which allows you to see how your worksheet will print and lets you add a header and/or footer also.

To see your spreadsheet as it would look on the printed page, follow these stepe

1. Click the View tab.

2. Click the Page Layout button in the Workbook Views section.

As you can see in figure, when you switch to Page Layout view, Excel adds horizontal and vertical rulers to the column letter and row number headings.

You can use the Excel 2007 Print Preview feature before you print any worksheet, section of worksheet, or entire workbook. Print Preview shows you exactly how the worksheet data will be paged when printed and you can make last minute changes to the page settings or even the spreadsheet contents before sending it to the printer when everything looks fit to be printed.

To switch to Print Preview, click Microsoft Office button Print Print Preview option.

You can press CTRL + F2 as the shortcut key to switch to the Print Preview mode. The Print Preview of a worksheet is shown in the figure below:

Excel displays the first page of the report in a separate window. When positioned over the spreadsheet, the mouse pointer becomes a magnifying glass. If you wish to read the data in the page shown in print preview, click on the text with the magnifying glass pointer.

The Print Preview comes with a special toolbar shown below:

Various buttons in the Print Preview toolbar do the following:

Print: Opens the Print dialog box to help you configure the print settings before you print the worksheet.

Page Setup Opens the Page Setup where you can change the paper size, set margins, orientation, align the pages of the worksheet, set headers and footers, set page order, etc

Zoom:

Changes the zoom level of the worksheet in preview.

Show Margins:  Select this check box to display markers for the current top, bottom, left, and right margins that you can then adjust by dragging them.

Close Print Preview:

Closes the print preview window to return to the original worksheet.

You can also press ESC for this.

The second section will inform you about the Analysis ToolPak that you can install on your computer for easy data and statistical analysis.

 LOOKUP FUNCTIONS

A lookup function essentially returns a value from a table in a range by looking up another value. A common telephone directory provides a good analogy. To find a person's telephone number, the name must be located, and then the corresponding number can be retrieved. This is essentially how a lookup function works.

The above figure shows a simple worksheet that uses several lookup formulas. This worksheet contains a table of employee data (named EmpData), beginning in row 7. When you enter a last name into cell C2, lookup formulas in D2:G2 retrieve the matching information from the table.

The following lookup formulas use the VLOOKUP function:

Cell

D2

E2

F2

G2

Formula

=VLOOKUP(B2.EmpData,2,FALSE)

VLOOKUP(B2 EmpData,3,FALSE)

=VLOOKUP(B2, EmpData 4,FALSE)

=VLOOKUP(B2,EmpData,5,FALSE)

This particular example uses four formulas to return information from the EmpData range. In many cases, you'll only want a single value from the table, so use only one formula.

Functions Used in Lookup Formulas

Function

Description

VLOOKUP

Vertical lookup. Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table.

HLOOKUP

Horizontal lookup. Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table

LOOKUP

Returns a value either from a one-row or one-column range. Another form of the LOOKUP function works like VLOOKUP but is restricted to returning a value from the last column of a range

 THE LOOKUP FUNCTION

In Microsoft Excel, the LOOKUP function returns a value from a range (one.row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function

Method - 1

In method -1, the LOOKUP function searches for value in the lookup range and returns the value in the result_range that is in the same position.

The syntax for the LOOKUP function is:

LOOKUP(value, lookup_range, [result_range]) value is the value to search for in the lookup_range.

lookup_range is a single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.

result range is optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, the LOOKUP function will return the first column of data.

Note

If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.

If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Worksheet Function Example

Let's take a look at an example to see how you would use the LOOKUP function in a worksheet:

Based on the Excel spreadsheet above, the LOOKUP function would return the following:

-LOOKUP(10251, A1:A6, B1:B6) would return "Pears"

-LOOKUP(10251, A1:A6) would return 10251

-LOOKUP(10246, A1:A6, B1:B6) would return #N/A

-LOOKUP(10248, A1:A6, B1:B6) would return "Apples"

Method-2

In method 2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

The syntax for the LOOKUP function is:

LOOKUP (value, array)

value is the value to search for in the array. The values must be in ascending order.

array is an array of values that contains both the values to search for and return.

Note

If the LOOKUP can not find an exact match, it chooses the largest value in the array that is less than or equal to the value.

If the value is smaller than all of the values in the array, then the LOOKUP function will return #N/A.

 If the values in the array are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Let's take a look at an example to see how you would use this function in a worksheet:

=LOOKUP("T", ("s","t","u","v":10,11,12,13))

would return 11

=LOOKUP("Tech on the Net", ("s","r","u","v":10,11,12,13))

would return 11

=LOOKUP("t", ("s","l","u","v":"a","b","c","d"})

would return "b"

=LOOKUP("T", ("s","t","u","V":"a","b","c","d"})

would return #N/A

=LOOKUP(2, (1,2,3,4,511,512,513,514))

would return 512

In cell F1, we've placed the following formula:

=LOOKUP("CB2", A2:A5,D2:D5)

And yes, even though CB2 exists in the data, the LOOKUP function returns 0.

Now, let's explain what is happening. At first, it looks like the function isn't finding CB2 in the list, but in fact, it is finding something else. Let's fill in the empty cells in D3:D5 to explain better.

If we place the values TEST1, TEST2, TEST3 in cells D3, D4, D5, respectively, we can see that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is it returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in ascending order.

If you look closer at column A, it is not in fact sorted in ascending order. If we quickly sorted column A, it would look like this:

Now the LOOKUP function correctly returns 3A when it is looking up CB2 in the data. To avoid these sorting problems with your data, we recommend using VLOOKUP function in this case. Let's show you how we would do this. If we changed our formula below (but left our data in column A in the original sort order):

The following VLOOKUP formula would return the correct value of 3A.

=VLOOKUP( " CB2",$A$2:$D$5,4, FALSE)

The VLOOKUP function does not require us to have the data sorted in ascending order since we used FALSE as the last parameter which means that it is looking for an exact match.

 HLOOKUP FUNCTION

In Microsoft Excel, the HLOOKUP function searches for value in the top row of table_array and returns the value in the same column based on the index_number.

Syntax

The syntax for the HLOOKUP function is:

HLOOKUP(value, table_array, index_number, [not_exact_match])

value is the value to search for in the first row of the table_array.

table_array is two or more rows of data that is sorted in ascending order.

index_number is the row number in table_array from which the matching value must be returned. The first row is 1.

not_exact_match is optional. It determines if you are looking for an exact match based on value Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the HLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, HLOOKUP will return an approximate match.

Note

If index_number is less than 1, the HLOOKUP function will return #VALUE!.

If index_number is greater than the number of columns in table_array, the HLOOKUP function will return #REF!

If you enter FALSE for the not exact match parameter and no exact match is found, then the HLOOKUP function will return #N/A.

Worksheet Function Example

Let's take a look at an example to see how you would use the HLOOKUP function in a worksheet:

Based on the Excel spreadsheet above, the HLOOKUP function will return the following:

=HLOOKUP(10251, A1:G3, 2, FALSE)

would return $16.80

=HLOOKUP(10251, A1:G3, 3, FALSE)

would return 6

=HLOOKUP(10248, A1:G3, 2, FALSE)

would return #N/A

=HLOOKUP(10248, A1:G3, 2, TRUE)

would return $14.00

https://www.youtube.com/@KrishnaDubaiMotivation

https://www.facebook.com/share/1H9PPi8tMX/

https://www.instagram.com/officialkrishnadubai?igsh=MXY1eDJiY3owOGtiYQ==

https://wa.me/message/ONUZUUV4Q2YGO1

For corporate Inquiries:

Call Us: +91 9262835223