Excel Toolkit 2

Practice using worksheet tools.

 

Learning Objectives

Understand how to use the Camera tool.

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 Camera Tool

Camera-tool-function

Links charts (or ranges) so when you change the original data (or the format!) the linked charts or ranges update automatically. It works for multiple copies of the same chart or range. Great tool if you have data in many different worksheets.

 

Quote from the web page: You can do the same via ‘copy’ and then paste using ‘Linked Picture’ in ‘other paste options’.

 

 I tried to do this.  Linked Picture is not available in Excel 2010.  Paste Special has a link feature, but it only links the data, not the formatting. – Mike

 

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 use the Camera tool.

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.