1 of 27

Pre session: What I already know

bit.ly/1129SHEET

2 of 27

What did we do last time?

A quick game of memory

1

3 of 27

Cyborgs vs Humans

  • Open tab that says “Cyborgs vs Humans”
  • When you hear the word “Go” count the number of cells in the specific column
  • Yell out “Humans” when you are ready
  • Stop if you hear someone yell “Cyborgs”

3

4 of 27

Cyborgs vs Humans

  • Should we be nervous of a cyborg invasion?
  • What can we learn from this activity?
  • What were the “cyborgs” using?

4

5 of 27

What we are going to achieve

5

1.0

Recap

Simple functions: Sum(), Average(), Filter & Sorting

Simple Formulas

Count() & Counta()

2.0

Charting

How to make a bar graph

3.0

Formatting

Wrap text, conditional formatting, and drop down menu

4.0

6 of 27

6

7 of 27

We’ve been hacked!

A whodunit story

7

8 of 27

Count() & Counta()

1

Click an open cell, and in that cell or in the formula bar on top, type an =, followed by the name of the function followed by an (

2

Next select (click and drag a box over) the values that you wish to count

3

Next type ) to close the formula and finally hit Enter

8

9 of 27

Count() & Counta()

4

The cell you entered the formula should now contain the count of cells in the selected range that contain numbers (count) or that aren’t empty (counta)

5

Suggested: Click on the cell next to the formula and label it

6

Next type ) to close the formula and finally hit Enter

9

10 of 27

Count() & Counta()

1

Click an open cell, and in that cell or in the formula bar on top, type an =, followed by the name of the function followed by an (

2

Next select (click and drag a box over) the values that you wish to count

3

Next type ) to close the formula and finally hit Enter

10

11 of 27

Count() & Counta()

4

The cell you entered the formula should now contain the count of cells in the selected range that contain numbers (count) or that aren’t empty (counta)

5

Suggested: Click on the cell next to the formula and label it

6

Next type ) to close the formula and finally hit Enter

11

12 of 27

Dear heavens, what is missing?!

What did we learn?

12

13 of 27

Charting

  1. Select the range of cells which you wish to include in the graph
  2. In the insert tab, identify and click the chart option
  3. Confirm a chart and Chart Editor window on right side of screen
  4. At the top of the Data tab in the Chart Editor, first check chart type
  5. Still using the Data Tab in the Chart Editor, check the data range(s) for your chart - the range(s) of cells that hold the data you want to display in the chart

13

14 of 27

Charting

  • To see the Chart Editor
    1. To get the Chart Editor to reappear double click on the chart
  • Customize Chart
    • Using the Customize Tab in the Chart Editor, you can make formatting changes such as adding titles, adding labels, choosing colors, etc

14

15 of 27

Charting

  • Select the range of cells which you wish to include in the graph
  • In the insert tab, identify and click the chart option
  • Confirm a chart and Chart Editor window on right side of screen
  • At the top of the Data tab in the Chart Editor, first check chart type
  • Still using the Data Tab in the Chart Editor, check the data range(s) for your chart - the range(s) of cells that hold the data you want to display in the chart

15

16 of 27

Charting

  • To see the Chart Editor
    • To get the Chart Editor to reappear double click on the chart
  • Customize Chart
    • Using the Customize Tab in the Chart Editor, you can make formatting changes such as adding titles, adding labels, choosing colors, etc

16

17 of 27

We’re getting closer. I can feel it.

17

18 of 27

Wrap text

  1. Select the cells in which you want text to wrap
  2. In the Format Tab, identify and click the Text Wrapping option
  3. Among the Text wrapping options, select Wrap
  4. You should now see all of the text in the cell broken into different lines as necessary

18

19 of 27

Conditional Formatting

  • Select the cells which you want to apply conditional formatting to
  • In the Format tab, identify and click the conditional formatting option
  • Notice that the conditional format rules window opens on the right of your screen
  • Select the rule that best fits your criteria of formatting. Select action that results in criteria formatting. Eg. To highlight scores above a given benchmark green you can select the greater than option in the format cells if...dropdown and select the green highlight option in the formatting style dropdown

19

20 of 27

Drop Down Menu

  • Select the cell which you want to put a dropdown in
  • In the data tab, identify and click data validation option
  • In the data validation window that pops up select the list of items option in the criteria dropdown to type a comma separated list.
  • Or if you have all the options you want in the dropdown in a range of cells select the list from a range option and specify the range of cells that you want as your dropdown values

20

21 of 27

Who did it?

21

22 of 27

22

23 of 27

Customer

Customer Service Rep

when finished, switch roles!

1. Select one challenge to the customer service rep that is on your paper

2. Determine what the challenge requires and then problem solve the customer’s wishes

23

24 of 27

Debrief

What we learned?

  • How can we apply what we learned today to our daily work?

What do I want to know?

  • What would you like to know more about to help in your daily work?

24

25 of 27

What I know now

bit.ly/1129SHEETS

25

26 of 27

Sandbox? More like funbox...

26

27 of 27

Now what?

Sandbox

  1. Explore, try and learn
  2. Try to complete all the tasks
  3. Try to figure out the uncovered material

Now is your chance!

  1. Do you have a question we didn’t cover?
  2. Now is your chance!
  3. Come find us and let’s try to solve it together!

27