|
As you work with Excel, you will probably create
worksheets that contain more data than can be viewed all at once on
screen. You also might find that you need to distribute related data among
several worksheets and workbooks. Fortunately, Excel provides several
methods that allow you to work with the content in large worksheets or
with the content spread across multiple worksheets and workbooks. These
methods include assorted viewing options, the ability to add or delete
worksheets in a workbook, and ways to sort and limit the data that’s
displayed.
Opening an existing file.
Download the following document and save it to your pc.
Sports Incomes05
Once it is saved go to the folder containing the document and open it with
Excel
Zoom in and out
In this
exercise, you zoom in and out on a worksheet.
Click cell C7. On the View menu, click Zoom.
The Zoom dialog box
appears.
Click the 200%
option, and click OK.
Each cell of the
worksheet appears twice its original size.
On the Standard
toolbar, click in the Zoom box.
In the Zoom box, type
60, and press Enter.
The worksheet shrinks to
60 percent of its original size.
Click the down
arrow on the Zoom box, and click 100%.
The worksheet returns to
its original size.
Keep this file
open for the next exercise.
Hiding Rows
and Columns
Sometimes you
have more rows or columns in a worksheet than you want
to see at one time. In
such situations, you can hide rows or columns so that
they don’t appear on
your screen or in worksheet printouts. When you
want to see them again,
you unhide them
In this exercise,
you hide and unhide worksheet rows and columns.
Click the column
selector for column D (Feb), hold the Shift key, and click the column
selector for column L (Oct).
Excel selects the
columns.
On the Format
menu, point to Column, and click Hide.
The columns are hidden.
Notice that the column labeling has not
changed and that a dark
line indicates where the hidden columns D
through
L are.
Click the row selector for row 3 (Kayaks). The row is selected.
On the Format
menu, point to Row, and then click Hide.
The row is hidden.
Notice that the row numbering has not changed
and that a dark line
indicates where the hidden row 3 is.
Save the File As
Sports Income051.
Click the column
selector for column C, and drag to the column
selector for column M.
Excel selects
columns C and M.
On the Format
menu, point to Column, and click Unhide.
Columns D through
L are redisplayed.
Click a blank
area of the worksheet outside of the selected area.
The columns are
deselected.
Click the row
selector for row 2, and drag to the row selector for
row 4.
Rows 2 and 4 are
selected.
On the Format
menu, point to Row, and then click Unhide.
Row 3 is
redisplayed.
Close the workbook without saving your changes
Freeze and Unfreeze rows and Columns
When your worksheet is larger than you can display on-screen at once,
you need to scroll right and down to see all of your columns and rows. If
your leftmost column and top row contain labels, scrolling can make the
labels disappear off the edge of your screen—leaving you to wonder what
exactly is in the cells you’re seeing.
Download the following document and save it to your pc.
Food
Once it is saved go to the folder containing the document and open it with
Excel
In this exercise,
you freeze and unfreeze rows and columns.
Scroll the
worksheet to the right and then back to column A.
When you scroll the
worksheet to the right, the leftmost columns disappear.
Click cell B3.
This cell is just
below the row you want to freeze and just to the right
of the column you want
to freeze.
On the Window
menu, click Freeze Panes.
The month row and Type
of Food column are now frozen.
Scroll the worksheet to
the right. The
leftmost column, with the category labels, remains visible on the screen.
Scroll down the
worksheet. The
month row remains visible on the screen.
Save the file as
Food Freeze.
Press Ctrl+Home.
Excel scrolls to
the top left unfrozen cell.
On the Window menu, click Unfreeze Panes. The panes are unfrozen.
Keep the file open for the next part.
Managing Worksheets in a Workbook
Storing related worksheet data in one workbook file has many
organizational benefits. By using this method of organization, you can
open all associated worksheets in a workbook at once, which saves time and
ensures that all necessary data is available for a specific task. This
also lets you quickly view related information and copy necessary data
from one worksheet to the next without having to open and close various
workbooks.
Moving between Worksheets
Click the Dry
Goods tab. The
Dry Goods worksheet is displayed.
Click the Produce
tab. The Produce
worksheet is displayed.
Click the Meats
tab. The Meats
worksheet is displayed.
Adding and Deleting Worksheets
Click the
Produce tab. The
Produce worksheet is displayed.
On the Insert
menu, click Worksheet.
A new worksheet named
Sheet1 is inserted to the left of Produce.
Select any cell,
type Test, and press Enter.
On the Edit menu,
click Delete Sheet.
An alert message box
opens.
Click Delete.
The new worksheet
is deleted, and the Produce worksheet is redisplayed. |
|
Click
the Meats tab. The Meats worksheet is displayed.
On the Insert menu, click Worksheet.
A new worksheet named Sheet2 is inserted to the left of Meats.
Double-click the Sheet2 tab. Type
Summary,
and press Enter. The name of the worksheet is changed.
Moving and Copying Worksheets
Just as you
can move and copy data in a worksheet, you can move and copy the
worksheets within a workbook. For example, you might want to move a
worksheet in order to change the order of sheets in the workbook. You
might want to copy a worksheet so that you can overwrite existing data
with new data in the copy. When you copy the worksheet, you retain the
structure and formatting of the original so that you don’t need to
“rebuild” it from scratch.
The chef at Adventure Works wants the Summary worksheet that was just
added to appear as the last sheet in the workbook instead of the first. He
also wants the workbook to include a worksheet for beverages. He decides
to copy one of the existing sheets so that he can simply overwrite the
data with the new beverage data.
In this exercise, you move a worksheet to another location in the workbook
and make a copy of a worksheet.
Click the Summary sheet tab, press and hold the mouse button, and drag
until the small, black sheet insertion arrow is at the right corner of the
Produce tab. The Summary sheet is moved from its position as the
first sheet to follow the Produce sheet.
Click the Produce sheet tab, hold the Ctrl key, and drag until the sheet
insertion arrow is between the Produce and the Summary sheet tabs.
A copy of the
Produce sheet, named
Produce (2),
is inserted between the Produce and Summary worksheets
Double-click the
Produce (2) sheet tab, type Beverages, and press Enter. The copied sheet
is renamed.
Save the file as
Food and Close
the file.
Sorting Data
Open Sports Income05 from the directory you downloaded it to.
In this exercise, you sort equipment by the cost per rental and by the
yearly revenue per type of equipment.
Select the range A2:P17. This is the list you will sort.
On the Data menu, click Sort to open the Sort dialog box. Click the down
arrow on the Sort By text box, and select Price per Rental. Click OK. The
data is sorted from lowest to highest price per rental
On the Standard toolbar, click the Undo button.
The rows of data are returned to their original order.
Select the range A2:P17.
On the Data menu, click Sort. The Sort dialog box opens.
Click the down arrow on the Sort By text box, and select Yearly Revenue
per Activity.
Click the Descending button, and then click OK. The data is sorted from
the type of equipment with the highest yearly revenue to the lowest yearly
revenue.
Click cell B3, and on the Window menu, click Freeze Panes.
You will freeze the column labels so that you can scroll to bring the
Yearly Revenue per Activity column into view.
Click the right scroll arrow so that column O is next to column A. You can
now see which type of equipment rentals generated the most yearly revenue.
Save the file as
Sports Income052
On the Window menu, click Unfreeze Panes. Columns B through N are
redisplayed.
Close the workbook without saving your changes.
Filtering Data
Excel spreadsheets can hold as much data as you need them to, but you
may not want to work with all of the data in a worksheet at the same time.
You can find data that meets certain rules by creating a filter. A filter
is a rule or a set of criteria that when applied, temporarily filters out
those entries that don’t meet the criteria and displays only those that
do. For example, you might want to see the entries for only those
employees in the Marketing Department. You would set up a filter to
temporarily hide the rows of those employees who are not in the Marketing
Department.
Download the following document and save it to your pc
Filter
Once it is saved go to the folder containing the document and open it with
Excel
In this exercise, you create a filter to show the top five sales days in
January and show sales figures for Mondays during the same month.
If necessary, click the January sheet tab.
Click cell O5.
On the Data menu, point to Filter, and then click AutoFilter. A down arrow
appears in all the label cells.
In cell O5, click the AutoFilter arrow, and click (Top 10...) from the
list that appears.
The Top 10 AutoFilter dialog box appears
Click in the middle box, delete 10, type 5, and click OK.
Only the rows containing the five largest values in column O are shown.
In cell O5, click the AutoFilter arrow, and click Sort Descending. The
values are sorted from largest to smallest, with 4013 at the top.
On the Data menu, point to Filter, and then click AutoFilter. The filtered
rows reappear.
Click cell B5.
On the Data menu, point to Filter, and then click AutoFilter. A down arrow
appears in the label cells.
In cell B5, click the down arrow, and from the list of unique column
values that appears, click Mon.
Only rows with Mon in column B are shown in the worksheet.
Save the file.
Email Sports Income051, Sports Income052, Food, Food Freeze, and
Filter to me.
|