Using the Data Model to Create Pivot Tables: 1-Hour Tutorial!

00:42:46
https://www.youtube.com/watch?v=udQNjLv--Vs

Résumé

TLDRIn this informative webinar, Deborah Ashby teaches attendees how to utilize the data model in Excel to create advanced pivot tables. The session begins with a brief review of basic pivot table creation and emphasizes the benefits of integrating multiple data sources through the data model. Important concepts such as importing data using 'Get and Transform,' utilizing Power Query for data cleaning, and establishing relationships between tables are explored. Participants learn how to create a pivot table from combined datasets, use descriptive fields for better interpretation, and incorporate pivot charts. The session concludes with a demonstration of efficiently updating pivot tables when new data is added, highlighting the structured process of maintaining ongoing analyses with ease. A variety of techniques are discussed, making the content valuable for users at different expertise levels.

A retenir

  • 📊 Understand the importance of the data model in Excel.
  • 💡 Learn to create pivot tables from multiple data sources.
  • 🔗 Establish relationships between different tables.
  • 🛠️ Use Power Query for importing and transforming data.
  • 🔄 Easily update pivot tables with new data.
  • 📂 Link to a folder for efficient data management.
  • 📈 Create meaningful pivot charts from your data.
  • 📝 Name your tables meaningfully for better organization.
  • 🎓 Ideal for both beginners and advanced users.
  • 🗨️ Interactive Q&A for participant engagement.

Chronologie

  • 00:00:00 - 00:05:00

    The webinar introduces the concept of using the data model to create a pivot table, following a previous session on basic pivot table creation. Deborah Ashby explains that while prior knowledge of pivot tables is helpful, it's not necessary for attending this session, and offers a quick recap of creating a basic pivot table from a single data source.

  • 00:05:00 - 00:10:00

    Deborah demonstrates creating a basic pivot table using a sales data file that includes information about orders, customers, and salespersons. She emphasizes the importance of formatting data as an Excel table, which simplifies the pivot table creation process. Deborah creates a pivot table using this single data source, highlighting its limited analytical capability compared to utilizing multiple data sources.

  • 00:10:00 - 00:15:00

    The session transitions to understanding the data model's purpose. Deborah outlines that the data model is essential for analyzing data from multiple sources, illustrating the difference between single-source pivot tables and those generated from a data model. The agenda includes combining files to enhance analysis, using Power Query for data management, and creating consolidated pivot tables.

  • 00:15:00 - 00:20:00

    Deborah opens transaction files for 2019 and 2020 to illustrate the consistency between their data structures. She explains the limitations in analysis due to the generic transaction data and the necessity of pulling in additional data related to accounts and products for a more meaningful analysis.

  • 00:20:00 - 00:25:00

    To create a more effective pivot table, Deborah explores how to combine transaction data from two years while ensuring data consistency across files. She offers insight into setting up a folder structure for ease of updating with additional transaction files in the future, emphasizing the power of having a well-organized data model.

  • 00:25:00 - 00:30:00

    Using Excel's Power Query, Deborah demonstrates how to import data from the folder containing transaction files. She explains the process of combining these two transaction data files in Power Query, demonstrating how to clean and prepare data efficiently, leading to a consolidated view suitable for pivot table creation.

  • 00:30:00 - 00:35:00

    After combining transaction data, Deborah adds additional tables (accounts and products) into the data model, elaborating on how to link different tables using common fields. She explains the necessity of creating relationships between tables for effective pivot table analysis, highlighting the importance of having a structured data model.

  • 00:35:00 - 00:42:46

    The session concludes with Deborah demonstrating how to create a pivot chart using the data model and subsequently updating it with new transaction data. She illustrates how the connection to the folder allows for straightforward updates to pivot tables and charts with minimal effort, showcasing the efficiency of a well-structured data model.

Afficher plus

Carte mentale

Vidéo Q&R

  • What is the focus of this webinar?

    The webinar focuses on using the data model to create pivot tables in Excel.

  • Is prior knowledge of pivot tables required for this session?

    No, but a basic understanding will be helpful.

  • What software is this training about?

    This training is about Microsoft Excel.

  • What is the data model?

    The data model is a way to analyze data from multiple sources using pivot tables.

  • How can I update my pivot tables when using a data model?

    You can quickly update pivot tables by refreshing them after adding new data files to the connected folder.

  • What user-friendly tool is introduced during the session?

    Power Query is introduced for transforming and cleaning data.

  • What is one main advantage of using the data model?

    The data model allows combining data from multiple tables into a consolidated pivot table.

Voir plus de résumés vidéo

Accédez instantanément à des résumés vidéo gratuits sur YouTube grâce à l'IA !
Sous-titres
en
Défilement automatique:
  • 00:00:00
    [Music]
  • 00:00:01
    Simon Says subscribe and click on the
  • 00:00:05
    Bell icon to receive notifications
  • 00:00:09
    welcome thank you so much for joining us
  • 00:00:12
    this month for today's webinar which is
  • 00:00:15
    using the data model to create a pivot
  • 00:00:17
    table and this is actually a really nice
  • 00:00:21
    follow-on session from the webinar that
  • 00:00:23
    we did last month where we took a look
  • 00:00:26
    at basically creating pivot tables from
  • 00:00:28
    scratch always a popular topic now if
  • 00:00:31
    you didn't attend last month don't worry
  • 00:00:33
    at all it isn't a prerequisite to be a
  • 00:00:36
    to be able to follow this particular
  • 00:00:38
    session but I will say that if you do
  • 00:00:40
    have a basic knowledge of pivot tables
  • 00:00:42
    then this is going to help you out a lot
  • 00:00:43
    in this session as we go through it I am
  • 00:00:46
    going to do a quick recap of creating a
  • 00:00:49
    basic pivot table not using the data
  • 00:00:51
    model at the beginning so that will give
  • 00:00:54
    you it will be very brief but it will
  • 00:00:56
    give you a bit of an idea if you're not
  • 00:00:58
    used to creating pivot tables or not as
  • 00:01:00
    familiar with that now the reason why
  • 00:01:02
    I'm going to start out with just
  • 00:01:04
    creating a basic pivot table is because
  • 00:01:05
    it illustrates really nicely why we
  • 00:01:08
    might want to use the data model to
  • 00:01:10
    create our pivot tables instead
  • 00:01:13
    now before we get on to all of that I do
  • 00:01:16
    have quite a bit of housekeeping today
  • 00:01:18
    so let's run through that very quickly
  • 00:01:20
    now for those of you that are new to our
  • 00:01:23
    training sessions I know we do get a lot
  • 00:01:25
    of regulars so I apologize if you see
  • 00:01:28
    this every single month but for anybody
  • 00:01:30
    who is new let me just introduce myself
  • 00:01:32
    so my name is Deborah Ashby and I'm our
  • 00:01:35
    it trainer and Microsoft subject matter
  • 00:01:38
    expert and I've been a trainer for a
  • 00:01:40
    pretty long time about 16 years now and
  • 00:01:43
    I've worked in the The Wider IT industry
  • 00:01:46
    not necessarily in training for a total
  • 00:01:48
    of about 25 years it always makes me
  • 00:01:51
    feel really old when I say that and it
  • 00:01:54
    is my absolute pleasure to be hosting
  • 00:01:55
    this webinar for you today on my
  • 00:01:58
    favorite subject which is basically
  • 00:01:59
    Excel pivot tables I am going to provide
  • 00:02:02
    you with the files that I'm going to be
  • 00:02:04
    using throughout this session and I know
  • 00:02:06
    a lot of people do find
  • 00:02:08
    um do you find that I go through a bit
  • 00:02:10
    too quick for them in the webinar and
  • 00:02:12
    they like to go back and re-watch it at
  • 00:02:14
    their own pace with the course files so
  • 00:02:16
    I definitely encourage you to do that if
  • 00:02:19
    you want to
  • 00:02:20
    and for this specific lesson there's
  • 00:02:23
    quite a few files and the way that you
  • 00:02:25
    save them is going to be quite important
  • 00:02:28
    this if you can see my desktop just here
  • 00:02:30
    I have a folder here called transaction
  • 00:02:32
    data if I double click inside that I
  • 00:02:35
    have transactions 2019 2020 and then I
  • 00:02:38
    have the other files that we're going to
  • 00:02:40
    be using outside of that folder simply
  • 00:02:42
    on my desktop what I want to do now is
  • 00:02:45
    just quickly walk you through today's
  • 00:02:46
    agenda what are we going to do with all
  • 00:02:48
    of those files that we've just
  • 00:02:50
    downloaded
  • 00:02:51
    well the first thing we're going to do
  • 00:02:53
    is we're going to talk about what
  • 00:02:55
    exactly is the data model and why do we
  • 00:02:58
    use it now I found that the best way to
  • 00:03:00
    understand this is to start with a basic
  • 00:03:02
    example of a pivot table report so again
  • 00:03:05
    if you attended the session last month
  • 00:03:07
    then we ran through pivot tables but in
  • 00:03:10
    this I am going to show you how we can
  • 00:03:11
    build a basic pivot table report and
  • 00:03:14
    during that I'm going to show you the
  • 00:03:16
    difference between doing a basic pivot
  • 00:03:18
    table report from one data source and
  • 00:03:21
    using the data model
  • 00:03:23
    what we're then going to do is we're
  • 00:03:24
    going to open up some of those files
  • 00:03:26
    that I've sent through to you so that we
  • 00:03:28
    can understand exactly what we're
  • 00:03:30
    looking at the data that we're using and
  • 00:03:32
    why we're using so many different files
  • 00:03:34
    in this session
  • 00:03:36
    we're then going to move on to importing
  • 00:03:38
    our data in using get and transform and
  • 00:03:41
    I'm going to show you how you can link
  • 00:03:43
    to a specific folder which is going to
  • 00:03:46
    make your life a lot easier when it
  • 00:03:47
    comes to adding data to and updating
  • 00:03:50
    your pivot tables later on
  • 00:03:53
    we're going to talk a little bit about
  • 00:03:54
    the power query editor we're not going
  • 00:03:56
    to linger too much on that because it is
  • 00:03:58
    an entire Topic in its own right but we
  • 00:04:01
    will take a quick look at that I'm going
  • 00:04:03
    to show you how you can do things like
  • 00:04:04
    combine multiple files together and then
  • 00:04:06
    how you can add data to the data model
  • 00:04:10
    we're going to take a look at creating
  • 00:04:12
    relationships between different tables
  • 00:04:14
    of data and then how we can use all of
  • 00:04:17
    our data to create what we call a
  • 00:04:19
    Consolidated pivot table
  • 00:04:21
    we're going to finish out the session by
  • 00:04:23
    creating a pivot chart based off of the
  • 00:04:25
    data model and then I'm going to show
  • 00:04:27
    you how easy it is to update the data
  • 00:04:30
    now if you've never used data models
  • 00:04:32
    before then most of that agenda is
  • 00:04:34
    probably not going to make too much
  • 00:04:35
    sense to you things like get and
  • 00:04:37
    transform power query combining you know
  • 00:04:40
    what does this all mean it is going to
  • 00:04:42
    become clear as we work our way through
  • 00:04:46
    so let's start out with that first
  • 00:04:48
    initial question what is the data model
  • 00:04:50
    and why do we use it now as I said to
  • 00:04:53
    illustrate this we're going to start out
  • 00:04:56
    by building a basic pivot table report
  • 00:04:58
    and we're going to use one of the files
  • 00:05:01
    that we've downloaded
  • 00:05:03
    so I'd like you to go to your desktop
  • 00:05:06
    and first of all open up the file called
  • 00:05:09
    sales2020.xlsx
  • 00:05:13
    yes
  • 00:05:14
    and it's this one just here
  • 00:05:19
    so I'll just give you a couple of
  • 00:05:21
    seconds to orientate yourselves because
  • 00:05:23
    a lot of information in the first five
  • 00:05:25
    minutes
  • 00:05:29
    all right
  • 00:05:30
    so once you've got that open this is
  • 00:05:32
    basically just some data that relates to
  • 00:05:35
    different sales so we've got in column A
  • 00:05:37
    we have order ID then we have customer
  • 00:05:39
    ID trip name so this is sales data
  • 00:05:42
    related to holidays to different trips
  • 00:05:44
    we have the sales person responsible for
  • 00:05:47
    that sale the trip price and the
  • 00:05:49
    quantity that they sold of that trip so
  • 00:05:51
    some very basic data now if I wanted to
  • 00:05:54
    analyze this data of course I could put
  • 00:05:57
    this into a pivot table
  • 00:06:00
    and currently this data that I'm using
  • 00:06:02
    is all coming from One Source it's all
  • 00:06:04
    here in this spreadsheet that I have
  • 00:06:06
    open
  • 00:06:07
    so let's just walk through the process
  • 00:06:09
    of creating a basic pivot table so you
  • 00:06:11
    can understand why we might use the data
  • 00:06:13
    model instead
  • 00:06:15
    so the first thing I'm going to do here
  • 00:06:17
    is this data is completely unformatted
  • 00:06:19
    I've tidied it up a little bit it's
  • 00:06:21
    quite neat but I'm going to put it into
  • 00:06:23
    an Excel table first this is my biggest
  • 00:06:26
    top tip always put data sets into an
  • 00:06:29
    Excel table there are many advantages to
  • 00:06:31
    doing this now a quick way to create an
  • 00:06:34
    Excel table is to click anywhere in your
  • 00:06:36
    data and press the Ctrl T keyboard
  • 00:06:39
    shortcut
  • 00:06:40
    you can see I've got my martians around
  • 00:06:43
    the outside of my data set that is the
  • 00:06:45
    data I want to use I want to make sure
  • 00:06:48
    that I have a tick in my table has
  • 00:06:49
    headers I click on OK and I've created
  • 00:06:52
    myself an Excel table if you don't
  • 00:06:55
    particularly like your table style
  • 00:06:57
    remember you can change it from here to
  • 00:06:59
    something a bit more pleasing to your
  • 00:07:01
    eye let's go for a nice orange color
  • 00:07:04
    so now that we've created a table we
  • 00:07:06
    have our table design contextual ribbon
  • 00:07:09
    again what I always advise people to do
  • 00:07:11
    when they're creating Excel tables is to
  • 00:07:14
    make sure that you give your table a
  • 00:07:15
    meaningful name it just helps you
  • 00:07:17
    identify your table
  • 00:07:19
    if we take a look at the first group on
  • 00:07:23
    this table design ribbon you can see it
  • 00:07:25
    says table name is currently table one
  • 00:07:27
    that's a bit too generic so let's call
  • 00:07:30
    this sales underscore
  • 00:07:33
    2020 and hit enter remember you can't
  • 00:07:36
    have any spaces in your table name so it
  • 00:07:40
    either has to be one word or separated
  • 00:07:42
    with an underscore
  • 00:07:44
    so I always do that before I put my data
  • 00:07:46
    or before I analyze my data in a pivot
  • 00:07:49
    table
  • 00:07:50
    so at this stage this is where I might
  • 00:07:52
    convert this to a pivot table again a
  • 00:07:54
    couple of different ways that I can do
  • 00:07:56
    this I can go on to the insert ribbon go
  • 00:07:59
    to Pivot tables and choose from table
  • 00:08:01
    range or because I'm already on that
  • 00:08:04
    table design ribbon when I'm renaming
  • 00:08:06
    the table I can choose summarize with
  • 00:08:09
    pivot table from here
  • 00:08:11
    now this is where it gets a little bit
  • 00:08:13
    more interesting So currently it's
  • 00:08:16
    telling me that it's going to create my
  • 00:08:17
    pivot table based off of my table range
  • 00:08:20
    and you can see it's picked up the name
  • 00:08:22
    of my table sales underscore 2020 which
  • 00:08:25
    in this case is exactly what I want it
  • 00:08:27
    to do
  • 00:08:28
    I can then choose where I want to put
  • 00:08:30
    this pivot table so do I want to put it
  • 00:08:32
    on a new worksheet or do I want to put
  • 00:08:34
    it on the existing worksheet again my
  • 00:08:37
    recommendation is always separate your
  • 00:08:39
    pivot tables from your Source data so
  • 00:08:42
    I'm going to keep mine on new worksheet
  • 00:08:45
    now normally at this stage I would just
  • 00:08:47
    click on OK and off we would go creating
  • 00:08:50
    a pivot table
  • 00:08:51
    but take a look at this bottom option we
  • 00:08:54
    also have an option to add this data to
  • 00:08:57
    the data model and take out take a look
  • 00:09:00
    at the heading just above there it says
  • 00:09:02
    choose whether you want to analyze
  • 00:09:04
    multiple tables and this is the
  • 00:09:08
    difference you would use the data model
  • 00:09:10
    if you want to analyze data from
  • 00:09:14
    multiple sources so in this example all
  • 00:09:18
    of the data I want to analyze is
  • 00:09:19
    contained within this table range One
  • 00:09:22
    Source but what if I wanted to analyze
  • 00:09:25
    data from lots of different tables maybe
  • 00:09:27
    other tables in this worksheet or maybe
  • 00:09:30
    other tables in other workbooks maybe I
  • 00:09:34
    want to bring them all together and be
  • 00:09:36
    able to analyze all of the different
  • 00:09:38
    fields in one pivot table that is where
  • 00:09:42
    we would use the data model so think
  • 00:09:44
    about this as a way to analyze multiple
  • 00:09:47
    sources of data and build a Consolidated
  • 00:09:50
    pivot table
  • 00:09:53
    so bear that in mind let's just quickly
  • 00:09:55
    create a pivot table
  • 00:09:58
    from this one data source so I'm not
  • 00:10:00
    going to add it to the data model I'm
  • 00:10:01
    just going to say new worksheet and
  • 00:10:03
    click on OK
  • 00:10:04
    so here this is your standard pivot
  • 00:10:06
    table it's created a new sheet at the
  • 00:10:09
    bottom I have my blank pivot table
  • 00:10:11
    report and it's populated it over here
  • 00:10:13
    with my pivot table fields and these are
  • 00:10:16
    effectively just the column headings
  • 00:10:18
    from my data source I then have the four
  • 00:10:21
    areas and I can simply drag and drop my
  • 00:10:24
    headings into these different areas to
  • 00:10:26
    build my pivot table that is effectively
  • 00:10:28
    all a pivot table is so it's very easy
  • 00:10:31
    for me to do an analysis here so maybe I
  • 00:10:33
    want to see how many trips each sales
  • 00:10:36
    person has sold I could drag sales
  • 00:10:39
    person into rows like a drag trip price
  • 00:10:42
    into values and very quickly I've done
  • 00:10:44
    an analysis just there
  • 00:10:46
    I could remove salesperson and maybe I
  • 00:10:49
    want to see This Time by trip name I
  • 00:10:51
    could drag that into rows I could even
  • 00:10:54
    add salesperson into columns to get a
  • 00:10:56
    completely different way of analyzing
  • 00:10:58
    this data that is a basic pivot table
  • 00:11:02
    we're using one source of data in order
  • 00:11:05
    to perform our analysis okay so that's a
  • 00:11:09
    quick recap on pivot tables and
  • 00:11:12
    hopefully there you can see the
  • 00:11:14
    difference data model is when we want to
  • 00:11:16
    use multiple sources as opposed to one
  • 00:11:21
    so with all that in mind let's close
  • 00:11:25
    down this particular worksheet because
  • 00:11:27
    we're going to take a look at some of
  • 00:11:29
    the files that we're going to be using
  • 00:11:30
    so you can understand what we're doing
  • 00:11:33
    now if you recall I asked you to create
  • 00:11:36
    a folder on your desktop it's this one
  • 00:11:38
    just here I've called mine transaction
  • 00:11:40
    data let's double click to open this up
  • 00:11:43
    and in there we should find we have two
  • 00:11:47
    files transactions 2019 and transactions
  • 00:11:50
    20 20.
  • 00:11:52
    now what I'm going to do here is I'm
  • 00:11:54
    just going to open up
  • 00:11:56
    both of these and I'm going to put them
  • 00:11:58
    in a split screen format so they're a
  • 00:12:01
    bit easier to compare side by side
  • 00:12:03
    so let's open up both of them
  • 00:12:07
    and put them
  • 00:12:10
    put them next to each other just there
  • 00:12:13
    now the first thing you'll notice about
  • 00:12:15
    these two files is that they are
  • 00:12:17
    consistent in the way that they're laid
  • 00:12:19
    out so you can see that each of these
  • 00:12:21
    files holds transaction data but they're
  • 00:12:24
    just for different years
  • 00:12:26
    so for this one here in column A this is
  • 00:12:28
    the transaction data for 2019. and this
  • 00:12:32
    one here in column A is the transaction
  • 00:12:34
    data for 20 20.
  • 00:12:36
    all of the column headings
  • 00:12:39
    I apologize for that motorbike going
  • 00:12:40
    past all of the column headings are
  • 00:12:43
    exactly the same so we have date uh
  • 00:12:46
    transaction ID product number account
  • 00:12:48
    number and amount and that is consistent
  • 00:12:50
    across both of these files
  • 00:12:52
    so maybe what I want to do is create a
  • 00:12:55
    pivot table where I can use all of this
  • 00:12:58
    data but my data is split across two
  • 00:13:00
    different files transactions 2019 and
  • 00:13:03
    transactions 20 20. now because this
  • 00:13:06
    data is consistent in its columns what I
  • 00:13:09
    could do here is I could combine both of
  • 00:13:11
    these files into one big file and then
  • 00:13:15
    analyze that data which is exactly what
  • 00:13:18
    we're going to do
  • 00:13:20
    now something else that I might want to
  • 00:13:22
    do here is if we take a look at the data
  • 00:13:25
    that we have in here it's not
  • 00:13:26
    particularly meaningful in these two
  • 00:13:28
    files I have transaction IDs which are
  • 00:13:31
    just random numbers I have product
  • 00:13:33
    numbers I have account numbers so on and
  • 00:13:35
    so forth
  • 00:13:37
    I was doing a pivot table based off of
  • 00:13:39
    this data it's not going to be really
  • 00:13:41
    very meaningful because what I might
  • 00:13:43
    have here is a pivot table that shows me
  • 00:13:45
    the product number and then the amount
  • 00:13:48
    what would be more meaningful is if I
  • 00:13:51
    had the product name as opposed to the
  • 00:13:53
    product number
  • 00:13:55
    what might be more meaningful is if I
  • 00:13:57
    had the account name as opposed to the
  • 00:14:00
    account number but that data doesn't
  • 00:14:02
    actually exist in either of these two
  • 00:14:05
    files
  • 00:14:06
    however we do have some other files that
  • 00:14:09
    contains this data
  • 00:14:11
    so let's just minimize these down
  • 00:14:13
    and take a look at the other files that
  • 00:14:16
    I sent through now the first one we're
  • 00:14:18
    going to open is this one here it's
  • 00:14:20
    called
  • 00:14:21
    accounts.xlsx let's double click and
  • 00:14:24
    take a look at this
  • 00:14:27
    now what we have in here is a very small
  • 00:14:31
    spreadsheet that shows me the account
  • 00:14:33
    number and the accounts that that
  • 00:14:35
    relates to so if I put this side by side
  • 00:14:38
    with one of one of our transaction files
  • 00:14:40
    you can see in the account number column
  • 00:14:43
    we're only going to have one of four
  • 00:14:45
    numbers in here it's either going to be
  • 00:14:47
    five thousand six thousand seven
  • 00:14:49
    thousand or eight thousand but these
  • 00:14:51
    account numbers relate to these specific
  • 00:14:54
    accounts
  • 00:14:56
    the same thing occurs with the products
  • 00:14:59
    spreadsheet as well so if we open this
  • 00:15:02
    one up and take a look at what we have
  • 00:15:04
    in there
  • 00:15:06
    are you being slow XL or did I not click
  • 00:15:08
    there we go
  • 00:15:11
    once again we have just a very small
  • 00:15:13
    table that shows the product numbers
  • 00:15:15
    which relates to the product number
  • 00:15:17
    column in the transaction spreadsheet
  • 00:15:19
    and the actual meaningful products that
  • 00:15:22
    those numbers relate to
  • 00:15:24
    so if I want to build a big meaningful
  • 00:15:26
    pivot table that's going to make sense
  • 00:15:28
    to my stakeholders and to my CEO and
  • 00:15:30
    whoever else who wants to read this data
  • 00:15:33
    I want to combine all of these tables
  • 00:15:36
    together and create one big pivot table
  • 00:15:39
    it means I'm going to be able to do
  • 00:15:41
    things like create a pivot table that
  • 00:15:43
    shows the product name and the amount of
  • 00:15:46
    that product that's sold okay so we have
  • 00:15:49
    our data stored in multiple different
  • 00:15:52
    sources we have the products file we
  • 00:15:54
    have the accounts file and then we have
  • 00:15:56
    our two transaction files as well
  • 00:15:59
    so this is where the data model is going
  • 00:16:02
    to be particularly useful
  • 00:16:04
    now I'm just going to close down all of
  • 00:16:07
    the files that I've currently got open
  • 00:16:09
    do I have anything else open yes I do
  • 00:16:11
    let's close down both of those
  • 00:16:15
    and make sure that that is closed
  • 00:16:19
    and I'm just going to double click to
  • 00:16:21
    open up the transaction folder again
  • 00:16:24
    so the first thing that we're going to
  • 00:16:25
    do here is I'm going to combine both of
  • 00:16:28
    these files together into one big file
  • 00:16:31
    now notice here that I've got them
  • 00:16:34
    stored in a folder called transaction
  • 00:16:36
    data and currently I have the file for
  • 00:16:39
    2019 and 2020. now each year a new file
  • 00:16:44
    is going to be added so I have the
  • 00:16:46
    transactions 2021 file ready to go into
  • 00:16:49
    this folder so when I'm creating my
  • 00:16:52
    pivot table and setting this up using
  • 00:16:54
    the data source I sorry using the data
  • 00:16:57
    model I want to set this up in such a
  • 00:16:59
    way so that it's extremely efficient so
  • 00:17:02
    that when a new file gets added into
  • 00:17:04
    this transaction data folder I can
  • 00:17:07
    update my Pivot tables in any pivot
  • 00:17:09
    charts that I've created with the click
  • 00:17:11
    of one button
  • 00:17:13
    so we're going to set this up so that
  • 00:17:15
    links to this folder so that anything
  • 00:17:18
    new that's added to the folder is
  • 00:17:20
    automatically going to be included in
  • 00:17:22
    our report okay
  • 00:17:24
    so we're doing a few different things
  • 00:17:26
    here quite a lot going on
  • 00:17:29
    so let's close this down and let's open
  • 00:17:32
    up Excel
  • 00:17:36
    now for this we're just going to start
  • 00:17:38
    with a blank workbook and I will say if
  • 00:17:41
    you are following along with me make
  • 00:17:43
    sure that you have all of the other
  • 00:17:44
    Excel files closed down before you begin
  • 00:17:48
    so let's maximize this out
  • 00:17:51
    now the first thing I'm going to do is
  • 00:17:52
    I'm going to create a connection to that
  • 00:17:55
    transaction data folder I'm going to
  • 00:17:57
    create a link between this Excel
  • 00:17:59
    workbook and that folder now anytime we
  • 00:18:02
    want to bring in data from outside into
  • 00:18:05
    Excel we want to make sure that we go to
  • 00:18:07
    the data tab
  • 00:18:09
    and it's this first group here get and
  • 00:18:12
    transform data so this is where we can
  • 00:18:14
    pull data in from multiple different
  • 00:18:16
    sources so it really depends how you
  • 00:18:18
    have your data stored I could import
  • 00:18:20
    data from a text file or from the web or
  • 00:18:24
    from another table or range that I have
  • 00:18:27
    in my workbook and if I click the get
  • 00:18:29
    data drop down you can see that I have
  • 00:18:31
    multiple other places that I can grab
  • 00:18:34
    data from so if I've got it stored in
  • 00:18:36
    another Excel workbook I can link to
  • 00:18:38
    that from here as well now we want to
  • 00:18:41
    link to that transaction data folder and
  • 00:18:45
    notice right at the bottom of the list
  • 00:18:47
    this is where we can set up that link so
  • 00:18:49
    I'm going to say let's import data from
  • 00:18:52
    a folder
  • 00:18:55
    and I'm going to go to my desktop where
  • 00:18:57
    I have that transaction data folder now
  • 00:19:01
    I'm not going to go into this and select
  • 00:19:03
    the files I want to link to the actual
  • 00:19:05
    folder itself so let's select it and
  • 00:19:08
    click on open
  • 00:19:12
    and this is where it's going to pop open
  • 00:19:14
    this little window
  • 00:19:16
    so you can see here it's found the two
  • 00:19:19
    files that are contained within that
  • 00:19:21
    folder transactions 2019 and
  • 00:19:24
    transactions 2020.
  • 00:19:27
    and right at the bottom here I have a
  • 00:19:29
    few different things that I can do at
  • 00:19:31
    this stage notice I have a combine
  • 00:19:34
    button so as I mentioned because these
  • 00:19:36
    files are consistent in their column
  • 00:19:38
    headings it's going to be quite useful
  • 00:19:40
    for me to combine them into one big long
  • 00:19:42
    file now I can do that directly from
  • 00:19:45
    down here I can choose to load this
  • 00:19:48
    directly into Excel or into a pivot
  • 00:19:50
    table or I can choose to transform the
  • 00:19:53
    data now transforming data basically
  • 00:19:56
    gives you the opportunity to tidy up
  • 00:19:59
    your data so if you haven't cleaned your
  • 00:20:01
    data before beforehand you could
  • 00:20:03
    transform the data from here so let's
  • 00:20:06
    click that because it's going to jump us
  • 00:20:07
    into Power query
  • 00:20:11
    now once again this looks a little bit
  • 00:20:13
    overwhelming and we're not going to go
  • 00:20:14
    into this in any detail whatsoever this
  • 00:20:17
    is an entire session on its own all you
  • 00:20:20
    really need to know is that power query
  • 00:20:21
    is a more user-friendly way of cleaning
  • 00:20:25
    up Excel data so if you've ever had a
  • 00:20:27
    data source in Excel and you've done
  • 00:20:29
    things like remove the duplicates
  • 00:20:31
    removed blank rows maybe done a spell
  • 00:20:33
    check or a find and replace
  • 00:20:35
    all of that process is called cleaning
  • 00:20:38
    data and we can do the same thing in
  • 00:20:41
    power query check out what we have up
  • 00:20:43
    here as an example we can do things like
  • 00:20:45
    remove columns we can split columns we
  • 00:20:48
    can change the formatting of different
  • 00:20:50
    columns things like that so this is
  • 00:20:52
    really a data cleaning utility
  • 00:20:55
    now we're not going to really use it to
  • 00:20:57
    clean the data because our data is
  • 00:20:59
    already clean but what we're going to do
  • 00:21:01
    is we're going to use it to combine
  • 00:21:03
    these two files together
  • 00:21:05
    so I've got both of my files sitting
  • 00:21:07
    there notice in this First Column where
  • 00:21:10
    it says content I have these two little
  • 00:21:12
    down facing arrows and it says combine
  • 00:21:16
    files if I click this it's going to
  • 00:21:19
    evaluate my two files
  • 00:21:22
    it's going to show me a sample of the
  • 00:21:24
    first file so it's going to show me
  • 00:21:26
    basically the first 200 rows of the
  • 00:21:29
    first file
  • 00:21:30
    that all looks good to me I'm going to
  • 00:21:32
    click on OK and it's going to combine
  • 00:21:35
    transactions 2019 and transactions 2020
  • 00:21:38
    together in one big long file
  • 00:21:43
    so a nice quick way of combining those
  • 00:21:45
    together
  • 00:21:46
    now at this stage I could look through
  • 00:21:48
    and I could start cleaning up this
  • 00:21:50
    combined data if I wanted to so maybe
  • 00:21:53
    I've got some errors in here some blank
  • 00:21:55
    rows I could use power query to tidy it
  • 00:21:57
    all up I'm going to do a couple of
  • 00:21:59
    little changes here because there is
  • 00:22:01
    something that's been added to my
  • 00:22:03
    spreadsheet that I don't particularly
  • 00:22:05
    need and that is this Source name column
  • 00:22:07
    because I have two files in here it's
  • 00:22:10
    helpfully giving me another color which
  • 00:22:12
    tells me what the source name is now I
  • 00:22:14
    don't really want that so I'm just going
  • 00:22:15
    to right click and I'm going to say
  • 00:22:17
    remove this column
  • 00:22:19
    the other thing I'm quickly going to do
  • 00:22:21
    is check to make sure I have the correct
  • 00:22:23
    data type selected for these fields so
  • 00:22:26
    this one is the date this one is a
  • 00:22:28
    number these all look pretty good
  • 00:22:30
    the amount now this is a currency amount
  • 00:22:33
    so I'm going to click where it says one
  • 00:22:34
    two three and make sure we set the data
  • 00:22:37
    type for that column to currency
  • 00:22:40
    as I said there is so much we could go
  • 00:22:42
    into in power query I'm just going to
  • 00:22:44
    show you those couple of little bits
  • 00:22:46
    here we've combined the files and we can
  • 00:22:48
    now load it back into Excel as one big
  • 00:22:51
    long file
  • 00:22:53
    so at the top here we have close and
  • 00:22:56
    load and I'm going to say close and load
  • 00:22:58
    to
  • 00:23:00
    now if I just wanted to combine these
  • 00:23:02
    two transaction files together and
  • 00:23:04
    create a pivot table I could select
  • 00:23:06
    create pivot table report from here and
  • 00:23:09
    it's going to automatically create the
  • 00:23:11
    report for me now I don't want to do
  • 00:23:13
    that because I have more data that I
  • 00:23:15
    want to add in remember we have that
  • 00:23:19
    smaller accounts workbook and we have
  • 00:23:21
    that smaller product workbook that I
  • 00:23:24
    want to use in my pivot table as well
  • 00:23:26
    so instead of creating the pivot table
  • 00:23:28
    report I'm going to say add this data to
  • 00:23:32
    the data model and click on OK
  • 00:23:36
    now it's going to go away and it's going
  • 00:23:37
    to create its Connections in the
  • 00:23:39
    background and you can see on the right
  • 00:23:41
    hand side I have a queries and
  • 00:23:43
    connections pane open that's telling me
  • 00:23:45
    that I've got 730 rows of data loaded in
  • 00:23:49
    so this is basically those two
  • 00:23:51
    transaction files combined together and
  • 00:23:54
    I should be able to check this by if I
  • 00:23:56
    click the filter at the top here yes I
  • 00:23:58
    have both of my data sets in there 2019
  • 00:24:01
    and 20 20.
  • 00:24:03
    but as I said this isn't the only data
  • 00:24:06
    that I want to use in my pivot table
  • 00:24:08
    so how can we also pull in those other
  • 00:24:13
    two tables of data
  • 00:24:15
    well we can do this in a few different
  • 00:24:16
    ways but I'm going to show you how to do
  • 00:24:18
    it using the power pivot ribbon
  • 00:24:21
    now if you take a look up at my ribbons
  • 00:24:24
    I have one here called Power pivot now
  • 00:24:27
    this isn't a ribbon that's turned on by
  • 00:24:29
    default so if you cannot see this ribbon
  • 00:24:32
    it might be that you need to add that in
  • 00:24:35
    in so if we go to file and down to let's
  • 00:24:40
    go to options
  • 00:24:42
    and uh we're going to go to customize
  • 00:24:46
    ribbon
  • 00:24:47
    and you can see it there selected on the
  • 00:24:50
    right hand side powerpivot if you have
  • 00:24:52
    it deselected then you're not going to
  • 00:24:53
    be able to see that ribbon now if you
  • 00:24:56
    can't see powerpivot in here at all it
  • 00:24:58
    might be that you need to go to add-ins
  • 00:25:01
    and I think this is under
  • 00:25:04
    com add-ins
  • 00:25:07
    let's click on go yes that one there
  • 00:25:09
    power pivot for Excel okay so if you
  • 00:25:13
    can't see it make sure you have that
  • 00:25:15
    add-in turned on and then you can see
  • 00:25:17
    that power pivot ribbon because this is
  • 00:25:19
    really going to help you when it comes
  • 00:25:21
    to adding things into the data model
  • 00:25:25
    so we've got our two transaction files
  • 00:25:27
    here I've got two more files that I want
  • 00:25:30
    to pull in before I create my pivot
  • 00:25:32
    table
  • 00:25:33
    so what we can do here is we can use our
  • 00:25:36
    powerpivot ribbon we can go to manage
  • 00:25:40
    notice that that is in the data model
  • 00:25:42
    group
  • 00:25:43
    and it's going to open up our
  • 00:25:45
    transaction data in the powerpivot
  • 00:25:47
    window
  • 00:25:48
    and take a look at the bottom we've got
  • 00:25:50
    the tab here transaction data and now I
  • 00:25:53
    can add more tables into here which is
  • 00:25:56
    effectively adding them into the data
  • 00:25:59
    model
  • 00:26:00
    so if we take a look at the ribbons at
  • 00:26:02
    the top where we have get external data
  • 00:26:04
    I'm going to choose from other sources
  • 00:26:09
    so this is where I can now go away and
  • 00:26:11
    choose where my other tables of data
  • 00:26:13
    that I want to use are located so maybe
  • 00:26:16
    I have a table in Access that I want to
  • 00:26:18
    use maybe it's a text file in this case
  • 00:26:21
    I have two other Excel files that I want
  • 00:26:24
    to pull in so I'm going to choose Excel
  • 00:26:26
    file click on next and now I can simply
  • 00:26:30
    browse for the first file
  • 00:26:33
    so I want to pull in the accounts file
  • 00:26:35
    and the products file as well I want to
  • 00:26:37
    use these along with my transaction data
  • 00:26:40
    in my pivot table so let's select
  • 00:26:43
    accounts first of all we have to do
  • 00:26:45
    these one at a time
  • 00:26:47
    I'm going to say use the first row as
  • 00:26:49
    the column headings
  • 00:26:52
    click on next and click on finish
  • 00:26:56
    and you can see it's imported four rows
  • 00:26:59
    successfully and if you recall this
  • 00:27:02
    actual data was very short and you can
  • 00:27:04
    see it loaded into the new window just
  • 00:27:06
    here that is the account data and the
  • 00:27:09
    accounts that match it
  • 00:27:11
    now notice at the bottom we now have two
  • 00:27:12
    sheets so I've got my transaction data
  • 00:27:14
    as one part of the data model and then I
  • 00:27:17
    just have sheet1 now I'm going to right
  • 00:27:18
    click and rename this to make it more
  • 00:27:21
    meaningful let's call this accounts
  • 00:27:25
    and now I can go back up and repeat that
  • 00:27:27
    process to pull in the other worksheet
  • 00:27:31
    so again from other sources I'm going to
  • 00:27:34
    scroll all the way down
  • 00:27:36
    import from Excel
  • 00:27:39
    I'm going to browse
  • 00:27:41
    and I'm going to choose products this
  • 00:27:44
    time
  • 00:27:46
    I'm going to use the first row as column
  • 00:27:48
    headings
  • 00:27:50
    and click on finish
  • 00:27:54
    and that's going to import that table
  • 00:27:57
    into the data model as well so now at
  • 00:28:00
    the bottom I have three tabs I have my
  • 00:28:02
    transaction data tab which is a
  • 00:28:04
    combination of two different files
  • 00:28:05
    transactions 2019 and 2020 I have my
  • 00:28:09
    accounts table and I have my products
  • 00:28:12
    table and these are all now part of the
  • 00:28:15
    data model and I can use them to build a
  • 00:28:18
    pivot table
  • 00:28:20
    now something that's also quite useful
  • 00:28:23
    when you're in this powerpivot window is
  • 00:28:26
    this diagram view button
  • 00:28:28
    so if I click on diagram view this is
  • 00:28:31
    basically going to show us all of the
  • 00:28:32
    tables that we've created in this
  • 00:28:34
    diagram format and we can move these
  • 00:28:37
    around
  • 00:28:38
    and it gives us a nice visual way of
  • 00:28:40
    looking at how our tables are related
  • 00:28:42
    together
  • 00:28:43
    so you can see at the top here I have my
  • 00:28:45
    transaction data table and those are all
  • 00:28:47
    the column headings I then have my
  • 00:28:49
    accounts table and my products table
  • 00:28:52
    now currently all three of these tables
  • 00:28:55
    are part of the data model but they're
  • 00:28:57
    not linked together in any way they're
  • 00:29:00
    still three independent tables
  • 00:29:02
    so what do you think is going to happen
  • 00:29:04
    if I try and build a pivot table based
  • 00:29:07
    off of this data model and maybe I use
  • 00:29:10
    let's say I use the amount field from
  • 00:29:13
    the transaction data table and the
  • 00:29:15
    products field from the products table
  • 00:29:17
    what do you think is going to happen is
  • 00:29:19
    that going to work
  • 00:29:23
    if you guessed no then you would be
  • 00:29:25
    correct because these tables aren't
  • 00:29:28
    linked together Excel thinks they're
  • 00:29:30
    just independent of one another so
  • 00:29:32
    nothing is going to flow through let me
  • 00:29:34
    show you an example of that so you
  • 00:29:36
    understand what I mean
  • 00:29:38
    let's close this powerpivot window down
  • 00:29:42
    and let's create our first pivot table
  • 00:29:46
    so I'm going to close down queries and
  • 00:29:48
    connections
  • 00:29:49
    I'm going to click in my data I'm going
  • 00:29:51
    to go to insert and I'm going to say
  • 00:29:53
    pivot table from data model
  • 00:29:57
    I'm going to say I want this on a new
  • 00:29:59
    worksheet let's click on ok now I can
  • 00:30:03
    see here look at what we've got forget
  • 00:30:05
    about that one at the bottom that's just
  • 00:30:07
    from an old table that I was using these
  • 00:30:09
    are the ones from our data model so
  • 00:30:12
    we've got our accounts table and our
  • 00:30:14
    Fields just there our products table and
  • 00:30:16
    our fields and then our transaction data
  • 00:30:18
    and our fields now remember these are
  • 00:30:21
    all three independent tables so if I now
  • 00:30:25
    try and build a pivot table using a
  • 00:30:27
    field from the accounts table so let's
  • 00:30:29
    grab accounts and drag it into rows
  • 00:30:31
    all looks okay so far but if I now try
  • 00:30:35
    to drag the amount field from the
  • 00:30:38
    transaction data table into values
  • 00:30:41
    it's not working it's giving me all of
  • 00:30:43
    the same value it's not able to work
  • 00:30:46
    this out how much sales we've generated
  • 00:30:49
    by those different accounts because none
  • 00:30:52
    of those tables are interacting with
  • 00:30:54
    each other they're just not linked
  • 00:30:55
    together yet
  • 00:30:57
    so we've got errors well not errors I
  • 00:31:00
    should say we've got the same value so
  • 00:31:02
    we know that that's not correct and take
  • 00:31:04
    a look at this yellow message it says
  • 00:31:07
    relationships between tables may be
  • 00:31:10
    needed
  • 00:31:11
    so we need to link all three of our
  • 00:31:14
    tables together in order for this to
  • 00:31:16
    work correctly
  • 00:31:18
    so how do we go about linking tables
  • 00:31:20
    together
  • 00:31:21
    well this is very similar to if you've
  • 00:31:23
    ever used something like vlookup you
  • 00:31:26
    need to have a field in each table that
  • 00:31:28
    is common between the tables for example
  • 00:31:31
    the transaction data table is our main
  • 00:31:34
    table it has the most information we
  • 00:31:36
    have date transaction ID product number
  • 00:31:38
    account number amount
  • 00:31:41
    the accounts table we have account
  • 00:31:43
    number we also have account number in
  • 00:31:46
    the transaction data table so there is a
  • 00:31:49
    common field between the accounts table
  • 00:31:51
    and the transaction data table
  • 00:31:54
    so I could use the account number field
  • 00:31:55
    to link these two tables together
  • 00:31:59
    similarly I can link the products table
  • 00:32:01
    to transaction data by the product
  • 00:32:04
    number field
  • 00:32:06
    and if I do that and create these
  • 00:32:08
    relationships then Excel knows about
  • 00:32:12
    these other tables and it can perform
  • 00:32:13
    the calculations correctly
  • 00:32:16
    so let's take a look at that in action
  • 00:32:18
    now I can see here I've got this little
  • 00:32:20
    little yellow warning message it's
  • 00:32:23
    saying relationships are needed so I'm
  • 00:32:25
    going to say okay let's create our first
  • 00:32:27
    relationship
  • 00:32:29
    so I want to create a relationship first
  • 00:32:31
    of all between the transaction data
  • 00:32:34
    table and the accounts table
  • 00:32:37
    so the first table is the transaction
  • 00:32:39
    data table
  • 00:32:41
    the related table is the accounts table
  • 00:32:44
    and then I just need to tell Excel which
  • 00:32:47
    field they're linked by
  • 00:32:49
    so what is the common field between
  • 00:32:51
    these two tables well it's the account
  • 00:32:53
    number field
  • 00:32:55
    so as soon as I select account number
  • 00:32:57
    here Excel recognizes it in the other
  • 00:32:59
    one and adds it in for me
  • 00:33:01
    now as soon as I click on OK here check
  • 00:33:04
    out what happens to the pivot table you
  • 00:33:06
    should see that those numbers work
  • 00:33:08
    themselves out so let's click on OK
  • 00:33:11
    and there we go it is now working
  • 00:33:13
    because we've created that link Excel
  • 00:33:17
    knows how to do the calculations
  • 00:33:20
    so we've created a link between the
  • 00:33:22
    accounts table and the transaction data
  • 00:33:24
    table
  • 00:33:26
    what about if I remove accounts from
  • 00:33:27
    rows and switch in products I'm going to
  • 00:33:31
    get the same problem because I haven't
  • 00:33:33
    linked products to transaction data once
  • 00:33:38
    again I have the value the same value
  • 00:33:40
    being repeated over and over again and
  • 00:33:42
    I'm getting this yellow warning message
  • 00:33:45
    so once again I'm going to do the same
  • 00:33:47
    thing let's click on create we're going
  • 00:33:49
    to link the transaction data table
  • 00:33:52
    and the products table
  • 00:33:54
    but this time we're going to use the
  • 00:33:57
    field that's common between the two
  • 00:33:58
    which is the product number field
  • 00:34:01
    so let's select product number
  • 00:34:04
    click on OK and take a look at that
  • 00:34:06
    everything is now working
  • 00:34:10
    so now that everything is linked
  • 00:34:11
    together I can start building out my
  • 00:34:14
    pivot table using any field from any of
  • 00:34:17
    these tables and because I've now added
  • 00:34:20
    in additional data my data's a lot more
  • 00:34:22
    meaningful instead of just having
  • 00:34:24
    product numbers which don't mean much to
  • 00:34:26
    anybody in my analysis I've now pulled
  • 00:34:30
    in these product names instead so it's
  • 00:34:32
    going to be a lot more meaningful to
  • 00:34:33
    anybody who's looking at this data
  • 00:34:37
    once I have all this data in here I can
  • 00:34:38
    then start using my pivot table fields
  • 00:34:40
    in exactly the same way I normally would
  • 00:34:43
    so maybe I want to add in something like
  • 00:34:45
    let's go for a date above rows
  • 00:34:49
    something like that and you can see I'm
  • 00:34:52
    going to remove quarters a month so that
  • 00:34:53
    we just have the years I could drag date
  • 00:34:56
    across to columns so I have it organized
  • 00:34:59
    like that instead so once you have
  • 00:35:02
    everything linked together
  • 00:35:04
    everything should work nicely together
  • 00:35:06
    as long as you've created those
  • 00:35:08
    relationships between the tables again
  • 00:35:10
    it's much like vlookup it relies on a
  • 00:35:13
    common field
  • 00:35:15
    so that is basically how you can utilize
  • 00:35:18
    the data model to pull in data from
  • 00:35:21
    different sources to create a
  • 00:35:24
    Consolidated pivot table
  • 00:35:26
    now we're going to do one more thing Let
  • 00:35:29
    me just do a quick time check we're
  • 00:35:31
    going to be a little bit quicker than
  • 00:35:32
    normal in this session which is very
  • 00:35:34
    nice if everybody gets to go a little
  • 00:35:35
    bit earlier but there is one more thing
  • 00:35:37
    that I want to do
  • 00:35:39
    I'm going to show you how to very
  • 00:35:41
    quickly create a pivot chart based off
  • 00:35:43
    of this pivot data and then I'm going to
  • 00:35:45
    show you how to update with the next
  • 00:35:48
    year's transaction file because if you
  • 00:35:51
    remember right at the beginning when we
  • 00:35:53
    created our connection we created a
  • 00:35:56
    connection to the folder where these
  • 00:35:58
    transaction files were stored
  • 00:36:00
    so theoretically when I add the next
  • 00:36:02
    year's file into that folder all of my
  • 00:36:04
    Pivot tables and pivot charts should
  • 00:36:07
    update automatically because that
  • 00:36:08
    connection is there to the folder
  • 00:36:11
    so let's first do a little bit of
  • 00:36:13
    tidying up of this data and create
  • 00:36:15
    ourselves a basic pivot chart
  • 00:36:18
    now in my Pivot chart I don't want my
  • 00:36:20
    grand totals so let's utilize uh uh
  • 00:36:24
    let's go to the design tab at the top
  • 00:36:26
    here
  • 00:36:27
    and I'm going to turn off Grand totals
  • 00:36:29
    for rows and columns so we're going to
  • 00:36:32
    click on Grand totals and I'm going to
  • 00:36:35
    say turn those off in general you don't
  • 00:36:38
    tend to have grand total showing in
  • 00:36:40
    pivot charts it's not particularly very
  • 00:36:42
    helpful
  • 00:36:43
    now again when it comes to creating
  • 00:36:45
    things like pivot charts based off of
  • 00:36:47
    pivot data be quite mindful as to the
  • 00:36:49
    chart type that you're selecting for
  • 00:36:52
    example data like this we've got quite a
  • 00:36:54
    bit there it might look alright in a pie
  • 00:36:56
    chart but it would probably be better
  • 00:36:58
    represented in something like a column
  • 00:37:00
    chart or a bar chart
  • 00:37:02
    even then this data might be a little
  • 00:37:05
    bit too much let's have a quick look and
  • 00:37:07
    see what we get
  • 00:37:08
    so I'm going to go to the pivot table
  • 00:37:09
    analyze ribbon
  • 00:37:11
    and we're going to click on pivot chart
  • 00:37:14
    it's not too bad it's going to look like
  • 00:37:16
    that I'm actually going to apply a
  • 00:37:18
    filter because the people that I'm going
  • 00:37:20
    to be showing this pivot chart to are
  • 00:37:22
    only really interested in the top five
  • 00:37:25
    best-selling products so I'm going to
  • 00:37:28
    use a top 10 filter on this data let's
  • 00:37:32
    right click I'm going to go to filter
  • 00:37:35
    I'm going to say top 10 and I'm going to
  • 00:37:37
    take that down to show only the top five
  • 00:37:41
    items by the amount
  • 00:37:44
    now I have a much smaller more
  • 00:37:46
    manageable data set to represent in my
  • 00:37:49
    chart
  • 00:37:50
    so let's go back to Pivot charts and
  • 00:37:53
    yeah I'm just going to stick with the
  • 00:37:55
    basic clustered column chart let's click
  • 00:37:57
    on OK
  • 00:38:00
    so here we go let's drag this out a
  • 00:38:02
    little bit of course you can go to town
  • 00:38:04
    on the formatting of your pivot charts
  • 00:38:06
    I'm going to do some very basic
  • 00:38:08
    formatting here I'm going to remove
  • 00:38:10
    these gray buttons this is the first
  • 00:38:12
    thing I always do because I don't
  • 00:38:13
    particularly like to have these on my
  • 00:38:15
    charts I might want to do something like
  • 00:38:19
    add a chart title so let's just give
  • 00:38:22
    this a bit of meaning so this is the top
  • 00:38:26
    five uh
  • 00:38:28
    products we'll just leave it as top five
  • 00:38:30
    products
  • 00:38:33
    um what else do I want to do here do I
  • 00:38:35
    want to do anything I think we're pretty
  • 00:38:37
    good here maybe I want to change the
  • 00:38:38
    color make it a little bit more
  • 00:38:39
    interesting let's go to design I'm going
  • 00:38:42
    to go to change color I'm just going to
  • 00:38:44
    make this various different shades of
  • 00:38:47
    blue
  • 00:38:48
    as I said so much more you can do when
  • 00:38:50
    it comes to formatting your charts but
  • 00:38:53
    what I really want to show you here is
  • 00:38:54
    how simple it is to add the transaction
  • 00:38:58
    file for 2021 and have everything that
  • 00:39:01
    you've created update
  • 00:39:04
    so take a look at the pivot table and
  • 00:39:06
    the pivot chart currently you can see
  • 00:39:08
    that we only have data for 2019 and
  • 00:39:11
    2020. that's also reflected in the
  • 00:39:13
    legend in the pivot chart as well 2019
  • 00:39:16
    and 2020.
  • 00:39:19
    now when we created this we created a
  • 00:39:21
    link to that transaction data folder
  • 00:39:24
    so let's minimize Excel
  • 00:39:27
    I'm going to open up this transaction
  • 00:39:29
    data folder
  • 00:39:31
    and what we're going to say is we're
  • 00:39:33
    going to say it's the end of 2021 and we
  • 00:39:37
    want to add the years transaction data
  • 00:39:40
    into this folder and have everything
  • 00:39:42
    update automatically in our pivot table
  • 00:39:45
    and our pivot chart
  • 00:39:47
    because we've linked to the folder we
  • 00:39:49
    can do this very simply
  • 00:39:51
    so you should also have the file that I
  • 00:39:54
    sent through called transactions 20 2021
  • 00:39:57
    saved off to your desktop I have mine in
  • 00:39:59
    this folder called new data
  • 00:40:02
    so what I want you to do is move or copy
  • 00:40:04
    this file into the transaction data
  • 00:40:07
    folder so I'm just going to drag and
  • 00:40:10
    drop this file into here
  • 00:40:15
    I'll give you a couple of seconds just
  • 00:40:17
    to do that if you're following along
  • 00:40:21
    so now that I've done that each year I
  • 00:40:24
    might do exactly the same thing if I
  • 00:40:27
    want to have that data included in my
  • 00:40:29
    analysis all I have to do is go back to
  • 00:40:32
    my pivot table and refresh it
  • 00:40:35
    and that is literally a one-click option
  • 00:40:39
    all you need to do is click in the pivot
  • 00:40:40
    table or click on the pivot chart go up
  • 00:40:43
    to pivot table analyze and in the middle
  • 00:40:45
    here you have a refresh button now check
  • 00:40:49
    out what happens when I click on refresh
  • 00:40:51
    all you should see that a column is
  • 00:40:54
    added automatically to the pivot table
  • 00:40:56
    for 2021 and we should get another bar
  • 00:40:58
    in our chart so let's click on refresh
  • 00:41:01
    all
  • 00:41:03
    and would you take a look at that how
  • 00:41:06
    quick has that been to do and that's
  • 00:41:08
    simply because we created a link to the
  • 00:41:11
    folder and we can then simply upload the
  • 00:41:13
    file each year to this folder click the
  • 00:41:16
    refresh button and all of our reporting
  • 00:41:18
    is updated in one click
  • 00:41:21
    really nice and straightforward
  • 00:41:25
    so that is pretty much what I wanted to
  • 00:41:27
    show you today as I said we have
  • 00:41:28
    finished a little bit quicker to normal
  • 00:41:30
    there is a lot that we covered in there
  • 00:41:33
    it can be quite overwhelming and with
  • 00:41:35
    something like this there are numerous
  • 00:41:37
    different ways that you can do this I
  • 00:41:39
    know that some people don't use the
  • 00:41:40
    power pivot ribbon at all some people
  • 00:41:43
    just import all of their data through
  • 00:41:45
    this get data area some people use
  • 00:41:47
    entirely power pivot there's many
  • 00:41:49
    different ways I've just showed you ways
  • 00:41:52
    that tend to work for me in ways that
  • 00:41:54
    make sense to me but as always you can
  • 00:41:57
    do your own research and find out the
  • 00:41:59
    best method that suits you
  • 00:42:02
    but hopefully you now have a better
  • 00:42:03
    understanding not only as to the
  • 00:42:05
    difference between creating a pivot
  • 00:42:07
    table based off of one data source for
  • 00:42:09
    how you can create a Consolidated pivot
  • 00:42:11
    table where you're bringing in data from
  • 00:42:13
    multiple data sources and the biggest
  • 00:42:16
    thing here for me is really linking to a
  • 00:42:19
    folder to make updating your data so
  • 00:42:22
    much quicker and easier and effectively
  • 00:42:24
    making yourself more efficient
  • 00:42:27
    if you're not a subscriber click down
  • 00:42:30
    below to subscribe so you get notified
  • 00:42:32
    about similar videos we upload
  • 00:42:34
    to see the full course that this video
  • 00:42:37
    came from click over there and
  • 00:42:41
    click over there to see more videos from
  • 00:42:44
    Simon says it
Tags
  • Excel
  • Pivot Tables
  • Data Model
  • Power Query
  • Data Transformation
  • Consolidated Data
  • Data Analysis
  • Data Relationships
  • Webinar
  • Microsoft Excel