Excel

We’ll start easy. All formulas in Excel start with = . Find the total January revenue by typing =B2+B3

=B2+B3

A better way to add numbers is to use the SUM function. Find total January revenue by typing =SUM(B2, B3) Notice how the function’s arguments are separated by commas.

=SUM(B2,B3)

You can pass a group of cells to a function by using a colon. B2:B6 tells Excel “every cell from B2 to B6”. Find January Revenue by typing =SUM(B2:B6)

=SUM(B2:B6)

Now use the SUM function to find total revenue for February.

=SUM(C2:C6)

The CFO tells you that February revenue was misreported and the total needs to be increased by $100. Type =SUM(C2:C6, 100)

=SUM(C2:C6, 100)

Use the SUM function with the argument B7:G7 to find the total revenue from January to June

=SUM(B7:G7)

Use the SUM function to find the total revenue of Product A, Product C, and Consulting, all for JANUARY ONLY. Hint: The arguments look like this (?, ?, ?)

=SUM(B2,B4,B6)

The COUNT function counts all the cells that contain numbers in a specified range. Try typing =COUNT(A2:E2) to get a return value of 4

=COUNT(A2:E2)

COUNT only counts the cells that contain numbers. What would =COUNT(A1:A5) return?

Write a COUNT function that counts how many students have at least one pet.

=COUNT(B2:B8)

The following table contains Q1 sales. Write a COUNT function to count how many sales people made at least one sale in January.

=COUNT(B2:B5)

Write a COUNT function that would count the number of months that Chuck made at least one sale in Q1 (January through March)

=COUNT(B4:D4)

In Excel, logical functions and decision-making are built with statements that evaluate to true or false. There are 6 logical operators to determine true or false, and they are listed in the table below. The statement 1=1 is true, because 1 is equal to 1. Would 1=2 evaluate to True or False?

False

The statement 1<>1 evaluates to false, because <> means “not equal to,” and it would be false to say “1 is not equal to 1.” What would 1<>2 evaluate to?

True

The statement 4>=1 evaluates to true, because >= means “greater than or equal to,” and 4 is greater than or equal to 1. What would 1<=2 evaluate to?

True

What would 1<=1 evaluate to?

True

What would B5=C5 evaluate to?

B5 = 950, C5 = 950

True

What would B2>B3 evaluate to?

B2 = 1000, B3 = 1200

False

What would B2>=C6 evaluate to?

B2 = 1000, C6= 1000

True

VLOOKUP, or Vertical Lookup, finds a value in a vertical list and returns the corresponding value in an adjacent vertical list. Get the year of hurricane Ike by typing this formula into the field below: =VLOOKUP(“Ike”, A1:B4, 2, 0)

=VLOOKUP("Ike", A1:B4, 2, 0)

The first argument (values separated by commas) is the match value (what VLOOKUP looks for first). The second argument is the search area. The third argument is how many columns to the right the lookup will search (including the current column). Find the year of hurricane Sandy with: =VLOOKUP(“Sandy”, A1:D4, 4, 0)

=VLOOKUP("Sandy", A1:D4, 4, 0)

The final argument, 0, just means that you’re looking for an exact match, rather than an approximate match. Don’t worry about this for now; it’ll be 0 in most cases. Find the MAKE of the Impala using a VLOOKUP formula

=VLOOKUP("Impala", A1:B3, 2, 0)

Find the YEAR of the RAV4 using vlookup

=VLOOKUP("RAV4", A1:C3, 3, 0)

Use VLOOKUP to find the name of the tenant in unit number 254. Use A:B as your second argument to include all of columns A and B in your search range.

=VLOOKUP(254, A:B, 2, 0)

Using the A:B range, use VLOOKUP to find the name of the student who scored 95 on the test

=VLOOKUP(95, A:B, 2, 0)

Using the B:C range, use VLOOKUP to find Patrick’s major

=VLOOKUP("Patrick", B:C, 2, 0)

Use LEN( ) to get the number of characters in text. Type =LEN(A2) in the field below to get the length of the text in cell A2

=LEN(A2)

Use LEFT( ) to get the left-most characters of some text. Type =LEFT(A2, 4) to return the 4 left-most characters in the text, which is “Mike”

=LEFT(A2, 4)

RIGHT( ) returns the right-most characters of some text. Try using =RIGHT( ) to return “Johnson”

=RIGHT(A2,7)

What would LEN(A2) return? (Don’t forget the space!)

Excel keyboard shortcuts make you faster and more efficient. Press and release the “Alt” key on your keyboard.

Alt

Now access tools in the “Home” tab by pressing and releasing Alt, then H (the letters guide you!)

Alt H

Access the Insert tab with Alt, N

Alt N

Access the Page Layout tab with Alt, P

Alt T

Access the Formulas tab using the keyboard

Alt M

Access the Data tab using the keyboard

Alt A

Access the View tab using the keyboard

Alt W

Access the Home tab using the keyboard

Alt H

Let’s try some keyboard shortcuts to quickly select cells. Don’t use the mouse! Hold Shift and press ➡ to select multiple cells

Shift -->

Hold Control and press ➡ to jump to non-empty cell

Control --->

Holding Control and pressing ➡ will also jump to the last non-empty cell

Control --->

Hold Control and Shift, then press ➡ to select all the non-empty cells

Control + Shift  + --->

Select the first two cells using the keyboard

shift + flecha abajo

Select all the non-empty cells using the keyboard

shift + control + flecha abajo

Jump to the text using the keyboard

Control + flecha abajo

Select all non-empty cells using the keyboard

control + shift + flecha abajo

Join two or more strings of text together with the CONCAT function. Type =CONCAT(A1, B1) to return “ABCD”.

=CONCAT(A1,B1)

The concat function can have as many arguments as you’d like. Use concat to get the person’s full name. (Hint: don’t forget the spaces - your function should have five arguments!)

=CONCAT(B1," ",B2," ",B3)

You can also use the ampersand (&) to concatenate strings. Type =A1&”, “&A2 to return “Minneapolis, Minnesota”.

=A1&", "&A2

Use the concat function to return “JSmith@example.com”

=CONCAT(A3, "@example.com")

Use & to return a string that says “Hello world”.

=A1&" "&A2

Use the concat function to return a string that says “Hello, Eric” (don’t forget the comma and space!)

=CONCAT(A1, "  , " ,C3)

Use & to return “ABCDEFG”

=C3&D4&A1

Use & to combine the pieces to form www.somedomain.com.

=A1&A2&A3

Use & to return the string “Biscuits & gravy”.

=A1&" & "&A2

Use the concat function to return the phone number 651-555-5816.

=CONCAT(A1,A4,A2,A4,A3)

The DATE function in Excel takes three number arguments (year, month, and day) and combines them to return a date. You can remember the order of the arguments because they go from largest unit to smallest. For example, to return the date February 5th, 2012 type the following formula:

=DATE(2012, 2, 5)

=DATE(2012, 2, 5)

Now use the date function to return January 15th, 2012.

=DATE(2012, 1, 15)

Use the numbers in the cells below to return the date.

=DATE(B3, B2, B1)

Write a DATE formula that returns January 1st, 2001.

=DATE(2001, 1, 1)

The MATCH function searches a range for a lookup value and returns its position in the range. Try typing =MATCH(15, A1:A6, 0) to return 5.

=MATCH(15, A1:A6, 0)

What would =MATCH(“Banana”, A1:A5, 0) return?

When the third argument is 0, MATCH looks for an exact match. When it is 1 and the values are sorted in ascending order, MATCH looks for the position of a largest value less than or equal to the match value. what does =MATCH(120, A1:A5, 1) return?

When the third argument is -1 and the values are sorted in descending order, MATCH looks for the smallest value that is greater than or equal to the match value. What would =MATCH(405, A1:A5, -1) return?

MATCH also supports wildcard matching, where the * symbol can substitute for any text. What would =MATCH(“*stin”, A:A, 0) return?

Write a MATCH function that would return 5.

=MATCH(1, A:A, 0)

What would =MATCH(“M*”, A:A, 0) return?

What would =MATCH(10, B1:B5, 1) return?

The AVERAGE functions returns the average of a group of cells. To get the average number of electoral votes among the top 6 states, type =AVERAGE(B2:B7)

=AVERAGE(B2:B7)

Use AVERAGE to find the average number of DEMOCRATIC Congressional Seats for the top 6 states

=AVERAGE(C2:C7)

Use AVERAGE to find the average number of REPUBLICAN Congressional Seats for the top 6 states

=AVERAGE(D2:D7)

Use AVERAGE to find the average number of electoral votes for the top 4 states

=AVERAGE(B2:B5)

Use AVERAGE to find the average number of Republican congressional seats for the top 3 states

=AVERAGE(D2:D4)

What would =AVERAGE(D2) return?

What would =AVERAGE(1, 1, 1, 1, 1, 1, 1) return?

What would =AVERAGE(50, 100, 150) return?

Use AVERAGE to find the average number of electoral votes for Texas, Florida, and Pennsylvania

=AVERAGE(B3,B5,B7)

In Excel, the entire Excel file is called a workbook, and each of the tabs along the bottom represents a worksheet. In the example below, the active worksheet (the one you’re currently viewing) is Sheet4. When you’re writing a formula on Sheet4, you can reference cells from other worksheets in your formula. Press Enter to continue.

Enter

To reference a cell in another sheet, type the sheet name followed by an exclamation point, then the cell letter and number. In the space below, type =Sheet1!A5 to reference cell A5 in worksheet Sheet1.

=Sheet1!A5

Write a reference to cell B2 in the second sheet.

=Sheet2!B2

Write a reference to the entire A column in the second worksheet.

=Data!A:A

Use the SUM function to add cell A1 from sheet 1, and cell A1 from sheet 2.

=SUM(Sheet1!A1, Sheet2!A1)

Use the MIN function to find the minimum value in column G in sheet 2.

=MIN(Sheet2!G:G)

Use the UPPER function to convert the contents of cell C5 in sheet 3 to uppercase.

=UPPER(Sheet3!C5)

Write a reference for the entire row 4 of the Data sheet.

=Data!4:4

Use the SUM function to find the sum of all values in column B of the data tab.

=SUM(Data!B:B)

Use the MAX function to find the maximum value in column C in sheet 3.

=MAX(Sheet3!C:C)

W3schools

Complete the formula:

1+1

=1+1

Type the value that the formula returns: =2*4

Type the value that the formula returns: =20/2

Type the cell reference for Column A, Row 1:

A1

Type the cell reference for Row 5, Column B:

B5

Type the range Column A, Row 1 : Column E, Row 10:

A1:E10

Type the range Column A, Row 2 : Column B, Row 5:

A2:B5

Complete the sequence of numbers:

A1(1)
A2(2)
A3(3)

Complete the sequence of numbers:

A1(2)
A2(4)
A3(6)

Complete the sequence of dates:

A1(07.20.2021)
A2(07.21.2021)
A3(07.22.2021)

Complete the sequence of dates:

A1(07.20.2021)
A2(07.21.2021)
A3(07.22.2021)

Complete the sequence of text and numbers:

A1(Hello1)
A2(Hello2)
A3(Hello3)

Type the cell in the bottom right corner of the range B2:C5:

C5

Type the cell in the top left corner of the range B2:C5

B2

Complete the name of the command for adding columns:

insert Columns

Complete the name of the command for removing columns:

delete Columns

Complete the name of the command for adding rows:

insert rows

Complete the keyboard shortcut for the Undo command (for Windows and MAC):

CTRL + z

Command + z

Complete the keyboard shortcut for the Redo command (for Windows and MAC):

CTRL + y

Command + y

Complete the formula:

=8+10

Type the value that the formula returns: =2*15

Type the value that the formula returns: =6+15+30

Complete the SUM function:

=SUM(E2:E4)

Type the value that the SUM function returns: =SUM(25;15;5)

Type reference B2 as relative:

B2

Type reference A1 as absolute:

$A$1

Type the symbol that Excel uses for addition:

Complete the formula using addition:

=5+50

Complete the formula using subtraction:

=5-50

Complete the formula using multiplication:

=5*50

Complete the formula using division:

=25/5

Type the value that the formula returns: =10+10*2

Complete the formula:

=(10+10)*2

Type the value that the formula returns: =(10+10)*2

Type the value that the formula returns: =(2(2+2)+(4+4)2):