Microcomputer Application and Microsoft Excel (Notes)
Spreadsheet is the software (Program) that enables to store, manipulate and chart numerical data and composed of a huge amount of cells.
Worksheet is a single page of a spreadsheet consists of 256 numbers of columns labeled in letters A to IV and about 65,536 numbers of rows labeled in numbers starting by 1
Cell address is a name given to a cell that describes the location of an insertion point (cursor). E.g. A1, B5 etc.
Workbook is the collection of worksheets stored under one name. By default when you open an excel file (Workbook) it display 3 worksheets, however you can add or delete some worksheets.
Cell is simply the intersection between rows and columns, where you enter, edit, format your data.
Advantages
- A spreadsheet is of great advantages.
- Used to analyze and summarize Mathematical, Statistical and Financial data.
- Used to create a budget for your monthly living expenses.
- Used to analyze returns in the stock market, develop a business plans etc.
Running Microsoft excel application
- Using Start Button
- Click on “Start” button
- Select “Programs” command
- Click on “Microsoft Excel” option
- Using Run Command
- Click on “Start” command.
- Click on “Run” utility
- Type “EXCEL” in the Run dialog box
- Click on “Ok” button
Microsoft excel
Title bar
Is the bar that lies at the top of the window screen and displays program icon, current file name, current program name and window control buttons (i.e. Close, Minimize and Maximize or Restore).
Menu bar
Is the bar that normally lies below the title bar and displays commands in pull down list (Menus).
Excel contains about nine different menus, e.g. File, Edit, View, Insert, Format, Tools, Data, Window and Help.
Each menu has an underlined letter so that you can alternatively access that menu using keyboard.
Tool bars (Icon bars)
These are the bars distributed on the screen that displays commands in form of icons for quick access of a particular command.
Examples;
- Standard toolbar is the bar that normally lies below the menu bar.
- Formatting toolbar is the bar that normally lies below the Standard toolbar
- Drawing bar is the bar that normally lies above the status bar etc.
Spreadsheet
Is the area where you enter, edit and format your text (records) that made up of a huge number of cells
Formula bar
Is the bar that normally lies above the spreadsheet and displays cell address and its contents (formula )
Scroll bars
Are the bars that lies vertically at the right side and horizontally below the spreadsheet used to view hidden parts of a worksheet. (i.e. Vertical scroll bar and Horizontal Scroll bar)
Row and column headings
Are the headings that describes a column name (i.e. A – IV) and row name (i.e. 1 – 65,536)
Using Control Buttons
- Click on ‘Close’ button at top corner of the screen.
- Using Program Icon
- Click on the Program icon at the top left corner of the screen.
- Click on ‘Close’ command.
Using Keyboard
- Hold down ‘Alt’ key.
- Press ‘F4’ key
Using Menu Bar
- Click on ‘File’ menu
- Click on ‘Exit’ command.
Opening a blank workbook
Using New Office Documents command
- Click on “Start” button.
- Click on “New Office Documents” command
- Select “Blank Workbook” icon
- Click on “Ok” button
Using Menu bar
- Click on “File” menu
- Click on “New” command
- Select “Blank Workbook” icon
- Click on “Ok” button
Entering Data in a Worksheet
Adding text in workbook
- Select a cell you wish to add text
- Type your text.
- Press “Enter” key
Resizing column width and row height
- Point at the right border of a column heading, you wish to resize until mouse pointer changed shape or point at the bottom border of a row heading you wish to resize until mouse pointer changed shape.
- Drag the border inward to reduce or outward to enlarge the size of the column or row or double click on the border and the column or row will automatically adjusts.
Entering data in a worksheet
Types of data that can be entered in an Excel Worksheet
a) Label - any text is considered as label
b) Constant - these are all numerical values and symbols
c) Formula - these include all formula which can be entered and the formula starts with equal
Saving a new workbook
- Click on ‘File’ menu
- Click on ‘Save’ or ‘Save as ’ command'
OR: Click on ‘Save’ icon on a Standard toolbar
OR: Hold-down “Ctrl” key, then Press on ‘S’ key
NOTE: Save As dialog box will appear.
- Select the location under at ‘Save in’ text box
- Type the name for a file at ‘File name’ text box
- Click on ‘Save’ button
Opening an existing workbook
- Click on ‘File’ menu.
- Click on ‘Open’ command.
OR: Click on ‘Open’ icon on a standard toolbar
OR: Hold-down ‘Ctrl’ key, then Press on ‘O’ key
NOTE: Open dialog box will appear.
- Select the location of the file, at ‘Look in’ text box
- Click on the name of a file you wish to open
- Click on ‘Open’ command.
Editing a workbook
Replacing text.
- Click on a cell you wish to replace its record.
- Type a new record.
- Press “Enter” key
- Adding text in existing text.
- Select a cell you wish to add more text.
- Press ‘F2’ key
OR: Click on a text you wish to edit at the formula bar
OR: Double click on a cell you wish to edit its text.
- Start editing your text.
- Press ‘Enter’ key when you are done
Saving changes in existing workbook
Using menu bar
- Click on ‘File’ menu
- Click on ‘Save’ command
OR: Click on ‘Save’ icon on a Standard toolbar
OR: Hold-down Ctrl key, then Press on ‘S’ key
Applying merge and center
- Select a range of cells you wish to merge and center
- Click on “Merge and Center” icon on Formatting toolbar.
Saving a workbook with a password
- Click on ‘File’ menu
- Click on ‘Save as ’ command.
NOTE: ‘Save As ’ dialog box will appear.
- Click on ‘Tools’ command.
- select ‘General Options’ command.
- Type a password to open, and then Click on ‘OK’ button
- Re type the password to confirm and then click on ‘Ok’ button
- Click on ‘Save’ button when you are done.
Formula and Functions
The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.
The ability to perform calculations is one of the purposes of using a spreadsheet program. Some examples on the types of calculations that can be done are, totals, subtotals, count, average, standard deviation and much more.
Formulas
A Formula is simply described as the instructions for performing calculations.
The best way to construct a formula is to use Cell References instead of typing numbers in the formula. This enables Excel to automatically update the results of formulas when you change values in the cells referenced.
Formula is entered in a worksheet cell and must begin with an Equal sign “=“, then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between.
Example of formulas
A Function is simply the preset formula that come with spreadsheet applications.
Normally a function simplifies formula that you can type in manually.
E.g. Instead of =E4+E5+E6+E7+E8+E9+E10, you can type =SUM (E4:E10)
Functions perform calculations by using specific values, called arguments, in a particular order called syntax.
The syntax of a function always begins with an equal sign (=), the function name, and the arguments contained within the parenthesis.
Copying Formulas
The data fill feature allows you to copy formulas to adjacent cells.
In Excel, you can use the fill handle to accomplish the above task. It helps to avoid errors and to save time when you are entering formulas in your spreadsheet.
The fill handle is a small black box located in the lower right corner of the selected cell.
When the cursor is placed over the fill handle it changes to a thin black cross. You can then use the cursor to drag across adjacent cells.
Excel will copy the formula with the appropriate cell references to the new cells.
Common Error Messages
When writing formulas it is easy to make a mistake. Below are some common error messages.
#### - Means that the contents of the cell can’t be displayed correctly as the column is too narrow.
# REF! – Means that a cell reference is not valid.
# NAME? – Means that Excel does not recognize text contained within a formula.
Absolute cell reference and relative cell reference
i. Relative References
Relative Cell Reference adjusts copied formulas so that the cell references are changed relative to their new location.
By default, Excel is copying the formula relative to where you are, and where you are going.
Example; the original formula, in cell B6 is =SUM (B2:B5), when this formula copied one cell to the right (i.e. C6), then Excel automatically updates the formula using a Relative reference, making the new formula =SUM (C2:C5).
ii. Absolute References
Absolute Cell Reference do not adjusts copied formula therefore the cell references are maintained.
There are instances when you might want to use a constant in a formula. In this case you would use an Absolute Cell Reference
To create an absolute cell reference, you will need to add dollar signs ($) in front of the column and row elements for the cell referenced.
Example; The original formula in cell B8 is =B7*$C$1, since the amount of the increase is in cell C1 (i.e. 5%). When we copy the formula in B8 to column C8, the only cell number to change is B7 to C7, while the $C$1 cell remained constant in the copy process.
Formatting
Formatting cells
Data in the worksheet can be formatted using the formatting toolbar or format menu
Formatting text
- Select a cell (s) you wish to format
- Click a command buttons on the formatting toolbar in order to change the Font size, Color, align text and font style.
Number formatting
- Select the numbers you want to format
- Click Format menu
- Click Cells, a dialog box will open
- Select a Number tab
- Select a category you want on the category List
- Apply the formats you wish
- Click OK button to apply
Formatting cells
Format Cells command controls the formatting for numbers, alignment, fonts, borders, and patterns (color).
- Select the cells to which the formatting will apply
- Execute the Format Cells command
Number formatting
- Select a range of cells you wish to apply currency style.
- Click on currency style icon ($) on the Formatting toolbar
Changing a currency style
- Select a range of cells you wish to change its currency style.
- Click on ‘Format’ menu.
- Click on ‘Cells’ command.
- Click on ‘Number’ tab.
- Click on ‘Currency’ category.
- Select a symbol you want.
- Click on ‘Ok’ button.
Separating numbers from currency style
- Select a range of cells you wish separate its numbers from currency.
- Click on ‘Format’ menu.
- Click on ‘Cells’ command.
- Click on ‘Number’ tab.
- Click on ‘Accounting’ category
- Confirm a symbol and then click on ‘Ok’ button.
Working with Worksheets
Adding new column, row or worksheet
- Click on a Column (S) heading, Row (s) heading or Worksheet name beside a new one.
- Click on “Insert” menu.
- Click on the option you want. E.g. Columns, Rows or Worksheet.
Note:
> New column (s) is added at the left side of a selected column.
> New row(s) is added above a selected row(s)
> New worksheet (s) is added at the left side of a selected worksheet(s)
Removing column, row or worksheet
- Click on a Column (s) heading or Row (s) heading or Worksheet name (s) you wish to remove.
- Click on “Edit” menu.
- Click on “Delete” command.
OR: Click on “Delete Sheet” command, then click on “Yes” button to confirm.
Note: When you remove a Worksheet, it is permanently deleted i.e. you can’t restore.
Hiding column, row or worksheet
Changing text alignment
- Select the text you wish to change alignment.
- Click on ‘Format’ menu.
- Click on ‘Cells’ command.
- Click on ‘Alignment’ tab.
- Under “Orientation” text box, set an angle you want.
- Click on ‘Ok’ button when you are done
Note: Under “Text Control” text box you can apply a “Wrap text” effect in a Cell.
Freezing and unfreezing
A freeze is a technique that involves halting all body motion, often in an interesting or balance-intensive position.
Freezing action takes place on top and left side of the current cursor position.
- Click on ‘Window’ menu.
- Click on ‘Freeze panes’ command
OR; Click on ‘Unfreeze panes’ command
Formatting charts
You can modify your chart by using the Chart toolbar.
- Click on a down arrow beside “Chart Object” tool
- Select the object you wish to format. E.g. Legend
- Click on “Options” tool
- Select the tab you want, E.g. Pattern, Font etc.
Note; Under Pattern Tab, select the options you want, E.g. Border Style, Border Color, Border Weight, Shadow, Area (Fill) color or using Fill Effects colors.
- Click on “Ok” button when you are done.
Printing a worksheet
Previewing a worksheet
- Select a worksheet you wish to preview.
- Click on “File” menu
- Click on “Print Preview” command
OR: Click on “Print Preview” icon on Standard toolbar.
- Click on “Close” icon on Preview bar.
NOTE; You can apply Margins to help you to set your page. Also you can switch to Page Setup or Printing options.
Printing a worksheet
Before you print a worksheet you need to perform page set up
- Click File menu
- Click Page set up
- A box will appear
Post a Comment