Microcomputer: Spread Sheets: Week 12-14

MICROSOFT EXCEL

What is Microsoft Excel ?

Microsoft Excel is an application that helps you create intricate and dynamic spreadsheets. You can use this robust application to enter numerical values or data into the rows or columns of a spreadsheet, and to use these numerical entries for calculations, charts, formulas, statistical analysis, etc.

The Microsoft Office Excel is a member of the Microsoft Office suite of programs. It is a powerful tool for analyzing, sharing, and managing information to help you make more informed decisions.
The first impression when you open the program - brand-new Ribbon user interface being introduced to replaced the old menus.

To start Excel 2007 from the Start Menu

Click on the Start button, point to All Programs then Microsoft Office and click on Microsoft Office Excel 2007.

The first screen that you will see a new blank worksheet that contains grid of cells. This grid is the most important part of the Excel window. It's where you'll perform all your work, such as entering data, writing formulas, and reviewing the results.

Microsoft Excel 2007 Workbook and Worksheet

A worksheet is the grid of cells where you can type the data. The grid divides your worksheet into rows and columns.
Columns are identified with letters (A, B, C … ), while rows are identified with numbers (1, 2, 3 … ).
A cell is identified by column and row. For example, B8 is the address of a cell in column B (the second column), and row 8 (the eighth row).
A worksheet in Excel 2007 consists of 16,384 columns and over 1 million rows. The worksheets in turn are grouped together into a workbook.
By default each workbook in Excel 2007 contains 3 blank worksheets, which are identified by tabs displaying along the bottom of your screen. By default the first worksheet is called Sheet1, the next is Sheet2 and so on as shown here.

When you first launch Excel 2007, the program opens up the first of three new worksheets (named Sheet1) in a new workbook file (named Book1).
The Excel 2007 program window containing this worksheet of the workbook is made up of the following components:

SCREEN ELEMENTS

To know the excel 2007 screen elements

Office button: When clicked, this button opens the Office menu, from which you can open, save, print, and exit as well as the Excel Options button that enables you to change Excel's default settings.
Quick Access Toolbar: A small toolbar next to the Office button contains shortcuts for some of the most common commands such as Save, Undo, and Redo buttons. You also can customize quick access toolbar.
Ribbon: A combination of old versions menu bar and toolbar, arranged into a series of tabs ranging from Home through View. Each tab contains buttons, lists, and commands.
Name box: Displays the address of the current active cell where you work in the worksheet.
Formula bar: Displays the address of the active cell on the left edge, and it also shows you the current cell's contents.
Worksheet area: This area contains all the cells of the current worksheet identified by column headings, using letters along the top, and row headings, using numbers along the left edge with tabs for selecting new worksheets.
Sheet tabs: Excel 2007 contains 3 blank worksheet tabs by default. Click on the intended tab will go to the particular worksheet.
Status bar: Reports information about the worksheet and provides shortcuts for changing the view and the zoom.
Zoom control: Use to zoom the Excel screen in or out by dragging the slider.

RIBBON

The Excel 2007 Ribbon …a new outlook that replaces the previous versions menu bar and drop down menus.

Yes, Excel 2007's user interface is based on the ribbon, which is a bar across the top of the window that contains tabbed pages of commands and icons/buttons.
With the Ribbon, it shows you all the most commonly used options needed to perform a particular Excel task and you just click on it to take action.

Here are some important terms related to Excel 2007 ribbon:

Ribbon: The whole bar, including all of the tabs.
Office Button: On the far left side of the Ribbon, the Office Button lets you perform tasks that are common across many Office applications.
Quick Access Toolbar: Located on top of the Excel screen beside the Office Button.
Tab: Excel's main tasks are brought together and display all the commands commonly needed. Clicking a tab name once activates the tab.
Group: A section of a tab that organizes related command buttons into subtasks. For example, the Home tab has the following groups: Clipboard, Font, Alignment, Number, Styles, Cells, and Editing.
Dialog box launcher: A small icon in the bottom-right corner of a group, from which you can open a dialog box related to that group.

To understand tabs on the Excel 2007 ribbon

Home: Use this tab when creating, formatting, and editing a spreadsheet.
Insert: Use this tab when adding particular elements (including graphics, PivotTables, charts, hyperlinks, and headers and footers) to a spreadsheet.
Page Layout: Use this tab when preparing a spreadsheet for printing or reordering graphics on the sheet.
Formulas: Use this tab when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors.
Data: Use this tab when importing, querying, outlining, and subtotaling the data placed into a worksheet's data list.
Review: Use this tab when proofing, protecting, and marking up a spreadsheet for review by others.
View: Use this tab when changing the display of the Worksheet area and the data it contains.

STATUS BAR

Excel 2007 status bar provides you with additional information when you select a range of cells. This information included the status of the Num Lock, Caps Lock, and Scroll Lock keys on your keyboard.

This bar keeps you informed of the program's current mode and any special keys you engage, and enables you to select a new worksheet view and to zoom in and out on the worksheet.
However, you also can customize the status bar!
Customize the status bar in Excel 2007 and make the overlooked status bar keep you up to date on the status of certain items within your Excel spreadsheet. This tutorial will show you how to add, and remove certain options/elements to the status bar.

To add options to the Status Bar

Right-click the Status Bar to bring up the Customize Status Bar menu.

Click to select the options you want, click again if you do not want the item displayed.
When finished, click anywhere on the spreadsheet to close the menu.

To remove options from the Status Bar

Right-click on the Excel status bar and click on any option that you wish to remove from the status bar.
The menu's options are enabled (selected) when a check mark appears next to them and vice-versa.

ENTERING DATA

Enter data into excel 2007 worksheet?

Yes, to start using Excel, you need to know how to enter data into the Excel worksheet. The tutorial here shows you the very basic of entering text, number, date and time into the worksheet.

To enter text into a worksheet

Select the cell in which you want to enter the text.
Type in the text/data into the cell.
Press the Enter key. Text entries are left aligned by default.

To enter numbers into a worksheet

Select the cell in which you want to enter a number.
Type in the number into the cell and press Enter.
If you want to enter a negative number, type a minus sign in front of it or enclose it in parentheses (bracket), e.g. -15 or (15).
To indicate decimal places, you type a full stop such as 125.89.
The numbers will be right aligned by default.

To enter dates and times into a worksheet

Select the cell in which you want to enter the date or time.
Type in the date or time. Separate the date with either hyphens (-) or slashes (/).
Press Enter.
To enter the current date, press Ctrl+;
To enter the current time, press Ctrl+:

You may utilize the keyboard to help you speed up the data entry process. Below is a list of keys that you can use when you want to quickly enter your data.

To speed up your data entry

Enter key: Pressing the key will enters the data and moves the active cell highlight down to the next cell in the current column.
Tab key: Pressing the key will enters the data and moves the active cell highlight to the next cell in the current row.
Arrow keys: Pressing the key will enters the data and moves the active cell highlight to the next cell in the direction of the specific arrow key pressed.
Esc key: Pressing the key will will cancels the current data entry.

ALIGNMENT

The options allow you to change alignment of the text in a cell or cells, text orientation, merge several cells together and so on.

For more alignment options, you can click on the Alignment group dialog box launcher to display the Format Cells dialog box.

To align data between the left and right sides of a cell

Select the cell, or cells, you wish to align.
On the Home tab, in the Alignment group, click the Align Text Left icon to align data with the left edge of the cell.
Click on the Center icon to center data in the cell.
Click on the Align Text Right icon to align data with the right edge of the cell.

To align data between the top and bottom of a cell

Select the cell, or cells, you wish to align.
On the Home tab, in the Alignment group, click the Top Align icon to align data in the top position of the cell.
Click on the Middle Align icon to centralized data vertically in the cell.
Click the Bottom Align icon to align data in the bottom position of the cell.

To change the orientation of data cells

Select the cell, or cells, you wish to change.
On the Home tab, in the Alignment group, click the Orientation icon. You will see a drop down menu allowing you to format the cell orientation.

Select any one of the command. Experiment with applying some of the other orientation effects.

To wrap multiple lines of data in a cell

Type the text 'The Ultimate Guide to Excel 2007' into the cell C5 and press Enter. The entry will appear as one long line that does not 'fit' into the cell.
Select the cell C5 and then on the Home tab, in the Alignment group, click the Wrap Text icon. The text will wrap as follow:

To merge several cells

Select the cells that you wish to merge to become one cell.
On the Home tab, in the Alignment group, click the Merge & Center icon.

To shrink the text into one cell

Type the data you require into the cell and press Enter.
Right-click on the selected cells, click Format Cells.

From the Format Cells dialog box displayed, click on the Alignment tab.
Under the Text control section, tick the Shrink to fit check box and click on OK.

Note: You can also use this right-click method to do the wrap text and merge cells.

NUMBER FORMATTING

Number Formats help you to change the appearance of numbers or values in a cell in the Excel spreadsheet. They are not difficult, and can be achieve with a few clicks.

Formatting is done to improve the appearance of the spreadsheet and to make the numbers easier to read and understand. Commonly used number formats include adding commas ( , ), percent symbols ( % ), decimal places, and dollar signs( $ ).

In Excel 2007, the basic number formatting options are located on the Home tab, Number group as shown here.

To change the basic number formatting

Select the cell containing number that you wish to format.
Click on the down arrow next to the Number Format drop-down list and select a suitable command.

To change number formatting using the formatting icons

You can quickly change the formatting of a cell or selected range by using the following icons on the Home tab, Number group.

To format a number as a currency

Select the cell or range of cells you want to format.
Right-click on the cell and choose Format Cells from the pop-up menu.
From the Format Cells dialog box displayed, select the Number tab.
Under the Category: section, select Currency.

Select the number of decimal places you require by using the Decimal places: spin box arrows.
In the Symbol: drop down list, select the type of currency.
Click OK.

To format a number as a percentage

Select the cell or range of cells you wish to format.
Right-click on the cell and choose Format Cells from the pop-up menu.
From the Format Cells dialog box displayed, select the Number tab.
Under the Category: section, select Percentage.
Select the number of decimal places you require by using the Decimal places: spin box arrows.
Click OK.

To change the number of decimal places

Select the cell or range of cells, you wish to change the number of decimal places.
To increase a decimal place, click on the Increase Decimal icon on the Home tab, Number group. You can continue to click to increase the decimals as required.
To reduce a decimal place, click on the Decrease Decimal icon on the Home tab, Number group. You can continue to click to reduce the decimals as required.

To round numbers using a numeric format

Select the cell or range of cells you wish to format.
Right-click on the cell and choose Format Cells from the pop-up menu.
From the Format Cells dialog box displayed, select the Number tab.
In the Category: section, select Number.
Select the number of decimal places you require by using the Decimal places spin box arrows.
Click OK.

To change colors based on the value in the cells

Select the cells you wish to change, which contain numeric values.
Right-click on the cell and choose Format Cells from the pop-up menu.
From the Format Cells dialog box displayed, select the Number tab.
Choose Custom from the Category: list box.
Use the scroll bars in the Type: section of the dialog box to view what custom number formats are available.
For example, to force all negative numbers to be displayed in red, you would select the option illustrated below.
Click OK.

To display negative numbers that enclosed within brackets

You would edit the above example, as illustrated.

FORMULAS AND FUNCTIONS

A formula is an equation that performs operations on worksheet data. You can use an Excel 2007 formula to perform mathematical operations, such as addition and multiplication, or they can compare worksheet values, join text, averaging a student's test results, etc.

Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. In addition, if you change the data in your spreadsheet, Excel will automatically recalculate the answer without you having to re-enter the formula.

A basic formula format will start with an equals sign (=) followed by one or more operands, separated by one or more operators. Operands can be values, text, cell references, ranges, defined names, or function names. Operators are symbols used to represent the various arithmetic and comparison operations you can perform on the operands.

In Microsoft Excel 2007, operators are executed in this order:

To enter a formula

Place the cursor in the cell where the formula will appear, i.e.E5.
Enter an = sign. All Excel formulas start with the 'equal' sign.
Enter the expression that will produce the result you want. This can consist of operands, values, variables, and symbols which represent mathematical procedures such as + or - to add and subtract, e.g. A5+C5.
When the formula is complete, press Enter. The result of the formula will be calculated and displayed in the cell E5.
You can see the formula in the Formula bar at the top of the screen by placing the cell pointer on the cell E5.
If there is an error in a formula, an error message is displayed which will begin with a # sign.

To know the Excel 2007 formula error messages

When writing formulas it is easy to make a mistake. Here are some common mistakes:

To enter a cell or range reference by pointing

Place the cursor in the cell where the formula will appear.
Enter the formula up to the point of the cell or range reference, e.g. to enter the formula =E2+E5, only enter the = sign.
Using the arrow keys, move the cell pointer to the first cell reference, in this case E2. The formula will track your progress and enter the current address into the formula.
Enter the operand, + sign.
Using the arrow keys, move the cell pointer to the second cell reference, in this case E5. If you are calculating a range of cells, hold down the Shift key while using the arrow keys to move to the intended cells.
Press Enter to complete the formula when you have reached the cell you require.

Excel 2007 contains a build-in list of worksheet functions, which are categorised to make it easy to search for the ones you need.

Excel has over 300 built-in functions divided into various function categories, including:

Financial
Logical
Text
Date & Time
Lookup & Reference
Math & Trigonometry
Information
Database
Statistical
Engineering
Cube

Here are some commonly used functions:

AVERAGE: Used to determine the average value of the selected cells contents.

COLUMNS: Used to return the number of columns within a reference.

COUNT: Used to count how many numbers are in the list.

MAX: Used to return the maximum number from a list.

MIN: Used to return the minimum number from a list.

ROUND: Used to round off numbers to a specified number of decimal points.

SUM: Used to add the contents of selected cells.

To display all the available functions

Open a blank Excel workbook.
Click on the Formulas tab and within the Function Library group click on the Insert Function icon.

From the Insert Function dialog box displayed, under Or select a category: section, select a particular category function, the related functions will displayed.

To enter functions directly into the worksheet cell

Select the cell into which the formula will be entered.
Insert an equal (=) sign to begin the formula. The formula toolbar buttons will appear.
Enter the name of the function [e.g. SUM], followed by an opening parenthesis [(], any arguments required for the function [e.g. E2:E5], and closing parenthesis[)].
Press Enter. If there are no errors in the formula, the result of the function will be entered in the cell. If you activate the cell again, the function will be displayed in the formula bar.

IF FUNCTION

The Excel 2007 IF function, one of Excel's logical functions, tests to see if a certain condition in a spreadsheet is true or false.

You can use the IF Function for various purposes. For example, you can use the function to grade student exam scores. If the student has above 85, award an A grade; if the student has below 40, award a fail grade.
The syntax for the IF function is:
=IF (logical_test, value_if_true, value_if_false)
logical_test - a value or expression that is tested to see if it is true or false.
value_if_true - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

To use the Excel 2007 IF function (an example)

Enter the following data in an Excel worksheet as illustrated.

Let say the student scores are based on the following:

A If the student scores 85 or above
B If the student scores 70 to 84
C If the student scores 55 to 69
D If the student scores 40 to 54
FAIL If the student scores below 40

Now, click on cell C3 - the location where one of the results will be displayed.
Click on the Formulas tab and choose Logical function from the ribbon to open the drop down list.
Click on IF in the list to bring up the function's dialog box.

From the If Function Arguments dialog box displayed, click on the icon with red color pointing upwards behind the Logical_test.

Then, type in the following formula and click on the red color icon pointing down.

B3>=85, "A", IF(B3>=70, "B", IF(B3>=55, "C", IF(B3>=40, "D", "Fail" ) ) )

Click OK.
If you want to see the full formula, click on the cell C3 and the formula will be display on the Formula Bar.

Note: Some of the conditional operator you need to know:
< - Less Than
>= - Greater than Or Equal To
<= - Less than Or Equal To
<> - Not Equal To

CHARTS

The Excel 2007 Chart (also known as graph) is a visual representation of numeric values. Displaying data in a well-conceived chart can make your numbers more understandable.

The Excel 2007 charts have a great new look. That is why today Excel is one of the most commonly used application for creating charts.
Before you can create a chart, you must have some numbers (data) that stored in the cells in an Excel worksheet. Normally, the data that a chart uses can be resides in a single worksheet, or use data that's stored in other worksheets.

Creating an Excel 2007 Chart from Data in a Worksheet
Once you have entered the data you want to display as a chart into a worksheet and decided which type of chart you require, you can create it with just a few mouse clicks. Use the following illustration to quickly create a chart on the current worksheet.
Open Excel 2007 and create a worksheet as illustrated below.

To create a chart

Click on any cell within the data containing the information that you wish to display as a chart, or highlight the exact data area that you wish to display as a chart.
On the Insert menu, in the Charts group, click the chart type you require. A gallery of thumbnail images for the related chart subtypes will appear.

Click the required chart subtype to create the chart, or click All Chart Types to open the Insert Chart dialog box and choose from all available chart types.
When you have selected a subtype, a chart will be created as an object in the worksheet and Chart Tools will appear on the Ribbon incorporating Design, Layout, and Format tabs.
For example, when choose the '3-D Clustered Column' subtype, the chart created as follow:

Alternatively, click the Charts group Dialog Box Launcher to open the Insert Chart dialog box and see all the available chart types.

You can also change the default chart type in the Insert Chart dialog box by selecting a chart type and clicking the Set as Default Chart button.

Note: To quickly create a chart of the selected data (based on the default chart type) as an object in the current worksheet, press Alt+F1. To create a chart of the selected data as a new sheet, press F11.

Creating an Excel 2007 Chart from Data in a Different Worksheet
It is possible to display a chart on one worksheet using data from a different worksheet. The source data can even come from a separate open workbook.
To create a chart using data from a different worksheet, do the following:

To create a chart (data in another worksheet)

Click a blank cell in the worksheet that you want the chart to be displayed in.
From the Insert menu, in the Charts group, click the chart type and subtype you require.
Select the Design tab under Chart Tools on the Ribbon and click Select Data in the Data group.

From the Select Data Source dialog box displayed, enter the data range that you want to use for the chart in the Chart data range: box by clicking the Collapse Dialog icon to the right of the Chart data range: box and selecting the workbook and/or worksheet and then the cell range that you want to use for the data source.

Click the Expand Dialog icon to the right of the Select Data Source dialog box when you have finished.
Alternatively you can type the data range into the Chart data range: box following the following formats:

>> Type the formatSheet2!$A$8:$C$12 if you are charting the data in cells A8 to C12 in Sheet2 of the current workbook. >> If you want to use data from a sheet (Sheet1) in a different workbook (Workbook2), the format would be [Workbook2]Sheet1!$A$8:$C$12.
If the name of either the worksheet or workbook contains spaces, enclose the workbook / worksheet name in single quotes,'[Sales2007]Jan Sales!'$A$8:$C$12.

Click OK to close the Select Data Source dialog box.

WORKING WITH CHARTS

Excel 2007 chart legend is the little box that appears next, above or below the chart. It provides the descriptions what the different colors or patterns mean in a chart. You can so some formatting to the legend such as resize, placement, legend font and color.

To resize a legend box

Click the chart legend.
Drag one of its selection handles (legend border) to make it bigger or smaller.

To set the chart legend placement

Select the chart, if needed.
On the Chart Tools Layout tab, in the Labels group, click Legend icon and choose the option where you want to position the chart legend.

To format the chart legend

Right-click the chart legend and choose Format Legend.
From the Format Legend dialog box displayed, do the formation as needed. The available categories are Fill, Border Color, Border Styles, and Shadow.