Excel (Theory)

Seite 1

- A formula in excel always starts with =.

- Whenever working in a spreadsheet, always press the sqaure above row 1 and next to column A to autofit the whole spreedsheet.

- Refer to a cell by the column heading which is the letter then the row heading which is the number (B23).

- Always work with spreadsheets in the .xslx format and NEVER in the .csv format.

- Use the fat white cross to select cells of a range.

- Use the thin black cross to copy cell contents across.

- To copy a formula to a new cell do CTRL + C then go to the cell where the formula is to be placed and do a paste special.

- Relative cell reference means that the cells mentioned will change as the forumla is copied or moved.

- Aboslute cell referencing makes sure the ell mentioned is the only cell that will be used in the formlua even when it is copied or moved.

- To absolute reference to a cell, press F4 to get $A$13.

- A function is +, -, * and /.

- To name a cell range select the range of cells (A3 to G6) then go to the formula tab and define name option, apply the name and make sure the scope is on workbook.

-To freeze a row(s) go to the view ribbon and go to the column A of the row below the row to be frozen and select freeze row(s).

-To freeze a column(s) go to the view ribbon and go to the row 1of the column below the row to be frozen and select freeze column(s).

DATA VALIDATION

- Data validation is found in the data ribbon and is used to control what data is entered into a cell and stops invalid user input.

- If a user copies data from an unvalidated cell the validation is destroyed or replaced.

- When data validation is made a user has 8 output options:

*Any Value: no validation performed.

*Whole Number: only allows whole numbers that fit within a certain range of inputs (e.g. 0 to 10).

*Decimal: only decimals allows that fit within a certain range of inputs (e.g. 0 to 6).

*List: only allows values from a predefined drop down list.

*Date: only allows dates between a certain date period (e.g. January 15th to March 23th).

*Time: only allows times between a certain time period (e.g. 8:00am to 4:00pm).

*Text Length: only allows data based on the number of digits or characters.

*Custom: allows the user to make a custom validation formula.

Seite 2

- Page layout view is used to add headers and footers quickly and easily and change the page orientation.

- Page break preview allows a user to see their spreadsheet in the print preview format and make the necessary changes, e.g. making the spreadsheet fill a single page when printed.

- To print gridlines and/or headings go to the page layout ribbon and then tick the option of print under the headings and/or gridlines option.

EXCEL FUNCTIONS

>SUM

Adds two or more numbers together as a more efficient way of carrying out the addition of multiple cells in a cell range (=SUM(G2:G9)).

>AVERAGE

Calculates the mean of a list of numbers in a cell range (=AVERAGE(G2:G9)).

>MAX

Finds the maximum figure within a named cell range (=MAX(G2:G9)).

>MIN

Finds the smallest figure within a cell range (=MIN(G2:G9)).

>INT

Takes the whole number part of a number and ignores everything after the decimal point (=INT(D5)).

>ROUND

This function takes the content of the cell and rounds the number up or down mathematically (=ROUND(B4)).

>COUNT

ONLY counts the number of numeric values in a list or given named cell range (=COUNT(C2:C18)).

>COUNTA

Counts the number of numeric values AND text values displayed in a given cell range (=COUNTA(C2:C18)).

>COUNTIF

Counts the number of cells in a cell range that meet the requirements of a particular condition, whether it be a number, text, and inequality, or a cell reference (=COUNTIF($D$2:$D$25, "Fruit")).

>IF

Test for a specific condition to see if it is TRUE or FALSE according to the logic statement inputted; the first part of an IF function is the logical_test containing a value or expression that can be gauged as TRUE or FALSE; the next part is the value_if_true containing the value to be displayed if the cell meets the requirements of the logical_test; the final part of an IF function is the value_if_false containing the value or computation to be carried out if the cell fails to meet the requirements of the logical_test (=IF(A2=6, A1*2, ""). (IF cell A2 is 6 multiply 2 by A1 and display that value is A is not 6 display an empty cell).

>IFS

A way of efficiently nesting multiple IF statements together in a less confusing manner.

>NESTED IF

If a question requires 4 different TRUE or FALSE statements 3 IF functions will be used. The nested IF is ALWAYS placed in the value_if_false. A nested VLOOKUP is always placed in the value_if_true part of an IF statement. When the AND function or the OR function is being nested with an IF the formula is =IF(AND(logical test(s) for the AND function), value_if_true, value_if_false) or =IF(OR(logical test(s) for the OR function), value_if_true, value_if_false).

>AND

AND is a logical function that checks more than one logical condition at a time usually nested with IF and gives TRUE if all conditions placed in the logic are met. e.g. =IF(AND(A1>0,A1<5), 10%, "")- If A1 is greater than 5 AND less than 0 display 10% if not display an empty cell.

>OR

OR is a logical function to test multiple conditions at a time giving TRUE if any logic condition met is evaluating to TRUE. e.g. =IF(A3>60,C3>60, 45%, "") : if A3 is greater than 60 OR C3 is greater than 60 display 45% if both criteria's are not met display an empty cell.

>SUMIF

Totals the cells that meet a single criteria.

>VLOOKUP

Carries out a vertical lookup of data from a vertically organised table. The lookup_value is the value to look up and compare; the table_array is the table from which a value is to retrieved from; col_index_num is the column in the referred table from which a data value will be coming from; range_lookup is always FALSE or 0 at IG level to display an exact match.

Seite 3

-Abnormal data is when the data cannot be accepted and processed in a function.

-Normal data is data that is accepted and processed by the function because it is within the limits of requirements.

-Extreme is when it exceeds the max value allowed or is under the value allowed so it cannot be capped by function.

Seite 4

Validation is a process when data is checked to see if it fits certain inputted criteria.

Validation Checks

Range Check