EXCEL BASICS

3.3 EXCEL BASICS

In this section, we will cover how to start Excel, open, save and close a workbook and what different parts of Excel Window stand for.

3.3.1 Starting Excel

You can start MS Excel by either of the following two ways:

Click on Start All ProgramMicrosoft Office Microsoft Office Excel 3007.
Double click on the MS Excel icon on the desktop (if you have one).

When Excel opens, a new document (called Workbook in Excel) with default name as Book1 is opened. For each additional workbook you open, the number increases by one. Please note that you can open more than one workbook at a time. By default each workbook contains three worksheets. You may increase or decrease the number of worksheets in a workbook. How we do it, we will learn later in this unit. You may also start Excel by clicking on a workbook saved on your hard drive. Excel will open automatically and the workbook will be displayed in the Excel window.

3.3.2 Commands and Resources in Excel Window

Let us familiarize ourselves with the key commands and resources in Excel Window:

The Microsoft Office Button

It is the button in the upper-left corner of the Excel Window. When you click on the button, it displays a menu that can be used to create a new workbook, open an existing workbook, save a workbook, print and perform many other tasks.

The Quick Access Toolbar

It is present next to the Microsoft Office Button on the top. It provides you access to the commands you frequently use. By default Following appear on the Quick Access Toolbar:

Save: To save your file (you may also press keyboard button ( Ctrl+S).
Undo: To rollback the action that you last took (Ctrl+Z).
Redo: To reapply the action you rolled back or to repeat an action (Ctrl+Y).

The Title Bar

It is next to the Quick Access toolbar at the top. It displays the title of the workbook on which you are currently working. By default, the first new workbook is named as Book1. For each additional workbook you open, the number increases by one. You may save the workbooks by any legal filename you want.

The Ribbon

The Ribbon is the panel at the top portion of the document, right below the Title Bar. To begin with it has following seven tabs:

Home: It has basic commands for creating, formatting and editing the spreadsheets. It has controls for working with the clipboard, fonts, alignment, number, styles, cells and editing.
Insert: It has commands for inserting tables, pictures, shapes, other illustrations, links, charts, header, footer, etc.
Page Layout: The commands here help to set the layout of the spreadsheet, apply a theme to set the overall look, set the margins, orientation, size, backgrounds, etc.
Formulas: It has commands that help you use different formulas and functions.
Data: Has commands to import, query, view data from external sources, sort, filter or manage data.
Review: Has commands to add comments, protect sheet, protect workbook, share workbook, etc.
View: Helps to change the display of the worksheet area.

Besides these basic tabs, additional tabs appear from time to time, depending on the context we are working in. These tabs are called contextual tabs. For example, if you select a chart, a Chart Tools contextual tab appears that has commands to help you design and format the chart. These contextual tabs appear in a different colour to make them easy to spot.

The commands on each tab are organized into groups. Hence, a group is a collection of logically related command buttons that you can use to manage a Worksheet. Commonly used features are displayed on the Ribbon and additional options can be accessed through the dialog box launcher at the bottom-right corner of each group.

The Formula Bar

The formula bar is divided into three sections:

Name Box: Located on the left most side of the formula bar, it displays the address of the current cell

Formula Bar Buttons: Middle section of the formula bar with indented circle on the left (to increase or decrease the size of the name box) and function wizard (labeled fx) on the right. When you start entering data in the cell, Cancel (X ) and Enter ( Right) buttons also appear.

Cell Contents: Right side of the formula bar displays the cell entries.

The Worksheet Area:

The worksheet area displays all the cells. It is in the cells that you enter, format or edit your data.

The Status Bar

The Status bar appears at the very bottom of the Excel window and provides such information as the sum, average, minimum, and maximum value of selected numbers. You can change what displays on the Status bar by right-clicking on the Status bar and selecting the options you want from the Customize Status Bar menu. You click a menu item to select it. You click it again to deselect it. A check mark next to an item means the item is selected.

3.3.3 Setting up Your Excel Environment

Before you begin working on your spreadsheet, you may want to set up your Excel environment and become familiar with a few key tasks such as how to maximize and minimize the Ribbon, configure the Quick Access toolbar, display/ hide the formula bar, change page views etc.

Minimize and Maximize the Ribbon

Right click anywhere in the main menu
Select Minimize the Ribbon in the menu that appears. This will toggle the Ribbon on and off.

The check mark beside ‘Minimize the Ribbon’ option indicates the feature is active. You may choose to use this option, if you prefer not to use the Ribbon, but use different menus and keyboard shortcuts.

This menu also has option to Show Quick Access Toolbar Below the Ribbon, instead of at the top. You can also Customize Quick Access Toolbar using the option available in this menu. Choosing this option displays the window as shown in figure 3.

Add Commands to Quick Access Toolbar

Click the arrow (customize quick access toolbar) to the right of the Quick Access toolbar.
Select the command you wish to add from the drop down menu. The command will appear in the Quick Access Toolbar

You can also select More commands… from the menu to open the screen as shown in Figure 3.3. Here you can one by one add commands to the toolbar or remove commands from the toolbar to make specific features easily accessible.

Figure 3.3: Customizing Quick Access Toolbar

Display or Hide the Formula Bar

Click the View Tab of the Ribbon.
In the Show/ Hide group check/ uncheck against formula bar to show/ hide formula bar

Expand Formula Bar

The Expand/ Collapse formula bar button is present at the far right end of the formula bar.

Click on the button to expand or collapse the formula bar

Customize the Status Bar

Right click anywhere on the Status Bar.
From the menu that appears, select the options that you want to see on the Status Bar. If the option is selected a check mark appears before it. Ensure that View Shortcuts and Zoom Slider options are selected.
Locate the zoom bar in the bottom, right corner.
Left-click the slider and drag it to the left to zoom out and to the right to zoom in.

You can also use the Zoom group in the View Tab of the Ribbon to set your zoom.

Change Page Views

Locate the Page View options in the bottom, right corner. Click on an option to select it. Different page view options are:
Normal: This is the default view
Page Layout: This view is very helpful from printing point of view. When you select this view, you are able to see the header block, all the margins around the worksheet, the vertical and horizontal rulers and the column and row headings also appear differently. This view shows how exactly the workbook would look like when you print it.
Page Break: This is view is useful to determine where the page will break when you are trying to print an Excel sheet that spans multiple pages.

You can also set the page view using Workbook Views group in the View tab on the Ribbon.

Customizing the Environment Using Excel Options

The Excel Options menu allows you to customize Excel 3007 according to your preferences.

Click the Microsoft Office Button
Click on ‘Excel Options’ button located at the bottom of the menu.

Figure 3.7 : Excel Options Button

When you click on Excel Options button, an extensive menu will open.

Using Excel Options menu, you can personalize your work environment with the mini toolbar, Live preview, colour schemes, customize sort and fill sequence, auto correction setting, modify default options for new workbooks, calculation options, specify options for editing, copying, pasting formulas, calculations and other general setting.

3.3.4 Creating a New Workbook

You can create a new workbook as follows:

Click the Microsoft Office Button.
Select New. The New Workbook dialog box appears.
Select Blank Workbook under ‘Blank and Recent’ section. It will be highlighted by default.
Click Create. A new, blank workbook appears in the Excel window.

You may also use keyboard shortcut Ctrl+N to create a new workbook. Please note that when you first open Excel, it open with a blank new workbook.

If you want to create a new document from a template, explore the templates and choose one that fits your needs, instead of choosing new blank workbook.

3.3.5 Opening an Existing Workbook

You can open an existing document in one of the following ways:

Click the Microsoft Office Button.
Select Open. Select the required workbook in the dialog box.
Use keyboard shortcut Ctrl+O to select and open an existing document. OR If you have recently used workbook then
Click the Microsoft Office Button.
Choose from the Recent Documents section. OR
Go to Windows Explorer. Find your document.
Right mouse click on the document and select Open.

3.3.6 Saving a Existing Document

Click the Microsoft Office Button.
Select Save from the menu. OR
Use keyboard shortcut Ctrl+S
OR Use Save on the Quick Access Toolbar

On using any of these options, the workbook is saved in its current location with the same file name. If you are saving the workbook for the first time, then Save As dialog box appears which accepts the workbook name and location where it is to be saved.

Using Save As Option

You may use Save As option as below:

Click the Microsoft Office Button.
Select Save As from the menu. The Save As dialog box appears.
Select the location where you wish to save the workbook.
Enter the name for the workbook
Click the Save button

The Save As option can be used to:

Create a backup copy of the workbook by saving it at another location or by different name.
Save the workbook in a format that is fully compatible with Excel97-3003
Save the workbook as macro-enabled or binary workbook.

3.3.7 Working with Multiple Workbooks

Multiple workbooks can be opened simultaneously if there is such a need. To see the list of open workbooks:

Click on View tab of the Ribbon
Click on Switch Windows in the Window group. A drop down list of all open workbooks is displayed.

The current workbook has a checkmark besides its name. You may select any workbook from the list to make it current.

3.3.8 Closing a Workbook

To close a workbook:

Click the Microsoft Office Button.
Select Close from the menu.

The current workbook closes. The next document in the list becomes current. If there is no other open document, then only Excel window is there.

3.3.9 Closing Microsoft Excel

Click the Microsoft Office Button. A menu appears.
Click Close. Excel closes.