Microsoft Excel and Access Essentials

Posted on Dec 5, 2024 in Computers

Excel

1. What is Excel?

Excel is a spreadsheet application from Microsoft Office used to organize data and perform calculations. It allows you to create statistics and graphics from datasets.

2. Three Ways to Sum in Excel

  1. Click the cell where you want the sum to appear, select AutoSum in the toolbar, select the range with your mouse, and press Enter.
  2. Click the cell, type the = sign, select the cells you want to sum, type the formula, and press Enter.
  3. Click fx (Insert Function), select the SUM function, and specify the range of cells.

3. Relative and Absolute References

When calculating a product like x * n in cell D5 with the formula =A5*B5, B5 is a relative reference. If you copy this formula to cell D6, it automatically becomes =A6*B6. To make a reference absolute, use $ before the row or column (e.g., $A$5).

4. Cell Displaying ######

When a cell displays ######, it means the content is too wide to fit. You need to increase the column width.

5. Sorting Data

To sort data in a sheet by any column in ascending or descending order, select the data, go to the Data menu, and choose Sort.

6. Selecting Items in Excel

  • a) A cell: Click on it.
  • b) A row: Click the row number in the header.
  • c) Multiple consecutive rows: Click the header number of the first row and drag to the last, or vice versa.
  • d) Multiple non-consecutive rows: Click the header number of any row, press and hold CTRL, and click on each additional row.
  • e) A column: Click the column letter in the header.
  • f) Multiple consecutive columns: Click the header letter of the first column and drag to the last, or vice versa.
  • g) Multiple non-consecutive columns: Click the header letter of any column, press and hold CTRL, and click on each additional column.
  • h) A range: Click on one corner cell and drag to the opposite corner.
  • i) Multiple ranges: Select the first range, press and hold CTRL, and select the remaining ranges.

7. Insert Function (fx)

The Insert Function dialog presents functions grouped by categories. When you select a function, it shows the syntax, rationale, and an explanation at the bottom of the window.

8. What is Fill?

Fill automatically completes a range of cells. If a cell contains a number, date, or time, Excel can extend the series, increasing values instead of just copying them.

Access

1. What is Access?

Access is a database application from Microsoft that organizes various types of information. Key components include tables, queries, forms, and reports.

2. What are Tables?

Tables organize information internally within a database. A table consists of rows and columns.

3. Records and Fields

Records are the individual entries in a table, representing a set of characteristics for each element. Each row is a record. Fields are common characteristics across all records, represented by columns (e.g., title, author, date).

4. What is a Query?

A query selects data from one or more tables or queries based on specified conditions. It allows you to filter, sort, and present data in a determined order, providing power to databases.

5. Types of Queries

  • Select Query: Chooses a group of records from one or more tables.
  • Crosstab Query: Groups data into categories and presents values in a compact format similar to a spreadsheet.
  • Make Table Query: Creates a new table with all or parts of other tables.
  • Delete Query: Deletes records from a table.
  • Append Query: Adds a group of records to a table.
  • Update Query: Makes changes to the data in a group of records.

6. Actions on Tables

  • a) Word: Links data to an existing or new Word document.
  • b) Publish with Word: Creates a Word document with the selected table in RTF format.
  • c) Review with Excel: Exports data to an Excel workbook for analysis.

7. What are Forms?

Forms present table records in a practical and elegant way, typically using a single window to display the fields of one record at a time.

8. What are Filters?

Filters select records that meet specific criteria. When you open a form, it reflects the entire table, but filters allow you to view only certain records, similar to a query form.