Functions

Question 1

SEQUENCE

Answer 1

Allows you to generate a list of sequential numbers

=sequence(

Question 2

Auto fit data

Answer 2

ALT H O I

ALT H O A

Question 3

XLOOKUP

Answer 3

Find order ID & corresponding values

=XLOOKUP(Lookup_value, lookup_array, return array)

Question 4

Combine cells

Answer 4

= [cell] & “ “ & [cell]

Question 5

Unhide all cells

Rows & columns

Answer 5

= CTRL SHIFT 9
-highlight entire sheet then double click between a row or column

OR

=CTRL SHIFT 9 (ROWS)
=APT O C U (COLUMNS)

Question 6

Hide row & column

Answer 6

CTRL 9

CTRL 0

Question 7

Auto sum

Answer 7

ALT =

Question 8

Delete all blank rolls

Answer 8

CTRL G [BLANKS]

CTRL -

Question 9

Insert row

Answer 9

CTRL SHIFT +

Question 10

Select row

Answer 10

SHIFT SPACEBAR

Question 11

Delete row

Answer 11

CTRL -

Question 12

Absolute reference

Answer 12

To fixate a cell location for reference

F4 [cell]

Question 13

Create drop down menu

Answer 13

CTRL A V V

Question 14

Separate text into columns

Answer 14

ALT A E

Question 15

CALCULATE DAYS UNTIL DUE

Answer 15

=DAYS(CELL, TODAY())& “ DAYS”

OR

=DAYS(“DATE”,”DATEL)

*MUST USE QUOTATIONS AROUND DATE

Question 16

Convert number to percentage

Answer 16

CTRL 1, CUSTOM, 0\%

Question 17

Remove duplicates

Answer 17

ALT A Q, check unique records only
A (data)
Q (advanced filter)

OR

ALT A M

Question 18

Create a weekday only series

Answer 18

ALT H FI S

Home, filter, series

Question 19

Automatically highlight duplicates

Answer 19

Highlight column or row you want to use it on 
Right click sheet 
View code 
Paste VBA code 
F5 to apply code

Question 20

Insert a row after every row

Answer 20

• number the rows & copy the row numbers and paste it below

- select the first number, ALT A SA

Question 21

Count cells within a range that meets a criteria

Answer 21

=COUNTIF(range,criteria
Range is the range of cells to count if criteria is met
Criteria that must be true in order for th cells to be counted.

Question 22

Shortcut for current date a& time

Answer 22

CTRL :

CTRL SHIFT :

Question 23

TEXTJOIN FUNCTION

Answer 23

Combine cells together with any delimiter using this function.

-TEXTJOIN(“;”,,[cells]

Question 24

Replace any string of text with the SUBSTITUTE function

Answer 24

=SUBSTITUTE([cell],”old text “, “New text”)

Question 25

Filtering data

Answer 25

=FILTER(array, include
Array = data you want to filter
Include = the criteria to filter data on

Ex: =FILTER(B5:B37, C5:C37=E4)

Question 26

Move columns

Answer 26

HOLD SHIFT, HOVER ON COLUMN TIL YOU SEE THE 4 DIRECTION POINTER, THEN DRAG COLUMN TO DESIRE LOCATION.

Question 27

Case function

Answer 27

=UPPER(
=LOWER(
=PROPER(

Question 28

REMOVE GIRDLINES

Answer 28

ALT W VG

Question 29

REPLACE CHARACTER

Answer 29

=REPLACE(

Replace characters within a text string based on location

Question 30

COUNT FUNCTION

Answer 30

Count cells within a range that meets a criteria

=COUNTIF(range, criteria

Question 31

INSERT ROWS AFTER EVERY ROW

Answer 31

1. Number the rows & copy the row #s and paste it below.

2. Select the first number, ALT A SA

Question 32

Convert lists into data tables

Answer 32

=TEXTSPLIT(text, col_delimiter, row_delimiter)

Question 33

Add or remove filter

Answer 33

CTRL SHIFT L

Question 34

Create table shortcut

Answer 34

CTRL T