Download Microsoft Excel 2007-Computer Fundamentals-Lecture Notes and more Study notes Computer Fundamentals in PDF only on Docsity!
256
CHAPTER NO 16
MICROSOFT EXCEL 2007
Microsoft Excel 2007 is an electronic spreadsheet software package that allows you to organize data in a tabular format, perform mathematical calculations, and analyze data. This software includes functions, formulas, and charts. It is commonly used in business, engineering, mathematics, and statistics.
Microsoft Excel 2007 is the newest version of Microsoft Office's worksheet (spreadsheet)
program. Technically a worksheet is a single document inside a workbook but we often use
the terms worksheet, spreadsheet and workbook interchangeably. Worksheets contain
numerical information presented in tabular row and column format with text that labels the
data.
It can also contain graphics and charts. Like Microsoft Word 2007, Excel 2007 takes
advantage of a new, results-oriented user interface to make powerful productivity tools easily
accessible. If you're worried about capacity, Excel 2007 now accommodates 1 million rows
and 16,000 columns.
GETTING START
To start MS Excel click on the Start Menu on the bottom left corner of your
desktop and select Start → All Programs → Microsoft Office → Microsoft
Office excel 2007.
SCREEN LAYOUT
The MS Excel 2007 interface brings out all the functionality of the software using tabs rather than drop-down menus.
The main work area is called a spreadsheet where you can communicate your data using functions, formulas, and charts.
257
The highlighted cell is called the active cell and is the current place where you can enter a value or a formula. We will describe the parts of the interface in the following sections.
Getting started with Excel 2007 will notice that there are many features. There are three features that you should remember as you work within Excel 2007:
- The Microsoft Office Button,
- The Quick Access Toolbar,
- The Ribbon.
The function of these features will be more fully explored below.
259
Commonly utilized features are displayed on the Ribbon. To view additional features within each group, click the arrow at the bottom right corner of each group.
HOME : Clipboard, Fonts, Alignment, Number, Styles, Cells, Editing. INSERT : Tables, Illustrations, Links, Text, and Media Clips. PAGE LAYOUTS:
Themes, Page Setup, Scale to Fit, Sheet Options, Arrange
FORMULAS : Function Library, Defined Names, Formula Auditing, Calculation DATA : Start Slide Show, Set Up, Monitors.
REVIEW Proofing, Comments, Protect.
VIEW : Presentation Views, Show/Hide, Zoom, Window, Macros
QUICK ACCESS TOOLBAR
The Quick Access Toolbar is a customizable toolbar that contains commands that you may want to use. You can place the quick access toolbar above or below the ribbon. To change the location of the quick access toolbar, click on the arrow at the end of the toolbar and click Show below the Ribbon.
260
MINI TOOLBAR
A new feature in Office 2007 is the Mini Toolbar. This is a floating toolbar that is displayed when you select text or right-click text. It displays common formatting tools, such as Bold, Italics, Fonts, Font Size and Font Color.
262
SAVE A WORKBOOK
When you save a workbook, you have two choices: Save or Save As. To save a document:
Click the Microsoft Office Button Click Save
You may need to use the Save As feature when you need to save a workbook under a different name or to save it for earlier versions of Excel. Remember that older versions of Excel will not be able to open an Excel 2007 worksheet unless you save it as an Excel 97- 2003 Format. To use the Save As feature:
Click the Microsoft Office Button Click Save As Type in the name for the Workbook In the Save as Type box, choose Excel 97-2003 Workbook
263
OPEN A WORKBOOK
To open an existing workbook:
Click the Microsoft Office Button Click Open Browse to the workbook Click the title of the workbook Click Open
265
WHAT ARE SPREADSHEETS?
A spreadsheet is a group of cells organized into columns and rows containing data. Cell References: Rows and Columns
A Row is a set of cells in the horizontal direction. Each row is numbered where the top-most row is numbered as 1. You can change the height of a row by moving your mouse over the row line and dragging it to your desired height.
A Cell is a single box that is the intersection of a column and row. Each cell is named according to its column label and row number, called a Cell Reference.
For example, the highlighted cell shown below is named cell B3. The name box is located at the top left corner of the worksheet and displays the cell reference of the active cell.
266
WORKING WITH DATA
You can enter text , numbers, Booleans, dates , times , and formulas into a cell.
TEXT
Text is any combination of numbers, spaces, and non-numeric characters. For example, Excel treats the following entries as text: 10AA109, 127AXY, 12-976, 208 4675. All text is left- aligned in a cell by default.
NUMBER
A number can contain only the following characters: 0 1 2 3 4 5 6 7 8 9 + - ( ) /. All numbers are right-aligned in a cell by default. Excel ignores leading plus signs (+) and treats a single period as a decimal. You can use the following characters to format the number: , $ % ., e.g. 1,000 , $13.25 , and 57%. All other combinations of numbers and nonnumeric characters are treated as text.
BOOLEAN
The values TRUE and FALSE are special values called Boolean values. A Boolean expression in a formula will evaluate to one of these two values. Note that surrounding either TRUE or FALSE with quotes, e.g. “TRUE”, does NOT indicate a Boolean value, rather a text value.
DATE AND TIME
The way that a date or time is displayed on a worksheet depends on the format applied to the cell. All dates and times are right-aligned in a cell by default. A date may be a short date , e.g. 11/27/2008 , or a long date , e.g. Thursday, November 27, 2008. Excel only recognizes dates staring from January 1, 1900. Also, Excel treats dates as numbers where 1/1/1900 is the same as 1, 1/2/1900 is the same as 2, and so on. A time is formatted as hours, minutes, seconds, and AM/PM, e.g. 1:12:04 PM. Times are also numbers, where each time is a number between 0 and 1. For example, midnight is 0 and noon is 0.5, and 11:59:59 PM is 0.999988425925926. If Excel cannot recognize a date or time that you enter, then it is treated as text.
FORMULA
A formula is a mathematical expression that evaluates to a single value. It can be used to perform a useful calculation in a cell. You can construct a formula using any mix of the following components: constants , mathematical operators , cell references and ranges , and functions.
268
CALCULATING WITH FUNCTIONS
One or more functions may be specified in a formula to help calculate a desired final value in a cell. The most commonly used functions can be found under the AutoSum button , which looks like a summation symbol in mathematics and found on the Home tab under the Editing se.
Consider the following table of student names and homework scores. The block of cells A1:D5 contain constant values (text and numbers).
269
To compute the SUM over the column of HW#1 scores in cell B7 perform the following:
- Click on the cell B7 to make it the active cell.
- Click the AutoSum button’s drop-down menu and choose Sum.
- Highlight cells B2 through B5 with your mouse (Excel will outline the cells in the chosen range). The formula shown in cell B7 will be =SUM (B2:B5) , where the function called SUM is given the range B2:B5 is its input argument.
- The cell B7 will display evaluated value (final answer) for the formula.
The AutoSum button contains other commonly used as listed below:
Sum : Add all the numeric values contained in the cells specified in the input argument. Average : Averages all the numeric values contained in the cells specified in the input argument. Min : Determines the smallest numeric value among all the cells specified in the input argument. Max : Determines the largest numeric value among all the cells specified in the input argument. Count : Counts how many cells contain numeric values in the input argument.
The IF function
The IF function is a useful function in that it allows you to place one of two values in a cell based on a decision.
For example, if you would like to place either the text value “YES” or value “NO” in E based on the decision whether Jane Doe has the highest HW#1 score, then you can enter the formula:
=IF (B2 = B10, "YES", "NO")
271
CUT , COPY & PASTE
You can copy (or cut) the contents of a cell and then paste the contents to a different cell.
Select text which you want to copy by using mouse or key board (by shift and arrow keys)
o Press CTRL+C (Depress the CTRL key while pressing the “C”) from the keyboard, for copy and CTRL+X for cut.
OR
o You can select a shortcut button on ribbon a copy. Where you want to paste, you have to take cursor by using key board arrow keys Or by mouse click.
o Press CTRL+V (Depress the CTRL key while pressing the “V”) from the keyboard, for paste the text. Which you already copy.
OR
o You can select a shortcut button on ribbon as “Paste”.
COPY/PASTE WITH AUTOFILL
To copy the contents of a cell to neighboring cells (along a row, column, or block of cells) use the AutoFill feature, which is the square located at the bottom right corner of the active cell.
272
For example:
To copy the number 22 in cell A1 (see left figure) to the cells in A2:A4 , first click on the cell to activate it. Move your cursor over the AutoFill square so that your mouse pointer turns into a thin cross. Click down on the square with the left mouse button and drag your mouse to A Release you’re the left mouse button and you will see the result of the copy/paste in A2:A4.
RELATIVE ADDRESSING
When you copy/paste formulas, Excel will preserve the structure of the formula but may change the cell references in the formula according to the direction in which the copy/paste occurs, called Relative Addressing.
For example,
If we copy the contents of cell B7 (=SUM (B2:B5)) and paste it across the row to cell D7 l, then cell D7 will contain the updated formula =SUM(D2:D5) , which is the total of all the HW#3 scores (see the left figure). Using the Auto fill feature again across each row, we can copy/paste the formulas at the bottom of the table to compute values for HW#2 and HW#
274
275
TEXT FORMATTING
Format text values using the commands in the Font Section such as the font type, font size, color, bold, italics, etc. Use the All Borders button to change the border attributes of blocks of cells.
Position values in a cell both horizontally and vertically. If the text you type in a cell is longer than the width of the cell use the Wrap Text button to fit the text by adjusting the size and shape of the cell. If you would like to merge text in adjacent cells, then highlight all the cells and then press the Merge and Center button.
NUMBER FORMATTING
Format number values as you would like them to be displayed using the Number section. You can determine the number of digits to be displayed after the decimal place as well.
