CONVERTING FUNCTIONS

Functions and Arguments

Functions – are predefined formulas that perform calculation in Excel. A specific way in which a function is written is
called syntax.
Syntax – is the grammar rule we follow so that our function can work properly.
- Came from the Greek word syntaxis, which means “coordination” or “together.” In math and linguistics
(the study of language), we study syntax to understand the principles and processes.

Just like formulas, functions also start with an equal sign. The values found inside the parenthesis are called
arguments.
Arguments – supply the information for the function to evaluate. They are separated by commas or colons.
- Can also refer to both individual or single cells and cell ranges. Let us remember always to enclose
them with parenthesis.
Ex: =AVERAGE(E4:E9)
=SUM(D4,D9)
=COUNT(C4:C9,C17:C21)

TABLE OF FUNCTIONS

Function Description Syntax

SUM It computes the sum of the =SUM(cell addresses or
arguments range)
AVERAGE It computes the average of =AVERAGE(cell addresses
the arguments or range)
PRODUCT It computes the product of =PRODUCT(cell
the arguments addresses or range)
MAX It returns the largest value =MAX(cell addresses or
in the arguments range)
MIN It returns the smallest =MIN(cell addresses or
value in the arguments range)
COUNT It counts the number of =COUNT(cell addresses or
cells that have numeric range)
values
ROUND It rounds a number to a =ROUND(cell addresses or
specified number of digits range)

Colon and Comma

- The colons inside the arguments refer to a range of cells that are adjacent to each other. In the example
=AVERAGE(E4:E9), E4:E9 means the function will get the average of the values inside the cells E4 up to
E9. In other words, the average of the value in cells E4, E5, E6, E7, E8, and E9 will be used in the
computation.
- The comma inside =SUM(D4,D9) refers to cells D4 and D9 only. The comma specifies only the cells
indicated and not the entire range.
- In the example, =COUNT(C4:C9,C17:C21), the contents of cells C4 up to C9, and C17 up to C21 will be
considered in the count.

USING CELL REFERENCES AND AUTOFILL

 it helps us lock in specific cell addresses to retain their value. It is useful to copy formulas across a row or down
a column
 For instance, we can copy a formula from one cell and paste it into the cells we want the formula to be copied or
placed
 This is quite easy if we have a small amount of data. How will we be able to copy a formula into hundreds of
cells? Good thing, there is a way to simplify copying formulas into cells. It is called Autofill.

AutoFill
 To use AutoFill, we do the following steps:
1. Select the cell of the original formula
- we will see a small square in the lower right-hand area of the cell
2. Move the mouse pointer to the square until it changes into a plus (+) sign
3. Click the corner and drag the formula until we reach the last cell where the formula will be copied.
Types of Reference
1. Relative Reference
- By default, Excel uses relative reference. When a formula points to other cells (references), the
references are relative. When a formula is dragged down the column, the cell addresses adjust their
values in relation to the cells.
2. Absolute Reference
- Uses the dollar ($) symbol placed before the column and row address. This keeps the reference intact
when a formula is copied to other cells
3. Mixed Reference
- Is a combination of relative and absolute references. To retain the cell column of a value, we put a dollar
symbol before the column header. To retain its cell now, we put a dollar symbol before the row number

ORGANIZING DATA
- We make our date meaningful for easier interpretation and use. There will be times when our data
exceeds the cells visible on our monitor. Fortunately, Excel has a way that can help us organize our data.

Freeze Panes
- We freeze a portion of the worksheet to keep it visible while scrolling through it. This is useful for
checking our data in other parts of the worksheet without losing the headers or labels.

How to Freeze Panes

To do this, let us do the following steps:
1. Select the row of the first entry in our table just below the header.
- In this case, select row 3 where T-shirt is seen.
2. Go to the View menu, click the Freeze Panes button, and then click the Freeze Panes option.
- This keeps the row visible while the rest of the worksheet scrolls up. To freeze the panes vertically, we
select the column of the entry we want to freeze. This keeps the firs column intact when we scroll to the
right.
3. Go to View, click the Freeze Panes button, and click the Freeze Panes option.

To see the item column and the number of items available in each size, we need to select the intersection of
the items where the scroll starts.

Workbook View
- There are three different commonly used workbook views in Excel. We can find these in the Workbook
Views group. The Custom Views is clicked when we want to modify our workbook.

1. Normal View – is the default view of all worksheets in Excel.

2. Page Break View – makes it easy to change the location of page breaks in our workbook. It is helpful when
printing a lot of data from Excel.
3. Page Layout View – lets visualize how our workbook will look like when printed.

Sort Data
- Sorting data allows us to find values quickly. We can arrange data in ascending order or descending
order.
Let us do these steps to sort data:
1. Select the data that we want to sort.
2. Make sure to include the headers and other values attached to the data.
- This is important to keep the integrity of the data safe. When given a set of data, we do not want to
arrange only one column and leave the rest of the data untouched.
3. Select the Sort A to Z button.
- This will automatically sort the data alphabetically using the leftmost column as its basis. The values are
automatically reordered with the first column data. When we sort from Z to A, it reverses the order.

Filter Data
- We filter data to help us narrow it down, allowing us to view only the data we need. For Filter Data to
work properly, we must have headers for each column.
To Filter the data, let us do the following steps:
1. Select our table, Click filter in the Data menu.
- This will activate the filters for each column in the column header.
2. Select a drop-down arrow. A sorting option will appear.
- This will ask us how we want the data filtered
3. Choose Sort A to Z or Z to A and even Sort by color.
Excel can also show specific values we need to see. In the table, we want to see what large-sized items have four
pieces in stock. To do this, we do these steps:
1. Highlight the entire table
2. Go to Data menu and choose Filter
- This will put the filter symbol in the column headers.
3. Select the filter in size L and select the number 4.
4. This will shou us all the items that are sized L with four pieces in stock.
5. Click Ok.
- Excel will shou us the items that are sized L with four pieces in stock. The Filter sign will be shown beside
column header L and the row number for Beach Shorts and Swimwear. These are also indicators that a
filter has been used to show the data.

Conditional Formatting
- Conditional Formatting is used to apply styles to data that meet a certain condition or criteria. We can
also use this to highlight values that we want to detect quickly like failing marks on a record book or
empty cells in a worksheet.
To use this, let us do the following steps:
1. Highlight the cells to which we want to apply conditional formatting.
- We apply formatting to the cells with values 90 and above.
2. Select conditional formatting on the Home ribbon.
3. Highlight cell rules, and choose Greater than.
4. Supply the needed information in the dialog box, and click Ok.