Electronic Spreadsheet (10)

 Introduction of Spreadsheet:

• Electronic spreadsheets are a computerized version of the spreadsheet which works with row and column for mathematical calculation.

Types of Electronic Spreadsheet:

1. VisiCalc: VisiCalc running on an Apple II, in 1979.
2. Super Calc: Supercalc was a spreadsheet application published by Sorkin in 1980.
3. Lotus 1-2-3: The Lotus 1-2-3 developed in November, 1982, and released for sale in January, 1983.
4. Microsoft Excel: Microsoft resealed the first version of Excel for the Macintosh on September 30, 1985 for windows.
5. Notable current spreadsheet software: Calligra sheets, Corel Quattro Pro, King soft Spreadsheets, Neo Office, Plan maker, Apple works, Kspread, Pyspread.

List of Spreadsheet Package:

1. KSpread
2. PlanMaker
3. Quattro Pro
4. AppleWorks
5. Gnumeric
6. OpenOffice Calc
7. Lotus 1-2-3
8. Thinkfree office
9. Mariner Calc
10. Ability
11. Star Office, Visi Calc

Introduction of MS Excel:

• It is Electronic spread sheet application software.
• Developed by Microsoft Corporation.
• In this application work with Row and Column (spread sheet)

Feature of MS Excel:

• Fast calculation
• Graphical Analysis (Chart, Graph)
• Data analysis
• Financial purpose
• List management
• Partial database management system
• executable file of excel (Excel.exe).

Organization of Electronic Spreadsheet application:

Workspace: Workspace is collection of multiple workbooks.

Workbook: Collection of worksheet/ various types of sheet.

Worksheet: It allows to make calculation that have been enter data. It contains the Row and Column.

Chart sheet: It holds the dialog box that can be using any action.

Macro sheet/ Module sheet: It contains programming coding as a any function.

Grid Line: It is sheet element.

Cell: The least elementary block unit of sheet.

Column: Series of vertical cells. Each column is labeled by Alphabet (A-Z)

Row: Series of Horizontal line/cells. Row reference is 1-65536, 1048576.

Cell Pointer: A cell shaped element is indicated thick dark border and move around a sheet.

Fill Handle: A Thin + symbol appear bottom right corner of cell pointer.

Creating New Workbook:

• Go to Office button/file menu/file Tab.
• Click on new option. or directly CTRL + N.

Saving new Workbook:

• Go to office button/file menu/file tab.
• Click on save option or directly press Ctrl + S key.
• Choose save location.
• Give the file name
• Click on save button.

Using formula:

Cell Address/ Cell Reference:

• It is location of cell in a sheet.
• It is the value of cell is reference.
• In cell reference Column reference and row reference are combined. Eg. A5:C6.

There are three types of cell reference in Excel:

1. Relative cell reference: The row and column reference can change when you copy the formula to another cell because the references are actually offsets from the current row and column. E.g. A1,B1,C1, in Cell C1 contains the formula: =A1+B1.
2. Absolute: cell reference in a formula that does NOT change when copied to another cell. Example: $D$S, $AB$58, $B$3,$B$2 etc. Cell C3 contains the formula: =$B$2+B3.
3. Mixed: cell reference in a cell with an absolute and mixed part. Example: $B8, A$5

Function argument:

1. No arguments - E.g. Now(), Date(), etc.
2. One argument - UPPER(), LOWER(), etc.
3. A fixed number of arguments - IF(), MAX(), MIN(), AVERAGE(),etc.
4. Infinite number of arguments
5. Optional arguments.

SOME Basic Function:

1) Text Function:

• LOWER: Converts all characters in lower case.
• UPPER: Converts all characters in upper case.
• TRIM: Removes duplicate spaces.
• CONCATENATE: Joins together two or more text string.
• LEFT: Returns a specified number of characters from the start of a supplied text string.
• MID: Returns a specified number of character from the middle of a supplied text string.
• RIGHT: Returns a specified number of characters from the end of a supplied text string.
• LEN: Returns the length of a supplied text string.
• FIND: Returns the position of a supplied character or text string from within a supplied text string (case sensitive).

2. SEARCH Function: It helps to find the starting position of a text string from another text string.

3) Date and Time function:

• Date: Returns a date, from a user-supplied year, month and day.
• Time: Returns a time, from a user-supplied hour, minute and second.
• Date value: Converts a text string showing a date, to an integer that represents the date in Excel's date-time mode.
• Time value: Converts a text string showing a time, to a decimal that represents the time in excel.
• Now: Returns the current date and time.
• Today: Returns today date.
• Date function: It creates the valid date according to Excel's date format. You need to specify the day, month and year.
• DATEDIF function: It returns the difference between the start date and end date.
• DATEVALUE: function: It converts a date (sorted as text) into a serial number that Excel recognizes as a date.
• EDATE Function: It returns a date which is a number of months before or after a specific date.
• DAY Function: It returns the day of the month ranging from 1-31 from the date you specify.
• DAYS function: It returns the difference between two dates.
• TODAY function: It returns a serial number that excel recognizes as the current date.
• MONTH function: It returns the month name (Jan-Dec) from the date you specify ( a valid date).
• EOMONTH Function : It gives you the date of a month which is a number of months before the date.
• YEAR Function: It returns the year (Jan-Dec) from the date you specify ( a valid date).
• WEEKDAY Function: It returns the day number of the week (Ranging from 1-7) for the date you have specified the date.
• WEEKNUM function: It returns the week number of the specified date.
• NETWORKDAYS Function: It returns the number of workdays between the start date and end date which exclude holidays.

4) Statistical Function:

• MAX: Returns the largest value from a list of supplied numbers.
• MIN: Returns the smallest value from a list of supplied numbers.
• AVERAGE: Returns the Average of a list of supplied numbers.
• AVERAGEIFS: is a perfect excel function when we want to calculate an average using more than one criteria.
• COUNT: Returns the number of numerical values in a supplied set of cells values.
• COUNT IF: Returns the number of cells (of a supplied range), that satisfies a given criteria.
• SUM: Returns the sum of a supplied list of numbers.

5.) Logical Function:

• AND: Tests a number of user-defined conditions and returns TRUE if all of the conditions evaluate to TRUE, or FALSE otherwise.
• OR: Tests a number of user-defined conditions and returns TRUE if any of the conditions evaluate to TRUE, or FALSE otherwise.
• NOT: returns a logical value that is the opposite of a user supplied logical value or expression.

6) Math and Tring: 

• ABS: Returns the absolute value (i.e. the modules) of a supplied number.
• SIGN: Returns the sign (+1, -1, or 0) of a supplied number.
• SQRT: Returns the positive square root of a given number.
• MOD: Returns the reminder from a division between two supplied numbers.

Changing default option: To change the default setting of excel:

Go to "office button/file - Option - General"

• Popular:
• Formula
• Proofing:
• Save:
• Advanced:
• Customize:
• Add-ins:
• Trust Center:

Sorting and filtering Data:

• Place a cursor on the Header Row.
• Choose Data Tab >> Filter to set filter.
• Click the dropdown arrow in the Area Row Header and Remove the check mark from select All, which unless everything.
• Then select the check mark for required option.

Advance Filter: It is used to filter the selected data by user criteria.

Method of Sort before Subtotal:

• Go to Data Tab.
• Click on sort under the sort and filter group.
• Sort by product, Sort on values and then select A to Z. in order.
• Click on ok.

Creating Chart:

Chart: A chart is a visual representation of numeric values.

Types of Charts:

• Column:
• Bar:
• Pie:

Inserting Header and footer: A header is the information that appears at the top of each printed page and footer is the information that appears at the bottom of printed page.

Adding header and footer:

• On the Insert Tab.
• In the group choose header and footer.
• Click add header.
• On the Design tab, in the Header and Footer Elements group, Click Current date to add the current date (or add the current time, file name, sheet name, ext.

Spell checking:

• To access the spell checker, Choose Review - Spelling or Press F7.
• To Check the spelling in just a particular range, select the range before you active the spell checker.

Various option available in spell check dialogue.

• Ignore one
• Ignore all
• Add to dictionary
• Change all
• Auto correct.

Importing from and Exporting into other Formats:

We can Import data From:

• From MS access
• From Webpage(html)
• Text
• CSV
• XML
• SQL Server

Excel file can be export in:

• XML
• Webpage
• Text (Tab delimited)
• Unicode text
• CSV (Comma delimited)
• Formatted text
• Text
• DIF (data interchange format)
• SYLK
• PDF
• XPS

What if analysis tool, pivot Table:

• What if Analysis tools in Excel, you use several different sets of values in one or more formulas to explore all the various results.
• What analysis tools are Goal seek, data table and Scenario manager.

Goal Seek: Goal seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another.

Goal seeking is the ability to calculate backward to obtain an input that would result in a given output.

Scenario: A Scenario is a set of values that Excel save different groups of values as scenarios and then switch between these scenarios to view the different results.

Dat table: A data table is a range of cells in which you you can change values in some in some of the cells and come up with different answers to a problem.

Pivot Tables: A Pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet or in an external data file.

Pivot tables are available under Insert Tab - PivotTable dropdown - Pivot table.

Pivot Charts: A Pivot chart is a graphical representation of a data summary, displayed in a pivot table.

Pivot charts are available under Insert Tab - PivotTable dropdown - Pivot chart.

Page setting, Previewing and printing:

Page setup dialog box:

Page Orientation:

Types of Page Orientation:

• Portrait (The default)
• Landscape

Changing Page Orientation:

• Choose Page Layout - Page Setup - Orientation - Portrait or Landscape.

Margins:

• Choose page layout - page setup - Margin drop down list, you can select Normal, Wide, Narrow, or the custom 
  Setting.

Adding Header and Footer:

• Choose Page Setup dialog box - Header or footer tab.

Types of operators in Excel:

1. Arithmetic operators: To perform basic mathematical operations such as addition(+ sign), Subtraction (- sign), Multiplication (* sign), division (/ sign), Percent (% sign), Exponentiation (^ caret sign).
2. Comparison Operators: You can compare to values with the following operators. Equal to (= sign), Greater then (> sign), Less than (< sign), Greater than or equal to( >= sign), Less than or equal to (<= sign), Not equal to (<> sign)
3. Text Concatenation operator: Use the ampersand (&) to concatenate (combine) one or more text string to produce a single piece of text.
4. Reference Operator: Combine ranges of cell for calculations with the following operators. : (colon), , (comma), _()Space.