A.
EXCEL
1. Shortcut Keys:
TAB: Next cell of the same row, or first cell of next row or creates a new row after last row.
CTRL-HOME: Cell A1
CTRL-END: Last cell
CTRL->: Last column of the same row (in steps of non-empty cells)
CTRL <-: First column of the same row. (in steps of non-empty cells)
F2: Enter the Edit Mode (just like double-clicking a cell)
CTRL-D: Fill the selected cell(s) with the value from the cell above it.
CTRL-R: Fill the selected cell(s) with the value from the cell to its left.
CTRL-`: Show/Hide formulas (the symbol ` is the one to the left of 1 at top of the keyboard)
2. WorkSheet Size: Last Cell: XFD (16384), 1048576
3. Data Types: Number, Label, Formula (“=” must appear in the first position of a cell)
4. Cell references: Absolute($E$9): Adjusted only when the cell identification itself changes
due to inserion/deletions of other cells/rows/columns
Relative (E9) Automatically adjusted when copied as well as above cases
5.
A3:F10 (Standard) A10:F3 F3:A10 F10:A3
6. Function categories on Insert Tab.
All, Financial, Statistical, Math & Trig, Lookup & Reference, Data & Time and Logical,
7. Financial functions:
FV(0.5%, 12, -1000) = $12,335 > 1000*12
FV(0.5%, 240, -1000) = $462,040 > 1000*240
PMT (rate, nPer, Principal)
PMT(5%, 120, -100000)= $1060.66 > 100000/120
PMT(5%, 120, -200000) = $2121.32 > 200000/120
PMT(5%, 240, -100000) = $659.96 > 100000/240
SLN (Cost, Salvage, Life): Straight-line depreciation (same depreciation amount for all periods)
SLN (20000, 5000, 5) = 3000 (The same for each of 5 years of useful life)
SYD(Cost, Salvage, Life, Period) Sum-of-Years Digits Dep (Amount decreases as time goes on)
SYD (20000, 5000, 5, 1) = 5000
SYD (20000, 5000, 5, 2) = 4000
SYD (20000, 5000, 5, 3) = 3000
SYD (20000, 5000, 5, 4) = 2000
SYD (20000, 5000, 5, 5) = 1000
DDB(Cost, Salvage, Life, Period) Double-Declining Dep ( Amount decreases as time goes on)
For the First period (year) DDB > SYD > SLN
For the last period (year) DDB < SYD < SLN
8. Some Statistical and Other functions.
Statistical Functions:
COUNT (H10:H14): counts cells that have numbers including Date/time
COUNTA (H10:H14): counts cells in the range that are not empty
COUNTIF (B15:H15, “>8”): counts number of cells in the given range that meet given
condition.
SUM(A5:A9) IF(Condition, TrueValue, FalseValue)
IF (F2>F3, F2, F3) = to take the larger of F2 or F3
POWER(4,3) = 4*4*4 = 64
9. AutoFill Option using the Fill Handle
Convenient way to copy a block of cells to any number of adjacent blocks of cells
Auto Fill Options Mini-Menu lets us choose (1) Copy Cells, (2) Fill Series or (3) Fill Without Formatting.
10. Charts
Compare values across different categories, effective for small number of categories (<=7),
X-axis (category Axis), Y-axis (value axis), vertical bars for values. Row Headings and Column Headings selected automatically become Categories and Data Series, respectively, of the chart being inserted.
Horizontal bars for values, effective for long category names, several subtypes (more like
Column charts) including Clustered, Stacked, 100% Stacked, 3-D, Cylinder, Cone, Pyramid.
Lines connecting data points to show trends over equal time intervals, Category axis (X-axis)
represents time with or without data point markers
Each data point represents a proportion of the whole data series, good only one single data series, slices can be exploded.
a. Area charts: More like line charts (trends over time)
b. XY (Scatter) charts: Helps to determine existing relationships between two sets of values.
c. Doughnut charts: More like Pie charts except that they have multiple rings each representing a data series so they are good for multiple data series.
1. Need to name a file before starting. (Not any more)
2. Objects: A database file contains various objects:
A table has records and all records of a table a common set of fields. A row of a table represents a record and its columns represent fields and a field shows a particular value of each and every record of a table.
3. Various Views:
4. Table building: Rows=Records Columns=Fields
Design View: For defining field names, their data types and properties(optional)
Datatypes: Text (default), number, Date/Time
DataSheet View: For entering record information
5. Some useful shortcuts:
CTRL’ Insert the value from the same field just above
CTRL= move to first blank record
TAB Next field of same record or first field of next record
F11 Navigation Pane Shutter Bar Open/Close
CTRL+ Adding a record
CTRL- Deleting a record
6. Navigation Pane: Expands automatically when Access is open. This is where you organize various objects existing in the database file that is open. When the menu is open it shows two Option Lists:
· Navigate to Category.
Custom
Object type
Tables and Related Views
Created Date
Modified Date
· Filter by Group
Options vary according to the option selected in the above List.
For example when the “Tables and Related Views” option is chosen, you see the following options:
Tables
Queries
Forms
Reports
All Access Objects.
7. Using Compact and Repair Database
Database needs to be compacted regularly for efficiency sake. This command is available in the “info” Option of the File Tab which is the default option of the File Tab.
8. Setting up Filters (Selecting certain records meeting certain selection criteria) by “Advanced Filter/Sorting”
Command.
9. Primary key field (Optional) No two records of a table should have same key field value
No two tables of the DB should have same key field name.
Often for establishing relationships between two tables.
10. Object Importing/Exporting: Both cases, a DB file must preexist.
11. Results of filter application saved as a “QUERY” by “SAVE AS QUERY” button
12. After a query has been created from a single table, any legitimate change made to the query will also
automatically change the underlying table and vice versa.
13. One-to-many relationship is created from the primary key of a Primary table to a foreign key of the
Related table and not the other way around. “Cascade Update Related Fields” option is to direct Access
to automatically update all foreign key values in a related table when the Primary key value is modified
in the Primary table.
14. When the Referential Integrity is enforced, (a) you cannot enter a foreign key value in the related table
unless the Primary key value exists in the Primary table and (b) you cannot delete a record in the
Primary table if it has a related record in other related tables.
15. Filter Criteria definition Example:
----------------------------------------------------------------------
Field Day Time
Sort Ascending Descending
Criteria TUESDAY <10:00 or >16:00
OR MONDAY
-----------------------------------------------------------------------
Note that in the above example, (1) Tuesday classes either before 10AM or after 4PM are to be selected while (2) all Monday classes are to be selected regardless of Time.