Download and install the following software that we shall be using while studying this course.

1. Microsoft Office 2010 https://drive.google.com/file/d/18p9ZHoxhRRFy01BfmLemjccMiFhRowbd/view?usp=sharing
2. For zipping and unzipping use this software (Winrar) https://drive.google.com/drive/folders/10QCneHc39W-xZJwyK6NvmwbcY62rajNS?usp=sharing

A spread sheet is a grid of rows and columns that accepts entry of data, allows editing, formatting and manipulation of numeric data. Spread sheets can also display data graphically with the help of charts and graphs.The file extension of spread sheet file is .xls or .xlsx

TYPES OF SPREADSHEETS

1. Manual spread sheets. The manual spread sheet is the most commonly used type by book keepers as a ledger book with many sheets of papers divided into rows and columns on which various amounts of money are entered manually using a pen or a pencil and manipulated manually with the help of a calculator.
2. Electronic spread sheets. An electronic spread sheet is a spread sheet prepared using a computer program that enables the user to enter values in rows and columns and to manipulate them mathematically using formulae and functions automatically.

Examples of electronic spread sheet programs

• VisiCalc,
• Lotus 1-2-3,
• Microsoft Office Excel,
• Quattro Pro,
• Microsoft Works,
• Multiplan,
• View sheet,
• Lucid 3D, etc.

ADVANTAGES AND DISADVANTAGES OF MANUAL SPREAD SHEETS

ADVANTAGES OF MANUAL SPREAD SHEETS

1. They are easy and cheap to acquire
2. They are easily portable
3. They are suitable for draft or rough work
4. They are not electronic, thus, can be used without electric power
5. No skills are needed, hence, even a computer illiterate can use manual spread sheets

DISADVANTAGES OF MANUAL SPREAD SHEETS

1. They require a lot of manual effort and time
2. Many errors are bound to be made
3. Rubbing out to correct errors makes the work untidy
4. They do not have pre-existing tables as opposed to electronic spread sheets
5. They are very small in size
6. They are not durable. They can easily wear and tear out
7. They do not have automatic formulas that would otherwise quicken the work
8. You cannot easily insert or delete extra columns and rows

ADVANTAGES AND DISADVANTAGES OF ELECTRONIC SPREAD SHEETS

ADVANTAGES OF ELECTRONIC SPREAD SHEETS

1. They have pre-existing tables, thus, no need to draw gridlines
2. They have in-built formulas and functions, enabling automation in calculations and work manipulations
3. There are minimal errors and in case of any, they are easily corrected
4. They have very large worksheets that can store a lot of work easily and for long
5. Extra columns and rows can be inserted and deleted without any bad effect
6. The work can be protected with passwords thus ensuring security
7. Work can be enhanced to look very attractive with various formats to suit the user’s needs
8. The records can be sorted and filtered to get only those that you want
9. They allow printing of multiple copies without re-creation

DISADVANTAGES OF ELECTRONIC SPREAD SHEETS

1. They are expensive to buy and maintain
2. They are electronic, thus cannot be used without electricity
3. They require computer skills and continuous training
4. There is data loss due to virus attacks and system failure
5. There are privacy problems like unauthorised access over networks
6. Health related hazards as they are associated with use of computers

FEATURES OF ELECTRONIC SPREADSHEET SOFTWARE

1. Workbook. This is a collection of multiple worksheets in a single file
2. Worksheet. This is a single page of a workbook. It is an equivalent of a work area in Microsoft Word. A worksheet is made up of rows and columns which intersect to form cells. Worksheets are labelled sheet1, sheet2, sheet3 by default, but they can be renamed. A workbook by default has 3 worksheets, however, these can be increased in the user’s interest and renamed
3. Columns. These are vertical lines which run through the worksheet. Worksheet columns are labelled by letters; A, B, C, D, E… which are displayed in grey buttons across the top of the worksheet
4. Rows. Are horizontal lines across a worksheet. Worksheet rows are labelled by numbers; 1, 2, 3, 4, 5… which are displayed in grey buttons across the left of the worksheet
5. A cell. This is an intersection of a column and a row. Each cell on the spread sheet has a cell address. A cell address is a unique name of a cell. It is given by the column letter and row number, e.g. A1, B5, G6, D12, C1, A4, B3, etc. Cells can contain; text, numbers, formulas, etc.
6. Range. It is a group of adjacent cells defined as a single unit. A range address is a reference to a particular range. It has a format of top left cell address: bottom right cell address. e.g. D5:G10
7. Value. This is a numerical entry in a cell. All values are right aligned in a cell by default.
8. Labels. This is a text entry in a cell. All labels are left aligned in a cell by default
9. Name box. This displays the address of the selected cell or cells. Also you can rename a selected cell or cells using the name box
10. Formula bar. Is a bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts
11. Auto-fill; this is the feature that allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text. To use this feature, you type one or two initial values or text entries, and then Auto fill does the rest using the fill handle, which is the small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross. Auto fill recognises series of numbers, dates, months, times and certain labels.
12. Sorting data is to arrange records in either ascending or descending order.
13. Filtering data is the displaying of records that satisfy the set condition from the parent list.
14. Graphs. A graph is a pictorial representation of the base data on a worksheet. Most spread sheets refer to graphs as charts. A chart is a graphical representation of data. A chart may be 2-D or 3-D
15. What-if analysis. Is a process of changing the values in cells to see how those changes affect the outcome of formulas on the work sheet. For example, varying the interest rate that is used in the paying-back table to determine the amount of the payments.
16. Freezing panes. This is where rows and columns are frozen such that they remain visible as you scroll through the data especially if the database is too big to fit on one screen.

USES/APPLICATIONS OF SPREAD SHEETS

1. Preparation of budgets
2. Preparation of cash flow analysis
3. Preparations of financial statements
4. Processing basic business information, like, job costing, payment schedules, stock control, tax records
5. Analysis of data from questionnaires
6. Presentation of information in tabular form, graphical or charts forms
7. Mathematical techniques and computation like trigonometry
8. Statistical computations like standard deviations.

OPERATORS

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using brackets.

Types of Operators

There are four types of calculation operators: arithmetic, comparison, text concatenation, and reference.

Arithmetic operators: These are used to perform basic mathematical operations such as addition, subtraction, division or multiplication; combine numbers; and produce numeric results.

Comparison Operators: These are used to compare two values, and the result is a logical value either TRUE or FALSE.

CELL REFERENCES

A Cell reference is an address given to a particular cell or group of cells on a worksheet. e.g. A2, B6, B3.

There are three types of cell references;

1. Relative cell reference. Here, the address of a cell is based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative cell reference takes the form: A1, B17, G20, and C2.
2. Absolute cell reference. Here, the exact address of a cell is used in the formula, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form: $A$1, $D$6, $B$3, $E$6.
3. Mixed cell reference. This is a type that uses both relative and absolute cell references at once. It may use an absolute column reference and a relative row reference or vice versa, e.g. $G17, B$14, D$2, $E2.

FORMULAS, LOGICAL FUNCTIONS, FUNCTIONS AND ERRORShttps://www.youtube.com/embed/DsZ7lsTaAYo FORMULAS

Formulas are equations that perform calculations on values in your worksheet and return a value in a chosen cell, e.g. =A2+B2,   = (A3+B3+C3+D3)/4,   =A6*B4,   =C4-D4,   =E10/G10

LOGICAL FUNCTIONS

Function     Description

AND           Returns TRUE if all of its arguments are TRUE; Returns FALSE if any argument is FALSE

FALSE       Returns the logical value FALSE

IF                Specifies a logical test to perform

IFERROR   Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

NOT           Reverses the logic of its argument

OR              Returns TRUE if any argument is TRUE

TRUE         Returns the logical value TRUE

FUNCTIONS

A function is a prewritten formula that takes a value or values, performs an operation, and returns a value or values in a chosen cell. Examples of functions in MS Office Excel include the following

ERROR ALERTS

Microsoft Excel displays an error value in a cell when it cannot properly calculate the formula for that cell. Below are some common error values and their meanings.

Error message     Meaning

1. ##### Column is not wide enough, or a negative date or time is Used.
2. #DIV/0! A number is divided by zero
3. #N/A! A value is not available to a function or formula
4. #NAME? Microsoft Office Excel does not recognise text in a formula.
5. #NULL! You specified an intersection of two areas that do not Intersect
6. #NUM! The numeric values used in a formula or function are invalid
7. #REF! The cell reference is not valid, e.g. 6E instead of E6
8. #VALUE! An argument or operand used is of wrong type

Working with Charts

A chartis a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends much easier. In this lesson, you’ll learn how to insert charts and modify them so they communicate information effectively.

Charts

Excel workbooks can contain a lot of data, and this data can often be difficult to interpret. For example, where are the highest and lowest values? Are the numbers increasing or decreasing?

The answers to questions like these can become much clearer when data is represented as a chart. Excel has various types of charts, so you can choose one that most effectively represents your data.

Types of charts

• Bar chart
• Line chart
• Pie chart
• Area chart
• Column chart
• Scatter chart
• Surface chart
• Stock chart
• Radar chart

Identifying the parts of a chart

Vertical Axis

The vertical axis, also known as the y axis, is the vertical part of the chart.

In this example, a column chart, the vertical axis measures the height—or value—of the columns, so it is also called the value axis. However, in a bar chart, the horizontal axis would be the value axis.

Data Series

The data series consists of the related data points in a chart. If there are multiple data series in the chart, each will have a different color or style. Pie charts can only have one data series.

In this example, the green columns represent the Romance data series.

Legend

The legend identifies which data series each color on the chart represents. For many charts it is crucial, but for some charts it may not be necessary and can be deleted.

In this example, the legend allows viewers to identify the different book genres in the chart.

Horizontal Axis

The horizontal axis, also known as the x axis, is the horizontal part of the chart.

In this example, the horizontal axis identifies the categories in the chart, so it is also called the category axis. However, in a bar chart, the vertical axis would be the category axis.

How to create a chart

Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart.

Click the Insert tab.

In the Charts group, select the desired chart category (Column, for example).

Select the desired chart type from the drop-down menu (Clustered Column, for example).

The chart will appear in the worksheet.

Chart tools

Once you insert a chart, a set of chart tools arranged into three tabs will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.

How to change chart type

From the Design tab, click the Change Chart Type command. A dialog box appears.

Select the desired chart type, then click OK.

How to switch row and column data

Sometimes when you create a chart, the data may not be grouped the way you want. In the clustered column chart below, the Book Sales statistics are grouped by Fiction and Non-Fiction, with a column for each year.

However, you can also switch the row and column data so the chart will group the statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart contains the same data—it’s just organized differently.

Select the chart.

From the Design tab, select the Switch Row/Column command.

The chart will readjust.

How to change chart layout

Select the Design tab.

Click the More drop-down arrow in the Chart Layouts group to see all of the available layouts.

Select the desired layout.

The chart will update to reflect the new layout.

To change chart style

Select the Design tab.

Click the More drop-down arrow in the Chart Styles group to see all of the available styles.

Select the desired style.

The chart will update to reflect the new style.

To move the chart to a different worksheet

Select the Design tab.

Click the Move Chart command. A dialog box appears. The current location of the chart is selected.

Select the desired location for the chart (choose an existing worksheet, or select New Sheet and name it).

Click OK. The chart will appear in the new location.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the new data. To fix this, you can adjust the data range. Simply click the chart, and it will highlight the data range in your spreadsheet. You can then click and drag the handle in the lower-right corner to change the data range.

If you frequently add more data to your spreadsheet, it may become tedious to update the data range. Luckily, there is an easier way. Simply format your source data as a table, then create a chart based on that table. When you add more data below the table, it will automatically be included in both the table and the chart, keeping everything consistent and up to date.

Worksheet Page Layout

By default, Microsoft Excel prints worksheets in portrait orientation (taller than wide). You can change the page orientation to landscape on a worksheet-by-worksheet basis.

Change the page orientation

Select the worksheet or worksheets for which you want to change the orientation.

Tip: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

On the Page Layout tab, in the Page Setup group, click Orientation, and then click Portrait or Landscape.

Notes: 

If you don’t have a printer set up, the Orientation option will appear dimmed, and you won’t be able to select it. To resolve this, you must set up a printer. The option also appears dimmed when you’re editing the contents of a cell. To resolve this, press Enter to accept the changes or Esc to cancel the changes.

Because you can set page orientation on a worksheet-by-worksheet basis, you might print some worksheets in a workbook in one orientation (such as portrait orientation) and other worksheets in the same workbook in the opposite orientation (such as landscape orientation). Simply set the orientation for each worksheet as appropriate, and then Print a worksheet or workbook.

Change the page orientation when you are ready to print

Select the worksheet, worksheets, or worksheet data that you want to print.

Click File > Print.

In the Page Orientation drop-down box, under Settings, click Portrait Orientation or Landscape Orientation.

When you are ready to print, click Print.

Create a template that uses landscape orientation by default

To save time, you can save a workbook configured to print in landscape orientation as a template. You can then use this template to create other workbooks.

Create the template

Create a workbook.

Select the worksheet or worksheets for which you want to change the orientation.

How to select worksheets

Note: When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

On the Page Layout tab, in the Page Setup group, click Orientation, and then click Landscape.

Note: If you don’t have a printer set up, the Orientation option will appear dimmed, and you won’t be able to select it. To resolve this, you must set up a printer. The option also appears dimmed when you’re editing the contents of a cell. To resolve this, press Enter to accept the changes or Esc to cancel the changes.

Make any other customizations as necessary.

Tip: If you want to customize several worksheets at one time, you can temporarily group the worksheets, make your changes, and then ungroup them. To group the worksheets, right-click any tab at the bottom of the worksheet. On the shortcut menu, click Select All Sheets. In the title bar, you should see the name of the workbook followed by the word [Group]. Then, change the orientation to landscape or make any other customization that you want. To turn off the grouping, right-click any tab, and then click Ungroup Sheets (or just click another worksheet tab).

Click the File tab.

Click Save As, then select the location where you want to save your worksheet. For example, click Computer (or This PC in Excel 2016), and then click Desktop.

In the File name box, type the name that you want to use for the template.

In the Save as type box, click Excel Template (*.xltx), or click Excel Macro-Enabled Template (*.xltm)if the workbook contains macros that you want to make available in the template.

Click Save.

The template is automatically sent in the Templates folder.

To use the template to create a workbook, do the following (for Excel 2016, Excel 2013, and Excel 2010 only):

Click File > New.

Click Personal.

Click the icon or name of your saved template.

Printing a Worksheet.

You can print entire or partial worksheets and workbooks, one at a time, or several at once. And if the data that you want to print is in a Microsoft Excel table, you can print just the Excel table.

You can also print a workbook to a file instead of to a printer. This is useful when you need to print the workbook on a different type of printer from the one that you originally used to print it.

Before you print

Before you print anything in Excel, do remember that there are many options available for an optimal print experience. For more information, see Printing in Excel.

Important: Some formatting, such as colored text or cell shading, may look good on the screen but not look how you expect when it prints on a black-and-white printer. You may also want to print a worksheet with gridlines displayed so that the data, rows, and columns stand out better.

Print one or several worksheets

Select the worksheets that you want to print.

Click File > Print, or press CTRL+P.

Click the Print button or adjust Settings before you click the Print button.

Print one or several workbooks.

All workbook files that you want to print must be in the same folder.

Click File > Open.

Hold down CTRL click the name of each workbook to print, and then click Print.

Print all or part of a worksheet

Click the worksheet, and then select the range of data that you want to print.

Click File, and then click Print.

Under Settings, click the arrow next to Print Active Sheets and select the appropriate option.

Click Print.

Note: If a worksheet has defined print areas, Excel will print only those print areas. If you don’t want to print only the defined print area, select the Ignore print area check box. Learn more on setting or clearing a print area.

Print an Excel table

Click a cell within the table to enable the table.

Click File, and then click Print.

Under Settings, click the arrow next to Print Active Sheets and select Print Selected Table.

Click Print.

Print a workbook to a file

Click File, and then click Print, or press Ctrl+P.

Under Printer, select Print to File.

Click Print.

In the Save Print Output As dialog box, enter a file name and then click OK. The file will be saved in your Documents folder

Important: If you print the saved file on a different printer, the page breaks and font spacing may change.