I Didnt Know Excel Could Do That Matt Farrow @_MattFarrow What I - - PowerPoint PPT Presentation

▶
i didn t know excel could do that
SMART_READER_LITE
LIVE PREVIEW

I Didnt Know Excel Could Do That Matt Farrow @_MattFarrow What I - - PowerPoint PPT Presentation

Apr 08, 2023 224 likes •671 views

I Didnt Know Excel Could Do That Matt Farrow @_MattFarrow What I feel qualified to teach. Considerations before you start in Excel Dont Try to Copy Everything navigation manipulation visualization navigation window management

slide-1
SLIDE 1

I Didn’t Know Excel Could Do That

Matt Farrow @_MattFarrow

slide-2
SLIDE 2

What I feel qualified to teach.

slide-3
SLIDE 3
slide-4
SLIDE 4

Considerations before you start in Excel

slide-5
SLIDE 5

Don’t Try to Copy Everything

slide-6
SLIDE 6

navigation manipulation visualization

slide-7
SLIDE 7

navigation

slide-8
SLIDE 8

window management

slide-9
SLIDE 9

window management

slide-10
SLIDE 10

window management

slide-11
SLIDE 11

filters

slide-12
SLIDE 12

filters

slide-13
SLIDE 13

Filters

slide-14
SLIDE 14

filters

slide-15
SLIDE 15

Keyboard Shortcuts & Tricks

Jump to the next break (or end)

  • f a row or column.

Ctrl + Arrow

slide-16
SLIDE 16

Ctrl + Shift + Arrow

Select to the next break (or end)

  • f a row or column.

keyboard shortcuts & tricks

slide-17
SLIDE 17

F2

Edit in Place

keyboard shortcuts & tricks

slide-18
SLIDE 18

Manipulation

slide-19
SLIDE 19

formulas

“A formula performs calculations or other actions on the data in your worksheet.”

Microsoft Support

slide-20
SLIDE 20

vlookup

=VLOOKUP(Value to look up, Range to look for value, Column for the return value, Type of match) =VLOOKUP(A1,Lookup!A1:B7,2,TRUE)

slide-21
SLIDE 21

absolute/relative references

Relative references can move. A1 Absolute references don’t move. $A$1

slide-22
SLIDE 22

absolute/relative references

$A$2 The column and the row do not change when copied. A$2 The row does not change when copied. $A2 The column does not change when copied.

slide-23
SLIDE 23

countif

=COUNTIF(Where are we looking?, What are we looking for?) =COUNTIF(A:A, “Washington”)

slide-24
SLIDE 24

concatenate

=CONCATENATE(1st Cell, Separator, 2nd Cell,…) =CONCATENATE(A1,“ ”,B2,“ ”,C2)

slide-25
SLIDE 25

date difference

=DATEDIF(Start date, End date, Difference calculation) =DATEDIF(A1,A2,“d”)

Today’s date: today() Difference in days: “d” Difference in weeks: …“d”)/7 Difference in months: “m” Difference in years: “y”

slide-26
SLIDE 26

if

=IF(logical test,[value if true],[value if false]) =IF(A1=B1,“Match”,“No Match”)

slide-27
SLIDE 27
  • ther tricks

Extract date element =YEAR(A1) Generate random number between 0 and 1 =RAND()

slide-28
SLIDE 28

macros

“A macro is an action or a set of actions that you can run as many times as you want.”

Microsoft Support

slide-29
SLIDE 29

macros

slide-30
SLIDE 30

pivot tables

“PivotTables are a great way to summarize, analyze, explore, and present your data, and you can create them with just a few clicks.”

Microsoft Support

slide-31
SLIDE 31

pivot tables

slide-32
SLIDE 32

pivot tables

What information do we include? Or not? How are we filtering the data?

slide-33
SLIDE 33

slicers

slide-34
SLIDE 34

visualization

slide-35
SLIDE 35

pivot charts

slide-36
SLIDE 36

mapping

slide-37
SLIDE 37

Power Map

Requires Office 365 ProPlus

slide-38
SLIDE 38

be aware…

“Power Map uses Bing to geocode your data based on its geographic properties.”

Microsoft Office Support

slide-39
SLIDE 39

sparklines

slide-40
SLIDE 40
  • ther resources
slide-41
SLIDE 41
  • ther resources
slide-42
SLIDE 42
  • ther resources
slide-43
SLIDE 43

Questions?

Matt Farrow

@_MattFarrow