Invite to [excel] university. My name is John, and also [this] is the first in a collection of video clips regarding pivot tables So I intend to introduce you to my pal andy Andy is new on the job as well as he has actually been asked by his manager to produce some records and inevitably a control panel for a forthcoming discussion all Andy was provided is this gigantic sheet of information and also a listing of records that his employer requires although he” s excited about the difficulty [andy] is asking yourself how it” s going to turn this data into a great looking control panel obviously he intends to save the day and also come to be the superhero of the office So what andy really requires is to learn exactly how to utilize pivot tables? In this video series, I” m going to show you just how we” re going to resolve Andy” s issue We ‘ re mosting likely to take this sheet of data and transform it right into a [nice-looking] interactive dashboard utilizing pivottables and pivotcharts We are going to build each of these records using pivot tables and after that bring all of it together to produce the control panel And don” t concern if you assume this looks complicated Pivot Tables are really easy to make use of as well as wear” t need any complicated formulas or VBa Macros simply straightforward drag-and-drop Throughout this video series.I ‘ m also going to be showing you an ad as well as I created called pivot Friend Pivot Friend makes it less complicated to work with some elements of pivot tables [and] will certainly save you a great deal [of] time However you wear ‘ t requirement to have pivot'[ pal] to develop these pivot tables and also control panels whatever you require is currently built into stand out Alright, so in this very first video.We ‘ re mosting likely to cover the fundamentals of developing a pivot table I ‘ m going to show you just how to create this pivot table below That ‘ s a recap of income by sales associate and likewise this pivot chart over here Which simply displays that info aesthetically? So we understand [that] Andy has this collection of information to work with this is really all he ‘ s been Offered and also this is what we ‘ re going to utilize to create our pivot tables this will be considered our resource information And also prior to you begin constructing a pivot table. It ‘ s a good idea to acquaint on your own with Resource information specifically if you sanctuary ‘ t seen it prior to so this information was exported from our general ledger software application and Generally it contains order information for December so each row in the information below consists of details for certain Order so you can see we have the order day some client details here the sales representative Area we obtained some delivery info as I scroll over and also we likewise have some item information and afterwards some info regarding the cost of the item the earnings quantity all that stuff, so it ‘ s great information The other point you wish to do is see to it that your information remains in a tabular style Which [simply] indicates that you have [a] row of headers below at the top of the data set and each of these headers defines the Column of Data below and also I offered a checklist, so [you] can review this in even more information the other things you desire to look out for is that you desire to make sure there ‘ s? No empty Columns within your header row below And no Blank Rows within your information set especially [if] you have a column of dates’you [desire] to make certain there ‘ s no empty rows or cells in your day column you additionally wish to keep an eye out for reading [joined] cells a lot of times when you ‘ re exporting information the Software program will automatically combine some cells with each other, and you desire to see to it you unmiss Information looks respectable We go to most likely to the insert
on the Ribbon and afterwards click the pivot table button And also that will raise this produce pivot table home window and primarily it ‘ s asking us to select the table or array now You can see that succeed immediately detected the variety for me Which ‘ s due to the fact that I had a cell picked inside the pivot table I ‘ m sorry inside the information set Before I click to the develop pivot table button so as long as you do that select a cell inside the array Click this switch and stand out will instantly spot the array for you If it doesn ‘ t you can simply click this blue switch over right here and also go as well as choose your range The following action is that we want to pick where we ‘ re mosting likely to put the pivot table’in this case We ‘ ll placed it on a new worksheet So I ‘ ll proceed and also Click alright As well as now you ‘ ll see that ‘ s automatically added a new sheet to our workbook below as well as developed this pivot table location’you ‘ ll also see over on the best side that’this pivot table fields listing has appeared as well as this is essentially the Home window we ‘ re going’to use to create the pivot table So here ‘ s all a listing of all the fields in our information established below As well as after that down below’is the areas that we can dress those areas [right into] to create a pivot table record So the first point I ‘ m going to do is discover that Salesman field and also below it is right here I ‘ m going to drag that into the rows location [so]. I ‘ m left click and also hold drag it right into the rows location of the pivot table as well as drop it there as well as You ‘ ll notice now [that] in the pivot table It ‘ s noted our sales reps in the rows location of the pivot table’so each row contains Our sale a sales rep name now I want to add the income to this record as well, so I can get a summary of earnings so I ‘ m going to locate this income field here in the area list and afterwards drag it in [to] the worths area of the pivot table When I drag it into the values area as well as drop it there you ‘ ll notification that Excel has actually automatically Computed the amount of Revenue for every representative that ‘ s noted in the rows location of the pivot table [so] this is actually the power in the magic of the pivot table is automatically computed the sum [of] earnings for every rep simply practically instantaneously as we drag the area into the values area and also It ‘ s a great idea to obtain an understanding of what actually happens there, so allow me attempt and explain this a little better so right here we can see [andruw] has Sales of Twelve thousand three hundred [sixty-eight] here in the pivot table as well as generally what the pivot table is done So I go back to the information Tab I ‘ m mosting likely to filter the salesperson field right here for Andrew So we ‘ re [just] seeing the sales that Andrews done As well as you can see right here that he ‘ s done eight sales in the month of December now.If I go [over] [to] the Rows Column and also I ‘ m simply going to pick these earnings numbers here you can see here ‘ s our amount of income
twelve 368 for Andrew so essentially the pivot table has done this filter and Computation for us. It ‘ s essentially filtered the data collection for each and every rep each unique product in that’area and then Calculated the amount of Earnings for each and every rep so it ‘ s an extremely powerful device since with simply a couple of clicks’You can see that we ‘ ve automatically produced this great summary report from our information set as well as I likewise have a Pivot table layout below that simply kind of programs’you where each of these areas are within its Typical pivot So you can see this is’type of a conventional looking record we have our sales rep below on the left side we have quarters across the leading and after that the amount of Profits here in the center section as well as up right here. We have the year a filter for the year So these are primarily the various areas that you can drag areas right into in the pivot table as well as Each of them presents details a little in different ways so throughout this video series.I ‘ ll clarify how each [of] these locations within the Pivot Table works Okay, so let ‘ s proceed developing out our pivot table [so] probably the initial thing [you ‘ ll] notification is that these numbers are [not] formatted when we medicine the Revenue field into the values location it did not format those numbers so we want to format those and the easiest method to do that Is we perfect click any type of cell in the values’area there and after that go to worth field setups? That will raise this worth field setups for the income Fields sum of income And we ‘ re mosting likely to click the number layout button that ‘ ll bring up the format cells dialog this You ‘ re probably aware of I ‘ m simply going to choose money No, decimals click ok and after that click ok below, as well as you can see now that our numbers are formatted, as well as they look a little better The following point I ‘ m mosting likely to do is sort this info So I wish to put the top-performing rap on top of the listing here [so] [primarily] I ‘ m just going to pick any type of cell in the worths location and right click and afterwards [go] to sort and also sort biggest to tiniest And also currently that ‘ s sorted our list below from largest to tiniest so we can see our leading executing rap Which is nancy on top of the list and afterwards our bottom performing representative which is jan down near the bottom so this? Currently provides us some truly excellent details We can sort of see who ‘ s doing well, and also possibly who struggled this month or for one reason or another didn ‘ t do also As well as I ‘ m more of an aesthetic person so I wish to see this information in a chart as well so as long as I have actually any type of cell selected in the pivot table and also go up to the examine tab or the choices tab and Click pivot graph, which will certainly raise this chart window, and I ‘ m going to put a bar graph Click alright So currently we ‘ ve immediately produced a bar chart here.Just permits us to envision this information As well as I ‘ m going to swiftly show you exactly how we can clean this up due to the fact that the default graph formatting out of package isn ‘ t It ‘ s kind of unsightly Honestly, so we ‘ re going to cleanse this up a little bit so the initial thing I do is just conceal all these filled’switches these loaded buttons permit you to apply
[ filters] But we wear ‘ t actually need that for our control panel so I ‘ m mosting likely to conceal those you ‘ ll also discover that these Names right here or’in the contrary order of what they are here on the pivot table So we require to reverse the order of this, so I ‘ m mosting likely to ideal go here Go to Format access that will bring up this format accessibility home window It looks a bit various in succeed 2010,’but the options are the same go to access alternatives And after that I ‘ m mosting likely to examine this box categories in reverse order, which ‘ s mosting likely to turn around the [order] of these groups below So they match what ‘ s on the web page? I ‘ m mosting likely to close that the various other thing.I wish to do [I ‘ m] going to obtain rid of these tags here So I ‘ m simply going to left-click to choose those as well as struck the remove key on the keyboard I ‘ m also going to obtain eliminate the tale so click that and also remove on the keyboard As well as I intend to get rid [of] these upright lines So’I” m mosting likely to left click on those erase as well as
then I additionally desire to add some tags right here, so I in succeed 2013 You can just click this plus button go to information tags that ‘ ll add our data tags there. I intend to make my plot location a little smaller sized so the information label doesn ‘ t overlap the bar as well as Now I likewise wish to make these bars larger, so to do that I ‘ m going to once more right click click on [a] bar right click most likely to style data series That will certainly raise the layout information collection home window, as well as we ‘ re looking a collection alternatives We ‘ re looking for a space width as well as if I simply make that smaller that ‘ ll make the void between benches Smaller sized and also make the bars bigger, so currently we can see that we have some larger looking bars As well as this is starting [to] look pretty good naturally we intend to add a total or I ‘ m sorry a title right here so we ‘ re mosting likely to go sales by associate for December 2014 Something like that so now with simply a couple of clicks We have this fantastic looking graph that enables us to aesthetically see our [report] [here] are sales by rep for the month of December So ideally you kind of seen the power of the pivottable right here, and it ‘ s not actually that frightening of a thing It ‘ s in fact extremely simple to make use of as well as exceptionally powerful as well as allows you to promptly summarize as well as picture your details Great So I wish to promptly reveal [you] how you can utilize pivot Buddy to develop this same pivot table report And also just how it makes it a little faster to do so so the very first thing I ‘ m mosting likely to do is I ‘ m going to remove this report so we can begin fresh and also to’erase fields out of your pivot table [you] can discuss here to the pivot table field list and simply left-click [and] drag them out of the location till you see that x Next to the computer mouse symbol and afterwards allow [go] which will certainly remove the field out of the aerial right there So currently we have this Blank pivot table, and also [I ‘ m]. simply going to go up to the succeed campus tab on the Bow as well as Click the pivot [Chum] button Once you set up pivot Pal You ‘ ll see this succeed university tab up here and also basically pivot Pal is an add-in that I ‘ ve established that makes it faster and simpler to collaborate with some aspects of pivot tables So you ‘ ll notice something when we were building the pivot table [we] basically were scrolling via this checklist over here to attempt and find our Field that we desired to contribute to among these locations [as well as] pivot Friend has a built-in search that makes this much faster so if I intend to locate sales person I can just start inputting [salesman] you ‘ ll see as I begin keying the Results are limited I can pick [salesperson] below struck get in that will select it down right here in the area listing for B And after that I can use these buttons or key-board faster ways to put the selected area in the location of the pivot table So I wish to place salesperson in rows, and also now it ‘ s just added two rows.I also wish to place income into My worths location this [time] We ‘ ll use keyboard Shortcut alt V which will put the income in’my worths area very swiftly The various other great function of pivot Friend is it instantly formats the worths location for you? So you see when we added this revenue area? I ‘ ll just proceed and remove the values location out when we include the income area into the worths location making use of the field checklist The numbers are not formatted you can see that there not formatted We have’to take a few additional actions
to layout them nonetheless when we do this with pivot Buddy the once more. I ‘ ll add the profits to the values area this with pivot [Chum] the numbers automatically [formatted] and that ‘ s because pivot Chum identifies the resource format so there ‘ s a formatting [Drop-down] here in Pivot Friend As well as it discovers the source Formatting of the area so if I most likely to the data collection as well as this button enables you to jump back to the data you can see our revenue field has this currency format already put on it and Pivot Friend Will automatically format that field for you like that and also If your source data is not formatted or you want to change the format you can do that really rapidly With pivot [pals] well you can just pick from any one of these predefined options right here as well as Layout, the values very very quickly and also obviously you can transform this list right here as well as add your personalized styles to it as you like So in the next video we ‘ re mosting likely to learn a few more great attributes of pivot tables we ‘ re going to have a look at several of the various computation kinds as well as additionally how to [add] much more data to your data collection as well as Apply filters as well as an entire great deal a lot more so as you can see Andy ‘ s rather excited concerning pivot tables [and] I hope you are also, however I ‘ d love to understand what you assume so, please leave a remark listed below with any type of inquiries Or suggestions.Thanks once again for enjoying and also I ‘ ll see you soon.
Call Now for Discount Airline Tickets
