Beginner

Question 1

Sorting data in Excel allows you to organise your data for more effective visualisation, understanding and analysis. Some examples of the uses of this function are if you want to organise names in alphabetical order, to organise prices from highest to lowest, or rows by colours or icons.

Answer 1

Go on Home tab, press Sort.

Question 2

The running total, also called a running count, allows you to watch the number of items in a table add up as you continue to enter new items.

Answer 2

=SUM($C$2:C2)

Question 3

Formula that finds an average of a set of data

Answer 3

=AVERAGE(range)

Question 4

Formula that calculates the arithmetic mean of a set of values, only of values that meet certain criteria.

Answer 4

=AVERAGEIF(range, criteria, [average_range])

Question 5

Formula that is used to add up the values in a range that meet the criteria specified.
equals to a number
greater than a number
less than a number
greater or equal than a number
not equal to a number
not similar to a name
starts with letter
word is x digits long

Answer 5

=SUMIF(range, criteria, [sum_range])
=SUMIF(range, name, [sum_range])
=SUMIF(range, number, [sum_range])
=SUMIF(range, ">number", [sum_range])
=SUMIF(range, "=number", [sum_range])
=SUMIF(range, "<>number", [sum_range])
=SUMIF(range, "<>name", [sum_range])
=SUMIF(range, "letter*", [sum_range])
=SUMIF(range, "letter?????", [sum_range])

Question 6

Formula that allows you to make logical comparisons between an actual value in a worksheet, and what you expect.

Answer 6

=IF(Something is TRUE, then do something, if not do something else)

Question 7

Formula that helps to you find specific data in a table or in a specific range by row of data.

Answer 7

=VLOOKUP(value, table, col_index, [range_lookup])

Question 8

Formula that searches for a value in the first row of a table. At the match column, it retrieves a value from the specified row.

Answer 8

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Question 9

Formula that automatically counts the number of cells that meet the criteria you have specified.

Answer 9

=COUNTIF(range, criteria)

Question 10

Formula that counts the number of empty cells in a specified range.

Answer 10

=COUNTBLANK(range)

Question 11

Formula that counts the number of cells that are not empty in a given range.

Answer 11

=COUNTA(value 1,value 2)

Question 12

Formula that counts the number of cells that contain numbers within the list of arguments.

Answer 12

=COUNT(range)

Question 13

Formula that joins two or more text strings into one string.

Answer 13

=CONCATENATE(text1, [text2]…)

Question 14

Formula to switch or rotate cells

Answer 14

=TRANSPOSE(range of cells to be transposed)

or

You can do this by copying, and then using the Transpose option in Paste Special, but be aware that this will create duplicated/static data i.e. any later changes to data in your original columns will not be pulled through to the transposed data.

Question 15

Formula when you need to take three values presented separately and combine them to form a date.

Answer 15

=DATE(year,month,day)

Question 16

Function allows you to express the months of the year (January to December) as integers (whole numbers) that correspond to the number of the month in the year.

Answer 16

=MONTH(serial_number)

Question 17

a way for you to analyse your data quickly and to help you make better decisions.

Answer 17

click Insert and then on Pivot Tables or Recommended Pivot Tables. A dialogue box will open with different options regarding how to present the analysed data. Then click OK.

Question 18

a quick and effective way to highlight important information in a spreadsheet as you continue to update it.

Answer 18

you highlight the cells that will be used as reference points. Then click Home, then Conditional Formatting, then New Rule