EXCEL 2016 TIPS AND TRICKS - California State University ... ?· EXCEL 2016 TIPS AND TRICKS Tammara…

  • Published on
    30-Jun-2018

  • View
    212

  • Download
    0

Transcript

  • EXCEL 2016

    TIPS AND TRICKSTammara Sherman, Ed. D.Campus TrainerCSU Bakersfield

  • ABOUT ME

    2

    40+ years in IT

    AS in Liberal Studies, BS in Business Administration / MIS, MBA, Ed. D.

    in Educational Leadership

    CSUB Campus Trainer

    7 years with CSUB

    2 Girls, 4 grandchildren

    2 Cats

    KCGKerry Consulting Group

  • SESSION OUTLINE

    Managing Workbooks

    Working with Data

    Summarizing Data

    Visualizing Data

    Printing Data

    Getting Help

    3

  • ABOUT THE DATA

    CSU Finance Data Warehouse

    Transaction Inquiry Dashboard > Actuals Report

    Report Filters:

    Excluded 603 Benefits

    Account Types: 50 & 60

    2 Funds and 1 Department

    Ran twice: FY 2015 & FY 2016

    Made minor changes to remove personal information

    4

    ACTUALS TRANSACTIONS

  • WORKBOOK

    MANAGEMENT

    5

  • WORKBOOK MANAGEMENT

    Tab management

    Rename

    Move

    Color Code

    Copy

    Copy to another workbook

    Hide/Unhide

    Making Tabs

    work for you

    6

  • WORKSHEET MANAGEMENT

    View > Show Group

    Formula Bar

    Gridlines

    Headings

    Freeze Panes

    Show/Hide Columns

    (Home > Format > Visibility)

    Home > Format > Auto Fit Row Height or Width

    7

  • WORKING WITH

    TEXT

    Formatting, Filtering, Sorting

    Working with text doesnt have to be challenging

    8

  • WORKING WITH TEXT

    9

  • CREATING TEXT

    FORMULA

    =LEFT(I2,1)

    I2 represents the cell containing the text

    1 represents the number of characters to return

    Shows the number of characters specified for a cell

    FUNCTION DESCRIPTION

    Left(I2,1) Returns the first letter

    Left(I2,2) Returns the first 2 letters

    Left(I2,1)&0 Create Account Type

    Left(K2,3) Create Account Category10

  • FILTERING TEXT

    Filters

    Filters > Text Filters > Contains (i.e. Salaries)

    11

  • SORTING TEXT Sorts by column A or specified order

    Sorts by

    selected

    column

    12

  • WORKING WITH

    DATES

    Formatting, Filtering, Sorting

    13

  • FORMATTING DATES

    Long Date

    Tuesday, July 19, 2016

    Short Date

    7/19/2016

    14

  • FORMATTING DATES

    Custom Dates

    mm/dd/yy

    7/19/16

    15

  • SPLITTING DATES

    FUNCTION DESCRIPTION BEFORE AFTER

    YEAR(cell) Gives the year 5/30/2017 2017

    MONTH(cell) Gives the month 5/30/2017 5

    DAY(cell) Gives the day 5/30/2017 30

    NOW() Gives todays date 5/31/2017

    YEAR(NOW()) Current year 2017

    MONTH(NOW()) Current month 5

    EOMONTH() Give the last day of

    month

    8/1/2017 31

    Taming your data is only one function or two away

    16

  • FILTERING DATES

    Filter by Year,

    Month, and

    Day

    17

  • FILTERING DATES

    Date Filters

    18

  • SORTING DATES

    Filter > Sort Custom Sort

    Sorts based on

    column selected

    Sorts based on

    specified order

    19

  • CUSTOM SORT

    20

  • WORKING WITH

    NUMBERS

    Formatting, Filtering, Sorting

    21

  • FORMATTING NUMBERS

    Currency

    Percentage

    Comma

    Increase Decimal

    Decrease Decimal

    NUMBER Group

    22

  • FORMATTING NUMBERS

    Custom

    Numbers

    23

  • FILTERING NUMBERS

    Filter by

    Specific

    AmountHelpful when looking for a

    specific dollar amount

    24

  • FILTERING NUMBERS

    Number Filters

    25

  • SORTING NUMBERS

    Filter > Sort Custom Sort

    Sorts based on

    column selected

    Sorts based on

    specified order

    26

  • SUMMARIZING

    Quantifying text and numbers

    27

  • SUMMARIZING TEXT

    28

    FUNCTION DESCRIPTION

    CountA(range) Counts the cells in a range that are not empty

    CountifS(range) Counts the cells when a specified condition is met

    Sumifs(range) Adds the cells when a specified condition is met

    Taming your data is only

    one function or two away

  • COUNTA

    FORMULA

    =COUNTA(A2:A16)

    A2:A16 represents the range

    containing Doc Ln Descr

    Spaces or other hidden

    characters are counted

    Counts the cells in a range that are not empty

    29

  • COUNTIFS

    FORMULA

    =COUNTIFS(G2:G270,Person 8")

    G2:G270 represents the range

    containing Person 8

    Person 8 represents the condition

    to be met wrapped in

    (double quotes)

    Counts the cells when a specified condition is met

    30

  • SUMIFS

    FORMULA

    =SUMIFS(H2:H270,G2:G270,"person 8")

    H2:H270 represents the

    range to add up

    G2:G270 represents the range

    containing the values

    to compare

    person 8 represents the

    condition to be met

    Working with text doesnt

    have to be challenging

    Adds cells when specified condition is met

    31

  • SUMMARIZING NUMBERS

    32

    FUNCTION DESCRIPTION

    Count(range) Counts the cells in a range that contain numbers

    Sum(range) Sums the cells in a range that contain numbers

    Taming your data is

    only one function or

    two away

  • COUNT

    FORMULA

    =COUNT(B2:B16)

    B2:B16 represents the range

    containing Amount

    Text is not counted

    Counts the cells in a range that contain numbers

    33

  • SUM

    FORMULA

    =Sum(B2:B16)

    B2:B16 represents the range

    containing Amount

    Adds the cells in a range that contain numbers

    34

  • SUMMARIZING NUMBERS

    35

    FUNCTION DESCRIPTION

    Value(cell) Converts text to number

    Taming your data is only

    one function or two away

    Just because it

    looks like one,

    doesnt mean it is.

    Excel can store

    numbers as

    numbers or text.

  • SUMMARIZING DATA

    Classic 3

    Pivot Tables

    Over time

    Categorical

    Hierarchical

    36

  • HIERARCHICAL

    We created

    this field

    37

  • CATEGORICAL

    38

  • OVER TIME

    39

  • VISUALIZING

    Last step. Takes the work out of identifying trends and giving meaning to your data

    40

  • HOW MANY 8S DO YOU SEE?

    5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0

    41

  • NOW, HOW MANY?

    5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 89 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0

    42

  • DID YOU FIND 13?

    5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 89 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0

    43

  • WHY VISUALIZE?

    MIT neuroscientists find the

    brain can identify images

    seen for as little as

    13 milliseconds.

    Anne Trafton

    MIT News

    1/16/2014

    44

  • VISUALIZING TEXT

    Wingdings

    Its a Font

    Use instead of binary responses

    E.g. Yes or No, Completed or Uncompleted

    Calibri Wingdings

    n n

    Format as table

    45

  • VISUALIZING TEXT

    Cell Styles

    Transaction Register Themed

    Salaries June 2017 100 Y n

    Commencement Expenses 200 Y n

    Office Supplies 300 N

    ProCard Puchases 400 Y n

    Cell Styles

    46

  • VISUALIZING NUMBERS

    Conditional Formatting

    Filter results by

    Color

    47

  • VISUALIZING DATA

    Classic 3

    Over time

    Categorical

    Hierarchical

    48

  • OVER TIME

    49

    Slicer

  • CATEGORICAL

    50

  • HIERARCHICAL

    51

    Slicer

  • PRINTING DATA

    PAGE LAYOUT TAB PAGE LAYOUT BUTTONS

    52

    Margins

    Orientation

    Print titles

    Selection area

    Export to PDF

    Normal

    Page Layout

    Page Break Preview

  • GETTING

    ASSISTANCE

    53

  • WEB RESOURCES

    GCF Learn Free

    GCF Learn Free.org

    Subsidiary of Goodwill Industries

    Step-by-step instructions & videos

    Google Search

    Use English like phrase

    Find instructions and videos

    YouTube Search

    Use English like phrases

    Find videos

    Start with the short ones

    CSU SkillPort

    Several courses that include videos

    54

    https://www.gcflearnfree.org/https://www.google.com/search?q=how+to+use+excel+2016&ie=utf-8&oe=utf-8https://www.google.com/search?q=YouTube+Video+https://www.youtube.com/watch?v%3DExJTTo8mw6g&ie=utf-8&oe=utf-8#q=how+to+use+excel+2016&tbm=vidhttps://centralstationu.skillport.com/skillportfe/main.action#whatshappening

  • SESSION SUMMARY

    Managing Workbooks

    Working with Data

    Summarizing Data

    Visualizing Data

    Printing Data

    Getting Help

    55

  • THATS ALL

    Thank you for your participation!

    56

  • EXTRA TIME

    Pivot Tables using Registration Data

    57