Excel Toolkit 2

Practice using worksheet tools.

 

Learning Objectives

Understand how to Google Search shortcuts.

Understand how to create a formula to display pictures.

Understand how to use formula checking tools.

Understand Paste Special options.

Understand how to use Scenario Manager.

Understand how to use the Solver tool.

 

The DATEDIF Function

This function is used to show the number of days, months, or years between two dates.  Its three arguments are an earlier date, a more recent date, and the value you want displayed. This can be either the days, months, or years.

Its structure looks like this. =DATEDIF(earlier date, more recent date, type of measurement)

If Cell A1 = 3/6/2008 and Cell A2 = 5/6/2016, then =DATEDIF (A1, A2, "D") gives us the number of days. =DATEDIF (A1, A2, "M") gives us the number of months and =DATEDIF (A1, A2, "Y") gives us the number of years. The D, M and Y argument value must be enclosed within quotation marks. You can’t use “W” for weeks. The function doesn’t work for dates before Jan1, 1900

Watch this video on DATEDIF

 

Convert Cells to all Caps using a Function   1:28

 

To fill in blank cells in a column.   Time: 3:16

https://www.youtube.com/watch?v=9TDcVOKbm34&list=PLjQrfrgBLzJOoKa3OYwmNGiAu0OCA8Odu&index=12  

To enter a formula simultaneously into multiple cells you can select a range, then type a formula, and then press [Ctrl + Enter]. This enters your formula into every cell in the range, replacing any content that was in the range. But what if your range is a mixture of empty cells and cells that contain information you want to keep? Excel has a shortcut to enter the formula into just the blank cells, while retaining the content in the other cells. 

 

Task: Open a new worksheet and copy the data in this table into your worksheet.

 

 

A

B

C

1

Company

First

Last

2

ABC Telecom

Grace

Edwards

3

 

Vanessa

Fox

4

 

Vanessa

Gonzalez

5

 

Zachary

Hunter

6

Acme Corp

Taylor

Rose

7

 

Mackenzie

Cruz

8

Ajax

Lillian

Mason

9

 

Katherine

Hudson

10

 

Destiny

Berry

11

 

David

Anderson

12

 

Brody

Ruiz

13

Allied Biscuit

Nevaeh

Kelly

14

 

Arianna

Marshall

15

 

Victoria

Ortiz

16

 

Eric

Carroll

17

 

Mackenzie

King

 

Mouse over the range A1:A17 to select it.

Press the F5 function Key. (Note: for some laptops you must press both the Fn key and the F5 key.)

Type [ALT+S] to select the Special options.

Type [ALT+K] to select Blank Cells. Click OK.

Select the first blank cell in the column: cell A3. Type in the formula =A2.

Press [CTRL+ Enter].  The formula will be copied into all of the blank cells in the column. The cell references will be automatically adjusted so each formula selects the contents of the cell directly above it.

 

Next we want to convert the formulas into text (labels)

Mouse over the range A1:A17 to select it.

Press [CTRL+C] to copy.

Right click. Select Paste Special. Choose the Values option. Click Ok and then press Enter. The formulas will be replaced with the text values (labels).

 

 

Displaying Pictures

 

Inserting pictures using Index and Match Functions   Time = 4:54

https://www.youtube.com/watch?v=CEM0pp5SjMU

 

Display Image Based on an Excel Formula   2:56

Convert Cells to all Caps using a Function   1:28

 

Task: Create a worksheet that displays pictures of each member of your team.

Use Data Validation to show a message telling the user how to select a picture to display.

 

Formula Checking Tools

 

Error Tracking/Tracing      Time:   1:45

https://www.youtube.com/watch?v=wWOBdd9rTew

 

Excel Formula Auditing     Time:   3:58

https://www.youtube.com/watch?v=CpXWslv-e8c

Task: Download and open http://bus91l.altervista.org/Data/96XData.xlsx

Select the Tables worksheet. Click on a cell (try D64) containing a formula.

Trace the precedents [ALT  M  P], then trace the dependents [ALT M  D], and then remove the arrows [ALT M  A].

There is another set of keyboard shortcuts for tracing precedents, CTRL + [ and dependents Ctrl + ]. Try using them. Remove the arrows.

 

Formula Evaluation Tool    Time: 1:40

https://www.youtube.com/watch?v=ZmmOa9Z2-Sc

Task: Download and open http://bus91l.altervista.org/Data/96XData.xlsx

Select the Tables worksheet. Click on a cell (try D66) containing a formula.

Use the keyboard shortcut [ALT  M  V] to open the Formula Evaluation dialog box. You will see the formula =D63+D64+D65. Select the Evaluate option.

The formula changes. The first cell reference is replaced with a number.

Keep selecting Evaluate until the formula display area shows a single number. 

Click Close to exit the dialog box. Close the file and do NOT save the changes.

 

Google Search

Google Search Techniques for Excel         Time = 2:51         https://www.youtube.com/watch?v=-VDaF-7YCCU  

Task: Watch the video and try the Related, Site, and Similar search tools.

 

Paste Special

 

Using Paste Special    Time = 1:52

Shows how to use the Transpose and Divide options.

https://www.youtube.com/watch?v=ZgZyesnyOm4

 

Using Paste Special    Time = 2:57

Shows how to use the Links options. Start at 2:20 in the video.

https://www.youtube.com/watch?v=_ODK4XW-aNs

 

Scenario Manager

 

What-If Analysis using Scenario Manager    Time = 1:22

This video is for information only. There is no task to complete.

https://www.youtube.com/watch?v=4-WT2u-EBx4

 

The Scenario Manager can be useful if you want to create a record of the contents of a form, like a monthly sales report, each time it changes. You can then generate a summary report showing the various reports side by side. 

 

Solver

 

Solver     Time = 2:20

https://www.youtube.com/watch?v=ytSOya1aax0

Task: Watch the video and reproduce what he did.

 

Review – Fine Points

 

 

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for the “No” answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name ___________________      Date___________       Section _______

Check for Understanding – Excel Toolkit 2

Y/N I understand how to create a formula to display pictures.

Y/N I understand how to use the formula checking tools.

Y/N I understand how to Google Search shortcuts.

Y/N I understand how to use Paste Special options.

Y/N I understand how to use the Scenario Manager.

Y/N I understand how to use the Solver tool.