[eBook] Free: "Pro Excel - Pivot tables & VLookUp" $0 Amazon AU, US

1570

Affiliate

Go to Deal

Cashback

nocure on 06/07/2020 - 21:28 amazon.com.au (4437 clicks)
Last edited 06/07/2020 - 21:29

This post contains affiliate links. OzBargain might earn commissions when you click through and make purchases. Please see this page for more information.

This 2 in 1 bundle covers the following topics

Pivot Tables

• Introduction to Pivot Tables
• Creating a Pivot Table
• Creating Pivot Charts
• Slicers and Advanced Filtering
• Calculations in Pivot Tables
• Customizing Pivot Tables
• Using VBA to create Pivot Tables

VLookUp

• Basic excel lookup functions
• Using VLOOKUP to search within a large volume of data
• VLOOKUP Error messages
• The MATCH function
• Applying VLOOKUP in VBA
• Advanced Tips & Tricks

US Link

Related Stores

Amazon AU

Marketplace

Amazon Cloud Reader

• 

  jv on 06/07/2020 - 21:31

  +1

  Pivot tables & VLookUp

  What about XLOOKUP ?

  • 

    Munki on 06/07/2020 - 23:37

    Shhh…Not allowed to talk about that…yet.

    • 

      life is suffering on 07/07/2020 - 11:08

      Psst you want some HLOOKUP ?

• 

  SpendLess on 06/07/2020 - 21:33

  +8

  vlookups are rubbish
  learn index match right from the start

  • 

    jv on 06/07/2020 - 21:42

    +2

    index, match are now redundant with the introduction of XLOOKUP

    • 

      SpendLess on 06/07/2020 - 21:45

      Yeah if you are on the new excel version.
      Just looked it up, might start using it now we on 365 but im so used to using index match it probably slow me down

    • 

      pdtmathieson on 06/07/2020 - 22:22

      Got to consider backwards compatibility… that said, XLOOKUP looks like it can do a lot of things that Excel users have been working around with for a long time.

    • 

      Jackson on 06/07/2020 - 23:38

      +4

      Is that like xhamster?

      • 

        Munki on 07/07/2020 - 01:05

        Can you buy hamsters from xhamster? I should show my kids.

  • 

    smpantsonfire on 06/07/2020 - 22:20

    They both have their weaknesses and strong points.

    • 

      jv on 06/07/2020 - 22:35

      like what?

      • 

        Reuborz on 06/07/2020 - 23:03

        +4

        Prefer to use vlookup when possible over index/match myself, for a few reasons:
        - Easier to quickly type/select cells, if you're referencing a table on another sheet with index match you go, table sheet, formula sheet, table sheet. vlookup is just formula sheet, table sheet.
        - Can return multiple columns at once easily with vlookup: =VLOOKUP(A2,data!A:E,{2,3,4,5},0) (array formula)
        - Google sheets array formula will expand =VLOOKUP(A2:A, TABLE, 2, 0) all the way down your sheet, meaning you don't have to drag down the formula

        Index match also useful/required in some places though:
        - Returning data to the left of your lookup column
        - Returning data based on multiple lookup values/columns at once (array formula needed here)

        Best tip for getting speed out of your index match in a big sheet:
        If you’re returning multiple columns of results, create one column for the match formula, and just put an index in each of the other columns

        • 

          jv on 06/07/2020 - 23:07

          @Reuborz:

          Prefer to use vlookup when possible over index/match myself,

          vlookup only works when searching for data in columns to the right, index/match works on any columns (as does xlookup)

          • 

            Munki on 06/07/2020 - 23:39

            @jv: Hence why you arrange your table such that your lookup ID is always on the leftmost column.

            • 

              jv on 07/07/2020 - 00:01

              +1

              @Munki: It's not always your table to arrange, or you might only have read access to the file.

              Also, if the lookup data is a foreign key, it doesn't make sense to put it on the left.

              • 

                Munki on 07/07/2020 - 01:04

                @jv: Then I'll make it my table. Haven't had any complaints thus far.

• 

  canberrascooter on 06/07/2020 - 22:35

  lol, just studied pivot tables in Macquaries Uni's Excel for business specialization.

  https://www.youtube.com/playlist?list=PLVcsagPgWhhxjKYi0_C8F…

  Will DL this book after this, anyone signing for modeloff this year?

• 

  Baghern on 06/07/2020 - 22:46

  +2

  F'Ck Slicers, don't do slicers…

  Them young'n always trying to be fancy… then discovering most computers can't handle slicers with substantial data… where you want to use them

  • 

    Phobic on 06/07/2020 - 22:58

    Ehh, They're ok.

    I've definitely been burnt by them before. But I've got some workbook that use slicers. Just saves a couple of minutes every month.

• 

  amsaini15 on 06/07/2020 - 23:03

  Is there youtube video or course that teaches use of excel for real life data analysis and reporting?

  • 

    canberrascooter on 06/07/2020 - 23:09

    +2

    yeah I am doing them. All on coursera, all free if you can finish the cause within 7 days.

    Macq Uni excel for business.
    Wharton's two course on data analystics
    PWC's data analysis cause.

    these are the courses I'm doing.

    https://www.youtube.com/channel/UClZc7HD3TWOX8kVVe12xCqg?vie…

    • 

      Must Buy More on 07/07/2020 - 08:39

      Thanks

• 

  [Deactivated] on 07/07/2020 - 18:22

  -2

  What is the purpose of excel, apart from business, can you do scientific notation,can you build a XML sheet? Can you create a pysuedo type code layout, can you build a mysql data sheet generated layout…

  Anyone

  Anyone