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

I Didn’t Know Excel Could Do That Matt Farrow @_MattFarrow

What I feel qualified to teach.

Considerations before you start in Excel

Don’t Try to Copy Everything

navigation manipulation visualization

navigation

window management

window management

window management

filters

filters

Filters

filters

Keyboard Shortcuts & Tricks Ctrl + Arrow Jump to the next break (or end) of a row or column.

keyboard shortcuts & tricks Ctrl + Shift + Arrow Select to the next break (or end) of a row or column.

keyboard shortcuts & tricks F2 Edit in Place

Manipulation

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

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)

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

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

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

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

date difference =DATEDIF(Start date, Today’s date: today() End date, Difference in days: “d” Difference Difference in weeks: …“d”)/7 calculation) Difference in months: “m” Difference in years: “y” =DATEDIF(A1,A2,“d”)

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

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

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

macros

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

pivot tables

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

slicers

visualization

pivot charts

mapping

Power Map Requires Office 365 ProPlus

be aware… “Power Map uses Bing to geocode your data based on its geographic properties.” Microsoft Office Support

sparklines

other resources

other resources

other resources

Questions? Matt Farrow @_MattFarrow