MICROSOFT EXCEL

              

              Microsoft Excel 

Microsoft Excel: This is an electronic spread sheet package that is used to Create, Analysed, Calculate, Manage and store data with the help of rows and columns. 

Excel:is part of the Microsoft Office suite and is available for Windows, macOS, and mobile devices.

   The place where rows and column meet is called Cell.

Key features of Microsoft Excel:

1. Title bar: Display the names of programme and document you are currently working on.

2. Ribbon bar: Display option used in managing data 

3. Quick access bar: Display option represented as icon 

4. Formula bar: Display the content of an active cell/display the formula use in calculation.

5. Heading: Is the identity of rows and columns 

6. Workbook: displays worksheet. 

Worksheet: Is the working environment of Microsoft word.

7. Scroll bar: Is used to navigate the worksheet.

There are two types of scroll bar:

i. Vertical scroll bar   ii. Horizontal scroll bar.

8.Data management: Store, organize, and analyze large datasets.

9.Calculations: Perform arithmetic, statistical, and financial calculations.

10.Charts and graphs: Visualize data using various chart types, such as column, line, pie, and more.

11.Formulas and functions: Use built-in formulas and functions, such as SUM, AVERAGE, and VLOOKUP.

12.Conditional formatting: Highlight cells based on specific conditions, such as values, formulas, or formatting.

13.PivotTables: Analyze and summarize large datasets using PivotTables.

14.Macros: Automate repetitive tasks using Visual Basic for Applications (VBA) macros.

15.Collaboration: Share and collaborate on workbooks with others in real-time.

Microsoft Excel is commonly used for:

1. Budgeting and financial planning

2. Data analysis and visualization

3. Reporting and dashboard creation

4. Scientific and engineering applications

5. Business intelligence and decision-making

How to launch Microsoft Excel 

1. Click the start button 

2. select all programme 

3. Select Microsoft office 

4. Click Microsoft Excel 

                  CHART

A chart is a graphical representation of data. This can be very effective especially when used for representation and report.

                    SPREADSHEET 

Spreadsheet: Is the collection of data using row and column.

Types of Spreadsheet 

1. Traditional spreadsheet: Is a collection of data using the row and column on a paper. Also is the analysis of data on a sheet of paper.

2. Electronic spreadsheet: This is the analysed data using row and column. Also is used on the computer electronically e.g lotus 1-2-3, Microsoft works spreadsheet, Open Office Calc and Google Drive Spreadsheet.

Pointer in Microsoft Excel

   Shape, Context, Action

                       

+. The default pointer shape; appears in most Excel workspace contexts. 

Moves cell pointer or selects a range of cells

↔️Appears when the pointer is on the border of a window

Adjusts height and width of rows and columns

✛ Appears when the pointer is between a row or column divider

Adjusts height and width of rows and columns

l Appears when you are editing cell contents

Provides a text insertion point

➡️ Appears when the pointer is on a column or row heading

Selects columns or rows

👼Appears when the pointer is placed over a cell border, graphic, or other object

Moves cells, graphics, or objects.

◩ Appears when the pointer is on the "fill corner" of a cell or cell range

AutoFills other cells with similar information fill corner 

↖️Appears when mouse is placed over the Ribbon

The standard Microsoft Windows pointer; selects Ribbon and menu options

           Component of worksheet/Some terms in Microsoft Excel 

1) A rows: is a horizontal line that slop from left to right/are horizontal group of cell that are labelled with number on the left side of the worksheet 

2) A column: is a vertical line that runs from top to bottom/it is a vertical group of cell that runs from top to bottom in a worksheet.

3) A cell: is the intersection of rows and columns also it is the identity of a number during calculation/the boxes you see in the grid of an Excel worksheet.

4) Grid line: shows the boundary between rows and columns i.e It is the boundary of cells/it is also horizontal and vertical lines in Excel that separate cells in a worksheet.

5) Heading: is the identifying of rows and columns/the top row of a table within a spreadsheet, usually containing labels or titles for each column.

6) Cell address: Is the identifying of a cell/is a combination of a column letter and a row number that identifies a cell on a worksheet.

7) Active cell: Is a cell that is ready to receive data furthermore known as cell you are currently working on/the cell in the spreadsheet that is currently selected for data entry. 

8) Cell pointer: is a Rectangle box that show the active cell/a visual representation of the active cell in a spreadsheet, typically represented by a blinking cursor or a colored highlight.

9) Cell reference:it is used to calculate two different cell in a worksheet or its used to identify, used to find cell across worksheet./a combination of a column letter and a row number that identifies a cell or range of cells.

        Example of cell reference

*A1: The cell at the intersection of column A and row 1

*B2: The second cell in column B 

B2:B10: A range of cells that includes cells B2 through B10

*A1+A2: A formula that adds the values in cells A1 and A2 

               Type of cell reference 

Relative reference: A reference that changes when copied

Absolute reference: A reference that does not change when copied

Mixed reference: A type of cell reference.

       Microsoft Excel operation

Are used to specify the type of calculation of performance.

     Order of calculation in Excel

(PEMDAS): parentheses, Exponent, Multiplication, Division, Addition and Subtraction.

         Type of operations 

(1) Arithmetic operator : ➕ (plus) -: addition e.g 5+2, * (Asterisk) -: Multiplication ✖️ e.g 2 ✖️ 2, / (forward, slash) -: Division ➗ e.g 10➗ 2 10/2, %(percent) -: percent e.g 20%, ^(Caret)-:Exponation e.g  4^2 means "4 to the power of 2"

(2) Comparison operator : 🟰 equal to sign -: equal to e.g =66, M+E=ME. >Greater than sign -: greater than e.g M4>O5 . <Less than sign -: less than e.g 22<70. <> Not equal to.

(3) Text concatenation: $ Ampersand -: to connect to or more value to produce on counting text value. (To join two value together) E.g John and Mary, John &Mary.

(4) Reference: (:) colon -: is used to calculate the range of cells, to range operator which produce one reference to all the scale between reference including two reference e.g = Sum (31:32). Comma (,)-: A union operator which combine reference operator to scale e.g =Sum (B1,B2, B5). Space: It is an intersection operator which combines a reference to scale. Pound: it is used to indicate an errors or name. It is used to indicate insufficient operations =Sum (Az#). @(at) : it is used to indicate.

        Excel worker payroll 

To work payroll in Excel, create a spreadsheet with columns for employee details like name, hourly rate, hours worked, overtime hours, then use formulas to calculate gross pay by multiplying regular hours by the pay rate, add overtime pay if applicable, and then subtract deductions like taxes and other deductions to arrive at the net pay for each employee; essentially, you set up columns for each data point and use basic math functions like multiplication and subtraction to calculate the final pay amount for each employee. 

        Key steps

Set up column:

*Employee Name

*Hourly Pay Rate

*Regular Hours Worked

*Overtime Hours Worked

*Overtime Rate

*Gross Pay

*Income Tax

*Other Deductions (e.g., health insurance, retirement)

*Net Pay

Calculate Gross pay:

Formula: = (Regular Hours * Hourly Pay Rate) + (Overtime Hours * Overtime Rate)

Calculate Dedication:

Income tax: Use a formula based on the tax bracket and gross pay

Other deductions: Multiply the deduction amount by the appropriate percentage or fixed value

Calculate Net Pay:

Formula: = Gross Pay - Total Deductions 

Important point to remember:

*Formatting: Use appropriate formatting for currency values. 

*Drag and Drop: Once you have the formulas set up for one employee, you can drag them down to apply them to all employees. 

*Tax Tables:For more accurate tax calculations, you may need to reference a separate tax table or use a lookup function in Excel. 

      Workers pay roll on Access

To work payroll on Access, you need to create a database with tables for employee information, pay rates, deductions, hours worked, and calculations, then design forms to input data, and use queries to generate paystubs and reports, ensuring accurate calculations based on your company's payroll rules and tax regulations. 

Key steps to set up payroll on Access:

1.Create a table

Employee Table:

*Employee ID (Primary Key)

*Name

*Address

*Social Security Number

*Bank Account Number

*Hire Date

*Job Title

*Pay Rate

*Federal Tax Withholding

*State Tax Withholding

Pay Rate Table:

*Job Title (Foreign Key to Employee Table)

*Pay Rate

*Overtime Rate (if applicable)

Deduction Table:

*Deduction Code

*Description

*Calculation Method (Percentage, Fixed Amount)

Hours Worked Table:

*Employee ID (Foreign Key to Employee Table)

*Date

*Regular Hours

*Overtime Hours

*Sick Leave

*Vacation Leave

2. Design Forms:

Employee Data Entry Form: To add and update employee details.

Timecard Entry Form: To input hours worked for each employee each pay period.

3. Create Queries:

Gross Pay Calculation Query: Calculate gross pay based on hours worked and pay rate.

Deduction Calculation Query: Calculate deductions based on gross pay and deduction settings.

Net Pay Calculation Query: Calculate net pay by subtracting deductions from gross pay.

Paystub Report Query: Generate a query to display employee details, gross pay, deductions, and net pay on a paystub format.

4. Incorporate Payroll Calculations:

Use functions like:

° "Sum": To calculate total hours worked.

° "If": To apply different pay rates for overtime hours.

° "Lookup": To retrieve deduction information based on employee data.

5. Important Considerations:

Tax Calculations:

Integrate federal and state tax withholding calculations based on current tax tables.

Payroll Frequency:

Set up the system to process payroll based on your company's pay period (weekly, bi-weekly, monthly).

Data Validation:

Implement data validation rules to prevent incorrect data entry (e.g., ensuring valid Social Security numbers).

Backup and Security:

Regularly back up your database and implement security measures to protect sensitive employee data.