LESSON 1 & 2: Advanced Spreadsheet Skills & Advanced Word Processing Application

Microsoft Excel

is a spreadsheet application used for data organization, analysis, and visualization

SUM() Function

allows to sum numerical data in any number of columns or rows by selecting them or typing them in

sum_range

=SUM(_______)

SUMIF() Function

is used to summate the values of cells in a range that meet the criteria that have been specified

range, criteria, sum_range

=SUMIF(________________)

Range

these are the cells that contain values and form which values will be checked against the criteria

Criteria

is the value which will define which cells will be summed up

Sum_range

this is the range of cells to be summed up

AVERAGE() Function

allows user to get the average of all numbers in the chosen cells

range to be averaged

=AVERAGE(___________)

COUNT() Function

is used to count the number of chosen cells that have a number value in them

range to be counted

=COUNT(____________)

IF() Function

allows users to achieve a particular value if the condition has been satisfied or TRUE, or if the condition has not been satisfied or FALSE

condition, result, if true, result if false

=IF(_________________)

AND() Function

it checks if certain conditions or criteria are true or false

VLOOKUP() Function

enables users to find something on the leftmost column of the chosen range and return a value related to it

lookup_value, table_range, column index number, range lookup

=VLOOKUP(___________________)

Lookup_value

this is the value to be found in the leftmost column of the table array ; this can be a cell reference or an actual value

Table_range

it is the table from which the data is retrieved ; this is the table being searched

Column_index_num

it is the column number in table array from which the value is to be retrieved ; the first column in table is Column 1

Range_lookup

it is a logical value that can be either “TRUE“ or “FALSE“ ; if left empty, the formula will consider it as “TRUE“ and will look for the closest value in Column 1 ; if “FALSE“ the formula will look for an exact match

CONCATENATE() Function

this function combines two or more text strings ; combines the values from two or more cells into another cell

text1, text2, text3

=CONCATENATE(___________)

Conditional Formatting

this feature enables users to emphasize certain cells with the formatting they want

Microsoft Word

a word processing program developed by Microsoft that allows users to create, edit, and format documents like letters, reports, and resumes

Inserting Pictures

are needed for readers to visualize what the textual content is all about

Joint Photographic Experts Group

JPG

Graphical Interchange Format

GIF

Portable Network Graphics

PNG

Wrap Text

it means that images and other objects that are inserted can be surrounded by the text in that same area of the document

In line with text

the image is located in the line where the text is located

Square

the text surrounds the image

Tight

the text surrounds the image, but the margin or space between the image and text is smaller

Through

it allows the text to cross between some part of the image ; if there is a fillable part within the image, texts may flow through it

Top and bottom

the text is located at the top and bottom of the image

Behind text

the image is located behind the text

In front of text

the image hovers above the text which obscures it

Insert Shapes

are especially useful in creating flowcharts for tasks, diagrams, illustrations, and comics

Header

is the placeholder for texts at the top margin of each page

Footer

is the placeholder for texts at the bottom margin of each page

Footnotes

are for short citations since it will be used directly at the bottom of the page where the text supported by the note is located

Endnotes

are used for long and more detailed citations since they are located at the end of the documents

Watermark

is a symbol, text, logo, or a pattern that is superimposed onto another symbol, text, or logo within a document

Page Colour

applies colour to the entire page of a document

Page Border

appears on all sides of the documents

Mail Merge

is a way of getting data from spreadsheets or other similar structured data format and transferring them to other documents such as letters and mailing labels

Form Document

it is generally the document that contains the main body of the message we want to convey or send

List or Data File

this is where the individual information or data that needs to be plugged in (merged) to the form document is placed and maintained

Label Generation

it creates a blank form document that simulates either a blank label or envelope of pre-defined size and will use the data file that you selected to print the information or individual address

Pictures

electronic or digital pictures or photographs you have saved in any local stored device

Clip Art

it is a .GIF type ; line art drawings or images used as generic representation for ideas and objects

Shapes

printable objects or materials that enhance the appearance

Smart Art

predefined sets of different shapes grouped together to form ideas that are organizational or structural in nature

Chart

type of material that represent data characteristics and trends

Screenshots

realistic image that you can capture while discussing any report