Microsoft Excel Fundamentals: A Comprehensive Guide, Lecture notes of Programming Languages

Download Microsoft Excel Fundamentals: A Comprehensive Guide and more Lecture notes Programming Languages in PDF only on Docsity!

Microsoft Excel

programming(mku)

DEFINITION

Spreadsheet is an application program used to calculate, organize and analyze numerical data.

EXAMPLES OF SPREADSHEETS

1. Microsoft Excel
2. Lotus 1,2,
3. Viscale
4. Quatro Pro.

ADVANTAGES OF ELECTRONIC

SPREADSHEET

1. Utilizes the powerful aspects of the computer like speed, accuracy and efficiency to enable the user to quickly accomplish tasks.
2. Offers a large virtual sheet for data entry and manipulation.
3. Produces neat work since it has editing tools
4. Have better document formatting capabilities
5. Have inbuilt formulae called functions that enable the user to quickly manipulate mathematical data.

EXAMPLES OF AREAS

SPREADSHEETS ARE APPLICABLE

1. Budgeting
2. Financial analysis
3. Database Management
4. Charting & Graphing
5. Mathematical and scientific
6. Forecasting – ‘what if’ analysis

COMPONENTS OF A SPREADSHEET

1. Worksheet – the working area made up of rows and columns where data is entered.
2. Database – a collection of related data items organized so as to provide a consistent and controlled access to items.
3. Graphs (charts) – is a pictorial representation of the base data on a worksheet. It enables the user to present complex data elements from a worksheet in a simple and easy to understand. Examples; pie charts, line charts, and bar chart

MS. EXCEL SCREEN FEATURES/PARTS

Formula bar – is where a cell entry or a formula is displayed before being entered into the selected cell Worksheets - the working area made up of rows and columns where data is entered Cells - an intercession between a Column and a Row each cell is referenced using the column label followed by the row label e.g A2. Name Box – displays the name of the active cell Active cell pointer – indicates the current active cell which is highlighted with a dark outline. Cell address – the combination of a column header and a row header that indicate the location of a specific cell. E.g. A

STARTING MS. EXCEL

• Click on Start Button
• Point at Programs
• Click on Microsoft Excel
• Click on Microsoft Office Excel 2007 MOVING THROUGH THE WORKSHEET USING THE MOUSE Click on a particular cell to make it active USING THE KEYBOARD
• Ctrl + Home – Takes you to the first cell in the worksheet
• Ctrl + End – Takes you to the last edited cell
• Ctrl + Arrow Right – Takes you to the last column in the worksheet
• Ctrl + Arrow Down – Takes you to the last row in the worksheet
• Cursor movement keys MAKING SELECTIONS
• Click and hold down the left mouse button & drag the pointer over the range or cells
• Click on the first cell in the range, then hold down the Shift Key & click on the last cell in the range
• Click on the first cell in the range, then hold down the shift key & move through the range of cells using the arrow keys
• To make a non-continuous selection, hold down the Ctrl Key, then select each range by holding & dragging the cell pointer through it. INSERTING A WORKSHEET
• Combine Shift key with F ENTERING DATA INTO A WORKSHEET Data being typed is displayed in the formula bar and it is entered by pressing the enter key, an arrow key or by clicking into another cell. Cell data entries Entries into a worksheet can be classified into four categories namely:

1. Labels

SPREADSHEET- MICROSOFTEXCE SPREADSHEET- MICROSOFTEXCELL

Product() multiplies all the values in the argument. E.g. =product(40,3,2) returns 240 B) Statistical functions Average() returns the arithmetic mean of its arguments. E.g =average(A3:C3) Count() counts the number of cells that contain numbers within a range e.g. =count(A3:C3) CountIf() conditionally counts the number of cells within a range that meets a given condition. E.g=countIf(A3:C3,”>50”) Max() – returns the largest value in a set of value or within a range. E.g =max(A3:E3) Min() – returns the least value in a set of value or within a range. E.g =max(A3:E3)

C) Logical functions IF() – returns a value or label if a condition you specify is evaluated to True and another is evaluated to False. E.g if A3,B3,C3,D3 and E3 contained a set of marks 35,50,80,60 and 45, grades are to be awarded as follows: The Function has 3 arguments

1. The comparison to be performed
2. The value to be returned if the result of the comparison is TRUE
3. The value to be returned if the result of the comparison is FALSE NB/ The two values may be constant values, text or further expression The syntax is: - = If (Conditional Text, True Value, False Value) 80 to 100 A 60 to 79 B 40 to 59 C Below 40 Fail, To assign the grade use =if (A3>=80,”A”,IF(A3>=60,”B”, IF (A3>=40,”C”, IF (A3>=60,”Fail”,)))) And() – returns True if all its arguments are true or false and if any argument is False. E.g. =And(3+2=5, 2+2=4) returns True. Or() - returns True if all its arguments is true or false if both argument are False. E.g. =Or(3+2=7, 2+2=4) returns True Not() – negates the unary operand e.g =Not(3+2= 5) returns False D) Date and time Today() – returns a number that represent today’s date e.g =today() Now() – returns the current date and time formatted as date and time. E.g =Now() Date() – returns a serial number that represents a particular date. E.g. Date(107,1,4) returns January 4,2007. Year = (1900 +107), month = 1, day = 4.

Hour(), Minute() or Second() returns the current hour as number 0 to 23, minute from 0 to 59 and seconds from 0 to 59 respectively

INSERTING FUNCTIONS

• Click the fx button on the standard toolbar
• In the function dialog box displayed, select the category
• In the function list box, select a function
• Specify the function argument than click ok. COUNTIF It the number of cells within a range that meet the given criteria. FORMULA =Countif(Range,Criteria) RANGE: It is the range of cells from which you want to count cells CRITERIA: Is the criteria in the form of a number, expression or text that defines which cells will be counted. EXAMPLES Suppose A3:A6 contains apples, oranges, peaches, and apples. The Formula will be: - =Countif(A3:A6,”apples”) The answer will be 2 Suppose B3:B6 contain 32, 54, 75, 86, 54 The Formula will be: - =Countif(B3:B6,”54”) SUMIF Adds the cells specified by a given criteria FORMULA =Sumif(Range,Criteria,Sum Range) SUM RANGE: They are the actual cells to sum -The cells in the Sum range are summed only if their corresponding cells in the range match the criteria. -If sum range is omitted, the cells in range are summed EXAMPLE Calculate the sum of commission for properties whose value is greater than $160,000. Suppose A1:A4 contain the following property values: - 100,000, 200,000, 300,000, 400,

B1:B4 contain the following sales commission on each property: - $7,000 $14,000 $21,000 $28, The Formula will be: - =Sumif(A1:A4,”>160000”,B1:B4) The answer will be $63, AUTO FILLING A FUNCTION

• Select the cell in which it is contained (Function)
• Point to the Auto fill handle, Click, hold down and drag to the desired location

EDITING WORKSHEET

Involves deleting entries, copying, moving, finding and replacing, spell checking etc. Editing cell entries There are two methods of editing a cell entry:

• Using the formula bar – click the cell to display its content on the formula bar. Delete or insert required word or text.
• Double click the cell to place the insertion pointer in it and then type or modify the entry. DELETING WORKSHEET RANGE Select worksheet range Press the delete key RENAMING A WORKSHEET
• Right click on the Worksheet Name
• Click on Rename
• Delete the existing name & type the new name
• Press Enter

DELETING A SHEET

• Right click on the sheet name
• Click on Delete
• Click Yes COPYING A SHEET
• Right click on the sheet name
• Click on Move or Copy
• Click on the Sheet to move before it
• Click on create a copy checkbox
• Click Ok. MOVING A SHEET
• Right click on the sheet name
• Click on Move or Copy
• Click on the sheet to move before it
• Click Ok. COPYING & PASTING To make copies of data
• Select the data you want to copy
• Click on Home Menu
• Click on Copy
• Place the cursor to the desired destination cell
• Click on Home Menu
• Click on Paste MOVING DATA
• Select the data you want to move
• Click on Home Menu
• Click on Cut
• Place the cursor to the desired destination cell
• Click on Home Menu
• Click on Paste INSERTING AND DELETING ROWS/COLUMNS
• Position the cursor where the new row or column will appear
• Click Home
• On the insert tab click either insert sheet row or column DRAG AND DROP
• Select the range to be moved. Place the mouse pointer at the edge of the selection until the pointer changes into an arrow.
• Click and drag the selected range to the required position Release the mouse button to complete the move to the new location FREEZING PANES It is used to anchor tittles This helps to keep the tittles or headings stagnant while other data is scrolled. Procedure
• Click at the beginning of the row next to the one to freeze. E.g. If you want to freeze Column 1, click at the beginning of Column 2
• Click on View Menu
• Click on Freeze panes and choose the desired option (freeze panes, freeze Top Row, freeze First Column) SPLITTING A WINDOW WITH PANES This enables you to view different areas of a worksheet simultaneously. You can use panes when you want to scroll together vertically or horizontally You split your window into 2 – 4 panes Procedure
• Position the mouse pointer where you want the panes to appear.
• Click on View Menu
• Click on Split REMOVING SPLITS METHOD 1
• Double click the split bar you want to remove METHOD 2
• Click on View Menu
• Click on Split

FORMATTING WORKSHEET

It refers to enhancing the appearance of the worksheet to make it more attractive and appealing to the reader. It includes: changing the text color, typeface(font), size, styles and alignment. FORMATTING LABELS  Select the cells  Click Home  Choose by clicking the different formatting features from the formatting ribbon (toolbar) FORMATTING NUMBERS  Select the cells that have numbers  Click Home then the Format tab  Click Format cell  Select the number tab  Choose the number format as explained below:

• Click Ok.

SORTING BY MORE THAN ONE FIELD (COLUMN)

• Click on any cell within the data to be sorted
• Click on Home Menu
• Click on Sort &Filter
• Under Sort By, select the desired header row from the pull down menu & select either ascending or descending button.
• Under then by, click the down arrow button to open the pull down menu, select the header row & select either ascending or descending. FILTERING DATA Filtering does not rearrange the data; it temporarily displays only those rows that contain a certain value or that meet a set of search conditions called criteria & hides rows you do not want displayed. There are two ways of filtering a list in Ms Excel AUTO FILTER To filter your list quickly by matching cell contents or using simple comparison criteria such as: Display rows where sales are greater than 2000

ADVANCED FILTER To filter data based on complex or computed criteria. Also used to automatically copy data that meet specified to another location.

TO FILTER A LIST USING AUTO FILTER

• Click on any cell within the list
• Click on Home Menu
• Point at Filter and Sort
• Click on Filter
• Drop down arrows are placed on the field headings in the data list. Click the drop down arrow on the field headings. A drop down list appears
• Select the desired item from the drop down list. The item you selected in a column list is called the Filter Criteria

REMOVING A FILTER To remove a filter for a single column, select ALL from the column drop down list and select Filter from the Sort and Filter Tab. NB/ You can also filter with top ten or custom

CHARTS

A chart is a graphical representation of numeric data worksheet

• Each Cell (piece of data) represented in the chart is called a Data Point. These points are represented on the charts by bars, columns, lines or some other graphical device - A group of related data points is called Data Series - A chart created in a worksheet in a graphical object is called an Embedded Chart. It is saved as part of the worksheet on which it is created.

EXAMPLES OF CHARTS

• Column Charts
• Bar Charts
• Pie Charts

CREATING A CHART You can create an Embedded Chart or a chart sheet.

Procedure

• Select the cells that contain the data you want the Column or Rows to appear. In the chart, include the cells that contain in the selection.
• Click on Insert Menu
• Click on Chart type from the tab
• Select the Chart Type Set the following from the Home menu from the relevant tab Chart title, Y & X – axis, Legend Tab, and Data Labels

FORMATTING CHART AREA This enables you to change the chart background area & put a border around the chart.

Procedure

• Click on the chart
• Click on Layout or Format Menu to set the Patterns, line style, line colour & weight to apply as a border, colour & fill effects for the chart area

PRINTING WORK BOOK Before sending the work to a printer you need to preview it so that you make any necessary adjustment

Print preview Print preview displays the worksheet, chart or selected range exactly the way it would appear when printed.

• Click the Print Preview button on the standard toolbar -The worksheet will be displayed in the print preview window with the status bar reading preview
• To make any page adjustment, click the setup button to setup the following:  Page orientation  Margins  Size

 Sheet options such as row and column heading, gridlines and page order are used to specify how multiple sheets are to be printed.

• To print, click the print option to display print dialog box -Select printer -The print what options are:  Selection:- this prints the selected worksheets in the workbook  Workbook – prints all the worksheet in the workbook  Selected chart – prints the selected chart only -Specify the number of copies, then click OK to start printing the job.