Excel Cell References and Formatting Quiz

Introduction to Computers Part III: Excel

• Excel is a spreadsheet application within the Microsoft Office suite
• Other applications in the Microsoft Office suite include Word (word processor), Access (database management), PowerPoint (presentation software), and Outlook.
• Spreadsheets are electronic equivalents of accounting worksheets, consisting of rows and columns.
• The intersection of rows and columns stores numbers and text.

Spreadsheets and their Uses

• Spreadsheets are used in research, industry, and business for data storage, manipulation, comparison, planning, and forecasting.
• In accounting, spreadsheets are used for budget preparation, balance sheets, trial balances, cash flow analysis, project costing, inventory management, payroll, and financial plans.
• Spreadsheets facilitate calculations like discounts, loans, taxation, investments, and interest rate calculations. They also aid in future predictions and forecasting.
• Spreadsheets are employed in scientific fields by mathematicians, engineers, physicists, chemists, meteorologists, and statisticians for statistical computations like averages, standard deviations, variances, R-squared values, regression coefficient calculations, analysis of variance (ANOVA), solving simultaneous linear equations, and creating frequency distribution tables.
• Spreadsheets are used in graphical representation within disciplines for analyses, such as histograms, line graphs, and pie charts. Examples include mortality and morbidity analyses in healthcare and statistical divisions.
• Spreadsheets are used by forex bureaus and multinational companies for currency conversions.

Spreadsheets Packages

• Many spreadsheet packages are available, including AS-EASY-AS, SUPERCALC, LOTUS 1-2-3, SYMPHONY, QUATROPRO, FRAMEWORK IV, and EXCEL, among others.

Spreadsheets

• Spreadsheet refers to the computer program or the sheet where the work is done.
• Some programs use the term "worksheet" for the sheet.

Overview of Spreadsheet Programs

• Key components of excel highlighted: Cell Address, Cell, Workbook , Cell Pointer.

• Other tools include: Cut, Copy, Paste, Format Painter, Clipboard, File, Home, Insert, Pg Layout, Formulas, Data, Review, View, Help

Starting and Quitting Excel

• To start Excel:
  • Click the Start button.
  • Scroll through the programs to find Excel and click on it to open.
  • Double-click the Excel shortcut icon on the desktop.
• To quit Excel:
  • Make sure your work is saved.
  • Click on the close button in the title bar.

Cells

• Cells are referenced using their row and column headings (e.g., B10).
• The active cell's reference is displayed in the name box.

Working in a Spreadsheet

• Entering data:

  • Click on a cell and type the data.
  • Press ENTER to input data.

• Replacing data:

  • Click on the desired cell and type the replacement data.

• Data types:

  • Text: Text data doesn't have numeric value
  • Numbers: Constant numeric values like test scores.
  • Formulas and functions: Mathematical equations.

Entering Text and Numbers

• Up to 255 characters can be typed in a cell, while exceeding the cell width can lead to partial display or scientific notation.
• To enter numbers as text, use an apostrophe (') before the number or an equal sign followed by quotation marks (e.g., = "25,000").

Entering Dates and Times

• Dates and times are stored as serial numbers representing the number of days since January 1, 1900.
• Dates are typically displayed in the m/d/yyyy format.
• Times are displayed as decimal fractions of a 24-hour day.

Cutting & Pasting Data

• To copy cell content:
  • Select the cell.
  • Select the 'Home' tab.
  • Click 'Copy' in the Clipboard Group or press Ctrl+C.
• To paste cell content:
  • Select the cell.
  • Select the 'Home' tab.
  • Click 'Paste' in the Clipboard Group or press Ctrl+V.

Selecting Cells

• To select a range of cells in a column or row:
  • Click the first cell, and drag the mouse pointer to highlight the desired range.

Adding Rows & Columns

• To insert a row or column:
  • Select the row or column heading.
  • Click the 'Home' tab.
  • Click 'Insert' in the Cells group.
  • The insertion occurs before the selected item.

Deleting Rows & Columns

• To delete a row or column:
  • Select the row or column heading.
  • Click the 'Delete' button in the Cells group of the Home ribbon.

Assignment 1

• Auto complete, Auto fill, Fill series.

Finding the right size

• To change column width:
  • Position the mouse pointer over the right edge of the column heading.
  • Wait for it to become a double-headed arrow.
  • Drag to the desired size.

Inserting a chart (or graph)

• This section shows how to create a chart using data in a spreadsheet.

Formulas

• Formulas are the core of a spreadsheet, performing calculations and generating results.

• A formula begins with an equal sign (=) and can include mathematical operators (+, -, *, /, ^), cell references, constants, parentheses, functions.

• Formula evaluation follows specific rules.

• Formulas can be displayed/updated by pressing F2 key.

Cell Referencing

• Cell references (analogous to addresses) refer to cell content within formulas.
• Different reference types exist (relative, absolute, mixed, circular), each affecting how references change when copied.

Relative, Absolute, and Mixed References

• Relative: Adjusts when copied, based on the location within the formula.
• Absolute: Remains constant when copied. (Dollar signs precede column letter and row number).
• Mixed: Either the column or row is fixed, but not both, when copied.

3-D References

• Formulas can reference cells across multiple worksheets within a workbook.

Operators in Formulas

• Arithmetic operators (e.g., +, -, *, /, ^) perform basic mathematical calculations.
• Comparison operators (e.g., =, >, <, >=, <=, <>) compare values, returning TRUE or FALSE.

Logical Functions

• Logical functions test conditions (e.g., TRUE, FALSE) and return results (TRUE/FALSE).

The FALSE function

• Returns a logical value FALSE.

The TRUE function

• Returns a logical value TRUE.

The NOT function

• Reverses the logical value

The AND function

• Checks whether all logical tests are true.
• Returns TRUE if all are TRUE and FALSE otherwise

The OR function

• Checks whether at least one logical test is true
• Returns FALSE if none are true, and TRUE if at least one is true.

The IF function

• A conditional function returning value if true or false, based on a logical test.
• Arguments include logical test, value if true/false.

EXAMPLE: IF

• Demonstrates practical use of the IF function, involving conditional calculations based on values in other cells.

Exercise 1:

• Employees of KNUST Computer Company are paid hourly, and overtime rates apply past 40 hours worked. A formula to compute pay based on input variables would solve this calculation automatically.

Exercise 2:

• A lecturer used Excel to calculate final marks and letter grades for students. Excel formulas were needed to compute final marks and assign letter grades; and to determine the number of students who received each grade.

Printing Spreadsheet Data

• Instructions on printing the spreadsheet data.

Sorting

• Method for arranging data in ascending/descending order (numerical/alphabetical).
• Each row is a record; each column is a field.
• Instructions for performing sorting based on a single column or multiple.

Filtering

• Isolating specific data rows in a spreadsheet based on criteria.
• Three types of filtering options: values, format, criteria.
• Steps outlined to filter based on a specific numeric condition (e.g. failing a course).

Protecting Your Worksheet

• Techniques for safeguarding spreadsheet data. This section outlines procedures to protect a worksheet (preventing unauthorized changes from occurring).