How to Perform Basic Worksheet Operations in Excel ?

Spread the love

Fundamentals of Excel Worksheets –

In Excel, each file is called a workbook, and each workbook can contain one or more worksheets. You may find it helpful to think of an Excel workbook as a binder and worksheets as pages in the binder. As with a binder, you can view a particular sheet, add new sheets, remove sheets, rearrange sheets, and copy sheets.
A workbook can hold any number of sheets, and these sheets can be either worksheets (sheets consisting of rows and columns) or chart sheets (sheets that hold a single chart). A worksheet is what people usually think of when they think of a spreadsheet.
The following sections describe the operations that you can perform with windows and worksheets.

Working with Excel windows –

Each Excel workbook file that you open is displayed in a window. A window is the operating system’s container for that workbook. You can open as many Excel workbooks as necessary at the same time.

Each Excel window has five icons at the right side of its title bar. From left to right, they
are Account, Ribbon Display Options, Minimize, Maximize (or Restore Down), and Close.
An Excel window can be in one of the following states:
Maximized Fills the entire screen. To maximize a window, click its Maximize button.
Minimized Hidden but still open. To minimize a window, clicks its Minimize button.
Restored Visible but smaller than the whole screen. To restore a maximized window, click
its Restore Down button. To restore a minimized window, click its icon in the Windows
taskbar. A window in this state can be resized and moved.

If you work with more than one workbook simultaneously (which is quite common), you
need to know how to move, resize, close, and switch among the workbook windows.

Moving and resizing windows –

To move a window, click and drag its title bar with your mouse. If it’s maximized, it will
change to a restored state. If it’s already in a restored state, it will maintain its current
size.
To resize a window, click and drag any of its borders until it’s the size that you want it to
be. When you position the mouse pointer on a window’s border, the mouse pointer changes
to a double arrow, which lets you know that you can now click and drag to resize the win-
dow. To resize a window horizontally and vertically at the same time, click and drag any of
its corners.
If you want all of your workbook windows to be visible (that is, not obscured by another
window), you can move and resize the windows manually, or you can let Excel do it for
you. Choosing View ➪ Window ➪ Arrange All displays the Arrange Windows dialog box,
as shown in Figure below. This dialog box has four window arrangement options. Just select
the one that you want and click OK. Windows that are minimized aren’t affected by this
command.

Switching among windows –

At any given time, one (and only one) workbook window is the active window. The active
window accepts your input, and it is the window on which your commands work. The active
window appears at the top of the stack of windows. To work in a workbook in a different
window, you need to make that window active. You can make a different window the active
window in several ways.

  1. Click another window if it’s visible. The window you click moves to the top and
    becomes the active window. This method isn’t possible if the current window is
    maximized.
  2. Press Ctrl+Tab to cycle through all open windows until the window that you
    want to work with appears on top as the active window. Pressing Shift+Ctrl+Tab
    cycles through the windows in the opposite direction.
  3. Choose View ➪ Window ➪ Switch Windows, and select the window that you
    want from the drop-down list (the active window has a check mark next to it).
    This menu can display as many as nine windows. If you have more than nine work-
    book windows open, choose More Windows (which appears below the nine window
    names).
  4. Click the corresponding Excel icon in the Windows taskbar

You might be one of the many people who prefer to do most work with maximized work-
book windows, which enables you to see more cells and eliminates the distraction of other
workbook windows getting in the way. At times, however, viewing multiple windows is preferred. For example, displaying two windows is more efficient if you need to compare information in two workbooks or if you need to copy data from one workbook to another.

Closing windows –

If you have multiple windows open, you may want to close those windows that you no lon-
ger need. Excel offers several ways to close the active window.

  1. Choose File ➪ Close.
  2. Click the Close button (the X icon) on the right side of the workbook window’s title
    bar.
  3. Press Alt+F4
  4. Press Ctrl+W

When you close a workbook window, Excel checks whether you have made any changes
since the last time you saved the file. If you have made changes, Excel prompts you to save
the file before it closes the window. If you haven’t, the window closes without a prompt
from Excel.
Sometimes you will be prompted to save a workbook even if you’ve made no changes to it.
This occurs if your workbook contains any volatile functions. Volatile functions recalculate
every time the workbook recalculates. For example, if a cell contains =NOW(), you will be
prompted to save the workbook because the NOW function updated the cell with the current
date and time.

Activating a worksheet –

At any given time, one workbook is the active workbook, and one sheet is the active sheet
in the active workbook. To activate a different sheet, just click its sheet tab, which is
located at the bottom of the workbook window. You also can use the following shortcut
keys to activate a different sheet:

  1. Ctrl+PgUp activates the previous sheet, if one exists.
  2. Ctrl+PgDn activates the next sheet, if one exists

If your workbook has many sheets, all of its tabs may not be visible. Use the tab scrolling
controls (see Figure below) to scroll the sheet tabs. Clicking the scrolling controls scrolls one
tab at a time, and Ctrl+clicking scrolls to the first or last sheet. The sheet tabs share space
with the worksheet’s horizontal scrollbar. You also can drag the tab split control (to the
left of the horizontal scrollbar) to display more or fewer tabs. Dragging the tab split control
simultaneously changes the number of visible tabs and the size of the horizontal scrollbar.

Adding a new worksheet to your workbook –

Worksheets can be an excellent organizational tool. Instead of placing everything on a
single worksheet, you can use additional worksheets in a workbook to separate various
workbook elements logically. For example, if you have several products whose sales you
track individually, you may want to assign each product to its own worksheet and then use
another worksheet to consolidate your results.
Here are four ways to add a new worksheet to a workbook:

  1. Click the New Sheet control, which is the plus sign icon located to the right of the
    last visible sheet tab. A new sheet is added after the active sheet.
  2. Press Shift+F11. A new sheet is added before the active sheet.
  3. From the Ribbon, choose Home ➪ Cells ➪ Insert ➪ Insert Sheet. A new sheet is
    added before the active sheet.
  4. Right-click a sheet tab, choose Insert from the shortcut menu, and select the
    General tab of the Insert dialog box that appears. Then select the Worksheet icon
    and click OK. A new sheet is added before the active sheet.

Deleting a worksheet you no longer need –

If you no longer need a worksheet or if you want to get rid of an empty worksheet in a
workbook, you can delete it in either of two ways.

  1. Right-click its sheet tab and choose Delete from the shortcut menu.
  2. Activate the unwanted worksheet and choose Home ➪ Cells ➪ Delete ➪ Delete
    Sheet.

If the worksheet is not empty, Excel asks you to confirm that you want to delete the sheet

Changing the name of a worksheet –

The default names that Excel uses for worksheets—Sheet1, Sheet2, and so on—are generic
and non descriptive. To make it easier to locate data in a multi 1. sheet workbook, you’ll want
to make the sheet names more descriptive.
These are three ways to change a sheet’s name:

  1. From the Ribbon, choose Home ➪ Cells ➪ Format ➪ Rename Sheet.
  2. Double-click the sheet tab
  3. Right-click the sheet tab and choose Rename Sheet.

Excel highlights the name on the sheet tab so that you can edit the name or replace it with
a new name. While editing a sheet name, all of the normal text selection techniques work,
such as Home, End, arrow keys, and Shift+arrow keys. Press Enter when you’re finished
editing and the focus will be back on the active cell.

Changing a sheet tab color –

Excel allows you to change the background color of your worksheet tabs. For example,
you may prefer to color-code the sheet tabs to make identifying the worksheet’s contents
easier.
To change the color of a sheet tab, choose Home ➪ Cells ➪ Format ➪ Tab Color, or right-
click the tab and choose Tab Color from the shortcut menu. Then select the color from the
color palette. You can’t change the text color, but Excel will choose a contrasting color to
make the text visible. For example, if you make a sheet tab black, Excel will display white
text.
If you change a sheet tab’s color, the tab shows a gradient from that color to white when
the sheet is active. When a different sheet is active, the whole tab appears in the selected
color.

Rearranging your worksheets –

You may want to rearrange the order of worksheets in a workbook. If you have a separate
worksheet for each sales region, for example, arranging the worksheets in alphabetical
order might be helpful. You can also move a worksheet from one workbook to another and
create copies of worksheets, either in the same workbook or in a different workbook.
You can move a worksheet in the following ways:

  1. Right-click the sheet tab and choose Move or Copy to display the Move or Copy dia-
    log box (see Figure below figure). Use this dialog box to specify the location for the sheet.
  2. From the Ribbon, choose Home ➪ Cells ➪ Format ➪ Move or Copy Sheet. This shows
    the same dialog box as the previous method.
  3. Click the worksheet tab and drag it to its desired location. When you drag, the
    mouse pointer changes to a small sheet icon, and a small arrow indicates where the
    sheet will be placed when you release the mouse button. To move a worksheet to a
    different workbook by dragging, both workbooks must be visible.

Copying the worksheet is similar to moving it. If you use one of the options that shows the
Move or Copy dialog box, select the Create a copy check box. To drag and create a copy, hold
down the Ctrl key while you drag the worksheet tab. The mouse pointer will change to a
small sheet icon with a plus sign on it.

If you move or copy a worksheet to a workbook that already has a sheet with the same
name, Excel changes the name to make it unique. For example, Sheet1 becomes Sheet1 (2).
You probably want to rename the copied sheet to give it a more meaningful name.

Hiding and unhiding a worksheet –

In some situations, you may want to hide one or more worksheets. Hiding a sheet may be
useful if you don’t want others to see it or if you just want to get it out of the way. When a
sheet is hidden, its sheet tab is also hidden. You can’t hide all of the sheets in a workbook;
at least one sheet must remain visible.
To hide a worksheet, choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Hide Sheet, or
right-click its sheet tab and choose Hide. The active worksheet (or selected worksheets) will
be hidden from view.
To unhide a hidden worksheet, choose Home ➪ Cells ➪ Format ➪ Hide & Unhide ➪ Unhide
Sheet, or right-click any sheet tab and choose Unhide. Excel opens the Unhide dialog box, which lists all hidden sheets. Choose the sheet that you want to redisplay, and click OK.
You can’t select multiple sheets from this dialog box, so you need to repeat the command
for each sheet that you want to unhide. When you unhide a sheet, it appears in its previous
position among the sheet tabs.

Zooming in or out for a better view –

Normally, everything you see onscreen is displayed at 100%. You can change the zoom per-
centage from 10% (very tiny) to 400% (huge). Using a small zoom percentage can help you
get a bird’s-eye view of your worksheet to see how it’s laid out. Zooming in is useful if you
have trouble deciphering tiny type. Zooming doesn’t change the font size specified for the
cells, so it has no effect on printed output.

You can change the zoom factor of the active worksheet window by using any of these
three methods:

  1. Use the Zoom slider located on the right side of the status bar. Click and drag the
    slider, and your screen transforms instantly.
  2. Press Ctrl and use the wheel button on your mouse to zoom in or out.
  3. Choose View ➪ Zoom ➪ Zoom, which displays a dialog box with some zoom options.

Also in the Zoom Ribbon group is a 100% button to return to 100% zoom quickly and a
Zoom to Selection button to change the zoom so that whatever cells you have selected take
up the whole window (but still limited to the 10–400% zoom range).

Comparing sheets side by side –

In some situations, you may want to compare two worksheets that are in different win-
dows. The View Side by Side feature makes this task a bit easier.
First, make sure that the two sheets are displayed in separate windows. (The sheets can
be in the same workbook or in different workbooks.) If you want to compare two sheets in
the same workbook, choose View ➪ Window ➪ New Window to create a new window for the

active workbook. Activate the first window; then choose View ➪ Window ➪ View Side by
Side. If more than two windows are open, you see a dialog box that lets you select the win-
dow for the comparison. The two windows are tiled to fill the entire screen.
When using the Compare Side by Side feature, scrolling in one of the windows also scrolls
the other window. If you don’t want this simultaneous scrolling, choose View ➪ Window ➪
Synchronous Scrolling (which is a toggle). If you have rearranged or moved the windows,
choose View ➪ Window ➪ Reset Window Position to restore the windows to the initial
side-by-side arrangement. To turn off the side-by-side viewing, choose View ➪ Window ➪
View Side by Side again.
Keep in mind that this feature is for manual comparison only. Unfortunately, Excel doesn’t
provide a way to identify the differences between two sheets automatically.

Leave a Reply