Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint…
WATCH PART 2: • Introduction to Pivot Tables, Charts,…
Download file used in the video: https://www.excelcampus.com/pivot-tab…
In this video series you will learn how to create an interactive dashboard using Pivot Tables and Pivot Charts.
Works with Excel 2003, 2007, 2010, 2013 for Windows \u0026 Excel 2011 for Mac
Don’t worry if you have never created a Pivot Table before, I cover the basics of formatting your source data and creating your first Pivot Table as well. You will also get to see an add-in I developed named PivotPal that makes it easier to work with some aspects of Pivot Tables.
Download the files to follow along at the following link.
http://www.excelcampus.com/pivot-tabl…
I have another video that shows how to reformat the pivot chart in Excel 2010. In the video above I’m using Excel 2013 and the menus are different from Excel 2007/2010. Here is the link to that video. • Excel 2010: Pivot Chart Formatting Ma…
Get PivotPal: https://www.excelcampus.com/pivotpal
Free webinar on The 5 Secrets to Understanding Pivot Tables: https://www.excelcampus.com/pivot-web…
Subscribe to my free newsletter: https://www.excelcampus.com/newsletter
Content
1.03 -> Welcome to [excel] campus. My name is John, and [this] is the first in a series of videos about pivot tables
7.27 -> So I want to introduce you to my friend andy
10.45 -> Andy is new on the job and he has been asked by his boss to create some reports and
14.469 -> ultimately a dashboard for an upcoming presentation all
18.07 -> Andy was given is this giant sheet of data and a list of reports that his boss needs
22.779 -> although he's excited about the challenge [andy] is wondering how it's going to turn this data into a nice looking dashboard of
29.41 -> Course he wants to save the day and become the superhero of the office
33.16 -> So what andy really needs is to learn how to use pivot tables?
38.35 -> In this video series, I'm going to show you how we're going to solve Andy's problem
42.28 -> We're going to take this sheet of data and turn it into a [nice-looking] interactive dashboard using pivottables and pivotcharts
49.84 -> We are going to build each of these reports using pivot tables and then bring it all together to create the dashboard
56.649 -> And don't worry if you think this looks complicated
59.77 -> Pivot Tables are very easy to use and don't require any complex formulas or VBa Macros just simple drag-and-drop
67.659 -> Throughout this video series. I'm also going to be showing you an ad and I developed called pivot Pal
72.97 -> Pivot Pal makes it easier to work with some aspects of pivot tables [and] will save you a lot [of] time
78.369 -> But you don't need to have pivot [pal] to create these pivot tables and dashboards everything you need is already built into excel
85.54 -> Alright, so in this first video. We're going to cover the basics of building a pivot table
89.979 -> I'm going to show you how to create this pivot table here
93.03 -> That's a summary of revenue by sales rep and also this pivot chart over here
97.38 -> Which just displays that information visually?
100.36 -> So we know [that] Andy has this set of data to work with this is really all he's been
105.159 -> Given and this is what we're going to use to create our pivot tables this will be considered our source data
111.399 -> And before you start building a pivot table. It's a good idea to familiarize yourself with Source data
115.869 -> especially if you haven't seen it before so this data was exported from our general ledger software and
121.869 -> Basically it contains order details for December so each row in the data here contains information for specific
130.27 -> Order so you can see we have the order date some customer information here the salesperson
136.49 -> Region we got some shipping
138.41 -> information as I scroll over and we also have some product information and
143 -> Then some information about the price of the product the revenue quantity all that stuff, so it's good information
149.48 -> The other thing you want to do is make sure that your data is in a tabular format
153.58 -> And that [just] means that you have [a] row of headers here at the top of the data set and each of these headers
160.64 -> describes the Column of Data below and I provided a checklist, so
165.47 -> [you] can go over this in more detail the other things you want to look out for is that you want to make sure there's?
169.84 -> No blank Columns within your header row here
172.75 -> And no Blank Rows
174.38 -> within your data set especially [if] you have a column of dates you [want] to make sure there's no blank rows or cells in your
181.24 -> date column you also want to look out for reading [merged] cells a lot of times when you're exporting data the
187.07 -> Software will automatically merge some cells together, and you want to make sure you
194.24 -> unmiss Data looks pretty good
196.01 -> We go to go to the insert [tab] on the Ribbon and then click the pivot table button
201.89 -> And that will bring up this create pivot table window and basically it's asking us to select the table or range now
209.65 -> You can see that excel automatically detected the range for me
213.16 -> And that's because I had a cell selected inside the pivot table
217.57 -> I'm sorry inside the data set
219.47 -> Before I click to the create pivot table button so as long as you do that select a cell inside the range
224.84 -> Click this button and excel will automatically detect the range for you
228.22 -> If it doesn't you can just click this blue button over here and go and select your range
233.269 -> The next step is that we want to choose where we're going to put the pivot table in this case
238.06 -> We'll put it on a new worksheet
239.269 -> So I'll go ahead and Click ok
241.28 -> And now you'll see that's automatically added a new sheet to our workbook here and created this pivot table area
248.72 -> you'll also notice over on the right side that this pivot table fields list has appeared and this is basically the
256.4 -> Window we're going to use to create the pivot table
259.06 -> So here's all a list of all the fields in our data set here
263.2 -> And then down below is the areas that we can dress those fields [into] to create a pivot table report
269.96 -> So the first thing I'm going to do is find that
272.99 -> Salesperson field and here it is here
275.47 -> I'm going to drag that into the rows area
277.629 -> [so] I'm left click and hold drag it into the rows area of the pivot table and drop it there and
282.169 -> You'll notice now [that] in the pivot table
284.9 -> It's listed our sales reps in the rows area of the pivot table so each row contains
290.06 -> Our sale a sales rep name now
292.75 -> I want to add the revenue to this report as well, so I can get a summary of revenue
297.49 -> so I'm going to find this revenue field here in the field list and
300.71 -> Then drag it in [to] the values area of the pivot table
304.19 -> When I drag it into the values area and drop it there you'll notice that
308.449 -> Excel has automatically
310.94 -> Calculated the sum of Revenue for each rep that's listed in the rows area of the pivot table
316.49 -> [so] this is really the power in the magic of the pivot table is
320.509 -> automatically calculated the sum [of] revenue for each rep just almost instantly as we drag the field into the values area and
328.639 -> It's a good idea to get an understanding of what actually happens there, so let me try and explain this a little better
333.86 -> so here we can see [andruw] has
336.53 -> Sales of Twelve thousand three hundred [sixty-eight] here in the pivot table and basically what the pivot table is done
343.58 -> So I go back to the data Tab
345.469 -> I'm going to filter the salesperson field here for Andrew
349.779 -> So we're [just] seeing the sales that Andrews done
352.219 -> And you can see here that he's done eight sales in the month of December now. If I go [over] [to] the Rows Column and
359.479 -> I'm just going to select these revenue
362.33 -> numbers here
363.159 -> you can see here's our sum of revenue twelve 368 for Andrew so basically the pivot table has done this filter and
371.449 -> Calculation for us. It's basically
373.699 -> filtered the data set for each rep each unique item in that field and then
380.63 -> Calculated the sum of Revenue for each rep so it's a very powerful tool because with just a few clicks
385.93 -> You can see that
387.4 -> we've automatically created this nice summary report from our data set and
391.4 -> I also have a
392.96 -> Pivot table diagram here that just kind of shows you where each of these areas are within its Standard pivot
400.24 -> So you can see this is kind of a standard looking report
403.09 -> we have our sales rep here on the left side we have quarters across the top and then the sum of
408.199 -> Revenue here in the middle section and up here. We have the year a filter for the year
413.8 -> So these are basically the different
415.94 -> areas that you could drag fields into in the pivot table and
419.69 -> Each of them displays information a little differently so throughout this video series. I'll explain how each [of] these areas
426.53 -> within the Pivot Table works
429.59 -> Okay, so let's continue building out our pivot table
432.009 -> [so] probably the first thing [you'll] notice is that these numbers are [not] formatted when we drug the
438.289 -> Revenue field into the values area it did not format those numbers so we want to format those and the easiest way to do that
444.16 -> Is we just right click on any cell in the values area there and then go to value field settings?
450.919 -> That will bring up this value field settings for the revenue Fields sum of revenue
455.33 -> And we're going to click on the number format button that'll bring up the format cells dialog this one
460.36 -> You're probably familiar with I'm just going to choose currency
463.55 -> No, decimals click ok and then click ok here, and you can see now that our numbers are formatted, and they look a little nicer
472.13 -> The next thing I'm going to do is sort this information
475.21 -> So I want to put the top-performing rap at the top of the list here [so] [basically]
480.199 -> I'm just going to select any cell in the values area and right click and then [go] to sort and sort largest to smallest
488.12 -> And now that's sorted our list here from largest to smallest so we can see our top performing rap
494.09 -> Which is nancy at the top of the list and then our bottom performing rep which is jan down at the bottom so this?
500.12 -> Now gives us some really good information
502.03 -> We can kind of see who's doing well, and maybe who struggled this month or for some reason didn't do as well
509.539 -> And I'm more of a visual person so I want to see this information in a chart as well
514.899 -> so as long as I have any cell selected in the pivot table and go up to the analyze tab or the options tab and
522.74 -> Click pivot chart, and that will bring up this chart window, and I'm going to insert a bar chart
528.89 -> Click ok
530.72 -> So now we've automatically created a bar chart here. Just allows us to visualize this information
537.68 -> And I'm going to quickly show you how we can clean this up because the default chart formatting out of the box isn't
543.47 -> It's kind of ugly
544.97 -> Honestly, so we're going to clean this up a bit so the first thing
547.329 -> I do is just hide all these filled buttons these filled buttons allow you to apply [filters]
552.889 -> But we don't really need that for our dashboard so I'm going to hide those you'll also notice that these
559.339 -> Names here or in the opposite order of what they are here on the pivot table
563.959 -> So we need to reverse the order of this, so I'm going to right click here
567.88 -> Go to Format access that will bring up this format access window
571.81 -> It looks a little different in excel 2010, but the options are the same go to access options
576.79 -> And then I'm going to check this box categories in reverse order, and that's going to reverse the [order] of these categories here
583.209 -> So they match what's on the page?
585.23 -> I'm going to close that the other thing. I want to do
588.56 -> [I'm] going to get rid of these labels here
591.31 -> So I'm just going to left-click to select those and hit the delete key on the keyboard
595.069 -> I'm also going to get rid of the legend so click that and delete on the keyboard
599.899 -> And I want to get rid [of] these vertical lines
603.67 -> So I'm going to left click on those delete and then I also want to add some labels here, so I in excel 2013
611.899 -> You can just click on this plus button go to data labels that'll add our data labels there. I want to make my
618.74 -> plot area a little smaller so the data label doesn't overlap the bar and
624.11 -> Now I also want to make these bars wider, so to do that
627.399 -> I'm going to again right click click on [a] bar right click go to format data series
632.51 -> That will bring up the format data series window, and we're looking a series options
637 -> We're looking for a gap width and if I just make that smaller that'll make the gap between the bars
642.319 -> Smaller and make the bars bigger, so now we can see that we have
646.76 -> some bigger looking bars
648.13 -> And this is starting [to] look pretty nice of course we want to add a total or I'm sorry a title here
652.389 -> so we're going to go sales by rep for
655.73 -> December
656.93 -> 2014
657.949 -> Something like that so now with just a few clicks
661.3 -> We have this great looking chart that allows us to visually see our [report] [here] are
667.779 -> sales by rep for the month of December
671.06 -> So hopefully you kind of seen the power of the pivottable here, and it's not really that scary of an item
676.959 -> It's actually very easy to use and extremely powerful and allows you to quickly summarize and visualize your information
685.67 -> All right
686.379 -> So I want to quickly show [you] how you can use pivot Pal to create this same pivot table report
691.24 -> And how it makes it a little faster to do so so the first thing
694.959 -> I'm going to do is I'm going to clear out this report so we can start fresh and to delete
699.649 -> fields out of your pivot table
701.259 -> [you] can go over here to the pivot table field list and
703.73 -> just left-click [and] drag them out of the area until you see that x
707.36 -> Next to the mouse icon and then let [go] and that will delete the field out of the aerial right there
712.87 -> So now we have this
714.259 -> Blank pivot table, and [I'm] just going to go up to the excel campus tab on the Ribbon and
719.689 -> Click the pivot [Pal] button
721.73 -> Once you install pivot Pal
723.249 -> You'll see this excel campus tab up here and basically pivot Pal is an add-in that I've developed that makes it
729.889 -> faster and easier to work with some aspects of pivot tables
733.79 -> So you'll notice one thing when we were building the pivot table
736.699 -> [we] basically were scrolling through this list over here to try and find our
741.139 -> Field that we wanted to add to one of these areas [and] pivot Pal has a built-in search that makes this much faster
748.149 -> so if I want to find sales person I can just start typing [salesperson] you'll see as I start typing the
753.259 -> Results are narrowed down
754.449 -> I can choose [salesperson] here hit enter that will select it down here in the field list for B
759.85 -> And then I can use these buttons or keyboard shortcuts to place the selected field in the area of the pivot table
766.66 -> So I want to put salesperson in rows, and now it's just added two rows. I also want to put revenue into
773 -> My values area this [time]
774.67 -> We'll use keyboard Shortcut alt V and that will put the revenue in my values area very quickly
780.8 -> The other nice feature of pivot Pal is it automatically formats the values area for you?
786.279 -> So you notice when we added this revenue field?
790.04 -> I'll just go ahead and delete the values area out when we add the revenue field into the values area using the field list
798.019 -> The numbers are not formatted you can see that there not formatted
802.06 -> We have to take a few extra steps to format them however when we do this with pivot Pal the again. I'll add the revenue
810.29 -> to the values area
811.76 -> this with pivot [Pal] the numbers automatically [formatted] and that's because pivot Pal detects the source formatting so there's a
819.32 -> formatting
820.73 -> [Drop-down] here in Pivot Pal
822.279 -> And it detects the source
823.58 -> Formatting of the field so if I go to the data set and this button allows you to jump back to the data
829.73 -> you can see our revenue field has this currency format already applied to it and
836.3 -> Pivot Pal Will
837.44 -> automatically format that field for you like that and
840.62 -> If your source data is not formatted or you want to change the format you can do that very quickly
846.05 -> With pivot [pals] well you can just select from any of these predefined options
851.27 -> here and
852.5 -> Format, the values very very quickly and of course you can change this list here and add your custom formats to it as you like
861.529 -> So in the next video we're going to learn a few more great features of pivot tables
865.16 -> we're going to take a look at some of the different calculation types and also how to [add] more data to your data set and
871.07 -> Apply filters and a whole lot more so as you can see Andy's pretty excited about pivot tables
877.67 -> [and] I hope you are too, but I'd love to know what you think so, please leave a comment below with any questions
883.39 -> Or suggestions. Thanks again for watching and I'll see you soon
Source: https://www.youtube.com/watch?v=9NUjHBNWe9M